牛客『データベースSQL実戦』練習まとめ（一）

6745 ワード

SQL

1.最終社員のすべての情報を検索する
難しい点：一番遅いということは、並べ替え後に一本だけ選んでください。LIMITが使えます。

SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 1

2.入社社員の時間ランキングの下から3番目の社員のすべての情報を検索する。
難点：後ろから3番目です。まだLIMITが使えます。

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

なお、LIMITの使い方には、1.LIMIT mは、m行記録よりも多く戻ることを示しています。2.LIMIT m-1,n-(m-1)は、m-nに戻るレコードを表し、m-1から(m-1個を除く)どれだけのレコードがオフセットされているかを理解することができる。例えば3行目に戻るなら、LIMIT 2,1であっても良い。2行目から4行目に戻るなら、LIMIT 1,3です。

3.各部門の現在（toudte='9999-01-01'）を検索して、現在の給与の詳細及びその対応部門番号dept_no

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

4.配属された全ての部署の従業員を検索するラスター。nameとfirst_name

SELECT e.last_name,e.first_name,d.dept_no 
FROM employees AS e,dept_emp AS d
WHERE d.emp_no = e.emp_no

5.従業員全員のラストを検索する。nameとfirst_name及び対応部署番号dept_いいえ、具体的な部門を割り当てていない従業員を展示することも含みます。
難しい点：配属されていない部門の従業員を含む、つまり一部の従業員はemployes表にいますが、deptにいません。emp表は、left join/Rightjoinが使えます。

SELECT e.last_name,e.first_name,d.dept_no
FROM employees AS e 
LEFT JOIN dept_emp AS d
ON d.emp_no = e.emp_no

6.全社員の入社時の給料状況を調べて、emp_を提供する。noとsalaryは、emp_に従います。ノ逆順
難点：入社時、すなわち給料のfromdate=hire_ダテ

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

7.給与の上昇幅が15回を超える従業員番号emp_ノ及びその対応する上げ幅回数t
難しい点：ここでは給料が一回上がると記録が多くなります。上げ幅はcount関数で統計できます。
注意：SQL文の実行順序は、GROUT BY->アグリゲーション関数（sum、count、max...）->HAVINGで、countを実行する時にはgroup byになりますので、count()のすべての記録はOKです。

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

8.従業員全員の現在（toudte='9999-01-01'）の具体的な給料のsalary状況を探し出して、同じ給料に対して一回だけ表示して、逆順で表示します。
難点：同じ給料は一回しか表示されません。DISTINCT

SELECT DISTINCT salary
FROM salaries
WHERE to_date='9999-01-01'
ORDER BY salary DESC

9.すべての部門の現在のマネージャーの給料状況を取得し、dept_を提供する。の、emp_no及びsalaryは、現在はto_を表しています。date='9999-01-01'

SELECT d.dept_no,d.emp_no,s.salary
FROM dept_manager AS d,salaries AS s
WHERE d.emp_no = s.emp_no
AND s.to_date = '9999-01-01'
AND d.to_date = '9999-01-01'

10.すべての非managerの従業員emp_0026 quot;を取得する。no
難点：非manager。managerではない従業員の不在dept_manager表は、left join/Rightjoinで二つの表を関連づけてdept_を選ぶことができます。noは空の記録です

SELECT emp_no
FROM (SELECT * FROM employees AS e 
LEFT JOIN dept_manager AS d
ON e.emp_no = d.emp_no)
WHERE dept_no is NULL

11.全従業員の現在のmanagerを取得し、現在のmanagerが自分であれば結果は表示されません。現在はto_を表しています。date='9999-01-01'結果の第一列は現在の従業員にemp_を与えます。ノ、二列目はそのmanagerに対応するmanager_を与えます。ノ・ノ
難点：managerは自分ではない。従業員番号を通して部門経理表の従業員番号と同じにして選別できます。

SELECT e.emp_no,m.emp_no AS 'manager_no'
FROM dept_emp AS e,dept_manager AS m
WHERE e.dept_no = m.dept_no
AND e.emp_no<>m.emp_no
AND m.to_date = '9999-01-01'

12.全部門の中で現在従業員の給料が一番高い情報を取得し、dept_を提供する。の、emp_noとその対応するsalary
難点：部門別で一番給料が高い社員は、グループby再max()

SELECT dept_no,d.emp_no,MAX(salary) AS salary
FROM dept_emp AS d 
INNER JOIN salaries AS s
ON d.emp_no = s.emp_no
WHERE d.to_date = '9999-01-01'
GROUP BY d.dept_no

