SQL单表查询、多表查询、嵌套查询

create table Emp
(eid int primary key,
 ename char(9),
 salary real
);


create table Dept
(did int primary key,
 dname char(9),
 managerid int,
 foloornum int
);


create table Works
(eid int,
 did int,
 foreign key(eid) references Emp(eid),
 foreign key(did) references Dept(did)
);
alter table Emp nocheck constraint PK__Emp__7C8480AE;
insert into Emp values (001,'Lily',10000);
insert into Emp values(004,'Liyong',15000);
insert into Emp values (002,'Ouyang',2000);
insert into Emp values (003,'Ningxuan',112000);
insert into Emp values (005,'Santa',21000);


insert into Dept values (111,'CS','101',2);
insert into Dept values (112,'TV','102',3);
insert into Dept values (113,'BUG','103',4);
insert into Dept values (114,'PLAY',null,10);


insert into Works values (001,111);
insert into Works values (001,112);
insert into Works values (002,112);
insert into Works values (003,113);
insert into Works values (004,111);
insert into Works values (005,114);
用单表查询完成如下操作：
1)	输出所有员工的姓名和工资
select ename,salary from Emp;
2)	输出薪水少于10 000或者大于100 000的雇员的名字
select ename from Emp where salary<10000 or salary>100000;
3)	输出所有姓“欧阳”，且全名为四个字的雇员的姓名和工资
select ename,salary from Emp where ename like '欧阳__';
4)	输出薪水在20 000和50 000之间的雇员的名字
select ename from Emp where salary>20000 and salary<50000;
5)	输出部门名字中含有“_”的所有部门的名字和楼层号
select dname,foloornum from Dept 
where dname like '%\_%'escape'\';
6)	查询公司的员工数
select count(*) from Emp;
7)	查询所有还没有部门经理的部门的名字和编号
select dname,did from Dept where managerid is null;
8)	查询所有已分配楼层的部门的所有信息
select * from Dept where floornum is null;
用连接查询完成如下操作：
1)	查询“电视”部门的职工人数
select count(*) from Dept,Works
where Works.did=Dept.did and dname='电视部';
2)	输出每个部门的名字和平均工资
select dname,avg(salary) from Dept,Emp,Works
where Dept.did=Works.did and Emp.eid=Works.eid
group by dname;
3)	查询每个部门的部门编号，及其拥有的雇员的人数
select  Dept.did,count(*) from Dept,Works
where Dept.did=Works.did
group by Dept.did;
4)	查询在第10层工作，同时薪水少于￥50000的所有雇员的名字
select ename from Emp,Dept,Works
where Works.did=Dept.did and Works.eid=Emp.eid and foloornum=10 and salary<50000;


7)	输出雇员“刘丽”工作的部门的名字
select dname from Emp,Dept,Works
where Works.did=Dept.did and Works.eid=Emp.eid and ename='刘丽';
用嵌套查询完成如下操作：
1)	查询工资最高的雇员的名字
select ename from Emp
where salary in (select max(salary) from Emp)
2)	查询工资最低的雇员的名字及其所在部门的编号和名字
select ename,Dept.did,dname from Emp,Dept,Works
where Works.did=Dept.did and Works.eid=Emp.eid and salary in(select min(salary) from Emp);
3)	输出与Santa工作部门相同的所有雇员的所有信息
select Emp.eid,ename,salary from Emp,Dept,Works
where Works.did=Dept.did and Works.eid=Emp.eid and Dept.did in(select Dept.did from Dept,Works,Emp
 where Works.did=Dept.did and Works.eid=Emp.eid and ename='Santa');
4)	找出薪水在20 000以上，并且在电视部门或者玩具部门工作的雇员的名字
select ename from Emp
where salary>20000 and eid in (select eid from Dept,Works
                               where Works.did=Dept.did and Works.eid=Emp.eid    and (dname='电视部' or dname='玩具部'));
5)	输出与刘丽在同一层工作的雇员的名字
select ename
from Emp,Works,Dept
where Emp.eid=Works.eid AND Works.did=Dept.did 
AND Dept.floornum in (
                   select Dept.floornum
                   from Emp,Works,Dept
                   where Emp.eid=Works.eid 
                      AND Works.did=Dept.did  AND Emp.ename='刘丽')
6)	输出比所在部门的经理挣的还要多的雇员的名字
select ename
from Emp,Works,Dept D
where Emp.eid=Works.eid AND Works.did=D.did 
     AND salary > (
                      select salary
                      from Emp,Dept
                      where Emp.eid=Dept.managerid
                      AND Dept.did=D.did
                       )
7)	输出满足如下条件的各个部门的名字：经理的姓为张，同时他的薪水既不是本部门最高也不是最低
select dname
from Dept D,Emp
where D.managerid=Emp.eid AND ename LIKE 'zhang' 
 AND salary >(
                 select MIN(salary)
                 from Emp,Dept,Works 
                 where Dept.did=Works.did AND Works.eid=emp.eid
                 AND Dept.did=D.did
                  )
AND salary <(
                 select MAX(salary)
                 from Emp,Dept,Works 
                 where Dept.did=Works.did AND Works.eid=emp.eid
                 AND Dept.did=D.did
                  )
8)	输出比“玩具”部门所有职工工资都高的雇员的姓名
select ename from Emp
where salary>(select sum(salary) from Emp,Dept,Works
              where Works.did=Dept.did and Works.eid=Emp.eid and dname='PLAY');
9)	输出比“电视”部门职工平均工资高的雇员的姓名
select ename from Emp
where salary>(select avg(salary) from Emp,Dept,Works
              where Works.did=Dept.did and Works.eid=Emp.eid and dname='电视部');
10)	找出所有有职工的部门的名字和楼层号
select DISTINCT dname,floornum
from Emp,Dept,Works 
where Works.eid=emp.eid AND Dept.did=Works.did
11)	查询所有没有职工的部门编号和名字
select DISTINCT did,dname
from Dept
where dname NOT IN (select DISTINCT dname
			from Emp,Dept,Works 
			where Works.eid=emp.eid AND Dept.did=Works.did)
12）输出同时在玩具部门和糖果部门工作的雇员的名字和薪水
select ename,salary from Emp
where Emp.eid in (select Emp.eid from Dept,Works,Emp
              where Dept.did=Works.did and Works.eid=Emp.eid and dname='糖果部' and dname='玩具部');