本文主要是介绍一起重新开始学大数据-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;
|
|
|
|
|
上一章-MySQL篇-Day34-日期函数、计算、排序分组筛选、连表联查等
下一章-MySQL篇-Day36-case值替换,备份表,稍微了解一下视图,事务
|
|
|
|
|
听说长按大拇指👍会发生神奇的事情呢!好像是下面的画面,听说点过的人🧑一个月内就找到了对象的💑💑💑,并且还中了大奖💴$$$,考试直接拿满分💯,颜值突然就提升了😎,虽然对你好像也不需要,是吧,吴彦祖🤵! |
这篇关于一起重新开始学大数据-MySQL篇-Day35-练习题的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!