mysql之join练习

最新推荐文章于 2023-05-15 16:02:01 发布

山海自有归期

最新推荐文章于 2023-05-15 16:02:01 发布

阅读量344

点赞数 1

分类专栏：数据库文章标签： java 开发语言后端

本文链接：https://blog.csdn.net/weixin_50691399/article/details/121525429

版权

数据库专栏收录该内容

34 篇文章 0 订阅

订阅专栏

一：插入数据

二：题目

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

一：插入数据

注意：要是发现代码有错误可能是复制粘贴到这出现了错误。

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


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');

二：题目

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

select emp.DEPTNO, DNAME,LOC,COUNT(*) AS COUNT from emp
left join dept
on emp.DEPTNO=dept.DEPTNO
GROUP BY emp.DEPTNO
HAVING COUNT > 4;

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

SELECT * FROM emp WHERE SAL >(select SAL from emp where ENAME = "SMITH");

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

SELECT A.ENAME,A.MGR,B.ENAME AS MGRNAME FROM emp AS A
LEFT JOIN emp AS B
ON A.MGR=B.EMPNO;

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

SELECT * FROM (SELECT A.ENAME,A.MGR,B.ENAME AS MGRNAME,A.HIREDATE AS ETIME,B.HIREDATE AS MTIME  FROM emp AS A
LEFT JOIN emp AS B
ON A.MGR=B.EMPNO) AS C WHERE ETIME<MTIME;

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

select dname,emp.* from emp
right join dept
on emp.DEPTNO=dept.DEPTNO;

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

SELECT ENAME,DNAME FROM (SELECT * FROM emp WHERE JOB ='CLERK') AS A
LEFT JOIN dept
ON A.DEPTNO=dept.DEPTNO;

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

SELECT JOB,SAL FROM  emp WHERE SAL >1500;

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

select * from emp where deptno =( select deptno from dept where dname = 'SALES');

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

SELECT * FROM emp HAVING SAL> (SELECT FLOOR(SUM(SAL)/COUNT(*)) FROM emp);

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

SELECT * FROM emp WHERE JOB =(SELECT JOB FROM emp WHERE ENAME='SCOTT') AND ENAME<>'SCOTT';

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

select ename,sal from emp where sal in (select sal from emp where deptno =30) and deptno <> 30;

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

select ename,sal from emp where sal>(select max(sal) from emp where deptno =30);

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

select  count(*),avg(sal),avg(datediff("2021-11-08",hiredate)) from emp group by deptno;

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

select ename,if(comm is null, sal,sal+comm)as sal,dname from emp
inner join dept
on emp.deptno=dept.deptno;

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

select dept.*,e.count from dept
inner join (select count(*)as count,deptno from emp group by deptno) as e
on dept.deptno=e.deptno;

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

SELECT JOB,MIN(IF(COMM IS NULL, SAL,SAL+COMM))AS MINSAL FROM emp GROUP BY JOB;

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

select * from (select * from emp where job='MANAGER') as e1 where 0=(
select count(*) from (select * from emp where job='MANAGER') as e2 where e1.deptno=e2.deptno and e1.sal>e2.sal );

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

SELECT ENAME, IF(COMM IS NULL,SAL,SAL+COMM)*12 AS SAL FROM emp ORDER BY SAL;

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

SELECT * FROM (SELECT ENAME,DEPTNO, IF(COMM IS NULL,SAL,SAL+COMM)AS SAL FROM emp ) AS S1 WHERE 2>(
SELECT COUNT(*) FROM (SELECT ENAME,DEPTNO, IF(COMM IS NULL,SAL,SAL+COMM)AS SAL FROM emp ) AS S2 WHERE S1.DEPTNO=S2.DEPTNO AND S1.SAL < S2.SAL )ORDER BY DEPTNO;