Sql练习题

1.表和数据的准备

员工表

create table emp (
    empno numeric(4) not null comment '员工号',
    ename varchar(10) comment '员工姓名',
    job varchar(9) comment '工作',
    mgr numeric(4) comment '上级编号',
    hiredate datetime comment '受雇日期',
    sal numeric(7, 2) comment '薪金',
    comm numeric(7, 2) comment '佣金',
    deptno numeric(2) comment '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';
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, '1982-12-09', 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, '1983-01-12', 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-03', 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

部门表

create table dept (
    deptno numeric(2) comment '部门编号',
    dname varchar(14) comment '部门名称',
    loc varchar(13) comment '地点'
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
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 salgrade (
    grade numeric comment '等级',
    losal numeric comment '此等级的最低',
    hisal numeric comment '此等级的最高'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工资等级表';

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, 9999);

2.练习题目

1. 查询出部门编号为30的所有员工的编号和姓名。

select empno,ename 
from emp 
where deptno=30;

2.找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。

select empno,ename,job,hiredate,sal,deptno 
from emp 
where (deptno = 10 and job = 'MANAGER') 
or (deptno = 20 and job = 'SALESMAN');

3.查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序（工资 ＝ 薪金 ＋ 佣金）

select empno,ename,job,hiredate,sal,deptno 
from emp
order by (sal+ifnull(comm,0)) desc,hiredate;

4.列出最低薪金大于1500的各种工作及从事此工作的员工人数。

select job,count(1) from emp group by job having min(sal)>1500;

5.列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

select e.ename from emp as e left join dept as d on e.deptno=d.deptno where  d.dname='SALES' ; 
select e.ename from emp as e where e.deptno = (select deptno from dept where dname='SALES');

6.查询姓名以S开头的\以S结尾\包含S字符\第二个字母为L

select * from emp where ename like 'S%';
select * from emp where ename like '%S';
select * from emp where ename like '%S%';
select * from emp where ename like '_L%';

7.查询每种工作的最高工资、最低工资、人数

select job,max(sal+ifnull(comm,0)) as max_sal,min(sal+ifnull(comm,0)) as min_sal,count(1) from emp group by job;

8.列出薪金 高于 公司平均薪金的所有员工号，员工姓名，所在部门名称，上级领导，工资，工资等级

select
    e.empno,
    e.ename,
    d.dname,
    m.ename,
    e.sal + ifnull(e.comm,0),
    s.grade
from
    emp e
left join dept d on
    e.deptno = d.deptno
left join emp m on
    e.mgr = m.empno
left join salgrade s on
    e.sal between s.losal and s.hisal
where
    (e.sal + ifnull(e.comm,0)) >
    (select avg(sal + ifnull(comm, 0))
    from
        emp);

9.列出薪金 高于 在部门30工作的 所有/任何一个员工的薪金的员工姓名和薪金、部门名称。

select ename,e.sal,d.dname
from emp e,dept d 
where e.deptno=d.deptno and sal > all(select sal from emp where deptno=30);

select ename,e.sal,d.dname
from emp e,dept d 
where e.deptno=d.deptno and sal > any(select sal from emp where deptno=30);