1.查詢所有的部門編號(hào)：

select deptno
from emp

select deptno
from dept

2.查詢所有有人的部門編號(hào)：

select  ename,deptno
from emp

3.查詢所有崗位名稱：

select job
from emp

4.查詢所有薪水超過兩千的員工信息

select *
from emp
where sal >2000

5.查詢所有20部門的員工姓名，編號(hào)及薪水

select ename,empno,sal
from emp
where deptno = 20

6.查詢所有沒有獎(jiǎng)金的員工信息

select * 
from emp
where comm is null or comm = 0

7.查詢所有有獎(jiǎng)金的員工信息

select *
from emp
where comm is not null and comm != 0

8.查詢最高領(lǐng)導(dǎo)的員工信息

select *
from emp 
where mgr is null

9.查詢所有81年之后入職的員工信息

select *
from emp 
where hiredate > '1981-12-31'

10.查詢所有薪水在2000-4000范圍內(nèi)的員工信息

select *
from emp
where sal between 2000 and 4000

11.查詢所有部門編號(hào)是10或30的員工信息

select *
from emp 
where deptno in (10,20)

12.查詢所有20部門并且薪水超過2000的員工信息：

select *
from emp
where deptno = 20 and sal > 2000

13.查詢所有薪水不在2000-4000范圍內(nèi)的員工信息

select *
from emp 
where sal <2000 or sal >4000

14.查詢所有部門編號(hào)不是10，30的員工信息

select *
from emp 
where deptno not in (10,30)

15.查詢用戶名為scott的員工信息：注意區(qū)分大小寫

select *
from emp
where binary ename = 'scott'

16.查詢姓名里面包含ALL的員工姓名

select ename
from emp
where ename like '%ALL%'

17.查詢所有以”S”開頭的同學(xué)

select *
from emp
where ename like 'S%'

18.查詢第二個(gè)字母為A的員工姓名

select ename
from emp
where ename like '_A%'

19.查詢所有員工的編號(hào)、姓名、部門編號(hào)、職位、薪水，按照薪水降序排列

select empno,ename,deptno,job,sal
from emp 
order by sal desc

20.查詢所有員工信息，按照部門降序排列，部門內(nèi)按照薪水升序排列

select *
from emp
order by deptno desc,sal

21.查詢姓名中包含‘A’員工的姓名，編號(hào)，薪水，按照薪水降序排列

select ename,empno,sal
from emp
where ename like '%A%'
order by sal desc

22.查詢年收入超過10000的員工的姓名，編號(hào)，薪水，年收入，按照年收入降序排列

select ename,empno,sal,sal*12 yearsal
from emp 
where sal*12>10000
order by yearsal desc

23.查詢年薪超過10000的員工的姓名，編號(hào)，薪水，年收入，按照年薪降序排列

select ename,empno,sal,sal*12 yearsal
from emp 
where sal*12>10000
order by yearsal desc

后續(xù)練習(xí)題：

24.查詢雇員表中，姓名為SMITH的雇員，截止到今天共工作了多少周，則可以使用如下的SQL語句

select timestampdiff(week,hiredate,now())
from emp
where ename = 'SMITH'

25.查詢各部門的最高薪水、最低薪水、平均薪水…..

select max(sal),min(sal),avg(sal)
from emp
group by deptno

26.查詢‘SMITH’的領(lǐng)導(dǎo)姓名

select ename
from emp
where ename = (select mgr from emp where ename ='SMITH')

27.查詢部門名稱是‘SALES’的員工信息

select *
from emp
where deptno = (select deptno from dept where dname='SALES')

28.查詢公司中薪水最高的員工信息

select *
from emp
where sal = (select max(sal) from emp)

29.查詢公司所有員工的個(gè)數(shù)

select count(empno)
from emp

30.查詢公司中最高薪水是多少

select max(sal)
from emp

31.查詢公司中平均獎(jiǎng)金是多少

select avg(ifnull(sal,0))
from emp

32.查詢公司中最晚入職的時(shí)間

select max(hiredate)
from emp

33.查詢公司中有獎(jiǎng)金的人數(shù).

select count(empno)
from emp
where comm is not null

34.查詢20部門的最高薪水是多少.

select max(sal)
from emp 
where deptno = 20

