【SQL中級編】SQL問題集サイト『SQLab』でSQLを実践しながら理解する(中級編)

こんにちはフロントエンドエンジニアのまさにょんです！

今回はSQLの基本を SQL問題集サイト『SQLab』の「中級編」で実践しながら理解する方法について解説します。

以前に、『SQLabの初級編』の記事を書いたので、その続編になります。

SQLab

【SQL初級編】SELECT文の基本を SQL問題集サイト『SQLab』の初級編で実践しながら理解する

1 『SQLab』とは？
2 SQLab中級編を解いていく！
3 Twitterやってます！Follow Me！
4 DB・SQL関連書籍
5 参考・引用
6 最近の投稿

『SQLab』とは？

Qiitaの『SQLの練習ができる学習サービス「SQLab」を作ってみた』と言う記事を拝見して『SQLab』の存在を知りました。

簡単に言うと、SQLの問題集ですね。

何がいいって、手軽に実行ができて、無料でSQLを訓練できるところですね。

先述のQiitaにも次のように記載されています。

・無料でバリエーション豊かな多くの練習問題に取り組める
・環境構築不要でブラウザ上ですぐに実践できる
・シンプルで使いやすいUI
・ER図やデータベース値を参考にしながら自力でクエリを組み立てる必要がある
上記の条件を揃えた学習サービスがなかったので作ることにしました。
引用元: SQLの練習ができる学習サービス「SQLab」を作ってみた

ちなみに、まさにょんのSQLスキルは、プロゲートのSQL講座や実務でSQLを使って、初歩的な理解はできている程度の実力です。

SQLab中級編を解いていく！

実際に、SQLabの問題を解きながら、SQLに対する理解を深めていきます。

また、SQLabで問題を解きながらわからない箇所は、その都度調べることをお勧めします。

時にQiitaの【これだけ覚えてたらOK！】SQL構文まとめが特に役立ちました！

SQLでレコード(データ)を集計するための関数の一覧

中級編の最初は、SQLでレコード(データ)から集計などの計算をする方法を学びます。

SQLの集計関数は、次の表のとおりです。

関数名	分類 & 説明
SUM	「集計」: 各行の値の合計を求める
MAX	「集計」: 各行の値の最大値を求める
MIN	「集計」: 各行の値の最小値を求める
AVG	「集計」: 各行の値の平均値を求める
COUNT	「計数」: 行数(レコード数)をカウントする

引用元：【これだけ覚えてたらOK！】SQL構文まとめ

COUNTでレコード数を数えて取得する

-- [問題文] 女性の著者数を取得してください。

-- SQLのCOUNT関数を使うと、条件に一致したレコード数を取得することができます。
select count(*) from authors where gender = '女性';

SUMで指定カラムの合計値を取得する

-- [問題文] 書籍の販売数(figure)の合計値を取得してください。

select sum(figure) from book_sales;

AVGで指定カラムの平均値を取得する

-- [問題文] 書籍の総ページ数の平均値を取得してください。

select avg(total_page) from books;

MAXとMINで、指定カラムの最大値、最小値を取得する

-- [問題文] 書籍の総ページ数の最大値、最小値を取得してください。

select max(total_page), min(total_page) from books;

ASCまたはDESCを使って、並び順を整える

ASCは、ascending(上昇・昇順)の略で、DESCは、descending(下降・降順)の略です。

[ order by 指定カラム ] の後に続けて、ASC または DESC を記述すると、

指定カラムを基準に昇順または降順で並び替えます。

-- [問題文] 書籍一覧を発売年が新しい順に並び替えて取得してください。

-- release_year を基準に降順で並び替え
select * from books order by release_year desc;

GROUP BY句を使って、グループに分ける

GROUP BY句を使用することで、指定したカラムの値を基準にテーブル内のデータをグループ化することができます。

また、グループ化した後は、集合関数を使いグループごとに集計することができます。

なので、指定したカラムの値ごとの合計値や平均値、最大値・最小値を取得したいような場合に役立ちます

-- [問題文] 発行年毎の書籍数を取得してください。
-- また、書籍数は降順に並び替えてください。
-- 出力項目はrelease_year(発行年)とbooks_num(書籍数)です。


