SQL 基础练习

 1 create table chengji1(
 2 name  varchar(50) not null,
 3 kecheng varchar(50) not null,
 4 fenshu int not null
 5 )
 6 insert chengji1(name,kecheng,fenshu)values(‘张三‘,‘语文‘,81),(‘张三‘,‘数学‘,75),(‘李四‘,‘语文‘,76),(‘李四‘,‘数学‘,90),(‘王五‘,‘语文‘,81),(‘王五‘,‘数学‘,100),(‘王五‘,‘英语‘,90)
 7 
 8 select * from chengji1
 9 --distinct 用于返回唯一不同的值--
10 select distinct name from chengji1 where name not in (select distinct name from chengji1 where fenshu <= 80 )
11 --增加having字句的原因是，where关键字无法与合计函数一起使用--
12 select name from chengji1 group by name having min(fenshu)> 80
13 ----
14 select name from chengji1 group by name having COUNT(kecheng) >= 3 and MIN(fenshu) >= 80 

 1 create table stutable1(
 2 id int primary key IDENTITY(1,1) not null,
 3 stunumber varchar(20) not null,
 4 name varchar(20) not null,
 5 kcnumber varchar(20) not null,
 6 kecheng varchar(20) not null,
 7 fenshu int not null
 8 )
 9 insert stutable1(stunumber,name,kcnumber,kecheng,fenshu) values(‘2005001‘,‘张三‘,‘0001‘,‘数学‘,69),(‘2005002‘,‘李四‘,‘0001‘,‘数学‘,89),(‘2005001‘,‘张三‘,‘0001‘,‘数学‘,69)
10 select * from stutable1
11 delete stutable1 where id not in (select  MIN(id) from stutable1 group by stunumber,name,kcnumber,kecheng,fenshu)

create table yma(
year int not null,
month int not null,
amount decimal(18,1) not null
)
insert yma values(1991,1,1.1),(1991,2,1.2),(1991,3,1.3),(1991,4,1.4),(1992,1,2.1),(1992,2,2.2),(1992,3,2.3),(1992,4,2.4)
select * from yma
delete from yma

select year,
(select amount from yma m where month =1 and m.year= yma.year) as m1,
(select amount from yma m where month =2 and m.year= yma.year) as m2,
(select amount from yma m where month =3 and m.year= yma.year) as m3,
(select amount from yma m where month =4 and m.year= yma.year) as m4
from yma group by year

create table yma1(
year int not null,
month int not null,
amount decimal(18,1) not null
)
insert into yma1(year,month,amount)select year,month,amount from yma
select * from yma1