练习一： 提示：emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate

练习一：

提示：

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 demo2USE demo2-- 员工表CREATE TABLE emp(empnoint primary key,-- 员工号enameVARCHAR(10),-- 员工姓名jobVARCHAR(20),-- 工作mgrint,-- 上级编号hiredateVARCHAR(30),-- 受雇日期sal int,-- 薪金commint,-- 佣金deptnoint-- 所属部门编号) -- 部门表CREATE TABLE dept(deptnoVARCHAR(30),-- 部门编号dnameVARCHAR(30),-- 部门名称locVARCHAR(30)-- 地点) INSERT INTO emp VALUES(1001,'张三','拧螺丝',21,'-09-02',1500,200,2);INSERT INTO emp VALUES(1002,'张思','包装',201,'-09-02',1400,300,20);INSERT INTO emp VALUES(1003,'李四','贴标签',61,'-09-02',1600,100,6);INSERT INTO emp VALUES(1004,'SHDH','包装',201,'-09-02',1200,300,20);INSERT INTO emp VALUES(1005,'HAD','贴胶布',91,'-09-02',1500,200,9);INSERT INTO emp VALUES(1006,'王五','贴标签',61,'-09-02',1500,200,6);INSERT INTO emp VALUES(1007,'赵六','运货',101,'-09-02',1700,null,10);INSERT INTO emp VALUES(1008,'钱德','运货',101,'-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 emp2、在emp表中查询出薪水大于1500的记录，并且按照薪水的降序排列。SELECT * FROM emp WHERE sal>1500 ORDER BY sal DESC3、在emp表中查询出comm字段为空值的记录。SELECT * FROM emp WHERE comm is NULL4、查询出emp表中含有几个部门的记录。（用DISTINCT去除重复记录）SELECT count(DISTINCT deptno) as 部门 FROM emp5、在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 emp8、查询emp表中出每个部门的部门代码、薪水之和、平均薪水。SELECT deptno as 部门代码,AVG(sal+IFNULL(comm,0))AS 平均薪水,SUM(sal+IFNULL(comm,0))AS 薪水之和 FROM emp GROUP BY deptno

练习二

CREATE DATABASE demo3USE demo3-- 员工表CREATE TABLE emp(empno int primary key,-- 员工号ename VARCHAR(10),-- 员工姓名jobVARCHAR(20),-- 工作mgrint,-- 上级编号hiredate VARCHAR(30),-- 受雇日期sal int,-- 薪金comm int,-- 佣金deptno int-- 所属部门编号) -- 部门表CREATE TABLE dept(deptno VARCHAR(30),-- 部门编号dname VARCHAR(30),-- 部门名称locVARCHAR(30)-- 地点) INSERT INTO emp VALUES(1001,'SMITH','CLERK',101,'-09-02',2500,200,2);INSERT INTO emp VALUES(1002,'张思','CLERK',101,'-09-02',2400,300,2);INSERT INTO emp VALUES(1003,'李四','CLERK',101,'-09-02',2600,100,2);INSERT INTO emp VALUES(1004,'SHDH','销售',102,'-09-02',3200,300,6);INSERT INTO emp VALUES(1005,'HAD','销售',102,'-09-02',3500,200,6);INSERT INTO emp VALUES(1006,'SCOTT','销售',102,'-09-02',3500,200,6);INSERT INTO emp VALUES(101,'赵六','MANAGER',102,'-03-22',6700,600,2);INSERT INTO emp VALUES(102,'钱德','MANAGER',102,'-10-02',7600,400,6)INSERT INTO dept VALUES(6,'SALES','河南郑州');INSERT INTO dept VALUES(2,'售后部','河南郑州');SELECT * FROM dept;SELECT * FROM empDELETE 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)

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