《数据库系统概论》复习重点问答题3数据查询.docx

《数据库系统概论》复习重点问答题3数据查询.docx

《《数据库系统概论》复习重点问答题3数据查询.docx》由会员分享，可在线阅读，更多相关《《数据库系统概论》复习重点问答题3数据查询.docx（15页珍藏版）》请在冰点文库上搜索。

《数据库系统概论》复习重点问答题3数据查询

问答题4—数据查询

一、第3章课后习题

P127-4．针对上题中建立的四个表试用SQL完成第二章习题5中的查询

（1）求供应工程J1零件的供应商号码SNO

答：

SELECTSNOFROMSPJWHEREJNO=’J1’

（2）求供应工程J1零件P1的供应商号码SNO

答：

SELECTSNOFROMSPJWHEREJNO=’J1’ANDPNO=’P1’

（3）求供应工程J1零件为红色的供应商号码SNO

答：

SELECTSNOFROMSPJWHEREJNO=’J1’ANDPNOIN

（SELECTPNOFROMPWHERECOLOR=’红’）

或：

SELECTSNOFROMSPJ,PWHEREJNO=’J1’ANDSPJ.PNO=P.PNOANDCOLOR=’红’

（4）求没有使用天津供应商生产的红色零件的工程号JNO

答：

SELECTJNOFROMJWHERENOTEXISTS

（SELECT*FROMSPJWHERESPJ.JNO=J.JNOANDSNOIN

（SELECTSNOFROMSWHERECITY=’天津’）

ANDPNOIN

（SELECTPNOFROMPWHERECOLOR=’红’）

）

或：

SELECTJNOFROMJWHERENOTEXISTS

（SELECT*FROMSPJ,S,P

WHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDSPJ.PNO=P.PNO

ANDS.CITY=’天津’ANDP.COLOR=’红’

）

（5）求至少用了供应商S1所供应的全部零件的工程号JNO

答：

SELECTDISTINCTJNOFROMSPJ,SPJZ

WHERENOTEXISTS

（SELECT*FROMSPJSPJXWHERESNO=’S1’ANDNOTEXISTS

（SELECT*FROMSPJSPJY

WHERESPJY.PNO=SPJX.PNOANDSPJY.JON=SPJZ.JNO

）

）

P127-5．针对习题3中的四个表，试用SQL完成以下各项操作

（1）找出所有供应商的姓名和所在城市

答：

SELECTSNAME,CITYFROMS

（2）找出所有供应商的姓名和所在城市

答：

SELECTPNAME,COLOR,WEIGHTFROMP

（3）找出使用供应商S1所供应零件的工程号码

答：

SELECTJNOFROMSPJWHERESNO=’S1’

（4）找出工程项目J2使用的各种零件的名称及其数量

答：

SELECTP.PNAME,SPJ.QTYFROMP,SPJWHEREP.PNO=SPJ.PNOANDSPJ.JNO=’J2’

（5）找出上海厂商供应的所有零件号码

答：

SELECTDISTINCTPNOFROMSPJWHERESNOIN

（SELECTSNOFROMSWHERECITY=’上海’）

（6）找出使用上海产的零件的工程名称

答：

SELECTJNAMEFROMJ,SPJ,S

WHEREJ.JNO=SPJ.JNOANDSPJ.SNO=S.SNOANDS.CITY=’上海’

或：

SELECTJNAMEFROMJWHEREJNOJNO

（SELECTJNOFROMSPJ,SWHERESPJ.SNO=S.SNOANDS.CITY=’上海’）

（7）找出没有使用天津产的零件的工程号码

答：

SELECTJNOFROMJWHERENOTEXISTS

（SELECT*FROMSPJWHERESPJ.JNO=J.JNOANDSNOIN

（SELECTSNOFROMSWHERECITY=’天津’）

）

或：

SELECTJNOFROMJWHERENOTEXISTS

（SELECT*FROMSPJ,S

WHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDS.CITY=’天津’

）

（8）把全部红色零件的颜色改成蓝色

答：

UPDATEPSETCOLOR=’蓝’WHERECOLOR=’红’

（9）由S5供给J4的零件P6改为由S3供应，请作必要的修改

答：

UPDATESPJSETSNO=’S3’WHERESNO=’S5’ANDJNO=’S4’ANDPNO=’P6’

（10）从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录

答：

