实验5_SQL语言之高级查询

实验五高级查询

姓名：学号：

专业：班级：

同组人：实验日期：

【实验目的与要求】

1、熟练掌握IN子查询

2、熟练掌握比较子查询（尤其要注意ANY、ALL谓词如何用集函数代替）

3、熟练掌握EXISTS子查询（尤其是如何将全称量词和逻辑蕴含用EXISTS谓词代替）

4、熟练掌握复杂查询的select语句

【实验准备】

1．准备好测试数据

2．熟悉多表查询与嵌套查询的用法。

【实验内容】

5.1.准备工作

1.创建测试用数据库XSGL，并在其中创建三个表

本实验需用到student、course和SC表，其结构和约束如下：

Student表结构及其约束为：

表5-1 student表结构和约束

列名称类型宽度允许空值缺省值主键说明

Sno char 8 否是学号

Sname varchar 8 否学生姓名Sex char 2 否男性别

Birth datetime 否出生年月Classno char 3 否班级号Entrance_date datetime 否入学时间Home_addr varchar 40 是家庭地址

Course表结构及其约束为：

表5-2 course表结构和约束

列名称类型宽度允许空值缺省值主键说明

cno Char 3 否是课程号Cname varchar 20 否课程名称Total_perior int 是总学时

credit int 是学分

SC表结构及其约束为：

表5-3 SC表结构和约束

列名称类型宽度允许空值缺省值主键外键说明

sno Char 8 否是学号,参照student表

cno char 3 否是课程号,参照course表

grade int 是否成绩其中成绩为百分制。

2.对表添加、修改、删除数据

向student表中插入如下数据:

表5-4 student表

Sno sname sex birth classno Entrance_date Home_addr sdept postcode 20050001 张虹男1984/09/011 051 2005/09/01 南京CS 200413 20050002 林红女1983/11/12 051 2005/09/01 北京CS 100010 20050003 赵青男1982/05/11 051 2005/09/01 上海MA 200013

向course表中插入数据:

表5-5 course表

cno Cname Total_perior credit

001 高数68 3

002 C语言程序设计75 4

003 JAVA语言程序设计68 3

向SC表中插入数据:

表5-6 SC表

Sno Cno grade

20050001 001 89

20050001 002 78

20050001 003 89

20050002 002 60

20050003 001 80

为达到更好的测试效果，请自行向数据库表中添加其它数据，使表中数据量达10条以

上，并使每个字段值表现出多样性。

5.2.复杂查询

（1）查询比“林红”年纪大的男学生信息。

SQL语句：

select*

from Student

where Sex ='男'and

Birth-(select Birth from Student where Sname ='林红')<0;

（2）检索所有学生的选课信息。

SQL语句：

select Student.Sno,Sname,Sex,Classno,Cname

from Student,SC,Course

where Student.Sno=SC.Sno

and https://www.360docs.net/doc/fc1843753.html,o=https://www.360docs.net/doc/fc1843753.html,o;

（3）查询已选课学生的学号、姓名、课程名、成绩。

连接查询T—SQL语句：

select Student.Sno,Sname,Cname,grade

from SC,Course,Student

where SC.Sno=Student.Sno and

https://www.360docs.net/doc/fc1843753.html,o=https://www.360docs.net/doc/fc1843753.html,o;

（4）查询选修了“C语言程序设计”的学生的学号和姓名。

SQL语句：

select Student.Sno,Student.Sname

from SC,Course,Student

where SC.Sno=Student.Sno and

https://www.360docs.net/doc/fc1843753.html,o=https://www.360docs.net/doc/fc1843753.html,o and

Cname='C语言程序设计';

（5）查询与“张虹”在同一个班级的学生学号、姓名、家庭住址。

（子查询）SQL语句：

select Classno from Student

where Sname='张虹'

连接查询SQL语句：

select Sno,Sname,Home_addr,Classno

from Student

where Classno=(select Classno from Student where Sname='张虹');

（6）查询其他班级中比“051”班任一学生年龄大的学生的学号、姓名。

带有ANY或ALL谓词的子查询语句：

use XSGL

select Sno,Sname

from Student

where Classno <>'051'

and Birth

where Classno ='051');

用聚合函数实现：

use XSGL

select Sno,Sname

from Student

where Classno <>'051'

and Birth <(select min(Birth)from Student

where Classno ='051');

（7）查询选修了全部课程的学生姓名。

SQL语句：

select Sname

from Student

