新浪博客

实验四  查询（多表查询，嵌套查询，分组查询）

2012-06-16 15:53阅读：

http://blog.sina.cn/dpool/blog/u/2700859174

1. 目的要求：
实现单表和多表的普通查询和嵌套查询。包括返回单值的子查询和返回多值的子查询。使用5个聚合函数以及GROUP BY子句和HAVING子句实现分组查询.
2. 实验内容
有如下关系模式，分析每个关系模式的主码，外码，完成后面的查询
职员表：Emp(eid:integer;ename:string,salary:real)
部门表：Dept(did:integer,dname:string,managerid:integer,floornum:integer)
职员与部分的关系表：Works(eid:integer,did:integer)；
Works表表示：一个职员可以在多个部门工作，一个部门有多个职员
Dept表中managerid可以取值null，表示尚未任命部门经理，
FONT>可以取值null，表示尚未分配工作地点

用单表查询完成如下操作：
1) 输出所有员工的姓名和工资
2) 输出薪水少于10 000或者大于100 000的雇员的名字
3) 输出所有姓“欧阳”，且全名为四个字的雇员的姓名和工资
4) 输出薪水在20 000和50 000之间的雇员的名字
5) 输出部门名字中含有“_”的所有部门的名字和楼层号
6) 查询公司的员工数
7) 查询所有还没有部门经理的部门的名字和编号
8) 查询所有已分配楼层的部门的所有信息
用连接查询完成如下操作：
1) 查询“电视”部门的职工人数
2) 输出每个部门的名字和平均工资
3) 查询每个部门的部门编号，及其拥有的雇员的人数
4) 查询在第10层工作，同时薪水少于￥50000的所有雇员的名字
5) 输出同时管理三个或者更多部门的管理者的名字
6) 输出管理在同一层上10个以上部门的所有管理者的名字
7) 输出雇员“刘丽”工作的部门的名字
用嵌套查询完成如下操作：
1) 查询工资最高的雇员的名字
2) 查询工资最低的雇员的名字及其所在部门的编号和名字
3) 输出与Santa工作部门相同的所有雇员的所有信息
4) 找出薪水在20 000以上，并且在电视部门或者玩具部门工作的雇员的名字
5) 输出与刘丽在同一层工作的雇员的名字
6) 输出比所在部门的经理挣的还要多的雇员的名字
7) 输出满足如下条件的各个部门的名字：经理的姓为张，同时他的薪水既不是本部门最高也不是最低
8) 输出比“玩具”部门所有职工工资都高的雇员的姓名
9) 输出比“电视”部门职工平均工资高的雇员的姓名
10) 找出所有有职工的部门的名字和楼层号
11) 查询所有没有职工的部门编号和名字
12) 输出同时在玩具部门和糖果部门工作的雇员的名字和薪水
3. 主要仪器设备及软件：
（1）PC
（2）Microsoft SQL Server 2005

create table Emp
(
Eid char(10) primary key,
Ename char(6),
salary float(10)
)
create table Dept
(
Did char(10) primary key,
Dname char(20),
managerid char(10),
floornum int
)
create table Works
(
Eid char(10),
Did char(10),
primary key (Eid,Did),
foreign key (Eid) references Emp (Eid),
foreign key (Did) references Dept (Did)

)
1)输出所有员工的姓名和工资

select Ename,salary
from Emp;

2)输出薪水少于000或者大于000的雇员的名字

select Ename
from Emp
where salary<10000 or salary >100000;

3)输出所有姓“欧阳”，且全名为四个字的雇员的姓名和工资

select Ename,salary
from Emp
where Ename like '欧阳_ _';

4)输出薪水在000和000之间的雇员的名字

select Ename
from Emp
where salary between 20000 and 50000;

5)输出部门名字中含有“_”的所有部门的名字和楼层号

select Dname,floornum
6) 查询公司的员工数
select count(*) as '员工数'
from Emp;
7) 查询所有还没有部门经理的部门的名字和编号
select Dname,Did
from Dept
where managerid is null;
8) 查询所有已分配楼层的部门的所有信息
select *
from Dept
where floornum is not null;

用连接查询完成如下操作：

1) 查询“电视”部门的职工人数
select count(*) as '人数'
from Dept,Works
where Dname='电视' and Dept.Did=Works.Did;

