实验要求

  • 查询审批通过人数最多的课程名称和教师姓名;(连接、排序)
  • 查询只选了课程《C++编程》的学生姓名;(子查询)
  • 查询选修了课程3的学生学号、姓名、身份证号;(多表)
  • 对于请过假的学生,查询其学号,姓名和请假次数;(自然连接)
  • 查询所有学生的学号,姓名和请假次数(无论该学生是否请过假);(左连接)
  • 查询所有学生对所有课程的选课情况,输出学号与课程号(无论该学生是否选课,无论该课程是否被选);(全外连接)
  • 总结自然连接、左连接、右连接查询在产生的结果上面有什么区别;
  • 查询选课人数最多和第二多的课程名称和任课教师姓名;(函数、连接)
  • 查询选修了全部课程的学生姓名;(子查询)
  • 查询课程3获得最高分的学生的姓名和分数;(多表)
  • 对lesson表进行查询,字段包括课程号、课程名称、学分、先修课程号、先修课程名称、先修课程学分;(自连接)
  • 查找每门课的最大applytime请假单
  • 查找每个月每门课的最大applytime请假单
  • 查看没有选修‘C语言’课程的学生
  • 查看成绩都不高于80分的学生
  • 查看每个学生的最高分

实验过程

Step1:查询审批通过人数最多的课程名称和教师姓名

将lesson,teacher,leave_apply三张表通过等值连接后进行分组,降序排列后取第一个元组,也就是审批通过人数最多的课程名称和教师姓名

SELECT lessonName, teacherName FROM lesson le, teacher te, leave_apply la
	WHERE la.state = '审批已通过' AND la.teacherNum = te.teacherNum AND la.lessonNum = le.lessonNum
	GROUP BY lessonName, teacherName
	ORDER BY COUNT(1) desc

方法二:直接使用top

SELECT TOP 1 lesson.lessonName, teacher.teacherName, COUNT(*) number
	FROM leave_apply, lesson, teacher	
	WHERE leave_apply.lessonNum = lesson.lessonNum and leave_apply.teacherNum = teacher.teacherNum and state = '审批已通过'
	GROUP BY lesson.lessonName, teacher.teacherName
	ORDER BY number desc

Step2: 查询只选了课程《C++编程》的学生姓名

首先选出只选修了一门课的学生,然后判定选的课程是否是C++编程

SELECT studentName FROM student, lesson
	WHERE studentNum IN (
		SELECT sc2.studentNum FROM stu_choose_lesson sc2
		GROUP BY studentNum HAVING COUNT(lessonNum) = 1) 
			AND lessonNum IN (SELECT lessonNum FROM lesson WHERE lessonName = 'C++编程')

查询结果为空,说明不存在只选修了C++编程的学生

Step3:查询选修了课程3的学生学号、姓名、身份证号

直接使用等值连接

SELECT st.studentNum, st.studentName, st.IDcard FROM student st, stu_choose_lesson sc
	WHERE sc.lessonNum = 3 AND st.studentNum = sc.studentNum

Step4:对于请过假的学生,查询其学号,姓名和请假次数

先对两张表进行内连接,连接之后进行分组,去除冗余元组,并通过聚集函数count来计算请假次数

SELECT st.studentNum, st.studentName, COUNT(la.studentNum) AS 请假次数  
    FROM student st INNER JOIN leave_apply la ON st.studentNum = la.studentNum  
    GROUP BY st.studentNum, st.studentName  

Step5:查询所有学生的学号,姓名和请假次数(无论该学生是否请过假)

直接左连接

SELECT st.studentNum, st.studentName, COUNT(la.studentNum) AS 请假次数
	FROM student st LEFT JOIN leave_apply la ON st.studentNum = la.studentNum
	GROUP BY st.studentNum, st.studentName

Step6:查询所有学生对所有课程的选课情况,输出学号与课程号(无论该学生是否选课,无论该课程是否被选)

在学生表和选课表中使用全外连接

SELECT st.studentNum, sc.lessonNum
	FROM student st FULL JOIN stu_choose_lesson sc
		ON st.studentNum = sc.studentNum

Step7:总结自然连接、左连接、右连接查询在产生的结果上面有什么区别

