环境搭建（MySQL：8.0.25）：创建并填充了四个表：dept, emp, salgrade 和 tbyear。
- 创建
dept表
CREATE TABLE dept
(
deptno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);
INSERT INTO dept (deptno, dname, loc)
VALUES (10, '教研部', '北京'),
(20, '学工部', '上海'),
(30, '销售部', '深圳'),
(40, '财务部', '广州'),
(50, '董事会', '太原');
- 创建
emp表
CREATE TABLE emp
(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(10, 2),
comm DECIMAL(10, 2),
deptno INT,
FOREIGN KEY (deptno) REFERENCES dept (deptno)
);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, NULL, 20),
(1002, '龚绣丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 20),
(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),
(1004, '刘备', '经理', 1009, '2001-04-10', 29750.00, NULL, 30),
(1005, '谢迅', '销售员', 1009, '2001-09-28', 12500.00, 14000.00, 10),
(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, NULL, 30),
(1007, '张飞', '文员', 1013, '2001-09-11', 24500.00, NULL, 10),
(1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, NULL, 20),
(1009, '张无忌', '董事长', NULL, '2001-11-17', 50000.00, NULL, 50),
(1010, '专一笑', '文员', 1006, '2001-09-08', 13000.00, 0.00, 30),
(1011, '周杰', '文员', 1006, '2007-05-23', 9500.00, NULL, 30),
(1012, '程普', '文员', 1004, '2007-12-03', 30000.00, NULL, 20),
(1013, '庞统', '分析师', 1002, '2001-01-23', 13000.00, NULL, 20),
(1014, '貂蝉', '文员', 1001, '2002-01-23', 8000.00, 50000.00, 10),
(1015, '张三', '保洁员', 1001, '2013-05-01', 3000.00, NULL, 50);
- 创建
salgrade表
CREATE TABLE salgrade (
grade INT PRIMARY KEY,
losal DECIMAL(10, 2),
hisal DECIMAL(10, 2)
);
INSERT INTO salgrade (grade, losal, hisal) VALUES
(1, 7000.00, 12000.00),
(2, 12000.00, 14000.00),
(3, 14000.00, 20000.00),
(4, 20000.00, 30000.00),
(5, NULL, 90000.00);
- 创建
tbyear表
CREATE TABLE tbyear
(
year INT PRIMARY KEY,
zz INT
);
INSERT INTO tbyear (year, zz)
VALUES (2010, 100),
(2011, 150),
(2012, 250),
(2013, 800),
(2014, 1000);
1、查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数
SELECT d.deptno,
d.dname,
d.loc,
COUNT(e.empno) AS emp_count
FROM dept d
JOIN
emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname, d.loc
HAVING COUNT(e.empno) >= 1;
2、列出薪金比关羽高的所有员工
SELECT ep2.ename
FROM emp ep1
INNER JOIN emp ep2 ON ep1.deptno = ep2.deptno
WHERE ep1.ename = '关羽'
AND ep2.sal > ep1.sal
AND ep2.ename != '关羽';
3、列出所有员工的姓名及其直接上级的姓名
SELECT ep1.ename AS Employee, ep2.ename AS Manager
FROM emp ep1
INNER JOIN emp ep2 ON ep1.mgr = ep2.empno;
4、列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称
SELECT ep1.empno AS EmployeeNumber, ep1.ename AS EmployeeName, d.dname AS DepartmentName
FROM emp ep1
INNER JOIN emp ep2 ON ep1.mgr = ep2.empno
AND ep1.hiredate < ep2.hiredate
INNER JOIN dept d ON ep1.deptno = d.deptno;
5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT d.dname,
e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
ORDER BY d.dname;
6、列出所有文员的姓名及其部门名称，部门的人数
-- 选择需要的列：文员的姓名（e.ename）、部门名称（d.dname）和部门员工数量（dept_count.dept_emp_count）
SELECT e.ename, d.dname, dept_count.dept_emp_count
FROM emp e
-- 将 emp 表与 dept 表连接，连接条件是 emp 表的 deptno 与 dept 表的 deptno 相等
INNER JOIN dept d ON e.deptno = d.deptno
-- 将 emp 表与计算部门员工数量的子查询连接，连接条件是 emp 表的 deptno 与子查询的 deptno 相等
INNER JOIN (
-- 选择 deptno 和计算每个部门的员工数量
SELECT deptno, COUNT(*) AS dept_emp_count
FROM emp
-- 按 deptno 分组，计算每个部门的员工数量
GROUP BY deptno) AS dept_count ON e.deptno = dept_count.deptno
-- 只选择职位为 '文员' 的记录
WHERE e.job = '文员';
SELECT e.ename, d.dname, dept_count.dept_emp_count
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
INNER JOIN (SELECT deptno, COUNT(*) AS dept_emp_count
FROM emp
GROUP BY deptno) AS dept_count ON e.deptno = dept_count.deptno
WHERE e.job = '文员';
7、列出最低薪金大于15000的各种工作及从事此工作的员工人数
SELECT ep.job, COUNT(ep.sal) AS EmployeeCount
FROM emp ep
WHERE ep.sal > 15000
GROUP BY ep.job;
8、列出在销售部工作的员工的姓名，假定不知道销售部的部门编号
select ep.ename
from dept de
left join emp ep on de.deptno = ep.deptno
where de.dname = '销售部';
9、列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级
SELECT ep.*,
dp.dname AS '部门名称',
mgr.ename AS '上级领导',
sg.grade AS '工资等级'
FROM emp ep
LEFT JOIN
emp mgr ON ep.mgr = mgr.empno
LEFT JOIN
dept dp ON ep.deptno = dp.deptno
LEFT JOIN
salgrade sg ON ep.sal BETWEEN sg.losal AND sg.hisal
WHERE ep.sal > (SELECT AVG(sal) FROM emp);
10、列出与庞统从事相同工作的所有员工及部门名称
select ep2.ename, dp.dname
from emp ep1
left join emp ep2 on ep1.job = ep2.job
left join dept dp on dp.deptno = ep1.deptno
where ep1.ename = '庞统'
and ep2.ename != '庞统';
11、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
WITH max_sal30 AS (SELECT MAX(sal) AS max_sal
FROM emp
WHERE deptno = 30)
SELECT ep.ename AS EmployeeName,
ep.sal AS Salary,
dp.dname AS DepartmentName
FROM emp ep
LEFT JOIN
dept dp ON ep.deptno = dp.deptno
WHERE ep.sal > (SELECT max_sal FROM max_sal30);
12、列出每个部门的员工数量、平均工资
SELECT d.dname AS DepartmentName,
COUNT(e.empno) AS EmployeeCount,
AVG(e.sal) AS AverageSalary
FROM dept d
LEFT JOIN
emp e ON d.deptno = e.deptno
GROUP BY d.dname;
13、查出年份、利润、年度增长比
-- 选择年份、利润以及年度增长比
SELECT
t1.year, -- 当前年份
t1.zz AS Profit, -- 当前年份的利润
-- 计算年度增长比：
-- 当前年的利润减去前一年的利润，然后除以前一年的利润，乘以100得到百分比增长率，并保留两位小数
ROUND(((t1.zz - COALESCE(t2.zz, 0)) / COALESCE(t2.zz, 1)) * 100, 2) AS GrowthRate
FROM
tbyear t1 -- 从tbyear表中选择数据，并将其别名为t1
LEFT JOIN
tbyear t2 ON t1.year = t2.year + 1 -- 将tbyear表与自身左连接，通过年份获取前一年的利润数据
ORDER BY
t1.year; -- 按年份排序结果
SELECT t1.year,
t1.zz AS Profit,
ROUND(((t1.zz - COALESCE(t2.zz, 0)) / COALESCE(t2.zz, 1)) * 100, 2) AS GrowthRate
FROM tbyear t1
LEFT JOIN
tbyear t2 ON t1.year = t2.year + 1
ORDER BY t1.year;
2662
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
