SQL

http://www.sohu.com/a/206931104_100012573

1：假设有一个“职工”表，表结构如下：(14分)

职工号	姓名	年龄	月工资	部门号	电话	办公室
1	张三	25	2000	1	12345	101
2	李四	26	1500	1	54321	102
3	王五	23	900	2	23456	201
…..	….	…..	……	…….	……	……..

问题：

查询每个部门中月工资最高的“职工号”的SQL查询语句如下：

SELECT 职工号 FROM 职工 AS A

WHERE职工.月工资 ＝ （SELECT MAX(月工资) FROM 职工 AS BWHERE A.部门号 ＝ B.部门号;

1:请简要的说明该查询语句对查询效率的影响(6分)

2:对该查询语句进行修改，使它既可以完成相同的功能，又可以提高查询效率。(8分)

1．对于外层职工关系A 中的每一个记录，都要对内层职工关系B进行检索，所有效率不高

2．（1）使用临时表

SELECT MAX(月工资) as 最高工资，部门号 INTO tempFROM 职工

GROUP BY 部门号;

SELECT 职工号 FROM 职工，temp WHERE 月工资＝最高工资

AND 职工.部门号 ＝ temp.部门号；

(2)

SELECT 职工号 FROM 职工，（SELECT MAX(月工资) as 最高工资，部门号 FROM 职工 GROUP BY 部门号） as DEPMAX

WHERE 月工资 ＝ 最高工资 AND 职工.部门号 ＝ DEPMAX.部门号;

2：有一个“职工”表，表结构如下：(14分)

职工号	姓名	年龄	月工资	部门号	电话	办公室
1	张三	25	2000	1	12345	101
2	李四	26	1500	1	54321	102
3	王五	23	900	2	23456	201
…..	….	…..	……	…….	……	……..

问题：

假设在“职工”表中的“年龄”和“月工资”字段上创建了索引，下列的查询语句可能不会促使查询优化器使用索引，从而降低了效率，请写出可以完成相同功能又可以提高查询效率的SQL语句

SELECT 姓名，年龄，月工资 FROM 职工

WHERE 年龄 > 45 OR 月工资 < 1000;

SELECT 姓名，年龄，月工资 FROM 职工

WHERE 年龄 > 45

UNION

SELECT 姓名，年龄，月工资 FROM 职工

WHERE月工资 < 1000;

使用UNION就可以促使 查询优化器使用索引

3：设有关系EMP（ENO，ENAME，SALARY，DNO），其中各属性的含义依次为职工号、姓名、工资和所在部门号，

以及关系DEPT（DNO，DNAME，MANAGER），其中各属性含义依次为部门号、部门名称、部门经理的职工号。（回答下列题目）(20分)

1. 列出各部门中工资不低于600元的职工的平均工资。(10分)

2. 请用SQL语句将“销售部”的那些工资数额低于600的职工的工资上调10%。(10分)

1.SELECT DNO, AVG (SALARY)

FROM EMP

WHERE SALARY＞=600

GROUP BY DNO

2.UPDATE EMP

SET SALARY=SALARY*1.1

WHERE ENO IN