35.查詢各部門的平均薪水及部門編號(hào)，部門名稱。

select deptno,dname,avg(sal)
from emp e join dept d
on e.deptno = d.deptno
group by deptno,dname

36.查詢各部門中最高薪水的員工編號(hào)，姓名…

select empno,ename
from emp e
where sal = (select max(sal) from emp where deptno = e.deptno)

37.查詢所有員工姓名中包含‘A’的最高薪水

select max(sal)
from emp
where ename like '%A%'

38.查詢各崗位的最高薪水，最低薪水。要求只統(tǒng)計(jì)薪水>1000的

select max(sal), min(sal)
from emp
where sal > 1000
group by job

39.查詢各部門的平均薪水及部門編號(hào)，要求只列出平均薪水>2000

select deptno,avg(sal) avgsal
from emp
group by deptno
having avgsal > 2000

40.查詢各部門的平均薪水及部門編號(hào)，要求只有員工姓名中包含‘A’才參與統(tǒng)計(jì)，只列出平均薪水>1500的，按照平均薪水降序排列

select deptno,avg(sal) avgsal
from emp
where ename like '%A%'
GROUP BY deptno
having avgsal > 1500
order by avgsal desc

41.查詢各部門最高薪水的員工信息

select *
from emp e
where sal =(select max(sal) from emp where deptno = e.deptno)

42.查詢最高薪水的員工信息

select *
from emp e
where sal =(select max(sal) from emp where deptno = e.deptno)

43.查詢薪水大于該部門平均薪水的員工信息

select *
from emp e 
where sal > (select avg(sal) from emp where deptno = e.deptno)

44.查詢最高薪水的員工信息

select *
from emp e
where sal =(select max(sal) from emp where deptno = e.deptno)

45.查詢各部門最高薪水的員工信息

select *
from emp e
where sal =(select max(sal) from emp where deptno = e.deptno)

46.查詢‘SMITH’的領(lǐng)導(dǎo)姓名

select ename
from emp
where ename = (select mgr from emp where ename ='SMITH')

47.查詢部門名稱是‘SALES’的員工信息

select *
from emp
where deptno = (select deptno from dept where dname='SALES')

48.查詢公司中薪水最高的員工信息

select *
from emp e
where sal =(select max(sal) from emp where deptno = e.deptno)

49.查詢薪水等級(jí)為4的員工信息

select *
from emp e join salgrade s
on sal between losal and hisal
where grade = 4

50.查詢領(lǐng)導(dǎo)者是‘BLAKE’的員工信息

select *
from emp 
where mgr = (select empno from emp where ename = 'BLAKE')

51.查詢最高領(lǐng)導(dǎo)者的薪水等級(jí)

select grade
from emp e join salgrade s
on sal between losal and hisal
where mgr is null

52.查詢薪水最低的員工信息

select *
from emp e
where sal =(select min(sal) from emp where deptno = e.deptno)

53.查詢和SMITH工作相同的員工信息

select *
from emp
where job in (select job from emp where ename = 'SMITH' )

54.查詢不是領(lǐng)導(dǎo)的員工信息

select *
from emp 
where empno not in (select mgr from emp) 

55.查詢平均工資比10部門低的部門編號(hào)

select deptno 
from emp
group by deptno
having avg(sal) < (select avg(sal) from emp where deptno = 10)

56.查詢?cè)诩~約工作的所有員工

select *
from emp
where deptno in (select deptno from dept where loc = 'NEW YORK')

57.查詢‘SALES’部門平均薪水的等級(jí)

select avg(grade)
from emp e join salgrade s
on sal between losal and hisal
where deptno = (select deptno from dept where dname = 'SALES')

58.查詢10號(hào)部門的員工在整個(gè)公司中所占的比例：

select count(empno)/(select count(empno) from emp)
from emp 
where deptno =10

59.emp顯示前5條。

select *
from emp
limit 0,5

60.查詢各部門工資大于該部門平均工資的員工信息：

select *
from emp e
where sal >(select avg(sal) from emp where deptno = e.deptno)

61.查詢各崗位工資小于該崗位平均工資的員工信息；

select *
from emp e
where sal >(select avg(sal) from emp where job = e.job)

