練習SQL利器尾序，牛客網(wǎng)SQL實戰(zhàn)題庫饶辙，17~24題

哦拙鳎客網(wǎng)SQL實戰(zhàn)網(wǎng)址：https://www.nowcoder.com/ta/sql
持續(xù)更新——記錄自己在牛客網(wǎng)SQL的做題過程

17.獲取當前薪水第二多的員工的emp_no以及其對應(yīng)的薪水salary

題目描述：
獲取當前（to_date='9999-01-01'）薪水第二多的員工的emp_no以及其對應(yīng)的薪水salary
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));

SELECT emp_no,salary
FROM salaries
WHERE to_date='9999-01-01'
ORDER BY salary DESC
LIMIT 1,1;

注：在做題討論時候弃揽，有的人說薪水第一的有可能喲多個脯爪，所以要去重后選擇第二位，但是還有一個問題矿微，如果有兩個人并列薪水第一痕慢，那么你選擇的第二其實是薪水第三多的人，所以其實得看一下數(shù)據(jù)集再確定涌矢，很明顯掖举，牛客網(wǎng)給定的數(shù)據(jù)集只有一個第一娜庇，所以怎么寫都行塔次。

18.查找當前薪水排名第二多的員工編號emp_no、薪水salary名秀、last_name以及first_name励负，不準使用order by

題目描述：
查找當前薪水(to_date='9999-01-01')排名第二多的員工編號emp_no、薪水salary匕得、last_name以及first_name继榆，不準使用order by
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));

#wasrehpic的答案
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name 
FROM employees AS e INNER JOIN salaries AS s 
ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01'
AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')

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

題目描述:
查找所有員工的last_name和first_name以及對應(yīng)的dept_name，也包括暫時沒有分配部門的員工
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));

SELECT emp.last_name, emp.first_name, dep.dept_name
FROM employees AS emp LEFT JOIN dept_emp AS dept ON emp.emp_no = dept.emp_no
LEFT JOIN departments AS dep ON dept.dept_no = dep.dept_no

注：多表連接（超過兩個表）

20.查找員工編號emp_no為10001其自入職以來的薪水salary漲幅值growth

題目描述:
查找員工編號emp_no為10001其自入職以來的薪水salary漲幅值growth
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));

SELECT MAX(salary)-MIN(salary) AS growth
FROM salaries
WHERE emp_no="10001"

21.查找所有員工自入職以來的薪水漲幅情況

題目描述：
查找所有員工自入職以來的薪水漲幅情況，給出員工編號emp_no以及其對應(yīng)的薪水漲幅growth略吨，并按照growth進行升序
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));

思考過程：
①創(chuàng)建第一個表集币，存儲現(xiàn)在的薪水和員工號
②創(chuàng)建第二個表，存儲入職的薪水和員工號
③將兩個表連接
④計算漲薪

#wasrehpic答案翠忠，自己沒寫出來
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT s.emp_no,s.salary 
      FROM employees e LEFT JOIN salaries s 
      ON e.emp_no = s.emp_no 
      WHERE s.to_date = '9999-01-01') AS sCurrent
INNER JOIN(SELECT s.emp_no,s.salary 
           FROM employees e LEFT JOIN salaries s 
           ON e.emp_no = s.emp_no 
           WHERE s.from_date = e.hire_date) AS sStart
ON sCurrent.emp_no = sStart.emp_no
ORDER BY growth

22.統(tǒng)計各個部門對應(yīng)員工漲幅的次數(shù)總和

題目描述：
統(tǒng)計各個部門對應(yīng)員工漲幅的次數(shù)總和鞠苟，給出部門編碼dept_no、部門名稱dept_name以及次數(shù)sum
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));

SELECT dep.dept_no,dep.dept_name,COUNT(dep.dept_no)AS sum
FROM departments dep INNER JOIN dept_emp dept ON dep.dept_no=dept.dept_no
INNER JOIN salaries s ON s.emp_no=dept.emp_no
GROUP BY dep.dept_no

23.對所有員工的薪水按照salary進行按照1-N的排名

題目描述: 對所有員工的當前(to_date='9999-01-01')薪水按照salary進行按照1-N的排名负间，相同salary并列且按照emp_no升序排列 CREATE TABLEsalaries(emp_noint(11) NOT NULL,salaryint(11) NOT NULL,from_datedate NOT NULL,to_datedate NOT NULL, PRIMARY KEY (emp_no,from_date));

沒寫出來
答案如下

Image.png

24.獲取所有非manager員工當前的薪水情況

題目描述：
獲取所有非manager員工當前的薪水情況偶妖，給出dept_no、emp_no以及salary 政溃，當前表示to_date='9999-01-01'
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));

考慮思路：
①將dept_emp和salaries連接趾访，salaeies中就有所有的員工名單，所以不用employees董虱。
②過濾掉經(jīng)理

SELECT de.dept_no, s.emp_no, s.salary 
FROM dept_emp AS de INNER JOIN salaries AS s 
ON s.emp_no = de.emp_no 
AND s.to_date = '9999-01-01'
WHERE de.emp_no NOT IN (SELECT emp_no 
                        FROM dept_manager 
                        WHERE to_date = '9999-01-01')