SQL Server数据查询（提高篇）

 建表步骤省略了，直接上题目和代码

--（二）实验内容(提高篇)
-- 1.查询全体学生的学号和姓名
select sno,sname from student 
-- 2.查询全体学生的详细记录
select * from student,sc,course where
student.sno = sc.sno and course.cno = sc.cno 
-- 3.查询CS学院的学生姓名、年龄、系别
select sname,sage ,sdept from student where sdept = 'cs'
-- 4.查询所有选修过课程的学生学号
select distinct sno from sc 
-- 5.查询考试不及格的学生学号
select distinct sno from sc where grade < 60
-- 6.查询不是CS学院的学生姓名、年龄、系别 （三种方法实现）
select sname,sage,sdept from student where sdept != 'cs'

select sname,sage,sdept from student x where exists 
(select * from student y where x.sno = y.sno and y.sdept != 'cs' )

select sname,sage,sdept from student x where not exists
(select * from student y where x.sno = y.sno and sdept = 'cs')
-- 7.查询年龄在18-20岁的学生学号、姓名、系别、年龄
select sno,sname,sdept,sage from student where sage  between 18 and 20
-- 8.查询姓刘的学生情况
select student.sno,sname,ssex,sage,sdept,cno,grade from
student left outer join sc on (student.sno = sc.sno) where sname like '刘%'
-- 9.查询姓刘或姓李的学生情况 （注意不同的写法）
select student.sno,sname,ssex,sage,sdept,cno,grade from
student left outer join sc on (student.sno = sc.sno) 
where sname like '刘%' or sname like '李%'
-- 10.查询姓刘且名字为两个字的学生情况
select student.sno,sname,ssex,sage,sdept,cno,grade from
student left outer join sc on (student.sno = sc.sno) 
where sname like '刘_' 
-- 11.查询1995年以后出生的学生姓名
select sname from student where sage < DATEPART(yy,getdate()) - 1995 
-- 12.利用内部函数year()查找CS学院学生的出生年份
select sname,sage, datepart(yy,getdate()) - sage 出生年份 from student 
-- 13.查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。
select * from student order by sdept , sage desc
-- 14.查询学生总人数
select count(sno)学生总人数 from student 
-- 15.查询选修了课程的学生人数 （两种方法实现）
select count (distinct sno)选修课程的学生人数 from sc 

select count(sno)选修课程的学生人数 from student where exists 
(select * from sc where student.sno = sc.sno )
-- 16.查询选修了2号课程的学生总人数和平均成绩
select count(sno)选择2号课程的学生总人数,avg(grade)平均成绩 from sc where cno = '2'
-- 17.查询选修2号课程学生的最好成绩
select max(grade)选修了2号课程的学生最好成绩 from sc where cno = '2' 
-- 18.查询每个系的系名及学生人数
select sdept, count (sno ) from student group by sdept 
-- 19.查找每门课的选修人数及平均成绩
select cno,count (cno)选修人数,avg (grade)平均成绩 from sc group by cno 
-- 20.查找没有选修课的课程情况
select * from course where cpno is null