當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

SQL题集(二)

發(fā)布時(shí)間：2023/12/20 数据库 27 豆豆

生活随笔收集整理的這篇文章主要介紹了 SQL题集(二) 小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

1.比賽名單整理

數(shù)據(jù)導(dǎo)入

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 ('誰(shuí)與爭(zhēng)鋒隊(duì)') ,('必勝隊(duì)') ,('乘風(fēng)破浪隊(duì)') ,('群英匯隊(duì)') ,('夢(mèng)之隊(duì)');

competition_list表

team_name : 參賽隊(duì)名

問(wèn)題:每個(gè)參賽隊(duì)伍都會(huì)和其他參賽隊(duì)伍開(kāi)展一次組隊(duì)比賽，要求輸出兩兩參賽隊(duì)伍的所有比賽情況組合(兩者分別為隊(duì)伍A和隊(duì)伍B)，并按照隊(duì)名依次升序排列

解題思路

使用表的自連接，通過(guò)在隊(duì)名之間以"<"的方式連接來(lái)確保隊(duì)伍不會(huì)和自身匹配，并依次按照隊(duì)名進(jìn)行升序排序即可（涉及的知識(shí)點(diǎn):多表連接）

SELECT a.team_name AS 隊(duì)伍A, b.team_name AS 隊(duì)伍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;

結(jié)果展示

2.參與優(yōu)惠活動(dòng)的商品

數(shù)據(jù)導(dǎo)入

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 : 商品優(yōu)惠活動(dòng)起始日期? ?end_date : 商品優(yōu)惠活動(dòng)結(jié)束日期

問(wèn)題:查詢(xún)?cè)?021年1月7日至2021年1月9日期間參與優(yōu)惠活動(dòng)的商品

解題思路

假設(shè)2021年1月7日為時(shí)間a,2021年1月9日為時(shí)間b,每個(gè)優(yōu)惠活動(dòng)的開(kāi)始時(shí)間為s，結(jié)束時(shí)間為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');

結(jié)果展示

3.連續(xù)售出的商品

數(shù)據(jù)導(dǎo)入

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 : 購(gòu)買(mǎi)的商品ID

問(wèn)題:找出連續(xù)下單大于或等于3次的商品ID

解題思路

使用窗口函數(shù)LAG(order_id,2),根據(jù)商品ID進(jìn)行分組，并按照訂單順序默認(rèn)升序延后兩行展開(kāi)

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

?輸出結(jié)果

在上述輸出結(jié)果中進(jìn)行子查詢(xún)操作，如果連續(xù)3次出現(xiàn)相同的commodity_id字段值，則應(yīng)該在相同commodity_id字段值的第3行出現(xiàn)order_id = temp + 2的情況。如上圖所示，commodity_id字段值為c_002, order_id = 5 和 temp = 3的那條記錄符合這一條件。在子查詢(xún)外部加入WHERE order_id = temp + 2進(jìn)行判斷并將結(jié)果去重即可。

涉及知識(shí)點(diǎn): 子查詢(xún)、窗口函數(shù)、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;

結(jié)果展示

4.修復(fù)串列的記錄

數(shù)據(jù)導(dǎo)入

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 '類(lèi)別標(biāo)簽',difficulty varchar(8) COMMENT '難度',duration int NOT NULL COMMENT '時(shí)長(zhǎng)',release_time datetime COMMENT '發(fā)布時(shí)間' )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: 試卷類(lèi)別? difficulty: 試卷難度? duration: 考試時(shí)長(zhǎng)? release_time: 發(fā)布時(shí)間?

問(wèn)題:?錄題同學(xué)有一次手誤將部分記錄的試題類(lèi)別tag、難度difficulty、時(shí)長(zhǎng)duration同時(shí)錄入到了tag字段，請(qǐng)幫忙找出這些錄錯(cuò)了的記錄，并拆分后按正確的列類(lèi)型輸出?

-- 解法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 ',+';

結(jié)果展示:

