create table house (house_id varchar(10),city varchar(10),area  int)insert into house values ('wh1','北京',370);insert into house values ('wh2','上海',500);insert into house values ('wh3','广州',200);insert into house values ('wh4','武汉',400);create table employee (house_id varchar(10),employee_id varchar(10),salary int)insert into employee values ('wh2','e1',1220);insert into employee values('wh1','e3',1210);insert into employee values ('wh2','e4',1250);insert into employee values ('wh3','e6',1230);insert into employee values ('wh1','e7',1250);create table purchase (employee_id varchar(10),provider_id varchar(10),purchase_id varchar(10),purchase_date datetime)insert into purchase values ('e3','s7','or67','2001-6-23');insert into purchase values ('e1','s4','or73','2001-7-28');insert into purchase values ('e7','s4','or76','2001-5-25') ;insert into purchase values ('e6',null,'or77',null);insert into purchase values ('e3','s4','or79','2001-6-13');insert into purchase values ('e1',null,'or80',null);insert into purchase values ('e3',null,'or90',null);insert into purchase values ('e3','s3','or91','2001-7-13');create table provider(provider_id varchar(10),provider_name varchar(max),[address] varchar(10))insert into provider values ('s3','振华电子厂','西安');insert into provider values ('s4','华通电子公司','北京');insert into provider values ('s6','607厂','郑州');insert into provider values ('s7','爱华电子厂','北京');truncate table provider--1.从职工关系中检索所有工资值。select salary from employee;--2.检索仓库关系中的所有记录select * from house ;--3.检索工资多于1230元的职工号select employee_id from employee where salary>1230;--4.检索哪些仓库有工资多于1210元的职工。select distinct house_id from employee where salary>1210;--5.给出在仓库“wh1”或“wh2”工作，并且工资少于1250元的职工号。select employee_id from employee where house_id in('wh1','wh2') and salary<1250;--6.找出工资多于1230元的职工号和他们所在的城市。select employee_id,city from employee e ,house h where e.house_id  = h.house_id and salary>1230;--7.找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市。select employee_id,city from  employee e join house h on e.house_id=h.house_id  and area>400;  --8 .哪些城市至少有一个仓库的职工工资为1250元。select city from house  where house_id in (select house_id from employee where salary=1250);--9.查询所有职工的工资都多于1210元的仓库的信息。select * from house where house_id in (select house_id from employee where salary>1210);select * from house where house_id in (select house_id from employee e1 where 1210 < all(select salary from employee e2 where e1.house_id=e2.house_id  ))--10.找出和职工e4挣同样工资的所有职工。select * from employee where salary = (select salary from employee where employee_id = 'e4')and employee_id != 'e4';--11.检索出工资在1220元到1240元范围内的职工信息。select * from employee where salary between 1220 and 1240;--12.从供应商关系中检索出全部公司的信息，不要工厂或其他供应商的信息。select * from provider --13.找出不在北京的全部供应商信息。select * from provider where address !='北京'--14.按职工的工资值升序检索出全部职工信息。select * from employee order by salary ;--15.先按仓库号排序，再按工资排序并输出全部职工信息。select * from employee order by house_id ,salary ;--16.找出供应商所在地的数目。select COUNT(*),address from provider group by address;--17.求支付的工资总数select SUM(salary) from employee;--18.求北京和上海的仓库职工的工资总和 select SUM(salary) from employee where house_id in (select house_id from house where city in ('北京','上海'))--19.求所有职工的工资都多于1210元的仓库的平均面积select AVG(area) from house where house_id in( select house_id from employee e1 where 1210 < all(select salary from employee e2 where e1.house_id=e2.house_id ));--20.求在wh2仓库工作的职工的最高工资值select MAX(salary ) from employee where house_id='wh2';--21.求每个仓库的职工的平均工资select AVG(salary ),house_id   from employee group by house_id  --22.求至少有两个职工的每个仓库的平均工资。select AVG(salary),house_id from employee group by house_id having COUNT(house_id)>1--23.找出尚未确定供应商的订购单select purchase_id from purchase where provider_id is null--24.列出已经确定了供应商的订购单信息select * from purchase where provider_id is not null;--25.查询供应商名select provider_name from provider --26.在订购单表中加入一个新字段总金额，说明完成该订购单所应付出的总金额数。alter table purchase add sum_money varchar(max);--27.列出每个职工经手的具有最高总金额的订购单信息。select * from purchase where sum_money in (select MAX(sum_money) from purchase  group by employee_id)--28.检索哪些仓库中还没有职工的仓库的信息select * from house where house_id not in (select house_id from employee )--29.检索哪些仓库中至少已经有一个职工的仓库的信息select* from house where house_id in (select house_id from employee )--30.检索有职工的工资大于或等于wh1仓库中任何一名职工工资的仓库号select *, house_id from employee where salary >=any (select salary from employee where house_id='wh1') and house_id!='wh1'--31.检索有职工的工资大于或等于wh1仓库中所有职工工资的仓库号。select *,house_id from employee where salary >=all (select salary from employee where house_id='wh1') and house_id!='wh1'