表結(jié)構(gòu)
--部門(mén)表
create table dept
(
deptno int(3) primary key,
dname varchar(14),
loc varchar(13)
);
--雇員表
create table emp
(
empno int(4) not null primary key,
ename varchar(10),
job varchar(10),
mgr int(4),
hiredate datetime,
sal double,
comm double,
deptno int(3),
foreign key(deptno) references dept(deptno)
);
--工資級(jí)別表
create table salgrade
(
grade int(3) primary key,
losal int(3),
hisal int(3)
);
2.插入數(shù)據(jù)（進(jìn)行初始化）
use company;
--往部門(mén)表中查數(shù)據(jù)
insert into dept values(10,'Accounting','New York');
insert into dept values(20,'Research','Dallas');
insert into dept values(30,'Sales','Chicago');
insert into dept values(40,'Operations','Boston');
insert into dept values(50,'Admin','Washing');
--往雇員表中插數(shù)據(jù)
insert into emp values(7369,'Smith','Clerk',7902,'1980-12-17',800,0,20);
insert into emp values(7499,'Allen','Salesman',7698,'1981-2-20',1600,300,30);
insert into emp values(7844,'Turner','Salesman',7499,'1981-9-8',1500,0,30);
insert into emp values(7698,'Tom','Manager',0,'1981-9-8',6100,600,40);
insert into emp values(7876,'Adams','Clerk',7900,'1987-5-23',1100,0,20);
insert into emp values(7900,'James','Clerk',7698,'1981-12-3',2400,0,30);
insert into emp values(7902,'Ford','Analyst',7698,'1981-12-3',3000,null,20);
insert into emp values(7901,'Kik','Clerk',7900,'1981-12-3',1900,0,30);
--往工資級(jí)別表中插數(shù)據(jù)
insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,5000);
insert into salgrade values(6,5001,10000);
1.查詢雇員表中工資最高的雇員的員工號(hào)穴张、員工姓名、工資和部門(mén)號(hào)缝呕。
SELECT
empno,
ename,
sal,
deptno
FROM emp
WHERE sal = (SELECT max(sal) FROM emp);
2.薪水大于1200的雇員澳窑，按照部門(mén)編號(hào)進(jìn)行分組，分組后的平均薪水必須大于1500供常，查詢各分組的平均工資摊聋，按照工資的倒序進(jìn)行排列。
SELECT
avg(sal) avg_sal,
deptno
FROM
emp
WHERE
sal > 1200
GROUP BY
deptno
HAVING
avg_sal > 1500
ORDER BY
avg_sal DESC;
3.查詢每個(gè)雇員和其所在的部門(mén)名
SELECT
ename,dname
FROM
emp,dept
WHERE(emp.deptno = dept.deptno);
-- 或者（推薦）(on中就寫(xiě)連接條件栈暇，where中就寫(xiě)過(guò)濾條件麻裁，各司其職)
SELECT
ename,
dname
FROM
emp
JOIN dept ON (emp.deptno = dept.deptno);
4.查詢每個(gè)雇員姓名及其工資所在的等級(jí)
SELECT
ename,
grade
FROM
emp e
JOIN salgrade s ON (
e.sal BETWEEN s.losal
AND s.hisal
);
5.查詢雇員名第2個(gè)字母不是a的雇員的姓名、所在的組名、工資所在的等級(jí)悲立。
SELECT
ename,
dname,
grade
FROM
emp e
JOIN dept d ON (e.deptno = d.deptno)
JOIN salgrade s ON (
e.sal BETWEEN s.losal
AND s.hisal
)
WHERE
ename NOT LIKE '_a%';
6.查詢每個(gè)雇員和其經(jīng)理的姓名
SELECT
e1.ename,
e2.ename
FROM
emp e1,
emp e2
WHERE
(e1.mgr = e2.empno);
SELECT
e1.ename,
e2.ename
FROM
emp e1
JOIN emp e2 ON (e1.mgr = e2.empno);
7.查詢每個(gè)雇員和其經(jīng)理的姓名（包括公司老板本身（他上面沒(méi)有經(jīng)理））
SELECT
e1.ename,
e2.ename
FROM
emp e1
LEFT JOIN emp e2 ON (e1.mgr = e2.empno);
8.查詢每個(gè)雇員的姓名及其所在部門(mén)的部門(mén)名（包括沒(méi)有雇員的部門(mén)）
SELECT
ename,
dname
FROM
emp e
RIGHT JOIN dept d ON (e.deptno = d.deptno);
9.查詢每個(gè)部門(mén)中工資最高的人的姓名鹿寨、薪水和部門(mén)編號(hào)
-- 先求出每個(gè)部門(mén)中的最高工資：
select max(sal) max_sal,deptno from emp group by deptno
-- 在使用連接查詢：
select ename,sal,e.deptno
from emp e join
(select max(sal) max_sal,deptno from emp group by deptno) t
on(e.sal = t.max_sal and e.deptno = t.deptno);
10.查詢每個(gè)部門(mén)平均工資所在的等級(jí)
select deptno,avg_sal,grade from salgrade
join(select deptno,avg(sal) avg_sal from emp group by deptno) t
on(t.avg_sal between salgrade.losal and salgrade.hisal);
或者：
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal);
11.查詢每個(gè)部門(mén)內(nèi)平均的薪水等級(jí)
先求每個(gè)人的薪水等級(jí)
select ename,deptno,grade from emp join salgrade s
on(emp.sal between s.losal and s.hisal);
再按組進(jìn)行分組求平均
select deptno,avg(grade) from
(select ename,deptno,grade from emp join salgrade s
on(emp.sal between s.losal and s.hisal)) t
group by deptno;
12.查詢雇員中有哪些人是經(jīng)理人：
select ename from emp where empno in(select distinct mgr from emp);
或者：
select ename from emp join
(select distinct mgr from emp) t
on(emp.empno=t.mgr);
13.平均薪水最高的部門(mén)的部門(mén)編號(hào)
1：先求出每個(gè)部門(mén)的平均薪水和部門(mén)號(hào)（把這個(gè)看成一張表）
select avg(sal) avg_sal,deptno from emp group by deptno;
2：再求出平均薪水最高值（把這個(gè)看成一個(gè)值）
select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t;
3：對(duì)1表使用2條件進(jìn)行查詢即可
select avg_sal,deptno from
(select avg(sal) avg_sal,deptno from emp group by deptno) t1
where avg_sal=
(select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t2);
14.求平均薪水最高的部門(mén)的部門(mén)名稱
select dname from dept where deptno =
( select deptno from
(select avg(sal) avg_sal,deptno from emp group by deptno) t1
where avg_sal=
(select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t2)
);
15.查詢平均薪水的等級(jí)最低的部門(mén)名稱
1.求平均薪水
select avg(sal) avg_sal,deptno from emp group by deptno;
2.求平均薪水的等級(jí)
select avg_sal,deptno,grade from
(select avg(sal) avg_sal,deptno from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and hisal);
3.求平均薪水的等級(jí)最低的那個(gè)值
select min(grade) from
( select avg_sal,deptno,grade from
(select avg(sal) avg_sal,deptno from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and hisal)
) t3;
4.平均薪水的等級(jí)最低的部門(mén)的部門(mén)編號(hào)(將2和3組合起來(lái)，將2看成要查詢的表薪夕，將3看成查詢條件)
select deptno from
( select avg_sal,deptno,grade from
(select avg(sal) avg_sal,deptno from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and hisal)
) t2
where grade=
( select min(grade) from
( select avg_sal,deptno,grade from
(select avg(sal) avg_sal,deptno from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and hisal)
) t3
);
5.平均薪水的等級(jí)最低的部門(mén)名稱
select dname,deptno from dept where deptno in
( select deptno from
( select avg_sal,deptno,grade from
(select avg(sal) avg_sal,deptno from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and hisal)
) t2
where grade=
( select min(grade) from
( select avg_sal,deptno,grade from
(select avg(sal) avg_sal,deptno from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and hisal)
) t3
)
);
