练习一： 提示：emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno所属部门编号) dept部门

练习一：
    提示：
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno所属部门编号) 
dept部门表(deptno部门编号/dname部门名称/loc地点) 
工资 ＝ 薪金 ＋ 佣金
在Emp 表中完成如下练习：
1、在emp表中查询出所有记录的姓名、部门编号、薪水，并且列名要显示为中文。
2、在emp表中查询出薪水大于1500的记录，并且按照薪水的降序排列。
3、在emp表中查询出comm字段为空值的记录。
4、查询出emp表中含有几个部门的记录。（用DISTINCT去除重复记录）
5、在emp表中查询出部门编号为10或20的记录（要求使用IN关键字）
6、在emp表中查询出姓名的第二个字母为A的记录。
7、查询出emp表中总共有多少条记录。
8、查询emp表中出每个部门的部门代码、薪水之和、平均薪水。

练习二：
1．列出至少有一个员工的所有部门。 
2．列出薪金比“SMITH”多的所有员工。 
3．列出所有员工的姓名及其直接上级的姓名。 
4．列出受雇日期早于其直接上级的所有员工。 
5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。 
6．列出所有“CLERK”（办事员）的姓名及其部门名称。 
7．列出最低薪金大于1500的各种工作。 
8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 
9．列出薪金高于公司平均薪金的所有员工。 
10．列出与“SCOTT”从事相同工作的所有员工。  
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 
13．列出在每个部门工作的员工数量、平均工资和平均服务期限。 
14．列出所有员工的姓名、部门名称和工资。 
15．列出所有部门的详细信息和部门人数。 
16．列出各种工作的最低工资。 
17．列出薪金最低的MANAGER的基本信息。 
18．列出所有员工的年工资,按年薪从低到高排序。

练习一

CREATE DATABASE demo2
USE demo2

-- 员工表
CREATE TABLE emp(
empno	int primary key,			-- 员工号
ename	VARCHAR(10),					-- 员工姓名
job	VARCHAR(20),						-- 工作
mgr	int,										-- 上级编号
hiredate	VARCHAR(30),			-- 受雇日期
sal	 int,										-- 薪金
comm	int,									-- 佣金
deptno	int									-- 所属部门编号
) 
-- 部门表
CREATE TABLE dept(
deptno		VARCHAR(30),		-- 部门编号
dname		VARCHAR(30),			-- 部门名称
loc		VARCHAR(30)				-- 地点
) 

INSERT INTO emp VALUES(1001,'张三','拧螺丝',21,'2018-09-02',1500,200,2);
INSERT INTO emp VALUES(1002,'张思','包装',201,'2018-09-02',1400,300,20);
INSERT INTO emp VALUES(1003,'李四','贴标签',61,'2018-09-02',1600,100,6);
INSERT INTO emp VALUES(1004,'SHDH','包装',201,'2018-09-02',1200,300,20);
INSERT INTO emp VALUES(1005,'HAD','贴胶布',91,'2018-09-02',1500,200,9);
INSERT INTO emp VALUES(1006,'王五','贴标签',61,'2018-09-02',1500,200,6);
INSERT INTO emp VALUES(1007,'赵六','运货',101,'2018-09-02',1700,null,10);
INSERT INTO emp VALUES(1008,'钱德','运货',101,'2018-09-02',1600,400,10)

INSERT INTO dept VALUES(2,'拧工部','河南郑州');
INSERT INTO dept VALUES(6,'贴签部','河南郑州');
INSERT INTO dept VALUES(9,'贴胶布','河南郑州');
INSERT INTO dept VALUES(10,'运货部','河南郑州');
INSERT INTO dept VALUES(20,'包装部','河南郑州')

 DELETE  FROM emp
-- 工资 ＝ 薪金 ＋ 佣金
1、在emp表中查询出所有记录的姓名、部门编号、薪水，并且列名要显示为中文。
SELECT ename as 姓名,deptno as 部门编号,sal as 薪水 FROM emp
2、在emp表中查询出薪水大于1500的记录，并且按照薪水的降序排列。
SELECT * FROM emp WHERE sal>1500 ORDER BY sal DESC
3、在emp表中查询出comm字段为空值的记录。
SELECT * FROM emp WHERE comm is NULL
4、查询出emp表中含有几个部门的记录。（用DISTINCT去除重复记录）
SELECT count(DISTINCT deptno) as 部门 FROM emp
5、在emp表中查询出部门编号为10或20的记录（要求使用IN关键字）
SELECT * FROM emp WHERE deptno IN (10,20)
6、在emp表中查询出姓名的第二个字母为A的记录。
SELECT * FROM emp WHERE ename LIKE '_A%'
7、查询出emp表中总共有多少条记录。
SELECT count(*) as 总记录数 FROM emp
8、查询emp表中出每个部门的部门代码、薪水之和、平均薪水。
SELECT deptno as 部门代码,AVG(sal+IFNULL(comm,0))AS 平均薪水,SUM(sal+IFNULL(comm,0))AS 薪水之和 FROM emp GROUP BY deptno

