历年数据库查询..

共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 Y

WHERE X.E#= Y.E# AND X.C# != Y.C#

3）检索与其经理居住在同一城市的职工的工号和姓名。

SELECT A.E#, A.ENAME

FORM EMP A, WORKS B, COMP C, EMP D

WHERE A.E#=B.E# AND B.C#=C.C# AND C.MGR_E#=D.E# AND A.ECITY=D.CITY

4）检索职工人数最多的公司的编号和名称。

SELECT C.C#, C,CNAME

FORM WORKS B,COMP C

WHERE B.C#=C.C#

GROUP BY C.C#

HA VING COUNT(*)>=ALL(SELECT COUNT(*)

FROM WORKS

GROUP BY C#)

5）检索平均工资高于联华公司平均工资的公司的编号和名称。

SELECT C.C#, AME

FROM WORKS B, COMP C

WHERE B.C#=C.C#

GROUP BY C.C#

HA VING A VG(SALARY)>(SELECT A VG(SALARY)

FROM WORKS B,COMP C

WHERE 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 COMP

WHERE CNAME=’联华公司’)

UPDATE WORKS SET SALARY= SALARY*1.08 WHERE SALARY>3000 AND C# IN (SELECT C#

FROM COMP

WHERE 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 MyearAS

SELECT *

FROM Movie

WHERE Year＞＝1990 AND Year＜＝1999

(2)对视图进行如下查询：长度超过120分钟的电影名称和演员姓名。

SELECT Title，actorName

FROM Myear，Acts

WHERE Acts.MovieTitle=Myear.Title AND Length＞120

(3) 要求为Movie关系增加一个属性MovieDirector(电影导演）。用SQL实现这种关系模式的改变。

ALTER TABLE Movie

ADD MovieDirector CHAR（20）

(4)把电影“我的世界”的长度改为109分钟；

UPDATE Movie SET Length＝109

WHERE Title=‘我的世界’

(5)删除1940年以前制作的所有电影记录以及电影主演记录。

DELETE FROM Movie

WHERE Year＜1940；