[大数据]恒生笔试题B卷 数据库

题目描述：

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

Employee 员工表

Company 公司信息表

Works 工作信息表

1. 找出所有为Small Bank corporation工作且薪金超过10000的员工的名字、居住的街道和城市
2. 查找数据库中所有居住城市和公司所在城市相同的员工
3. 找出数据库中所有工资高于Small Bank corporation的每一个员工工资的员工姓名
4. 假设一家公司可以在好几个城市有分部，找出所有这样的公司，其所在城市包含了Small Bank corporation所在的每一个城市
5. 找出平均工资高于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 and
                                                                Works.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');