2022年数据库SQL习题终稿 .pdf

《2022年数据库SQL习题终稿 .pdf》由会员分享，可在线阅读，更多相关《2022年数据库SQL习题终稿 .pdf（16页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、S Q L 习题商品销售数据库Article(商品号 char(4)，商品名 char(16) ，单价 Numeric(8,2)，库存量 int) 商品Customer( 顾客号 char (4)，顾客名 char (8)，性别 char(2)，年龄 int) 顾客OrderItem( 顾客号 char(4)，商品号 char(4)，数量 int, 日期 date) 定购1、 检索定购商品号为 0001的顾客号和顾客名。select distinct 顾客号 from OrderItem where 商品号 =0001 or 商品号 =0002; 2、 检索定购商品号为 0001或0002的顾

2、客号。 select distinct 顾客号 from OrderItem where 商品号 =0001 or 商品号 =0002; 3、检索至少定购商品号为0001和0002的顾客号。 ( 用交的方法 ) select 顾客号 from OrderItem where 商品号 =0001 and 顾客号 in( select 顾客号from OrderItem where 商品号 =0002); 4、检索至少定购商品号为0001和0002的顾客号。 ( 用自表连接方法 ) select X. 顾客号 from OrderItem X,OrderItem Y where X.顾客号 =Y.

