实验四：SQL Serve中的嵌套查询、数据更新和视图.doc

实验四：SQL Serve中的嵌套查询、数据更新和视图
一、 实验目的：
1． 掌握SQL语言的核心语句－－SELECT语句的使用，具体为在SQL Server2005环境下采用Transact-SQL实现嵌套查询。
2． 掌握采用Transact-SQL实现数据更新。
3． 掌握采用Transact-SQL实现视图的定义、删除、查询与更新。
二、实验内容
按照《数据库系统概论》P82页中的学生课程数据库和P75页中的SPJ数据库完成以下查询
（一）嵌套查询中的存在量词
1. 查询还没有给任何工程供应过零件的供应商号sno和供应商名称sname，用带EXISTS谓词的子查询或相关联子查询实现。
select sno,sname from s where not exists( select * from spj where spj.sno = s.sno)

2. 查询使用了全部零件的工程号JNO，用带EXISTS谓词的子查询实现。
select jno from j where not exists(select * from p where not exists(select * from spj where p.pno = spj.pno and j.jno = spj.jno))

3. 查询至少选修了’1’号和’2’号课程的学生学号, 用带EXISTS谓词的子查询或自身连接查询实现。
(1)select sno from sc where sno in (select sno from sc where cno='1') and cno='2'

(2)select sno from sc where cno='1' intersect select sno from sc where cno='2'
(3) select sno from sc sc1 where exists( select * from sc sc2 where sc1.sno = sc2.sno and o = '1' and o = '2')

附加题：查询至少用了供应商S1所供应的所有零件的工程号JNO，用带EXISTS谓词的子查询实现。
select jno from spj spj1 where not exists( select * from spj spj2 where sno = 's1' and not exists( select * from spj spj3 where spj1.sno = spj2.sno and spj3.pno = spj2.pno))

（二）数据更新　
1. 把全部蓝色零件的颜色改为红色；
update p set p.color='蓝' where p.color='红';
select * from p;

2. 将S5供应给J1的零件P3改为由S1供应，请作必要的修改；
update spj set sno='s1' where sno = 's5' and pno='p3' and jno = 'j1' ;
select * from spj;

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

delete from spj where sno='s3';
delete from s where sno='s3'
select * from s;
select * from spj;

4. 删除所有信息系（CS）同学的信息。

delete from student where sdept = 'cs';
delete from sc where sno in (select sno from student where sdept='cs' ) ;
select * from student;
select * from sc;

自行设计案例对学生－课程数据库的数据更新，并观察是否有违反数据的完整性约束。
（三）视图
1. 请为”一汽”工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。针对该视图完成下列查询：
create view s_s(sno,pno,qty) as select sno,pno,qty from spj,j where j.jno = spj.jno and j.jname='一汽'
① 找出”一汽”工程项目使用的各种零件代码及其数量；
select sno 零件代码,sum(qty) 数量from s_s group by sno;

② 找出供应商S2的供应情况；
select * from s_s where sno='s2';

2. 创建一个学生成绩统计视图，包括学号，姓名，选课门数，平均分，最高分。（没有选课的学生不参加统计）。
create view s_g(sno,sname,sum,avg,max)
as
select student.sno,sname,count(cno),avg(grade),max(grade) from
student,sc
group by student.sno,sc.sno,student.sname;

select * from s_g

三、实验要求
(1) 写出正确的Transact-SQL命令。
(2) 无须记录正确的查询结果。但要求记录实验过程中发生的有学习意义的错误及错误信息。
四、实验报告
根据以上实验内容的要求认真填写实验报告，记录所有的实现方法和运行结果，并记录实验过程中遇到的困难和解决问题的方法。
《数据库系统原理及应用》实验_4__报告
实验题目：
日期

班级

姓名

实验环境：
实验内容与完成情况(记录所有的实验过程)：
SELECT * FROM Student
select JNO FROM SPJ
WHERE NOT EXISTS
( SELECT * FROM J WHERE NOT EXISTS
(SELECT * FROM P WHERE PNO=SPJ.PNO AND JNO=J.JNO))

SELECT DISTINCT SNO
FROM SC SCX
WHERE NOT EXISTS
(SELECT * FROM SC SCY
WHERE NOT EXISTS
(SELECT * FROM SC SCZ
WHERE SCY.CNO='1' AND SCZ.CNO='2'))

UPDATE P
SET COLOR='红'
WHERE COLOR='蓝'

UPDATE SPJ
SET SNO='S1'
WHERE SNO='S5' AND PNO='P3' AND JNO='J1'

delete
from s
where sno='s3'

DELETE
FROM Student
WHERE Sdept='sc'

CREATE VIEW IS_SPJ( SNO,PNO,QTY)
AS
SELECT SNO,PNO,QTY
FROM SPJ
WHERE JNO='J2'

SELECT PNO,QTY
FROM IS_SPJ

SELECT SNO,PNO,QTY
FROM IS_SPJ
WHERE SNO='S2'

CREATE VIEW IS_S
AS
SELECT Student.SNO,Sname,count(* Cno),avg(Grade),MAX(Grade)
from Student,sc

出现的问题：
解决方案（列出遇到的主要问题和解决办法，列出没有解决的问题）：