数据库上级作业3

--1. 列出所有不姓刘的所有学生；
SELECT Sname
FROM T_Student
WHERE Sname NOT LIKE '刘%'


--2. 列出姓“沈”且全名为3个汉字的学生；
SELECT Sname
FROM T_Student
WHERE Sname LIKE '沈__'


--3. 显示在1985年以后出生的学生的基本信息；
SELECT Sno,Sname,Ssex,2015-Sage 'Sbirth',Sdept
FROM T_Student


--4. 查询出课程名含有“数据”字串的所有课程基本信息；
SELECT Cno,Cname,Cpno,Ccredit
FROM T_Course
WHERE Cname LIKE '%数据%'


--5. 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM T_Student
WHERE Sno LIKE '_______[12349][12349]'


--6. 列出选修了‘1’课程的学生，按成绩的降序排列；
SELECT Sno
FROM T_SC
WHERE Cno = '1'
ORDER BY Grade DESC


--7. 列出同时选修“1”号课程和“2”号课程的所有学生的学号；
SELECT Sno
FROM T_SC
WHERE Cno = '1' OR Cno = '2'


--8. 列出课程表中全部信息，按先修课的升序排列；
SELECT *
FROM T_Course
ORDER BY Cpno ASC


--9. 列出年龄超过平均值的所有学生名单，按年龄的降序显示；
SELECT Sname,Sage
FROM T_student
WHERE Sage > (SELECT AVG(Sage) FROM T_Student)
ORDER BY Sage DESC


--10. 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；
SELECT Sno'学号',Sname'姓名',Ssex'性别',2015-Sage'出生年月',Sdept'院系'
FROM T_Student


--11. 按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，
--其中院系按照以下规定显示：院系为CS显示为计算机系，院系为IS显示为信息系，
--院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明；
SELECT Sno,Sname,Ssex,Sage,Sdept =
CASE
WHEN Sdept = 'CS' THEN '计算机系'
WHEN Sdept = 'IS' THEN '信息系'
WHEN Sdept = 'MA' THEN '数学系'
WHEN Sdept = 'CM' THEN '中医系'
WHEN Sdept = 'WM' THEN '西医系'
WHEN Sdept NOT IN ('CS','IS','MA','CM','WM') THEN '不明院系'
end
FROM T_Student


--12. 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；
SELECT Sno,Cno,Grade
FROM T_SC
WHERE Grade BETWEEN 70 AND 80
ORDER BY Cno,Grade DESC

--13. 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；
SELECT COUNT(*)'学生总人数',AVG(Sage)'平均年龄'
FROM T_Student


--14. 显示选修的课程数大于3的各个学生的选修课程数；
SELECT Sno ,COUNT(*)'选修课程数'
FROM T_SC
GROUP BY Sno
HAVING COUNT(*)>3


--15. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；
SELECT Cno,COUNT(distinct sno)'总人数',MAX(Grade)'最高成绩',MIN(Grade)'最低成绩',AVG(Grade)'平均成绩'
FROM T_SC
GROUP BY Cno
ORDER BY Cno  DESC


--16. 显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩；
SELECT Sno'学号',AVG(Grade)'平均成绩'
FROM T_SC
GROUP BY Sno
HAVING (SELECT AVG(Grade) FROM T_SC WHERE Sno = '200515001')<AVG(Grade)


--17. 显示选修各个课程的及格的人数、及格比率；
SELECT '选修各个课程的及格的人数'=COUNT(CASE WHEN Grade>=60 then 1 end),
  '及格比率'=CAST(100*COUNT(CASE WHEN Grade>=60 then 1 end)/COUNT(Grade) AS FLOAT)
FROM T_SC
GROUP BY Cno


--18. 显示选修课程数最多的学号及选修课程数最少的学号；
SELECT Sno '选修课程数最多的学号'
FROM T_SC
GROUP BY Sno
HAVING COUNT(Sno)>= ALL (SELECT COUNT(Sno) FROM T_SC GROUP BY Sno)
SELECT Sno '选修课程数最少的学号'
FROM T_SC
GROUP BY Sno
HAVING COUNT(Sno)<= ALL (SELECT COUNT(Sno) FROM T_SC GROUP BY Sno)


--19. 显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；
SELECT Sdept,'男生人数'=COUNT(CASE WHEN Ssex='男' then 1 end),'女生人数'=COUNT(CASE WHEN Ssex='女' then 1 end)
FROM T_Student
GROUP BY(Sdept)


--20. 列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；
SELECT Sno'学生的学号',AVG(Grade)'平均成绩'
FROM T_SC
WHERE Grade <60
GROUP BY Sno
Having COUNT(Sno)>=2