SQL语句练习

表：

create table s(
    sno varchar2(50) primary key,
    sname varchar2(50)
);
create table c(
    cno varchar2(50) primary key,
    cname varchar2(50),
    cteacher varchar2(50)
);
create table sc(
    sno varchar2(50),
    cno varchar2(50),
    scgrade number
);

1 找出没选过“黎明”老师的所有学生姓名。

select sname,sno from s where sno not in(select sno from sc where cno in (select cno from c where cteacher='黎明'));

2 列出2门以上（含2门）不及格学生姓名及平均成绩。

select s.sname,avg(scgrade) from s,sc where s.sno=sc.sno and s.sno in(
select sno from sc
where scgrade<60 group by sno having count(*)>=2
) group by s.sno,s.sname;

3 既学过1号课程又学过2号课所有学生的姓名。

select sname from s where sno in(
select c1.sno from sc c1,sc c2 where c1.cno='c001' and c2.cno='c002' and c1.sno=c2.sno
)

4 查询每门功课成绩最好的前两名学生学号和姓名

select * from(select cno,sno,scgrade,rank() over(partition by cno order by scgrade desc)rank
 from sc) where rank<=2;

5 查询出只选修了一门课程的全部学生的学号和姓名

select sno,sname from s where sno in
(select sno from sc group by sno having count(*)=1);

6 查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名

select sno,sname from s where s.sno in
(select c1.sno from sc c1,sc c2 where c1.sno=c2.sno and c1.cno='c002' and c2.cno='c001'
and c1.scgrade < c2.scgrade);

select s.sno,s.sname from s
join sc a on s.sno=a.sno
join sc b on s.sno=b.sno
where a.cno='c002' and b.cno='c001' and a.scgrade < b.scgrade;

7 查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；

select sno,sname from s where sno in(
select sno from sc where cno in
(select cno from sc where sno='s001') and sno <> 's001'
group by sno having count(*)=(select count(*) from sc where sno='s001')
);

8 查询选修‘黎明’老师所授课程中成绩最好的学生姓名,课程编号和成绩

select sname,t.* from s inner join (select sno,cno,scgrade from sc where cno in
(select cno from c where cteacher='黎明') order by scgrade desc)t on s.sno=t.sno
where rownum <=1;

9 查询选修所有课程的学生学号和姓名。

select s.sno,s.sname,count(sc.cno) 
from s left join sc on s.sno=sc.sno
group by s.sno,s.sname
having count(sc.cno)=(select count(distinct cno)from c);

select sno,sname from s where sno in(select sno from sc where cno in(select cno from c)
group by sno having count(*)=(select count(*) from c)
);

10 查询所有学生都选修的课程编号和课程名称

select c.cno,c.cname,count(sc.sno) 
from c left join sc on c.cno=sc.cno
group by c.cno,c.cname
having count(sc.sno)=(select count(distinct sno)from s);

select cno,cname from c where cno in( 
select cno from sc group by cno having count(*)=(select count(*) from 
s));