日本語
メニュー

SQL練習ツール

ブラウザの中で動くデータベース(SQLite)に、社員・部署・売上の練習用の表を用意しました。SQLを書いて実行すると、結果がすぐ表で表示されます。SELECTとWHERE、ORDER BY、GROUP BYとHAVING、内部結合と外部結合、副問合せなどを、10問の練習問題で答え合わせしながら練習できます。基本情報技術者試験のSQLの問題の練習にも使えます。

練習問題

結果

練習用の表

データベースはこのページの中だけで動き、入力したSQLやデータはどこにも送りません。INSERTやUPDATEで表を書き換えても、「表を元に戻す」かページの読み込み直しで最初の状態に戻ります。答え合わせは、ORDER BY のない問題では行の順番を問いません。

練習用の表

社員表には社員番号・氏名・部署番号・給与・入社年、部署表には部署番号・部署名・所在地、売上表には売上番号・社員番号・月・金額が入っています。社員表の部署番号は、部署表の部署番号を参照する外部キーです。部署に属していない社員(部署番号が NULL)や、給与が NULL の社員、社員のいない部署もわざと入れてあり、結合や集計で NULL がどう扱われるかを確かめられます。画面の下の「練習用の表」で、各表の中身をいつでも見られます。

SELECTの基本: 選ぶ列、絞り込み、並べ替え

SELECT の後に取り出す列、FROM の後に表、WHERE の後に行の条件を書きます。SELECT 氏名, 給与 FROM 社員 WHERE 給与 >= 300000 ORDER BY 給与 DESC; は、給与が300000以上の社員を給与の高い順に並べます。NULL は「値がない」ことを表し、= NULL ではなく IS NULL や IS NOT NULL で調べます。給与が NULL の社員は、給与 >= 300000 にも 給与 < 300000 にも当てはまらない点に注意してください。

集計: GROUP BY と HAVING

COUNT、SUM、AVG、MAX、MIN などの集約関数は、GROUP BY で分けたグループごとに1つの値を返します。WHERE はグループに分ける前の行を絞り込み、HAVING はグループに分けた後の集計結果を絞り込みます。COUNT(*) は行の数を、COUNT(列) は NULL でない値の数を数え、AVG(給与) は給与が NULL の行を除いて平均します。練習問題5の答えが、8人ではなく7人の平均になっていることを確かめてください。

結合: 内部結合と外部結合

INNER JOIN(内部結合)は、ON の条件に合う組だけを返すので、部署に属していない社員や社員のいない部署は結果に出ません。LEFT OUTER JOIN(左外部結合)は、左の表の行をすべて残し、右の表に合う行がないときは右の列を NULL にします。部署表を左にして社員表を外部結合し、COUNT(社員.社員番号) で数えると、社員のいない部署も0人として表示できます。COUNT(*) にすると NULL の行も1行と数えてしまうため、0にはならない点に注意してください。

副問合せ: IN と EXISTS

WHERE の中に別の SELECT(副問合せ)を書くと、その結果を条件に使えます。IN は副問合せが返す値の一覧に含まれるかを、EXISTS は副問合せが1行でも返すかを調べます。NOT EXISTS を使うと、売上が1件もない社員のように「対応する行がないもの」を探せます。外部の行の値を副問合せの中で使うものを相関副問合せといい、外部の行ごとに副問合せが評価されると考えると分かりやすくなります。

データベースの問題を解く

商品表に、(商品名, 価格)が(ペン, 120)、(ノート, 250)、(消しゴム, 80)、(ファイル, 300)、(定規, 150)の5行がある。SELECT 商品名 FROM 商品 WHERE 価格 >= 150 ORDER BY 価格 DESC の結果として、返される行を順に並べたものはどれか。

  1. A定規、ノート、ファイル
  2. Bファイル、ノート
  3. Cファイル、ノート、定規
答えと解説を見る

正解：C. ファイル、ノート、定規

WHERE 価格 >= 150 で、価格が150以上の行だけが選ばれます。150ちょうどの定規も含まれるので、選ばれるのはノート(250)、ファイル(300)、定規(150)の3行です。次に ORDER BY 価格 DESC で、価格の大きい順(降順)に並べ替えるので、ファイル(300)、ノート(250)、定規(150)の順になります。ORDER BY で ASC を指定するか、何も指定しないと、小さい順(昇順)になります。ORDER BY を付けない場合は、結果の行の順番は保証されません。SELECT の後には取り出す列(ここでは商品名)を、FROM の後には表を、WHERE の後には行を選ぶ条件を書きます。

