《数据库系统概论》复习重点问答题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供应商