はじめに
SQLが苦手で、自分のレベルを測りたいと考えていたので、ChatGPTに依頼しSQLの問題を難易度別に10問出題してくれるように依頼をしました。1問ずつ出題してくれるので、テストのように問題を解きながら自分の実力を測ることができます。
僕が実際に入力したプロンプトは下記です。
データベースが苦手なのですが、sqlの問題を難易度別に10問出題し、今のレベルを測ってもらうことできますか？
1問ずつ出題してほしいです。
実際に出題された問題を以下に記載します。
問題と解答
問題 1 (初級)
問題内容:
次のようなemployeesテーブルがあります。
| id | name | department | salary |
|---|---|---|---|
| 1 | Alice | HR | 5000 |
| 2 | Bob | IT | 6000 |
| 3 | Charlie | IT | 7000 |
| 4 | Diana | HR | 4500 |
| 5 | Edward | IT | 8000 |
IT部門の社員の名前をすべて取得するSQLクエリを書いてください。
回答例を表示
SELECT name FROM employees WHERE department = 'IT';
解説:
WHERE句を使用して、department列が'IT'である行のみをフィルタリングしています。SELECT句ではname列のみを取得します。
問題 2 (初級)
問題内容:
次のようなordersテーブルがあります。
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 1 | 101 | 2024-01-01 | 250 |
| 2 | 102 | 2024-01-02 | 300 |
| 3 | 101 | 2024-01-03 | 150 |
| 4 | 103 | 2024-01-04 | 400 |
| 5 | 104 | 2024-01-05 | 500 |
customer_idが101の注文の合計金額を取得するSQLクエリを書いてください。
回答例を表示
SELECT SUM(amount) FROM orders WHERE customer_id = 101;
解説:
SUM関数を使用して、customer_idが101の行のamount列の合計を計算します。
問題 3 (初級)
問題内容:
次のようなproductsテーブルがあります。
| product_id | product_name | category | price |
|---|---|---|---|
| 1 | Laptop | Electronics | 1000 |
| 2 | Smartphone | Electronics | 700 |
| 3 | Desk | Furniture | 300 |
| 4 | Chair | Furniture | 150 |
| 5 | TV | Electronics | 1200 |
Electronicsカテゴリーの製品の中で最も高い価格を持つ製品の名前を取得するSQLクエリを書いてください。
回答例を表示
SELECT product_name
FROM products
WHERE category = 'Electronics'
AND price = (SELECT MAX(price) FROM products WHERE category = 'Electronics');
解説:
サブクエリを使用して、Electronicsカテゴリーの中で最も高い価格を持つ製品を特定し、その製品の名前を取得します。
問題 4 (中級)
問題内容:
次のようなstudentsテーブルがあります。
| student_id | student_name | grade | age |
|---|---|---|---|
| 1 | John | A | 15 |
| 2 | Mary | B | 16 |
| 3 | Steve | A | 17 |
| 4 | Anna | C | 16 |
| 5 | David | B | 15 |
各gradeごとに年齢の平均値を計算し、gradeとその平均年齢を表示するSQLクエリを書いてください。
回答例を表示
SELECT grade, AVG(age) AS average_age
FROM students
GROUP BY grade;
解説:
GROUP BY句を使用してgradeごとにグループ化し、AVG関数を使用して年齢の平均を計算します。
問題 5 (中級)
問題内容:
次のようなemployeesテーブルがあります。
| employee_id | name | department | salary |
|---|---|---|---|
| 1 | Alice | HR | 5000 |
| 2 | Bob | IT | 6000 |
| 3 | Charlie | IT | 7000 |
| 4 | Diana | HR | 4500 |
| 5 | Edward | IT | 8000 |
各部門（department）ごとに平均給与（salary）を計算し、部門とその平均給与を表示するSQLクエリを書いてください。ただし、平均給与の高い順に結果をソートしてください。
回答例を表示
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC;
解説:
GROUP BY句を使用してdepartmentごとにグループ化し、AVG関数を使って給与の平均を計算し、ORDER BY句で降順にソートします。
問題 6 (中級)
問題内容:
次のようなsalesテーブルがあります。
| sale_id | product_id | sale_date | quantity | total_amount |
|---|---|---|---|---|
| 1 | 101 | 2024-01-01 | 5 | 500 |
| 2 | 102 | 2024-01-02 | 3 | 450 |
| 3 | 101 | 2024-01-03 | 4 | 400 |
| 4 | 103 | 2024-01-04 | 2 | 300 |
| 5 | 101 | 2024-01-05 | 6 | 600 |
product_idごとの販売数量（quantity）の合計を計算し、販売数量が10以上の製品のproduct_idとその合計数量を表示するSQLクエリを書いてください。
回答例を表示
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id
HAVING SUM(quantity) >= 10;
解説:
GROUP BY句を使用してproduct_idごとにグループ化し、SUM関数で数量の合計を計算します。HAVING句を使用して、合計数量が10以上の製品のみを表示します。
問題 7 (上級)
問題内容:
次のようなemployeesテーブルとdepartmentsテーブルがあります。
employees テーブル
| employee_id | name | department_id |
|---|---|---|
| 1 | Alice | 1 |
| 2 | Bob | 2 |
| 3 | Charlie | 1 |
| 4 | Diana | 3 |
| 5 | Edward | 2 |
departments テーブル
| department_id | department_name |
|---|---|
| 1 | HR |
| 2 | IT |
| 3 | Finance |
各部門の名前（department_name）と、その部門に所属する社員数を表示するSQLクエリを書いてください。社員がいない部門も表示し、社員数は0と表示してください。
回答例を表示
SELECT B.department_name, COUNT(A.employee_id) AS employee_count
FROM departments AS B
LEFT JOIN employees AS A ON A.department_id = B.department_id
GROUP BY B.department_name;
解説:
LEFT JOINを使用して、departmentsテーブルを基準にemployeesテーブルと結合します。COUNT関数で各部門の社員数をカウントし、GROUP BY句で部門名ごとにグループ化します。社員がいない部門も表示され、その社員数は0になります。
問題 8 (上級)
問題内容:
次のようなordersテーブルとcustomersテーブルがあります。
orders テーブル
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 1 | 101 | 2024-01-01 | 250 |
| 2 | 102 | 2024-01-02 | 300 |
| 3 | 101 | 2024-01-03 | 150 |
| 4 | 103 | 2024-01-04 | 400 |
| 5 | 104 | 2024-01-05 | 500 |
customers テーブル
| customer_id | customer_name |
|---|---|
| 101 | John Doe |
| 102 | Jane Smith |
| 103 | Alice Johnson |
| 104 | Michael Brown |
各顧客（customer_name）ごとの総注文金額（amount）を計算し、総注文金額の高い順に並べ替えて表示するSQLクエリを書いてください。
回答例を表示
SELECT B.customer_name, SUM(A.amount) AS total_amount
FROM orders AS A
LEFT JOIN customers AS B ON A.customer_id = B.customer_id
GROUP BY B.customer_name
ORDER BY total_amount DESC;
解説:
LEFT JOINを使用して、ordersテーブルとcustomersテーブルを結合します。SUM関数で各顧客の注文金額の合計を計算し、GROUP BY句で顧客名ごとにグループ化します。ORDER BY句を使用して、総注文金額の高い順に並べ替えます。
問題 9 (上級)
問題内容:
次のようなemployeesテーブルとsalariesテーブルがあります。
employees テーブル
| employee_id | name | hire_date |
|---|---|---|
| 1 | Alice | 2019-01-10 |
| 2 | Bob | 2020-05-22 |
| 3 | Charlie | 2021-07-15 |
| 4 | Diana | 2022-11-30 |
| 5 | Edward | 2023-03-10 |
salaries テーブル
| salary_id | employee_id | salary | effective_date |
|---|---|---|---|
| 1 | 1 | 5000 | 2023-01-01 |
| 2 | 1 | 5500 | 2024-01-01 |
| 3 | 2 | 6000 | 2023-01-01 |
| 4 | 3 | 7000 | 2024-01-01 |
| 5 | 4 | 4500 | 2023-01-01 |
| 6 | 5 | 5000 | 2024-01-01 |
最新の給与（salary）を持つ社員の名前とその給与を取得するSQLクエリを書いてください。
回答例を表示
SELECT A.name, B.salary
FROM employees AS A
JOIN (
SELECT employee_id, salary
FROM salaries
WHERE (employee_id, effective_date) IN (
SELECT employee_id, MAX(effective_date)
FROM salaries
GROUP BY employee_id
)
) AS B ON A.employee_id = B.employee_id;
解説:
サブクエリを使用して、各社員の最新のeffective_dateに基づいて給与を取得し、それをメインクエリでemployeesテーブルと結合して、最新の給与を持つ全ての社員の名前と給与を取得します。
問題 10 (上級)
問題内容:
次のようなstudentsテーブルとcoursesテーブル、enrollmentsテーブルがあります。
students テーブル
| student_id | student_name |
|---|---|
| 1 | John Doe |
| 2 | Jane Smith |
| 3 | Bob Johnson |
| 4 | Alice White |
| 5 | Michael Green |
courses テーブル
| course_id | course_name |
|---|---|
| 101 | Mathematics |
| 102 | Science |
| 103 | History |
| 104 | Literature |
enrollments テーブル
| enrollment_id | student_id | course_id |
|---|---|---|
| 1 | 1 | 101 |
| 2 | 1 | 102 |
| 3 | 2 | 101 |
| 4 | 3 | 103 |
| 5 | 4 | 104 |
| 6 | 5 | 104 |
| 7 | 5 | 103 |
各コースごとに登録している学生数を表示し、学生数の多い順に並べるSQLクエリを書いてください。
回答例を表示
SELECT A.course_name, COUNT(B.student_id) AS student_count
FROM courses AS A
LEFT JOIN enrollments AS B ON A.course_id = B.course_id
GROUP BY A.course_name
ORDER BY student_count DESC;
解説:
LEFT JOINを使用して、coursesテーブルとenrollmentsテーブルを結合します。COUNT関数で各コースの登録学生数をカウントし、GROUP BY句でコース名ごとにグループ化します。ORDER BY句を使用して、学生数の多い順に並べます。
難易度別に選出した理由
ChatGPTへ難易度別に求められている知識を聞いたところ、下記のように帰ってきました。
これは同時に、どのようにSQLを学べば良いかの良い道標になりそうです。
-
初級 (問題 1〜3): 基本的な
SELECT文、WHERE句、SUMやMAXなどの集計関数の使い方を問う問題。単一のテーブル操作で、シンプルな条件でのフィルタリングや基本的な集計を行います -
中級 (問題 4〜6):
GROUP BY句やHAVING句、集計関数とソート（ORDER BY）を組み合わせた問題。複数の条件を考慮した中級レベルのクエリ操作が含まれます -
上級 (問題 7〜10): 複数のテーブルを結合するクエリ、サブクエリを使用した問題など、現実的なシナリオを反映した複雑なSQLクエリの作成を要求する問題です
おわりに
今回はSQLで作成しましたが、他の言語でも応用できそうです。
また、毎日10問解く訓練をしていけば、SQLの上達に繋がりそうです。
このように学習でChatGPTを使えると気づいたので、記事にしてみました。
ここまで読んでいただき、ありがとうございました。