历年数据库查询
共17第17页第一题：(注：查询答案不唯一，尽供参考)设某商业集团中有若干公司，其人事数据库中有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 SALARY<=3000 AND C# IN (SELECT C#FROM COMPWHERE CNAME= 联华公司)UPDATE WORKS SET SALARY= SALARY*1.08 WHERE SALARY>3000 AND C# IN (SELECT C#FROM COMPWHERE CNAME= 联华公司)7）在EMP表和WORKS表中删除年龄大于60岁的职工的有关元组。DELETE FROM WORKS WHERE E# IN (SELECT E# FROM EMP WHERE AGE>60)DELETE FROM EMP WHERE AGE>60第二题： 设一个电影资料数据库有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)<=ALL(SELECT SUM(SALARY)FROM WORKSGROUP BY C#)6）WANG职工的居住地改为苏州市。UPDATE EMP SET ECITY=苏州市 WHERE ENAME=WANG7）为联华公司的经理加薪8%。UPDATE WORKS A SET SALARY= SALARY*1.08WHERE EXISTS (SELECT *FROM COMP B WHERE B.C#=A.C# AND B.CNAME=联华公司AND B.MGR_E#=A.E#)8）在WORKS基本表中，删除联华公司的所有职工元组。DELETE FROM WORKSWHERE C# IN (SELECT C#FROM COMPWHERE CNAME=联华公司)第二题： 关系模式如下：学生S(SNO，SN，SEX，AGE)课程C(CNO，CN，PCNO)PCNO为直接先行课号选课SC(SNO，CNO，G)G为课程考试成绩用SQL写出查询程序：(1) 查询选修了所有以“MAT”为先行课的课程的学生姓名SN。SELECTSNFROMS，SCWHERES.SNO=SC.SNOANDSC.CNOIN（SELECTC.CNOFROMC，CASCXWHEREC.PCNOCX.CNOANDCX.CNMAT'）；(2) 查询年龄（AGE)大于“张三”年龄的学生姓名和年龄。SELECTS.SN，S.AGEFROMS，SASSXWHERES.AGE>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 CNOFR
