练习一:
提示:
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)