5.對(duì)過(guò)長(zhǎng)的昵稱(chēng)進(jìn)行截取處理

數(shù)據(jù)導(dǎo)入?

drop table if exists user_info; CREATE TABLE user_info (id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',uid int UNIQUE NOT NULL COMMENT '用戶(hù)ID',`nick_name` varchar(64) COMMENT '昵稱(chēng)',achievement int COMMENT '成就值',level int COMMENT '用戶(hù)等級(jí)',job varchar(32) COMMENT '職業(yè)方向',register_time datetime COMMENT '注冊(cè)時(shí)間' )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號(hào)', 1200, 3, '算法', '2020-01-01 10:00:00'),(1003, '牛客3號(hào)♂', 22, 0, '算法', '2020-01-01 10:00:00'),(1004, '牛客4號(hào)', 25, 0, '算法', '2020-01-01 11:00:00'),(1005, '牛客5678901234號(hào)', 4000, 7, '算法', '2020-01-01 10:00:00'),(1006, '牛客67890123456789號(hào)', 25, 0, '算法', '2020-01-02 11:00:00');

user_info表(用戶(hù)信息表)

uid: 用戶(hù)ID? ?nick_name: 昵稱(chēng)? ?achievement: 成就值? ?level: 等級(jí)? ?job: 職業(yè)方向

register_time: 注冊(cè)時(shí)間?

問(wèn)題:有的用戶(hù)的昵稱(chēng)特別長(zhǎng)，在一些展示場(chǎng)景會(huì)導(dǎo)致樣式混亂，因此需要將特別長(zhǎng)的昵稱(chēng)轉(zhuǎn)換一下再輸出，請(qǐng)輸出字符數(shù)大于10的用戶(hù)信息，對(duì)于字符數(shù)大于13的用戶(hù)輸出前10個(gè)字符然后加上三個(gè)點(diǎn)號(hào): "..."

-- 解法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_nameELSE CONCAT(SUBSTRING(nick_name,1,10),'...')END)AS nick_name FROM user_info WHERE CHAR_LENGTH(nick_name) > 10;

結(jié)果展示:

解釋: 字符數(shù)大于10的用戶(hù)有1005和1006，長(zhǎng)度分別為13、17；因此需要對(duì)1006的昵稱(chēng)截?cái)噍敵?

6.注冊(cè)時(shí)間最早的三個(gè)人

數(shù)據(jù)導(dǎo)入

drop table if exists user_info; CREATE TABLE user_info (id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',uid int UNIQUE NOT NULL COMMENT '用戶(hù)ID',`nick_name` varchar(64) COMMENT '昵稱(chēng)',achievement int COMMENT '成就值',level int COMMENT '用戶(hù)等級(jí)',job varchar(32) COMMENT '職業(yè)方向',register_time datetime COMMENT '注冊(cè)時(shí)間' )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號(hào)', 1200, 3, '算法', '2020-02-01 10:00:00'),(1003, '牛客3號(hào)♂', 22, 0, '算法', '2020-01-02 10:00:00'),(1004, '牛客4號(hào)', 25, 0, '算法', '2020-01-02 11:00:00'),(1005, '牛客555號(hào)', 4000, 7, 'C++', '2020-01-11 10:00:00'),(1006, '666666', 3000, 6, 'C++', '2020-11-01 10:00:00');

user_info表(用戶(hù)信息表)?

uid: 用戶(hù)ID? ?nick_name: 昵稱(chēng)? ?achievement: 成就值? ?level: 等級(jí)? ?job: 職業(yè)方向

register_time: 注冊(cè)時(shí)間?

問(wèn)題:請(qǐng)從中找到注冊(cè)時(shí)間最早的3個(gè)人，按注冊(cè)時(shí)間排序后選取前三名，輸出其用戶(hù)ID、昵稱(chēng)、注冊(cè)時(shí)間

-- 解法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_rankFROM user_info)AS a WHERE a.time_rank <= 3;

結(jié)果展示:

7.查找最晚入職員工的所有信息?

