数据库原理及应用实验指导★---实验4 SQL语言——SELECT查询操作

2023年6月21日发(作者：)

实验4 SQL语言——SELECT查询操作

实实验验44

SSQQLL语语言言————SSEELLEECCTT查查询询操操作作

实验示例

实验示例中要使用包括如下三个表的“教学管理”数据库JXGL：

(1)学生表Student，由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记作：Student(Sno,Sname,Ssex,Sage,Sdept)，其中主码为Sno。

(2)课程表Course，由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记作：Course(Cno,Cname,Cpno,Ccredit)，其中主码为Cno。

(3)学生选课SC，由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记作：SC(Sno,Cno,Grade)，其中主码为(SNO,CNO)。

1、在SQL SERVER查询分析器或企业管理器(以具有相应操作权限的某用户登录)的SQL操作窗口中执行如下命令创建数据库。需要说明的是不同数据库系统其创建数据库的命令或方式有所不同。

CREATE DATABASE JXGL

2、刷新数据库目录后，选择新出现的JXGL数据库，在SQL操作窗口中，创建Student、SC、Course三表及表记录插入命令如下：

Create Table Student

( Sno CHAR(5) NOT NULL PRIMARY KEY(Sno),

Sname VARCHAR(20),

Sage SMALLINT CHECK(Sage>=15 AND Sage<=45),

Ssex CHAR(2) DEFAULT '男' CHECK (Ssex='男' OR Ssex='女'),

Sdept CHAR(2));

Create Table Course

( Cno CHAR(2) NOT NULL PRIMARY KEY(Cno),

Cname VARCHAR(20),

Cpno CHAR(2),

Ccredit SMALLINT);

Create Table SC

( Sno CHAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES Student(Sno),

Cno CHAR(2) NOT NULL,

Grade SMALLINT CHECK ((Grade IS NULL) OR (Grade BETWEEN 0 AND 100)),

PRIMARY KEY(Sno,Cno),

1 数据库原理与应用实验指导

FOREIGN KEY(Cno) REFERENCES Course(Cno));

INSERT INTO Student VALUES('98001','钱横',18,'男','CS');

INSERT INTO Student VALUES('98002','王林',19,'女','CS');

INSERT INTO Student VALUES('98003','李民',20,'男','IS');

INSERT INTO Student VALUES('98004','赵三',16,'女','MA');

INSERT INTO Course VALUES('1','数据库系统', '5',4);

INSERT INTO Course VALUES('2','数学分析',null ,2);

INSERT INTO Course VALUES('3','信息系统导论','1',3);

INSERT INTO Course VALUES('4','操作系统原理','6',3);

INSERT INTO Course VALUES('5','数据结构','7',4);

INSERT INTO Course VALUES('6','数据处理基础',null,4);

INSERT INTO Course VALUES('7','C语言','6',3);

INSERT INTO SC VALUES('98001','1',87);

INSERT INTO SC VALUES('98001','2',67);

INSERT INTO SC VALUES('98001','3',90);

INSERT INTO SC VALUES('98002','2',95);

INSERT INTO SC VALUES('98002','3',88);

[例1] 查考试成绩大于等于90的学生的学号。

SELECT DISTINCT Sno

FROM SC

WHERE Grade>=90;

这里使用了DISTINCT短语，当一个学生有多门课程成绩大于等于90时，他的学号也只列一次。执行结果如图4-1所示。

图4-1 在查询分析器中的查询执行情况

[例2] 查年龄大于18，并不是信息系（IS）与数学系（MA）的学生的姓名和性别。

SELECT Sname,Ssex

FROM Student

WHERE Sage>18 AND Sdept NOT IN ('IS', 'MA');

在企业管理器中的执行情况如图4-2所示。

图4-2 在企业管理器中的查询执行情况

[说明]（1）在企业管理器中打开子窗口的方法：先选中数据库→选中表→在任一表上，按鼠标右键→从快捷菜单中选择“打开表”→点击“返回所有行”子菜单项→在打开返回表内容的子窗口后，按企业管理器工具栏上的“SQL”图标，即能把子窗口分为上下两部分，上面部分能输入不同的SQL命令来执行，执行时按企业管理器工具栏上的“运行”图标

