SQL练习题1

数据表

-- 部门表
create table dept(
	deptno int primary key auto_increment, -- 部门编号
	dname varchar(14) ,	  -- 部门名字
	loc varchar(13)   -- 地址
) ;
-- 员工表
create table emp(
	empno int primary key auto_increment,-- 员工编号
	ename varchar(10), -- 员工姓名										-
	job varchar(9),	-- 岗位
	mgr int,	 -- 直接领导编号
	hiredate date, -- 雇佣日期，入职日期
	sal int, -- 薪水
	comm int,  -- 提成
	deptno int not null, -- 部门编号
	foreign key (deptno) references dept(deptno)
);

测试数据

#部门表
insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');

#员工表
insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭靖','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'令狐冲','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'张无忌','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'杨过','职员',7782,'1983-01-23',1300,null,10);

use test_db;
# 列出至少有一个员工的所有部门（部门编号、部门名称、部门人数）
select d.deptno, d.dname, count(*) AS '部门人数'
from dept d
         inner join emp e on d.deptno = e.deptno
group by d.deptno
having count(*) != 0;

# 列出受雇日期早于其直接上级的所有员工
select e1.empno, e1.ename
from emp e1
         inner join emp e2 on e1.mgr = e2.empno
where e1.hiredate < e2.hiredate;

# 列出所有员工的年工资,按年薪从低到高排序
select sal * 12 + ifnull(0, emp.comm) AS '年薪'
from emp
order by '年薪';
#ifnull():三元运算符(a,b),空结果a,非空结果b

# 将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序
select convert(emp.ename using GBK), sal
from emp
order by ename, sal DESC;
# convert(转换值[,数据类型] [using 编码])

# 查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示
select max(e.sal), min(e.sal)
from emp e
         inner join dept d on e.deptno = d.deptno
group by d.deptno
having d.deptno <> 10;

# 查出emp表中所有部门的名称、最高薪水、最低薪水，部门编号为10的部门不显示
select max(e.sal), min(e.sal), d.dname
from emp e
         inner join dept d on e.deptno = d.deptno
group by d.deptno
having d.deptno <> 10;

# 删除10号部门薪水最高的员工
delete
from emp e
where e.deptno = 10
  and sal = (select MAX(sal) from emp e1 where e1.deptno = 10);

# 查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
select e.ename, e.sal, (case when e.sal >= 3000 then '3级' when e.sal > 2000 then '2级' when e.sal <= 2000 then '1级' END ) AS '工资等级'
from emp e