13.titlesテーブルから取得し、titleに従ってグループ化し、各グループの数が2以上であり、title及び対応する数tを与える。
難点：グループ分け後のグループの個数。7番の問題と似ています。group by後、各グループの記録数を統計します。記録数だけを選んで2以上のものを選びます。グループ化されているので、ここのcountはcount(*)に相当しますが、空き値があるときは使えません。count(*)は空の値を集計します。

SELECT title,Count(emp_no) AS t 
FROM titles
GROUP BY title
HAVING COUNT(emp_no) >= 2

14.titlesテーブルから取得し、titleに従ってグループ化し、各グループの数は2以上であり、title及び対応する数tを与える。繰り返しのemp_に注意してください。ノは無視します
難点：計数する時は重複のemp_を無視します。ノ・ノ重複値をフィルタしますので、count()のは必ずemp_いいえ、**ではありません。重ねればdistinctが使えます。

SELECT title,COUNT(DISTINCT emp_no) AS t
FROM titles
GROUP BY title
HAVING COUNT(DISTINCT emp_no) >= 2

15.employes表のすべてのemp_を検索します。NOは奇数で、かつlast_nameはMaryの従業員の情報ではなく、hire_に従います。ダテ逆順配列
難点：奇数を判断する。残りはゼロでないと奇数です。

SELECT * FROM employees
WHERE last_name <> 'Mary'
AND emp_no%2 <> 0
ORDER BY hire_date DESC

16.現在の各タイプに対応する従業員の現在の給料に対応する平均賃金を集計します。その結果、title及び平均給与avgが与えられた。

SELECT title,avg(salary)
FROM titles AS t,salaries AS s
WHERE s.emp_no = t.emp_no
AND s.to_date = '9999-01-01'
and t.to_date = '9999-01-01'
GROUP BY t.title

17.現在（toudiate='9999-01-01'）の給与を二番目に多く取得した社員のemp_noとそれに対応する給料salary
難点：2番目の多さ。LIMIT

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

18.現在の給与（toudte='9999-01-01'）を検索すると、2番目に多い従業員番号emp_ノ、給料salary、last_nameとfirst_name、order byを使用してはいけません。
難しい点：2番目のものを選ぶが、order byで並べ替えることはできない。
二つの考えがあります。（1）一番多いものを選んで排除します。（2）一番多いのを選んで、一番多いのより小さいのを選んでください。
(1)

SELECT e.emp_no,MAX(salary),last_name,first_name
FROM employees AS e,salaries AS s
WHERE e.emp_no = s.emp_no
AND to_date = '9999-01-01'
AND salary Not IN (SELECT MAX(salary) FROM salaries AS s
WHERE s.to_date = '9999-01-01')

(2)

SELECT e.emp_no,MAX(salary),last_name,first_name
FROM employees AS e,salaries AS s
WHERE e.emp_no = s.emp_no
AND to_date = '9999-01-01'
AND s.salary < (SELECT MAX(salary) FROM salaries AS s
WHERE s.to_date = '9999-01-01')

19.全従業員のラストを検索する。nameとfirst_name及び対応のdept_nameには、部署の割り当てがない社員も含まれています。
難点：多表連結はまだleft jinを使います。まずdepartmentsとdeptをください。emp 2つの表を連結して表tにして、表employeesleft jin表tを割り当てていない部門の従業員を獲得します。

SELECT e.last_name,e.first_name,t.dept_name
FROM employees e LEFT JOIN 
(SELECT * FROM departments d,dept_emp de
WHERE d.dept_no = de.dept_no) t
ON e.emp_no = t.emp_no

20.従業員番号emp_を検索するノは10001入社以来の給与のsalary上昇幅growthです。
難点：初日入社と最後の日の給料を調べる。order byを使ってもいいです。min、maxも使えます。

SELECT (a.salary - b.salary) AS growth
FROM (SELECT salary FROM salaries WHERE emp_no = 10001 
ORDER BY to_date DESC LIMIT 1 ) AS a,
(SELECT salary FROM salaries WHERE emp_no = 10001 
      ORDER BY to_date LIMIT 1 ) AS b

21.全従業員の入社以来の給与の上昇率を調べ、従業員番号emp_ノ及びそれに対応する給与の上昇幅growthは、growthによって昇順されます。
難点：社員全員の入社給料を表にして、今の給料を表にして、最後に相殺します。入社はfrom_ですdate=hire_date、現在はto_です。date='9999-01-01'

SELECT now.emp_no,(now.salary - old.salary) AS growth
FROM (SELECT s.emp_no,s.salary FROM employees AS e INNER JOIN salaries AS s 
      ON e.emp_no = s.emp_no AND s.from_date = e.hire_date) AS old,
(SELECT s.emp_no,s.salary FROM employees AS e INNER JOIN salaries AS s 
ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01') AS now
WHERE old.emp_no = now.emp_no
ORDER BY growth

ウェブフレームワークweb