一起重新开始学大数据-MySQL篇-Day35-练习题

本文主要是介绍一起重新开始学大数据-MySQL篇-Day35-练习题，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一起重新开始学大数据-MySQL篇-Day35-练习题

在这里插入图片描述

练习：将下列数据插入mysql表,完成所给查询需求

创建员工表

DROP TABLE IF EXISTS  `emp`;
CREATE TABLE `emp` (`EMPNO` int(4) NOT NULL,`ENAME` varchar(10) DEFAULT NULL,`JOB` varchar(9) DEFAULT NULL,`MGR` varchar(10) DEFAULT NULL,`HIREDATE` date DEFAULT NULL,`SAL` int(7) DEFAULT NULL,`COMM` int(7) DEFAULT NULL,`DEPTNO` int(2) DEFAULT NULL,PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

字段中文名字依次是：工号，姓名，工作岗位，部门经理，受雇日期，薪金，奖金，部门编号

员工表数据

insert into `emp`(`EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM`,`DEPTNO`) values
('7369','SMITH','CLERK','7902','1980-12-17','800',null,'20'),
('7499','ALLEN','SALESMAN','7698','1981-02-20','1600','300','30'),
('7521','WARD','SALESMAN','7698','1981-02-22','1250','500','30'),
('7566','JONES','MANAGER','7839','1981-04-02','2975',null,'20'),
('7654','MARTIN','SALESMAN','7698','1981-09-28','1250','1400','30'),
('7698','BLAKE','MANAGER','7839','1981-05-01','2850',null,'30'),
('7782','CLARK','MANAGER','7839','1981-06-09','2450',null,'10'),
('7788','SCOTT','ANALYST','7566','1987-04-19','3000',null,'20'),
('7839','KING','PRESIDENT',null,'1981-11-17','5000',null,'10'),
('7844','TURNER','SALESMAN','7698','1981-09-08','1500','0','30'),
('7876','ADAMS','CLERK','7788','1987-05-23','1100',null,'20'),
('7900','JAMES','CLERK','7698','1981-12-03','950',null,'30'),
('7902','FORD','ANALYST','7566','1981-12-03','3000',null,'20'),
('7934','MILLER','CLERK','7782','1982-01-23','1300',null,'10');

创建部门表

DROP TABLE IF EXISTS  `dept`;
CREATE TABLE `dept` (`DEPTNO` int(2) NOT NULL,`DNAME` varchar(14) DEFAULT NULL,`LOC` varchar(13) DEFAULT NULL,PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

部门编号、部门名字、部门地址

添加数据

insert into `dept`(`DEPTNO`,`DNAME`,`LOC`) values
('10','ACCOUNTING','NEW YORK'),
('20','RESEARCH','DALLAS'),
('30','SALES','CHICAGO'),
('40','OPERATIONS','BOSTON');

然后查询（sql和运行结果截图）：

1.列出至少有4个员工的所有部门编号和名称。

SELECT s.DEPTNO,s.ENAME  from(SELECT emp.DEPTNO,emp.ENAME,COUNT(DEPTNO) id from emp GROUP BY DEPTNO HAVING id>=4)AS s;

在这里插入图片描述

2.列出薪金比“SMITH”多的所有员工。

SELECT s.ENAME FROM(SELECT emp.* FROM emp HAVING emp.SAL>(SELECT SAL FROM emp WHERE ENAME='SMITH'))AS s;

在这里插入图片描述

3．列出所有员工的姓名及其直接上级的姓名。

 SELECT emp1.ENAME,emp2.ENAME from emp as emp1 LEFT JOIN emp AS emp2 ON emp1.MGR=emp2.EMPNO;

在这里插入图片描述

4．列出受雇日期早于其直接上级的所有员工。

SELECT emp1.ENAME from emp as emp1 LEFT JOIN emp AS emp2 ON emp1.MGR=emp2.EMPNO WHERE emp1.HIREDATE<emp2.HIREDATE;

在这里插入图片描述

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

SELECT dept.DNAME,emp.* FROM dept LEFT JOIN emp ON emp.DEPTNO=dept.DEPTNO;

在这里插入图片描述

6．列出所有“CLERK”（办事员）的姓名及其部门名称。

SELECT emp.ENAME,dept.DNAME FROM emp LEFT JOIN dept ON emp.DEPTNO=dept.DEPTNO WHERE emp.JOB='CLERK'

在这里插入图片描述

7．列出最低薪金大于1500的各种工作。

SELECT JOB,MIN(SAL)AS M FROM emp GROUP BY JOB HAVING M>1500;

在这里插入图片描述

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号

SELECT  ENAME FROM emp WHERE deptno=(SELECT deptno FROM dept where dname='SALES');

在这里插入图片描述

9．列出薪金高于公司平均薪金的所有员工。

SELECT  ENAME FROM emp WHERE SAL>(SELECT AVG(SAL) FROM emp)

在这里插入图片描述

10．列出与“SCOTT”从事相同工作的所有员工。

SELECT ENAME FROM emp WHERE job=(SELECT JOB FROM emp WHERE ENAME='SCOTT')

在这里插入图片描述

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

SELECT emp.ENAME,emp.SAL from emp right JOIN (SELECT emp.SAL FROM  emp LEFT JOIN dept ON emp.DEPTNO=dept.DEPTNO WHERE emp.DEPTNO=30)AS d ON emp.SAL=d.SAL GROUP BY ENAME;

在这里插入图片描述

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

SELECT ENAME,SAL from emp WHERE SAL>(SELECT MAX(emp.SAL) FROM  emp LEFT JOIN dept ON emp.DEPTNO=dept.DEPTNO WHERE emp.DEPTNO=30);

在这里插入图片描述

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。

SELECTCOUNT(ENAME) AS 员工数量,AVG(SAL + CASEWHEN COMM IS NULL THEN0ELSECOMMEND) AS 平均工资,AVG(CURRENT_DATE() - HIREDATE) AS 平均服务期限
FROMemp
GROUP BYDEPTNO;

在这里插入图片描述

14．列出所有员工的姓名、部门名称和工资。

-- 14．列出所有员工的姓名、部门名称和工资。 
-- SELECT SAL+CASE WHEN COMM IS null THEN 0 ELSE COMM END FROM emp;-- 工资
SELECTemp.ENAME AS 姓名,dept.DNAME AS 部门名称,(emp.SAL + CASEWHEN emp.COMM IS NULL THEN0ELSEemp.COMMEND) AS 工资
FROMemp
LEFT JOIN dept ON emp.DEPTNO = dept.DEPTNO;

在这里插入图片描述

15．列出所有部门的详细信息和部门人数。

SELECT dept.*,COUNT(emp.DEPTNO)AS 部门人数 FROM dept LEFT JOIN emp ON dept.DEPTNO=emp.DEPTNO GROUP BY DEPTNO;

在这里插入图片描述

16．列出各种工作的最低工资。

SELECT JOB,MIN(SAL+CASE WHEN COMM IS null THEN 0 ELSE COMM END)AS 最低工资 FROM emp GROUP BY JOB;

在这里插入图片描述

17．列出各个部门的MANAGER（经理）的最低薪金。

SELECTemp1.DEPTNO,min(emp1.SAL)
FROMemp AS emp1
LEFT JOIN emp AS emp2 ON emp2.MGR = emp1.EMPNO
GROUP BYemp1.DEPTNO;

在这里插入图片描述

18．列出所有员工的年工资,按年薪从低到高排序。

SELECTENAME,((SAL + CASEWHEN COMM IS NULL THEN0ELSECOMMEND) * 12) AS 年薪
FROMemp
ORDER BY年薪;

在这里插入图片描述

思考：列出每个部门薪水前两名最高的人员名称以及薪水

SELECTDEPTNO,ENAME,SAL
FROMemp AS emp1
WHERE2 > (SELECTCOUNT(*)FROMemp AS emp2WHEREemp1.DEPTNO = emp2.DEPTNOAND emp1.sal < emp2.sal)
ORDER BYDEPTNO;

在这里插入图片描述

函数小练习求每七天的平均数

原始数据：

insert into test(datetime,sum) values('2018-6-1','10');
insert into test(datetime,sum) values('2018-6-2','11');
insert into test(datetime,sum) values('2018-6-3','11');
insert into test(datetime,sum) values('2018-6-4','12');
insert into test(datetime,sum) values('2018-6-5','14');
insert into test(datetime,sum) values('2018-6-6','15');
insert into test(datetime,sum) values('2018-6-7','13');
insert into test(datetime,sum) values('2018-6-8','37');
insert into test(datetime,sum) values('2018-6-9','18');
insert into test(datetime,sum) values('2018-6-10','19');
insert into test(datetime,sum) values('2018-6-11','10');
insert into test(datetime,sum) values('2018-6-12','11');
insert into test(datetime,sum) values('2018-6-13','11');
insert into test(datetime,sum) values('2018-6-14','12');

最终需要的结果：

2018-06-01~2018-06-07 12
2018-06-08~2018-06-14 16

select CONCAT_WS('~',
DATE_ADD('2018-06-01',INTERVAL FLOOR(DATEDIFF(datetime,'2018-06-01')/7)*7 day),
DATE_ADD('2018-06-01',INTERVAL FLOOR(DATEDIFF(datetime,'2018-06-01')/7)*7+6 day))as time,
FLOOR(avg(sum)) as avgsum
from test GROUP BY time;