数据库上机试验（三）

这是第三次上机内容以及结果，本次的程序是基于第二次上机时候的建立好的表。所有完整代码在我空间的代码库中均存放，可以直接运行。 上机实验五 SELECT语句的使用（二） 一、实习目的 掌握SELECT语句的嵌套使用方法，能适用SQL Server对表作复杂查询。 二、

这是第三次上机内容以及结果，本次的程序是基于第二次上机时候的建立好的表&＃26684;。所有完整代码在我空间的代码库中均存放，可以直接运行。

上机实验五 SELECT语句的使用（二）

一、实习目的

掌握SELECT语句的嵌套使用方法，能适用SQL Server对表作复杂查询。

二、实习准备

1．复习SELECT语句较高级&＃26684;式的使用。

2．了解集合函数在分组查询中的使用规则。

三、实验内容

使用嵌套查询和表的连接查询分别完成以下查询语句：

(1) 找出与李勇在同一个班级的学生信息。

(2) 找出所有与李勇有相同选修课的学生信息。

(3) 找出年龄介于学生李勇和25岁之间的学生信息。

(4) 找出选修了课程操作系统的学生学号和姓名。

(5) 找出所有没有选修1号课程的学生姓名。

(6) 找出选修了全部课程的学生姓名。

完成以下查询：

(1) 查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列。

(2) 查询全体学生信息，要求查询结果按班级号升序，同一班级学生按年龄降序排列。

(3) 求每个课程号相应的选课人数。

(4) 查询选修了3门以上课程的学生学号。

四、实习报告内容

1．写出上述操作的SQL语句。

2．使用存在量词[NOT] EXISTS的嵌套查询时，何时外层查询的WHERE条件为真，何时为假？

3．当既能用连接查询又能用嵌套查询时，应该选择哪种比较好？为什么？

上机实验六 SQL的存储操作

一、实习目的

掌握用交互式SQL语句对已建基本表进行存储操作：修改，删除，插入，加深对数据的完整性的理解。

二、实习准备

1．复习数据的完整性，在进行数据的修改、删除、插入时，要注意保证数据的一致性。

2．复习UPDATE、DELETE、INSERT语句与子查询的结合使用。

三、实习内容

完成以下查询语句：

(1) 对每个班，求学生的平均年龄，并把结果存入数据库。

(2) 将01311班的全体学生的成绩置零。

(3) 删除2001级计算机软件班全体学生的选课记录。

(4) 学生李勇已退学，从数据库中删除有关他的记录。

四、实习报告内容

1．写出上述操作的SQL语句。

运行代码：

/*************上机试验五************************/
/**找出与李勇在同一个班级的学生信息**/
SELECT Sno '学号',Sname '姓名',Ssex '性别',Sage '年龄',Clno '班级'
FROM Student
WHERE Clno =
(SELECT Clno
FROM Student
WHERE Sname = '李勇');

/**找出所有与李勇有相同选修课的学生信息**/
SELECT *
FROM Student
WHERE Sno IN
(SELECT Sno
FROM Grade
WHERE Cno IN
(SELECT Cno
FROM Grade
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sname='李勇')));

/**找出年龄介于学生李勇和25岁之间的学生信息**/
SELECT *
FROM Student
WHERE Sage BETWEEN
(SELECT Sage
FROM Student
WHERE Sname = '李勇') AND 25;


/**找出所有没有选修1号课程的学生姓名**/
SELECT Sname '姓名'
FROM Student
WHERE Sno NOT IN
(SELECT Sno
FROM Grade
WHERE Cno = '1');

/**找出选修了全部课程的学生姓名**/
SELECT Sname
FROM Student
WHERE NOT EXISTS(SELECT *
FROM Course
WHERE NOT EXISTS (SELECT *
FROM Grade
WHERE Student.Sno=Grade.Sno and Course.Cno=Grade.Cno));

/**查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列**/
SELECT Sno '学号', Gmark '成绩'
FROM Grade
WHERE Cno = '3' ORDER BY Gmark DESC;


/**查询全体学生信息，要求查询结果按班级号升序，同一班级学生按年龄降序排列**/
SELECT *
FROM Student
ORDER BY Clno,Sage DESC;


/**求每个课程号相应的选课人数**/
SELECT Cno '课程号',COUNT(*) '选课人数'
FROM Grade
GROUP BY Cno;


/**查询选修了3门以上课程的学生学号**/
SELECT Sno
FROM Grade
GROUP BY Sno HAVING COUNT(*)>3;


/**********上机作业六***************/
/**对每个班，求学生的平均年龄，并把结果存入数据库**/
/*第一种方法建立新的数据库*/
DROP TABLE Savage;
CREATE TABLE Savage(
Clno Char(5) NOT NULL,
Avage Smallint
);
INSERT INTO Savage(Clno,Avage)
SELECT Clno,AVG(Sage)
FROM Student
GROUP BY Clno;
/*第二种方法在Class表中新建一个属性列*/ /*一直出错误，错在哪了。。。*/
/*ALTER TABLE Class add Avage Smallint;
UPDATE Class
SET Avage = AVG(Sage)
FROM Class,Student
GROUP BY Clno HAVING Student.Clno =Class.Clno;*/

/**将01311班的全体学生的成绩置零**/
UPDATE Grade
SET Gmark=0
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Clno = '01311');


/**删除2001级计算机软件班全体学生的选课记录**/
DELETE
FROM Grade
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Clno = (
SELECT Clno
FROM Class
WHERE Speciality = '计算机软件' AND Inyear = '2001'));


/**学生李勇已退学，从数据库中删除有关他的记录**/
/*受影响的表为Class Grade Student (新建的Savage 表先不考虑)*/
UPDATE Class
SET Number = Number-1
WHERE Clno =
(SELECT Clno
FROM Student
WHERE Sname = '李勇');
DELETE
FROM Grade
WHERE Sno =
(SELECT Sno
FROM Student
WHERE Sname = '李勇');
DELETE
FROM Student
WHERE Sname = '李勇';