【SQL数据库】查询语句练习题

表结构和初始化数据

drop database if exists emps;
create database emps;
use emps;

create table emp(
EMPNO INT, -- 员工号
ENAME VARCHAR(10), -- 员工姓名
JOB VARCHAR(9), -- 工作岗位
MGR int, -- 经理的员工号，外键
HIREDATE date, -- 入职时间
SAL double, -- 底薪
COMM double, -- 提成
DEPTNO int, -- 部门编号
primary key(EMPNO)
) ;

CREATE TABLE dept (
DEPTNO int, -- 部门编号
DNAME varchar(13), -- 部门名称
LOC VARCHAR(13), -- 部门地点
primary key(DEPTNO)
) ;

-- 插入数据

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,'2003-12-17',800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,'2007-9-3',1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,'2005-3-8',1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,'2007-7-7',2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,'2005-5-6',1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,'2005-5-6',2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,'2010-3-1',2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,'2005-5-6',3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,'2001-5-6',5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,'2008-8-8',1500,0,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,'2005-5-6',1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,'2005-5-6',950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,'2005-5-6',3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,'2005-5-6',1300,null,10);

Insert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');

练习题

-- 1．列出至少有一个员工的所有部门。

select * 
from dept 
where DEPTNO in (
select DEPTNO 
from emp group by DEPTNO having count(DEPTNO) >=1);  

-- 2．列出薪金比“SMITH”多或者相等的所有员工。

select  *
from emp
where SAL > (select SAL 
from emp 
where ENAME="SMITH");

-- 3．列出所有员工的姓名及其直接上级的姓名。

select e1.ename,(select ename from emp e2 where e2.empno=e1.mgr) from emp e1;

-- 4．列出受雇日期早于其直接上级的所有员工。

select *
from emp e1
where HIREDATE < (select HIREDATE
from emp e2
where e2.EMPNO = e1.MGR);

-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门


select d.dname,e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.deptno  
from dept d left join emp e on d.deptno=e.deptno;


-- 6．列出所有“CLERK”（办事员）的姓名及其部门名称。

select e.ename,d.dname 
from emp e join dept d 
WHERE e.deptno=d.deptno and e.job='CLERK';  

-- 7．列出最低薪金大于1500的各种工作。

select DISTINCT JOB 
from emp e1
where 1500 < (select min(SAL)
from emp e2
where e2.JOB = e1.JOB);

-- 8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

select ENAME
from emp e
where e.DEPTNO = (select DEPTNO
from dept
where DNAME = "SALES");

-- 9．列出薪金高于公司平均薪金的所有员工。

select *
from emp
where sal > (select AVG(sal)
from emp);

-- 10．列出与“SCOTT”从事相同工作的所有员工。

select e1.* from emp e1, emp e2
where e2.ename = "SCOTT" and e1.job = e2.job;

-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

select a.ename,a.sal from emp a where a.sal in (
select b.sal  
from emp b where b.deptno=30);

-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

select ENAME,SAL 
from emp 
where SAL > (
select max(SAL) from emp where DEPTNO=30);  

-- 13．列出在每个部门工作的部门名称,员工数量、平均工资。

select (select b.dname from dept b where a.deptno=b.deptno),count(deptno),avg(sal) 
from emp a group by deptno;

-- 14．列出所有员工的姓名、部门名称和工资(含奖金)。

select a.ename,(select b.dname from dept b where b.deptno=a.deptno),sal+IFNULL(COMM,0)  
from emp a; 

-- 15．列出所有部门的详细信息和部门人数。

select a.*,(select COUNT(deptno) 
from emp b
where b.DEPTNO = a.DEPTNO
group by b.DEPTNO)
from dept a;

-- 16．列出各种工作的最低工资。

select JOB,min(SAL)
from emp group by JOB; 

-- 17．列出各个MANAGER（经理）的最低薪金。

select DEPTNO,min(SAL) 
from emp 
where JOB='MANAGER' 
group by DEPTNO;

-- 18．列出所有员工的年工资,按年薪从低到高排序。

select ename,(sal+IFNULL(comm,0))*12 as salPlusComm
from emp order by salPlusComm;