練習(xí)SQL利器，牛客網(wǎng)SQL實戰(zhàn)題庫，9~16題
牛客網(wǎng)SQL實戰(zhàn)網(wǎng)址：https://www.nowcoder.com/ta/sql
持續(xù)更新——記錄自己在?？途W(wǎng)SQL的做題過程
9.獲取所有部門當(dāng)前manager的當(dāng)前薪水情況，給出dept_no, emp_no以及salary
題目描述：
獲取所有部門當(dāng)前manager的當(dāng)前薪水情況，給出dept_no, emp_no以及salary，當(dāng)前表示to_date='9999-01-01'
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));
應(yīng)該考慮的問題：
①同一emp_no會在salaries表中對應(yīng)多條漲薪記錄
SELECT d.dept_no,d.emp_no,s.salary
FROM dept_manager d,salaries s
WHERE d.emp_no = s.emp_no
AND d.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
注：①總感覺這種做了表連接的，總得將篩選的條件語句寫兩次
10.獲取所有非manager的員工emp_no
題目描述：
獲取所有非manager的員工emp_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));
SELECT e.emp_no
FROM employees e
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
注：
①子查詢在這里用到了
②?？途W(wǎng)的wasrehpic說，用LEFT JOIN，然后篩選NULL也行，wasrehpic寫的解析很棒，給了我很多幫助。
11.獲取所有員工當(dāng)前的manager
題目描述:
獲取所有員工當(dāng)前的manager，如果當(dāng)前的manager是自己的話結(jié)果不顯示，當(dāng)前表示to_date='9999-01-01'。
結(jié)果第一列給出當(dāng)前員工的emp_no,第二列給出其manager對應(yīng)的manager_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 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));
需要考慮的問題：
①兩個表應(yīng)該以dept_no進(jìn)行聯(lián)結(jié)而不是emp_no。
②需要注意如果當(dāng)前的manager是自己的話結(jié)果不顯示。
SELECT e.emp_no,m.emp_no AS manager_no
FROM dept_emp e,dept_manager m
WHERE e.dept_no = m.dept_no
AND e.to_date='9999-01-01'
AND m.to_date='9999-01-01'
AND e.emp_no != m.emp_no
注：①這里用到了重命名AS
12.獲取所有部門中當(dāng)前員工薪水最高的相關(guān)信息
題目描述
獲取所有部門中當(dāng)前員工薪水最高的相關(guān)信息，給出dept_no, emp_no以及其對應(yīng)的salary
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));
需要考慮的問題：
①需要用到分組函數(shù)GROUPBY，并以部門進(jìn)行分組。
②需要用到MAX函數(shù)取出最大的薪水值
③所以也就是一個分組加最大值函數(shù)
SELECT d.dept_no,d.emp_no,MAX(s.salary)
FROM dept_emp d,salaries s
WHERE d.emp_no = s.emp_no
AND d.to_date='9999-01-01'
AND s.to_date='9999-01-01'
GROUP BY d.dept_no
注：考慮的時候應(yīng)該先考慮分組然后再考慮每個取值的函數(shù)，所以想著最好是倒著去想，想完了再正著去寫。
13. 從titles表獲取按照title進(jìn)行分組
題目描述:
從titles表獲取按照title進(jìn)行分組，每組個數(shù)大于等于2，給出title以及對應(yīng)的數(shù)目t。
CREATE TABLE IF NOT EXISTS "titles" (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
需要考慮的問題：WHERE后邊不可跟COUNT()函數(shù)
SELECT title,COUNT(title) AS t
FROM titles
GROUP BY title
HAVING t>=2
14.從titles表獲取按照title進(jìn)行分組，注意對于重復(fù)的emp_no進(jìn)行忽略。
題目描述:
從titles表獲取按照title進(jìn)行分組，每組個數(shù)大于等于2，給出title以及對應(yīng)的數(shù)目t。
注意對于重復(fù)的emp_no進(jìn)行忽略。
CREATE TABLE IF NOT EXISTS "titles" (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
需要考慮的問題：一個title內(nèi)可能存在三個emp_no,其中兩個有可能是一樣的，所以就需要去重統(tǒng)計。
SELECT title,COUNT(DISTINCT emp_no) AS t
FROM titles
GROUP BY title
HAVING t>1
15.查找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 = 1
AND last_name <> 'Mary'
ORDER BY hire_date DESC;
16.統(tǒng)計出當(dāng)前各個title類型對應(yīng)的員工當(dāng)前薪水對應(yīng)的平均工資
題目描述
統(tǒng)計出當(dāng)前各個title類型對應(yīng)的員工當(dāng)前薪水對應(yīng)的平均工資。結(jié)果給出title以及平均工資avg。
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 IF NOT EXISTS "titles" (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
SELECT t.title,AVG(s.salary) AS avg
FROM titles t,salaries s
WHERE t.emp_no = s.emp_no
AND t.to_date='9999-01-01'
AND s.to_date='9999-01-01'
GROUP BY t.title;