數(shù)據(jù)導(dǎo)入

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表

問(wèn)題:查找employees里最晚入職員工的所有信息?

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

結(jié)果展示:

8.查找入職員工時(shí)間排名倒數(shù)第三的員工所有信息

數(shù)據(jù)導(dǎo)入

employees表?

問(wèn)題:查找employees里入職員工時(shí)間排名倒數(shù)第三的員工所有信息(注意:可能會(huì)存在同一個(gè)日期入職的員工，所以入職員工時(shí)間排名倒數(shù)第三的員工可能不止一個(gè))

-- 解法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_rankingFROM 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 employeesORDER BY hire_date DESCLIMIT 2,1);

結(jié)果展示:

9.查找當(dāng)前薪水詳情以及部門(mén)編號(hào)dept_no

數(shù)據(jù)導(dǎo)入

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表(各部門(mén)領(lǐng)導(dǎo)表)

問(wèn)題:查找各個(gè)部門(mén)當(dāng)前領(lǐng)導(dǎo)的薪水詳情以及其對(duì)應(yīng)部門(mén)編號(hào)dept_no，輸出結(jié)果以salaries.emp_no升序排序(輸出結(jié)果順序: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;

結(jié)果展示:?

10.查找所有員工的last_name和first_name以及對(duì)應(yīng)部門(mén)編號(hào)dept_no

數(shù)據(jù)導(dǎo)入

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表(部門(mén)表)

問(wèn)題:查找所有已經(jīng)分配部門(mén)的員工的last_name和first_name以及dept_no，也包括暫時(shí)沒(méi)有分配具體部門(mén)的員工

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;

結(jié)果展示:

注意
INNER JOIN 兩邊表同時(shí)有對(duì)應(yīng)的數(shù)據(jù)，即任何一邊缺失數(shù)據(jù)就不顯示
LEFT JOIN 會(huì)讀取左邊數(shù)據(jù)表的全部數(shù)據(jù)，即便右邊表無(wú)對(duì)應(yīng)數(shù)據(jù)
RIGHT JOIN 會(huì)讀取右邊數(shù)據(jù)表的全部數(shù)據(jù)，即便左邊表無(wú)對(duì)應(yīng)數(shù)據(jù)

11.獲取所有非manager的員工emp_no

數(shù)據(jù)導(dǎo)入

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表(部門(mén)領(lǐng)導(dǎo)表)

問(wèn)題:找出所有非部門(mén)領(lǐng)導(dǎo)的員工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_noFROM employees AS aINNER JOIN dept_manager AS bON 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;

結(jié)果展示:

12.獲取所有員工當(dāng)前的manager?

導(dǎo)入數(shù)據(jù)

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表(部門(mén)經(jīng)理表)

-- 解法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;

結(jié)果展示:

13.查找employees表emp_no與last_name的員工信息

數(shù)據(jù)導(dǎo)入

employees表(員工表)?

問(wèn)題:查找employees表所有emp_no為奇數(shù)，且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;

結(jié)果展示:

14.統(tǒng)計(jì)出當(dāng)前各個(gè)title類(lèi)型對(duì)應(yīng)的員工當(dāng)前薪水對(duì)應(yīng)的平均工資?

數(shù)據(jù)導(dǎo)入

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表

問(wèn)題:統(tǒng)計(jì)出各個(gè)title類(lèi)型對(duì)應(yīng)的員工薪水對(duì)應(yīng)的平均工資avg，結(jié)果給出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;

結(jié)果展示:

15.獲取當(dāng)前薪水第二多的員工的emp_no以及其對(duì)應(yīng)的薪水salary (一)

數(shù)據(jù)導(dǎo)入

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表?

問(wèn)題:獲取薪水第二多的員工的emp_no以及其對(duì)應(yīng)的薪水salary，若有多個(gè)員工的薪水為第二多的薪水，則將對(duì)應(yīng)的員工的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_rankingFROM 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;

結(jié)果展示:

16.獲取當(dāng)前薪水第二多的員工的emp_no以及其對(duì)應(yīng)的薪水salary(二)?

數(shù)據(jù)導(dǎo)入

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表(薪水表)

問(wèn)題:請(qǐng)你查找薪水排名第二多的員工編號(hào)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 salariesWHERE salary NOT IN(SELECT MAX(salary)FROM salaries));

