数据库上机试验（三）

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

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

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

一、实习目的

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

二、实习准备

1．复习SELECT语句较高级格式的使用。

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 = '李勇';