实验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 birth<

(

select birth

from Student

where Sname='林虹'

)and Sex='男';

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

SQL语句：

select Sno,Sname,Cno,Cname

from Student,Course

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

连接查询T—SQL语句：

select distinct Student.Sno,Sname,Cname,grade

from Student,Course,SC

where SC.Sno=Student.Sno and https://www.360docs.net/doc/7a7179015.html,o=https://www.360docs.net/doc/7a7179015.html,o

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

SQL语句：

select distinct Student.Sno,Sname

from Student,Course,SC

where https://www.360docs.net/doc/7a7179015.html,ame='C语言程序设计'

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

（子查询）SQL语句：

(select classno

from Student

where Sname='张虹'

);

连接查询SQL语句：

select distinct Student.Sno,Sname,Home_addr

from Student,Course,SC

where classno=

(select classno

from Student

where Sname='张虹'

);

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

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

select Sno,Sname

from Student

where birth< any(select birth

from Student

where classno='051')

and classno<>051;

用聚合函数实现：

select Sno,Sname

from Student

where birth< (select MIN(birth)

from Student

where classno='051')

and classno<>051;

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

SQL语句：

select Sname

from Student

where not exists

(

select *

from Course

where not exists

(select *

from SC

where Student.Sno=Sno and https://www.360docs.net/doc/7a7179015.html,o=Cno

)

);

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

SQL语句：

select Sname,Sno

from Student

where Sno IN

(

select DISTINCT Sno

from SC x

where not exists

(

select Sno,Cno

from SC y

where y.Sno='20050002' and

not exists

(

select Cno,Sno

from SC z

where z.Sno=x.Sno

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

)

)

);

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

SQL语句：

select distinct Student.Sno,Sname,Cname,grade

from Student,SC,Course

where SC.Sno=Student.Sno and https://www.360docs.net/doc/7a7179015.html,o=https://www.360docs.net/doc/7a7179015.html,o

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

SQL语句：

select Sno,Cno,grade

from SC

where grade>

(

select MAX(grade)

from SC

where Cno='002'

) and Cno=

(

select cno

from Course

where Cname='高数'

)

order by grade desc;

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

SQL语句：

select Sno,SUM(grade) as 'total_grade'

from SC

where grade>=60

group by Sno

having Sno in

(

select Sno

from SC

group by Sno

having COUNT(Cno)>3

)

order by SUM(grade) desc;

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

SQL语句：

select Sno,Sname,Cname as 'Untake_course'

from Student,Course

where not exists

(

select Sno,Cno

from SC

where Sno=Student.Sno and Cno=https://www.360docs.net/doc/7a7179015.html,o )

order by Student.Sno;

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

SQL语句：

select Cno,avg(grade) as 'average_grade'

from SC

where grade in

(

select grade

from SC

where Cno in

(

select Cno

from SC

group by Cno

having

Count(Cno)>=3

)

)and https://www.360docs.net/doc/7a7179015.html,o like '__3'

group by Cno;

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

SQL语句：

select SC.Sno,Sname,max(grade) as 'Max_Grade',min(grade) as 'Min_Grade' from SC left join Student on(SC.Sno=Student.Sno)

group by SC.Sno,Sname

having SC.Sno in

(

select SC.Sno

from SC,Student

where Student.Sno=SC.Sno

)

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

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

select Sno

from SC

where Cno='001'

and Sno in

(

select Sno

from SC

where Cno='002'

)

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

select Sno

from SC

where Cno='001'

and Sno not in

(

select Sno

from SC

where Cno='002'

)

5.3.嵌套子查询

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

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

用IN子查询：

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

select distinct 产品名称

from CP

where 产品编号 not in

(

select 产品编号

from CPXSB

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

);

用EXISTS子查询：

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

select distinct 产品名称

from CP

where 产品名称!=all

(

select 产品名称

from CP

where exists

(

select 产品编号

from CPXSB

where 销售日期!='2004-03-18' and

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

)

)

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

用IN子查询：

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

select 产品名称,数量

from CPXSB left join CP on(CPXSB.产品编号=CP.产品编号)

where 客户编号 in

(

select 客户编号

from XSS

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

)

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

用EXISTS子查询：

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

select 产品名称,数量

from CPXSB left join CP on(CPXSB.产品编号=CP.产品编号)

where CPXSB.客户编号 =

(

select 客户编号

from XSS

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

)

and exists

(

select distinct 产品名称

from CP

where 销售日期='2004-03-18' and

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

)

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

用ALL谓词：

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

select 产品编号

from CPXSB

where 数量>all(select 数量

from CPXSB

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

)

用集函数：

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

select 产品编号

from CPXSB

group by 产品编号,数量

having 数量> (select max(数量)

from CPXSB

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

)

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

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

select 客户名称

from XSS

where not exists(

select 产品编号

from CP

where not exists(

select 客户编号

from CPXSB

where CP.产品编号=CPXSB.产品编号and CPXSB.客户编号=XSS.客户编号

)

)

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

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

select 客户名称

from XSS

where 客户编号 in

(select distinct 客户编号

from CPXSB x

where not exists(select 客户编号

from CPXSB y

where y.客户编号='000001'

and not exists(select 客户编号 from CPXSB z

where z.客户编号=x.客户编号

and z.产品编号=y.产品编号))) and 客户编号!='000001'

5.4.集合操作

1. 准备工作

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

图5-1 测试表X和Y

阅读并执行以下语句，理解其功能，给出运行结果。

2. 集合并：union

执行以下语句：

请给出运行结果：

3. 集合的差：minus:

执行以下语句：

请给出运行结果：

4. 集合交：intersect:

执行以下语句：

请给出运行结果：