即可；（2）限于篇幅，其它查询命令的执行窗口与运行情况类似于上两图，原则将

2 实验4 SQL语言——SELECT查询操作

不再列出。

[例3] 查以”MIS_”开头，且倒数第二个汉字为“导”字的课程的详细情况。

SELECT * FROM Course

WHERE Cname LIKE ’MIS#_%导_’ ESCAPE ’#’;

[例4] 查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno) /* 加DISTINCT 去掉重复值后计数 */

FROM SC;

[例5] 查询计算机系（CS）选修了2门及以上课程的学生的学号。

SELECT

FROM Student,SC

WHERE Sdept='CS' AND =

GROUP BY HAVING COUNT(*)>=2;

[例6] 查询Student表与SC表的广义笛卡尔积。

Select Student.*,SC.*

From Student,SC;

或 Select Student.*,SC.*

From Student Cross Join SC;

[例7] 查询Student表与SC表基于学号SNO的等值连接。

Select *

From Student,SC

WHERE =;

[例8] 查询Student表与SC表基于学号SNO的自然连接。

SELECT , Sname, Ssex, Sage, Sdept, Cno, Grade

FROM Student, SC WHERE =;

或 SELECT , Sname, Ssex, Sage, Sdept, Cno, Grade

FROM Student INNER JOIN SC ON =;

[例9] 查询课程之先修课的先修课(自身连接例)。

SELECT ,

FROM Course FIRST, Course SECOND

WHERE =;

我们为Course表取两个别名FIRST与SECOND，这样就可以在SELECT子句和WHERE子句中的属性名前分别用这两个别名加以区分。

[例10] 查询学生及其课程、成绩等情况（不管是否选课，均需列出学生信息）。

SELECT , Sname, Ssex, Sage, Sdept, Cno, Grade

FROM Student Left Outer JOIN SC ON =;

3 数据库原理与应用实验指导

[例11] 查询学生及其课程成绩与课程及其学生选修成绩的明细情况（要求学生与课程均需全部列出）。

SELECT , Sname, Ssex, Sage, Sdept, , Grade, cname, cpno,

ccredit

FROM Student Left Outer JOIN SC ON = Full Outer join Course

on =;

[例12] 查询性别为男、课程成绩及格的学生信息及课程号、成绩。

SELECT Student.*,Cno,Grade

FROM STUDENT INNER JOIN ON =

WHERE SSEX=’男’ AND GRADE >=60

[例13] 查询与“钱横”在同一个系学习的学生信息。

SELECT * FROM Student

WHERE Sdept IN

(SELECT Sdept

FROM Student

WHERE Sname='钱横');

或 SELECT * FROM Student

WHERE Sdept =

( SELECT Sdept

FROM Student

WHERE Sname='钱横'); -- 当子查询为单列单行值时可以用“=”

或 SELECT S1.*

FROM Student S1,Student S2

WHERE = AND ='钱横';

一般来说，连接查询可以替换大多数的嵌套子查询。

SQL-92支持“多列成员”的属于(IN)条件表达，例：

[例14] 找出同系、同年龄、同性别的学生。

Select * from Student as T

Where (,,) IN

(Select sdept,sage,ssex

From student as S

Where <>); -- SQL Server 2000可能不支持

它等价于逐个成员IN的方式表达，如下（能在SQL Server 2000中执行）：

Select * from Student T

Where IN

( Select sdept

From student S

Where <> and

IN

( Select sage

4 实验4 SQL语言——SELECT查询操作

From student X

Where = and <> and

IN

( Select ssex

From student Y

Where = and <>)));

[例15] 查询选修了课程名为“数据库系统”的学生学号、姓名和所在系。

SELECT Sno,Sname,Sdept FROM Student -- IN嵌套查询方法

WHERE Sno IN

( SELECT Sno FROM SC

WHERE Cno IN

( SELECT Cno FROM Course WHERE Cname='数据库系统'));

或 SELECT Sno,Sname,Sdept FROM Student -- IN、= 嵌套查询方法

WHERE Sno IN

( SELECT Sno FROM SC

WHERE Cno=

( SELECT Cno FROM Course WHERE Cname='数据库系统'));

或 SELECT ,Sname,Sdept --连接查询方法

