历年数据库查询.doc

第一题：(注：查询答案不唯一，尽供参考)设某商业集团中有若干公司，其人事数据库中有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,ENAME 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 KEY(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）检索与其经理居住在同一城市的职工的工号和姓名。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 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）在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 MyearASSELECT * 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 SET 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，Gender）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）建立一个有关女职工信息的视图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=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）检索工资总额最小的公司的编号和名称。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 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*FROMSWHERENOTEXISTS（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#为主键且同时为外键。用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 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) 检索至少选修了LIU老师所授课程中一门课程的学生学号与姓名。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 BY 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 AVG(SCORE) FROM SC WHERE C# = C4)(9) 当C4课程的成绩低于该门课程平均成绩时，提高5%。 UPDATE SC SET SCORE = SCORE * 1.05 WHERE C# = C4 AND SCORE 39. 查询选修2号课程且成绩在90分以上的所有学生。SELECT Student.Sno, SnameFROM Student, SCWHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade90;10. 查询与“刘晨”在同一个系学习的学生。SELECT Sno, Sname, SdeptFROM StudentWHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname= 刘晨);11. 查询选修了课程名为“信息系统”的学生学号和姓名。SELECT Student, Sno, SnameFROM Student, SC, CourseWHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname= 信息系统;12. 找出每个学生超过他选修课程平均成绩的课程号。SELECT Sno, CnoFROM SC xWHERE Grade=(SELECT AVG(Grade)FROM SC yWHERE y.Sno=x.Sno); 13. 查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄。SELECT Sname, SageFROM StudentWHERE Sage(SELECT MAX(Sage)FROM StudentWHERE Sdept=CS) AND Sdept CS14. 将计算机科学系全体学生的成绩置零。UPDATE SCSET Grade=0WHERE CS=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno)15. 删除计算机科学系所有学生的选课记录。DELETEFROM SCWHERE CS=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno);第二题： 关系模式如下：商品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 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（SELECTPNOFROMPWHERECOLORRed）；第一题： 学生课程数据库的数据示例如下图所示，写出下列各题的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 16. 查询计算机科学系全体学生的名单。SELECT SnameFROM StudentWHERE Sdept=CS17. 查询考试成绩有不及格的学生的学号。SELECT DISTINCT SnoFROM SCWHERE Grade6018. 查询年龄在20到23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECT Sname, Sdept, SageFROM StudentWHERE Sage BETWEEN 20 AND 2319. 查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。SELECT Sname, SsexFROM StudentWHERE Sdept IN(CS, MA,IS)20. 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。（4分）SELECT *FROM StudentORDER BY Sdept,Sage DESC21. 查询名字中第2个字为“阳”字的学生的姓名和学号。SELECT Sname, SnoFROM StudentWHERE Sname LIKE _阳%22. 查询计算机科学系年龄在20岁以下的学生姓名。SELECT SnameFROM StudentWHERE Sdept=CS AND Sage2023. 求各个课程号及相应的选课人数。SELECT Cno, COUNT(Sno)FROM SCGROUP BY Cno24. 查询其他系中比计算机科学系所有学生年龄小的学生姓名和年龄。SELECT Sname, SageFROM StudentWHERE Sage(SELECT MIN(Sage)FROM StudentWHERE Sdept=CS) AND Sdept CS25. 查询所有选修了1号课程的学生姓名。SELECT SnameFROM StudentWHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1)26. 查询计算机科学系的学生及年龄不大于19岁的学生。SELECT *FROM StudentWHERE Sdept=CSUNIONSELECT *FROM StudentWHERE Sage=1927. 查询既选修了课程1又选修了课程2的学生。SELECT SnoFROM SCWHERE Cno=1INTERSECTSELECT SnoFROM SCWHERE Cno=228. 将所有学生的年龄增加1岁。UPDATE StudentSET Sage=Sage+129. 建立信息系选修了1号课程的学生的视图。CREATE VIEW IS_S1(Sno, Sname, Grade)ASSELECT Student.Sno, Sname, GradeFROM Student, SCWHERE Sdept=IS AND Student.Sno=SC.Sno AND SC.Cno=1第二题： 关系模式如下：商品P(PNO，PN，COLOR，PRICE)商店S(SNO，SN，CITY）销售SP(PNO，SNO,QTY)用SQL实现如下操作：(1) 所有商店都销售的商品的商品号PNO。答案一若销售关系中某商品号出现次数=商店总数，则该商品号为所求。SELECT PNOFROM SPGROUP BY PNOHAVING COUNT（*）（SELECT COUNT(*）FROM S)；答案二SELECT PNOFROM SP AS SPXWHERE NOT EXISTS（SELECT *FROM SP AS SPYWHERE NOT EXISTS（SELECT *FROM SPWHERE SNO=SPY.SNO AND PNO=SPX.PNO)）；(2) 查询与商品“TV”颜色相同的商品名PN。SELEC TP.PNFROM P,P AS PXWHERE P.COLORPX.COLOR AND PX.PNTV；(3) 查询销售商品“TV”的商店名SN。SELECT S.SNFROM S，SP，PWHERE S.SNOSP.SNO