DELETEFROMSPJWHERESNO=’S2’DELETEFROMSWHERESNO=’S2’

（11）请将（S2，J6，P4，200）插入供应情况关系

答：

INSERTINTOSPJ（SNO,JNO,PNO,QTY）VALUES（S2,J6,P4,200）

或：

INSERTINTOSPJVALUES（S2,P4,J6,200）

二、补充题

1．设数据库中有基本表：

教师（工号,姓名,性别,职称,工龄,基本工资,补贴），请用SQL语句写出工龄在20年以上（包括20年）、基本工资低于1500元的教师所有信息。

答：

SELECT*FROM教师WHERE工龄>=20AND基本工资<1500

2．在教学管理系统中含有两个关系：

学生（学号,姓名,性别,出生日期,系名），成绩（学号,课程名,成绩）。

若查询每个学生的选修课程数、总成绩、平均成绩，写出实现上述要求的SQL语句。

答：

SELECT学号,COUNT（课程名）,SUM（成绩）,AVG（成绩）FROM成绩

GROUPBY学号

3．图书出版管理数据库中有两个基本表：

图书（书号,书名,作者编号,出版社,出版日期）,作者（作者,编号,作者名,年龄,地址），试用SQL语句写出下列查询：

检索年龄低于作者平均年龄的所有作者的作者名、书名和出版社。

答：

SELECT作者名,书名,出版社FROM图书,作者

WHERE图书.作者编号=作者.作者编号AND年龄<=

（SELECTAVG（年龄）FROM作者）

4．基于数据库中的成绩表：

成绩（学号,成绩名,成绩），用SQL语句写出：

只选修一门课程的学生学号、课程名及成绩。

答：

SELECT*FROM成绩XWHERENOTEXISTS

（SELECT*FROM成绩YWHEREY.学号=X.学号ANDY.课程名<>X.课程名）

5．本题基于以下数据库模式：

学生关系S（学号,姓名,性别）、课程关系C（课程号,课程名）、成绩关系SC（学号,课程号,分数），请用SQL语言完成下列查询。

（1）分别统计男生和女生的人数

答：

SELECT“男生人数为：

”,COUNT（*）FROMSWHERE性别=‘男’

SELECT“女生人数为：

”,COUNT（*）FROMSWHERE性别=‘女’

（2）检索选修课程号为“C1”且分数最高的学生学号和分数

答：

SELECT学号,MAX（分数）FROMSCWHERE课程号=‘C1’

（3）检索选修课程名为“DB”的学生姓名和分数

答：

SELECT姓名,分数FROMS,SC,C

WHERES.学号=SC.学号ANDSC.课称号=C.课称号ANDC.课成名=‘DB’

6．本题基于这样的三个表即学生表S、课程表C和学生选课表SC，它们的结构如下：

S（S#,SN,SEX,AGE,DEPT）C（c#,CN）SC（S#,C#,GRADE），其中S#为学号，SN为姓名，SEX为性别；AGE为年龄，DEPT为系别，C#为课程号，CN为课程名，GRADE为成绩。

请用SQL语句写出如下查询。

（1）检索所有比“王华”年龄大的学生姓名、年龄和性别

答：

SELECTSN,AGE,SEXFROMSWHEREAGE>（SELECTAGEFROMSWHERESN=’王华’）

（2）检索选修了“C2”课程的学生中成绩最高的学生学号

答：

SELECTS#FROMSCWHEREC#=’C2’ANDGRADE>=ALL

（selectgradefromscwherec#=’c2’）

（3）检索学生姓名及其所选修课程的课程号和成绩

答：

SELECTS.SN,SC.C#,GRADEFROMS,SCWHERES.S#=SC.S#

（4）检索选修4门以上课程的学生总成绩（不含不及格课程），并要求按总成绩的降序排列

答：

SELECTS#,SUM（GRADE）FROMSCWHEREGRADE>=60

GROUPBYS#HAVINGCOUNT（*）>=4

ORDERBY2DESC

7．本题基于以下定义：

图书（总编号,分类号,书名,作者,出版单位,单价），读者（借书证号,单位,姓名,性别,职称,地址），借阅（借书证号,总编号,借书日期），写出下列各命令或命令组的功能。

（1）SELECTMAX（单价）最高价,MIN（单价）最低价,AVG（单价）平均价FROM图书

答：

求出所有图书的最高价、最低价和平均价