結(jié)果展示:

17.查找所有員工的last_name和first_name以及對(duì)應(yīng)的dept_name

數(shù)據(jù)導(dǎo)入

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表(部門(mén)表)?

dept_emp表(部門(mén)員工關(guān)系表)?

問(wèn)題:查找所有員工的last_name和first_name以及對(duì)應(yīng)的dept_name，也包括暫時(shí)沒(méi)有分配部門(mén)的員工?

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;

結(jié)果展示:

18.獲取每個(gè)部門(mén)中當(dāng)前員工薪水最高的相關(guān)信息

數(shù)據(jù)導(dǎo)入?

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表(薪水表)

問(wèn)題:獲取每個(gè)部門(mén)中當(dāng)前員工薪水最高的相關(guān)信息，給出dept_no, emp_no以及其對(duì)應(yīng)的salary，按照部門(mén)編號(hào)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_rankingFROM dept_emp AS aINNER JOIN salaries AS bON a.emp_no = b.emp_no)AS temp WHERE temp.dense_ranking = 1 ORDER BY temp.dept_no ASC;

結(jié)果展示:

19.統(tǒng)計(jì)各個(gè)部門(mén)的工資記錄數(shù)?

數(shù)據(jù)導(dǎo)入

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表(部門(mén)表)

dept_?emp表(部門(mén)員工關(guān)系表)?

salaries表(薪水表)

