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

はじめに

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

👀 プレビュー

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;

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

SQL クエリ実行結果

まとめ

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

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