SQL题集(二)

已于 2024-09-29 13:14:01 修改
阅读量985 收藏 4
点赞数 2
分类专栏： # SQL题集 Mysql 文章标签： mysql sql
于 2022-04-20 23:51:54 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Hudas/article/details/123561352
版权
本文通过一系列SQL查询案例，涵盖了比赛名单组合、优惠活动时间筛选、连续售出商品识别、员工信息修复、部门领导与普通员工薪水对比等实际业务场景，深入解析了SQL在数据处理中的应用。从基础的表连接、子查询到复杂的窗口函数和排名函数，每个例子都展示了SQL在解决实际问题时的强大能力。
摘要由CSDN通过智能技术生成

1.比赛名单整理

数据导入

DROP TABLE IF EXISTS competition_list; 
CREATE TABLE competition_list( 
team_name VARCHAR(8) 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO
competition_list (team_name) 
VALUE ('谁与争锋队') 
 ,('必胜队') 
 ,('乘风破浪队') 
 ,('群英汇队') 
 ,('梦之队');

competition_list表

team_name : 参赛队名

问题:每个参赛队伍都会和其他参赛队伍开展一次组队比赛，要求输出两两参赛队伍的所有比赛情况组合(两者分别为队伍A和队伍B)，并按照队名依次升序排列

解题思路

使用表的自连接，通过在队名之间以"<"的方式连接来确保队伍不会和自身匹配，并依次按照队名进行升序排序即可（涉及的知识点:多表连接）

SELECT a.team_name AS 队伍A, b.team_name AS 队伍B
FROM competition_list AS a
INNER JOIN competition_list AS b
ON a.team_name < b.team_name
ORDER BY a.team_name, b.team_name;

结果展示

2.参与优惠活动的商品

数据导入

DROP TABLE IF EXISTS product_promotion; 
CREATE TABLE product_promotion( 
commodity_id VARCHAR(8), 
start_date DATE, 
end_date DATE 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
product_promotion (commodity_id,start_date,end_date) 
VALUE ('a001','2021-01-01','2021-01-06') 
,('a002','2021-01-01','2021-01-10') 
,('a003','2021-01-02','2021-01-07') 
,('a004','2021-01-05','2021-01-07') 
,('b001','2021-01-05','2021-01-10') 
,('b002','2021-01-04','2021-01-06') 
,('c001','2021-01-06','2021-01-08') 
,('c002','2021-01-02','2021-01-04') 
,('c003','2021-01-08','2021-01-15');

product_promotion表

commodity_id : 商品ID   start_date : 商品优惠活动起始日期   end_date : 商品优惠活动结束日期

问题:查询在2021年1月7日至2021年1月9日期间参与优惠活动的商品

解题思路

假设2021年1月7日为时间a,2021年1月9日为时间b,每个优惠活动的开始时间为s，结束时间为e，则所有的可能序列为"sabe"、"saeb"、"asbe"、"aseb"

SELECT commodity_id 
FROM product_promotion 
WHERE (start_date<='2021-01-09' AND start_date>='2021-01-07') 
OR (end_date>='2021-01-07' AND end_date<='2021-01-09') 
OR (end_date>='2021-01-09' AND start_date<='2021-01-07') 
OR (start_date>='2021-01-07' AND end_date<='2021-01-09');

结果展示

3.连续售出的商品

数据导入

DROP TABLE IF EXISTS sold_succession; 
CREATE TABLE sold_succession( 
order_id INT, 
commodity_id VARCHAR(8) 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
sold_succession 
(order_id,commodity_id) 
VALUE (1,'c_001') 
 ,(2,'c_001') 
 ,(3,'c_002') 
 ,(4,'c_002') 
 ,(5,'c_002') 
 ,(6,'c_001') 
 ,(7,'c_003') 
 ,(8,'c_003') 
 ,(9,'c_003') 
 ,(10,'c_003') 
 ,(11,'c_001');

sold_succession表

order_id : 订单ID   commodity_id : 购买的商品ID

问题:找出连续下单大于或等于3次的商品ID

解题思路

使用窗口函数LAG(order_id,2),根据商品ID进行分组，并按照订单顺序默认升序延后两行展开

SELECT commodity_id,
       order_id,
       LAG(order_id,2) OVER (PARTITION BY commodity_id ORDER BY order_id) AS temp 
FROM sold_succession;

 输出结果

在上述输出结果中进行子查询操作，如果连续3次出现相同的commodity_id字段值，则应该在相同commodity_id字段值的第3行出现order_id = temp + 2的情况。如上图所示，commodity_id字段值为c_002, order_id = 5 和 temp = 3的那条记录符合这一条件。在子查询外部加入WHERE order_id = temp + 2进行判断并将结果去重即可。

涉及知识点: 子查询、窗口函数、DISTINCT

SELECT DISTINCT commodity_id 
FROM 
(SELECT commodity_id,
        order_id,
        LAG(order_id,2) OVER (PARTITION BY commodity_id ORDER BY order_id) AS temp 
 FROM sold_succession )AS a 
WHERE order_id = temp + 2;

结果展示

4.修复串列的记录

数据导入

drop table if exists examination_info;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, '算法', 'hard', 60, '2020-01-01 10:00:00'),
  (9002, '算法', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, 'SQL', 'medium', 70, '2020-01-01 10:00:00'),
  (9004, '算法,medium,80','', 0, '2020-01-01 10:00:00');

examination_info表(试卷信息表)

exam_id: 试卷ID  tag: 试卷类别  difficulty: 试卷难度  duration: 考试时长  release_time: 发布时间 

问题: 录题同学有一次手误将部分记录的试题类别tag、难度difficulty、时长duration同时录入到了tag字段，请帮忙找出这些录错了的记录，并拆分后按正确的列类型输出 

-- 解法1
SELECT exam_id,
       SUBSTRING_INDEX(tag,',',1)AS tag,
       SUBSTRING_INDEX(SUBSTRING_INDEX(tag,',',2),',',-1)AS difficulty,
       SUBSTRING_INDEX(tag,',',-1)AS duration
FROM examination_info
WHERE difficulty='';

-- 解法2
SELECT exam_id,
       SUBSTRING_INDEX(tag,',',1)AS tag,
       SUBSTRING_INDEX(SUBSTRING_INDEX(tag,',',2),',',-1)AS difficulty,
       SUBSTRING_INDEX(tag,',',-1)AS duration
FROM examination_info
WHERE tag LIKE'%,%';

-- 解法3
SELECT exam_id,
       SUBSTRING_INDEX(tag,',',1)AS tag,
       SUBSTRING_INDEX(SUBSTRING_INDEX(tag,',',2),',',-1)AS difficulty,
       SUBSTRING_INDEX(tag,',',-1)AS duration
FROM examination_info
WHERE tag REGEXP ',+';

结果展示:

5.对过长的昵称进行截取处理

数据导入 

drop table if exists user_info;
CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,`level`,job,register_time) VALUES
  (1001, '牛客1', 19, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号♂', 22, 0, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 25, 0, '算法', '2020-01-01 11:00:00'),
  (1005, '牛客5678901234号', 4000, 7, '算法', '2020-01-01 10:00:00'),
  (1006, '牛客67890123456789号', 25, 0, '算法', '2020-01-02 11:00:00');

user_info表(用户信息表)

uid: 用户ID   nick_name: 昵称   achievement: 成就值   level: 等级   job: 职业方向

register_time: 注册时间 

问题:有的用户的昵称特别长，在一些展示场景会导致样式混乱，因此需要将特别长的昵称转换一下再输出，请输出字符数大于10的用户信息，对于字符数大于13的用户输出前10个字符然后加上三个点号: "..."

-- 解法1
SELECT uid, IF(CHAR_LENGTH(nick_name) > 13,CONCAT(LEFT(nick_name,10),'...'),nick_name)AS nick_name
FROM user_info
WHERE CHAR_LENGTH(nick_name) > 10;

-- 解法2
SELECT uid,
       (CASE WHEN CHAR_LENGTH(nick_name) <= 13 THEN nick_name
             ELSE CONCAT(SUBSTRING(nick_name,1,10),'...')
        END)AS nick_name
FROM user_info
WHERE CHAR_LENGTH(nick_name) > 10;

结果展示:

解释: 字符数大于10的用户有1005和1006，长度分别为13、17；因此需要对1006的昵称截断输出 

6.注册时间最早的三个人

数据导入

drop table if exists user_info;
CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1', 19, 0, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-02-01 10:00:00'),
  (1003, '牛客3号♂', 22, 0, '算法', '2020-01-02 10:00:00'),
  (1004, '牛客4号', 25, 0, '算法', '2020-01-02 11:00:00'),
  (1005, '牛客555号', 4000, 7, 'C++', '2020-01-11 10:00:00'),
  (1006, '666666', 3000, 6, 'C++', '2020-11-01 10:00:00');

user_info表(用户信息表) 

uid: 用户ID   nick_name: 昵称   achievement: 成就值   level: 等级   job: 职业方向

register_time: 注册时间 

问题:请从中找到注册时间最早的3个人，按注册时间排序后选取前三名，输出其用户ID、昵称、注册时间

-- 解法1
SELECT uid, nick_name, register_time
FROM user_info
ORDER BY register_time ASC
LIMIT 3;

-- 解法2
SELECT uid, nick_name, register_time
FROM (SELECT uid, nick_name, register_time,
             row_number() over (ORDER BY register_time)AS time_rank
      FROM user_info)AS a
WHERE a.time_rank <= 3;

结果展示:

7.查找最晚入职员工的所有信息 

数据导入

drop table if exists  `employees` ; 
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

employees表

问题:查找employees里最晚入职员工的所有信息 

SELECT * 
FROM employees 
WHERE hire_date = (SELECT MAX(hire_date) FROM employees);

结果展示:

8.查找入职员工时间排名倒数第三的员工所有信息

数据导入

drop table if exists  `employees` ; 
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

employees表 

问题:查找employees里入职员工时间排名倒数第三的员工所有信息(注意:可能会存在同一个日期入职的员工，所以入职员工时间排名倒数第三的员工可能不止一个)

-- 解法1
SELECT emp_no, birth_date, first_name, last_name, gender, hire_date
FROM (SELECT *, 
             DENSE_RANK() OVER (ORDER BY hire_date DESC)AS dense_ranking
      FROM employees)AS a
WHERE a.dense_ranking = 3;

-- 解法2
SELECT emp_no, birth_date, first_name, last_name, gender, hire_date
FROM employees
WHERE hire_date=(SELECT DISTINCT hire_date 
                 FROM employees
                 ORDER BY hire_date DESC
                 LIMIT 2,1);

结果展示:

9.查找当前薪水详情以及部门编号dept_no

数据导入

drop table if exists  `salaries` ; 
drop table if exists  `dept_manager` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_manager VALUES('d001',10002,'9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');

salaries表(全部员工薪水表)

dept_manager表(各部门领导表)

问题:查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序(输出结果顺序:emp_no, salary, from_date, to_date, dept_no)

-- 解法1
SELECT a.emp_no, a.salary, a.from_date, a.to_date, b.dept_no
FROM salaries AS a
INNER JOIN dept_manager AS b
ON a.emp_no = b.emp_no
ORDER BY a.emp_no ASC;

-- 解法2
SELECT a.emp_no,a.salary,a.from_date,a.to_date,b.dept_no 
FROM salaries AS a
RIGHT JOIN dept_manager AS b
ON a.emp_no = b.emp_no
ORDER BY a.emp_no;

-- 解法3
SELECT b.emp_no,b.salary,b.from_date,b.to_date,a.dept_no 
FROM dept_manager AS a
LEFT JOIN salaries AS b
ON a.emp_no = b.emp_no
ORDER BY b.emp_no;

结果展示: 

10.查找所有员工的last_name和first_name以及对应部门编号dept_no

数据导入

drop table if exists  `dept_emp` ; 
drop table if exists  `employees` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d002','1996-08-03','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');

employees表(员工表)

dept_emp表(部门表)

问题:查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工

SELECT a.last_name, a.first_name, b.dept_no
FROM employees AS a
LEFT JOIN dept_emp AS b
ON a.emp_no = b.emp_no;

结果展示:

注意
INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据

11.获取所有非manager的员工emp_no

数据导入

drop table if exists  `dept_manager` ; 
drop table if exists  `employees` ; 
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');

employees表(员工表) 

dept_manager表(部门领导表)

问题:找出所有非部门领导的员工emp_no 

-- 解法1
SELECT emp_no 
FROM employees
WHERE emp_no NOT IN(SELECT emp_no 
                    FROM dept_manager);

-- 解法2
SELECT emp_no
FROM employees
WHERE emp_no NOT IN (SELECT a.emp_no
                     FROM employees AS a
                     INNER JOIN dept_manager AS b
                     ON a.emp_no = b.emp_no);

-- 解法3
SELECT a.emp_no 
FROM employees AS a
LEFT JOIN dept_manager AS b
ON a.emp_no = b.emp_no 
WHERE b.dept_no IS NULL;

结果展示:

12.获取所有员工当前的manager 

导入数据

drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');

dept_emp表(员工表)

dept_manager表(部门经理表)

-- 解法1
SELECT a.emp_no, b.emp_no AS manager
FROM dept_emp AS a
CROSS JOIN dept_manager AS b
ON a.dept_no = b.dept_no
WHERE a.emp_no != b.emp_no;

-- 解法2
SELECT a.emp_no, b.emp_no AS manager
FROM dept_emp AS a
LEFT JOIN dept_manager AS b
ON a.dept_no = b.dept_no
WHERE a.emp_no <> b.emp_no;

-- 解法3
SELECT a.emp_no, b.emp_no AS manager
FROM dept_emp AS a
INNER JOIN dept_manager AS b
ON a.dept_no = b.dept_no
WHERE a.emp_no != b.emp_no;

结果展示:

13.查找employees表emp_no与last_name的员工信息

数据导入

drop table if exists  `employees` ; 
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Bezalel','Mary','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1953-11-07','Mary','Sluis','F','1990-01-22');

employees表(员工表) 

问题:查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列 

SELECT emp_no, birth_date, first_name, last_name, gender, hire_date
FROM employees
WHERE emp_no % 2 != 0 AND last_name != 'Mary'
ORDER BY hire_date DESC;

结果展示:

14.统计出当前各个title类型对应的员工当前薪水对应的平均工资 

数据导入

drop table if exists  `salaries` ; 
drop table if exists  titles;
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'1995-12-01','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');

INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','2001-12-01','9999-01-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10006,'Senior Engineer','2001-08-02','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');

titles表

salaries表

问题:统计出各个title类型对应的员工薪水对应的平均工资avg，结果给出title以及平均工资avg，并且以avg升序排序 

SELECT a.title, AVG(b.salary)
FROM titles AS a
INNER JOIN salaries AS b
ON a.emp_no = b.emp_no
GROUP BY a.title
ORDER BY AVG(b.salary) ASC;

结果展示:

15.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary (一)

数据导入

drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');

salaries表 

问题:获取薪水第二多的员工的emp_no以及其对应的薪水salary，若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序 

-- 解法1
SELECT a.emp_no, a.salary
FROM(SELECT emp_no, salary,
            DENSE_RANK() OVER(ORDER BY salary DESC)AS dense_ranking
     FROM salaries)AS a
WHERE a.dense_ranking = 2
ORDER BY a.emp_no ASC;

-- 解法2
SELECT emp_no, salary
FROM salaries
WHERE salary = (SELECT DISTINCT salary 
				FROM salaries 
				ORDER BY salary DESC 
				LIMIT 1,1)
ORDER BY emp_no ASC;

结果展示:

16.获取当前薪水第二多的员工的emp_no以及其对应的薪水salary(二) 

