发布时间 : 星期四 文章数据库原理练习题集-参考答案更新完毕开始阅读
(三)应用题
1.
参考答案:
(1)查询201002班男生的信息。
select * from s where class=‘201002’ and ssex=‘男’
(2)查询最低分大于70、最高分小于90的学生的姓名。 select s# from sc group s#
having min(grade)>70 and max(grade)<90
(3)查询所有女生记录信息,并以班级降序排列。 select * from s where ssex=?女? order by class desc
(4)查询选取修数据结构课程的学生姓名及所在专业代码。
select sname,scode# from s,sc,c where s.s#=sc.s# and sc.c#=c.c# and cname=?数据结构?; (5)查询至少有5名学生选修的并以3开头的课程号的平均分。
select c#,avg(grade) from sc where c# like ?3%? group by c# having count(*)>=5 2.
参考答案:
(1)查询来自南京或上海的学生学号和姓名。
select s#,sname from s where placeofb='上海' or placeofb='南京' (2)查询选修课程号为C401001的学号和成绩,并要求对查询结果按成绩降序排列,如果成绩相同则按学号升序排列。
select s#,grade from sc where c#='C401001' order by grade desc,s# asc (3)查询计算机科学或网络工程专业姓张的学生的信息。
select * from s where scode# in (select scode# from ss where ssname=’计算机科学’or ssname=’网络工程’) and sname like '张%' (4)查询缺少了成绩的学生的学号和课程号。
select s#,c# from sc where grade is null
(5)查询每个专业的男、女生分别有多少人数。
select scode#,ssex,count(*) from s group by scode#,ssex
3. 参考答案:
(1).找出店员人数不超过100人或者在长沙市的所有商店的代号和商店名。
SELECT A#,ANAME FROM A;
WHERE WQTY<=100 OR CITY=“长沙” (2).找出供应书包的商店名。
SELECT A.ANAME FROM A,B,AB;
WHERE A.A#=AB.A# AND B.B#=AB.B# AND B.BNAME=“书包”
(3).找出至少供应代号为256的商店所供应的全部商品的商店名和所在城市。
SELECT A.ANAME,A.CITY FROM A,B;
WHERE A.A#=AB.A# AND AB.B# IN
(SELECT AB.B#; FROM AB
WHERE A#=“256”)
4. 参考答案:
(1).CREATE VIEW R-S-T
AS SELECT R.A,B,C,S.D,E,F FROM R,S,T
WHERE R.A=S.A AND S.D=T.D (2).SELECT AVG(C),AVG(E) FROM R-S-T GROUP BY A
5. 参考答案: (1).SELECT B FROM R,S
WHERE R.A=S.A AND C>50 (2).UPDATE R SET B=‘b4’ WHERE A IN (SELECT A FROM S
WHERE C=40)
6. 参考答案:
(1).SELECT DISTINCT PROV FROM S
WHERE SD=“信息系” (2).SELECT SN,GR FROM S,SC
WHERE SD=“英语系”AND CN=“计算机”AND S.SNO=SC.SNO ORDER BY GR DESC; 7. 参考答案:
(1).取出所有工程的全部细节;
SELECT * FROM JB
(2).取出所在城市为上海的所有工程的全部细节;
SELECT * FROM JB
WHERE CITY=“上海”
(3).取出重量最轻的零件代号;
SELECT PN FROM PB
WHERE WEIGHT=
(SELECT MIN(WEIGHT) FROM PB)
(4).取出为工程J1提供零件的供应商代号;
SELECT SN FROM SPJB
WHERE JN=“J1”
(5).取出为工程J1提供零件P1的供应商代号;
SELECT SN FROM SPJB;
WHERE JN=‘Jl’AND PN=‘Pl’
(6).取出由供应商S1提供零件的工程名称;
SELECT JB.JNAME FROM JB,SPJB
WHERE JB.JN=SPJB.JN AND SPJB.SN=‘S1’ (7).取出供应商S1提供的零件的颜色;
SELECT DISTINCT PB.COLOR FROM PB,SPJB
WHERE PB.PN=SPJB.PN AND SPJB.SN=‘S1’ (8).取出为工程J1或J2提供零件的供应商代号;
SELECT DISTINCT SN FROM SPJB
WHERE JN=‘J1’OR JN=‘J2’
(9).取出为工程J1提供红色零件的供应商代号;
SELECT DISTINCT SPJB.SN FROM SPJB,PB
WHERE PB.PN=SPJB.PN AND SPJB.JN=‘J1’AND PB.COLOR=‘红’
(10).取出为所在城市为上海的工程提供零件的供应商代号;
SELECT DISTINCT SPJB.SN FROM SPJB, JB
WHERE SPJB.JN=JB.JN AND JB.CITY‘上海’
(11).取出为所在城市为上海或北京的工程提供红色零件的供应商代号;
SELECT SPJB.SN FROM PB,JB SPJB WHERE SPJB.PN=PB.PN AND JB.JN=SPJB.JN AND PB.COLOR=’红’ AND JB.CITY=’上海’ (12).取出供应商与工程所在城市相同的供应商提供的零件代号;
SELECT DISTINCT SPJB.PN FROM SB,JB,SPJB
WHERE SB.SN=SPJB.SN AND JB.JN=SPJB.JN AND SB.CITY=JB.CITY (13).取出上海的供应商提供给上海的任一工程的零件的代号;
SELECT SPJB.PN FROM SB,JB,SPJB
WHERE SB.SN=SPJB.SN AND JB.JN=SPJB.JN AND SB.CITY=’上海’ (14).取出至少由一个和工程不在同一城市的供应商提供零件的工程代号;
SELECT DISTINCT SPJB.JN FROM SB,JB,SPJB
WHERE SB.SN=SPJB.SN AND JB.JN=SPJB.JN AND SB.CITY<>JB.CITY (15).取出上海供应商不提供任何零件的工程的代号;
SELECT DISTINCT JN FROM SPJB
WHERE JN NOT IN
(SELECT DISTINCT SPJB.JN
FROM SB,SPJB
WHERE SB.SN=SPJB.SN AND SB.CITY=’上海’)
(16).取出这样一些供应商代号,它们能够提供至少一种由红色零件的供应商提供的零件;
SELECT DISTINCT SPJB.SN FROM PB,SPJB WHERE SPJB.PN IN (SELECT SPJB.PN
FROM SPJB,SB,PB
WHERE SB.SN=SPJB.SN AND PB.PN=SPJB.PN AND PB.COLOR=’红’)
(17).取出由供应商S1提供零件的工程的代号;
SELECT DISTINCT SPJB.JN FROM SB,PB,SPJB
WHERE SB.SN=SPJB.SN AND PB.PN=SPJB.PN AND SB.SN=’S1’
8. 参考答案:
(1).检索在北京的供应商的名称。
SELECT SNAME FROM SUPPLIER WHERE ADDR=“北京”; (2).检索发给供应商S6的订购单号。
SELECT ONO FROM ORDER WHERE SNO=“S6”;
(3).检索出职工E6发给供应商S6的订购单号。
SELECT ONO FROM ORDER
WHERE SNO=“S6” AND ENO=“E6”;
(4).检索出向供应商S3发过订购单的职工的职工号和仓库号。
SELECT ENO,WHNO FROM EMPLOYEE WHERE ENO IN
(SELECT ENO FROM ORDER WHERE SNO=“S3”);
或:SELECT ENO,WHNO
FROM EMPLOYEE,ORDER
WHERE EMPLOYEE.ENO=ORDER.ENO AND ORDER.SNO=“S3”; (5).检索出目前与S3供应商没有联系的职工信息。
SELECT ENO,WHNO FROM EMPLOYEE WHERE ENO NOT IN
(SELECT ENO
FROM ORDER WHERE SNO=“S3”);
(6).检索出目前没有任何订购单的供应商信息。
SELECT * FROM SUPPLIER WHERE SNO NOT IN
(SELECT SNO FROM ORDER);
(7).检索出和职工E1、E3都有联系的北京的供应商信息。
SELECT * FROM SUPPLIER
WHERE ADDR=“北京” AND