有以下三个关系的学生选课数据库
学生（学号，姓名，性别，所在系）
课程（课程号，课程名，学分，开课系）
选课（学号，课程号，成绩）
创建上述关系及部分数据的的SQL语句见文件sc_db.rar(create_db.txt和insert_db.txt，见SQL练习1，在pgsql中执行上述命令前可能需要执行\encoding gbk命令)。
完成下列查询：
- 查询所有没有被选过的课程；
select cno
from course
where not exists (
select *
from sc
where course.cno=sc.cno );
2.查询选了“数据库原理”课程的所有学生的学号和姓名；
select sno,sname
from student
where sno in (
select sno
from sc
where exists (
select cno
from course
where sc.cno=course.cno and cname='数据库原理')
);
- 查询各选课学生的学号，姓名，选课门数和平均成绩；
select student.sno,sname,count(cno),avg(grade)
from student,sc
where student.sno=sc.sno
group by student.sno;
- 查询选课门数在2门以上（含2门）的学生中平均成绩最高的学生的学号；
select sno,avg(grade) as avg_grade
from sc
group by sno having avg(grade)>=all(
select avg(grade)
from sc
group by sno);
- 查询选修2号课程的成绩最高的学生的学号和姓名；
select sno,sname
from student
where sno=(
select sno
from sc
where cno='2' and grade =
(select max(grade)
from sc
where cno='2')
);
6.查询选课总学分最多的学生的姓名及其选课总学分；
select sname,sum(ccredit) SumCredit
from student
inner join sc on sc.sno=student.sno
inner join course on course.cno=sc.cno
group by student.sno having sum(ccredit)>=all(
select sum(ccredit)
from sc
inner join course on sc.cno=course.cno
group by sno
);
7.查询计算机系的学生姓名及其选课门数，按选课门数降序排列；
select sname,count(cno) as cnt_cno
from student
left join sc on sc.sno=student.sno
group by student.sno
order by cnt_cno desc;
8.查询选了“王五”所选所有课程的学生的学号和姓名；
select sno,sname
from student
where not exists(
(select cno
from sc
inner join student on sc.sno=student.sno
where student.sname='王五')
except
(select cno
from sc
where sc.sno=student.sno)
);
9.查询选了其所在系开设的所有课程的学生姓名。
--alter table course add cdept char(8);
--update course set cdept='CS' where cno in('1','3');
--update course set cdept='MA' where cno in('2');
--update course set cdept='IS' where cno in('6');
--update course set cdept=NULL where cno in('1');
select *
from student
where not exists(
(select sc.cno
from sc
right join course on sc.cno=course.cno
where student.sdept=course.cdept
)
except
(select sc.cno
from sc
where student.sno=sc.sno
)
);