FROM Student,SC,Course

WHERE = AND =

AND ='数据库系统';

或 Select Sno,Sname,Sdept From Student -- Exists嵌套查询方法

Where Exists( Select * From SC

Where = And

Exists( Select * From Course

Where = And Cname='数据库系统'));

或 Select Sno,Sname,Sdept From Student -- Exists嵌套查询方法

Where Exists( Select * From course

Where Cname='数据库系统' and

Exists( Select * From SC

Where = and = ));

[例16] 检索至少不学2和4两门课程的学生学号与姓名。

SELECT Sno,Sname FROM Student

WHERE Sno NOT IN

(SELECT Sno FROM SC WHERE Cno IN ('2','4'));

[例17] 查询其他系中比信息系IS所有学生年龄均大的学生名单,并排序输出。

SELECT Sname FROM Student

WHERE Sage>All(SELECT Sage FROM Student

WHERE Sdept='IS') AND Sdept <> 'IS'

ORDER BY Sname;

本查询实际上也可以用集函数实现：

5 数据库原理与应用实验指导

SELECT Sname FROM Student

WHERE Sage>(SELECT MAX(Sage) FROM Student

WHERE Sdept='IS') AND Sdept<>'IS'

ORDER BY Sname;

[例18] 查询哪些课程只有女生选读。(本题有多于两种表达法)

SELECT DISTINCT CNAME

FROM COURSE C

WHERE '女'=ALL( SELECT SSEX FROM SC,STUDENT

WHERE = AND =);

或 SELECT DISTINCT CNAME FROM COURSE C

WHERE NOT EXISTS

( SELECT * FROM SC,STUDENT

WHERE = AND = AND ='男');

[例19] 查询所有未修1号课程的学生姓名。

SELECT Sname FROM Student

WHERE NOT EXISTS

( SELECT * FROM SC WHERE Sno= AND Cno='1');

或 SELECT Sname FROM Student

WHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno='1');

但如下是错的：

SELECT Sname FROM Student,SC

WHERE = AND Cno<>'1';

[例20] 查询选修了全部课程的学生姓名(为了有查询结果，自己可调整一些表的)。

SELECT Sname FROM Student

WHERE NOT EXISTS

( SELECT * FROM Course

WHERE NOT EXISTS

(SELECT * FROM SC WHERE Sno= AND Cno=));

由于没有全称量词，我们将题目的意思转换成等价的存在量词的形式：查询这样的学生姓名没有一门课程是他不选的。

本题的另一操作方法是：

SELECT Sname FROM Student,SC WHERE =

Group by ,Sname having count(*)>=(SELECT count(*) FROM Course);

[例21] 查询至少选修了学生98001选修的全部课程的学生号码。

本题的查询要求可以做如下解释，不存在这样的课程y，学生98001选修了y，而要查询的学生x没有选。写成的SELECT语句为：

SELECT Sno

FROM Student SX

WHERE NOT EXISTS

6 实验4 SQL语言——SELECT查询操作

( SELECT * FROM SC SCY

WHERE ='98001' AND

NOT EXISTS

( SELECT * FROM SC SCZ

WHERE = AND =));

[例22] 查询选修了课程1或者选修了课程2的学生学号集。

SELECT Sno FROM SC WHERE Cno='1'

UNION

SELECT Sno FROM SC WHERE Cno='2';

[注意] 扩展的SQL中有集合操作并(UNION)、集合操作交(INTERSECT)和集合操作差(EXCEPT或MINUS)等。SQL的集合操作要求相容即属性个数、类型必须一致，属性名无关，最终结果集采用第一个结果的属性名，缺省为自动去除重复元组，各子查询不带Order By，Order By放在整个语句的最后。

[例23] 查询计算机科学系的学生与年龄不大于19岁的学生的交集。

SELECT * FROM Student WHERE Sdept='CS'

INTERSECT

SELECT * FROM Student WHERE Sage<=19; -- SQL Server 2000不支持运行

本查询等价于“查询计算机科学系中年龄不大于19岁的学生。”，为此变通法为：

SELECT * FROM Student WHERE Sdept='CS' AND Sage<=19;

[例24] 查询选修课程1的学生集合与选修课程2的学生集合的差集。

