sql例題學(xué)習(xí)

分享一波sql測試題及自己寫的sql答案, 如果有更好的sql寫法, 歡迎批評指正

部門表 dept：
DEPTNO DNAME LOC
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

員工表 emp:
EMPNO, ENAME, JOB, MGR, HIREDATE, SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,1980-12-17,800,null,20
7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02,2975,null,20
7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01,2850,null,30
7782,CLARK,MANAGER,7839,1981-06-09,2450,null,10
7788,SCOTT,ANALYST,7566,1987-04-19,3000,null,20
7839,KING,PRESIDENT,null,1981-11-17,5000,null,10
7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23,1100,null,20
7900,JAMES,CLERK,7698,1981-12-03,950,null,30
7902,FORD,ANALYST,7566,1981-12-02,3000,null,20
7934,MILLER,CLERK,7782,1982-01-23,1300,null,10

需求:

  1. 查詢出 JONES 的領(lǐng)導(dǎo)是誰（JONES 向誰報(bào)告）。
  2. JONES 領(lǐng)導(dǎo)誰。（誰向JONES 報(bào)告）。
  3. 查詢各職位的員工工資的最大值，最小值，平均值，總和
  4. 選擇具有各個(gè)job 的員工人數(shù)(提示:對job 進(jìn)行分組)
  5. 查詢員工最高工資和最低工資的差距,列名為DIFFERENCE；
  6. 查詢各個(gè)管理者屬下員工的最低工資，其中最低工資不能低于 800，沒有管理者的員工不計(jì)算在內(nèi)
  7. 查詢所有部門的部門名字dname，所在位置loc，員工數(shù)量和工資平均值；
  8. 查詢和scott 相同部門的員工姓名ename 和雇用日期hiredate
  9. 查詢工資比公司平均工資高的所有員工的員工號 empno，姓名ename 和工資sal。
  10. 查詢和姓名中包含字母u 的員工在相同部門的員工的員工號 empno 和姓名ename
  11. 查詢在部門的loc 為 newYork 的部門工作的員工的員工姓名ename，部門名稱 dname 和崗位名稱job
  12. 查詢管理者是king 的員工姓名 ename 和工資sal
  13. 顯示Operations 部門有哪些職位
  14. 各個(gè)部門中工資大于1500的員工人數(shù)
  15. 哪些員工的工資，高于整個(gè)公司的平均工資，列出員工的名字和工資（降序）
  16. 所在部門平均工資高于1500的員工名字
  17. 列出各個(gè)部門中工資最高的員工的信息：名字、部門號、工資
  18. 哪個(gè)部門的平均工資是最高的，列出部門號、平均工資

=======================

數(shù)據(jù)準(zhǔn)備

create table if not exists  dept(
deptno int,
dname varchar(20),
loc varchar(20)
);
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');

