Small Bank corporation有如下的員工數據庫，為下面每個查詢語句寫出SQL表達式:
Employee 員工表
員工姓名
employee_name
所住街道
street
所在城市
city
Company 公司信息表
公司名稱
company_name
所在城市
city
Works 工作信息表
員工姓名
employee_name
公司名稱
company_name
薪水
salary
找出所有為Small Bank corporation工作且薪金超過10000的員工的名字、居住的街道和城市
查找數據庫中所有居住城市和公司所在城市相同的員工
找出數據庫中所有工資高于Small Bank corporation的每一個員工工資的員工姓名
假設一家公司可以在好幾個城市有分部，找出所有這樣的公司，其所在城市包含了Small Bank corporation所在的每一個城市
找出平均工資高于Small Bank corporation平均工資的所有公司名字及平均工資
DROP DATABASE exam;
CREATE DATABASE exam;
use exam;CREATE table Employee(employee_name varchar(20),street varchar(50),city varchar(10)
);CREATE table Company(company_name varchar(100),city varchar(10)
);CREATE table Works(employee_name varchar(20),company_name varchar(100),salary int(10)
);INSERT INTO Employee values('張三','金湖街道','大冶');
INSERT INTO Employee values('李四','東岳街道','大冶');
INSERT INTO Employee values('王五','羅橋街道','大冶');
INSERT INTO Employee values('趙六','紅衛路街道','武漢');
INSERT INTO Employee values('孫七','獅子山街道','武漢');
INSERT INTO Employee values('蔡八','恩施街道','武漢');
INSERT INTO Employee values('曹九','金湖街道','大冶');Insert Into Company values('Small Bank corporation','大冶');
Insert Into Company values('Small Bank corporation','武漢');
Insert Into Company values('美的','武漢');
Insert Into Company values('美的','佛山');
Insert Into Company values('勁牌','大冶');
Insert Into Company values('勁牌','武漢');INSERT INTO Works values('張三','Small Bank corporation',12000);
INSERT INTO Works values('李四','Small Bank corporation',9000);
INSERT INTO Works values('王五','Small Bank corporation',11000);
INSERT INTO Works values('趙六','美的',13000);
INSERT INTO Works values('孫七','美的',10000);
INSERT INTO Works values('蔡八','美的',14000);
INSERT INTO Works values('曹九','美的',14000);
INSERT INTO Works values('王二狗','勁牌',11000);
INSERT INTO Works values('趙三蛋','勁牌',7000);-- 1) 找出所有為Small Bank corporation工作且薪金超過10000的員工的名字、居住的街道和城市
Select Employee.employee_name,Employee.street,Employee.city from Employee,
(Select employee_name from Works where salary>10000 and company_name = 'Small Bank corporation') as info where Employee.employee_name = info.employee_name;-- 2) 查找數據庫中所有居住城市和公司所在城市相同的員工
select Employee.employee_name from Employee,Company,Works where Employee.employee_name = Works.employee_name andWorks.company_name = Company.company_name and Company.city = Employee.city;-- 3) 找出數據庫中所有工資高于Small Bank corporation的每一個員工工資的員工姓名
select employee_name from Works where salary>(Select max(salary) from Works where company_name = 'Small Bank corporation')and company_name != 'Small Bank corporation';-- 4) 假設一家公司可以在好幾個城市有分部，找出所有這樣的公司，其所在城市包含了Small Bank corporation所在的每一個城市
-- 此類問題有兩個條件，城市要在Small Bank corporation所在城市集合內，且城市數量和Small Bank corporation所在城市數量相等
select company_name from company where city in (select city from company where company_name ='Small Bank Corporation') and company_name != 'Small Bank Corporation'
group by company_name
having count(*) = (select count(city) from company where company_name ='Small Bank Corporation');-- 5) 找出平均工資高于Small Bank corporation平均工資的所有公司名字及平均工資
select company_name,avg(salary) as avg_salary from Works
where company_name != 'Small Bank corporation' group by company_name
having avg(salary)>(select avg(salary) from Works where company_name = 'Small Bank corporation');
