数据库基础(2):简单查询和连接查询

1． 实验内容

　　建的数据库如下：

CREATE TABLE Depts
(
    Dno CHAR(20) PRIMARY KEY,
    Dname CHAR(20) NOT NULL
)
INSERT INTO Depts VALUES('D01','自动化')
INSERT INTO Depts VALUES('D02','计算机')
INSERT INTO Depts VALUES('D03','数学')
INSERT INTO Depts VALUES('D04','通信')
INSERT INTO Depts VALUES('D05','电子')

CREATE TABLE Students
(
    Sno  CHAR(20) PRIMARY KEY,
    Sname CHAR(20) NOT NULL,
    Ssex CHAR(2),
    Sage INT,
    Dno CHAR(20),
    CONSTRAINT FK_Dno FOREIGN KEY(Dno) REFERENCES Depts
)
INSERT INTO Students VALUES('S01','王建平','男',21,'D01')
INSERT INTO Students VALUES('S02','刘华','女',19,'D01')
INSERT INTO Students VALUES('S03','范林军','女',18,'D02')
INSERT INTO Students VALUES('S04','李伟','男',19,'D03')
INSERT INTO Students VALUES('S05','黄河','男',18,'D03')
INSERT INTO Students VALUES('S06','长江','男',20,'D03')

CREATE TABLE Courses
(
    Cno CHAR(20) PRIMARY KEY,
    Cname CHAR(20),
    Pre_Cno CHAR(6),
    Credits INT
)
INSERT INTO Courses VALUES('C01','英语',NULL,4)
INSERT INTO Courses VALUES('C02','数据结构','C05',2)
INSERT INTO Courses VALUES('C03','数据库','C02',2)
INSERT INTO Courses VALUES('C04','DB_设计','C03',3)
INSERT INTO Courses VALUES('C05','C++',NULL,3)
INSERT INTO Courses VALUES('C06','网络原理','C07',3)
INSERT INTO Courses VALUES('C07','操作系统','C05',3)

CREATE TABLE Reports
(
    Sno CHAR(20),
    Cno CHAR(20),
    Grade INT,
    PRIMARY KEY(Sno,Cno),
    CONSTRAINT Student_Report FOREIGN KEY(Sno) REFERENCES Students,
    CONSTRAINT Report_Course FOREIGN KEY(Cno) REFERENCES Courses
)
INSERT INTO Reports VALUES('S01','C01',92)
INSERT INTO Reports VALUES('S01','C03',84)
INSERT INTO Reports VALUES('S02','C01',90)
INSERT INTO Reports VALUES('S02','C02',94)
INSERT INTO Reports VALUES('S02','C03',82)
INSERT INTO Reports VALUES('S03','C01',72)
INSERT INTO Reports VALUES('S03','C02',90)
INSERT INTO Reports VALUES('S04','C03',75)

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

SELECT Sno,Sname
FROM Students
WHERE Dno IN
(
    SELECT Dno
    FROM Depts
    WHERE Dname='数学'
)

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

SELECT DISTINCT Sno
FROM Reports

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

SELECT Sno,Grade
FROM Reports
WHERE Cno='C01' 
ORDER BY Grade DESC,Sno 

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

SELECT Sno,Grade*0.8 NewGrade
FROM Reports
WHERE Cno='C01' AND Grade BETWEEN 80 AND 90

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

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

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

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

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

SELECT Students.Sno,Sname,Cname,Grade
FROM Students,Reports,Courses,Depts
WHERE Students.Sno= Reports.Sno AND Reports.Cno=Courses.Cno
 AND Students.Dno=Depts.Dno

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

SELECT A.Cno,A.Cname,B.Pre_Cno
FROM Courses A,Courses B
WHERE A.Pre_Cno=B.Cno

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

SELECT *
FROM Students
WHERE Dno!=
(
    SELECT Dno
    FROM Depts
    WHERE Dname='计算机'
) AND Sage<some
    (
        SELECT MAX(Sage)
        FROM Students
        WHERE Dno=
        (
            SELECT Dno
            FROM Depts
            WHERE Dname='计算机'
        )
    )

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

SELECT Sname 
FROM Students
WHERE 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 Students
WHERE 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
        )
    )
)