数据库 复杂查询操作

实验目的

掌握连接和嵌套查询操作。

实验平台

操作系统：Windows x64 

DBMS：SQL Server 2016 

实验代码

（1）根据学生、课程和成绩表，输出“计算机”课程的成绩单，包括姓名和分数。

SELECT  姓名,分数  

FROM 学生 A  JOIN 成绩 B ON(A.学号=B.学号) 

JOIN 课程 C ON(B.课程编号=C.课程编号) 

WHERE  C.课程名称='计算机’

（2）查询所开课程的选修情况，包括没有任何学生选修的课程。

​
SELECT  学号, 课程名称, 分数

FROM 成绩 A  RIGHT OUTER JOIN 课程 B 

ON (A.课程编号=B.课程编号)

​

（3）查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）。

SELECT  C.学号,C.姓名,A.课程编号,B.课程名称,A.分数

FROM  成绩 A,课程 B,学生 C

WHERE A.课程编号= B.课程编号 AND A.学号=C.学号

（4）查询没有选修一门课程的学生的基本信息。

SELECT  学号,姓名

FROM 学生

WHERE NOT EXISTS

(SELECT *

FROM 课程

WHERE EXISTS

(SELECT *

FROM 成绩

WHERE 学号=学生.学号 AND 课程编号=课程.课程编号));

（5）显示男学生的姓名和所在的系名称。

SELECT 姓名,系名称

FROM 学生,系

WHERE 学生.系编号=系.系编号 AND  性别='男'

(6)列出教工中比姜立伟工资低的老师姓名和工资。

SELECT  R.姓名,R.工资

FROM   教工 R, 教工 S

WHERE  R.工资<S.工资 AND  S.姓名='姜立伟'

（7）统计各科课程号、选课人数、平均分、最高分、最低分，并存入新表statscore.

CREATE TABLE statscore

(课程编号 char(8),

选课人数 NUMERIC(10,0),

最高分 NUMERIC(4,1),

最低分 NUMERIC(4,1),

平均分 NUMERIC(4,1)

);

INSERT

INTO statscore(课程编号,选课人数,平均分,最高分,最低分)

SELECT 课程编号,COUNT(学号)AS 选课人数,MAX(分数)AS 最高分,MIN(分数)AS 最低分,AVG(分数)AS 平均分

FROM 成绩

GROUP BY 课程编号

ORDER BY 课程编号

2、假设有贷款关系如图1所示。

帐号	姓名	金额
G10020	关平之	20033.00
T20078	李连玉
K65743	姜海洋	30092.00
H89765	田平平	30200.60
H76890	方一华	19080.40

图1 贷款关系

（4）创建贷款关系。

create table  贷款
( 帐号 char(20),
  姓名  char(8),
  金额  char(10))
insert into 贷款 values('G10020','关平之', '20033.0' )
insert into 贷款(帐号,姓名) values('T20078','李连玉' )
insert into 贷款 values('T65743','姜海洋', '30092.00 ')
insert into 贷款 values('H89765','田平平', '30200.60')
insert into 贷款 values('H76890','方一华', '19080.40')

（5）找出贷款表中金额为空值的帐号和姓名。

SELECT  帐号,姓名
FROM 贷款
WHERE 金额 IS NULL

（6）找出贷款表中贷款金额非空的帐号和姓名。

SELECT  帐号,姓名
FROM 贷款
WHERE 金额 IS NOT NULL

  3、 假设有导师与研究生2个表，如图2所示。

图2 导师与研究生表

建立导师表：

CREATE TABLE 导师

( 教工号  char(10),

  姓名  char(8),

  性别 char(2))

insert into 导师 values('T001','江海', '男' )

insert into 导师 values('T002','代宁', '女')

insert into 导师 values('T005','潘涛', '男')

insert into 导师 values('T008','田立', '女')

建立研究生表：

CREATE TABLE 研究生

( 学号  char(10),

  姓名  char(8),

  导师号 char(10))

insert into 研究生 values('99001','李南', 'T001' )

insert into 研究生 values('99002','刘星', 'T002')

insert into 研究生 values('99003','王海', 'T002')

insert into 研究生 values('99004','张力', 'T001')

insert into 研究生(学号,姓名) values('99006','郭天')

（7） 查询研究生与其导师的情况。

SELECT  *  
FROM  研究生 A INNER JOIN 导师 B ON A.导师号= B.教工号

（8）计算导师与研究生关系的笛卡儿乘积。

SELECT  *

FROM 导师 CROSS JOIN 研究生

（9）查询教师指导研究生的情况，包括不指导研究生的教师。

SELECT  *
FROM  导师 A  LEFT OUTER JOIN 研究生 B  ON (A.教工号= B.导师号)

（10）查询研究生和教师的情况，包括没有导师的研究生和没有指导研究生的教师。

SELECT * 
FROM 研究生 A FULL OUTER JOIN 导师 B ON (A.导师号=B.教工号)

（11）查询研究生与其导师的情况。

SELECT  *  
FROM  研究生 A INNER JOIN 导师 B ON A.导师号= B.教工号

实验分析 

（1）常见错误：因为该列没有包含在聚合函数或 GROUP BY 子句中。这是因为即指定 GROUP BY 时，选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。

（2）注意：在各个表中插入数据的顺序，首先在父表中插入数据，然后在子表中插入数据。另外，由于表的定义中包含完整性约束的定义，所以，当主码重复，或者外码不是被参照表的有效值时，系统将拒绝插入的数据。

（3）查询的基本原则：①FROM子句应当出现所有的表名。 ②WHERE子句应定义一个条件表达式，限定查询只能返回满足条件的记录。 ③当列名为多个表共有时，列名必须被限制。