問(wèn)題:統(tǒng)計(jì)各個(gè)部門(mén)的工資記錄數(shù)，給出部門(mén)編碼dept_no、部門(mén)名稱(chēng)dept_name以及部門(mén)在salaries表里面有多少條記錄sum，按照dept_no升序排序(輸出結(jié)果順序: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;

結(jié)果展示:

20.對(duì)所有員工的薪水按照salary降序進(jìn)行1-N的排名

數(shù)據(jù)導(dǎo)入

salaries表(薪水表)

問(wèn)題:對(duì)所有員工的薪水按照salary降序先進(jìn)行1-N的排名，如果salary相同，再按照emp_no升序排列?

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

結(jié)果展示:

21.將employees表的所有員工的last_name和first_name拼接起來(lái)作為Name

數(shù)據(jù)導(dǎo)入

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表?

問(wèn)題:將employees表的所有員工的last_name和first_name拼接起來(lái)作為Name，中間以一個(gè)空格區(qū)分?

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

結(jié)果展示:

22.使用子查詢(xún)的方式找出屬于Action分類(lèi)的所有電影對(duì)應(yīng)的title,description?

數(shù)據(jù)導(dǎo)入

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:電影名稱(chēng)? description:電影描述信息?

category表

category_id:電影分類(lèi)id??name:電影分類(lèi)名稱(chēng)??last_update:電影分類(lèi)最后更新時(shí)間?

film_category表?

film_id:電影id? category_id:電影分類(lèi)id??

last_update:電影id和分類(lèi)id對(duì)應(yīng)關(guān)系的最后更新時(shí)間?

問(wèn)題:使用子查詢(xún)的方式找出屬于Action分類(lèi)的所有電影對(duì)應(yīng)的title,description

SELECT title, description FROM film WHERE film_id IN (SELECT film_idFROM film_categoryWHERE category_id IN (SELECT category_idFROM categoryWHERE name = 'Action'));

結(jié)果展示:?

23.使用join查詢(xún)方式找出沒(méi)有分類(lèi)的電影id以及名稱(chēng)

數(shù)據(jù)導(dǎo)入

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:電影名稱(chēng)? description:電影描述信息?

film_category表(電影分類(lèi)表)?

film_id:電影id? category_id:電影分類(lèi)id??

last_update:電影id和分類(lèi)id對(duì)應(yīng)關(guān)系的最后更新時(shí)間?

問(wèn)題:使用join查詢(xún)方式找出沒(méi)有分類(lèi)的電影id以及其電影名稱(chēng)

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;

結(jié)果展示:

24.匯總各個(gè)部門(mén)當(dāng)前員工的title類(lèi)型的分配數(shù)目?

數(shù)據(jù)導(dǎo)入

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表(部門(mén)表)?

dept_emp表(部門(mén)員工關(guān)系表)?

titles表(職稱(chēng)表)?

問(wèn)題:匯總各個(gè)部門(mén)當(dāng)前員工的title類(lèi)型的分配數(shù)目，即結(jié)果給出部門(mén)編號(hào)dept_no、dept_name、其部門(mén)下所有的員工的title以及該類(lèi)型title對(duì)應(yīng)的數(shù)目count，結(jié)果按照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;

結(jié)果展示:

25.獲取所有非manager員工當(dāng)前的薪水情況

數(shù)據(jù)導(dǎo)入

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表(部門(mén)員工關(guān)系表)?

dept_manager表(部門(mén)經(jīng)理表)?

salaries表(薪水表)?

問(wèn)題:獲取所有非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_noFROM employees WHERE emp_no NOT IN (SELECT emp_noFROM 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';

結(jié)果展示:

26.查找在職員工自入職以來(lái)的薪水漲幅情況?

數(shù)據(jù)導(dǎo)入

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表(薪水表)?

問(wèn)題:查找在職員工自入職以來(lái)的薪水漲幅情況，給出在職員工編號(hào)emp_no以及其對(duì)應(yīng)的薪水漲幅growth，并按照growth進(jìn)行升序 (注明: to_date為薪資調(diào)整某個(gè)結(jié)束日期，或者為離職日期，to_date='9999-01-01'時(shí)，表示依然在職，無(wú)后續(xù)調(diào)整記錄)

-- 解法1 SELECT a.emp_no, (a.now_salary - b.past_salary) AS growth FROM (SELECT emp_no, salary AS now_salaryFROM salaries WHERE to_date = '9999-01-01') AS a INNER JOIN (SELECT a.emp_no, b.salary AS past_salaryFROM employees AS aINNER JOIN salaries AS bON a.emp_no = b.emp_noWHERE a.hire_date = b.from_dateAND a.emp_no IN (SELECT emp_no FROM salariesWHERE 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;

結(jié)果展示:

27.獲取員工其當(dāng)前的薪水比其manager當(dāng)前薪水還高的相關(guān)信息?

數(shù)據(jù)導(dǎo)入

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表(部門(mén)關(guān)系表)?

dept_manager表(部門(mén)經(jīng)理表)?

salaries表(薪水表)?

問(wèn)題:獲取員工其當(dāng)前的薪水比其manager當(dāng)前薪水還高的相關(guān)信息

輸出結(jié)果順序:

第一列給出員工的emp_no

第二列給出其manager的manager_no

第三列給出該員工當(dāng)前的薪水emp_salary

第四列給該員工對(duì)應(yīng)的manager當(dāng)前的薪水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_noFROM dept_emp AS aINNER JOIN salaries AS bON a.emp_no = b.emp_noINNER JOIN dept_manager AS cON a.dept_no = c.dept_noWHERE a.emp_no != c.emp_no) AS a INNER JOIN (SELECT a.emp_no AS manager_num, b.salary AS manager_money, a.dept_noFROM dept_manager AS aINNER JOIN salaries AS bON a.emp_no = b.emp_no) AS b ON a.dept_no = b.dept_no WHERE a.yuangong_money > b.manager_money;

結(jié)果展示:

總結(jié)

以上是生活随笔為你收集整理的SQL题集(二)的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

SQL
题集

上一篇： python宿舍管理系統毕业设计源码23
下一篇： PS制作渐变