数据库技术及应用：SQL习题

相关子查询相关子查询：（内层）子查询条件依赖于（外层）父查询的属性值。

特征：子查询中会出现属于父查询对象的属性查询所有选修了一号课程的学生姓名Select

SnameFrom

StudentWhere

Exists

(

Select*

FromSCWhereSno=Student.SnoAndCno=‘1’);Exists谓词

使用Exists，若内层查询为非空（即满足查询条件），外层的Where子句返回真值，将外层查询所对应的属性(组)列入结果表中。使用Not

Exists，若内层查询为非空（即满足查询条件），外层的Where子句返回假值，满足内层查询要求的元组不进入结果表中。查询没有选修了一号课程的学生姓名

Select

Sname

From

Student

Where

NOT

Exists

(

Select*

FromSC

WhereSno=Student.SnoAndCno=‘1’);内层查询关注哪些同学选修1号课程，通过外层取反由于带Exists的子查询只返回逻辑值,不涉及具体数据，因此子查询的目标列表达式通常采用*。求没有使用福建供应商生产的红色零件的工程号XNO分析：先把用了福建供应商生产的红色零件的工程找出来,再在外层取反Select

XNoFromGCXWhereNOTEXISTS(Select*fromGPX,GYS,CPwhereGPX.XNO=GCX.XNOandGPX.GNO=GYS.GNOandGPX.PNO=CP.PNOandGYS.CITY=‘福建’

and

CP.Color=‘红’);四表查询GYS（GNO，GNAME，STATUS，CITY）

CP（PNO，PNAME，COLOR，WEIGHT）

GCX（XNO，XNAME，CITY）

GPX（GNO，PNO，XNO，QTY）取出供应商与工程所在城市相同的供应商提供的零件代号分析：供应商有所在城市，建设的工程也有其所在城市；当某供应商供应了自身所在城市所建设的工程时，该供应商入围，再把他供应的零件列出即可Select

GPX.PNOfromGYS,GCX,GPXwhereGPX.XNO=GCX.XNOandGPX.GNO=GYS.GNOandGYS.CITY=GCX.CITY;查询上海的供应商提供给上海任一工程的零件代号分析：相对于前述“供应商与工程所在城市相同的供应商提供的零件代号”区别在于限定了供应商是上海的这一附加条件。可以在前例中再加一个条件子句

and

GYS.CITY=‘上海’也可以用嵌套查询Select

PNO

FromGPXWhereXNO

in(SelectXNOfromGCXwhereCITY=‘上海’)AndGNO

in(SelectGNOfromGYSwhereCITY=‘上海’);嵌套查询应注意内层Select前后为内外对象的公共属性查询能够提供至少一种提供红色零件的供应商所提供的零件的供应商代码分析：能提供红色零件的供应商可能有很多，相同的零件可能会有不同的颜色，会产生一个零件号集合；若某供应商提供的零件号在此范围内，他就能入选，而并不是说该供应商必须能供应红色的零件

SELECTDISTINCT

GNO

FROMGPXWHEREPNOIN(SELECTGPX.PNOFROMGPX,GYS,CPWHEREGPX.GNO=GYS.GNOANDGPX.PNO=CP.PNOANDCP.COLOR=‘红’);三表联接得出提供红色零件的供应商所提供的零件的零件号查询北京任何工程都不购买的零件代号分析：先把建设在北京的工程所用的零件找出来，再用NOT

EXISTS取反；由于CP中PNO最全，外层查询对象是CP而不是GPX；用相关子查询SELECT

PNOFROM

CPWHERE

NOT

EXISTS（SELECT*

FROM

GPX，GYS

WHERE

CP.PNO=GPX.PNO

ANDGPX.GNO=GYS.GNOANDGYS.CITY=‘北京’）；按工程号递增的顺序列出每个工程购买的零件总数分析：涉及集函数、分组、排序SELECT

XNO，SUM（QTY）FROM

GPXGROUP

BY

XNOORDER

BY

XNO

ASC三表查询S（SNO，SNAME，AGE，SEX，DEPT）

SC（SNO，CNO，GRADE）C（CNO，CNAME，TEACHER）查询选修了1，2，3号课程的学生姓名和学号分析：不能采用在where后接多个条件子句取交集的方法实现查询，因为where是按行操作的，SC中的cno只能取具体的值，取交集只能返回空值。可通过SC中Sno将范围逐步缩小先在选修1号课程的学生中找还选了2号课程的学生，再在选修2号课程的学生中找还选了3号课程的学生;都满足时学号入选结果集;由于还关心姓名，因此还要涉及Student

SELECT

SCX.SNO，SNAME

FROM

SC

SCX（别名），STUDENTWHERESCX.CNO=‘1’ANDSCX.SNOIN(SELECTSNOFROMSCSCYWHERESCY.CNO=‘2’ANDSCY.SNOIN(SELECTSNOFROMSCSCZWHERESCZ.CNO=‘3’))ANDSTUDENT.SNO=SCX.SNO;

图书馆借书系统数据库SQL练习

参考答案

创建表书bookCREATETABLEbook(bnochar(8)primarykey,categorychar(10),titlevarchar(40),presschar(30),yearint,authorchar(20),pricedecimal(7,2),totalint, remainsint);创建表借书证cardCREATETABLEcard(cnochar(7)primarykey,namechar(10),departmentvarchar(40),typechar(1),check(typein('T','G','U','O')));创建表借书记录borrowCREATETABLEborrow(cnochar(7)foreignkeyreferencescard(cno),bnochar(8)foreignkeyreferencesbook(bno),borrow_date date,return_date date);集函数求藏书种数、总册数、最高价、最低价。

selectcount（distinct(category)）"藏书种数",sum(total)"总册数",

max(price)"最高价",

min(price)"最低价",

frombook;哪些出版社的总藏书超过100种。

selectpress

frombook

groupbypress

having

sum(total)>=100;列出藏书在十本以上的书（书号、类别、书名、出版社、年份、作者、价格、总藏书量、现有库存）。

select*

frombook