SQL サブクエリ | データベース分析 | 人事データベース

はじめに

このプロジェクトでは、人事データベースの従業員 (emp) テーブルと部署 (dept) テーブルから関連する情報を取得するためのサブクエリの使い方を学びます。複雑なSQLクエリを書いて複数のテーブルからのデータにアクセスし分析する練習を行います。

👀 プレビュー

🎯 タスク

このプロジェクトで学ぶことは以下の通りです。

MySQLサーバを起動して人事データベースをインポートする方法
サブクエリを使って給与の最も高い従業員を見つける方法
特定の部署の従業員が会社全体に占める割合を計算する方法
サブクエリを使って特定の場所で働いているすべての従業員を取得する方法
部署内の平均給与を超える給与をもつ従業員を見つける方法

🏆 成果

このプロジェクトを完了すると、以下のことができるようになります。

SQLにおけるサブクエリの概念と使い方を理解する
複数のテーブルのデータを組み合わせた複雑なSQLクエリを書く
サブクエリを使ってデータベースから意味のある洞察を分析し抽出する
実際のシナリオでSQLスキルを展示する

MySQLを起動してデータベースをインポートする

このステップでは、MySQLサーバを起動して人事データベースをインポートする方法を学びます。

パスワードなしでsudoコマンドを使ってMySQLサーバを起動します。

sudo service mysql start

MySQLプロンプトにアクセスします。

sudo mysql

personnel.sqlファイルのデータをMySQLデータベースにインポートします。

SOURCE /home/labex/project/personnel.sql;

これにより、personnelデータベースが作成され、必要なテーブルとデータが格納されます。

給与の最も高い従業員をクエリする

このステップでは、サブクエリを使って給与の最も高い従業員の名前を取得する方法を学びます。

/home/labex/project ディレクトリに subquery.sql という名前の新しいファイルを作成します。
subquery.sql ファイルに、次のSQLクエリを追加します。

-- 給与の最も高い従業員を取得するクエリ
SELECT ename AS `Name` FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

このクエリはまず、サブクエリを使って emp テーブルの最大給与を見つけ、その最大給与を持つ従業員の ename（従業員名）を選択します。

部署10の従業員の割合を計算する

このステップでは、サブクエリを使って部署10の従業員が会社全体に占める割合を計算する方法を学びます。

subquery.sql ファイルに、次のSQLクエリを追加します。

-- 部署10の従業員の割合を計算するクエリ
SELECT d.dname AS Department, COUNT(e.empno) / (SELECT COUNT(empno) FROM emp) AS Proportion
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.deptno = 10;

このクエリはまず、emp テーブルと dept テーブルを結合して部署10の従業員数をカウントします。そして、このカウント数を emp テーブルの従業員総数（サブクエリを使って）で割って割合を計算します。

ニューヨークに勤務する従業員を取得する

このステップでは、サブクエリを使ってニューヨークに勤務するすべての従業員を取得する方法を学びます。

subquery.sql ファイルに、次のSQLクエリを追加します。

-- ニューヨークに勤務するすべての従業員を取得するクエリ
SELECT e.*
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.loc = 'NEW YORK';

このクエリは、emp テーブルと dept テーブルを結合して、その部署がニューヨークにある従業員を取得します。

部署平均給与を超える給与を持つ従業員を取得する

このステップでは、サブクエリを使って、その部署の平均給与を超える給与を持つ従業員を取得する方法を学びます。

subquery.sql ファイルに、次のSQLクエリを追加します。

-- その部署の平均給与を超える給与を持つ従業員を取得するクエリ
SELECT e.* FROM emp e
JOIN (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) AS avg
ON e.deptno = avg.deptno
WHERE e.sal > avg.avg_sal;

このクエリはまず、サブクエリを使って各部署の平均給与を計算します。そして、このサブクエリを emp テーブルと結合して、その部署の平均給与を超える給与を持つ従業員を選択します。

これらの手順を完了した後、subquery.sql ファイルにはプロジェクト要件を満たすためのすべての必要なSQLクエリが含まれている必要があります。

ファイルを保存します。
MySQLプロンプトで、次のコマンドを実行して subquery.sql スクリプトを実行します。

SOURCE /home/labex/project/subquery.sql;

次の出力が表示されるはずです。

✨ 解答を確認して練習

まとめ

おめでとうございます！このプロジェクトを完了しました。実験技術を向上させるために、LabExでさらに多くの実験を行って練習してください。

データ分析のための SQL サブクエリ