数据导入

drop table if exists  `employees` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');

employees表(员工表) 

salaries表(薪水表)

问题:请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成 

SELECT a.emp_no, b.salary, a.last_name, a.first_name
FROM employees AS a
INNER JOIN salaries AS b
ON a.emp_no = b.emp_no
WHERE b.salary = (SELECT MAX(salary)
                  FROM salaries
                  WHERE salary NOT IN(SELECT MAX(salary)
                                      FROM salaries)
                 );

结果展示:

17.查找所有员工的last_name和first_name以及对应的dept_name

数据导入

drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `employees` ; 
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO departments VALUES('d003','Human Resources');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1990-08-05','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');

employees表(员工表)

departments表(部门表) 

dept_emp表(部门员工关系表) 

问题:查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工 

SELECT a.last_name, a.first_name, c.dept_name
FROM employees AS a
LEFT JOIN dept_emp AS b
ON a.emp_no = b.emp_no
LEFT JOIN departments AS c
ON b.dept_no = c.dept_no;

结果展示:

18.获取每个部门中当前员工薪水最高的相关信息

数据导入 

drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');

INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,92527,'2001-08-02','9999-01-01');

dept_emp表(员工表)

salaries表(薪水表)

问题:获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列 

SELECT temp.dept_no, temp.emp_no, temp.salary AS maxSalary
FROM (SELECT a.dept_no, b.emp_no, b.salary,
             DENSE_RANK() OVER (PARTITION BY a.dept_no ORDER BY b.salary DESC)AS dense_ranking
      FROM dept_emp AS a
      INNER JOIN salaries AS b
      ON a.emp_no = b.emp_no)AS temp