社員表の部署の列に、上の行から順に 営業、開発、営業、総務、開発、営業 の値が入っている(全6行)。SELECT DISTINCT 部署 FROM 社員 の結果は何行になるか。

  1. A2行
  2. B3行
  3. C6行
答えと解説を見る

正解：B. 3行

DISTINCTは、結果から重複した行を取り除き、同じ値を1行にまとめます。部署の値の種類は営業、開発、総務の3つなので、結果は3行です。DISTINCTを付けずに SELECT 部署 FROM 社員 とすると、重複を含めて6行がそのまま返ります。2行は、2回以上現れる部署(営業と開発)だけを数えた場合の数で、これは SELECT 部署 FROM 社員 GROUP BY 部署 HAVING COUNT(*) >= 2 のような問い合わせの結果に当たります。

成績表の点数の列に、50、NULL、70、NULL、90、30 の6つの値がある。SELECT COUNT(*), COUNT(点数), AVG(点数) FROM 成績 の結果を順に並べたものはどれか。

  1. A6, 6, 40
  2. B6, 4, 40
  3. C6, 4, 60
答えと解説を見る

正解：C. 6, 4, 60

COUNT(*)は、NULLを含むかどうかに関係なく行の数を数えるので6です。COUNT(点数)のように列を指定すると、その列がNULLでない行だけを数えるので4です。AVG(点数)は、NULLでない4つの値の合計 50 + 70 + 90 + 30 = 240 を、NULLでない値の個数4で割るので 240 ÷ 4 = 60 です。合計を全体の行数で割った SUM(点数) / COUNT(*) = 240 ÷ 6 = 40 とは一致しないことに注意が必要です。NULLを0点として扱いたいなら、COALESCE(点数, 0) で置き換えてから集計します。

売上表(店舗, 金額)に、(A, 500)、(A, 700)、(B, 300)、(B, 400)、(C, 1200)、(D, 200)、(D, 900) の7行がある。SELECT 店舗, SUM(金額) FROM 売上 GROUP BY 店舗 HAVING SUM(金額) >= 1000 の結果は何行になるか。

  1. A1行
  2. B3行
  3. C4行
答えと解説を見る

正解：B. 3行

GROUP BY 店舗 で店舗ごとにまとめると、合計はAが 500 + 700 = 1200、Bが 300 + 400 = 700、Cが1200、Dが 200 + 900 = 1100 の4グループになります。HAVING SUM(金額) >= 1000 で合計が1000以上のグループだけを残すので、A、C、Dの3行が結果になります。1行は、WHERE 金額 >= 1000 のように、グループにまとめる前の1行ずつの金額で選んだ場合の結果(Cだけ)です。4行は、HAVINGを付けないときのグループの数です。

社員表(社員番号, 氏名, 部署ID)に4行あり、部署IDは順に10、20、10、NULLである。部署表(部署ID, 部署名)には部署IDが10、20、30の3行がある。SELECT 社員.氏名, 部署.部署名 FROM 社員 INNER JOIN 部署 ON 社員.部署ID = 部署.部署ID の結果は何行になるか。

  1. A3行
  2. B4行
  3. C12行
答えと解説を見る

正解：A. 3行

INNER JOIN(内部結合)は、ON の条件を満たす行の組み合わせだけを結果にします。部署IDが10の社員2人は部署10の行と、部署IDが20の社員1人は部署20の行と組み合わさるので、結果は3行です。部署IDがNULLの社員は、NULLとの比較が真にならないので、どの部署とも結合されずに結果から消えます。また、社員のいない部署30も結果に出てきません。4行は、社員表のすべての行を残す左外部結合(社員 LEFT OUTER JOIN 部署)の場合の行数です。12行は、条件を付けずにすべての組み合わせを作るCROSS JOIN(直積)の 4 × 3 = 12 行です。