62.查詢所有領(lǐng)導(dǎo)的信息：要求使用exists關(guān)鍵字

select *
from emp e
where exists (select 1 from emp where mgr = e.empno)

63.查詢所有員工的姓名，薪水，部門名稱

select ename,sal,dname
from emp e join dept d
on e.deptno = d.deptno

64.查詢所有員工的姓名，薪水，部門名稱,薪水等級(jí)

select ename,sal,dname,grade
from emp e join dept d
on e.deptno = d.deptno
join salgrade s
on sal between losal and hisal

65.查詢員工姓名及領(lǐng)導(dǎo)者姓名

select e.ename,m.ename
from emp e join emp m
on e.mgr = m.empno

66.查詢所有員工的姓名，部門名稱

select ename,dname
from emp e join dept d
on e.deptno = d.deptno

練習(xí)題：

1.查詢員工表中工資大于1600的員工的姓名和工資

select ename,sal
from emp
where sal >1600

2.查詢員工表中員工號(hào)是17的員工的姓名和部門編號(hào)

select ename,deptno 
from emp
where empno = 17

3.選擇員工表中工資不在4000到5000內(nèi)的員工的姓名和工資.

select ename,sal
from emp 
where sal < 4000 or sal >5000

4.選擇員工表中在20和30部門工作的員工的姓名和部門號(hào)

select ename,deptno 
from emp
where deptno in (20,30)

5.選擇員工表中沒有管理者的員工姓名及職位，按職位排序.

select ename,job 
from emp
where mgr is null
order by job

6.選擇員工表中有獎(jiǎng)金的員工姓名，工資和獎(jiǎng)金，按工資倒序排列

select ename,sal,comm
from emp
where comm is not null
order by sal desc

7.選擇員工表中員工姓名的第三個(gè)字母是A的員工姓名

select ename
from emp 
where ename like '__A%'

8.列出部門表中的部門名稱和所在城市

select dname,loc
from dept

9.顯示員工表中的不重復(fù)的崗位job

select distinct job
from emp

10.連接員工表中的員工姓名、職位、薪水，列之間用逗號(hào)連接，列頭顯示成out_put

select concate(ename,',',job,',',sal) out_put
from emp

11.查詢員工表中員工號(hào)，姓名，工資，以及工資提高百分之20之后的結(jié)果

select empno,ename,sal,sal*1.2
from emp

12.查詢員工的姓名和工資數(shù)，條件限定為工資數(shù)必須大于1200，并且查詢結(jié)果按入職時(shí)間進(jìn)行排序。早入職的員工排在前面

select ename,sal
from emp
where sal > 1200
order by hiredate

13.列出除了'ACCOUNTING'部門之外還有什么部門

select dname
from dept
where dname != 'ACCOUNTING'

14.把雇員按部門分組，求最高薪水，部門號(hào) 要求過濾掉名字中第二個(gè)字母是’A’的員工， 并且部門的平均薪水 > 3000,按照部門編號(hào)倒序排列

select max(sal),deptno
from emp
where ename not like '_A%'
group by deptno 
having avg(sal) >3000
order by deptno desc

15.求工作職位是’manager’的員工姓名，部門名稱和薪水等級(jí)

select ename,dname,grade
from emp e join dept d
on e.deptno = d.deptno
join salgrade s
on sal between losal and hisal
where job = 'manager'

16.按照部門分組統(tǒng)計(jì)，求最高薪水，平均薪水，最低薪水，只有薪水是1200以上的員工才參與統(tǒng)計(jì)，并且分組結(jié)果中只包含平均薪水在1500以上的部門，并且按照平均薪水倒序排列

select max(sal),avg(sal),min(sal)
from emp
where sal > 1200
group by deptno
having avg(sal) > 1500
order by avg(sal) desc

17.求薪水最高的員工姓名

select ename
from emp 
where sal = (select max(sal) from emp)

18.查詢各部門平均薪水等級(jí)，并且按平均薪水等級(jí)的降序排列

select avg(grade)
from emp e join salgrade s
on sal between losal and hisal
group by deptno
order by avg(grade)

19.查詢所有員工姓名以S或s開頭的所有員工信息

select *
from emp
where binary ename like 'S%' or binary ename like 's%'

