sql 练习

create table sc
(
id int primary key,
stuname varchar(20),
course varchar(20),
score int 
)

insert into sc values(1,'张三','语文',81)
insert into sc values(2,'张三','数学',75)

insert into sc values(3,'李四','语文',81)
insert into sc values(4,'李四','数学',81)

insert into sc values(5,'王五','语文',79)
insert into sc values(6,'王五','数学',100)
insert into sc values(7,'王五','英语',80)


select distinct stuname from sc where score>=80

--查询出每门课都大于80分的学生姓名 
select distinct A.stuname from sc A
where A.stuname not in (select sc.stuname from sc where sc.score<80 ) 

select distinct stuname from sc
where stuname not in (select distinct sc.stuname from sc where sc.score<80) 

insert into sc values(11,'张三','数学',75)
insert into sc values(12,'张三','数学',75)
insert into sc values(13,'张三','数学',75)

select * from sc
--查询id不同，其他字段都相同的记录
select * from sc as a where exists
(select * from sc as b where a.id<>b.id and a.stuname=b.stuname and
 a.course=b.course and a.score=b.score)

--删除除了Id不同其他都相同的记录
delete a from sc as a where stuname in (
select b.stuname from sc as b where a.id<>b.id and a.stuname=b.stuname and
 a.course=b.course and a.score=b.score)


--删除除了Id不同其他都相同的记录,但保存Id最大的那条记录
delete sc where not exists(
select 1 from sc as a where a.stuname=sc.stuname and
 a.course=sc.course and a.score=sc.score group by a.stuname,a.course,a.score having max(a.id)=sc.id) 


--统计某一列为某一个值的行数
select stuname,sum(case when course='语文' then 1 else 0 end) as '语文',
sum(case when course='数学' then 1 else 0 end) as '数学'
from sc group by stuname