select release_year, count(*) as books_num from books 
group by release_year 
order by books_num desc;

HAVIINGを使って「抽出条件」を指定する

まず「HAVIING」を一言で説明すると、「抽出条件を指定」する命令です。

SELECT [表示したい要素] FROM [テーブル名] HAVING [抽出条件];

上記のような形で「抽出条件」を指定することができます。

-- [問題文] 発行年別の書籍数を取得してください。
-- また、書籍数は降順に並び替え、書籍数が2つ以上のデータを取得してください。
-- 出力項目はrelease_year(発行年)とbooks_num(書籍数)です。


select release_year, count(*) as books_num from books
group by release_year
having count(*) >= 2
order by books_num desc;

「WHERE」と「HAVING」の違いは、呼ばれるタイミング！

レコードの取得条件・抽出条件を指定すると言ったら、「WHERE」もありましたね！

「WHERE」と「HAVING」の違いは、何なのでしょうか？

SELECT * FROM user HAVING team="チームA";

上記の「HAVING」を使って条件指定をしているSQL文は「WHERE」で書いても同じ結果が返ってきます。

SELECT * FROM user WHERE team="チームA";

「WHERE」と「HAVING」の違いは、呼ばれるタイミングにあります！

今回の主役である「WHERE」と「HAVING」は、

「WHERE」→「GROUP BY」→「HAVING」の順に呼ばれます。

つまり、間にある「GROUP BY」が関わってこなければ、同じ挙動をしますが、

「GROUP BY」を使って、グループ化を行った際には、次のような違いが出てくるわけです。

WHERE・・・グループ化をされる前の段階、つまり元々のデータでの抽出条件を指定できる
HAVING・・・グループ化した後の情報での、抽出条件を指定できる。

上記内容は、【SQL】一目でわかる!HAVINGとWHEREの違いと活用方法より参考・引用しました。

INSERT文でテーブルにデータ(レコード)を新規追加する

SQLにおけるINSERT文は「どのようなデータをどのテーブルに登録するか」を記述したものです。

基本構文の構造は、次のような形です。

INSERT INTO テーブル名 (カラム名1, カラム名2,...) VALUES (値1, 値2,...);

それでは、実際にSQLabで問題文を解いていきます。

-- [問題文] イベントテーブルに
--「イベント名：2022 WEBデザイントレンド、最大人数：100人」のイベントを追加後、
-- イベント一覧を取得してください。

insert into events (id, name, max_num) values (3, '2022 WEBデザイントレンド', 100);

select * from events order by id asc;

UPDATE文でテーブルのデータ(レコード)を更新する

データ(レコード)を更新する場合は、update文を使います。

基本は、whereで更新するレコードを指定してupdateします。

基本構文の構造は、次のような形です。

UPDATE テーブル名 SET 更新するカラム名 = 更新する値 WHERE 対象を特定するカラム名 = 対象を特定する値;

それでは、実際にSQLabで問題文を解いていきます。

-- [問題文] イベント「最新の英語学習法を学ぼう」の最大人数を30人に変更してください。
-- イベント一覧を取得してください。


update events set max_num = 30 where name = '最新の英語学習法を学ぼう';
select * from events order by id asc;

DELETE文でテーブルのデータ(レコード)を削除する

データ(レコード)を削除する場合は、delete文を使います。

基本は、whereで削除するレコードを指定してdeleteします。

基本構文の構造は、次のような形です。

DELETE FROM テーブル名 WHERE 対象を特定するカラム名 = 削除する対象を特定する値;

それでは、実際にSQLabで問題文を解いていきます。


-- [問題文] イベント「最新の英語学習法を学ぼう」を削除してください。
-- イベント一覧を取得してください。

delete from events where name = '最新の英語学習法を学ぼう';

select * from events order by id asc;

サブクエリを使って、SQLの中にSQLを記述する

SQLには、サブクエリと言う記述方法があります。

サブクエリとは、SQLの中に書くSQLのことです。

SQL文を実行することをクエリ（問合せ）の発行と呼びます。

