SQL 練習

ジムの会員データベースに SQL を書いて実行し、結果の表で答え合わせをします。課題はターミナルの「データベース」カテゴリと同じで、ここでの進捗はターミナル側とは別に記録します。

ヒント

テーブル定義

                        

実行のたびに最初のデータから実行します。UPDATE などの変更は次の実行に持ち越しません。

SQL 練習の課題一覧

ジムの会員データ(gym.db)と、旧システムから移行した履歴付きのデータ(gym_legacy.db)を使い、SQL の基本を 12 の課題で練習します。表の一覧と定義の確認から始め、行の絞り込み・並べ替え・集計、NULL と境界値の扱い、JOIN と LEFT JOIN、WHERE 付きの UPDATE までを順に扱います。答えは結果の表で判定し、表示とは値の違う隠しデータでも同じ結果になるかを確かめます。

  1. データベースの表を一覧する

    gym.db はジムの会員データを入れた SQLite のデータベースです。sqlite3 で開き、中の表(テーブル)の一覧を表示してください。Linux の ls にあたる操作です。

  2. 表の列の定義を見る

    sqlite3 のドットコマンド .schema に表の名前を付けて、members 表の定義(CREATE TABLE 文)だけを表示してください。Linux の cat にあたる操作です。

  3. 会員の数を数える

    members 表の会員の数を COUNT(*) で数えてください。Linux では wc -l members.csv にあたります。こちらも打つと数が 1 合わないはずです。理由を確かめてください。

  4. 条件に合う行だけを取り出す

    members 表から、city が Tokyo の会員の name だけを WHERE で取り出してください。Linux の grep にあたる操作です。

  5. 並べ替えて上位だけを出す

    members 表から age の高い順に 3 人の name と age を出してください(同じ年齢なら name 順)。ORDER BY と LIMIT を使います。Linux の sort -rn | head -3 にあたります。

  6. グループごとに数える

    city ごとの会員の数を GROUP BY で数えてください。Linux では cut -d , -f 4 members.csv | sort | uniq -c にあたります。こちらも打つと、数はそろうのに 1 行多いはずです。

  7. SQL の出力をパイプに流す

    sqlite3 の出力はただのテキストなので、Linux のパイプにそのまま流せます。sqlite3 -csv で members 表の city 列を出し、sort と uniq -c に渡して都市ごとの数を数えてください。

  8. メール未登録の会員を探す

    メール未登録の会員の name を出します。SELECT name FROM members WHERE email = NULL; は 0 行です。NULL は = で比べられないためです。直してください。Linux の grep -v @ members.csv にあたります。

  9. 「以上」の境界を確かめる

    20 歳以上の会員の数を数えます。SELECT COUNT(*) FROM members WHERE age > 20; は 20 歳ちょうどの会員を数えません。「以上」は境界の 20 を含みます。問いに合うように直してください。

  10. 表をつないで現在のプランを出す

    ここからは gym_legacy.db を使います。旧システムからの移行データで、プラン変更の履歴行があります。会員の name と、いま加入しているプランの名前を JOIN で出してください。

  11. 一度も入会していない会員を探す

    gym_legacy.db で、enrollments に 1 行も無い(一度も入会していない)会員の name を出してください。JOIN では相手のいない会員が消えます。LEFT JOIN なら残せます。

  12. WHERE を付けて 1 行だけ直す

    gym_legacy.db で、id 3 の会員の city を UPDATE で Osaka に直します。先に同じ WHERE で SELECT COUNT(*) を打ち、変わる行が 1 行か確かめてください。Linux の sed -i と同じく、データをその場で書き換えます。