sql server多表连查 sql server多表联合查询

一、实验目的

1、掌握Select语句的基本语法；
2、掌握连接查询的表示和使用方法；
3、掌握子查询的表示和使用方法。

二、实验准备

1、理解Select语句的基本语法格式和执行方法；
2、理解连接查询的表示；
3、理解子查询的表示方法。

三、实验内容

基于Student_DB 数据库中的三个表Student，Course，SC，进行如下的查询：
（给出查询语句及结果截图）
（1） 查询每个学生及其选修课情况。
（2） 查询每个学生的学号、姓名、选修的课程及成绩。
（3） 查询每个学生的平均成绩，包括学号和平均成绩两列，结果按学号升序排。
（4） 查询选修了2 号课程且成绩在80 分以上的学生姓名，年龄。
（5） 查询选修了课程名为“操作系统”的学生学号和姓名。
（6） 查询与“王敏”在同一个系学习的学生。（用IN 嵌套实现）
（7） 查询各班各门课的平均成绩，包括学号、课程号和平均成绩三列，结果先按学号升序排，学号一样的再按课程号升序排。
（8） 查询其它系中比信息系某一学生年龄大（age>20）的学生姓名和年龄。
（9） 查询选修了课程1 或者选修了课程2 的学生。
（10） 求没有选修2 号课程的学生姓名。
（11） 查询选修课程1 的学生集合与选修课程2 的学生集合的交集。
（12） 查询信息系的学生与年龄不大于19 岁的学生的差集。

四、实验代码及运行结果截图

--（1） 查询每个学生及其选修课情况。
SELECT STUDENT.*,SC.*
	FROM STUDENT,SC
	WHERE STUDENT.SNO = SC.SNO;

--（2） 查询每个学生的学号、姓名、选修的课程及成绩。
SELECT STUDENT.SNO,SNAME,CNAME,GRADE
	FROM STUDENT,SC,COURSE
	WHERE STUDENT.SNO = SC.SNO
	AND SC.CNO = COURSE.CNO;

--（3） 查询每个学生的平均成绩，包括学号和平均成绩两列，结果按学号升序排。
SELECT STUDENT.SNO,AVG(GRADE) AS 'AVG_GRADE'
	FROM STUDENT,SC
	WHERE STUDENT.SNO = SC.SNO
	GROUP BY STUDENT.SNO
	ORDER BY STUDENT.SNO

--（4） 查询选修了2 号课程且成绩在80 分以上的学生姓名，年龄。
SELECT DISTINCT SNAME,SAGE
	FROM STUDENT,SC
	WHERE STUDENT.SNO = SC.SNO
	AND	CNO = 2
	AND GRADE>80

--（5） 查询选修了课程名为“操作系统”的学生学号和姓名。
--INSERT INTO SC VALUES('201215122','4','90')
SELECT STUDENT.SNO,SNAME
	FROM STUDENT,SC,COURSE
	WHERE STUDENT.SNO = SC.SNO
	AND COURSE.CNO = SC.CNO
	AND COURSE.CNAME = '操作系统';

--（6） 查询与“王敏”在同一个系学习的学生。（用IN 嵌套实现）
SELECT SNO,SNAME,SDEPT
	FROM STUDENT
	WHERE SDEPT IN 
	(
	SELECT SDEPT
	FROM STUDENT
	WHERE SNAME = '王敏'
	)

--（7） 查询各门课的平均成绩，包括学号、课程号和平均成绩三列，结果先按学号升
--序排，学号一样的再按课程号升序排。
--CASE1
SELECT SC.SNO,SC.CNO,AVG(GRADE) AS AVERAGE
	FROM STUDENT,SC
	WHERE STUDENT.SNO = SC.SNO
	GROUP BY SC.SNO,SC.CNO
	ORDER BY SC.SNO,SC.CNO

--CASE2
SELECT SC.CNO,AVG(GRADE) AS 'AVG_GRADE'
	FROM SC
	GROUP BY SC.CNO

--（8） 查询其它系中比信息系某一学生年龄大（age>20）的学生姓名和年龄。
SELECT SNAME,SAGE
	FROM STUDENT
	WHERE SDEPT <> 'IS' AND SAGE>
	(
		SELECT MIN(SAGE)
		FROM STUDENT
		WHERE SDEPT='IS'
	)

--（9） 查询选修了课程1 或者选修了课程2 的学生。
SELECT DISTINCT SNO 
	FROM SC 
	WHERE SC.CNO = '1' OR SC.CNO = '2'

--（10） 求没有选修2 号课程的学生姓名。
SELECT DISTINCT SNAME
	FROM STUDENT
	WHERE STUDENT.SNO NOT IN
	(
		SELECT DISTINCT SC.SNO
			FROM SC
			WHERE SC.CNO = '2'
	)

--（11） 查询选修课程1 的学生集合与选修课程2 的学生集合的交集。
SELECT SC.SNO 
	FROM SC 
	WHERE SC.CNO ='1'
INTERSECT
SELECT SC.SNO FROM SC
WHERE SC.CNO = '2'

--（12） 查询年龄不大于19 岁的学生与信息系的学生的差集。
SELECT *
        FROM Student
        WHERE Sage<=19
EXCEPT
SELECT *
        FROM Student
        WHERE Sdept= 'IS'