サブクエリが入っているクエリはまずサブクエリから実行され、

サブクエリの実行結果を一つのテーブルと見なしながらメインクエリが実行されます。

つまり、SQL文の中に入れ子になってサブのSQL文が書かれていて、サブのSQL文の結果を使ってメインのSQL文が実行される。

サブクエリ(サブSQL)の実行 => メインクエリ(メインSQL)の実行

と言う処理フローになっています。

基本構文の構造は、次のような形です。

-- () の中に、Sub-Queryを記述する
-- Sub-Queryの処理結果が、1つのTable構造としてみなされる

SELECT * FROM (SELECT column1 FROM table1);

それでは、実際にSQLabで問題を解いていきます。

サブクエリを搭載したSQLを作成する際のコツがあります。

それは、サブクエリにするSQLを先に実行して、予想する結果になるか確認してから、

メインクエリ(メインSQL)に搭載することです。

この手法で問題を解いていきます。

-- [問題文] 書籍名「コードと回路」より総ページ数の多い書籍一覧を取得してください。

-- 書籍名「コードと回路」の総ページ数を取得する
select total_page from  books where name = 'コードと回路';

-- 上記を踏まえた、答え

-- [ Answer ]
-- 書籍名「コードと回路」より総ページ数の多い書籍一覧を取得
select * from books where total_page > (select total_page from  books where name = 'コードと回路');

もう一つサブクエリを使用する問題を解いていきます。

-- [問題文] 書籍名「時短レシピ100」「かもめ飛行」と発行年が同じ書籍一覧を取得してください。


-- 書籍名「時短レシピ100」「かもめ飛行」と発行年を取得する
select release_year from books where name = '時短レシピ100' or name = 'かもめ飛行';

-- 上記を踏まえた、答え

-- [ Answer ]
-- 書籍名「時短レシピ100」「かもめ飛行」と発行年が同じ書籍一覧を取得する
select * from books 
where release_year 
in (select release_year from books where name = '時短レシピ100' or name = 'かもめ飛行');

JOIN句を使って、Tableを結合する

複数のテーブルから情報を取得する必要がある場合、

テーブルを連結することでクエリの発行から情報の取得までを一度で済ませることができます。

複数のテーブルを連結させるには、FROMで複数のテーブルを指定することで実現することができます。

指定方法は、テーブル名とテーブル名の間にカンマ( , ) もしくは JOIN を置きます。

カンマ( , ) で複数のテーブルを連結する方法は、次のとおりです。

SELECT * FROM テーブル名, テーブル名

JOINで複数のテーブルを連結する方法は、次のとおりです。

SELECT * FROM テーブル名 JOIN テーブル名

次のクエリは「グンマー帝国(gunma)テーブル」と「さいたまー共和国(saitama)テーブル」の2つのテーブルを結合し、

「市町村ID(id_a)・フィールド」と「市町村(city)・フィールド」を表示します。

SELECT id_a, city 
FROM gunma JOIN saitama ON gunma.id=saitama.id;

「グンマー帝国(gunma)テーブル」と「さいたまー共和国(saitama)テーブル」を結合するために、JOINを使っています。

また ON以降の「 gunma.id=saitama.id 」という箇所は、

「グンマー帝国(gunma)テーブルのidフィールドと、さいたまー共和国(saitama)テーブルのidフィールドの値が一致したレコードを選択する」という意味です。

複数のテーブルにリレーションを張るには、FROMの部分に複数のテーブルをカンマ( ,)かJOINで区切って並べ、

ON句でそれらのテーブルを連結する条件を指定します。

一般的にテーブル同士を結合する時に、お互いを紐づけるための idカラム を指定することが多いです。

つまり、ON句には、相互に等しいデータが所属しているカラムを左辺右辺に設定するわけです。

それでは、実際にSQLabで問題文を解いていきます。

-- [問題文] 書籍テーブルと著者テーブルを結合してください。
-- 出力項目はbook_name(書籍名)とauthor_name(著者名)です。


select books.name as book_name, authors.name as author_name  
from books 
join book_authors
on books.id = book_authors.book_id
join authors
on book_authors.author_id = authors.id;