(SELECT ENO

FROM EMP, DEPT

WHERE EMP.DNO=DEPT.DNO

AND DNAME='销售部'

AND SALARY＜600;

或者

UPDATE EMP

SET SALERY = SALERY * 1.1

FROM EMP A,ENO B

WHERE A.DNO = B.DNO

AND B.DNAME='销售部'

AND B.SALARY＜600;

4：设职工---社团数据库有三个基本表：(20分)

职工（职工号，姓名，年龄，性别）；

社会团体（编号，名称，负责人，活动地点）；

参加（职工号，编号，参加日期）；

其中：

1）职工表的主键为职工号。

2）社会团体表的主键为编号；外码为负责人，被参照表为职工表，对应属性为职工号。

3）参加表的职工号和编号为主键；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。

试用SQL语句表达下列操作：

1）定义职工表、社会团体表和参加表，并说明其主键和参照关系。

2）建立下列两个视图。

社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；

参加人情况（职工号，姓名，社团编号，社团名称，参加日期）

3）查找参加唱歌队或篮球队的职工号和姓名。

4）求参加人数超过100人的社会团体的名称和负责人。

答：

1） CREATTABLE职工（职工号　CHAR（10）NOTNULL，

姓名　CHAR（8）NOTNULL，

年龄　SMALLINT，

性别　CHAR（2），

CONSTRAINT　C1_PKPRIMARYKEY（职工号）；　

CREAT　TABLE社会团体（编号　CHAR（8）NOTNULL，　

名称　CHAR（12）NOTNULL，

负责人　CHAR（8），

活动地点　VARCHAR（50），

CONSTRAINT　C2_PK PRIMARYKEY（职工号）

CONSTRAINT　C2FOREIGNKEY（负责人）REFERENCES 职工（职工号））；

CREAT　TABLE参加（职工号　CHAR（8），

编号　CHAR（8），

参加日期　DATA，

CONSTRAINT　C3_PKPRIMARYKEY（职工号，编号），

CONSTRAIN C3 FOREIGN KEY(职工号)REFERENCES职工（职工号））；

2）

CREAT VIEW 社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）

AS 　SELECT 编号，名称，负责人，姓名，性别

FROM社会团体，职工

WHERE社会团体.负责人＝职工.职工号;

CREAT VIEW参加人情况（职工号，姓名，社团编号，参加日期）

AS　SELECT参加.职员号,姓名，社会团体.编号，名称，参加日期 FROM 职工，社会团体，参加

WHERE职工.职工号＝参加.职工号AND 参加.编号＝社会团体.编号;

3) SELECT职工号，姓名

FROM职工，社会团体，参加

WHERE职工.职工号＝参加.职工号 AND 参加.编号＝社会团体.编号

AND社会团体.名称 IN（'唱歌队'，'篮球队'）；

4）　SELECT社会团体.名称，职工.姓名　

FROM职工，社会团体，参加

WHERE社会团体.编号＝参加.编号　AND社会团体.负责人＝职工.职工号

GROUP　BY参加.编号　HAVINGCOUNT（参加.编号）＞100；

5：设工程—零件数据库中有四个基本表：（18分）

供应商（供应商代码，姓名，所在城市，联系电话）；

工程（工程代码，工程名，负责人，预算）；

零件（零件代码，零件名，规格，产地，颜色）；

供应零件（供应商代码，工程代码，零件代码，数量）

试用SQL语句完成下列操作：

1） 查找预算在50000~100000元之间的工程的信息，并将结果按预算降序排列。

2) 找出使用上海产的零件的工程名称。

3)将由供应商S5供给工程代码为J4的零件P6改为由S3供应，并将零件P6的产地改为S3供货商的所在城市。

4）从供应商关系中删除S2的记录，并从供应零件关系中删除相应的记录。

1.SELECT*

FROM 工程

WHERE预算　BETWEEN50000AND100000 (或者 WHERE 预算>= 50000 AND 预算<= 1000000)

ORDER　BY预算　DESC；

2.SELECT工程名

FROM供应商，供应零件，工程

WHERE供应商.供应商代码＝供应零件.供应商代码AND

供应零件.工程代码＝工程.工程代码　AND

供应商.所在城市＝'上海'；

3.UPDATE供应零件

SET供应商代码＝'S3'

WHERE供应商代码＝'S5' AND 工程代码＝'J4' AND 零件代码＝'P6';

UPDATE零件

SET产地 = B. 所在城市

FROM 零件 A, 供应商 B

WHERE A. 零件代码 = 'P6'

AND B. 供应商代码 = 'S3';

4.DELETE

FROM 供应零件

WHERE 供应商代码='S2';

DELETE

FROM 供应商

WHERE 供应商代码='S2';