create table  if not exists  emp(
empno int,
ename varchar(20),
job varchar(20),
mgr int,
hiredate date,
sal double,
comm double,
deptno int
);
insert into emp values ( 7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30 );
insert into emp values ( 7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
insert into emp values ( 7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);
insert into emp values ( 7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into emp values ( 7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);
insert into emp values (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10 );
insert into emp values ( 7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);
insert into emp values ( 7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
insert into emp values ( 7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
insert into emp values ( 7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
insert into emp values (7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30 );
insert into emp values ( 7902,'FORD','ANALYST',7566,'1981-12-02',3000,null,20);
insert into emp values ( 7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);

==================================

答案


1. 查詢出 JONES 的領(lǐng)導(dǎo)是誰（JONES 向誰報(bào)告）。
select ename
from emp 
where empno in ( select mgr from emp where ename = 'JONES' );

2. JONES 領(lǐng)導(dǎo)誰。（誰向JONES 報(bào)告）。
select ename
from emp 
where mgr in ( select empno from emp where ename = 'JONES' );

3. 查詢各職位的員工工資的最大值，最小值，平均值，總和
SELECT  job,min(sal) min, max(sal) max, avg(sal) avg, sum(sal) sum 
from  emp 
group by  job ;

4. 選擇具有各個(gè) job 的員工人數(shù)(提示:對job 進(jìn)行分組)
SELECT  job,count(distinct empno) count 
from  emp 
group by  job ;

5. 查詢員工最高工資和最低工資的差距,列名為 DIFFERENCE ；
select max(sal)-min(sal) as  DIFFERENCE 
from emp ;

6. 查詢各個(gè)管理者屬下員工的最低工資，其中最低工資不能低于 800 ，沒有管理者的員工不計(jì)算在內(nèi)
select  a.empno, a.ename, b.min 
from 
emp a 
inner join  
(
    SELECT   mgr, min(sal) min 
    from emp 
    where mgr is not null 
    group by mgr 
    having min(sal) >= 800 
) b 
on a.empno = b.mgr ;

7. 查詢所有部門的部門名字 dname ，所在位置 loc ，員工數(shù)量和工資平均值；  
select min(a.dname) dname, min(a.loc) loc, count(b.empno) count, avg(b.sal) avg 
from 
dept a left join emp b
on a.deptno = b.deptno 
group by a.deptno  ;


8. 查詢和 scott 相同部門的員工姓名 ename 和雇用日期 hiredate
select  a.deptno,a.ename,a.hiredate 
from 
emp a  
join 
(
select   deptno 
from  emp  
where emp.ename = 'scott'  
) b 
on a.deptno = b.deptno and a.ename != 'scott';  


9. 查詢工資比公司平均工資高的所有員工的員工號 empno，姓名ename 和工資sal
select a.empno,a.ename,a.sal,b.sal 
from 
emp a 
join 
( select  avg(sal) sal from emp )  b 
on  a.sal > b.sal ;


10. 查詢和姓名中包含字母 u 的員工在相同部門的員工的員工號 empno 和姓名ename
select a.empno,a.ename,a.deptno 
from 
emp a 
inner join 
(
select  ename,deptno
from emp 
where ename like '%u%' 
) b 
on a.deptno = b.deptno and a.ename != b.ename ;

11. 查詢在部門的loc 為 newYork 的部門工作的員工的員工姓名ename，部門名稱 dname 和崗位名稱job
select 
a.ename, b.dname, a.job
from 
emp a
join 
(
select deptno, dname
from dept 
where loc = 'NEW YORK' 
) b 
on a.deptno = b.deptno ;

12. 查詢管理者是 king 的員工姓名 ename 和工資sal 
SELECT
a.ename, a.sal 
from 
emp a
join 
(
select empno from emp where ename = 'king' 
) b 
on a.mgr = b.empno;


13. 顯示 Operations 部門有哪些職位
select 
a.job
from 
emp a 
join 
( select deptno from dept where dname = 'Operations' ) b 
on a.deptno = b.deptno 
group by a.job ;

14. 各個(gè)部門中工資大于1500的員工人數(shù) 
select 
b.deptno, count(distinct a.ename) count 
from 
dept b 
left join 
(select ename,deptno from emp where sal > 1500 ) a 
on a.deptno = b.deptno 
group by b.deptno ;

15. 哪些員工的工資，高于整個(gè)公司的平均工資，列出員工的名字和工資（降序）
select 
a.ename, a.sal, b.avgsal 
from 
emp a 
join 
(select avg(sal) avgsal from emp ) b 
on a.sal > b.avgsal
order by a.sal desc  ;

16. 所在部門平均工資高于2000的員工名字
select 
a.ename, a.sal , a.deptno 
from 
emp a 
join 
(select deptno from emp group by deptno having avg(sal) > 2000 ) b 
on a.deptno = b.deptno ;

17. 列出各個(gè)部門中工資最高的員工的信息：名字、部門號、工資
select 
a.deptno, a.ename, a.sal 
from 
emp a 
join 
(select deptno,max(sal) sal from emp group by deptno ) b 
on a.deptno = b.deptno and a.sal = b.sal ;

18. 哪個(gè)部門的平均工資是最高的，列出部門號、平均工資
select 
deptno, avg(sal) avgsal 
from emp 
group by deptno 
order by avgsal desc
limit 1  ;
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 5.多表查詢 多表查詢 目的：從多張表獲取數(shù)據(jù) 前提：進(jìn)行連接的多張表中有共同的列 等連接 通過兩個(gè)表具有相同意義...
    喬震閱讀 1,549評論 0贊 0
  • 引出 ?請思考如下問題？ –查詢所有員工的每個(gè)月工資總和，平均工資？ –查詢工資最高和最低的工資是多少？ –查詢公...
    C_cole閱讀 7,392評論 0贊 3
  • 1. select * from emp; 2. select empno, ename, job from em...
    海納百川_4d26閱讀 1,997評論 0贊 4
  • 1.簡介 數(shù)據(jù)存儲有哪些方式？電子表格，紙質(zhì)文件，數(shù)據(jù)庫。 那么究竟什么是關(guān)系型數(shù)據(jù)庫？ 目前對數(shù)據(jù)庫的分類主要是...
    喬震閱讀 2,023評論 0贊 2
  • 《深夜加油站遇見蘇格拉底》這本書也許是我做批注最多的一本書，從個(gè)人成長走向心靈成長，我不覺得自己步伐快。 這本書是...
    南山讀書閱讀 692評論 0贊 0

友情鏈接更多精彩內(nèi)容