2) 输出每个部门的名字和平均工资
select Dname,avg(salary) as '平均工资'
from Emp,Dept,Works
where Emp.Eid=Works.Eid and Dept.Did=Works.Did
group by Dname;

3) 查询每个部门的部门编号，及其拥有的雇员的人数
select Dept.Did,count(Eid) as '人数'
from Dept,Works
where Dept.Did=Works.Did
group by Dept.Did;

4) 查询在第层工作，同时薪水少于￥的所有雇员的名字
select DISTINCT Ename
from Emp,Dept,Works
where floornum=10 and salary<50000 and Emp.Eid=Works.Eid and Dept.Did=Works.Did;

5) 输出同时管理三个或者更多部门的管理者的名字
select Ename
from Emp,Dept
where Dept.managerid=Emp.Eid
group by Ename
having count(did)>=3

6) 输出管理在同一层上个以上部门的所有管理者的名字
select Ename
from Emp,Dept
where Dept.managerid=Emp.Eid
group by Ename,floornum
having count(did)>2

7) 输出雇员“刘丽”工作的部门的名字
select dname
from emp,dept,works
where ename='刘丽' and Emp.Eid=Works.Eid and Dept.Did=Works.Did;

嵌套查询

1)查询工资最高的雇员的名字
select Ename
from Emp
Where salary=(select max(salary)from Emp);

2) 查询工资最低的雇员的名字及其所在部门的编号和名字
select Ename,Dept.Did,Dname
from Emp,Dept,Works
where Emp.Eid=Works.Eid and Dept.Did=Works.Did and salary=(select min(salary)from emp);

3) 输出与Santa工作部门相同的所有雇员的所有信息
select Emp.Eid,Ename,Dept.Did,Dname
from emp,dept,works
where Emp.Eid=Works.Eid and Dept.Did=Works.Did and
Works.Did in (select did from works where eid in(select eid from emp where ename='Santa'));

4) 找出薪水在000以上，并且在电视部门或者玩具部门工作的雇员的名字
select ename
from Emp,Dept,Works
where Emp.Eid=Works.Eid and Dept.Did=Works.Did and salary>20000 and dname in (select dname from dept where dept.dname='电视' or dept.dname='玩具');

5) 输出与刘丽在同一层工作的雇员的名字
select ename
from Emp,Dept,Works
where Emp.Eid=Works.Eid and Dept.Did=Works.Did and floornum in(select floornum from dept where did in(select did from works where eid in(select eid from emp where ename='刘丽')));

6) 输出比所在部门的经理挣的还要多的雇员的名字
select Ename
from emp,dept
where salary >all(select salary from emp join dept on emp.eid=dept.managerid where did in(select did from works where works.eid=emp.eid))

7) 输出满足如下条件的各个部门的名字：经理的姓为张，同时他的薪水既不是本部门最高也不是最低
select Dname
from emp,dept
where eid=managerid and ename like '张%' and salary<(select max(salary) from Emp,works where works.eid=emp.eid and dept.did=works.did) and salary> (select min(salary) from Emp, works where works.eid=emp.eid and dept.did=works.did)

8) 输出比“玩具”部门所有职工工资都高的雇员的姓名
select ename
from emp,dept,works
where emp.eid=works.eid and dept.did=works.did and salary>any (select salary from emp where eid in(select eid from works where did in (select did from dept where dname='玩具')));

9) 输出比“电视”部门职工平均工资高的雇员的姓名
select ename
from emp,dept,works
where emp.eid=works.eid and dept.did=works.did and salary>any (select avg(salary) from emp where eid in(select eid from works where did in (select did from dept where dname='电视')));

10) 找出所有有职工的部门的名字和楼层号
select dname,floornum
from dept
where did in(select did from works);

11) 查询所有没有职工的部门编号和名字
select dname,did
from dept
where did not in(select did from works);

12) 输出同时在玩具部门和糖果部门工作的雇员的名字和薪水
select ename ,salary
from emp
where emp.eid in(select eid from works where did in(select did from dept where dname='玩具')) intersect select ename ,salary from emp where emp.eid in( select eid from works where did in(select did from dept where dname='糖果'))

我的更多文章

下载客户端阅读体验更佳

APP专享