SQL 練習の課題一覧
ジムの会員データ(gym.db)と、旧システムから移行した履歴付きのデータ(gym_legacy.db)を使い、SQL の基本を 12 の課題で練習します。表の一覧と定義の確認から始め、行の絞り込み・並べ替え・集計、NULL と境界値の扱い、JOIN と LEFT JOIN、WHERE 付きの UPDATE までを順に扱います。答えは結果の表で判定し、表示とは値の違う隠しデータでも同じ結果になるかを確かめます。
-
データベースの表を一覧する
gym.db はジムの会員データを入れた SQLite のデータベースです。sqlite3 で開き、中の表(テーブル)の一覧を表示してください。Linux の ls にあたる操作です。
ターミナルで解く(シェルのコマンドで答える課題)
-
表の列の定義を見る
sqlite3 のドットコマンド .schema に表の名前を付けて、members 表の定義(CREATE TABLE 文)だけを表示してください。Linux の cat にあたる操作です。
ターミナルで解く(シェルのコマンドで答える課題)
-
会員の数を数える
members 表の会員の数を COUNT(*) で数えてください。Linux では wc -l members.csv にあたります。こちらも打つと数が 1 合わないはずです。理由を確かめてください。
この課題を解く
-
条件に合う行だけを取り出す
members 表から、city が Tokyo の会員の name だけを WHERE で取り出してください。Linux の grep にあたる操作です。
この課題を解く
-
並べ替えて上位だけを出す
members 表から age の高い順に 3 人の name と age を出してください(同じ年齢なら name 順)。ORDER BY と LIMIT を使います。Linux の sort -rn | head -3 にあたります。
この課題を解く
-
グループごとに数える
city ごとの会員の数を GROUP BY で数えてください。Linux では cut -d , -f 4 members.csv | sort | uniq -c にあたります。こちらも打つと、数はそろうのに 1 行多いはずです。
この課題を解く
-
SQL の出力をパイプに流す
sqlite3 の出力はただのテキストなので、Linux のパイプにそのまま流せます。sqlite3 -csv で members 表の city 列を出し、sort と uniq -c に渡して都市ごとの数を数えてください。
ターミナルで解く(シェルのコマンドで答える課題)
-
メール未登録の会員を探す
メール未登録の会員の name を出します。SELECT name FROM members WHERE email = NULL; は 0 行です。NULL は = で比べられないためです。直してください。Linux の grep -v @ members.csv にあたります。
この課題を解く
-
「以上」の境界を確かめる
20 歳以上の会員の数を数えます。SELECT COUNT(*) FROM members WHERE age > 20; は 20 歳ちょうどの会員を数えません。「以上」は境界の 20 を含みます。問いに合うように直してください。
この課題を解く
-
表をつないで現在のプランを出す
ここからは gym_legacy.db を使います。旧システムからの移行データで、プラン変更の履歴行があります。会員の name と、いま加入しているプランの名前を JOIN で出してください。
この課題を解く
-
一度も入会していない会員を探す
gym_legacy.db で、enrollments に 1 行も無い(一度も入会していない)会員の name を出してください。JOIN では相手のいない会員が消えます。LEFT JOIN なら残せます。
この課題を解く
-
WHERE を付けて 1 行だけ直す
gym_legacy.db で、id 3 の会員の city を UPDATE で Osaka に直します。先に同じ WHERE で SELECT COUNT(*) を打ち、変わる行が 1 行か確かめてください。Linux の sed -i と同じく、データをその場で書き換えます。
この課題を解く