where not exists(

select*

from Course

where not exists(

select*

from SC

where Sno=Student.sno

and cno=https://www.360docs.net/doc/fc1843753.html,o));

（8）查询至少选修了学生“20050002”选修的全部课程的学生的学号，姓名。

SQL语句：

select distinct Sno

from SC SCX

where not exists(

select*

from SC SCY

where SCY.Sno='20050002'

and not exists(

select*from SC SCZ

where SCZ.Sno=SCX.Sno and

https://www.360docs.net/doc/fc1843753.html,o=https://www.360docs.net/doc/fc1843753.html,o));

（9）检索学生的学号、姓名、学习课程名及课程成绩。

SQL语句：

select Student.Sno,Sname,Cname,grade

from Student left outer join SC on(Student.Sno=SC.Sno),Course

where https://www.360docs.net/doc/fc1843753.html,o=https://www.360docs.net/doc/fc1843753.html,o

union

select Student.Sno,Sname,Cname=null,grade=null

from Student,Course

where Student.Sno not in(

select Student.Sno from Student,SC

where Student.Sno=SC.Sno)

（10）检索选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩，并按成绩从高到低次序排列。

SQL语句：

use XSGL

select distinct(SC.Sno),cno,grade

from SC,Student

where(

select grade from SC

where cno='001'and Student.Sno=SC.Sno)>

(select grade from SC

where cno='002'and Student.Sno=SC.Sno)

and Student.Sno=SC.Sno

order by cno

（11）检索选修3门以上课程的学生的学号、总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。

SQL语句：

use XSGL

select SCA.Sno,sum(grade)as总成绩

from SC SCA

where SCA.Sno in

(

select SCB.Sno

from SC SCB

group by Sno

having count(Sno)>3

)

and SCA.grade>3

group by SCA.Sno

（12）检索出每位学生的学号、姓名、未修课程名。

SQL语句：

select Sno,Sname,Cname

from Student,Course

except

select SC.Sno,Sname,Cname

from SC,Student,Course

where SC.Sno=Student.Sno

and https://www.360docs.net/doc/fc1843753.html,o=https://www.360docs.net/doc/fc1843753.html,o

（13）检索多于3名学生选修的并以3结尾的课程号的平均成绩。

SQL语句：

use XSGL

select cno,avg(grade) as 平均成绩

from SC

group by cno

having count(cno)>3

（14）检索最高分与最低分之差大于5分的学生的学号、姓名、最高分、最底分。

SQL语句：

select Student.Sno,Sname,max(grade) as 最高分,min(grade) as 最低分

from Student inner join SC

on Student.Sno=SC.Sno

group by Student.Sno,Sname

having max(grade)-min(grade)>5

（15）查询选修课程‘001’的学生集合与选修课程‘002’的学生集合的交集SQL语句：

select Sno

from SC

where cno='001'

intersect

select Sno

from SC

where cno='002'

（16）查询选修课程‘001’的学生集合与选修课程‘002’的学生集合的差集SQL语句：

select Sno

from SC

where cno='001'

except

select Sno

from SC

where cno='002'

5.3.嵌套子查询

以下实验在前面实验中创建的CPXS数据库中完成，请根据前面实验创建的表结构和数据，完成如下嵌套查询：

查询在2004年3月18日没有销售的产品名称（不允许重复）。

用IN子查询：

写出对应SQL语句并给出查询结果：

select 产品名称

from CP

where 产品编号 not in

( select CPXSB.产品编号

from CPXSB

where 销售日期='2004-3-18'

)

用EXISTS子查询：

写出对应SQL语句并给出查询结果：

use CPXS

select 产品名称

from CP

where not exists

( select 产品编号

from CPXSB

where 销售日期='2004-3-18'

and CP.产品编号=CPXSB.产品编号

)

查询名称为“家电市场”的客户在2004年3月18日购买的产品名称和数量。

用IN子查询：

写出对应SQL语句并给出查询结果：

select 产品名称,数量

from CPXSB,CP

where 客户编号 in

(

select 客户编号

from XSS

where 客户名称='家电市场'

)

and 销售日期='2004-3-18'

and CP.产品编号=CPXSB.产品编号;

用EXISTS子查询：

写出对应SQL语句并给出查询结果：

use CPXS

select产品名称,数量

from CPXSB A,CP B,XSS C

where exists

(

select*

from CPXSB,XSS

where C.客户名称='家电市场'

and A.销售日期='2004-3-18'

)

and A.产品编号=B.产品编号

and C.客户编号=A.客户编号

查询销售量大于所有2004年3月18日销售的各产品销售数量的产品编号。

用ALL谓词：

写出对应SQL语句并给出查询结果：

select产品编号

from CPXSB

where数量>all

(

select数量

from CPXSB

where销售日期='2004-3-18'

)

用集函数：

写出对应SQL语句并给出查询结果：

select产品编号

from CPXSB

where数量>

(

select max(数量)

from CPXSB

where销售日期='2004-3-18'

)

查询购买了所有产品的客户的名称。

写出对应SQL语句并给出查询结果：

select distinct(客户名称)

from XSS,CPXSB

where CPXSB.客户编号in

(

select客户编号

from CPXSB

group by客户编号

having count(*)=

(select count(*)from CP)

)

and CPXSB.客户编号=XSS.客户编号

查询购买了客户编号为“000001”的客户购买的所有产品的客户的名称。

写出对应SQL语句并给出查询结果：

select distinct(客户名称)

from XSS,CPXSB

where产品编号in

(

select产品编号

from CPXSB

where客户编号='000001'

)

and CPXSB.客户编号=XSS.客户编号

5.4.集合操作

1. 准备工作

创建如下两表X和Y，并添加相应的值

图5-1 测试表X和Y 阅读并执行以下语句，理解其功能，给出运行结果。2. 集合并：union

执行以下语句：

请给出运行结果：

3. 集合的差：except:

执行以下语句：

请给出运行结果：

4. 集合交：intersect:

执行以下语句：

请给出运行结果：