历年数据库查询

共17第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,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#HA VING COUNT(*)>=ALL(SELECT COUNT(*)FROM WORKSGROUP BY C#)5）检索平均工资高于联华公司平均工资的公司的编号和名称。

SELECT C.C#, AMEFROM WORKS B, COMP CWHERE B.C#=C.C#GROUP BY C.C#HA VING A VG(SALARY)>(SELECT A VG(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 Year＞＝1990 AND Year＜＝1999(2)对视图进行如下查询：长度超过120分钟的电影名称和演员姓名。

SELECT Title，actorNameFROM Myear，ActsWHERE Acts.MovieTitle=Myear.Title AND Length＞120(3) 要求为Movie关系增加一个属性MovieDirector(电影导演）。

用SQL实现这种关系模式的改变。

ALTER TABLE MovieADD MovieDirector CHAR（20）(4)把电影“我的世界”的长度改为109分钟；UPDATE Movie SET Length＝109WHERE Title=‘我的世界’(5)删除1940年以前制作的所有电影记录以及电影主演记录。

DELETE FROM MovieWHERE Year＜1940；DELETE FROM ActsWHERE MovieYear＜1940(6)所有同名电影各自的电影名和制作年份；SELECT M1．Title，M1．Year，M2．YearFROM Movie AS M1，Movie AS M2WHERE M1．Title＝M2．Title AND M1．Year＜M2．Year(7)向演员关系Actor中插入一个演员记录，具体的分量由自己指定。

INSERT INTO Actor(Name，Gender）V ALUES (‘秀兰·邓波儿’，‘F’）(8)统计1999年制作电影的平均长度和最短长度；SELECT A VG（Length），MIN（Length)FROM MovieWHEREYear＝1999第一题：设某商业集团中有若干公司，其人事数据库中有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, SALARYFROM EMP A, WORKS B, COMP CWHERE A.E#=B.E# AND B.C#=C.C# AND SEX=’F’2）检索居住城市和公司所在城市相同的职工工号和姓名。

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 A VG(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#HA VING SUM(SALARY)<=ALL(SELECT SUM(SALARY)FROM WORKSGROUP BY C#)6）W ANG职工的居住地改为苏州市。

UPDATE EMP SET ECITY=’苏州市’ WHERE ENAME=’W ANG’7）为联华公司的经理加薪8%。

UPDATE WORKS A SET SALARY= SALARY*1.08WHERE EXISTS (SELECT *FROM COMP BWHERE B.C#=A.C# AND AME=’联华公司’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.SNOOIN（OFROMC，CASCXWHEREC.PCNO＝O＝‘MA T'）；(2) 查询年龄（AGE)大于“张三”年龄的学生姓名和年龄。

SELECTS.SN，S.AGEFROMS，SASSXWHERES.AGE>SX.AGEANDSX.SN＝‘张三’；(3) 查询课程名和直接先行课的课程名以及间接先行课的课程名。

SELECTCN，，FROMC，CASCX，CASCYWHEREC.PCNO=OANDCX.PCNO=O；(4) 将选修课程“DB"的学生学号SNO，姓名SN建立视图SDB。

CREATE VIEW SDBASSELCET SNO,SNFROMSWHERE SNO INSELECT SNOFROMS CWHERE CNO IN（SELECT CNOFROM CWHERE CN＝‘DB'））(5) 选修课程“DB”的学生姓名SN。