1、查找最晚入職員工的所有資訊
【題解】
hire_date可能存在重複值，是以需要找到hire_date的最大值，然後再篩選，才能hire_date最晚的記錄都篩選出來。
【代碼】
1 SELECT * FROM employees
2 WHERE hire_date = (SELECT MAX(hire_date) FROM employees) View Code
2、查找入職員工時間排名倒數第三的員工所有資訊
【題解】
還是hire_date可能存在重複值問題，是以需要先找到第三晚的hire_date（此處排序記得用distinct去重），然後再進行篩選。
【代碼】
1 SELECT * FROM employees
2 WHERE hire_date = (SELECT DISTINCT hire_date FROM employees
3 ORDER BY hire_date DESC LIMIT 2, 1) View Code
3、查找目前薪水詳情以及部門編号dept_no
【題解】
這題好坑哦，因為題目中說的是“薪水詳情以及其對應部門編号dept_no”，是以salaries表是主表，要寫在前面？（我對這個思路并不是很贊同）
但其實我覺得是背景沒有排序問題，隻要把emp_no排個序也能過。
【代碼】
SELECT s.*, d.dept_no
FROM salaries s, dept_manager d
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' AND d.emp_no = s.emp_no View Code
1 SELECT s.*, d.dept_no
2 FROM dept_manager d, salaries s
3 WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' AND d.emp_no = s.emp_no
4 ORDER BY s.emp_no View Code
4、查找所有已經配置設定部門的員工的last_name和first_name
【題解】
也就是說有的員工不一定被配置設定了部門，那麼隻要将部門表左連接配接到員工表，即部門表上的資訊都會有，但部門表上沒有員工表上有的資訊就不會被篩出，符合題目所求。
【代碼】
1 SELECT e.last_name, e.first_name, d.dept_no
2 FROM dept_emp d LEFT JOIN employees e ON e.emp_no = d.emp_no View Code
5、查找所有員工的last_name和first_name以及對應部門編号dept_no
【題解】
跟上一題剛好反一下，這裡是要把員工表左連接配接到部門表，這樣不管員工是否有對應的部門，都能被顯示出來。
【代碼】
1 SELECT e.last_name, e.first_name, d.dept_no
2 FROM employees e LEFT JOIN dept_emp d ON e.emp_no = d.emp_no View Code
6、查找所有員工入職時候的薪水情況
【題解】
這裡需要注意一下，因為是給出每個員工入職時的薪資，是以還需要将加上這個條件e.hire_date = s.from_date。
【代碼】
1 SELECT e.emp_no, s.salary
2 FROM employees e, salaries s
3 WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date
4 ORDER BY e.emp_no DESC View Code
7、查找薪水漲幅超過15次的員工号emp_no以及其對應的漲幅次數t
【題解】
GROUP BY配合聚合函數使用，按照emp_no分類後，COUNT記錄每個emp_no的薪水漲幅次數，最後選出大于15的即可。
COUNT語句後面要跟HAVING哦。
如果對GROUP BY和聚合函數的使用不是很了解的話可以戳這裡，我感覺講的蠻好的。
【代碼】
1 SELECT emp_no, COUNT(to_date) AS t
2 FROM salaries GROUP BY emp_no HAVING t > 15 View Code
8、找出所有員工目前薪水salary情況
【題解】
DISTINCT：如果作用于某一列，同一列的相同值隻會出現一次，如果作用于所有列，那麼所有列的相同值都相同才相同（可用于整張表去重）。
ORDER BY：ORDER BY col DESC 按照col列降序排，ASC為升序。
【代碼】
1 SELECT DISTINCT salary
2 FROM salaries WHERE to_date = '9999-01-01'
3 ORDER BY salary DESC View Code
9、擷取所有部門目前manager的目前薪水情況
【題解】
類似第3題
【代碼】
1 SELECT d.dept_no, d.emp_no, s.salary
2 FROM dept_manager d, salaries s
3 WHERE d.emp_no = s.emp_no AND d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' View Code
10、擷取所有非manager的員工emp_no
【題解】
使用NOT IN選出在employees但不在dept_manager中的emp_no記錄，NOT IN的話就是顧名思義啦，“不在”的意思。
【代碼】
1 SELECT e.emp_no FROM employees e
2 WHERE emp_no NOT IN(SELECT d.emp_no FROM dept_manager d) View Code
11、擷取所有員工目前的manager
【題解】
題意中明确說明如果是manager自己的話不用顯示，是以需要加上這一句e.emp_no != m.emp_no，然後按要求連接配接兩張表查詢就可以啦。
【代碼】
1 SELECT e.emp_no, m.emp_no AS manager_no
2 FROM dept_emp e, dept_manager m
3 WHERE e.dept_no = m.dept_no AND e.to_date = '9999-01-01'
4 AND m.to_date = '9999-01-01' AND e.emp_no != m.emp_no View Code
12、擷取所有部門中目前員工薪水最高的相關資訊
【題解】
可以通過兩步來了解。
第一步：通過emp_no将兩個表連接配接，并挑選出所有部門目前員工工薪，目前是9999-01-01，題裡沒說，是個小bug。
第二步：因為需要給出所有部門中工薪最高的資訊，是以我們按照部門分組，将最高的工薪選出來。
【代碼】
1 SELECT d.dept_no, d.emp_no, s.salary
2 FROM dept_emp d, salaries s
3 WHERE d.emp_no = s.emp_no AND d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
4 GROUP BY d.dept_no HAVING MAX(s.salary) View Code
13、從titles表擷取按照title進行分組
【題解】
第一步：根據title将表進行分組
第二步：分組後将具有相同title的記錄計數，傳回>=2的即可。
【代碼】
1 SELECT title, COUNT(title) AS t
2 FROM titles
3 GROUP BY title HAVING t >= 2 View Code
14、從titles表擷取按照title進行分組，注意對于重複的emp_no進行忽略。
【題解】
這題首先要了解清楚題目的意思，題意是想我們找到按照title分組後，每組個數大于等于2（并且其中不能包含重複的emp_no）
比如title emp_no
1 1
1 1
這樣的COUNT隻能算1個哦。是以還是分2步走。
第一步：按照title分組
第二步：利用DISTINCT去掉重複emp_no，然後計數。
【代碼】
1 SELECT title, COUNT(DISTINCT emp_no) AS t
2 FROM titles
3 GROUP BY title HAVING t >= 2 View Code
15、查找employees表
【題解】
這題比較簡單，直接按題意做就可以啦。
【代碼】
1 SELECT * FROM employees
2 WHERE emp_no % 2 = 1 AND last_name != 'Mary'
3 ORDER BY hire_date DESC View Code
16、統計出目前各個title類型對應的員工目前薪水對應的平均工資
【題解】
還是分2步來了解。
第一步：先通過emp_no将兩個表連接配接
第二步：按照title分組，并對同屬一個title的salay進行avg運算。
【代碼】
1 SELECT t.title, AVG(s.salary) AS avg
2 FROM titles t, salaries s
3 WHERE t.emp_no = s.emp_no AND t.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
4 GROUP BY t.title View Code
17、擷取目前薪水第二多的員工的emp_no以及其對應的薪水salary
【題解】
類似第二題
【代碼】
1 SELECT emp_no, salary FROM salaries
2 WHERE to_date = '9999-01-01' AND salary = (
3 SELECT DISTINCT salary FROM salaries
4 ORDER BY salary DESC LIMIT 1, 1
5 ) View Code
18、擷取目前薪水第二多的員工的emp_no以及其對應的薪水salary，不準使用order by
【題解】
仍然2步走。
第一步：先把兩個表通過emp_no連接配接起來
第二步：因為這裡不能用ORDER BY，是以不能再用上面的方法了，我們可以這麼想，我先找出一個最大值salary，然後再找比這個最大值小的最大值，那不就是次大值了嗎？
然後這裡要注意題意是說目前薪水第二多的員工，是以隻需s.to_date = '9999-01-01'即可。
【代碼】
1 SELECT e.emp_no, MAX(s.salary), e.last_name, e.first_name
2 FROM employees e, salaries s
3 WHERE s.salary < (SELECT MAX(salary) FROM salaries
4 WHERE to_date = '9999-01-01')
5 AND e.emp_no = s.emp_no AND s.to_date = '9999-01-01' View Code
19、查找所有員工的last_name和first_name以及對應的dept_name
【題解】
第一步：将employees表和dept_emp表通過emp_no進行左外連接配接，這樣不管有沒有配置設定部門的員工号對應的部門号就找出來了。
第二步：用同樣的方法就講dept_emp和departments左外連接配接，就能得到答案啦。
【代碼】
1 SELECT e.last_name, e.first_name, dp.dept_name
2 FROM (employees e LEFT JOIN dept_emp d ON e.emp_no = d.emp_no)
3 LEFT JOIN departments dp ON d.dept_no = dp.dept_no View Code
20、查找員工編号emp_no為10001其自入職以來的薪水salary漲幅值growth
【題解】
漲幅值即薪水的最大值 - 最小值，知道這個就可以做啦。
【代碼】
1 SELECT (MAX(salary) - MIN(salary)) AS growth
2 FROM salaries WHERE emp_no = '10001' View Code
21、查找所有員工自入職以來的薪水漲幅情況
【題解】
做到這題的時候會發現，這次要求的時候所有鹽工自入職以來的薪水漲幅情況，是以我們可以建立兩張表。
一張是目前員工的薪水，一張是員工入職時的薪水，再将兩張表連接配接一下排序就可以了。
【代碼】
1 SELECT now.emp_no, (now.salary - pre.salary) AS growth
2 FROM (SELECT e.emp_no, s.salary FROM employees e, salaries s
3 WHERE e.emp_no = s.emp_no AND s.to_date = '9999-01-01') AS now,
4 (SELECT e.emp_no, s.salary FROM employees e, salaries s
5 WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date) AS pre
6 WHERE now.emp_no = pre.emp_no
7 ORDER BY growth ASC View Code
22、統計各個部門對應員工漲幅的次數總和
【題解】
将三個表連接配接後，按照dept_no分組，計算有幾條salary記錄即可。
【代碼】
1 SELECT dp.dept_no, dp.dept_name, COUNT(s.salary) AS sum
2 FROM departments dp, dept_emp d, salaries s
3 WHERE dp.dept_no = d.dept_no AND d.emp_no = s.emp_no
4 GROUP BY dp.dept_no View Code
23、對所有員工的薪水按照salary進行按照1-N的排名
【題解】
這道題我覺得還是挺巧妙的，有必要收藏一下嘻嘻嘻。
首先要将兩張salaries表連接配接，先挑選出to_date = '9999-01-01'的記錄，然後注入靈魂的一句話就是s1.salary <= s2.salary，這句話是什麼意思呢？比如我在s1表中找到一個salary是100，在s2中就能找到對應的大于等于100的（100,100,300）這樣3條資料，然後用DISTINCT去重之後也就是2條，就是該salary的排名啦。
【代碼】
1 SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
2 FROM salaries s1, salaries s2
3 WHERE s1.to_date = '9999-01-01' AND s2.to_date = '9999-01-01' AND s1.salary <= s2.salary
4 GROUP BY s1.emp_no
5 ORDER BY s1.salary DESC, s1.emp_no ASC View Code
24、擷取所有非manager員工目前的薪水情況
【題解】
好像斌不需要用employees表？
因為我隻需從salaries表中找出不屬于dept_manager表的emp_no，然後讓salaries表和dept_emp連接配接就可以得到非manager員工目前的薪水情況啦。
【代碼】
1 SELECT d.dept_no, s.emp_no, s.salary
2 FROM dept_emp d, salaries s
3 WHERE s.emp_no NOT IN (SELECT emp_no FROM dept_manager) AND
4 s.emp_no = d.emp_no AND s.to_date = '9999-01-01' View Code
25、擷取員工其目前的薪水比其manager目前薪水還高的相關資訊
【題解】
首先将manager表和salaries表連接配接成一個新表ms，emp表和salaries表也連接配接成一個新表es。
然後就可以根據題意操作啦，隻要es.salary > ms.salary就可以了。
【代碼】
1 SELECT es.emp_no, ms.emp_no AS manager_no, es.salary AS emp_salary, ms.salary AS manager_salary
2 FROM (dept_emp AS d INNER JOIN salaries AS s ON d.emp_no = s.emp_no AND s.to_date = '9999-01-01')AS es,
3 (dept_manager AS dm INNER JOIN salaries AS s ON dm.emp_no = s.emp_no AND s.to_date = '9999-01-01')AS ms
4 WHERE es.emp_no NOT IN (SELECT emp_no FROM dept_manager) AND
5 es.dept_no = ms.dept_no AND es.salary > ms.salary View Code
26、彙總各個部門目前員工的title類型的配置設定數目
【題解】
這裡有幾個注意點。
（1）這裡統計的是目前員工的目前頭銜，是以t.to_date = '9999-01-01'，d.to_date = '9999-01-01'
（2）分組也有兩個參數，因為首先需要根據部門分組，再在部門裡面根據不同的title進行分組統計。
【代碼】
1 SELECT d.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
2 FROM departments dp, dept_emp d, titles t
3 WHERE dp.dept_no = d.dept_no AND d.emp_no = t.emp_no AND t.to_date = '9999-01-01'
4 AND d.to_date = '9999-01-01'
5 GROUP BY d.dept_no, t.title View Code
27、給出每個員工每年薪水漲幅超過5000的員工編号emp_no
【題解】
設s1表為員工漲薪之前的表，s2表為員工漲薪之後的表。那麼通過emp_no就可以把他們連接配接起來。然後這道題其實題意不是很明确，他說是每年，但事實上隻要年份差為1即可。
是以隻需要兩個表的to_date相差1，且滿足薪水漲幅超過5000就要把它選出來，另外不能忘記如果兩個表的from_date相差1，也是需要被選出來的。比如有兩個字段（2000-5-05,2001-6-13）和（2001-7-30,2001-8-12）也可以算是相差1年。
【代碼】
1 SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
2 FROM salaries s1, salaries s2
3 WHERE s1.emp_no = s2.emp_no
4 AND salary_growth > 5000
5 AND (strftime("%Y", s2.to_date) - strftime("%Y", s1.to_date) = 1
6 OR strftime("%Y", s2.from_date) - strftime("%Y", s1.from_date) = 1 )
7 ORDER BY salary_growth DESC View Code
28、查找描述資訊中包括robot的電影對應的分類名稱以及電影數目，而且還需要該分類對應電影數量
【題解】
這題我們可以把每個類别電影數目>=5的類别先找出來建立個虛表cc。
然後經過這幾個表的連接配接就可以得到答案啦。
【代碼】
1 SELECT c.name, COUNT(fc.film_id) AS num
2 FROM (SELECT category_id FROM film_category
3 GROUP BY category_id HAVING COUNT(film_id) >=5) AS cc,
4 film f, category c, film_category fc
5 WHERE f.description LIKE '%robot%' AND f.film_id = fc.film_id
6 AND fc.category_id = cc.category_id AND fc.category_id = c.category_id View Code
29、使用join查詢方式找出沒有分類的電影id以及名稱
【題解】
這題還是蠻簡單的，隻需要通過左連接配接找到有分類的電影id，然後加個NOT IN就可以了。
【代碼】
1 SELECT film_id, title
2 FROM film
3 WHERE film_id NOT IN(
4 SELECT f.film_id FROM film_category AS fc LEFT JOIN film AS f ON fc.film_id = f.film_id) View Code
30、用子查詢的方式找出屬于Action分類的所有電影對應的title,description
【題解】
簡單題，通過WHERE...IN...将3張表連接配接即可。
【代碼】
1 SELECT title, description FROM film
2 WHERE film_id IN (SELECT film_id FROM film_category WHERE category_id IN
3 (SELECT category_id FROM category WHERE name = 'Action')) View Code
31、擷取select * from employees對應的執行計劃
【題解】
explain模拟優化器執行SQL語句，在5.6以及以後的版本中，除過select，其他比如insert，update和delete均可以使用explain檢視執行計劃，進而知道mysql是如何處理sql語句，分析查詢語句或者表結構的性能瓶頸。
【代碼】
1 EXPLAIN SELECT * FROM employees View Code
32、将employees表的所有員工的last_name和first_name拼接起來作為Name
【題解】
MySQL、SQL Server、Oracle等資料庫支援CONCAT方法，
而本題所用的SQLite資料庫隻支援用連接配接符号"||"來連接配接字元串
【代碼】
mysql寫法：
1 SELECT CONCAT(last_name, " ", first_name) AS Name FROM employees View Code
SQLite寫法：
1 SELECT last_name ||" "|| first_name AS Name FROM employees View Code
33、建立一個actor表，包含如下列資訊
【題解】
擷取系統預設時間是datetime('now','localtime')
【代碼】
1 CREATE TABLE actor
2 (
3 actor_id smallint(5) NOT NULL PRIMARY KEY,
4 first_name varchar(45) NOT NULL,
5 last_name varchar(45) NOT NULL,
6 last_update timestamp NOT NULL DEFAULT (datetime('now','localtime'))
7 ) View Code
34、批量插入資料
【題解】
隻需要知道INSERT INTO 表名 VALUES的用法就可以啦
【代碼】
1 INSERT INTO actor VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33') ,
2 (2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33') View Code
35、批量插入資料，不使用replace操作
【題解】
需要知道INSERT OR IGNORE INTO 表名 VALUES...
即如果資料存在就忽略不插入
【代碼】
SQLite寫法：
1 INSERT OR IGNORE INTO actor VALUES
2 (3, 'ED', 'CHASE', '2006-02-15 12:34:33') View Code
Mysql寫法：
1 INSERT IGNORE INTO actor VALUES
2 (3, 'ED', 'CHASE', '2006-02-15 12:34:33') View Code
36、建立一個actor_name表
【題解】
把查詢到的内容建表！get√
【代碼】
SQLite寫法：
1 CREATE TABLE actor_name AS
2 SELECT first_name, last_name FROM actor View Code
MySQL寫法：
1 CREATE TABLE actor_name
2 SELECT first_name, last_name FROM actor View Code
37、對first_name建立唯一索引uniq_idx_firstname
【題解】
添加索引的兩種方式：（不知道為啥這題第二種過不了）
①修改表：ALTER TABLE 表名 ADD (UNIQUE) INDEX ON 索引名（屬性名）
②建立索引：CREATE (UNIQUE) INDEX 索引名 ON 表名(屬性名)
【代碼】
1 CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
2 CREATE INDEX idx_lastname ON actor(last_name);
3
4 //
5 ALTER TABLE actor ADD UNIQUE INDEX uniq_idx_firstname(first_name);
6 ALTER TABLE actor ADD INDEX idx_lastname(last_name); View Code
38、針對actor表建立視圖actor_name_view
【題解】
建立視圖：CREATE VIEW 視圖名 AS ...
【代碼】
1 CREATE VIEW actor_name_view AS
2 SELECT first_name AS first_name_v, last_name AS last_name_v
3 FROM actor View Code
39、針對上面的salaries表emp_no字段建立索引idx_emp_no
【題解】
SQLite中，使用INDEXED語句進行強制索引查詢：
SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005
MySQL中，使用 FORCE INDEX 語句進行強制索引查詢：
SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no = 10005
【代碼】
1 SELECT * FROM salaries
2 INDEXED BY idx_emp_no WHERE emp_no = '10005' View Code
40、在last_update後面新增加一列名字為create_date
【題解】
修改表結構：ARTER TABLE 表名
添加列：ADD COLUMN 列名 ...
删除列：DROP COLUMN 列名
修改列：CHANGE COLUMN 列名 新列名...
【代碼】
1 ALTER TABLE actor
2 ADD COLUMN create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00' View Code
轉載于:https://www.cnblogs.com/z1014601153/p/11310968.html