数据库上机实验-SELECT复杂查询_查询工资最高的教师的编号和开设的课程号提示:请使用select语句作答。-CSDN博客

列名	含义	数据类型	是否可空	主外键
SNo	学生学号	char(10)	N	PK
SN	学生姓名	varchar(20)	N
Sex	性别	char(2)	Y
Age	年龄	int	Y
Dept	所在系别	varchar(20)	Y

C表：

列名	含义	数据类型	是否可空	主外键
CNo	课程代码	char(10)	N	PK
CN	课程名称	varchar(10)	N
CT	课时	int	Y

T表：

列名	含义	数据类型	是否可空	主外键
TNo	教师号	char(10)	N	PK
TN	教师姓名	varchar(20)	N
Sex	性别	char(2)	Y
Age	年龄	int	Y
Prof	职称	char(10)	Y
Sal	工资	int	Y
Comm	岗位津贴	int	Y
Dept	系别	varchar(20)	Y

SC表：

列名	含义	数据类型	是否可空	主外键
SNo	学号	char(10)	N	PK，FK
CNo	课程代码	char(10)	N	PK，FK
Score	成绩	decimal(5，2)	Y

TC表：

列名	含义	数据类型	是否可空	主外键
TNo	教师号	char(10)	N	Pk，FK
CNo	课程代码	char(10)	N	PK，FK

二、基于前面创建的表，完成如下操作：

#(1)查询“王平”老师所讲授的课程，要求列出教师姓名、系别、课程号和课程名。（用两种方法：① where+连接条件; ② 内连接inner join...on…）

-- ① where+连接条件

SELECT TN,Dept,c.CNo,CN FROM t,c ,tc WHERE t.TNo = tc.TNo AND c.CNo = tc.CNo AND t.TN = '王平';

-- ② 内连接inner join...on…

SELECT TN,Dept,c.CNo,CN 
FROM t 
INNER JOIN tc ON t.TNo = tc.TNo 
INNER JOIN c ON c.CNo = tc.CNo WHERE t.TN = '王平';

#(2)查询所有选课学生的学号、姓名、选课名称及成绩。（未选课学生信息不显示，用两种方法：① where+连接条件; ② 内连接inner join...on…）

-- ① where+连接条件

SELECT s.SNo,s.SN,c.CN,sc.Score FROM s, c, sc WHERE s.SNo = sc.SNo AND c.CNo = sc.CNo AND Score IS NOT NULL;

-- ② 内连接inner join...on…

SELECT s.SNo,s.SN,c.CN,sc.Score 
FROM s 
INNER JOIN sc ON s.SNo = sc.SNo
INNER JOIN c ON c.CNo = sc.CNo WHERE Score IS NOT NULL;

#(3)查询所有学生的学号、姓名、选课名称、课程号及成绩。（未选课学生选课信息显示为空，使用外连接）

SELECT s.SNo,s.SN,c.CN,c.CNo,sc.Score 
FROM s
LEFT JOIN sc ON s.SNo = sc.SNo
LEFT JOIN c ON c.CNo = sc.CNo;

#(4)查询每门课程的课程号、课程名、授课教师姓名及所在系别，并统计课程选课人数。(所有课程都有学生来选，所以分别用内连接和外连接2种方法来做)

-- ①用内连接

SELECT c.CNo,c.CN,t.TN,t.Dept, COUNT(sc.SNo) AS '选课人数'
FROM c 
INNER JOIN tc ON c.CNo = tc.CNo
INNER JOIN t ON tc.TNo = t.TNo
INNER JOIN sc ON  sc.CNo = c.CNo
GROUP BY c.CNo,c.CN, t.TN, t.Dept;

-- ② 用外连接

SELECT c.CNo,c.CN,t.TN,t.Dept, COUNT(sc.SNo) AS '选课人数'
FROM c
LEFT JOIN tc ON c.CNo = tc.CNo
LEFT JOIN t ON t.TNo = tc.TNo
LEFT JOIN sc ON sc.CNo = c.CNo
GROUP BY c.CNo,c.CN, t.TN, t.Dept;

#(5)查询所有比“王平”老师年龄大的教师姓名、职称、系别，并显示王平老师姓名及年龄。

SELECT T1.TN,T1.Prof,T1.Dept,T2.TN,T2.Age 
FROM t AS T1 ,t AS T2
WHERE T1.Age >T2.Age AND T2.TN = '王平';

