sql语句练习-基础篇
本文内容源自改编http://blog.csdn.net/ochangwen/article/details/51297893，
- 针对mysql数据库做了语法更改
- 个人觉得原版有些不合理之处，改了部分表结构
开始，初始化表结构与数据
- 表1，部门表
create table dept(
deptno int(10) primary key,#主键
dname varchar(30),#部门名称
loc varchar(30)#所在地
);
-表2，员工表
create table emp(
empno int(10) primary key,#empno 员工号
ename varchar(30),#ename 员工姓名
job varchar(30),#job 工作
mgr int(10),#mgr 上级编号
hiredate int(10),#hiredate 受雇日期
sal int(10),#sal 薪金
comm int(10),#comm 佣金
deptno int(10),#deptno 部门编号
foreign key(deptno) references dept(deptno)
);
- 插入以及其他语句
insert into dept values(1, '技术部' ,'南泥湾');
insert into dept values(2, 'SALES' ,'深圳市');
insert into dept values(3, '事业部' ,'北京市');
insert into dept values(4, '服务部' ,'延安');
insert into dept values(5, '生产部' ,'南京市');
insert into dept values(6, '宣传部' ,'上海市');
insert into dept values(7, '打杂部' ,'广州市');
insert into dept values(8, '司令部' ,'重庆市');
insert into dept values(9, '卫生部' ,'长沙市');
insert into dept values(10, '文化部' ,'武冈市');
insert into dept values(11, '娱乐部' ,'纽约');
insert into dept values(12, '管理部' ,'伦敦');
insert into dept values(13, '行政部' ,'天津市');
insert into emp values(1, '关羽羽', 'CLERK' ,8, 20011109, 2000, 1000, 3);
insert into emp values(2, 'SMITH', 'CLERK' ,1, 20120101, 2000, 800, 6);
insert into emp values(3, '刘备备', 'MANAGER' ,0, 20080808, 9000, 4000, 3);
insert into emp values(4, 'TOM', 'ENGINEER' ,5, 20050612, 3000, 1000, 1);
insert into emp values(5, 'Steve', 'MANAGER' ,8, 20110323, 80000, 9000, 1);
insert into emp values(6, '张飞飞', 'CLERK' ,1, 20101010, 2000, 1000, 3);
insert into emp values(7, 'SCOTT', 'CLERK' ,1, 20071204, 2000, 1000, 3);
insert into emp values(8, '老板', 'Boss' ,0, 20060603, 2000, 1000, 8);
insert into emp values(9, '曹仁人', 'SALESMAN' ,10, 20120130, 2000, 1000, 2);
insert into emp values(10, '曹操操', 'MANAGER' ,8,20090815, 2000, 1000, 2);
insert into emp values(11, '酱油哥', 'HAPI' ,0,20090215, 3, 1, 2);
drop table emp;
drop table dept;
练习语句与答案
1、列出至少有一个员工的所有部门。
select * , (select count(*) from emp e where e.deptno = d.deptno) c from dept d having c > 0
-- 方法1：有一个代表员工有关联字段数据
select d.* from dept d where exists (select 1 from emp e where e.deptno = d.deptno)
-- 方法2：得到各个部门员工数量，count > 0
select d.* from dept d where EXISTS (select 1 from emp e where e.deptno = d.deptno having count(e.deptno) > 1);-- sql02
select d.* from dept d where d.deptno in (select e.deptno from emp e GROUP BY e.deptno having count(e.deptno) > 1);-- sql03
注：sql01是查询最少一个员工，sql02，sql03是查询大于1的2种写法！
2、列出薪金比“SMITH”多的所有员工。（大于最大薪水SMITH员工）
select * from emp e where e.sal > (select sal from emp where ename = "SMITH")
3、列出所有员工的姓名及其直接上级的姓名。
select e2.ename,e1.ename from emp e1,emp e2 where e1.empno = e2.mgr
4、列出受雇日期早于其直接上级的所有员工。
select e2.empno ,e2.ename from emp e1,emp e2
where e1.hiredate < e2.hiredate and e1.empno = e2.mgr
5、列出部门名称和这些部门的员工信息，包括那些没有员工的部门。
select * from dept d LEFT JOIN emp p on d.deptno = p.deptno
6、列出所有job为“CLERK”（办事员）的姓名及其部门名称。
select e.ename,d.dname from emp e,dept d where e.job = "CLERK" and e.deptno = d.deptno
7、列出薪金大于1500的各种工作。
select distinct(e.job) from emp e where e.sal > 1500
select e.job from emp e where e.sal > 1500 group by e.job
select e.job from emp e where e.sal > 1500 group by e.job having COUNT(e.job) = 1
8、列出在部门“SALES”（技术部）工作的员工的姓名，假定不知道销售部的部门编号。
select e.ename,d.dname from emp e,dept d where e.job = "CLERK" and e.deptno = d.deptno
select e.ename from emp e where exists (select 1 from dept d where d.dname = "技术部" and e.deptno = d.deptno)
9、列出薪金高于公司平均薪金的所有员工。
select e.ename from emp e where e.sal > (select AVG(es.sal) from emp es)
10、列出与“SCOTT”从事相同工作的所有员工。
select e.ename from emp e where e.job = (select job from emp where ename = "SCOTT") and e.ename != "SCOTT"
11、列出薪金等于"技术部"中员工的薪金的所有员工的姓名和薪金。
select e.ename and e.sal from emp e where e.sal in (select e.sal from dept d ,emp e where d.dname = "技术部" and e.deptno = d.deptno)
select e1.ename and e1.sal from emp e1 where exists (select 1 from dept d ,emp e where d.dname = "技术部" and e.deptno = d.deptno and e1.empno = e.empno)
12、列出薪金高于在"技术部"工作的所有员工的薪金的员工姓名和薪金。
select e.ename and e.sal from emp e where e.sal in (select e.ename,max(e.sal) from dept d ,emp e where d.dname = "技术部" and e.deptno = d.deptno)
13、列出在每个部门工作的员工数量、平均工资.
select d.dname, c,a from dept d left join (select e.deptno deptno,count(e.empno) c,avg(sal+comm) a from emp e group by e.deptno) t on d.deptno = t.deptno
14、列出所有员工的姓名、部门名称和工资。
select e.ename,d.dname,e.sal+e.comm sum from emp e left join dept d on d.deptno = e.deptno GROUP BY e.empno
15、列出从事同一种工作但属于不同部门的员工的一种组合。
select e1.ename,e1.job,e1.deptno ,e2.ename,e2.job,e2.deptno from emp e1 ,emp e2 where e1.job = e2.job and e1.deptno != e2.deptno limit 0,1
16、列出所有部门的详细信息和部门人数。
select d.* ,count(e.deptno) from dept d left join emp e on d.deptno = e.deptno group by e.deptno
select d.* ,( select count(*) from emp e where d.deptno = e.deptno) p from dept d
17、列出各种工作的最低工资。
select e.job,min(e.sal+e.comm) from emp e group by e.job
18、列出各个部门的MANAGER（经理）的最低薪金（job为MANAGER）。
select e.deptno,e.job,min(e.sal+e.comm) from emp e where e.job = "MANAGER" group by e.deptno;
19、列出所有员工的年工资，按年薪从低到高排序。
select e.ename ,(e.sal+e.comm) y from emp e order by y
