SQL Server数据查询（综合篇）

建表略过，直接上题目代码

-- 1.查询每个学生及其选课情况（等值连接 和 右连接）
select * from student,sc where student.sno = sc.sno

select student.*,sc.* from sc right outer join student on (student.sno = sc.sno )
-- 2.查询每门课的间接选修课
select x.cno,y.cpno from course x, course y where x.cpno = y.cno
-- 3.查询既选修了2号课程又选修了3号课程的学生姓名、学号
select sname,sno from student where exists
(select * from sc x where x.cno = '2' and exists 
(select * from sc y where y.cno = '3' and sno = student.sno and sno = x.sno))
-- 4.查询和刘晨同一年龄的学生
select * from student where sage in (select sage from student where sname = '刘晨')
-- 5.查询选修了课程名为“数据库”的学生姓名和年龄（相关子查询 和 不相关子查询）
select sname,sage from student where sno in 
(select sno from sc where cno in 
(select cno from course where cname = '数据库'))

select sname,sage from student where exists 
(select * from sc where exists 
(select * from course where student.sno = sc.sno and sc.cno = cno and cname = '数据库' ))
-- 6.查询其他系中比IS系任一学生年龄小的学生名单（两种解法）
select sname from student where sdept != 'is' and sage < any 
(select sage from student where sdept = 'is')

select sname,sage from student where sdept != 'is' and sage < 
(select max(sage) from student where sdept = 'is')
-- 7.查询其他系中比IS系所有学生年龄都小的学生名单（两种解法）
select sname from student where sdept != 'is' and sage < all 
(select sage from student where sdept = 'is')

select sname from student where sdept != 'is' and sage < 
(select min(sage) from student where sdept = 'is')
-- 8.查询选修了全部课程的学生姓名（相关子查询 和 不相关子查询）
select sname from student where not exists 
(select * from course where not exists 
(select * from sc where sno = student.sno and cno = course.cno ))

select sname from student where sno in 
(select sno from sc group by sno having count(*) = (select count(*) from course))
-- 9.查询计算机系学生及其性别是男的学生
select sname,sage from student where sdept = 'cs' and ssex = '男'
-- 10.查询张立同学不学的课程的课程号
select cno from course where not exists 
(select * from sc where course.cno = sc.cno and
sno = (select sno from student where sname = '张立'))
-- 11.查询选修了3号课程的学生平均年龄
select avg(sage)选修了3号课程的学生平均年龄 from student,sc 
where student.sno = sc.sno and cno = '3'
-- 12.求每门课程学生的平均成绩
select cno,avg(grade)平均成绩 from sc group by cno 
-- 13.统计每门课程的学生选修人数（超过1人的才统计）。
-- 要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列
select cno,count(distinct sno)选修人数 
from sc group by cno having count(cno) > 1 order by count(cno) desc,cno asc