#(6)查询每个学生选修课程超过他所选修所有课程平均成绩的课程，最后显示的字段为学号、课程号、课程名。

SELECT sc.SNo, SC.CNo, C.CN
FROM SC
INNER JOIN C ON SC.CNo = C.CNo
WHERE SC.Score > (
  SELECT AVG(SC2.Score)
  FROM SC AS SC2
  WHERE SC2.SNo = SC.SNo
);

#(7)查询每个学生选修课程超过他选修课程平均成绩的课程，并按照学号、课程号排序，最终显示字段为学号、姓名、课程号、课程名、成绩和平均成绩。

SELECT SC.Sno,S.Sn, SC.CNo, C.CN, SC.Score, T.Score
FROM SC
         RIGHT JOIN S ON SC.Sno = S.Sno
         LEFT JOIN
     (
         SELECT avg(SC.Score) Score, SC.Sno
         FROM SC
                  RIGHT JOIN S ON SC.Sno = S.Sno
         GROUP BY SC.Sno
     ) T ON SC.Score > T.Score AND SC.Sno = T.Sno
         RIGHT JOIN C ON C.Cno = SC.CNo
WHERE T.Score IS NOT NULL
GROUP BY SC.SNo, SC.CNo
ORDER BY SC.SNo, SC.CNo;

#(8)查询讲授课程号为 C5的教师姓名。（用三种方法：① where+连接条件; ② 内连接inner join...on… ③相关子查询）

-- ① where+连接条件;

SELECT TN FROM t ,tc WHERE t.TNo = tc.TNo AND tc.CNo = 'C5';

-- ② 内连接inner join...on…

SELECT TN FROM t INNER JOIN tc ON t.TNo = tc.TNo WHERE tc.CNo = 'C5';

-- ③相关子查询

SELECT TN FROM t WHERE TNo = (SELECT TNo FROM tc WHERE CNo = 'C5' LIMIT 1);

#(9)查询其他系中比计算机系某一教师工资高的教师的姓名和工资。（相关子查询）

SELECT TN, Sal
FROM T
WHERE Dept <> '计算机系' AND Sal > (
  SELECT Sal
  FROM T
  WHERE TNo = 'T1'
);

#(10)查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。（相关子查询）

SELECT TN ,Sal FROM t WHERE Sal>ANY (SELECT Sal FROM t WHERE Dept = '计算机') AND Dept NOT LIKE '计算机';

#(11)查询不讲授课程号为C5的教师姓名。（相关子查询）

SELECT TN FROM t WHERE TNo NOT in (SELECT TNo FROM tc WHERE CNo = 'C5');

#(12)查询其他系中比计算机系某一个学生年龄小的学生姓名、年龄和系别。（相关子查询）

SELECT SN,Age,Dept FROM s WHERE Age <  ANY (SELECT age FROM s WHERE Dept LIKE ('计算机')) AND Dept NOT LIKE('计算机');

#(13)查询其他系中比计算机系所有学生年龄都小的学生姓名及年龄。（相关子查询）

SELECT SN,Age FROM s WHERE Age<ALL(SELECT Age FROM S WHERE Dept LIKE ('计算机')) AND Dept NOT LIKE ('计算机');

总结

本次实验主要掌握使用 SELECT 命令进行连接查询和子查询。在 SQL 中，连接查询是指通过将多个表连接起来，从中获取所需数据的过程；而子查询是指在 SQL 语句中嵌套查询，将一个查询的结果作为另一个查询的条件进行查询的过程。在本次实验中，我们学习了以下内容：

连接查询的基本概念和使用方法，包括 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN 等类型的连接查询。
连接查询的语法格式和常见用法，例如使用 WHERE 子句和 ON 子句进行条件筛选和连接条件的设置。
子查询的基本概念和使用方法，包括单行子查询、多行子查询、相关子查询和非相关子查询等类型的子查询。
子查询的语法格式和常见用法，例如将子查询结果作为 WHERE 子句的条件、将子查询结果作为 SELECT 子句的一部分或将子查询结果作为表进行连接查询等。在实验中，我们通过具体的案例进行了实践操作，掌握了连接查询和子查询的使用方法，并进一步加深了对 SQL 语言的理解和应用能力。总之，本次实验是学习 SQL 语言的重要一步，通过掌握连接查询和子查询的方法，我们可以更加灵活地使用 SQL 语言进行数据查询和分析，为后续的学习和工作打下坚实的基础。