数据库题目3

题目描述：
Small Bank corporation有如下的员工数据库，为下面每个查询语句写出SQL表达式:

问题：
1) 找出所有为Small Bank corporation工作且薪金超过10000的员工的名字、居住的街道和城市
SELECT employee_name, street, city
FROM Employee e, Works w
WHERE e.employee_name = w.employee_name
AND company_name = ‘Small Bank corporation’ AND salary > 10000;

2) 查找数据库中所有居住城市和公司所在城市相同的员工
SELECT employee_name
FROM Employee e, Company c
WHERE e.city = c.city;

3) 找出数据库中所有工资高于Small Bank corporation的每一个员工工资的员工姓名
SELECT employee_name
FROM Works
WHERE salary >
(SELECT MAX(salary)
FROM Works
WHERE
company_name = ‘Small Bank Corporation’
GROUP BY company_name);

4) 假设一家公司可以在好几个城市有分部，找出所有这样的公司，其所在城市包含了Small Bank corporation所在的每一个城市
SELECT company_name
FROM (SELECT company_name
FROM Company
WHERE city
IN (SELECT city
FROM company
WHERE company_name = ‘Small Bank Corporation’))
AS sheet
WHERE company_name != ‘Small Bank Corporation’;

注意：这里如果只是
SELECT company_name
FROM Company
WHERE city
IN (SELECT city
FROM company
WHERE company_name = ‘Small Bank Corporation’))
则Small Bank Corporation也会显示，为了去除Small Bank Corporation的company_name，只要加上条件company_name != 'Small Bank Corporation即可。
但是如果这里没有sheet来作为别名的话，则会报一个“Every derived table must have its own alias”的错误，所以一定要将这个select语句设置一个别名。

5) 找出平均工资高于Small Bank corporation平均工资的所有公司名字及平均工资
SELECT company_name, AVG(salary)
FROM Works
GROUP BY company_name
HAVING (AVG(salary) >
(SELECT AVG(salary)
FROM works
WHERE company_name = ‘Small Bank Corporation’));