（2）SELECTCOUNT（*）FROM图书WHERE单价BETWEEN10AND20

答：

统计10至20元之间的图书数量

（3）SELECT借书证号,COUNT（*）FROM借阅WHERE借书日期<’98/01/01’

GROUPBY借书证号HAVINGCOUNT（*）>=3

答：

分组统计出97年底以前借阅不低于3本图书的借书证号及数量

（4）SELECT单位,COUNT（*）FROM读者,借阅WHERE读者.借书证号=借阅.借书证号

GROUPBY单位

答：

按单位分组统计出被借阅图书的数量

（5）SELECT书名,COUNT（*）FROM图书,借阅WHERE图书.总编号=借阅.总编号

GROUPBY书名ORDERBYCOUNT（*）

答：

求被借阅图书的名字和借阅数量，并按借阅数量升序排列

8．有2个表：

Student（学号char（8），姓名varchar（8），年龄int，专业varchar

（2），入学日期DateTime）

Score（学号char（8），课程名varchar（10），成绩numeric（5,2））

写出下列每条语句或程序段的功能。

（1）SELECTCOUNT（*）FROMStudentsWHEREleft（专业,3）=’计算机’

答：

统计计算机专业人数

（2）UPDATEScoreSET成绩=@cWHERE学号=@aAND课程名=@b

答：

将学号为@a且课程名为@b学生的成绩更改为@c

（3）DELETEFROMScoreWHERE学号=@aAND课程名=@b

答：

从Score表中删除学号为@a、课程名为@b值的学生成绩记录

（4）SELECT*FROMStudent,ScoreWHEREStudent.学号=Score.学号

答：

查看学生及成绩表

（5）SELECTStudent.学号,姓名,专业,COUNT（*）AS门数FROMStudent,Score

WHEREStudent.学号=Score.学号

GROUPBYStudent.学号,姓名,专业

答：

显示学生的学号、姓名、专业和选课门数

（6）SELECT学号,AVG（成绩）AS平均成绩FROMScore

GROUPBY学号

答：

显示每个学生的平均成绩

9．已知关系模式：

Customers（customerid,name,emailaddress,city,tel,company,birthday,type）

客户（客户编号，姓名，邮箱地址，城市，电话，公司，出生日期，客户类型）

Goods（goodsname,description,storage,supplier,status,price）

货品（货品名称，描述，库存量，供应商，状态，价格）

Orders（orderid,goodsname,customerid,quantity,ordersum,orderdate）

订单（订单编号，货品名称，客户编号，订货数量，总金额，订货日期）

请写出下列各命令或命令组的功能

（1）SELECTcustomerid,name,emailaddressFROMCustomers

WHEREemailaddressISNOTNULL

答：

查询留有邮箱地址客户的客户编号、客户姓名和客户邮箱地址

（2）SELECTgoodsname,SUM（quantity）FROMOrders

GROUPBYgoodsname

HAVINGSUM（quantity）>500

答：

查询订单中货品总量超过500（不含500）的货品名称和货品总量

（3）SELECTgoodsname,priceFROMGoodsWHEREprice>

（SELECTAVG（price）FROMGoods）

答：

查询价格高于所有货品平均价格的货品的名称和价格

（4）SELECTGoods.goodsname,storage,priceFROMGoods

WHEREEXISTS

（SELECT*FROMOrders,GoodsWHEREOrders.goodsname=Goods.goodsname）

答：

查询有订单记录的货品名称、库存量和价格

（5）DELETEFROMordersWHEREcustomeridIN

（SELECTcustormeridFROMcustomersWHEREcity=’北京’）

答：

删除北京客户的订单记录

10．在“教学库”中包含3个表，分别定义为：

学生（学号CHAR（7）,姓名CHAR（6）,性别CHAR

（2）,出生日期DATETIME,专业CHAR（10）,年级INT）；课程（课程号CHAR（4）,课程名CHAR（10）,课程学分INT）；选课（学号CHAR（7）,课程号CHAR（4）,成绩INT）

（1）查询每个学生选课的门数

答：

SELECT学生.学号，COUNT（*）AS选课门数FROM学生,选课

WHERE学生.学号=选课.学号

GROUPBY学生.学号

（2）查询每门课程被选修的学生数

答：

SELECT课程.课程号,课程.课程名,COUNT（*）AS选课人数FROM课程,选课

