SQL复习题带答案

以下查询均使用COMPANY数据库。

1．单表查询
(1)基本查询。

Q0. 使用查询分析器从Employee表中检索出所有员工的姓名。

SELECT FNAME,MINIT,LNAME FROM EMPLOYEE
Q1.使用查询分析器从Employee表中检索出员工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分别加上“名”、“姓”、“社会保险号”、“生日”、“工资”的标题。

员工的排序规则为：首先按工资的降序排列，然后按FNAME
的字母升序排列。

SELECT FNAME AS 名,LNAME AS 姓,SSN AS 社会保险号,BDATE AS 生
日,SALARY AS 工资 FROM EMPLOYEE
ORDER BY SALARY DESC,FNAME ASC
Q2. Retrieve all distinct salary values.
SELECT DISTINCT SALARY FROM EMPLOYEE
查询不重复的员工工资值。

Q3.Retrieve the names of all employees who do not have supervisors.
查询没有直接上司的员工姓名。

SELECT FNAME,LNAME FROM EMPLOYEE
WHERE SUPERSSN IS NULL
(2) 基于WHERE子句进行数据查询。

1）基于比较条件。

Q4. 从Employee表中查询出工资大于等于40000的员工资料。

SELECT * FROM EMPLOYEE WHERE SALARY>=40000
2）基于BETWEEN子句的查询。

Q5. 从Employee表中查询出1960年——1970年之间出生的员工资料。

SELECT * FROM EMPLOYEE WHERE BDATE BETWEEN ‘1960-1-1’ AND
‘1969-12-31’
SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 30000 AND 40000
SELECT * FROM PRODUCTS WHERE PNAME BETWEEN ‘APPLE’AND ‘PHONE’
3）基于IN子句的查询。

Q6.从Employee表中查询出部门号为4或者5的员工资料。

SELECT * FROM EMPLOYEE WHERE DNO=4 OR DNO=5
SELECT * FROM EMPLOYEE WHERE DNO IN (4,5)
4）基于LIKE子句的查询。

Q7.从Employee表中查询出LNAME中含有字母o的员工资料。

_ % [ACDF] [^A-F]
SELECT * FROM EMPLOYEE WHERE LNAME LIKE ‘%O%’
LNAME以O开头——‘O%’
LNAME以O结尾——‘%O’
LNAME长度为4个字符，且最后一个字母为O——‘_ _ _O’
查询LNAME符合邮编格式的员工——‘[0-9] [0-9] [0-9] [0-9] [0-9] [0-9]’
(3) 使用计算列查询。

Q8.从Employee表中检索出员工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需换算成人民币，汇率假定为1美元=8人民币元)，并分别加上“名”、“姓”、“社会保险号”、“人民币工资”的标题。

SELECT FNAME AS 名,LNAME AS 姓,SSN AS 社会保险号,SALARY*8 AS人民币工资FROM EMPLOYEE
2．多表连接查询(使用JOIN)
Q9. Retrieve the name and address of all employees who work for the ‘Research’ department.
查询所有为Research部门工作的员工姓名及地址。

SELECT FNAME,LNAME,ADDRESS FROM EMPLOYEE
JOIN DEPARTMENT ON
EMPLOYEE.DNO=DEPARTMENT.DNUMBER
WHERE DNAME=’RESEARCH’
查询RESEARCH部门控制的项目名称、编号。

SELECT PNAME,PNUMBER FROM PROJECT JOIN DEPARTMENT
ON PROJECT.DNUM=DEPARTMENT.DNUMBER
WHERE DNAME=’RESEARCH’
Q10. For each project, list the project name and the total hours per week (by all employees) spent on that project.
对于每个项目，列出项目名称以及所有员工在此项目上工作的总时间。

SELECT PNAME,SUM(HOURS) FROM WORKS_ON JOIN PROJECT
ON WORKS_ON.PNO=PROJECT.PNUMBER
GROUP BY PNAME
对于每个员工，列出员工的姓名以及他所参与的所有项目的总时间。

SELECT FNAME,LNAME,SUM(HOURS)
FROM WORKS_ON JOIN EMPLOYEE
ON WORKS_ON.ESSN=EMPLOYEE.SSN
GROUP BY ESSN,FNAME,LNAME
Q11.For each department, retrieve the department name and the average salary of all employees working in that department.
对于每个部门，列出部门名称以及此部门员工的平均工资。

SELECT DNAME,AVG(SALARY) FROM EMPLOYEE JOIN DEPARTMENT ON
EMPLOYEE.DNO=DEPARTMENT.DNUMBER
GROUP DNAME
汇总函数：COUNT,SUM,AVG,MAX,MIN
3．嵌套查询(子查询)
Q12.Retrieve the names of all employees who do not work on any project controlled by department number 5.
查询没有参与任何部门5控制项目的员工姓名。

SELECT FNAME,LNAME FROM EMPLOYEE WHERE SSN NOT IN
(SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO
IN (SELECT PNUMBER FROM PROJECT WHERE DNUM=5))
查询没有参与任何JOHN SMITH所参与项目的员工姓名
JOHN SMITH所参与项目的查询方法：
SELECT PNO FROM WORKS_ON WHERE ESSN=(SELECT SSN FROM EMPLOYEE WHERE FNAME=’JOHN’ AND LNAME=’SMITH’)
Q13.Retrieve the names of employees whose salary is greater than the salary of all the employees in department 5.
查询工资超过部门5所有员工工资的员工姓名。

SELECT FNAME,LNAME FROM EMPLOYEE
WHERE SALARY>(SELECT MAX(SALARY) FROM EMPLOYEE WHERE DNO=5)
SELECT FNAME,LNAME FROM EMPLOYEE
WHERE SALARY>ALL(SELECT SALARY FROM EMPLOYEE WHERE DNO=5)。