MariaDB 副問合せ

Question

実現したいこと

以下の3つのようなテーブルを持つデータベースがある。

– 属性のデータ型は以下の通りである
• 学籍番号，名前，科目名はCHAR(100)
• 科目IDはINT
– テーブル“学生”の主キーは“学籍番号”である
– テーブル“科目”の主キーは“科目ID”である
– テーブル“履修”の主キーは“学籍番号”と“科目ID”である
– テーブル“履修”の属性“学籍番号”は，テーブル“学生”の“学籍番号”を参照している外部キーである
– テーブル“履修”の属性“科目ID”は，テーブル“科目”の“科目ID”を参照している外部キーである

学生

学籍番号	名前
BX22001	田中
BX22002	山田
BX22003	鈴木

履修

科目ID	学籍番号
1	BX22001
3	BX22001
1	BX22003

科目

科目ID	科目名
1	データベース
2	ネットワーク
3	プログラミング

このデータベースに対して以下の問いのSQL文を作り下記の表のような結果を抽出したい。

• データベースを履修している学生の名前を参照しなさい
• 手順は以下の通りにしなさい
– テーブル“科目”の“科目名”がデータベースである“科目ID”を抽出する
– IN演算子を用いた副問合せで，抽出した“科目ID”と一致するデータを
テーブル“履修”から抽出し，“学籍番号”を抽出する
– IN演算子を用いた副問合せで，抽出した“学籍番号”と一致するデータを
テーブル“学生”から抽出し，“名前”を抽出する

結果

名前
田中
鈴木

前提

MariaDBで上記のようなシステムを作っています。
正しい結果を得るためのSQL文が分かりません。

該当のソースコード

SELECT 名前 FROM 学生
    -> WHERE 学籍番号
    -> IN (SELECT 科目ID FROM 履修 GROUP BY 学籍番号)
    -> IN (SELECT 科目名 FROM 科目 WHERE 科目ID);

+--------+
| 名前 |
+--------+
| 田中 |
| 山田 |
| 鈴木 |
+--------+

調査したこと・試したこと

問題の文章を上からそのまま文に書いてもうまく実行されなかったので、一番下の”名前”をSELECT文に持ってきて実行したところ、名前の一覧は出るようになったが、それからどういう風に正確に問いのように抽出するかが分からない。

Comments

[maisumakun]

> 問題の文章を上からそのまま文に書いてもうまく実行されなかったので

「どんなSQL文を書いて」「どんな結果を期待して」「実際にはどうなったか」を明示してください。

[m.ts10806]

学校の課題でしょうか。
講師に聞くか、これまで学んだことを復習するのが優先では。

Answer 1

命題の通りに書く練習だと思ってそのまま書けばいいんですよ

create table 学生(学籍番号 varchar(10) primary key,名前 varchar(10));
insert into 学生 values
('BX22001','田中'),
('BX22002','山田'),
('BX22003','鈴木');

create table 科目(科目ID varchar(10) primary key,科目名 varchar(20));
insert into 科目 values
('1','データベース'),
('2','ネットワーク'),
('3','プログラミング');

create table 履修(科目ID varchar(10),学籍番号 varchar(10),
primary key(科目ID,学籍番号),
foreign key(科目ID) references 科目(科目ID) on delete cascade,
foreign key(学籍番号) references 学生(学籍番号) on delete cascade
);
insert into 履修 values
('1','BX22001'),
('3','BX22001'),
('1','BX22003');

• テーブル“科目”の“科目名”がデータベースである“科目ID”を抽出する

select 科目ID from 科目 where 科目名='データベース'

• IN演算子を用いた副問合せで，抽出した“科目ID”と一致するデータをテーブル“履修”から抽出し，“学籍番号”を抽出する

select 学籍番号 from 履修 where 
科目ID in(
select 科目ID from 科目 where 科目名='データベース'
)

• IN演算子を用いた副問合せで，抽出した“学籍番号”と一致するデータをテーブル“学生”から抽出し，“名前”を抽出する

select 名前 from 学生 where
学籍番号 in(
select 学籍番号 from 履修 where 
科目ID in(
select 科目ID from 科目 where 科目名='データベース'
)
)

Comments

[kouchan_dd]

ありがとうございました。
解決できました。