1． 实验内容

　　建的数据库如下：

1 CREATE TABLE Depts 2 ( 3     Dno CHAR(20) PRIMARY KEY, 4     Dname CHAR(20) NOT NULL 5 ) 6 INSERT INTO Depts VALUES('D01','自动化') 7 INSERT INTO Depts VALUES('D02','计算机') 8 INSERT INTO Depts VALUES('D03','数学') 9 INSERT INTO Depts VALUES('D04','通信')10 INSERT INTO Depts VALUES('D05','电子')11 12 CREATE TABLE Students13 (14     Sno  CHAR(20) PRIMARY KEY,15     Sname CHAR(20) NOT NULL,16     Ssex CHAR(2),17     Sage INT,18     Dno CHAR(20),19     CONSTRAINT FK_Dno FOREIGN KEY(Dno) REFERENCES Depts20 )21 INSERT INTO Students VALUES('S01','王建平','男',21,'D01')22 INSERT INTO Students VALUES('S02','刘华','女',19,'D01')23 INSERT INTO Students VALUES('S03','范林军','女',18,'D02')24 INSERT INTO Students VALUES('S04','李伟','男',19,'D03')25 INSERT INTO Students VALUES('S05','黄河','男',18,'D03')26 INSERT INTO Students VALUES('S06','长江','男',20,'D03')27 28 CREATE TABLE Courses29 (30     Cno CHAR(20) PRIMARY KEY,31     Cname CHAR(20),32     Pre_Cno CHAR(6),33     Credits INT34 )35 INSERT INTO Courses VALUES('C01','英语',NULL,4)36 INSERT INTO Courses VALUES('C02','数据结构','C05',2)37 INSERT INTO Courses VALUES('C03','数据库','C02',2)38 INSERT INTO Courses VALUES('C04','DB_设计','C03',3)39 INSERT INTO Courses VALUES('C05','C++',NULL,3)40 INSERT INTO Courses VALUES('C06','网络原理','C07',3)41 INSERT INTO Courses VALUES('C07','操作系统','C05',3)42 43 CREATE TABLE Reports44 (45     Sno CHAR(20),46     Cno CHAR(20),47     Grade INT,48     PRIMARY KEY(Sno,Cno),49     CONSTRAINT Student_Report FOREIGN KEY(Sno) REFERENCES Students,50     CONSTRAINT Report_Course FOREIGN KEY(Cno) REFERENCES Courses51 )52 INSERT INTO Reports VALUES('S01','C01',92)53 INSERT INTO Reports VALUES('S01','C03',84)54 INSERT INTO Reports VALUES('S02','C01',90)55 INSERT INTO Reports VALUES('S02','C02',94)56 INSERT INTO Reports VALUES('S02','C03',82)57 INSERT INTO Reports VALUES('S03','C01',72)58 INSERT INTO Reports VALUES('S03','C02',90)59 INSERT INTO Reports VALUES('S04','C03',75)

1．  求数学系学生的学号和姓名

SELECT Sno,SnameFROM StudentsWHERE Dno IN(    SELECT Dno    FROM Depts    WHERE Dname='数学')

2．  求选修了课程的学生学号

SELECT DISTINCT SnoFROM Reports

3．  求选修课程号为‘C01’的学生号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。

SELECT Sno,GradeFROM ReportsWHERE Cno='C01' ORDER BY Grade DESC,Sno

4 .   求选修课程号为‘C01’且成绩在80~90之间的学生学号和成绩，并成绩乘以0.8输出。

SELECT Sno,Grade*0.8 NewGradeFROM ReportsWHERE Cno='C01' AND Grade BETWEEN 80 AND 90

5 .   求数学系或计算机系姓张的学生的信息。(若为'张__',则查询的姓名为不多于3个汉字，两个下划线代表一个汉字)

SELECT *FROM StudentsWHERE Sname LIKE'张%' AND Dno IN(    SELECT Dno    FROM Depts    WHERE Dname='数学' OR Dname='计算机')

6 .   查询每个学生的情况以及他所选修的课程

SELECT Students.* ,Reports.*FROM Students,ReportsWHERE Students.Sno= Reports.Sno

7．  求学生的学号、姓名、选修的课程及成绩

SELECT Students.Sno,Sname,Cname,GradeFROM Students,Reports,Courses,DeptsWHERE Students.Sno= Reports.Sno AND Reports.Cno=Courses.Cno AND Students.Dno=Depts.Dno

8．  查询每一门课程的间接先行课（即先行课的先行课）

SELECT A.Cno,A.Cname,B.Pre_CnoFROM Courses A,Courses BWHERE A.Pre_Cno=B.Cno

9 .  求其他系中比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）

SELECT *FROM StudentsWHERE Dno!=(    SELECT Dno    FROM Depts    WHERE Dname='计算机') AND Sage

【*】10.  查询选修了全部课程的学生姓名

SELECT Sname FROM StudentsWHERE NOT EXISTS(    SELECT *    FROM Courses    WHERE NOT EXISTS    (        SELECT *        FROM Reports        WHERE Sno=Students.Sno AND Cno=Courses.Cno    ))

【*】11.  求至少选修了学号为“S03”的学生所选修全部课程的学生学号和姓名

SELECT Sno,Sname FROM StudentsWHERE Sno IN(    SELECT DISTINCT Sno    FROM Reports A    WHERE NOT EXISTS    (        SELECT *        FROM Reports B        WHERE B.Sno='S03' AND NOT EXISTS        (            SELECT *            FROM Reports C            WHERE C.Sno=A.Sno AND C.Cno=B.Cno        )    ))