SQL Serve中的嵌套查询、数据更新和视图_实验六 嵌套查询和数据更新

以下SQL语句均在SQL2008环境下执行

按照《数据库系统概论》(第五版)P79页的学生课程数据库和P70-71页中的SPJ数据库完成以下查询

（一）嵌套查询中的存在量词

1. 查询还没有给任何工程供应过零件的供应商号sno和供应商名称sname，用带EXISTS谓词的子查询或相关联子查询实现。

代码：

方法一：用带EXISTS谓词的子查询实现

SELECT SNO, SNAME

FROM S

WHERE NOT EXISTS 

(SELECT *

 FROM SPJ

 WHERE SNO = S.SNO

)

 

方法二：用相关联子查询实现

SELECT SNO, SNAME

FROM S

WHERE SNO NOT IN

(SELECT SNO

 FROM SPJ

 WHERE SPJ.SNO = S.SNO

)

 

注：不存在没有给任何工程供应零件的供应商（即所有的供应商都有给某个工程供应国零件），所以查询结果为如图

 

1. 查询使用了全部零件的工程号JNO，用带EXISTS谓词的子查询实现。

 

代码：

--查询使用了全部零件的工程号JNO，用带EXISTS谓词的子查询实现

SELECT JNO

FROM J

WHERE NOT EXISTS/*没有一个零件*/

(SELECT *

 FROM P

 WHERE NOT EXISTS/*是该工程不使用的*/

 (SELECT *

  FROM SPJ

  WHERE JNO = J.JNO AND PNO = P.PNO))

 

1. 查询至少选修了’1’号和’2’号课程的学生学号, 用带EXISTS谓词的子查询或自身连接查询实现。

代码：

--方法二、自身连接查询实现

SELECT FIRST.SNO

FROM SC FIRST, SC SECOND

WHERE FIRST.SNO = SECOND.SNO AND FIRST.CNO = 1 AND SECOND.CNO = 2

 

 

附加题：查询至少用了供应商S1所供应的所有零件的工程号JNO，用带EXISTS谓词的子查询实现。

 

代码：

--查询至少用了供应商S1所供应的所有零件的工程号JNO，用带EXISTS谓词的子查询实现

--(不存在这样的零件，供应商S1提供了，但该工程没有选)

SELECT DISTINCT JNO

FROM SPJ X

WHERE NOT EXISTS

(SELECT *

 FROM SPJ Y

 WHERE Y.SNO = 'S1' AND NOT EXISTS

 (SELECT *

  FROM SPJ Z

  WHERE Z.PNO = Y.PNO AND Z.JNO = X.JNO))

 

（二）数据更新　

1. P130 (8)把全部蓝色零件的颜色改为红色；

 

代码：

--把全部蓝色零件的颜色改为红色

UPDATE P

SET COLOR = '红'

WHERE COLOR = '蓝'

 

1. 将S5供应给J1的零件P3改为由S1供应，请作必要的修改；

 

代码：

--将S5供应给J1的零件P3改为由S1供应

UPDATE SPJ

SET SNO = 'S1'

WHERE SNO = 'S5' AND PNO = 'P3' AND JNO = 'J1'

 

1. 从供应商关系中删除S3的记录，并从供应情况关系中删除相应的记录。

 

代码：

--从供应商关系中删除S3的记录，并从供应情况关系中删除相应的记录

DELETE

FROM S

WHERE SNO = 'S3'

DELETE

FROM SPJ

WHERE SNO = 'S3'

 

1. 删除所有信息系（IS）同学的信息。

 

代码：

--删除所有信息系（IS）同学的信息

DELETE

FROM STUDENT

WHERE SDEPT = 'IS'

 

自行设计案例对学生－课程数据库的数据更新，并观察是否有违反数据的完整性约束。

 

案例一、分别对每个系的学生求年龄的平均值，并存入新表中

代码：

--第一步

CREATE TABLE DEPT_AGE

(Sdept CHAR(15),

 AVG_AGE SMALLINT

)

 

--第二步

INSERT INTO DEPT_AGE

SELECT SDEPT, AVG(SAGE)

FROM STUDENT

GROUP BY SDEPT

 

（三）视图

1. P130 (9)请为”三建”工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。针对该视图完成下列查询：

①找出”三建”工程项目使用的各种零件代码及其数量；

 

代码：

--找出”三建”工程项目使用的各种零件代码及其数量

SELECT PNO, SUM(QTY) 零件总数

FROM 三建

GROUP BY PNO

 

代码：

--找出供应商S2的供应情况

SELECT *

FROM 三建

WHERE SNO = 'S2'

 

1. 创建一个学生成绩统计视图，包括学号，姓名，选课门数，平均分，最高分。（没有选课的学生不参加统计）。

 

代码：

--创建一个学生成绩统计视图，包括学号，姓名，选课门数，平均分，最高分

CREATE VIEW STU_GRADE (SNO,SNAME,SCNUM,AVG_GRA,MAX_GRA)

AS

SELECT STUDENT.SNO, SNAME, COUNT(CNO), AVG(GRADE), MAX(GRADE)

FROM STUDENT FULL OUTER JOIN SC ON STUDENT.SNO = SC.SNO

GROUP BY STUDENT.SNO, SNAME