SQL語法總結(jié)：http://www.w3school.com.cn/sql/sql_func_min.asp
SQL練習(xí)：https://www.nowcoder.com/ta/sql

1.

查找最晚入職員工的所有信息
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 * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees)

或

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 1

2.

查找入職員工時(shí)間排名倒數(shù)第三的員工所有信息
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));

LIMIT m,n : 表示從第m+1條開始，取n條數(shù)據(jù)；
LIMIT n ： 表示從第0條開始，取n條數(shù)據(jù)，是limit(0,n)的縮寫。

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1

3.

查找各個(gè)部門當(dāng)前(to_date='9999-01-01')領(lǐng)導(dǎo)當(dāng)前薪水詳情以及其對(duì)應(yīng)部門編號(hào)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));

SELECT s.*, dept_no
FROM salaries s 
INNER JOIN dept_manager d
ON d.emp_no = s.emp_no 
WHERE s.to_date = '9999-01-01'
AND d.to_date = '9999-01-01'

4.

查找所有已經(jīng)分配部門的員工的last_name和first_name
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 e.last_name, e.first_name, d.dept_no 
FROM dept_emp d, employees e
WHERE d.emp_no = e.emp_no

5.

查找所有員工的last_name和first_name以及對(duì)應(yīng)部門編號(hào)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 e.last_name, e.first_name, d.dept_no
FROM employees e
LEFT JOIN dept_emp d
ON e.emp_no = d.emp_no

6.查找所有員工入職時(shí)候的薪水情況

查找所有員工入職時(shí)候的薪水情況，給出emp_no以及salary， 并按照emp_no進(jìn)行逆序
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));

SELECT e.emp_no, s.salary
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
AND hire_date = from_date
ORDER BY e.emp_no DESC

查找薪水漲幅超過15次的員工號(hào)emp_no以及其對(duì)應(yīng)的漲幅次數(shù)t
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));

HAVING用于對(duì)聚合結(jié)果做限制。這里有一個(gè)錯(cuò)誤，漲薪大于15次，那么t應(yīng)該>16。

SELECT emp_no, COUNT(*) AS t
FROM salaries s
GROUP BY emp_no
HAVING t > 15

找出所有員工當(dāng)前(to_date='9999-01-01')具體的薪水salary情況，對(duì)于相同的薪水只顯示一次,并按照逆序顯示
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 DISTINCT salary
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary DESC

.查找employees表

查找employees表所有emp_no為奇數(shù)，且last_name不為Mary的員工信息，并按照hire_date逆序排列
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 * 
FROM employees
WHERE emp_no % 2 != 0
AND last_name != 'Mary'
ORDER BY hire_date DESC