SELECT Sno FROM SC WHERE Cno='1'

EXCEPT(或MINUS)

SELECT Sno FROM SC WHERE Cno='2'; -- SQL Server 2000不支持运行

本例实际上是查询选修了课程1但没有选修课程2的学生。为此变通法为：

SELECT Sno FROM SC

WHERE Cno='1' AND Sno NOT IN

(SELECT Sno FROM SC WHERE Cno='2');

[例25] 查询平均成绩大于85分的学号、姓名、平均成绩。

Select stu_no,sname,avgr

From Student,( Select sno,avg(grade) From SC

Group By sno) as SG(stu_no,avgr)

Where =_no And avgr>85;

SQL-92允许在 From中使用查询表达式，并必须为查询表达式取名。

它等价于如下未使用查询表达式的形式：

Select ,Sname,AVG(Grade)

From Student,SC Where =

Group By ,Sname HAVING AVG(Grade)>85;

[例26] 查出课程成绩在90分以上的女学生的姓名、课程名和成绩。

7 数据库原理与应用实验指导

SELECT SNAME,CNAME,GRADE

FROM (SELECT SNAME,CNAME,GRADE

FROM STUDENT,SC,COURSE

WHERE SSEX='女' AND = AND =)

AS TEMP(SNAME,CNAME,GRADE)

WHERE GRADE>90; -- 特意用查询表达式实现，完全可用其它方式实现

但如下使用查询表达式的查询，则不易改写为其它形式。

[例27] 查询各不同平均成绩所对应的学生人数(给出平均成绩与其对应的人数)。

Select avgr,COUNT(*)

From (Select sno,avg(grade) From SC

Group By sno) as SG(Sno,avgr)

Group By avgr;

[例28] 查出学生、课程及成绩的明细信息及课程门数、总成绩及平均成绩。

SELECT sno,cno,grade -- COMPUTE的其它使用方法，请参阅帮助。

FROM sc

ORDER BY sno

COMPUTE count(cno),SUM(grade),avg(grade) BY sno

[例29] 建立信息系学生的视图（含有学号、姓名、年龄及性别），并要求进行修改和插入操作时仍须保证该视图只有信息系的学生。通过视图查找年龄大于等于18岁的女学生。

CREATE VIEW IS_Student

AS SELECT Sno,Sname,Sage,Ssex

FROM Student

WHERE Sdept='IS' WITH CHECK OPTION;

SELECT * FROM IS_Student WHERE Sage>=18 AND Ssex='女';

[例30] 设有学生-课程关系数据库，其数据库关系模式为：

