create database bdqn;
use bdqn;
drop table if exists emp;
drop table if exists dept;
drop table if exists salgrade;
-- 部门表
CREATE TABLE DEPT(
DEPTNO INT PRIMARY KEY, -- 部门编号
DNAME VARCHAR(14), -- 部门名称
LOC VARCHAR(13) -- 部门地址
);
INSERT INTO DEPT VALUES (10,"ACCOUNTING","NEW YORK");
INSERT INTO DEPT VALUES (20,"RESEARCH","DALLAS");
INSERT INTO DEPT VALUES (30,"SALES","CHICAGO");
INSERT INTO DEPT VALUES (40,"OPERATIONS","BOSTON");
-- 员工表
CREATE TABLE EMP
(
EMPNO INT PRIMARY KEY, -- 员工编号
ENAME VARCHAR(10), -- 员工名称
JOB VARCHAR(9), -- 工作
MGR DOUBLE, -- 直属领导编号
HIREDATE DATE, -- 入职时间
SAL DOUBLE, -- 工资
COMM DOUBLE, -- 奖金
DEPTNO INT, -- 部门号
FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));
INSERT INTO EMP VALUES
(7369,"SMITH","CLERK",7902,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES
(7499,"ALLEN","SALESMAN",7698,"1981-02-20",1600,300,30);
INSERT INTO EMP VALUES
(7521,"WARD","SALESMAN",7698,"1981-02-22",1250,500,30);
INSERT INTO EMP VALUES
(7566,"JONES","MANAGER",7839,"1981-04-02",2975,NULL,20);
INSERT INTO EMP VALUES
(7654,"MARTIN","SALESMAN",7698,"1981-09-28",1250,1400,30);
INSERT INTO EMP VALUES
(7698,"BLAKE","MANAGER",7839,"1981-05-01",2850,NULL,30);
INSERT INTO EMP VALUES
(7782,"CLARK","MANAGER",7839,"1981-06-09",2450,NULL,10);
INSERT INTO EMP VALUES
(7788,"SCOTT","ANALYST",7566,"1987-07-13",3000,NULL,20);
INSERT INTO EMP VALUES
(7839,"KING","PRESIDENT",NULL,"1981-11-17",5000,NULL,10);
INSERT INTO EMP VALUES
(7844,"TURNER","SALESMAN",7698,"1981-09-08",1500,0,30);
INSERT INTO EMP VALUES
(7876,"ADAMS","CLERK",7788,"1987-07-13",1100,NULL,20);
INSERT INTO EMP VALUES
(7900,"JAMES","CLERK",7698,"1981-12-03",950,NULL,30);
INSERT INTO EMP VALUES
(7902,"FORD","ANALYST",7566,"1981-12-03",3000,NULL,20);
INSERT INTO EMP VALUES
(7934,"MILLER","CLERK",7782,"1982-01-23",1300,NULL,10);
-- 薪资表
CREATE TABLE SALGRADE
( GRADE INT, -- 工资等级
LOSAL DOUBLE, -- 最低工资
HISAL DOUBLE -- 最高工资
);
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
dept部门表(deptno部门编号/dname部门名称/loc地点)工资=薪金＋佣金
1．列出至少有一个员工的所有部门。
select DEPT.DNAME from DEPT JOIN EMP ON DEPT.DEPTNO=EMP.DEPTNO group by DEPT.DNAME;
2．列出工资比"SMITH”多的所有员工。
select ENAME,sum(IFNULL(SAL,0) + IFNULL(COMM,0)) as gongzi from EMP GROUP by ENAME
having sum(IFNULL(SAL,0) + IFNULL(COMM,0)) > (select sum(IFNULL(SAL,0) + IFNULL(COMM,0))
as gongzi from EMP where EMP.ENAME='SMITH' GROUP by ENAME);
3．列出所有员工的姓名及其直接上级的姓名。
select a.ENAME,b.ENAME from EMP as a join EMP as b on a.MGR=b.EMPNO;
4．列出受雇日期早于其直接上级的所有员工。
select a.ENAME,b.ENAME from EMP as a join EMP as b on a.MGR=b.EMPNO where b.HIREDATE>a.HIREDATE;
5．列出部门名称和这些部门的员工.信息，同时列出那些没有员工的部门。
select DEPT.DNAME,EMP.ENAME from DEPT join EMP on DEPT.DEPTNO=EMP.DEPTNO
UNION
select DEPT.DNAME,EMP.ENAME from DEPT left join EMP on DEPT.DEPTNO=EMP.DEPTNO;
6．列出所有“CLERK”(办事员）的姓名及其部门名称。
select EMP.ENAME,DEPT.DNAME from DEPT join EMP on DEPT.DEPTNO=EMP.DEPTNO where EMP.JOB='CLERK';
7．列出最低薪金大于1500的各种工作。
select JOB,SAL from EMP where SAL>1500;
8．列出在部门"SALES”（销售部）工.作的员工.的姓名，假定不知道销售部的部门编号。
select DEPT.DNAME,EMP.ENAME from DEPT join EMP on DEPT.DEPTNO=EMP.DEPTNO
where DEPT.DNAME='SALES';
9.列出薪金高于公司平均薪金的所有员T.。
select ENAME,SAL from EMP where SAL > (select avg(SAL) from EMP);
10．列出与“SCOTT”从事相同T.作的所有员工。
select EMP.ENAME,b.job from EMP join (select JOB from EMP where ENAME='SCOTT') as b on EMP.JOB=b.JOB;=b.JOB;
11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select a.ENAME,b.SAL from EMP as a join (select ENAME,SAL FROM EMP WHERE DEPTNO=30) as b
on a.SAL=b.SAL where a.DEPTNO != 30;
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select distinct a.ENAME,a.SAL from EMP as a,(select ENAME,SAL FROM EMP WHERE DEPTNO=30) as b
where here a.SAL>b.SAL;
13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
14．列出所有员工的姓名、部门名称和工资。
15．列出所有部门的详细信息和部门人数。
16．列出各种工作的最低工资。
17．列出各个部门的MANAGER（经理)的最低薪金。
18．列出所有员工的年工资,按年薪从低到高排序。
参考：https://blog.csdn.net/weixin_73961973/article/details/127203034
