<データベース論T、第8回、テスト問題の解答例> |
|
|
|
|
|
|
|
配布のTBL一覧を参照して、以下の問いに答えなさい。 |
|
問1 |
5つのテーブルをよく見て、ER図を作成してください。 |
|
|
(配点 20点) |
|
|
問2 |
受講者TBLのテーブル定義文を完成させてください。 |
|
(配点 20点) |
|
|
CREATE |
|
jukousha |
|
|
|
|
|
( gakka_no |
|
|
|
|
int(7), |
|
|
nendo |
|
int(4), |
|
|
jukousha |
|
|
|
hyouka |
|
char(1) |
|
|
soten |
|
|
); |
|
問3 |
以下の設問について、SQL文を作成してください。 |
|
(配点 60点) |
|
|
@ 学生TBLの全行を取出す。 |
|
|
|
|
|
|
A 学生TBLからnyuugaku_nendo が「2000年」のデータを持つものについて、氏名を取出す。 |
|
|
|
select name |
|
|
from gakusei |
|
|
where nyuugaku_nendo=2000 ; |
|
|
|
B 学科名が「system」に所属する、学生の学籍番号、氏名を取出す。 |
|
|
select distinct
gakusei.gakuseki,gakusei.name
from gakusei,shozoku,gakka
where gakka.gakkamei =
'system'
and gakusei.gakuseki =
shozoku.gakusei_code
and
gakka.gakka_no= shozoku.gakka_no ;
|
|
|
|
|
または |
|
|
select
gakusei.gakuseki,gakusei.name |
|
|
|
from gakusei,shozoku,gakka |
|
|
where
gakka.nendo = 2002 |
|
|
and gakka.gakkamei = 'system' |
|
|
and gakusei.gakuseki = shozoku.gakusei_code |
|
|
and gakka.gakka_no= shozoku.gakka_no ; |
|
|
|
C 学科名が「system」で、しかも科目名が「system 33」で年度「2002」の |
|
|
|
|
受講者の学籍番号、氏名、評価、粗点を年次、学籍番号順に取出す。 |
|
|
|
更に、上記の対象者の平均点を出す。 |
|
|
副問い合わせ |
|
|
|
|
|
|
select
jukousha.kamoku_code,gakusei.gakuseki, |
|
select
gakusei.gakuseki,gakusei.name, |
|
|
|
gakusei.name,jukousha.hyouka, |
|
|
jukousha.hyouka,jukouha.soten |
|
|
|
jukousha.soten |
|
|
from jukousha,gakusei |
|
|
|
from gakka, kamoku, jukousha, gakusei |
|
where jukousha.nendo = 2002 |
|
|
|
where gakka.gakkamei = 'system' |
|
|
and
jukousha.jukousha = gakusei.gakuseki |
|
|
|
and gakka.nendo =
2002 |
|
|
and
jukousha.kamoku_code = |
|
|
|
and kamoku.kamoku_mei like 'system%33%' |
|
(select kamoku_code |
|
|
|
and kamoku.nendo = 2002 |
|
|
from kamoku |
|
|
|
and jukousha.nendo = 2002 |
|
|
where kamoku_mei =
'system 33' |
|
|
and gakka.gakka_no = kamoku.gakka_no |
|
and nendo = 2002 |
|
|
|
and kamoku.kamoku_code = jukousha.kamoku_code |
and gakka_no = |
|
|
|
and jukousha.jukousha = gakusei.gakuseki |
|
(select gakka_no |
|
|
order by
gakusei.nenji,gakusei.gakuseki ; |
|
|
from gakka |
|
|
|
|
|
|
|
|
|
where gakkamei = 'system' |
|
and nendo = 2002 ) |
|
|
) |
|
|
|
order by
gakusei.nenji,gakusei.gakuseki ; |
|
|
|
|
|
select
avg(jukousha.soten) |
|
|
|
|
|
select avg(jukousha.soten) |
|
|
|
from jukousha |
|
|
|
from gakka, kamoku, jukousha, gakusei |
|
where jukousha.nendo = 2002 |
|
|
|
where gakka.gakkamei = 'system' |
|
|
and
jukousha.kamoku_code = |
|
|
|
and gakka.nendo =
2002 |
|
|
(select kamoku_code |
|
|
|
and kamoku.kamoku_mei like 'system%33%' |
|
from kamoku |
|
|
|
and kamoku.nendo = 2002 |
|
|
where kamoku_mei =
'system 33' |
|
|
and jukousha.nendo = 2002 |
|
|
and nendo = 2002 |
|
|
|
and gakka.gakka_no = kamoku.gakka_no |
|
and gakka_no = |
|
|
|
and kamoku.kamoku_code = jukousha.kamoku_code |
(select gakka_no |
|
|
|
and jukousha.jukousha = gakusei.gakuseki |
|
from gakka |
|
|
|
order by
gakusei.nenji,gakusei.gakuseki
; |
|
|
where gakkamei = 'system' |
|
|
and nendo = 2002 ) |
|
|
) ; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|