sql笔试题有两个表第一个TableStoreStore-id（PK）CitySpacewh1NewYork370wh2Beijing500wh3NewYork200wh4London400……这是第一个表TableEmployee（EmployeeInStore）Store-id（FK）Emp-ID（PK）SalaryWH1e1

Question

sql笔试题
有两个表
第一个
Table Store
Store-id（PK） City Space
wh1 New York 370
wh2 Beijing 500
wh3 New York 200
wh4 London 400
……
这是第一个表
Table Employee（Employee In Store） Store-id（FK） Emp-ID（PK） Salary WH1 e1 1220 wh1 e3 1210 wh2 e4 1250 WH2 e6 1230 …… 第二个表
1Get the average salary of each store
2Get the average salary of each city
3find cities in which there exists salary less than 1000
4Get the average salary of cities there exists salary less than 1000(can create temporary tables)
5Get the employees who are paid the max salary for each city.( can create temporary tables)
6Get the total salary the total space ,the number of the store ,the number of the employee and maximum salary of every city.Show the results as following layout.( can create temporary table)

Answer 1

两个表第一个store（store_id,city,space)
第二个employee（store_id,emp_id,salary）
1、每家店的平均工资
\x09select t1.store_id,AVG(salary) from store t1 inner join employee t2 on t1.store_id=t2.store_di
group by t1.store_id
2、每个城市的平均工资
select t1.city,AVG(salary) from store t1 inner join employee t2 on t1.store_id=t2.store_di
group by t1.city
3、查找该城市存在员工工资低于1000的城市信息
select distinct t1.city
from store t1 inner join employee t2 on t1.store_id=t2.store_id
where t2.salary