3、 顾客号 and X. 商品号=0001 and Y. 商品号 =0002; 5、检索没定购商品的顾客号和顾客名。select 顾客号 , 顾客名 from Custommer where 顾客号 not in (select 顾客号 from OrderItem); 6、检索一次定购商品号 0001商品数量最多的顾客号和顾客名。select 顾客号 , 顾客名 from Custommer where 顾客号 in(select 顾客号 from OrderItem where 商 品 号 =0001and 数 量 =(select MAX(数 量 )from OrderItem where

4、 商 品 号=0001); 7、检索男顾客的人数和平均年龄。 select count(*) 人数 ,avg( 年龄 ) 平均年龄 from Custommer where 性别 = 男; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 16 页 - - - - - - - - - 8、检索至少订购了一种商品的顾客数。select count(distinct 顾客号 ) from OrderItem; 9、检索订购了商品的人次数。select count( 顾客号 )

5、from OrderItem; select count(distinct 顾客号 ) from OrderItem; 10、检索顾客张三订购商品的总数量及每次购买最多数量和最少数量之差。select sum( 数量 ),MAX( 数量 )-MIN( 数量 ) from OrderItem,Custommer where OrderItem.顾客号 =Custommer.顾客号 and 顾客名 = 张三 ; 11、检索至少订购了3 单商品的顾客号和顾客名及他们定购的商品次数和商品总数量，并按商品总数量降序排序。select Custommer.顾客号 , 顾客名 ,count( *),Sum(

6、数量 ) from OrderItem,Custommer where OrderItem.顾客号 =Custommer.顾客号 group by Custommer.顾客号 , 顾客名having count( *)3 order by 4 desc; 12、检索年龄在 30至 40 岁的顾客所购买的商品名及商品单价。select 商 品 名 , 单 价from Custommer,Article,OrderItem where Custommer. 顾 客 号=OrderItem.顾客号 and Article.商品号 =OrderItem. 商品号 and 年龄 between 30 a

7、nd 40; 13、创建一个视图GM ，字段包括：顾客号，顾客名和定购的商品名，日期和金额（金额 =数量*单价）。指定用内连接方式做。create view GM as select Custommer.顾客号 , 顾客名 , 商品名 , 日期 , 单价 * 数量 as 金额from Custommer,Article,OrderItem where Custommer. 顾客号 =OrderItem. 顾客号 and Article.商品号 =OrderItem.商品号create view GM1 as select Custommer.顾客号 , 顾客名 , 商品名 , 日期 , 单价

8、* 数量 as 金额from (Custommer inner join OrderItem on Custommer.顾客号 =OrderItem.顾客号 ) inner join Article on Article.商品号 =OrderItem.商品号14、 检索购买的商品的单价至少有一次高于或等于1000 元的顾客号和顾客名。select Custommer.顾客号 , 顾客名 from Custommer,OrderItem,Article where Custommer. 顾客号 =OrderItem. 顾客号 and Article.商品号 =OrderItem.商品号名师资料总

9、结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 16 页 - - - - - - - - - and 单价 1000 15、 检索购买的购买价都高于或等于1000 元的顾客号和顾客名。 select Custommer.顾客号 , 顾客名 from Custommer where 顾客号 in (select 顾客号 from OrderItem where 顾客号 not in (select 顾客号 from OrderItem,Article where OrderItem.

10、商品号 =Article.商品号 and 单价 10) 22、建立断言：顾客的年龄必须大于18 岁。 Create ASSERTION A1 check ( not exists (select * from Custommer where 年龄 23) AND (SEX=M ) （3）检索至少选修LIU 老师所授课名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 16 页 - - - - - - - - - 程中一门课程的女学生姓名。SELECT SNAME FROM S

11、 WHERE SEX=F AND S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE TEACHER=LIU)NOTICE ：有多种写法，比如联接查询写法：SELECT SNAME FROM S,SC,C WHERE SEX=F AND SC.S#=S.S# AND SC.C#=C.C# AND TEACHER=LIU 但上一种写法更好一些。（4）检索 WANG 同学不学的课程的课程号。SELECT C# FROM C WHERE C# NOT IN (SELECT C# FROM SC WHERE S# IN (SELEC

12、T S# FROM S WHERE SNAME=WANG) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 16 页 - - - - - - - - - （5）检索至少选修两门课程的学生学号。SELECT DISTINCT X.SNO FROM SC X,SC Y WHERE X.SNO=Y.SNO AND X.CNOY.CNO Notice:对表 SC进行自连接，X，Y是 SC的两个别名。（6）检索全部学生都选修的课程的课程号与课程名。SELECT C#,CNAME F

13、ROM C WHERE NOT EXISTS (SELECT * FROM S WHERE S# NOT IN (SELECT * FROM SC WHERE SC.C#=C.C#) 要从语义上分解：（1）选择课程的课程号与课程名，不存在不选这门课的同学。其中，“不选这门课的同学”可以表示为：SELECT * FROM S WHERE S# NOT IN （ SELECT * FROM SC WHERE SC.C#=C.C#) 或者SELECT * FROM S WHERE NOT EXISTS （SELECT * FROM SC WHERE S.S#=C.S# AND SC.C#=C.C#

14、) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 16 页 - - - - - - - - - （7）检索选修课程包含LIU 老师所授课的学生学号。SELECT DISTINCT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE TEACHER=LIU) 2. 设有两个基本表R（A，B，C）和S（D，E，F），试用 SQL查询语句表达下列关系代数表达式：（1）A(R) (2) B=17(R) (3)RS(4) A,F(C=D(

15、RS) (1)SELECT A FROM R (2)SELECT * FROM R WHERE B=17 (3)SELECT A,B,C,D,E,F FROM R,S (4)SELECT A,F FROM R,S WHERE R.C=S.D 3. 设有两个基本表R（A，B，C）和 S（A，B，C）试用 SQL查询语句表达下列关系代数表达式：（包含）（1） RS （2） RS（3） RS （4） A,B(R)B,C(S) (1)SELECT A,B,C 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - -

16、- - - 第 8 页，共 16 页 - - - - - - - - - FROM R UNION SELECT A,B,C FROM S (2)SELECT A,B,C FROM R INTERSECT SELECT A,B,C FROM S (3)SELECT A,B,C FROM R WHERE NOT EXISTS (SELECT A,B,C FROM S WHERE R.A=S.A AND R.B=S.B AND R.C=S.C) (4)SELECT R.A,R.B,S.C FROM R,S WHERE R.B=S.B 4. 试用 SQL查询语句表达下列对教学数据库中三个基本表S、S

17、C 、C的查询： ( 集函数 )（1）统计有学生选修的课程门数。SELECT COUNT(DISTINCT C#) FROM SC 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 16 页 - - - - - - - - - （2）求选修 C4课程的学生的平均年龄。SELECT AVG(AGE) FROM S WHERE S# IN (SELECT S# FROM SC WHERE C#=C4) 或者，SELECT AVG(AGE) FROM S ， SC WHERE S

18、.S#=SC.S# AND C#=004 （3）求 LIU 老师所授课程的每门课程的学生平均成绩。SELECT CNAME,AVG(GRADE) FROM SC ,C WHERE SC.C#=C.C# AND TEACHER=LIU GROUP BY C# （4）统计每门课程的学生选修人数（超过 10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 16 页 - - - - - - - -

19、 - 课程号升序排列。SELECT DISTINCT C#,COUNT(S#) FROM SC GROUP BY C# HAVING COUNT(S#)10 ORDER BY 2 DESC, C# ASC （5）检索学号比 WANG 同学大，而年龄比他小的学生姓名。SELECT X.SNAME FROM S AS X, S AS Y WHERE Y.SNAME=WANG AND X.S#Y.S# AND X.AGE(SELECT AVG(AGE) FROM S AS Y WHERE Y.SEX=女) （9）求年龄大于所有女同学年龄的男学生姓名和年龄。SELECT SNAME,AGE FROM

20、S AS X WHERE X.SEX= 男 AND X.AGEALL (SELECT AGE FROM S AS Y WHERE Y.SEX= 女) 5. 试用 SQL更新语句表达对教学数据库中三个基本表S、SC 、C的各个更新操作： ( 插入操作 ) （1）往基本表S中插入一个学生元组（S9， WU ，18）。INSERT INTO S(S#,SNAME,AGE) VALUES(59,WU,18) （2）在基本表S中检索每一门课程成绩都大于等于80 分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表STUDENT （S， SANME ，SEX ）。INSERT INTO STU

21、DENT(S#,SNAME,SEX) SELECT S#,SNAME,SEX FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE GRADE80 AND S.S#=SC.S#) （3）在基本表SC中删除尚无成绩的选课元组。DELETE FROM SC 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 16 页 - - - - - - - - - WHERE GRADE IS NULL （4）把 WANG 同学的学习选课和成绩全部

22、删去。DELETE FROM SC WHERE S# IN (SELECT S# FROM S WHERE SNAME=WANG) （5）把选修MATHS 课不及格的成绩全改为空值。UPDATE SC SET GRADE=NULL WHERE GRADE60 AND C# IN (SELECT C# FROM C WHERE CNAME=MATHS) （6）把低于总平均成绩的女同学成绩提高5% 。UPDATE SC SET GRADE=GRADE*1.05 WHERE GRADE(SELECT AVG(GRADE) FROM SC) AND S# IN (SELECT S# FROM S WH

23、ERE SEX=F) （7）在基本表SC中修改 C4课程的成绩， 若成绩小于等于75 分时提高5% ， 若成绩大于75 分时提高 4% （用两个UPDATE 语句实现）。UPDATE SC SET GRADE=GRADE*1.05 WHERE C#=C4 AND GRADE75 6. “仓库管理”关系模型有五个关系模式：零件PART （P， PNAME ，COLOR ，WEIGHT ）项目PROJECT （J， JNAME ，DATE ）供应商SUPPLIER （ S ， SNAME ，SADDR ）供应PP（J， P， TOTOAL ）采购PS（P， S， QUANTITY ）名师资料总结

24、- - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 16 页 - - - - - - - - - （1）试用 SQL DDL语句定义上述五个基本表，并说明主键和外键。CREATE TABLE PART (P# CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL, COLOR CHAR(10),WEIGHT REAL, PRIMARY KEY(P#) CREATE TABLE PROJECT (J# CHAR(4) NOT NULL,JNAME CHAR(12

25、) NOT NULL, DATE DATE, PRIMARY KEY(J#) CREATE TABLE SUPLIER (S# CHAR(4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20), PRIMARY KEY(S#) CREATE TABLE P_P (J# CHAR(4),P# CHAR(4),TOTAL INTEGER, PRIMARY KEY(J#,P#), FOREIGN KEY(J#) REFERENCE PROJECT(J#), FOREIGN KEY(P#) REFERENCE PART(P#) CREATE TABLE P_S (P#

26、 CHAR(4),S# CHAR(4),QUANTITY INTEGER, PRIMARY KEY(P#,S#), FOREIGN KEY(P#) REFERENCE PART(P#), FOREIGN KEY(S#) REFERENCE SUPLIER(S#) （2）试将 PROGECT、PP、PART三个基本表的自然联接定义为一个视图VIEW1 ，PART 、PS、SUPPLIER 三个基本表的自然联接定义为一个视图VIEW2 。CREATE VIEW VIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL) AS SELECT PROJECT.J#

27、,JNAME,DATE,PART.P#,PNAME,COLOR,WEIGHT,TOTAL FROM PROJECT,PART,P_P WHERE PART.P#=P_P.P# AND P_P.J#=PROJECT.J# CREATE VIEW VIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 16 页 - - - - - - - - - AS SELECT PART.

28、P#,PNAME,COLOR,WEIGHT,SUPPLIER.S#,SNAME,SADDR,QUANTITY FROM PART,P_S,SUPPLIER WHERE PART.P#=P_S.P# AND P_S.S#=SUPPLIER.S# （3）试在上述两个视图的基础上进行数据查询：1）检索上海的供应商所供应的零件的编号和名字。SELECT P#,PNAME FROM VIEW2 WHERE SADDR=SHANGHAI 2）检索项目J4 所用零件的供应商编号和名字。SELECT S#,SNAME FROM VIEW2 WHERE P# IN(SELECT P# FROM VIEW1 WH

29、ERE J#=J4) 3.9 对于教学数据库中基本表SC ，已建立下列视图：CREATE VIEW SGRADE （S， C NUM ，AVG GRADE ）AS SELECT S， COUNT(C ) ，AVG （GRADE ）FROM SC GROUP BY S试判断下列查询和更新是否允许执行。若允许，写出转换到基本表SC上的相应操作。（1） SELECT * FROM S GRADE 允许SELECT S#,COUNT(C#),AVG(GRADE) FROM SC GROUP BY S# （2） SELECT S， CNUM FROM SGRADE WHERE AVG GRADE 80

30、允许SELECT S#,COUNT(C#) FROM SC WHERE AVG(GRADE)80 （3） SELECT S， AVG GRADE FROM SGRADE WHERE CNUM (SELECT CNUM FROM SGRADE WHERE S S4) 允许SELECT S#,AVG(GRADE) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 16 页 - - - - - - - - - FROM SC AS X WHERE COUNT(X.C#)(SELECT COUNT(Y.C#) FROM SC AS Y WHERE Y.S#=S4) GROUP BY S# （4） UPDATE SGRADE SET C NUM CNUM 1 WHERE S S4 不允许（5） DELETE FROM S GRADE WHERE CNUM 4 不允许名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 16 页 - - - - - - - - -