20.查詢所有工作時(shí)間超過一年的員工編號(hào)，姓名及入職時(shí)間,要求雇用時(shí)間的格式為’yyyy年mm月dd日’

select empno,ename,date_format(hiredate,'%Y年%月m%d日')
from emp
where timestampdiff(year,hiredate,now())>1

21.查詢20部門的所有員工的員工姓名，實(shí)際收入

select ename,sal
from emp
where deptno = 20

22.查詢10部門工資大于3000的員工信息，要求按員工的入職時(shí)間由前到后排序

select *
from emp
where sal > 3000 and deptno = 10
order by hiredate

23.查詢10部門或20部門的所有員工的姓名，并截取前三位，按員工姓名升序排列

select ename
from emp 
where deptno in (10,20)
order by ename
limit 0,3

24、查詢所有員工的姓名，要求所有員工的姓名顯示成小寫，雇用日期顯示為”yyyy-mm-dd”這種格式

select lcase(ename),date_format(hiredate,'%Y-%m-%d')
from emp

25、查詢所有員工的姓名，所在部門名稱，薪水，薪水等級(jí)、直接領(lǐng)導(dǎo)的姓名 （有問題，不顯示最高領(lǐng)導(dǎo)）

select ename,dname,sal,grade,m.ename
from emp e join dept d
on e.deptno = d.deptno
join salgrade s
on sal between losal and hisal
join emp m
on e.mgr = m.empno

26、查詢部門名稱是’ACCOUNTING’的員工姓名及薪水等級(jí)

select ename,grade
from emp e join salgrade s
on sal between losal and hisal
where deptno = (select deptno from dept where dname = 'ACCOUNTING')

27、不能使用組函數(shù)，查詢薪水的最高值.

select sal
from emp
order by sal desc
limit 0,1

28、統(tǒng)計(jì)平均薪水最高的部門名稱

select dname
from (select dname,avgsal from(select dname,avg(sal) avgsal
from emp e join dept d
on deptno,dname)) f
where avgsal = (select max(avgsal) from (select dname,avg(sal) avgsal
from emp e join dept d
on e.deptno = d.deptno
group by deptno,dname) where dname = f.dname)

29、查詢平均薪水等級(jí)最低的部門名稱

select dname
from (select dname,avggrade from(select dname,avg(grade) avggrade
from emp e join dept d
on e.deptno = d.deptno
join salgrade s
on sal between losal and hisal
group by deptno,dname)) f
where avggrade = (select min(avggrade) from (select dname,avg(grade) avggrade
from emp e join dept d
on e.deptno = d.deptno
join salgrade s
on sal between losal and hisal
group by deptno,dname) where dname = f.dname)

選做

1、查詢平均薪水最低的部門名稱，要求：只有領(lǐng)導(dǎo)才參加統(tǒng)計(jì)

select dname
from (select dname,avgsal from(select dname,avg(sal) avgsal
from emp e join dept d
on deptno,dname)) f
where avgsal = (select max(avgsal) from (select dname,avg(sal) avgsal
from emp e join dept d
on e.deptno = d.deptno
group by deptno,dname where empno in (select distinct mgr from emp)) where dname = f.dname)

2、查詢比普通員工的最高薪水還要高的領(lǐng)導(dǎo)者姓名

//領(lǐng)導(dǎo)的員工編號(hào)及工資
(select ename,empno,sal
from emp
where empno in (select mgr from emp)) c

//普通員工的編號(hào)及工資
select empno,sal
from emp
where empno not in (select mgr from emp)

//普通員工的最高薪水
select max(sal)
from (select sal from(select empno,sal
from emp
where empno not in (select mgr from emp)
) a) b
//求領(lǐng)導(dǎo)姓名
select ename
from (select ename,empno,sal from(select ename,empno,sal
from emp
where empno in (select mgr from emp)) c) d
where sal >(select max(sal)
from (select sal from(select empno,sal
from emp
where empno not in (select mgr from emp)
) a) b)

3、找出薪水最高的五個(gè)人

select *
from emp
order by sal desc
limit 0,5

4、查詢第2到第7名的員工，按薪水降序排列

select *
from emp
order by sal desc
limit 1,6

5、查詢最后入職的5名員工

select *
from emp
order by hiredate desc
limit 0,5