1、数据库系统概论复习重点问答题3数据查询问答题4数据查询一、第3章课后习题P127-4针对上题中建立的四个表试用SQL完成第二章习题5中的查询（1）求供应工程J1零件的供应商号码SNO答： SELECT SNO FROM SPJ WHERE JNO=J1（2）求供应工程J1零件P1的供应商号码SNO答： SELECT SNO FROM SPJ WHERE JNO=J1 AND PNO=P1（3）求供应工程J1零件为红色的供应商号码SNO答： SELECT SNO FROM SPJ WHERE JNO=J1 AND PNO IN ( SELECT PNO FROM P WHERE COLOR=红
2、)或：SELECT SNO FROM SPJ,P WHERE JNO=J1 AND SPJ.PNO=P.PNO AND COLOR=红 （4）求没有使用天津供应商生产的红色零件的工程号JNO 答：SELECT JNO FROM J WHERE NOT EXISTS ( SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO AND SNO IN ( SELECT SNO FROM S WHERE CITY=天津 ) AND PNO IN ( SELECT PNO FROM P WHERE COLOR=红 ) )或： SELECT JNO FROM J WHERE NOT E
3、XISTS ( SELECT * FROM SPJ,S,P WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.CITY=天津 AND P.COLOR=红 )（5）求至少用了供应商S1所供应的全部零件的工程号JNO答： SELECT DISTINCT JNO FROM SPJ,SPJZ WHERE NOT EXISTS ( SELECT * FROM SPJ SPJX WHERE SNO=S1 AND NOT EXISTS ( SELECT * FROM SPJ SPJY WHERE SPJY.PNO=SPJX.PNO A
4、ND SPJY.JON=SPJZ.JNO ) )P127-5针对习题3中的四个表，试用SQL完成以下各项操作（1）找出所有供应商的姓名和所在城市答：SELECT SNAME,CITY FROM S（2）找出所有供应商的姓名和所在城市答：SELECT PNAME,COLOR,WEIGHT FROM P（3）找出使用供应商S1所供应零件的工程号码答：SELECT JNO FROM SPJ WHERE SNO=S1（4）找出工程项目J2使用的各种零件的名称及其数量答：SELECT P.PNAME,SPJ.QTY FROM P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.JNO=J
5、2（5）找出上海厂商供应的所有零件号码答：SELECT DISTINCT PNO FROM SPJ WHERE SNO IN ( SELECT SNO FROM S WHERE CITY=上海 )（6）找出使用上海产的零件的工程名称答：SELECT JNAME FROM J,SPJ,S WHERE J.JNO=SPJ.JNO AND SPJ.SNO=S.SNO AND S.CITY=上海或：SELECT JNAME FROM J WHERE JNO JNO ( SELECT JNO FROM SPJ,S WHERE SPJ.SNO=S.SNO AND S.CITY=上海 )（7）找出没有使用天
6、津产的零件的工程号码答：SELECT JNO FROM J WHERE NOT EXISTS ( SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO AND SNO IN ( SELECT SNO FROM S WHERE CITY=天津 ） ）或：SELECT JNO FROM J WHERE NOT EXISTS （ SELECT * FROM SPJ,S WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND S.CITY=天津 ）（8）把全部红色零件的颜色改成蓝色答：UPDATE P SET COLOR=蓝 WHERE COLOR=红
7、（9）由S5供给J4的零件P6改为由S3供应，请作必要的修改答：UPDATE SPJ SET SNO=S3 WHERE SNO=S5 AND JNO=S4 AND PNO=P6（10）从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录答：DELETE FROM SPJ WHERE SNO=S2 DELETE FROM S WHERE SNO=S2（11）请将（S2，J6，P4，200）插入供应情况关系答：INSERT INTO SPJ(SNO,JNO,PNO,QTY) VALUES(S2,J6,P4,200)或：INSERT INTO SPJ VALUES(S2,P4,J6,200
8、)二、补充题1设数据库中有基本表：教师(工号,姓名,性别,职称,工龄,基本工资,补贴），请用SQL语句写出工龄在20年以上（包括20年）、基本工资低于1500元的教师所有信息。答：SELECT * FROM 教师 WHERE 工龄=20 AND 基本工资15002在教学管理系统中含有两个关系：学生(学号,姓名,性别,出生日期,系名），成绩(学号,课程名,成绩)。若查询每个学生的选修课程数、总成绩、平均成绩，写出实现上述要求的SQL语句。答：SELECT 学号,COUNT(课程名),SUM(成绩),AVG(成绩) FROM 成绩GROUP BY 学号3图书出版管理数据库中有两个基本表：图书(书号
9、,书名,作者编号,出版社,出版日期）,作者(作者,编号,作者名,年龄,地址)，试用SQL语句写出下列查询：检索年龄低于作者平均年龄的所有作者的作者名、书名和出版社。答：SELECT 作者名,书名,出版社 FROM 图书,作者 WHERE 图书.作者编号=作者.作者编号 AND 年龄=( SELECT AVG(年龄) FROM 作者 )4基于数据库中的成绩表：成绩(学号,成绩名,成绩)，用SQL语句写出：只选修一门课程的学生学号、课程名及成绩。答：SELECT * FROM 成绩 X WHERE NOT EXISTS（ SELECT * FROM 成绩 Y WHERE Y.学号=X.学号 AND
10、 Y.课程名X.课程名）5本题基于以下数据库模式：学生关系S(学号,姓名,性别)、课程关系C(课程号,课程名)、成绩关系SC(学号,课程号,分数) ，请用SQL语言完成下列查询。（1）分别统计男生和女生的人数答：SELECT “男生人数为：”,COUNT(*) FROM S WHERE 性别=男SELECT “女生人数为：”,COUNT(*) FROM S WHERE 性别=女（2）检索选修课程号为“C1”且分数最高的学生学号和分数答：SELECT 学号,MAX(分数) FROM SC WHERE 课程号=C1（3）检索选修课程名为“DB”的学生姓名和分数答： SELECT 姓名,分数 FRO
11、M S,SC,C WHERE S.学号=SC.学号 AND SC.课称号=C.课称号 AND C.课成名=DB6本题基于这样的三个表即学生表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）检索所有比“王华”年龄大的学生姓名、年龄和性别答： SELECT SN,AGE,SEX FROM S WHERE AGE(SELECT AGE FROM S WHER
12、E SN=王华)（2）检索选修了“C2”课程的学生中成绩最高的学生学号答：SELECT S# FROM SC WHERE C#=C2 AND GRADE=ALL(select grade from sc where c#=c2)（3）检索学生姓名及其所选修课程的课程号和成绩答： SELECT S.SN,SC.C#,GRADE FROM S,SC WHERE S.S#=SC.S#（4）检索选修4门以上课程的学生总成绩（不含不及格课程），并要求按总成绩的降序排列答：SELECT S#,SUM(GRADE) FROM SC WHERE GRADE=60GROUP BY S# HAVING COUNT
13、(*)=4 ORDER BY 2 DESC7本题基于以下定义：图书(总编号,分类号,书名,作者,出版单位,单价)，读者(借书证号,单位,姓名,性别,职称,地址)，借阅(借书证号,总编号,借书日期)，写出下列各命令或命令组的功能。（1）SELECT MAX(单价) 最高价, MIN(单价) 最低价, AVG(单价) 平均价 FROM 图书答：求出所有图书的最高价、最低价和平均价（2）SELECT COUNT(*) FROM 图书 WHERE 单价 BETWEEN 10 AND 20答：统计10至20元之间的图书数量（3）SELECT 借书证号,COUNT(*) FROM 借阅 WHERE 借书日
14、期=3答：分组统计出97年底以前借阅不低于3本图书的借书证号及数量（4）SELECT 单位,COUNT(*) FROM 读者,借阅 WHERE 读者.借书证号=借阅.借书证号GROUP BY 单位答：按单位分组统计出被借阅图书的数量（5）SELECT 书名,COUNT(*) FROM 图书,借阅 WHERE 图书.总编号=借阅.总编号 GROUP BY 书名 ORDER BY COUNT(*)答：求被借阅图书的名字和借阅数量，并按借阅数量升序排列8有2个表：Student（学号char(8)，姓名varchar(8)，年龄int，专业varchar(2)，入学日期DateTime）Score（
15、学号char(8)，课程名varchar(10)，成绩numeric(5,2)）写出下列每条语句或程序段的功能。（1）SELECT COUNT(*) FROM Students WHERE left(专业,3)=计算机答： 统计计算机专业人数（2）UPDATE Score SET 成绩=c WHERE 学号=a AND 课程名=b答： 将学号为a且课程名为b学生的成绩更改为c（3）DELETE FROM Score WHERE 学号=a AND 课程名=b答： 从Score表中删除学号为a、课程名为b值的学生成绩记录（4）SELECT * FROM Student , Score WHERE
16、Student.学号=Score.学号答： 查看学生及成绩表（5）SELECT Student.学号, 姓名,专业,COUNT(*) AS 门数 FROM Student ,Score WHERE Student.学号=Score.学号 GROUP BY Student.学号,姓名,专业答： 显示学生的学号、姓名、专业和选课门数（6）SELECT 学号,AVG(成绩) AS 平均成绩 FROM Score GROUP BY 学号答： 显示每个学生的平均成绩9已知关系模式：Customers(customerid,name,emailaddress,city,tel,company,birthd
17、ay,type)客户(客户编号，姓名，邮箱地址，城市，电话，公司，出生日期，客户类型)Goods(goodsname, description, storage, supplier, status, price)货品(货品名称，描述，库存量，供应商，状态，价格)Orders(orderid,goodsname,customerid,quantity, ordersum,orderdate )订单(订单编号，货品名称，客户编号，订货数量，总金额，订货日期)请写出下列各命令或命令组的功能（1）SELECT customerid, name, emailaddress FROM Customers
18、WHERE emailaddress IS NOT NULL答：查询留有邮箱地址客户的客户编号、客户姓名和客户邮箱地址（2）SELECT goodsname, SUM(quantity) FROM Orders GROUP BY goodsname HAVING SUM(quantity)500答：查询订单中货品总量超过500（不含500）的货品名称和货品总量（3）SELECT goodsname, price FROM Goods WHERE price ( SELECT AVG(price) FROM Goods )答：查询价格高于所有货品平均价格的货品的名称和价格（4）SELECT Go
19、ods.goodsname, storage, price FROM GoodsWHERE EXISTS ( SELECT * FROM Orders,Goods WHERE Orders.goodsname=Goods.goodsname )答：查询有订单记录的货品名称、库存量和价格（5）DELETE FROM orders WHERE customerid IN ( SELECT custormerid FROM customers WHERE city=北京 )答： 删除北京客户的订单记录10在“教学库”中包含3个表，分别定义为：学生(学号CHAR(7),姓名CHAR(6),性别CHAR
20、(2),出生日期DATETIME,专业CHAR(10),年级INT)；课程(课程号CHAR(4),课程名CHAR(10),课程学分INT)；选课(学号CHAR(7),课程号CHAR(4),成绩INT)（1）查询每个学生选课的门数答：SELECT 学生.学号，COUNT(*) AS 选课门数 FROM 学生,选课 WHERE 学生.学号=选课.学号 GROUP BY 学生.学号（2）查询每门课程被选修的学生数答： SELECT 课程.课程号,课程.课程名,COUNT(*) AS 选课人数 FROM 课程,选课WHERE 课程.课程号=选课.课程号GROUP BY 课程.课程号 （3）查询同时选修
21、了3门课程的全部学生学号答：SELECT 学号 FROM 选课GROUP BY 学号 HAVING COUNT(*)=3（4）查询至少选修了姓名为ml学生所选课程中一门课的全部学生答：SELECT DISTINCT 学生.* FROM 学生,选课WHERE 学生.学号=选课.学号 AND 课程号=ANY ( SELECT 课程号 FROM 学生,选课 WHERE 学生.学号=选课.学号 AND 姓名=ml )（5）查询被810名学生选修的所有课程答：SELECT * FROM 课程 WHERE EXISTS （ SELECT 课程号 FROM 选课 WHERE 课程.课程号=选课.课程号 GR
22、OUP BY 课程号 HAVING COUNT(*) BETWEEN 8 AND 10 )（6）查询最多选修了2门课程（含未选任何课程）的全部学生答：SELECT * FROM 学生 WHERE 学号 IN （ SELECT 学号 FROM 选课 GROUP BY 学号 HAVING COUNT(*)=2 ） OR NOT EXISTS （ SELECT * FROM 选课 WHERE 学生.学号=选课.学号 ）11设工程-零件数据库中有4个基本表：供应商(供应商代码,姓名,所在城市,联系电话)；工程(工程代码,工程名,负责人,预算)；零件(零件代码,零件名,规格,产地,颜色)；供应零件(供应
23、商代码,工程代码,零件代码,数量)。试用SQL语句完成下列操作：（1）找出天津供应商的姓名和电话答：SELECT 姓名,联系电话 FROM 供应商 WHERE 所在城市=天津市（2）查找预算在50000100000元之间的工程信息，并将结果按预算顺序排列答：SELECT * FROM 工程 WHERE 预算 BETWEEN 50000 AND 100000 ORDER BY 预算 DESC（3）找出使用供应商S1所供零件的工程代码答： SELECT 工程代码 FROM 供应零件 WHERE 供应商代码=SI（4）找出工程项目J2使用的各种零件名称及数量答：SELECT 零件名,数量 FROM
24、零件,供应零件WHERE 零件.零件代码=供应零件.零件代码 AND 工程代码=J2（5）找出上海厂商供应的所有零件代码答：SELECT 零件代码 FROM 供应商,供应零件WHERE 供应商.供应商代码=供应零件.供应商代码 AND 所在城市=上海（6）找出使用上海产的零件的工程名称答： SELECT 工程名 FROM 零件,供应零件,工程WHERE 零件.零件代码=供应零件.零件代码 AND 供应零件.工程代码=工程.工程代码 AND 产地=上海（7）找出没有使用天津供应商供应的零件的工程代码答：SELECT 工程代码 FROM 供应零件WHERE NOT EXISTS( SELECT *
25、 FROM 供应零件 WHERE 供应商代码 IN ( SELECT 供应商代码 FROM 供应商 WHERE 所在城市=天津 )（8）将全部红色零件的颜色改成蓝色答：UPDATE 零件 SET 颜色=蓝色 WHERE 颜色=红色（9）将由供应商S5供给工程代码为J4的零件P6改为由S3供应答：UPDATE 供应零件 SET 供应商代码=S3 WHERE 供应商代码=S5 AND 工程代码=J4（10）从供应商关系中删除S2的记录，并从供应零件关系中删除相应的记录答：DELETE FROM 供应零件 WHERE 供应商代码=S2DELETE FROM 供应商 WHERE 供应商代码=S212以
26、下是一个“仓库管理”数据库，包含职工、仓库、供应商和订购单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 供应商