WHERE课程.课程号=选课.课程号

GROUPBY课程.课程号

（3）查询同时选修了3门课程的全部学生学号

答：

SELECT学号FROM选课

GROUPBY学号HAVINGCOUNT（*）=3

（4）查询至少选修了姓名为@ml学生所选课程中一门课的全部学生

答：

SELECTDISTINCT学生.*FROM学生,选课

WHERE学生.学号=选课.学号AND课程号=ANY

（SELECT课程号FROM学生,选课

WHERE学生.学号=选课.学号AND姓名=@ml

）

（5）查询被8~10名学生选修的所有课程

答：

SELECT*FROM课程WHEREEXISTS

（SELECT课程号FROM选课WHERE课程.课程号=选课.课程号

GROUPBY课程号HAVINGCOUNT（*）BETWEEN8AND10

）

（6）查询最多选修了2门课程（含未选任何课程）的全部学生

答：

SELECT*FROM学生WHERE学号IN

（SELECT学号FROM选课

GROUPBY学号HAVINGCOUNT（*）<=2

）

ORNOTEXISTS

（SELECT*FROM选课WHERE学生.学号=选课.学号）

11．设工程-零件数据库中有4个基本表：

供应商（供应商代码,姓名,所在城市,联系电话）；工程（工程代码,工程名,负责人,预算）；零件（零件代码,零件名,规格,产地,颜色）；供应零件（供应商代码,工程代码,零件代码,数量）。

试用SQL语句完成下列操作：

（1）找出天津供应商的姓名和电话

答：

SELECT姓名,联系电话FROM供应商WHERE所在城市=’天津市’

（2）查找预算在50000~100000元之间的工程信息，并将结果按预算顺序排列

答：

SELECT*FROM工程WHERE预算BETWEEN50000AND100000

ORDERBY预算DESC

（3）找出使用供应商S1所供零件的工程代码

答：

SELECT工程代码FROM供应零件WHERE供应商代码=’SI’

（4）找出工程项目J2使用的各种零件名称及数量

答：

SELECT零件名,数量FROM零件,供应零件

WHERE零件.零件代码=供应零件.零件代码AND工程代码=’J2’

（5）找出上海厂商供应的所有零件代码

答：

SELECT零件代码FROM供应商,供应零件

WHERE供应商.供应商代码=供应零件.供应商代码AND所在城市=’上海’

（6）找出使用上海产的零件的工程名称

答：

SELECT工程名FROM零件,供应零件,工程

WHERE零件.零件代码=供应零件.零件代码AND供应零件.工程代码=工程.工程代码

AND产地=’上海’

（7）找出没有使用天津供应商供应的零件的工程代码

答：

SELECT工程代码FROM供应零件

WHERENOTEXISTS

（SELECT*FROM供应零件WHERE供应商代码IN

（SELECT供应商代码FROM供应商WHERE所在城市=’天津’）

）

（8）将全部红色零件的颜色改成蓝色

答：

UPDATE零件SET颜色=’蓝色’WHERE颜色=’红色’

（9）将由供应商S5供给工程代码为J4的零件P6改为由S3供应

答：

UPDATE供应零件SET供应商代码=’S3’

WHERE供应商代码=’S5’AND工程代码=’J4’

（10）从供应商关系中删除S2的记录，并从供应零件关系中删除相应的记录

答：

DELETEFROM供应零件WHERE供应商代码=’S2’

DELETEFROM供应商WHERE供应商代码=’S2’

12．以下是一个“仓库管理”数据库，包含职工、仓库、供应商和订购单4个表，内容如下：

职工：

供应商：

订购单：

仓库：

请用嵌套方法完成如下查询：

（1）哪些城市至少有一个仓库的职工工资为1250元

答：

SELECT城市FROM仓库WHERE仓库号IN

（SELECT仓库号FROM职工WHERE工资=1250）

（2）找出和职工E4工资相同的所有职工

答：

SELECT职工号FROM职工WHERE工资=

（SELECT工资FROM职工WHERE职工号='E4'）

（3）找出哪些城市的仓库向北京的供应商发出了订购单

答：

SELECT城市FROM仓库WHERE仓库号IN

（SELECT仓库号FROM职工WHERE职工号IN

（SELECT职工号FROM订购单

WHERE供应商号IN

（SELECT供应商号FROM供应商