数据库概论实验指导书(3)

又如：查询至少选修了学生200215122选修的全部课程的学生号码。

SELECT DISTINCT Sno FROM SC SCX

WHERE NOT EXISTS (SELECT * FROM SC SCY

WHERE SCY.Sno='200215122' AND NOT EXISTS (SELECT * FROM SC SCZ

WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno));

（9）集合查询

例如：查询计算机系的学生以及年龄不大于19岁的的学生。

SELECT * FROM Student WHERE Sdept='CS'

UNION /*并集运算*/ SELECT * FROM Student WHERE Sage<=19;

可以改用多重条件查询： SELECT * FROM Student

WHERE Sdept='CS' OR Sage<=19;

又如：查询既选修了课程1又选修了课程2的学生（交集运算）。

SELECT Sno FROM SC

WHERE Cno='1'

INTERSECT /*交集运算*/ SELECT Sno FROM SC

WHERE Cno='2';

可以使用嵌套查询： SELECT Sno FROM SC

WHERE Cno='1' AND Sno IN

(SELECT Sno FROM SC

WHERE Cno='2'); 思考：能不能改用多重条件查询？

SELECT Sno FROM SC

WHERE Cno='1' AND Cno='2';

再如：查询计算机系的学生与年龄不大于19岁的学生的差集。

SELECT * FROM Student WHERE Sdept='CS'

EXCEPT /*差集运算*/ SELECT * FROM Student WHERE Sage<=19;

可以改用多重条件查询： SELECT * FROM Student

WHERE Sdept='CS' AND Sage>19;

扩展练习（要求写出并执行SQL语句完成以下各种操作，记录查询结果）

（1）查询每门课程及其被选情况（输出所有课程中每门课的课程号、课程名称、选修该课程的学生学号及成绩--如果没有学生选择该课，则相应的学生学号及成绩为空值）。 （2）查询与“张立”同岁的学生的学号、姓名和年龄。（要求使用至少3种方法求解） （3）查询选修了3号课程而且成绩为良好（80~89分）的所有学生的学号和姓名。 （4）查询学生200215122选修的课程的课程号、课程名

（思考：如何查询学生200215122选修的课程的课程号、课程名及成绩？）

（5）找出每个学生低于他所选修课程平均成绩5分以上的课程号。（输出学号和课程号） （6）查询比所有男生年龄都小的女生的学号、姓名和年龄。 （7）查询所有选修了2号课程的学生姓名及所在系。

实验三SQL的数据更新（2学时）

1、实验目的

(1) 掌握DBMS的数据查询功能 (2) 掌握SQL语言的数据更新功能 2、实验内容

(1) update 语句用于对表进行更新 (2) delete 语句用于对表进行删除 (3) insert 语句用于对表进行插入 3、实验要求

(1) 熟练掌握SQL的数据更新语句INSERT、UPDATE、DELETE (2) 写出实验报告 4、实验步骤

4.1 认真阅读S_T.sql，理解其中插入和修改语句的作用和用法；执行S_T.sql，建立示例数据库S_T。

4.2 对学生关系Student、课程关系Course和选修关系SC进行数据插入、修改和删除元组（记录）的更新操作。

基本练习

（1）插入一个元组

练习1：将一个新学生元组（200215140，田刚，男，计算机系，19岁）插入Student表中。

INSERT

INTO Student (Sno,Sname,Ssex,Sdept, Sage) VALUES('200215140','田刚','男'，'CS',19);

练习2：插入一条选课记录（‘200215140’, ‘3’）。

INSERT INTO SC

VALUES('200215140','3',NULL); 也可以使用如下SQL语句： INSERT

INTO SC(Sno,Cno)

VALUES('200215140','3');

思考：这两种用法有什么区别？（但最终效果一样！）

（2）插入子查询结果

练习3：对每一个学生，求其平均成绩，并把结果存入新表Sno_grade。

/*首先创建新表Sno_grade：*/ CREATETABLE Sno_grade ( Sno char(9)primarykey, Avg_grade SMALLINT); go

/*然后对SC表按学号分组求平均成绩，再把学号和平均成绩存入新表中：*/ INSERTINTO Sno_grade(Sno,Avg_grade) SELECT Sno,AVG(grade) FROM SC

GROUP BY Sno; （3）修改某一个元组的值