自然连接中:如果有共同属性的值有不匹配的情况,则此元组会被忽略。

左连接中:如果共同属性的值有不匹配的情况,会将左表的元组保留,右表对应位置设置为空。

右连接中:如果共同属性的值有不匹配的情况,会将右表的元组保留,左表对应位置设置为空。

Step8:查询选课人数最多和第二多的课程名称和任课教师姓名

首先将三张表进行连接,连接后对课程名称和任课教师进行分组,按出现的次数进行降序排列,最后在选择的时候,使用top 2语句,表示查询选课次数前两名的课程名称和教师姓名

SELECT top 2 lessonName, teacherName, COUNT(*) AS 选课次数
	FROM stu_choose_lesson sc, teacher te, lesson le
		WHERE sc.lessonNum = le.lessonNum AND le.teacherNum = te.teacherNum
			GROUP BY lessonName, teacherName
			ORDER BY COUNT(1) desc

Step9:查询选修了全部课程的学生姓名

使用IN子查询,首先我们需要求出一共有多少门课程,然后在选课表中按学号给学生分组,在每一组中,如每一组中的数量等于总课程数,则说明该学生选修了全部的课程

SELECT studentName FROM student st
	WHERE studentNum IN (
		SELECT studentNum FROM stu_choose_lesson sc 
			GROUP BY studentNum 
			HAVING count(*) = (SELECT COUNT(*) FROM lesson))

Step10:查询课程3获得最高分的学生的姓名和分数

SELECT st.studentName, sc.score 
	FROM stu_choose_lesson sc, student st
		WHERE sc.studentNum = st.studentNum 
			AND sc.lessonNum = 3 
			AND  sc.score = (SELECT MAX(score) 
				FROM stu_choose_lesson sc2 WHERE sc2.lessonNum = 3) 

Step11: 对lesson表进行查询,字段包括课程号、课程名称、学分、先修课程号、先修课程名称、先修课程学分

为了调试代码,我们将之前的实验:删除的指定教工号(teacherNum)的教师的所有相关记录重新加回数据库

INSERT INTO lesson VALUES (2,'C++编程',2013, '上', 3,  2,  1)  
INSERT INTO teacher VALUES (2, '许明', '副教授', '男')  

然后使用左外连接将两张lesson表进行连接:

SELECT le1.lessonNum, le1.lessonName, le1.credit, le1.beforeLessonNum, le2.lessonName, le2.credit
	FROM lesson le1 LEFT JOIN lesson le2 
		ON le1.beforeLessonNum = le2.lessonNum

Step12:查找每门课的最大applytime请假单

分组后求最大值

SELECT lessonNum, MAX(applyTime) AS Max_applyTime
	FROM leave_apply
	GROUP BY lessonNum

Step13:查找每个月每门课的最大applytime请假单

SELECT  MONTH(MAX(applyTime)) AS Max_Month, lessonNum, MAX(applyTime) AS Max_applyTime
	FROM leave_apply 
	GROUP BY lessonNum, MONTH(applyTime)

Step14:查看没有选修‘C语言’课程的学生

在所有学生中,使用EXCEPT除去选修过C语言的学生,就得到没有选修C语言课程的学生

SELECT studentName FROM student WHERE studentNum IN (
	SELECT studentNum FROM student
	EXCEPT
	SELECT studentNum FROM stu_choose_lesson sc
		WHERE studentNum = sc.studentNum AND lessonNum = 1
)

Step15:查看成绩都不高于80分的学生

只要有一科高于80分,就排除掉

SELECT studentName FROM student
	WHERE studentNum NOT IN (
		SELECT studentNum FROM stu_choose_lesson 
			WHERE score > 80)

Step16:查看每个学生的最高分

我们先在stu_choose_lesson中选出每个学号对应的最高分,并将其作为一张表与student表进行连接后选择我们需要的属性

SELECT studentName, highestScore FROM student, 
	(SELECT studentNum, MAX(score) AS highestScore FROM stu_choose_lesson sc
		GROUP BY sc.studentNum) AS sc2
	WHERE student.studentNum = sc2.studentNum

实验五Over


立志做一名攻城狮