学生S(学号S#，姓名SN，所在系SD，年龄SA)、课程C(课程号C#,课程名称CN,先修课号PC#)、学生选课SC(学号S#,课程号C#,成绩G)。

试用SQL语言分别写出下列查询（只需写出SQL命令）：

(1) 求学生'98001'(为学号)所选的成绩为60以上的课程号。

(2) 求选读了“数据库概论”，并成绩为80或90的学生学号和姓名。

(3) 求选修了全部课程的学生学号、姓名及其所在系名。

(4) 找出没有学生选修的课程号及课程名称。

(5) 列出选课数超过3门的学生学号、其所修课程数及平均成绩。

(6) 删除“数据结构”课程及所有对它的选课情况。

解：

(1) SELECT C# FROM SC WHERE S#='98001' AND G>=60;

(2) SELECT S#,SN FROM S,SC,C

WHERE C.C#= SC.C# AND SC.S#=S.S# AND ='数据库概论'

8 实验4 SQL语言——SELECT查询操作

AND (G=90 OR G=80);

(3) SELECT S#,SN,SD FROM S

WHERE NOT EXISTS(SELECT * FROM C X WHERE NOT EXISTS

(SELECT * FROM SC Y WHERE Y.C#= X.C# AND Y.S#=S.S#));

(4) SELECT C#,CN FROM C WHERE C.C# NOT IN (SELECT SC.C# FROM SC);

(5) SELECT S#,COUNT(C#),AVG(G) FROM SC GROUP BY S# HAVING COUNT(C#)>3;

(6) DELETE FROM SC

WHERE SC.C# IN (SELECT C.C# FROM C WHERE CN='数据结构');

DELETE FROM C WHERE CN='数据结构';

9

2023年6月21日发(作者：)

实验4 SQL语言——SELECT查询操作

实实验验44

SSQQLL语语言言————SSEELLEECCTT查查询询操操作作

实验示例

实验示例中要使用包括如下三个表的“教学管理”数据库JXGL：

(1)学生表Student，由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记作：Student(Sno,Sname,Ssex,Sage,Sdept)，其中主码为Sno。

(2)课程表Course，由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记作：Course(Cno,Cname,Cpno,Ccredit)，其中主码为Cno。

(3)学生选课SC，由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记作：SC(Sno,Cno,Grade)，其中主码为(SNO,CNO)。

1、在SQL SERVER查询分析器或企业管理器(以具有相应操作权限的某用户登录)的SQL操作窗口中执行如下命令创建数据库。需要说明的是不同数据库系统其创建数据库的命令或方式有所不同。

CREATE DATABASE JXGL

2、刷新数据库目录后，选择新出现的JXGL数据库，在SQL操作窗口中，创建Student、SC、Course三表及表记录插入命令如下：

Create Table Student

( Sno CHAR(5) NOT NULL PRIMARY KEY(Sno),

Sname VARCHAR(20),

Sage SMALLINT CHECK(Sage>=15 AND Sage<=45),

Ssex CHAR(2) DEFAULT '男' CHECK (Ssex='男' OR Ssex='女'),

Sdept CHAR(2));

Create Table Course

( Cno CHAR(2) NOT NULL PRIMARY KEY(Cno),

Cname VARCHAR(20),

Cpno CHAR(2),

Ccredit SMALLINT);

Create Table SC

( Sno CHAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES Student(Sno),

Cno CHAR(2) NOT NULL,

Grade SMALLINT CHECK ((Grade IS NULL) OR (Grade BETWEEN 0 AND 100)),

PRIMARY KEY(Sno,Cno),

1 数据库原理与应用实验指导

FOREIGN KEY(Cno) REFERENCES Course(Cno));

INSERT INTO Student VALUES('98001','钱横',18,'男','CS');

INSERT INTO Student VALUES('98002','王林',19,'女','CS');

INSERT INTO Student VALUES('98003','李民',20,'男','IS');

INSERT INTO Student VALUES('98004','赵三',16,'女','MA');

INSERT INTO Course VALUES('1','数据库系统', '5',4);

INSERT INTO Course VALUES('2','数学分析',null ,2);

INSERT INTO Course VALUES('3','信息系统导论','1',3);

INSERT INTO Course VALUES('4','操作系统原理','6',3);

INSERT INTO Course VALUES('5','数据结构','7',4);

INSERT INTO Course VALUES('6','数据处理基础',null,4);

INSERT INTO Course VALUES('7','C语言','6',3);

INSERT INTO SC VALUES('98001','1',87);

INSERT INTO SC VALUES('98001','2',67);

INSERT INTO SC VALUES('98001','3',90);

INSERT INTO SC VALUES('98002','2',95);

INSERT INTO SC VALUES('98002','3',88);

[例1] 查考试成绩大于等于90的学生的学号。

SELECT DISTINCT Sno

FROM SC

WHERE Grade>=90;

这里使用了DISTINCT短语，当一个学生有多门课程成绩大于等于90时，他的学号也只列一次。执行结果如图4-1所示。

图4-1 在查询分析器中的查询执行情况

[例2] 查年龄大于18，并不是信息系（IS）与数学系（MA）的学生的姓名和性别。

SELECT Sname,Ssex

FROM Student

WHERE Sage>18 AND Sdept NOT IN ('IS', 'MA');

在企业管理器中的执行情况如图4-2所示。

图4-2 在企业管理器中的查询执行情况

[说明]（1）在企业管理器中打开子窗口的方法：先选中数据库→选中表→在任一表上，按鼠标右键→从快捷菜单中选择“打开表”→点击“返回所有行”子菜单项→在打开返回表内容的子窗口后，按企业管理器工具栏上的“SQL”图标，即能把子窗口分为上下两部分，上面部分能输入不同的SQL命令来执行，执行时按企业管理器工具栏上的“运行”图标

即可；（2）限于篇幅，其它查询命令的执行窗口与运行情况类似于上两图，原则将

2 实验4 SQL语言——SELECT查询操作

不再列出。

[例3] 查以”MIS_”开头，且倒数第二个汉字为“导”字的课程的详细情况。

SELECT * FROM Course

WHERE Cname LIKE ’MIS#_%导_’ ESCAPE ’#’;

[例4] 查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno) /* 加DISTINCT 去掉重复值后计数 */

FROM SC;

[例5] 查询计算机系（CS）选修了2门及以上课程的学生的学号。

SELECT

FROM Student,SC

WHERE Sdept='CS' AND =

GROUP BY HAVING COUNT(*)>=2;

[例6] 查询Student表与SC表的广义笛卡尔积。

Select Student.*,SC.*

From Student,SC;

或 Select Student.*,SC.*

From Student Cross Join SC;

[例7] 查询Student表与SC表基于学号SNO的等值连接。

Select *

From Student,SC

WHERE =;

[例8] 查询Student表与SC表基于学号SNO的自然连接。

SELECT , Sname, Ssex, Sage, Sdept, Cno, Grade

FROM Student, SC WHERE =;

或 SELECT , Sname, Ssex, Sage, Sdept, Cno, Grade

FROM Student INNER JOIN SC ON =;

[例9] 查询课程之先修课的先修课(自身连接例)。

SELECT ,

FROM Course FIRST, Course SECOND

WHERE =;

我们为Course表取两个别名FIRST与SECOND，这样就可以在SELECT子句和WHERE子句中的属性名前分别用这两个别名加以区分。

[例10] 查询学生及其课程、成绩等情况（不管是否选课，均需列出学生信息）。

SELECT , Sname, Ssex, Sage, Sdept, Cno, Grade

FROM Student Left Outer JOIN SC ON =;

3 数据库原理与应用实验指导

[例11] 查询学生及其课程成绩与课程及其学生选修成绩的明细情况（要求学生与课程均需全部列出）。

SELECT , Sname, Ssex, Sage, Sdept, , Grade, cname, cpno,

ccredit

FROM Student Left Outer JOIN SC ON = Full Outer join Course

on =;

[例12] 查询性别为男、课程成绩及格的学生信息及课程号、成绩。

SELECT Student.*,Cno,Grade

FROM STUDENT INNER JOIN ON =

WHERE SSEX=’男’ AND GRADE >=60

[例13] 查询与“钱横”在同一个系学习的学生信息。

SELECT * FROM Student

WHERE Sdept IN

(SELECT Sdept

FROM Student

WHERE Sname='钱横');

或 SELECT * FROM Student

WHERE Sdept =

( SELECT Sdept

FROM Student

WHERE Sname='钱横'); -- 当子查询为单列单行值时可以用“=”

或 SELECT S1.*

FROM Student S1,Student S2

WHERE = AND ='钱横';

一般来说，连接查询可以替换大多数的嵌套子查询。

SQL-92支持“多列成员”的属于(IN)条件表达，例：

[例14] 找出同系、同年龄、同性别的学生。

Select * from Student as T

Where (,,) IN

(Select sdept,sage,ssex

From student as S

Where <>); -- SQL Server 2000可能不支持

它等价于逐个成员IN的方式表达，如下（能在SQL Server 2000中执行）：

Select * from Student T

Where IN

( Select sdept

From student S

Where <> and

IN

( Select sage

4 实验4 SQL语言——SELECT查询操作

From student X

Where = and <> and

IN

( Select ssex

From student Y

Where = and <>)));

[例15] 查询选修了课程名为“数据库系统”的学生学号、姓名和所在系。

SELECT Sno,Sname,Sdept FROM Student -- IN嵌套查询方法

WHERE Sno IN

( SELECT Sno FROM SC

WHERE Cno IN

( SELECT Cno FROM Course WHERE Cname='数据库系统'));

或 SELECT Sno,Sname,Sdept FROM Student -- IN、= 嵌套查询方法

WHERE Sno IN

( SELECT Sno FROM SC

WHERE Cno=

( SELECT Cno FROM Course WHERE Cname='数据库系统'));

或 SELECT ,Sname,Sdept --连接查询方法

FROM Student,SC,Course

WHERE = AND =

AND ='数据库系统';

或 Select Sno,Sname,Sdept From Student -- Exists嵌套查询方法

Where Exists( Select * From SC

Where = And

Exists( Select * From Course

Where = And Cname='数据库系统'));

或 Select Sno,Sname,Sdept From Student -- Exists嵌套查询方法

Where Exists( Select * From course

Where Cname='数据库系统' and

Exists( Select * From SC

Where = and = ));

[例16] 检索至少不学2和4两门课程的学生学号与姓名。

SELECT Sno,Sname FROM Student

WHERE Sno NOT IN

(SELECT Sno FROM SC WHERE Cno IN ('2','4'));

[例17] 查询其他系中比信息系IS所有学生年龄均大的学生名单,并排序输出。

SELECT Sname FROM Student

WHERE Sage>All(SELECT Sage FROM Student

WHERE Sdept='IS') AND Sdept <> 'IS'

ORDER BY Sname;

本查询实际上也可以用集函数实现：

5 数据库原理与应用实验指导

SELECT Sname FROM Student

WHERE Sage>(SELECT MAX(Sage) FROM Student

WHERE Sdept='IS') AND Sdept<>'IS'

ORDER BY Sname;

[例18] 查询哪些课程只有女生选读。(本题有多于两种表达法)

SELECT DISTINCT CNAME

FROM COURSE C

WHERE '女'=ALL( SELECT SSEX FROM SC,STUDENT

WHERE = AND =);

或 SELECT DISTINCT CNAME FROM COURSE C

WHERE NOT EXISTS

( SELECT * FROM SC,STUDENT

WHERE = AND = AND ='男');

[例19] 查询所有未修1号课程的学生姓名。

SELECT Sname FROM Student

WHERE NOT EXISTS

( SELECT * FROM SC WHERE Sno= AND Cno='1');

或 SELECT Sname FROM Student

WHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno='1');

但如下是错的：

SELECT Sname FROM Student,SC

WHERE = AND Cno<>'1';

[例20] 查询选修了全部课程的学生姓名(为了有查询结果，自己可调整一些表的)。

SELECT Sname FROM Student

WHERE NOT EXISTS

( SELECT * FROM Course

WHERE NOT EXISTS

(SELECT * FROM SC WHERE Sno= AND Cno=));

由于没有全称量词，我们将题目的意思转换成等价的存在量词的形式：查询这样的学生姓名没有一门课程是他不选的。

本题的另一操作方法是：

SELECT Sname FROM Student,SC WHERE =

Group by ,Sname having count(*)>=(SELECT count(*) FROM Course);

[例21] 查询至少选修了学生98001选修的全部课程的学生号码。

本题的查询要求可以做如下解释，不存在这样的课程y，学生98001选修了y，而要查询的学生x没有选。写成的SELECT语句为：

SELECT Sno

FROM Student SX

WHERE NOT EXISTS

6 实验4 SQL语言——SELECT查询操作

( SELECT * FROM SC SCY

WHERE ='98001' AND

NOT EXISTS

( SELECT * FROM SC SCZ

WHERE = AND =));

[例22] 查询选修了课程1或者选修了课程2的学生学号集。

SELECT Sno FROM SC WHERE Cno='1'

UNION

SELECT Sno FROM SC WHERE Cno='2';

[注意] 扩展的SQL中有集合操作并(UNION)、集合操作交(INTERSECT)和集合操作差(EXCEPT或MINUS)等。SQL的集合操作要求相容即属性个数、类型必须一致，属性名无关，最终结果集采用第一个结果的属性名，缺省为自动去除重复元组，各子查询不带Order By，Order By放在整个语句的最后。

[例23] 查询计算机科学系的学生与年龄不大于19岁的学生的交集。

SELECT * FROM Student WHERE Sdept='CS'

INTERSECT

SELECT * FROM Student WHERE Sage<=19; -- SQL Server 2000不支持运行

本查询等价于“查询计算机科学系中年龄不大于19岁的学生。”，为此变通法为：

SELECT * FROM Student WHERE Sdept='CS' AND Sage<=19;

[例24] 查询选修课程1的学生集合与选修课程2的学生集合的差集。

SELECT Sno FROM SC WHERE Cno='1'

EXCEPT(或MINUS)

SELECT Sno FROM SC WHERE Cno='2'; -- SQL Server 2000不支持运行

本例实际上是查询选修了课程1但没有选修课程2的学生。为此变通法为：

SELECT Sno FROM SC

WHERE Cno='1' AND Sno NOT IN

(SELECT Sno FROM SC WHERE Cno='2');

[例25] 查询平均成绩大于85分的学号、姓名、平均成绩。

Select stu_no,sname,avgr

From Student,( Select sno,avg(grade) From SC

Group By sno) as SG(stu_no,avgr)

Where =_no And avgr>85;

SQL-92允许在 From中使用查询表达式，并必须为查询表达式取名。

它等价于如下未使用查询表达式的形式：

Select ,Sname,AVG(Grade)

From Student,SC Where =

Group By ,Sname HAVING AVG(Grade)>85;

[例26] 查出课程成绩在90分以上的女学生的姓名、课程名和成绩。

7 数据库原理与应用实验指导

SELECT SNAME,CNAME,GRADE

FROM (SELECT SNAME,CNAME,GRADE

FROM STUDENT,SC,COURSE

WHERE SSEX='女' AND = AND =)

AS TEMP(SNAME,CNAME,GRADE)

WHERE GRADE>90; -- 特意用查询表达式实现，完全可用其它方式实现

但如下使用查询表达式的查询，则不易改写为其它形式。

[例27] 查询各不同平均成绩所对应的学生人数(给出平均成绩与其对应的人数)。

Select avgr,COUNT(*)

From (Select sno,avg(grade) From SC

Group By sno) as SG(Sno,avgr)

Group By avgr;

[例28] 查出学生、课程及成绩的明细信息及课程门数、总成绩及平均成绩。

SELECT sno,cno,grade -- COMPUTE的其它使用方法，请参阅帮助。

FROM sc

ORDER BY sno

COMPUTE count(cno),SUM(grade),avg(grade) BY sno

[例29] 建立信息系学生的视图（含有学号、姓名、年龄及性别），并要求进行修改和插入操作时仍须保证该视图只有信息系的学生。通过视图查找年龄大于等于18岁的女学生。

CREATE VIEW IS_Student

AS SELECT Sno,Sname,Sage,Ssex

FROM Student

WHERE Sdept='IS' WITH CHECK OPTION;

SELECT * FROM IS_Student WHERE Sage>=18 AND Ssex='女';

[例30] 设有学生-课程关系数据库，其数据库关系模式为：

学生S(学号S#，姓名SN，所在系SD，年龄SA)、课程C(课程号C#,课程名称CN,先修课号PC#)、学生选课SC(学号S#,课程号C#,成绩G)。

试用SQL语言分别写出下列查询（只需写出SQL命令）：

(1) 求学生'98001'(为学号)所选的成绩为60以上的课程号。

(2) 求选读了“数据库概论”，并成绩为80或90的学生学号和姓名。

(3) 求选修了全部课程的学生学号、姓名及其所在系名。

(4) 找出没有学生选修的课程号及课程名称。

(5) 列出选课数超过3门的学生学号、其所修课程数及平均成绩。

(6) 删除“数据结构”课程及所有对它的选课情况。

解：

(1) SELECT C# FROM SC WHERE S#='98001' AND G>=60;

(2) SELECT S#,SN FROM S,SC,C

WHERE C.C#= SC.C# AND SC.S#=S.S# AND ='数据库概论'

8 实验4 SQL语言——SELECT查询操作

AND (G=90 OR G=80);

(3) SELECT S#,SN,SD FROM S

WHERE NOT EXISTS(SELECT * FROM C X WHERE NOT EXISTS

(SELECT * FROM SC Y WHERE Y.C#= X.C# AND Y.S#=S.S#));

(4) SELECT C#,CN FROM C WHERE C.C# NOT IN (SELECT SC.C# FROM SC);

(5) SELECT S#,COUNT(C#),AVG(G) FROM SC GROUP BY S# HAVING COUNT(C#)>3;

(6) DELETE FROM SC

WHERE SC.C# IN (SELECT C.C# FROM C WHERE CN='数据结构');

DELETE FROM C WHERE CN='数据结构';

9