社員表(社員番号, 氏名, 部署ID)に5行あり、部署IDは順に10、20、10、NULL、NULLである。部署表(部署ID, 部署名)には部署IDが10、20、30の3行がある。SELECT 部署.部署名, 社員.氏名 FROM 部署 LEFT OUTER JOIN 社員 ON 部署.部署ID = 社員.部署ID の結果は何行になるか。

  1. A3行
  2. B4行
  3. C5行
答えと解説を見る

正解：B. 4行

LEFT OUTER JOIN(左外部結合)は、左側の表(ここでは部署表)のすべての行を残し、右側の表に一致する行がなければ、右側の列をNULLで埋めて1行にします。部署10には社員が2人いるので2行、部署20は1行、社員のいない部署30は氏名がNULLの1行になり、合計4行です。部署IDがNULLの社員2人は、どの部署とも一致せず、残す対象ではない右側の表の行なので、結果には出てきません。内部結合なら部署30の行も消えて3行です。表の左右を逆にして 社員 LEFT OUTER JOIN 部署 とすると、社員5人がすべて残るので5行になります。外部結合では、どちらの表の行をすべて残すのかを確かめることが大切です。

顧客表(顧客ID, 氏名)と注文表(注文ID, 顧客ID, 金額)から、一度も注文していない顧客の氏名を求めたい。正しいSQL文はどれか。

  1. ASELECT 氏名 FROM 顧客 WHERE EXISTS (SELECT * FROM 注文 WHERE 注文.顧客ID = 顧客.顧客ID)
  2. BSELECT 氏名 FROM 顧客 WHERE NOT EXISTS (SELECT * FROM 注文)
  3. CSELECT 氏名 FROM 顧客 WHERE NOT EXISTS (SELECT * FROM 注文 WHERE 注文.顧客ID = 顧客.顧客ID)
答えと解説を見る

正解：C. SELECT 氏名 FROM 顧客 WHERE NOT EXISTS (SELECT * FROM 注文 WHERE 注文.顧客ID = 顧客.顧客ID)

EXISTSは、括弧の中の副問い合わせが1行でも結果を返せば真、1行も返さなければ偽になる述語で、NOT EXISTSはその逆です。副問い合わせの中で 注文.顧客ID = 顧客.顧客ID のように外側の表の列を使うと、外側の顧客表の1行ごとに、その顧客の注文があるかどうかを調べる相関副問い合わせになります。これをNOT EXISTSと組み合わせると、注文が1件もない顧客だけが残ります。EXISTSにすると、逆に注文したことがある顧客が求まります。副問い合わせに顧客と対応させる条件がないと、注文表に1行でもあれば副問い合わせは常に行を返すので、NOT EXISTSは常に偽になり、結果は0行になります。同じ結果は、LEFT OUTER JOIN で結合して、注文側の列がNULLになる行を選ぶ方法でも得られます。

よくある質問

インストールや登録は必要ですか?

必要ありません。SQLite をブラウザで動かす sql.js というライブラリを使っていて、ページを開くだけで使えます。入力したSQLやデータはどこにも送られません。

表を壊してしまったらどうすればよいですか?

「表を元に戻す」を押すか、ページを読み込み直すと、最初の状態に戻ります。答え合わせは毎回新しいデータベースで行うので、表を書き換えたあとでも正しく判定されます。

答え合わせはどう判定していますか?

入力したSQLと模範解答を、それぞれ最初の状態のデータベースで実行し、結果の行と列の数、値を比べます。ORDER BY のない問題では行の順番を問いません。列の名前や別名は問わないので、模範解答と違う書き方でも結果が同じなら正解になります。

使えるSQLの文法は?

SQLite の文法です。SELECT、INSERT、UPDATE、DELETE、CREATE TABLE、結合、副問合せ、集約関数、CASE 式などが使えます。データベースの製品によって関数名や細かい文法が違うことがあるので、試験やほかの製品で使うときはその文法も確かめてください。

基本情報技術者試験の対策になりますか?

SELECT、結合、GROUP BY と HAVING、副問合せ、NULL の扱いなど、試験でよく問われる考え方を、実際に動かして確かめるのに役立ちます。試験の問題と同じ表や形式ではないので、過去問題などとあわせて使ってください。

関連する用語

SQL 関係データベース 主キー 外部キー ビュー

関連する計算ツール

半導体・電子回路・情報の計算ツール →