历年数据库查询.docx

1、历年数据库查询第一题： (注：查询答案不唯一，尽供参考)设某商业集团中有若干公司，其人事数据库中有3个基本表：职工关系 EMP（E#，ENAME，AGE，SEX，ECITY），其属性分别表示职工工号、姓名、年龄、性别和居住城市。工作关系 WORKS（E#，C#，SALARY），其属性分别表示职工工号、工作的公司编号和工资。公司关系 COMP（C#，CNAME，CITY，MGR_E#），其属性分别表示公司编号、公司名称、公司所在城市和公司经理的工号。1）用CREATE TABLE语句创建上述3个表，需指出主键和外键。CREATE TABLE EMP( E# CHAR(4) NOT NULL,EN

2、AME CHAR(8) NOT NULL,AGE SMALLINT,SEX CHAR(1),ECITY CHAR(20),PRIMARY KEY(E#)OR:CREATE TABLE EMP( E# CHAR(4) PRIMARY KEY,ENAME CHAR(8) NOT NULL,AGE SMALLINT,SEX CHAR(1),ECITY CHAR(20)CREATE TABLE COMP( C# CHAR(4) NOT NULL,CNAME CHAR(20) NOT NULL,CITY CHAR(20),MGR_E# CHAR(4),PRIMARY KEY(C#),FOREIGN KE

3、Y(MGR_E#) REFERENCES EMP(E#)CREATE TABLE WORKS( E# CHAR(4) NOT NULL,C# CHAR(4) NOT NULL,SALARY SMALLINT,PRIMARY KEY(E#,C#),FOREIGN KEY(E#) REFERENCES EMP(E#),FOREIGN KEY(C#) REFERENCES COMP(C#)2）检索至少为两个公司工作的职工工号。SELECT X.E#FROM WORKS X, WORKS YWHERE X.E#= Y.E# AND X.C# != Y.C#3）检索与其经理居住在同一城市的职工的工号和姓

4、名。SELECT A.E#, A.ENAMEFORM EMP A, WORKS B, COMP C, EMP DWHERE A.E#=B.E# AND B.C#=C.C# AND C.MGR_E#=D.E# AND A.ECITY=D.CITY4）检索职工人数最多的公司的编号和名称。SELECT C.C#, C,CNAMEFORM WORKS B,COMP CWHERE B.C#=C.C#GROUP BY C.C#HAVING COUNT(*)=ALL(SELECT COUNT(*)FROM WORKSGROUP BY C#)5）检索平均工资高于联华公司平均工资的公司的编号和名称。SELECT

5、 C.C#, C.CNAMEFROM WORKS B, COMP CWHERE B.C#=C.C#GROUP BY C.C#HAVING AVG(SALARY)(SELECT AVG(SALARY)FROM WORKS B,COMP CWHERE B.C#=C.C# AND CNAME= 联华公司)6）为联华公司的职工加薪，月薪不超过3000元的职工加薪10%，超过3000元的职工加薪8%。UPDATE WORKS SET SALARY= SALARY*1.1 WHERE SALARY3000 AND C# IN (SELECT C#FROM COMPWHERE CNAME= 联华公司)7）在

6、EMP表和WORKS表中删除年龄大于60岁的职工的有关元组。DELETE FROM WORKS WHERE E# IN (SELECT E# FROM EMP WHERE AGE60)DELETE FROM EMP WHERE AGE60第二题： 设一个电影资料数据库有3个基本表：电影表Movie、演员表Actor和电影主演表Acts。Movie关系的属性包括电影名、制作年份、长度等；Actor关系的属性包括演员名、地址、性别、生日等；电影主演表Acts包括电影名、制作年份、演员姓名等。用SQL实现如下操作： (1)为1990-1999年制作的电影建立视图；CREATE VIEW MyearA

7、SSELECT * FROM MovieWHERE Year1990 AND Year1999(2)对视图进行如下查询：长度超过120分钟的电影名称和演员姓名。SELECT Title，actorNameFROM Myear，ActsWHERE Acts.MovieTitle=Myear.Title AND Length120(3) 要求为Movie关系增加一个属性MovieDirector(电影导演）。用SQL实现这种关系模式的改变。ALTER TABLE MovieADD MovieDirector CHAR（20）(4)把电影“我的世界”的长度改为109分钟；UPDATE Movie S

8、ET Length109WHERE Title=我的世界(5)删除1940年以前制作的所有电影记录以及电影主演记录。DELETE FROM MovieWHERE Year1940；DELETE FROM ActsWHERE MovieYear1940(6)所有同名电影各自的电影名和制作年份；SELECT M1Title，M1Year，M2YearFROM Movie AS M1，Movie AS M2WHERE M1TitleM2Title AND M1YearM2Year(7)向演员关系Actor中插入一个演员记录，具体的分量由自己指定。INSERT INTO Actor(Name，Gend

9、er）VALUES (秀兰邓波儿，F）(8)统计1999年制作电影的平均长度和最短长度；SELECT AVG（Length），MIN（Length)FROM MovieWHEREYear1999第一题：设某商业集团中有若干公司，其人事数据库中有3个基本表：职工关系 EMP（E#，ENAME，AGE，SEX，ECITY），其属性分别表示职工工号、姓名、年龄、性别和居住城市。工作关系 WORKS（E#，C#，SALARY），其属性分别表示职工工号、工作的公司编号和工资。公司关系 COMP（C#，CNAME，CITY，MGR_E#），其属性分别表示公司编号、公司名称、公司所在城市和公司经理的工号。1

10、）建立一个有关女职工信息的视图EMPWOMAN，属性包括（E#，ENAME，C#，CNAME，SALARY）。CREATE VIEW EMPWOMANAS SELECT A.E#,A.ENAME, C.C#,CNAME, SALARY FROM EMP A, WORKS B, COMP C WHERE A.E#=B.E# AND B.C#=C.C# AND SEX=F2）检索居住城市和公司所在城市相同的职工工号和姓名。SELECT A.E#,A.ENAMEFROM EMP A, WORKS B,COMP CWHERE A.E#=B.E#, AND B.C#=C.C#, AND A.ECITY=

11、C.CITY3）假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和工资总数。显示（E#，NUM，SUMSALARY），分别表示工号、公司数目和工资总数。SELECT E#, COUNT(C#) AS SUM, SUM(SALARY) AS SUMSALARYFROM WORKSGROUP BY E#4）检索工资高于其所在公司职工平均工资的所有职工的工号和姓名。SELECT A.E#, A.ENAMEFROM EMP A, WORKS BWHERE A.E#=B.E# AND SALARY(SELECT AVG(SALARY)FROM WORKS CWHERE C.C#=B.C#)5）检

12、索工资总额最小的公司的编号和名称。SELECT C.C#, C,CNAMEFORM WORKS B,COMP CWHERE B.C#=C.C#GROUP BY C.C#HAVING SUM(SALARY)SX.AGEANDSX.SN张三；(3) 查询课程名和直接先行课的课程名以及间接先行课的课程名。SELECTCN，CX.CN，CY.CNFROMC，CASCX，CASCYWHEREC.PCNO=CX.CNOANDCX.PCNO=CY.CNO；(4) 将选修课程“DB的学生学号SNO，姓名SN建立视图SDB。CREATE VIEW SDB AS SELCET SNO,SN FROMSWHERE

13、SNO INSELECT SNOFROMS CWHERE CNO IN（SELECT CNOFROM CWHERE CNDB）(5) 选修课程“DB”的学生姓名SN。答案一SELECT SNFROM SWHERE SNO INSELECT SNOFROM SCWHERE CNO IN（SELECT CNOFROM CWHER ECNDB）；答案二SELECT SNFROM S，SC，CWHERE S.SNOSC.SNOAND SC.CNOC.CNOAND C.CNDB；(6) 所有学生都选修的课程名CN。SELECTCNFROMCWHERENOTEXISTS（SELECT*FROMSWHERE

14、NOTEXISTS（SELECT*FROMSCWHERESNO=S.SNOANDCNO=C.CNO)；答案二SELECTCNFROMCWHERECNOIN（SELECTCNOFROMSCGROUPBYCNOHAVINGCOUNT（*）=（SELECTCOUNT（*）FROMS)；第一题： 对于教学数据库中的4个关系：教师关系 T(T#, TNAME, TITLE)课程关系 C(C#, CNAME, T#)学生关系 S(S#, SNAME, AGE, SEX)选课关系 SC(S#, C#,SCORE)其中，T中T#为主键；C中C#为主键，T#为外键；S中S#为主键；SC中S#, C#为主键且同时

15、为外键。用SQL实现如下操作： (1) 用语句创建上述四个基本表：T，C，S，SC，并正确表达其数据类型和主、外键约束。 CREATE TABLE T (T# CHAR(4) NOT NULL, TNAME CHAR(8) NOT NULL, TITLE CHAR(10), PRIMARY KEY(T#) CREATE TABLE C (C# CHAR(4), CNAME CHAR(10) NOT NULL, T# CHAR(4), PRIMARY KEY(C#), FOREIGN KEY (T#) REFERENCE T(T#); CREATE TABLE S (S# CHAR(4) NOT

16、 NULL, SNAME CHAR(8) NOT NULL, AGE SMALLINT, SEX CHAR(2), PRIMARY KEY(S#); CREATE TABLE SC (S# CHAR(4), C# CHAR(4), SCORE SMALLINT, PRIMARY KEY (S#,C#), FOREIGN KEY (S#) REFERENCE S(S#), FOREIGN KEY (C#) REFERENCE C(C#);(2) 检索学习课程号为C2课程的学生学号与成绩。（3分） SELECT S#, SCORE FROM SC WHERE C#=C2;(3) 检索至少选修了LI

17、U老师所授课程中一门课程的学生学号与姓名。SELECT S,S#, SNAME FROM S,SC, C, T WHERE S.S#= SC.S# AND SC.C#=C.C.# AND C.T#=T.T# AND TNAME=LIU(4) 检索选修课程号为C2和C4的学生学号。(5) 求每一教师每门课程的学生选修人数（超过50人），要求显示教师工号、课程号和学生人数。显示时，查询结果按人数升序排列，人数相同按工号升序、课程号降序排列。 SELECT T#, C.C#, COUNT(S#) FROM C,SC WHERE C.C#=SC.C# HAVING COUNT(*)50 ORDER B

18、Y 3, T#, C.C# DESC;(6) 检索平均成绩最高的学生学号。 SELECT S# FROM SC GROUP BY S# HAVING AVG(SCORE) = ALL (SELECT AVG(SCORE) FROM SC GROUP BY S#)(7) 往基本表S中插入一个元组（S36, GU, 20, M）。 INSERT INTO S(S#,SNAME, AGE,SEX) VALUES (S36, GU, 20. M)(8) 把C4课程中小于该课程平均成绩的成绩元组从基本表SC中删除。 DELETE FROM SC WHERE C# C4 AND SCORE (SELECT

19、 AVG(SCORE) FROM SC WHERE C# = C4)(9) 当C4课程的成绩低于该门课程平均成绩时，提高5%。 UPDATE SC SET SCORE = SCORE * 1.05 WHERE C# = C4 AND SCORE (SELECT AVG(SCORE) FROM SC WHERE C# =C4)(10) 基于基本表S、SC、C，建立关于S#、SNAME、CNAME和SCORE的视图。 CREATE VIEW STUDENT_SCORE (S#, SNAME, CNAME, SCORE) AS SELECT S.S#, SNAME, CNAME, SCORE FRO

20、M S, SC, C WHERE S.S#= SC.S# AND SC.C# =C.C#第二题： 关系模式如下：商品P(PNO，PN，COLOR，PRICE)商店S(SNO，SN，CITY）销售SP(PNO，SNO,QTY)用SQL实现如下操作：(1) 查询销售所有商品的商店名SN。SELECT SN FROM SWHERE SNO IN（SELECT SNOFROM SPGROUP BY SNOHAVING COUNT（*）=（SELECT COUNT（*）FROM P）；答案二SELECT SN FROM SWHERE NOT EXISTS（SELECT * FROM PWHERE NOT

21、 EXISTS（SELECT * FROM SPWHERE SNOS.SNO AND PNOP.PNO）； (2) 将在London销售红色商品的商店号SNO，商店名SN建立视图RLS。答案一CREATEVIEWRLSASSELECTSNO，SNFROMS，SP，PWHERES.SNOSP.SNOANDSP.PNOP.PNOANDS.CITYLondonANDP.COLORRed；答案二CREATEVIEWRLSASSELECTSNO，SNFROMSWHERECITYLondonANDSNOIN（SELECTSNOFROMSPWHEREPNOIN（SELECTPNOFROMPWHERECOLO

22、RRed）；第一题： 对于教学数据库中的4个关系：教师关系 T(T#, TNAME, TITLE)课程关系 C(C#, CNAME, T#)学生关系 S(S#, SNAME, AGE, SEX)选课关系 SC(S#, C#,SCORE)其中，T中T#为主键；C中C#为主键，T#为外键；S中S#为主键；SC中S#, C#为主键且同时为外键。用SQL实现如下操作：(1) 在基本表S中增加一个地址（ADDRESS）列。 ALTER TBALE S ADD ADDRESS VARCHAR(30);(2) 为基本表SC中的（S#，C#）建立唯一索引。 CREATE UNIQUE INDEX SC_IND

23、EX ON SC( S# ASC, C# DESC);(3) 检索学习课程号为C2课程的学生学号与姓名。 SELECT S, S#, SNAME FROM S,SC WHERE S.S#= SC.S# AND C#=C2;(4) 在基本表SC中检索男同学选修的课程号 SELECT DISTINCT C# FROM S,SC WHERE S.S#=SC.S# AND SEX=M(5) 检索选修课程号为C2或C4的学生学号。(6) 检索不学C2课程的学生姓名与年龄。 SELECT SNAME, AGE FROM S WHER S# NOT IN (SELECT S# FROM SC WHERE C

24、#=C2)(7) 统计每门课程的学生选修人数。 SELECT C.C#, COUNT(S#) FROM C,SC WHERE C.C#=SC.C# GROUP BY C.C#(8) 在基本表S和SC中检索至少不学C2和C4两门课程的学生学号。 SELECT S# FORM S WHERE S# NOT IN (SELECT S# FROM SC WHERE C# IN (C2,C4)(9) 往基本表SC中插入一个选课元组（S5， C8），此处成绩值为空值。 INSERT INTO SC(S#, C#) VALUES (S5, C8)(10) 把课程名为MATHS的成绩从基本表SC中删除。 DE

25、LETE FROM SC WHERE C# IN (SELECT C# FROM C WHERE CNAME = MATHS)(11) 把女同学的成绩提高10%。 UPDATE SC SET SCORE = SCORE * 1.1 WHERE S# IN (SELECT S# FROM S WHERE SEX =F)(12) 定义一个有关男学生的视图。 CREEATE VIEW S_MALE AS SELECT S#, SNAME, AGE FROM S WHERE SEX = M第二题： 关系模式如下：商品P(PNO，PN，COLOR，PRICE)商店S(SNO，SN，CITY）销售SP(P

26、NO，SNO,QTY)用SQL实现如下操作：(1) 所有商店都销售的商品的商品号PNO。SELECT PNO FROM SPGROUP BY PNOHAVING COUNT（*）（SELECT COUNT(*）FROM S)；(2) 查询与商品“TV”颜色相同的商品名PN。SELECT P.PNFROM P, P PXWHERE P.COLORPX.COLOR AND PX.PNTV；(3) 查询销售商品“TV”的商店名SN。SELECTS.SNFROM S，SP，PWHERE S.SNOSP.SNO AND P.PNOSP.PNOAND P.PN=TV第一题： 学生课程数据库的数据示例如下图

27、所示，写出下列各题的SQL语句。不要求写出执行后的结果。Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200215121李勇男20CS200215122刘晨女19CS200215123王敏女18MA200215125张立男19ISCourse SC课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64学号Sno课程号Cno成绩Grade200215121192200215121285200215121388200215122290200215122380 1. 画出PowerDesigner中上述学生课程数据库的物理数据模型。2. 用SQL语句建立一个“课程”表Course，须在语句中写出主键和外键。CREATE TABLE Course（Cno CHAR(4) PRIMAR