SQL学校募集社募集問題シリーズの学生成績情報検索

文書ディレクトリ

ソーステーブル

s表

sc表

c表

問題

問題1の解答

問題2の解答

問題3の解答

ソーステーブル
s表
Sid:学生ID Sname:学生名
Sid
Sname
1
趙雷
2
銭電
3
孫風
4
李雲
5
周梅
6
呉蘭
7
鄭竹
scテーブル
Sid学生id Cid課程id score点数
Sid
Cid
score
1
1
80
1
2
90
1
3
99
2
1
70
2
2
60
2
3
80
3
1
80
3
2
80
3
3
80
4
1
50
4
2
30
4
3
20
5
1
76
5
2
87
6
1
31
6
3
34
7
2
89
c表
Cidカリキュラムid Cnameカリキュラム名
Cid
Cname
1
国語
2
数学
3
英語
に質問

SQL文で各コースが80点以上の学生の名前を検索する

「国語」課程が「数学」課程より成績の高い学生の情報と課程点数

を照会する

平均成績が65点より大きい学生のidと名前、および平均成績

を検索する.
問題1の解答

SELECT s.Sid,s.Sname 
FROM xiaoliu.s
INNER JOIN
xiaoliu.sc
ON sc.Sid=s.Sid
GROUP BY sc.Sid,s.Sname
HAVING MIN(sc.score)>80;

構想解析:各課程が80点より大きい学生の名前を調べるため,学生の名前を含む和学号のs表と課程成績を含むsc表を結合し,この2つの表に共通に含まれるSidすなわち学号に基づいて結合し,最後にgroupbyとwhereフィルタリングを加える.
問題2の解答

SELECT t1.Sid,t1.score AS '  ',t2.score AS '  '
FROM
(SELECT Sid,score FROM xiaoliu.sc
INNER JOIN
xiaoliu.c
ON c.Cid=sc.Cid
WHERE c.Cname='  ') t1
INNER JOIN
(SELECT Sid,score FROM xiaoliu.sc
INNER JOIN
xiaoliu.c
ON c.Cid=sc.Cid
WHERE c.Cname='  ') t2
ON t1.Sid=t2.Sid
WHERE t1.score>t2.score;

構想解析:「国語」の課程が「数学」の課程より成績の高い学生の情報と課程点数を検索するため、返されるのはやはり学生の情報と課程点数、すなわちSid、国語成績、数学成績であるため、2枚の表を作ってそれぞれ学生の国語成績と数学成績、すなわちt 1とt 2を検索しなければならない.さらに学号に従ってこの2枚の表を内接続し、WHERE t 1に基づいている.score>t2.scoreこのフィルタリング条件は「国語」課程が「数学」課程より成績の高い学生の情報と課程点数を得る.
問題3の解答

SELECT s.Sid,s.Sname,AVG(sc.score) AS '    '
FROM 
xiaoliu.s
INNER JOIN
xiaoliu.sc
ON s.Sid=sc.Sid
GROUP BY s.Sid,s.Sname
HAVING AVG(sc.score)>65;

構想解析:平均成績が65点より大きい学生のidと名前及び平均成績を調べるため、同じ学号に基づいて学生の名前を含む和学号のs表と課程成績を含むsc表を連結し、平均成績が65より大きいことからSidとSname及び平均成績を選出する