WHERE temp.dense_ranking = 1
ORDER BY temp.dept_no ASC;

结果展示:

19.统计各个部门的工资记录数 

数据导入

drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','2001-06-22','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10003,32323,'1996-08-03','9999-01-01');

departments表(部门表)

dept_ emp表(部门员工关系表) 

salaries表(薪水表)

问题:统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序(输出结果顺序:dept_no,dept_name, sum)

SELECT a.dept_no, a.dept_name, COUNT(c.salary)AS 'sum'
FROM departments AS a
INNER JOIN dept_emp AS b
ON a.dept_no = b.dept_no
INNER JOIN salaries AS c
ON b.emp_no = c.emp_no
GROUP BY a.dept_no
ORDER BY a.dept_no ASC;

结果展示:

20.对所有员工的薪水按照salary降序进行1-N的排名

数据导入

drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,72527,'2001-12-01','9999-01-01');

salaries表(薪水表)

问题:对所有员工的薪水按照salary降序先进行1-N的排名，如果salary相同，再按照emp_no升序排列 

SELECT emp_no, salary, 
       DENSE_RANK() OVER (ORDER BY salary DESC)AS t_rank
FROM salaries;

结果展示:

21.将employees表的所有员工的last_name和first_name拼接起来作为Name

数据导入

