新たに、以下のテーブル【student_list2】および【score2】を作成しましょう。
【student_list2】
| カラム名 | 堅 | 備考 |
|---|---|---|
| class | int | クラス |
| num | int | 出席番号 |
| name | varchar(50) | 生徒氏名 |
主キーは(class, no)
CREATE TABLE student_list2(
class INT,
num INT,
name VARCHAR(50),
PRIMARY KEY(class,num)
);【score2】
| カラム名 | 堅 | 備考 |
|---|---|---|
| class | int | クラス |
| num | int | 出席番号 |
| subject_id | char(3) | 科目ID |
| score | int | テストの得点 |
主キーは(class, no, subject_id)
CREATE TABLE score2(
class INT,
num INT,
subject_id char(3),
score INT,
FOREIGN KEY(class,num) REFERENCES student_list2(class,num),
FOREIGN KEY(subject_id) REFERENCES subject_list(subject_id),
PRIMARY KEY(class,num,subject_id)
);下記のようにデータを挿入する。
【student_list2】
| class | num | name |
|---|---|---|
| 1 | 1 | 鈴木 |
| 1 | 2 | 佐藤 |
| 2 | 1 | 高橋 |
| 2 | 2 | 田中 |
INSERT INTO student_list2(class,num,name)
VALUES(1,1,'鈴木'),(1,2,'佐藤'),(2,1,'高橋'),(2,2,'田中');【score2】
| class | num | subject_id | score |
|---|---|---|---|
| 1 | 1 | A01 | 54 |
| 1 | 2 | A01 | 44 |
| 2 | 1 | A01 | 60 |
| 2 | 2 | A01 | 64 |
| 1 | 1 | B01 | 71 |
| 1 | 2 | B01 | 58 |
| 2 | 1 | B01 | 65 |
| 2 | 2 | B01 | 81 |
INSERT INTO score2(class,num,subject_id,score)
VALUES(1,1,'A01',54),
(1,2,'A01',44),
(2,1,'A01',60),
(2,2,'A01',64),
(1,1,'B01',71),
(1,2,'B01',58),
(2,1,'B01',65),
(2,2,'B01',81);student_list2から、subject_id=’B01’の試験で80点以上の得点をとった生徒のリストを得るSQLを、副問い合わせを用いて書いてみましょう（※結合してしまえば通常のWHERE句だけで出来るないようですが、練習のために敢えて副問い合わせを用います）
まず、【score2】テーブルから『subject_id=’B01’の試験で80点以上だったレコード』を抽出するには、
SELECT * FROM score2 WHERE subject_id='B01' AND score>=80;とします。
【student_list2】テーブルの主キーは(class,num)の複合キーなので、classとnumをそれぞれ別の副問い合わせで取得して、
SELECT * FROM student_list2
WHERE class=(SELECT class FROM score2 WHERE subject_id='B01' AND score>=80)
AND num=(SELECT num FROM score2 WHERE subject_id='B01' AND score>=80);とすれば結果が得られそうです。
実際これを実行すると、
を得ることが出来ます。
しかしほぼ同内容の副問い合わせを2回記述するのはいかにも能率が悪そうです。
実は複合キーを得るための副問い合わせは、
SELECT * FROM student_list2
WHERE (class,num)=(SELECT class,num FROM score2
WHERE subject_id='B01' AND score>=80);のように、（）でカラムの組を作ることによって1つの副問い合わせで記述することが出来ます。
次に、student_list2から、『subject_id=’B01’の試験で70点以上の得点をとった生徒のリスト』を得てみましょう。
先ほどの例の得点部分だけを書き換えて、
SELECT * FROM student_list2
WHERE (class,num)=(SELECT class,num FROM score2
WHERE subject_id='B01' AND score>=70);
とすると、
『サブクエリが2行以上の結果を返します』
というエラーが発生してしまいます。
この場合は、キーが1つだけの場合と同じくIN演算子が使えます。
SELECT * FROM student_list2
WHERE (class,num) IN (SELECT class,num FROM score2
WHERE subject_id='B01' AND score>=70);
このようにすると、
という予想通りの結果を得ることが出来ます。
なお、最初に挙げた例を使って
SELECT * FROM student_list2
WHERE class IN (SELECT class FROM score2 WHERE subject_id='B01' AND score>=70) AND
num IN (SELECT num FROM score2 WHERE subject_id='B01' AND score>=70);とすると、結果が
となり、うまくいきません。
これは、条件記述を2つに分けたことにより、主問い合わせの抽出条件が
『classが1または2』かつ『numが1か2』
となるからです。
練習
【sales】【sales_detail】【member_list】【item_list】の各テーブルのうち必要な物を組み合わせ、副問い合わせを用いて『単価（price）が7000の商品を購入したことがある顧客』を抽出するSQLを記述しなさい。
コメント