练习二

CREATE DATABASE demo3
USE demo3

-- 员工表
CREATE TABLE emp(
empno int primary key,			-- 员工号
ename VARCHAR(10),				-- 员工姓名
job	VARCHAR(20),				-- 工作
mgr	int,						-- 上级编号
hiredate VARCHAR(30),			-- 受雇日期
sal int,						-- 薪金
comm int,						-- 佣金
deptno int						-- 所属部门编号
) 
-- 部门表
CREATE TABLE dept(
deptno VARCHAR(30),		-- 部门编号
dname VARCHAR(30),			-- 部门名称
loc	VARCHAR(30)				-- 地点
) 

INSERT INTO emp VALUES(1001,'SMITH','CLERK',101,'2018-09-02',2500,200,2);
INSERT INTO emp VALUES(1002,'张思','CLERK',101,'2018-09-02',2400,300,2);
INSERT INTO emp VALUES(1003,'李四','CLERK',101,'2018-09-02',2600,100,2);
INSERT INTO emp VALUES(1004,'SHDH','销售',102,'2018-09-02',3200,300,6);
INSERT INTO emp VALUES(1005,'HAD','销售',102,'2018-09-02',3500,200,6);
INSERT INTO emp VALUES(1006,'SCOTT','销售',102,'2018-09-02',3500,200,6);
INSERT INTO emp VALUES(101,'赵六','MANAGER',102,'2008-03-22',6700,600,2);
INSERT INTO emp VALUES(102,'钱德','MANAGER',102,'2008-10-02',7600,400,6)

INSERT INTO dept VALUES(6,'SALES','河南郑州');
INSERT INTO dept VALUES(2,'售后部','河南郑州');

SELECT * FROM dept;
SELECT * FROM emp

DELETE FROM emp;
DELETE FROM dept
-- 1．列出至少有一个员工的所有部门。 
SELECT deptno FROM dept WHERE deptno IN (SELECT DISTINCT deptno FROM emp)
-- 2．列出薪金比“SMITH”(史密斯)多的所有员工。 
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');  
-- 3．列出所有员工的姓名及其直接上级的姓名。 
SELECT a.ename as 员工的姓名,b.ename as 上级的姓名 FROM emp a LEFT JOIN emp b ON a.mgr = b.empno
-- 4．列出受雇日期早于其直接上级的所有员工。 
SELECT a.ename as 受雇日期早于其直接上级 FROM emp a LEFT JOIN emp b ON a.mgr = b.empno WHERE a.hiredate<b.hiredate
-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。 
select  dept.dname,emp.* from dept left join emp on dept.deptno = emp.deptno
-- 6．列出所有“CLERK”（办事员,职员）的姓名及其部门名称。 
SELECT e.ename as 姓名名称,d.dname as 部门名称 FROM 
-- 7．列出最低薪金大于1500的各种工作。 
SELECT  job 工作,min(sal) 最低薪金 FROM emp GROUP BY job HAVING min(sal) > 1500
-- 8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 
SELECT ename 姓名,dname 部门名称 FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno WHERE dname = 'SALES'
-- 9．列出薪金高于公司平均薪金的所有员工。 
SELECT * FROM emp WHERE sal > (SELECT avg(sal) FROM emp)
-- 10．列出与“SCOTT”从事相同工作的所有员工。
SELECT ename as 员工姓名 FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT') AND ename != 'SCOTT'
-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 
SELECT ename as 员工姓名,sal as 薪金 FROM emp WHERE sal > (SELECT max(sal) FROM emp WHERE deptno = 2)
-- 13．列出在每个部门工作的员工数量、平均工资和平均服务期限。 
SELECT dept.dname as 部门名称, count(empno) as 员工数量,avg(sal) 平均工资,avg(DATEDIFF(CURDATE(),hiredate)) 平均服务期限
FROM emp LEFT JOIN dept on emp.deptno = dept.deptno  GROUP BY emp.deptno
-- 14．列出所有员工的姓名、部门名称和工资。 
SELECT ename as 姓名,dname as 部门名称,sal as 工资 FROM emp left JOIN dept on emp.deptno = dept.deptno
-- 15．列出所有部门的详细信息和部门人数。 
SELECT dept.*,count(emp.deptno) as 部门人数 FROM emp RIGHT JOIN dept on emp.deptno = dept.deptno  GROUP BY dept.deptno
-- 16．列出各种工作的最低工资。 
SELECT min(sal) 最低工资,job 工作 FROM emp GROUP BY job
-- 17．列出薪金最低的MANAGER（经理）的基本信息。
SELECT * FROM emp WHERE sal = (SELECT min(sal) FROM emp WHERE job = 'MANAGER')
-- 18．列出所有员工的年工资,按年薪从低到高排序。
SELECT ename 姓名,sal 月薪,(sal * 12) 年薪 FROM emp ORDER BY (sal * 12)