| 一起重新开始学大数据-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．列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT
COUNT(ENAME) AS 员工数量,
AVG(
SAL + CASE
WHEN COMM IS NULL THEN
0
ELSE
COMM
END
) AS 平均工资,
AVG(CURRENT_DATE() - HIREDATE) AS 平均服务期限
FROM
emp
GROUP BY
DEPTNO;
14．列出所有员工的姓名、部门名称和工资。
-- 14．列出所有员工的姓名、部门名称和工资。
-- SELECT SAL+CASE WHEN COMM IS null THEN 0 ELSE COMM END FROM emp;-- 工资
SELECT
emp.ENAME AS 姓名,
dept.DNAME AS 部门名称,
(
emp.SAL + CASE
WHEN emp.COMM IS NULL THEN
0
ELSE
emp.COMM
END
) AS 工资
FROM
emp
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（经理）的最低薪金。
SELECT
emp1.DEPTNO,
min(emp1.SAL)
FROM
emp AS emp1
LEFT JOIN emp AS emp2 ON emp2.MGR = emp1.EMPNO
GROUP BY
emp1.DEPTNO;
18．列出所有员工的年工资,按年薪从低到高排序。
SELECT
ENAME,
(
(
SAL + CASE
WHEN COMM IS NULL THEN
0
ELSE
COMM
END
) * 12
) AS 年薪
FROM
emp
ORDER BY
年薪;
思考： 列出每个部门薪水前两名最高的人员名称以及薪水
SELECT
DEPTNO,
ENAME,
SAL
FROM
emp AS emp1
WHERE
2 > (
SELECT
COUNT(*)
FROM
emp AS emp2
WHERE
emp1.DEPTNO = emp2.DEPTNO
AND emp1.sal < emp2.sal
)
ORDER BY
DEPTNO;
| 函数小练习 求每七天的平均数 |
原始数据：
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;
|
|
|
|
|
上一章-MySQL篇-Day34-日期函数、计算、排序分组筛选、连表联查等
下一章-MySQL篇-Day36-case值替换，备份表，稍微了解一下视图，事务
|
|
|
|
|
| 听说长按大拇指👍会发生神奇的事情呢！好像是下面的画面，听说点过的人🧑一个月内就找到了对象的💑💑💑，并且还中了大奖💴$$$，考试直接拿满分💯，颜值突然就提升了😎，虽然对你好像也不需要，是吧，吴彦祖🤵！ |
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
