中兴SQL面试题汇总

中兴SQL题目

7：假设有一个“职工”表，表结构如下：(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 B WHERE A.部门号 ＝ B.部门号;

1:请简要的说明该查询语句对查询效率的影响(6分)
2:对该查询语句进行修改，使它既可以完成相同的功能，又可以提高查询效率。(8分)
1． 对于外层职工关系 A 中的每一个记录，都要对内层职工关系B进行检索，所有效率不高
2． （1）使用临时表
      SELECT MAX(月工资) as 最高工资，部门号 INTO temp FROM 职工
GROUP BY 部门号;
SELECT 职工号 FROM 职工，temp WHERE 月工资＝最高工资
AND 职工.部门号 ＝ temp.部门号；
(2)
SELECT 职工号 FROM 职工，（SELECT MAX(月工资) as 最高工资，部门号 FROM 职工 GROUP BY 部门号） as DEPMAX
WHERE 月工资 ＝ 最高工资 AND 职工.部门号 ＝ DEPMAX.部门号;




8：有一个“职工”表，表结构如下：(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就可以促使 查询优化器 使用索引


8：设有关系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;




6：设职工---社团数据库有三个基本表：(20分)
职工（职工号，姓名，年龄，性别）；
社会团体（编号，名称，负责人，活动地点）；
参加（职工号，编号，参加日期）；
其中：
1）职工表的主键为职工号。
2）社会团体表的主键为编号；  外码为负责人，被参照表为职工表，对应属性为职工号。
3）参加表的职工号和编号为主键；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。
试用SQL语句表达下列操作：
1）定义职工表、社会团体表和参加表，并说明其主键和参照关系。
2）建立下列两个视图。
社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；
参加人情况（职工号，姓名，社团编号，社团名称，参加日期）
3）查找参加唱歌队或篮球队的职工号和姓名。
4）求参加人数超过100人的社会团体的名称和负责人。
答：
1）  CREAT　TABLE　职工（职工号　CHAR（10）NOT　NULL　，
　　　　　　　　　　　　　姓名　CHAR（8）NOT　NULL，
　　　　　　　　　　　　　年龄　SMALLINT，
　　　　　　　　　　　　　性别　CHAR（2），
　　　　　CONSTRAINT　C1_PK　PRIMARY　KEY（职工号）；　

CREAT　TABLE　社会团体（编号　CHAR（8）NOT　NULL　，　
名称　CHAR（12）NOT　NULL，
负责人　CHAR（8），
活动地点　VARCHAR（50），
CONSTRAINT　C2_PK  PRIMARY　KEY（职工号）
CONSTRAINT　C2　FOREIGN　KEY（负责人）REFERENCES 职工（职工号））；

CREAT　TABLE　参加（职工号　CHAR（8），
编号　CHAR（8），
参加日期　DATA，
CONSTRAINT　C3_PK　PRIMARY　KEY（职工号，编号），
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　参加.编号　HAVING　COUNT（参加.编号）＞100；



8：设工程—零件数据库中有四个基本表：（18分）
供应商（供应商代码，姓名，所在城市，联系电话）；
工程（工程代码，工程名，负责人，预算）；
零件（零件代码，零件名，规格，产地，颜色）；
供应零件（供应商代码，工程代码，零件代码，数量）
试用SQL语句完成下列操作：
1） 查找预算在50000~100000元之间的工程的信息，并将结果按预算降序排列。

2 ) 找出使用上海产的零件的工程名称。
3)  将由供应商S5供给工程代码为J4的零件P6改为由S3供应，并将零件P6的产地改为S3供货商的所在城市。
4）从供应商关系中删除S2的记录，并从供应零件关系中删除相应的记录。
1.SELECT*
FROM 工程
WHERE　预算　BETWEEN　50000　AND　100000  (或者 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';