⼤学mysql实验报告（四）附答案
实验报告（四）
专业：班级：学号：姓名：
实验名称：数据库的多表连接查询实验
报告内容：
1、⽤SELECT语句完成第183页实验3的23）27）28）31）的查询语句
23) 求选修了课程的学⽣⼈数
SELECT COUNT（*）选课⼈数FROM Enrollment
27)求选修每门课程的学⽣⼈数。
SELECT Cno AS '课程号', COUNT(Sno) AS '选修⼈数'
FROM Enrollment GROUP BY Cno
28)求每个学⽣的学号和各门课程的总成绩。
SELECT Sno '学号', Sum(grade) '总成绩' FROM Enrollment GROUP BY Sno
31)查询选修了C1课程的学⽣的学号和成绩，查询结果按成绩降序排列。
SELECT Sno, Grade FROM Enrollment WHERE Cno='C1' ORDER BY Grade DESC
32)查询全体学⽣信息，查询结果按所在系的系名升序排列，同⼀系的学⽣按年龄降序排列。SELECT * FROM Students ORDER BY Sdept, Sage DESC
2、⽤SELECT语句完成第183页实验4的2）3）4）6）的查询语句
2)查询每个学⽣的学号、姓名、选修的课程名、成绩。
SELECT Students.Sno,Sname, Cname,Grade
FROM Students,Courses,Enrollment
WHERE Students.Sno = Enrollment.Sno AND www.doczj/doc/4b14113722.html
o= www.doczj/doc/4b14113722.html
o
3)查询选修了C2且成绩⼤于90分的学⽣的学号、姓名、成绩。
SELECT Students.Sno,Sname, Grade
FROM Students, Enrollment
WHERE Students.Sno = Enrollment.Sno AND Cno='C2 ' AND Grade>90
4)求计算机系选修课程超过2门课的学⽣的学号、姓名、平均成绩, 并按平均成绩从⾼到低排序。
SELECT Students.Sno, Sname, A VG(Grade) 'Average'
FROM Students, Enrollment
WHERE Students.Sno= Enrollment.Sno AND Sdept='Computer'
GROUP BY Students.Sno,Sname
HA VING COUNT(*) >= 2
ORDER BY SUM(Grade) DESC
6)查询所有学⽣的选修情况，要求包括选修了课程的学⽣和没有修课的学⽣，显⽰他们的学号、姓名、课程号、成绩。
SELECT Students.Sno, Sname,Cno,Grade
FROM Students,Enrollment
WHERE Students.Sno*=Enrollment.Sno
上述左外连接可可以⽤右外连接等价表⽰：
SELECT Students.Sno, Sname,Cno,Grade
FROM Students,Enrollment
WHERE Enrollment.Sno =* Students.Sno
在SQLSERVER2000中，以上左外连接还可等价表⽰为：
SELECT Students.Sno, Sname,Cno,Grade
FROM Students LEFT JOIN Enrollment ON Students.Sno=Enrollment.Sno
-或者表⽰为：
SELECT Students.Sno, Sname,Cno,Grade
FROM Enrollment RIGHT JOIN Students ON Enrollment.Sno=Students.Sno
3、写出习题3第75页的17）18）19）⼩题的SQL语句
17）求借阅了图书的读者的总⼈数
SELECT COUNT(DISTINCT 读者号) AS’借阅过图书的读者总⼈数’ FROM 借阅
18）求机械⼯业出版社图书的平均价格、最⾼价、最低价。
SELECT A VG(单价) AS’平均价’,MAX(单价) AS’最⾼价’, MIN(单价) AS’最低价’FROM 图书WHERE 出版社=‘机械⼯业出版社’19）查询借阅图书本数超过2本的读者号、总本数。并按借阅本数值从⼤到⼩排序。SELECT 读者号,COUNT(图书号) AS '总本数'
FROM 借阅
sql统计每个系的学生人数
GROUP BY 读者号
HA VING COUNT(*) > 2
ORDER BY COUNT(图书号) DESC