练习4：将学生200215131的姓名改为“张莉莉”

UPDATE Student SET Sname='张莉莉' WHERE Sno='200215131'; （4）修改多个元组的值

练习5：将所有计算机系学生的年龄统一增加1岁。

UPDATE Student SET Sage=Sage+1 WHERE Sdept='CS'; （5）带子查询的修改语句

练习6：将计算机系全体学生的成绩统一增加2分。

UPDATE SC

SET Grade=Grade+2 WHERE 'CS'= (SELECT Sdept FROM Student

WHERE Student.Sno = SC.Sno); （6）删除某一个元组的值

练习7：删除学号为200215133的学生记录。

DELETE

FROM Student

WHERE Sno='200215133'; 删除多个元组的值

练习8：删除表Sno_grade中平均成绩低于90分（不含90分）的记录。

DELETE

FROM Sno_grade

WHERE Avg_grade<90;

练习9：删除表Sno_grade中全部记录。

DELETE

FROM Sno_grade

思考：删除一个表中的全部元组后，该表的定义是否被同时删除？ （8）带子查询的删除语句

练习10：删除计算机系所有学生的选课记录。

DELETE FROM SC WHERE 'CS'= (SELECT Sdept FROM Student

WHERE Student.Sno = SC.Sno); 扩展练习

首先执行S_T.sql，重建示例数据库S_T。

然后写出并执行SQL语句完成以下各种操作，记录查询结果。 （1）在Student表中插入计算机系的两个新生元组：（学号：200215180；姓名：王新宇；性别：男；年龄：19岁），（学号：200215181；姓名：丛欣然；性别：女；年龄：18岁）。 （2）对每一门课，求其选课人数，并把结果（课程号，选课人数）存入新表Cno_Number。 （3）将计算机系CS的所有学生的系别改为软件工程系SE。 （4）将软件工程系SE的全体学生的成绩统一减去2分。 （5）删除学号为200215180的学生记录。

（6）将信息系IS所有学生的成绩置为空值（非0值）。 （7）将所有成绩为空值的选课记录删除掉。

实验四SQL的数据视图（2学时）

1、实验目的

(1) 掌握DBMS的数据查询功能 (2) 掌握SQL语言的视图功能 2、实验内容

(1) 创建表的视图

(2) 利用视图完成表的查询 (3) 删除表的视图 3、实验要求

(1) 掌握视图的定义 (2) 掌握对视图的操作 (3) 写出实验报告 4、实验步骤

4.1 执行S_T.sql，建立示例数据库S_T。

4.2 对学生关系Student、课程关系Course和选修关系SC进行视图的定义（创建）、查询、更新、删除等操作。 基本练习

（1）创建视图

练习1：创建信息系学生的视图。

CREATE VIEW IS_Student AS

SELECT Sno,Sname,Ssex,Sage FROM Student WHERE Sdept='IS' WITH CHECK OPTION;

思考：其中，WITH CHECK OPTION子句的作用是什么？

练习2：建立信息系选修了1号课程且成绩在88分以上的学生的视图。

解法1：

CREATE VIEW IS_S1_88(Sno,Sname,Grade) AS

SELECT Student.Sno, Sname, Grade FROM Student,SC

WHERE Sdept='IS' AND

Student.Sno=SC.Sno AND Cno='1' AND Grade>=88; 解法2：

CREATE VIEW IS_S1_88(Sno,Sname,Grade) AS

SELECT IS_Student.Sno, Sname, Grade FROM IS_Student,SC

WHERE IS_Student.Sno=SC.Sno AND Cno='1' AND Grade>=88;

练习3：将学生的学号及平均成绩定义为一个视图。

CREATE VIEW S_G(Sno,Gavg) AS

SELECT Sno, AVG(Grade) FROM SC

GROUP BY Sno; （2）查询视图

练习4：在信息系学生的视图中找出年龄小于19的女生。

SELECT Sno,Sname,Ssex,Sage FROM IS_Student

WHERE Sage<19 and Ssex='女';

思考：试写出通过视图消解法转换后的查询语句。 练习5：利用IS_S1_88视图，查询信息系选修了1号课程且成绩在92分以上的学生的学号、姓名和成绩。

SELECT *

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库数据库概论实验指导书(3)在线全文阅读。