drop table if exists  `employees` ; 
CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` char(1) NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

employees表 

问题:将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分 

SELECT CONCAT(last_name, ' ', first_name)AS Name
FROM employees;

结果展示:

22.使用子查询的方式找出属于Action分类的所有电影对应的title,description 

数据导入

drop table if exists   film ;
drop table if exists  category  ; 
drop table if exists  film_category  ; 
CREATE TABLE IF NOT EXISTS film (
  film_id smallint(5)  NOT NULL DEFAULT '0',
  title varchar(255) NOT NULL,
  description text,
  PRIMARY KEY (film_id));
CREATE TABLE category  (
   category_id  tinyint(3)  NOT NULL ,
   name  varchar(25) NOT NULL, `last_update` timestamp,
  PRIMARY KEY ( category_id ));
CREATE TABLE film_category  (
   film_id  smallint(5)  NOT NULL,
   category_id  tinyint(3)  NOT NULL, `last_update` timestamp);
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(3,6,'2006-02-14 21:07:09');

film表

film_id:电影id  title:电影名称  description:电影描述信息 

category表

category_id:电影分类id  name:电影分类名称  last_update:电影分类最后更新时间 

film_category表 

film_id:电影id  category_id:电影分类id  

last_update:电影id和分类id对应关系的最后更新时间 

问题:使用子查询的方式找出属于Action分类的所有电影对应的title,description

SELECT title, description
FROM film
WHERE film_id IN (SELECT film_id
                  FROM film_category
                  WHERE category_id IN (SELECT category_id
                                        FROM category
                                        WHERE name = 'Action'));

结果展示: 

23.使用join查询方式找出没有分类的电影id以及名称

数据导入

drop table if exists  film ;
drop table if exists  film_category  ; 

CREATE TABLE IF NOT EXISTS film (
  film_id smallint(5)  NOT NULL DEFAULT '0',
  title varchar(255) NOT NULL,
  description text,
  PRIMARY KEY (film_id));

CREATE TABLE film_category  (
   film_id  smallint(5)  NOT NULL,
   category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

INSERT INTO film_category VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,11,'2006-02-14 21:07:09');

film表(电影信息表)

film_id:电影id  title:电影名称  description:电影描述信息 

film_category表(电影分类表) 

film_id:电影id  category_id:电影分类id  

last_update:电影id和分类id对应关系的最后更新时间 

问题:使用join查询方式找出没有分类的电影id以及其电影名称

SELECT a.film_id, a.title
FROM film AS a
LEFT JOIN film_category AS b
ON a.film_id = b.film_id
WHERE category_id IS NULL;

结果展示:

24.汇总各个部门当前员工的title类型的分配数目 

数据导入

drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  titles ;
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10002,'Staff','1996-08-03','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','1995-12-03','9999-01-01');

departments表(部门表) 

dept_emp表(部门员工关系表) 

titles表(职称表) 

问题:汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序，dept_no一样的再按title升序排序 

SELECT a.dept_no, a.dept_name, c.title, COUNT(c.title)AS 'count'
FROM departments AS a
INNER JOIN dept_emp AS b
ON a.dept_no = b.dept_no
INNER JOIN titles AS c
ON b.emp_no = c.emp_no
GROUP BY a.dept_no, c.title
ORDER BY a.dept_no ASC, c.title ASC;

结果展示:

25.获取所有非manager员工当前的薪水情况

数据导入

drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
drop table if exists  `employees` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1996-08-03');
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');

employees表(员工表) 

dept_emp表(部门员工关系表) 

dept_manager表(部门经理表) 

salaries表(薪水表) 

问题:获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary 

-- 解法1
SELECT a.dept_no, a.emp_no, b.salary
FROM dept_emp AS a
INNER JOIN salaries AS b
ON a.emp_no = b.emp_no
WHERE a.emp_no NOT IN(SELECT emp_no FROM dept_manager)
AND a.to_date = '9999-01-01';

-- 解法2
SELECT b.dept_no, a.emp_no, d.salary 
FROM employees AS a,
     dept_emp AS b,
     dept_manager AS c,
     salaries AS d 
WHERE a.emp_no = b.emp_no 
AND b.dept_no = c.dept_no 
AND b.emp_no != c.emp_no 
AND a.emp_no = d.emp_no;

-- 解法3
SELECT de.dept_no,a.emp_no,s.salary
FROM (SELECT emp_no
      FROM employees 
      WHERE emp_no NOT IN (SELECT emp_no
                           FROM dept_manager))AS a 
INNER JOIN dept_emp AS de 
ON a.emp_no=de.emp_no
INNER JOIN salaries AS s 
ON a.emp_no=s.emp_no
WHERE s.to_date='9999-01-01';

结果展示:

26.查找在职员工自入职以来的薪水涨幅情况 

数据导入

drop table if exists  `employees` ; 
drop table if exists  `salaries` ;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','2001-06-22');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1999-08-03');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');

employees表(员工表)

salaries表(薪水表) 

问题:查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序 (注明: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01'时，表示依然在职，无后续调整记录)

-- 解法1
SELECT a.emp_no, (a.now_salary - b.past_salary) AS growth
FROM (SELECT emp_no, salary AS now_salary
      FROM salaries 
      WHERE to_date = '9999-01-01') AS a
INNER JOIN (SELECT a.emp_no, b.salary AS past_salary
            FROM employees AS a
            INNER JOIN salaries AS b
            ON a.emp_no = b.emp_no
            WHERE a.hire_date = b.from_date
            AND a.emp_no IN (SELECT emp_no 
                             FROM salaries
                             WHERE to_date = '9999-01-01')) AS b
ON a.emp_no = b.emp_no
ORDER BY growth ASC;

-- 解法2
SELECT a.emp_no, (b.salary - c.salary)AS growth
FROM employees AS a
INNER JOIN salaries AS b
ON a.emp_no = b.emp_no 
AND b.to_date = '9999-01-01'
INNER JOIN salaries AS c
ON a.emp_no = c.emp_no AND a.hire_date = c.from_date 
ORDER BY growth ASC;

结果展示:

27.获取员工其当前的薪水比其manager当前薪水还高的相关信息 

数据导入

drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');

dept_emp表(部门关系表) 

dept_manager表(部门经理表) 

salaries表(薪水表) 

问题:获取员工其当前的薪水比其manager当前薪水还高的相关信息

输出结果顺序:

第一列给出员工的emp_no

第二列给出其manager的manager_no

第三列给出该员工当前的薪水emp_salary

第四列给该员工对应的manager当前的薪水manager_salary

SELECT a.yuangong_num AS emp_no, b.manager_num AS manager_no, a.yuangong_money AS emp_salary, b.manager_money AS manager_salary
FROM (SELECT a.emp_no AS yuangong_num, b.salary AS yuangong_money, a.dept_no
      FROM dept_emp AS a
      INNER JOIN salaries AS b
      ON a.emp_no = b.emp_no
      INNER JOIN dept_manager AS c
      ON a.dept_no = c.dept_no
      WHERE a.emp_no != c.emp_no) AS a
INNER JOIN (SELECT a.emp_no AS manager_num, b.salary AS manager_money, a.dept_no
            FROM dept_manager AS a
            INNER JOIN salaries AS b
            ON a.emp_no = b.emp_no) AS b
ON a.dept_no = b.dept_no
WHERE a.yuangong_money > b.manager_money;

结果展示:

山茶花开时。
关注
专栏目录
SQL习题集_详细注释版答案
zhsworld的博客
03-02 1099
一、准备工作 1 环境 MySQL 5.7.28 + Ubuntu18.04.4 2 登录MySQL -- Terminal下输入 mysql -uroot -p 3 数据库操作 -- 创建数据库 practice -- character set 用来指定编码格式，方便之后插入中文 create database practice character set utf8; -...
SQL习题集大全（超级好）
07-30
本文是个一个超级全面的SQL习题集以及讲解，适用于复习笔试面试，以及有工作经验的人使用，非常的好
SQL题集
song3的专栏
10-09 1064
//要求：用一条SQL语句实现日期过滤，当pid相同的时候返回离系统时间最近的那条记录。SELECT             A.*FROM       exam_1 A       JOIN (                SELECT                            MAX(time) date,                           pid   
sql经典练习题集~面试有用哦
03-18
从网上搜集回来的sql经典习题，在笔试面试中企业经常会考察sql的编写
Sql经典练习题库（附答案）
02-17
SQL是高级的非过程化编程语言，是沟通数据库服务器和客户端的重要工具，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以，具有完全不同底层结构的不同数据库系统，可以使用相同的SQL语言作为数据输入与管理的SQL接口。 它以记录集合作为操作对象，所有SQL语句接受集合作为输入，返回集合作为输出，这种集合特性允许一条SQL语句的输出作为另一条SQL语句的输入，所以SQL语句可以嵌套，这使它具有极大的灵活性和强大的功能，在多数情况下，在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的，这也意味着用SQL语言可以写出非常复杂的语句。 　　 结构化查询语言(Structured Query Language）最早是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEM R开发的一种查询语言，它的前身是SQUARE语言。SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年推出以来，SQL语言得到了广泛的应用。如今无论是像Oracle、Sybase、DB2、Informix、SQL Server这些大型的数据库管理系统，还是像Visual Foxpro、PowerBuilder这些PC上常用的数据库开发系统，都支持SQL语言作为查询语言。 　 　美国国家标准局(ANSI）与国际标准化组织（ISO）已经制定了SQL标准。ANSI是一个美国工业和商业集团组织，负责开发美国的商务和通讯标准。ANSI同时也是ISO和International Electrotechnical Commission(IEC）的成员之一。ANSI 发布与国际标准组织相应的美国标准。1992年，ISO和IEC发布了SQL国际标准，称为SQL-92。ANSI随之发布的相应标准是ANSI SQL-92。ANSI SQL-92有时被称为ANSI SQL。尽管不同的关系数据库使用的SQL版本有一些差异，但大多数都遵循 ANSI SQL 标准。SQL Server使用ANSI SQL-92的扩展集，称为T-SQL，其遵循ANSI制定的 SQL-92标准。 　　 SQL语言包含4个部分： 　　 数据定义语言(DDL），例如：CREATE、DROP、ALTER等语句。 　　 数据操作语言(DML），例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。 　　 数据查询语言（DQL），例如：SELECT语句。 　　 数据控制语言（DCL），例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。 　　 SQL语言包括三种主要程序设计语言类别的语句：数据定义语言（DDL），数据操作语言（DML）及数据控制语言（DCL）。
数据分析师面试题SQL业务机器学习SQL面试集训题集含指导视频
最新发布
10-04
数据分析师面试题SQL业务机器学习SQL面试集训题集含指导视频
SQL练习题集：选择与填空
"SQL同步练习，包括选择题和填空题，旨在提升SQL技能" SQL（Structured Query Language），即结构化查询语言，是用于管理关系数据库的标准语言。它包括数据查询、数据操作、数据定义和数据控制四个主要部分。以下是...
SQL练习题集：从基础到进阶
"这些SQL练习题涵盖了基础的SQL查询技巧，包括子查询、连接查询、聚合函数、条件过滤等，适合SQL新手学习和巩固。" SQL是用于管理和操作关系数据库的强大工具，它允许用户执行各种任务，如创建、修改和查询数据库。...
SQL考试题集与答案解析
- 第二个应用题要求创建数据库并配置文件，以及演示如何在SQL Server中执行这些操作。 这份文档涵盖了SQL基础和实际操作的考试题目，对于准备SQL考试或学习SQL的用户来说，是一份宝贵的参考资料。
SQL考试题集与答案详解
本文档是一份SQL考试题目及答案参考材料，涵盖了选择题和简答题部分，主要涉及SQL基础知识、数据库管理、系统组件、SQL语句以及数据库操作等核心内容。以下是详细的知识点解析： 1. **数据库管理系统(DBMS)**: 题目...
50道数据库SQL练习题（深入理解各关键字的作用）
林夕07
06-15 4146
通过设有学生表、教师表、课程表和成绩表衍生出来近50多个题，每个题都有不同的解法。让你对sql更加有深层的认知
sql题+算法题
TSzero的博客
07-31 349
一、sql题 1.1 随机抽样 从库表table_a中随机抽取100条数据出来，那么这时候我们就需要用到order by了，因为我们知道order by是根据某一个字段值来统一排序，那么如果我们把order by 的字段换成随机函数，会直接让结果集随机排序，最后我们再加上limit 100,这样就可以达到随机抽取结果的目的，具体的实现示例即为如下： select * from table_a order by rand() limit 100 ; 1.2 udf函数（第NNN高的薪水） 1.
SQL的各种常用算法问题
liupeng900605的专栏
12-11 630
************************************分页算法 实现分页，可以使用嵌套查询，也可以使用存储过程。不过存储过程太复杂，执行效率也不一定高， 所以我就用这样的嵌套语句来实现： SELECT TOP 页大小 * FROM TestTable  WHERE (ID >(SELECT MAX(id) FROM (SELECT TOP 页大小*页数 id  FRO
SQL题集(三)
Hudas的博客
05-12 651
本文主要收录练习过的SQL练习题
《SQL数据分析——从基础破冰到面试题解》题解1
XueEEX的博客
07-29 632
《SQL数据分析——从基础破冰到面试题解》第一部分22个简单难度的SQL题目的题解。其中包含课本中的解答，也包含自己的题解。欢迎大家前来交流讨论
DROP TABLE IF EXISTS `ay_admin` ;
热门推荐
u012062810的专栏
05-05 1万+
导入数据库一直出现标题错误，但是数据库是没错的。如图：这是因为你要先新建这个数据库名，然后再该名下导入数据库文件，就可以了。
2020-10-17 mysql练习（1表的创建）
weixin_46029176的博客
10-17 257
myemployees数据库创建(mysql可直接复制粘贴) /* SQLyog Ultimate v10.00 Beta1 MySQL - 5.5.15 : Database - myemployees ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UN
SQL练习题合集
gly1653810310的博客
06-02 1902
本文都是SQL练习题，本人写的答案不一定是正确或者完善的，如果有更简便的方法，欢迎留言交流学习
sql面试题
wangec199339的博客
03-19 887
问题及描述：  --1.学生表  Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别  --2.课程表  Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号  --3.教师表  Teacher(TID,Tname) --TID 教师编号,T
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值