第二周

(一)以student数据库中数据为基础，请使用T-SQL 语句实现以下操作：

–1. 列出不姓刘的所有学生；

SELECT *
FROM Student
WHERE Sname NOT LIKE '刘%';

–2. 列出年龄超过平均值的所有学生名单，按年龄的降序显示；

SELECT *
FROM Student
WHERE Sage>(SELECT AVG(Sage)
			FROM Student)
ORDER BY Sage DESC;

–3. 显示在2001年以后出生的学生的基本信息；

SELECT *
FROM Student
WHERE 2021-Sage>2001;

–4. 查询出课程名含有“数据”字串的所有课程基本信息；

SELECT *
FROM Course
WHERE Cname LIKE '%数据%';

–5. 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；

SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student
WHERE Sno LIKE '_______[1-4,9][1-4,9]%';

–6. 列出选修了‘1’课程的学生，按成绩的降序排列；

SELECT *
FROM Student,SC
WHERE Student.Sno=SC.Sno AND SC.Cno='1'
ORDER BY SC.Grade DESC;

–7. 查询选修课程超过3门的学生的学号和选课门数，以及平均成绩

SELECT Student.Sno,COUNT(*) AS '选课门数',AVG(SC.Grade) AS '平均成绩'
FROM Student,SC
WHERE Student.Sno=SC.Sno
GROUP BY Student.Sno
HAVING COUNT(*)>3;

–8. 列出课程表中全部信息，按先修课的升序排列；

SELECT *
FROM Course
ORDER BY Cpno ASC;

–9. 查询平均成绩超过80分的课程的课程号和平均成绩；

SELECT Cno,AVG(Grade) AS '平均成绩'
FROM SC
GROUP BY Cno
HAVING AVG(Grade)>80;

–//查出来好像是一条也没有?

–10. 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；

SELECT Sno AS '学号',Sname AS '姓名',Ssex AS '性别',2021-Sage AS '出生年份',Sdept AS '院系'
FROM Student
ORDER BY 2021-Sage ASC;

–11. 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；

SELECT Sno,Cno,Grade
FROM SC
WHERE Grade BETWEEN 70 AND 80
ORDER BY CONVERT(INT,Cno) DESC,Grade DESC;

–12. 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；

SELECT COUNT(*) AS '学生总人数',AVG(Sage) AS '平均年龄'
FROM Student;

–13. 查询每个专业所有姓张的人数；

SELECT Sdept,COUNT(*) AS '姓张的人数'
FROM Student
WHERE Sname LIKE '张%'
GROUP BY Sdept;

–14. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；

SELECT Cno,COUNT(*) AS '选修总人数',MAX(Grade) AS '最高成绩',MIN(Grade) AS '最低成绩',AVG(Grade) AS '平均成绩'
FROM SC
GROUP BY Cno
ORDER BY CONVERT(INT,Cno) DESC;

–15. 显示选修课程数最多的学号；

SELECT TOP 1 WITH TIES Sno
FROM SC
GROUP BY Sno
ORDER BY COUNT(*) DESC;

–16. 列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

SELECT Sno,AVG(Grade) AS '平均成绩'
FROM SC
WHERE Grade<60
GROUP BY Sno
HAVING COUNT(*)>=2;

–17. 查询选修‘1’号课程成绩排名前五的学生的学号和成绩

SELECT TOP 5 WITH TIES Sno,Grade 
FROM SC
WHERE Cno='1'
ORDER BY Grade DESC;

–18.查询每个学生的最低分和选课门数

SELECT Sno,MIN(Grade) AS '最低分',COUNT(*) AS '选课门数'
FROM SC
GROUP BY Sno;

–19. 查询各个专业男生、女生的人数

SELECT Sdept,Ssex,COUNT(*) AS '人数'
FROM Student
GROUP BY Ssex,Sdept;

–20. 查询各种姓氏的人数（假设没有复姓）

SELECT LEFT(Sname,1) AS '姓氏',COUNT(*) AS '人数'
FROM Student
GROUP BY LEFT(Sname,1);

–（二）以SPJ数据库中数据为基础，请使用T-SQL 语句实现以下操作：

–1． 求供应工程J1零件的供应商号码SNO

SELECT SNO
FROM SPJ
WHERE JNO='J1';

–2． 求每个工程使用不同供应商的零件的个数

SELECT JNO,SNO,SUM(QTY) AS '零件数'
FROM SPJ
GROUP BY SNO,JNO;

–3． 求供应工程使用零件P3数量超过200的工程号JNO

SELECT JNO
FROM SPJ 
WHERE PNO='P3' AND QTY>200;

–4． 求颜色为红色和蓝色的零件的零件号和名称

SELECT PNO,PNAME
FROM P
WHERE COLOR LIKE '[红蓝]';

–5． 求使用零件数量在200-400之间的工程号

SELECT JNO
FROM SPJ
GROUP BY JNO
HAVING SUM(QTY) BETWEEN 200 AND 400;

–6． 查询每种零件的零件号，以及使用该零件的工程数。

SELECT P.PNO,COUNT(*) AS '工程数'
FROM P,SPJ
WHERE P.PNO=SPJ.PNO
GROUP BY P.PNO;