値を参照して更新
配点 : 10点難易度 : 1
制限実行時間 : 1000ms制限メモリ使用量 : 128 MB
SQLiteの注意点

TOPSIC SQLでは、SQLの実行環境にSQLiteを使用しています。SQLite以外のRDBMSの文法を使用することはできません。
問題を解く際に注意すべきSQLiteの制約について、以下に記載してありますのでご確認下さい。

  1. NUMERIC、INTEGER、REALは、自身のクラスへの変換が推奨されていますが、変換できなかった場合でも、そのまま格納されます。
  2. 新しくデータを追加した場合、既に格納されているデータの中で ROWID の値が最大のものを探し、それに1を加えた値が新しく追加されるデータの ROWID の値として保存されます。
  3. 外部結合は、左外部結合を行う LEFT OUTER JOIN 句のみ使用できます。
    ※2022年12月以降、LEFT / RIGHT / FULL OUTER JOIN が使用可能となりました。
  4. 指定した日付と時刻のタイムゾーンはUTCとして扱われます。
  5. 日付関数や文字列関数については、SQLiteで固有の関数があります。
  6. テーブル項目の論理名が「◯◯日」となっている場合は、日付項目の時分秒は設定されていません。論理名が「◯◯日時」となっている場合は、日付項目の時分秒まで設定されています。
    • 「受注日」の場合は「2021-01-01」
    • 「受注日時」の場合は「2021-01-01 15:15:15」

その他のSQLiteの制約については、SQLiteの公式ドキュメントをご確認ください。
また、ER図の読み方やSQLiteの関数および構文などについては、「受験ルール・用語」をご確認ください。

問題

学生を対象としたとある試験(試験コード: 'T00001')を実施しました。
ところが'国語'の試験の配点に誤りがあり、本来100点満点にするところを90点満点にしてしまいました。
他の科目に合わせて、今回は'国語'の試験の得点を100点換算することにしました。

※本試験は今回が初開催であり、試験コードが'T00001'のデータのみとなっています。


※左が物理名、右が論理名です。

得点テーブルの'国語'の得点を100点換算して更新してください。
計算方法は「得点 = 元の得点 ÷ 0.9(小数切捨)」とします。
'国語'の科目コードは'S00001'です。

ヒント UPDATE文のSET句では更新テーブルの値を参照できます。

UPDATE テーブル名
SET カラム名 = カラム名 * 2 -- 元の値を2倍した値で更新
WHERE 条件

テーブル定義

テーブル名: subjects(科目)

主キー 列名 データ型 必須 デフォルト値
✔︎ subject_code VARCHAR ✔︎ NULL
subject_name VARCHAR ✔︎ NULL

テーブル名: scores(得点)

主キー 列名 データ型 必須 デフォルト値
✔︎ test_code VARCHAR ✔︎ NULL
✔︎ student_code VARCHAR ✔︎ NULL
✔︎ subject_code VARCHAR ✔︎ NULL
score INTEGER NULL

サンプルデータ

テーブル名: subjects(科目)

subject_code subject_name
S00001 国語
S00002 算数
S00003 英語

テーブル名: scores(得点)

test_code student_code subject_code score
T00001 A00001 S00001 64
T00001 A00001 S00002 19
T00001 A00001 S00003 1
T00001 A00002 S00001 20
T00001 A00002 S00002 85
T00001 A00002 S00003 5
T00001 A00003 S00001 80
T00001 A00003 S00002 40
T00001 A00003 S00003 85
T00001 A00004 S00001 85
T00001 A00004 S00002 66
T00001 A00004 S00003 55
T00001 A00005 S00001 75
T00001 A00005 S00002 91
T00001 A00005 S00003 18
T00001 A00006 S00001 62
T00001 A00006 S00002 35
T00001 A00006 S00003 92
T00001 A00007 S00001 35
T00001 A00007 S00002 21
T00001 A00007 S00003 69
T00001 A00008 S00001 50
T00001 A00008 S00002 3
T00001 A00008 S00003 44
T00001 A00009 S00001 77
T00001 A00009 S00002 79
T00001 A00009 S00003 81
T00001 A00010 S00001 0
T00001 A00010 S00002 56
T00001 A00010 S00003 89
T00001 A00011 S00001 77
T00001 A00011 S00002 6
T00001 A00011 S00003 49
T00001 A00012 S00001 85
T00001 A00012 S00002 79
T00001 A00012 S00003 33
T00001 A00013 S00001 52
T00001 A00013 S00002 25
T00001 A00013 S00003 34
T00001 A00014 S00001 82
T00001 A00014 S00002 75
T00001 A00014 S00003 76
T00001 A00015 S00001 87
T00001 A00015 S00002 94
T00001 A00015 S00003 40
T00001 A00016 S00001 65
T00001 A00016 S00002 31
T00001 A00016 S00003 91
T00001 A00017 S00001 25
T00001 A00017 S00002 100
T00001 A00017 S00003 29
T00001 A00018 S00001 54
T00001 A00018 S00002 88
T00001 A00018 S00003 87
T00001 A00019 S00001 2
T00001 A00019 S00002 16
T00001 A00019 S00003 90
T00001 A00020 S00001 50
T00001 A00020 S00002 70
T00001 A00020 S00003 64
T00001 A00021 S00001 15
T00001 A00021 S00002 72
T00001 A00021 S00003 85
T00001 A00022 S00001 65
T00001 A00022 S00002 100
T00001 A00022 S00003 37
T00001 A00023 S00001 67
T00001 A00023 S00002 60
T00001 A00023 S00003 41
T00001 A00024 S00001 8
T00001 A00024 S00002 36
T00001 A00024 S00003 45
T00001 A00025 S00001 82
T00001 A00025 S00002 19
T00001 A00025 S00003 95
T00001 A00026 S00001 9
T00001 A00026 S00002 52
T00001 A00026 S00003 24
T00001 A00027 S00001 58
T00001 A00027 S00002 1
T00001 A00027 S00003 23
T00001 A00028 S00001 55
T00001 A00028 S00002 36
T00001 A00028 S00003 96
T00001 A00029 S00001 38
T00001 A00029 S00002 90
T00001 A00029 S00003 16
T00001 A00030 S00001 74
T00001 A00030 S00002 16
T00001 A00030 S00003 9
T00001 A00031 S00001 58
T00001 A00031 S00002 22
T00001 A00031 S00003 90
T00001 A00032 S00001 67
T00001 A00032 S00002 99
T00001 A00032 S00003 25
T00001 A00033 S00001 84
T00001 A00033 S00002 100
T00001 A00033 S00003 79
T00001 A00034 S00001 27
T00001 A00034 S00002 82
T00001 A00034 S00003 62
T00001 A00035 S00001 15
T00001 A00035 S00002 2
T00001 A00035 S00003 86
T00001 A00036 S00001 36
T00001 A00036 S00002 97
T00001 A00036 S00003 26
T00001 A00037 S00001 65
T00001 A00037 S00002 18
T00001 A00037 S00003 99
T00001 A00038 S00001 78
T00001 A00038 S00002 35
T00001 A00038 S00003 85
T00001 A00039 S00001 55
T00001 A00039 S00002 39
T00001 A00039 S00003 93
T00001 A00040 S00001 57
T00001 A00040 S00002 58
T00001 A00040 S00003 51
T00001 A00041 S00001 59
T00001 A00041 S00002 67
T00001 A00041 S00003 91
T00001 A00042 S00001 35
T00001 A00042 S00002 66
T00001 A00042 S00003 80
T00001 A00043 S00001 77
T00001 A00043 S00002 11
T00001 A00043 S00003 49
T00001 A00044 S00001 20
T00001 A00044 S00002 92
T00001 A00044 S00003 22
T00001 A00045 S00001 4
T00001 A00045 S00002 79
T00001 A00045 S00003 98
T00001 A00046 S00001 87
T00001 A00046 S00002 29
T00001 A00046 S00003 94
T00001 A00047 S00001 8
T00001 A00047 S00002 53
T00001 A00047 S00003 89
T00001 A00048 S00001 25
T00001 A00048 S00002 54
T00001 A00048 S00003 17
T00001 A00049 S00001 35
T00001 A00049 S00002 13
T00001 A00049 S00003 33
T00001 A00050 S00001 34
T00001 A00050 S00002 81
T00001 A00050 S00003 34
T00001 A00051 S00001 44
T00001 A00051 S00002 31
T00001 A00051 S00003 63
T00001 A00052 S00001 59
T00001 A00052 S00002 38
T00001 A00052 S00003 100
T00001 A00053 S00001 56
T00001 A00053 S00002 96
T00001 A00053 S00003 34
T00001 A00054 S00001 40
T00001 A00054 S00002 61
T00001 A00054 S00003 26
T00001 A00055 S00001 30
T00001 A00055 S00002 50
T00001 A00055 S00003 42
T00001 A00056 S00001 6
T00001 A00056 S00002 48
T00001 A00056 S00003 41
T00001 A00057 S00001 69
T00001 A00057 S00002 99
T00001 A00057 S00003 40
T00001 A00058 S00001 53
T00001 A00058 S00002 60
T00001 A00058 S00003 93
T00001 A00059 S00001 44
T00001 A00059 S00002 53
T00001 A00059 S00003 48
T00001 A00060 S00001 62
T00001 A00060 S00002 86
T00001 A00060 S00003 76
T00001 A00061 S00001 24
T00001 A00061 S00002 64
T00001 A00061 S00003 13
T00001 A00062 S00001 57
T00001 A00062 S00002 75
T00001 A00062 S00003 18
T00001 A00063 S00001 50
T00001 A00063 S00002 27
T00001 A00063 S00003 82
T00001 A00064 S00001 30
T00001 A00064 S00002 63
T00001 A00064 S00003 9
T00001 A00065 S00001 70
T00001 A00065 S00002 59
T00001 A00065 S00003 11
T00001 A00066 S00001 53
T00001 A00066 S00002 19
T00001 A00066 S00003 0
T00001 A00067 S00001 5
T00001 A00067 S00002 67
T00001 A00067 S00003 28
T00001 A00068 S00001 37
T00001 A00068 S00002 52
T00001 A00068 S00003 17
T00001 A00069 S00001 10
T00001 A00069 S00002 12
T00001 A00069 S00003 85
T00001 A00070 S00001 7
T00001 A00070 S00002 37
T00001 A00070 S00003 57
T00001 A00071 S00001 65
T00001 A00071 S00002 28
T00001 A00071 S00003 30
T00001 A00072 S00001 81
T00001 A00072 S00002 85
T00001 A00072 S00003 88
T00001 A00073 S00001 15
T00001 A00073 S00002 10
T00001 A00073 S00003 52
T00001 A00074 S00001 51
T00001 A00074 S00002 4
T00001 A00074 S00003 41
T00001 A00075 S00001 27
T00001 A00075 S00002 70
T00001 A00075 S00003 74
T00001 A00076 S00001 35
T00001 A00076 S00002 39
T00001 A00076 S00003 28
T00001 A00077 S00001 1
T00001 A00077 S00002 98
T00001 A00077 S00003 41
T00001 A00078 S00001 44
T00001 A00078 S00002 3
T00001 A00078 S00003 15
T00001 A00079 S00001 2
T00001 A00079 S00002 98
T00001 A00079 S00003 22
T00001 A00080 S00001 12
T00001 A00080 S00002 10
T00001 A00080 S00003 7
T00001 A00081 S00001 75
T00001 A00081 S00002 16
T00001 A00081 S00003 40
T00001 A00082 S00001 53
T00001 A00082 S00002 86
T00001 A00082 S00003 28
T00001 A00083 S00001 49
T00001 A00083 S00002 55
T00001 A00083 S00003 40
T00001 A00084 S00001 6
T00001 A00084 S00002 45
T00001 A00084 S00003 10
T00001 A00085 S00001 0
T00001 A00085 S00002 38
T00001 A00085 S00003 53
T00001 A00086 S00001 8
T00001 A00086 S00002 65
T00001 A00086 S00003 59
T00001 A00087 S00001 13
T00001 A00087 S00002 33
T00001 A00087 S00003 40
T00001 A00088 S00001 19
T00001 A00088 S00002 72
T00001 A00088 S00003 64
T00001 A00089 S00001 54
T00001 A00089 S00002 43
T00001 A00089 S00003 68
T00001 A00090 S00001 25
T00001 A00090 S00002 23
T00001 A00090 S00003 56
T00001 A00091 S00001 44
T00001 A00091 S00002 21
T00001 A00091 S00003 0
T00001 A00092 S00001 47
T00001 A00092 S00002 70
T00001 A00092 S00003 41
T00001 A00093 S00001 53
T00001 A00093 S00002 96
T00001 A00093 S00003 67
T00001 A00094 S00001 30
T00001 A00094 S00002 33
T00001 A00094 S00003 73
T00001 A00095 S00001 44
T00001 A00095 S00002 64
T00001 A00095 S00003 49
T00001 A00096 S00001 1
T00001 A00096 S00002 18
T00001 A00096 S00003 52
T00001 A00097 S00001 10
T00001 A00097 S00002 46
T00001 A00097 S00003 57
T00001 A00098 S00001 77
T00001 A00098 S00002 29
T00001 A00098 S00003 54
T00001 A00099 S00001 5
T00001 A00099 S00002 67
T00001 A00099 S00003 64
T00001 A00100 S00001 54
T00001 A00100 S00002 34
T00001 A00100 S00003 34
T00001 A00101 S00001 14
T00001 A00101 S00002 30
T00001 A00101 S00003 53
T00001 A00102 S00001 39
T00001 A00102 S00002 23
T00001 A00102 S00003 30
T00001 A00103 S00001 12
T00001 A00103 S00002 1
T00001 A00103 S00003 55
T00001 A00104 S00001 14
T00001 A00104 S00002 95
T00001 A00104 S00003 52
T00001 A00105 S00001 35
T00001 A00105 S00002 58
T00001 A00105 S00003 61
T00001 A00106 S00001 67
T00001 A00106 S00002 86
T00001 A00106 S00003 83
T00001 A00107 S00001 75
T00001 A00107 S00002 98
T00001 A00107 S00003 100
T00001 A00108 S00001 2
T00001 A00108 S00002 87
T00001 A00108 S00003 28
T00001 A00109 S00001 0
T00001 A00109 S00002 9
T00001 A00109 S00003 90
T00001 A00110 S00001 79
T00001 A00110 S00002 88
T00001 A00110 S00003 85
T00001 A00111 S00001 67
T00001 A00111 S00002 78
T00001 A00111 S00003 98
T00001 A00112 S00001 51
T00001 A00112 S00002 42
T00001 A00112 S00003 41
T00001 A00113 S00001 20
T00001 A00113 S00002 52
T00001 A00113 S00003 51
T00001 A00114 S00001 9
T00001 A00114 S00002 68
T00001 A00114 S00003 11
T00001 A00115 S00001 37
T00001 A00115 S00002 15
T00001 A00115 S00003 8
T00001 A00116 S00001 0
T00001 A00116 S00002 18
T00001 A00116 S00003 69
T00001 A00117 S00001 26
T00001 A00117 S00002 94
T00001 A00117 S00003 35
T00001 A00118 S00001 27
T00001 A00118 S00002 97
T00001 A00118 S00003 4
T00001 A00119 S00001 81
T00001 A00119 S00002 56
T00001 A00119 S00003 69
T00001 A00120 S00001 86
T00001 A00120 S00002 35
T00001 A00120 S00003 23
T00001 A00121 S00001 33
T00001 A00121 S00002 34
T00001 A00121 S00003 14
T00001 A00122 S00001 78
T00001 A00122 S00002 75
T00001 A00122 S00003 66
T00001 A00123 S00001 87
T00001 A00123 S00002 87
T00001 A00123 S00003 30
T00001 A00124 S00001 71
T00001 A00124 S00002 11
T00001 A00124 S00003 58
T00001 A00125 S00001 26
T00001 A00125 S00002 20
T00001 A00125 S00003 63
T00001 A00126 S00001 63
T00001 A00126 S00002 30
T00001 A00126 S00003 41
T00001 A00127 S00001 25
T00001 A00127 S00002 23
T00001 A00127 S00003 56
T00001 A00128 S00001 32
T00001 A00128 S00002 20
T00001 A00128 S00003 41
T00001 A00129 S00001 82
T00001 A00129 S00002 20
T00001 A00129 S00003 30
T00001 A00130 S00001 14
T00001 A00130 S00002 78
T00001 A00130 S00003 31
T00001 A00131 S00001 65
T00001 A00131 S00002 50
T00001 A00131 S00003 18
T00001 A00132 S00001 46
T00001 A00132 S00002 33
T00001 A00132 S00003 13
T00001 A00133 S00001 56
T00001 A00133 S00002 5
T00001 A00133 S00003 42
T00001 A00134 S00001 7
T00001 A00134 S00002 66
T00001 A00134 S00003 74
T00001 A00135 S00001 27
T00001 A00135 S00002 15
T00001 A00135 S00003 67
T00001 A00136 S00001 63
T00001 A00136 S00002 13
T00001 A00136 S00003 62
T00001 A00137 S00001 44
T00001 A00137 S00002 91
T00001 A00137 S00003 51
T00001 A00138 S00001 37
T00001 A00138 S00002 15
T00001 A00138 S00003 96
T00001 A00139 S00001 66
T00001 A00139 S00002 29
T00001 A00139 S00003 90
T00001 A00140 S00001 72
T00001 A00140 S00002 53
T00001 A00140 S00003 21
T00001 A00141 S00001 76
T00001 A00141 S00002 68
T00001 A00141 S00003 50
T00001 A00142 S00001 71
T00001 A00142 S00002 27
T00001 A00142 S00003 42
T00001 A00143 S00001 87
T00001 A00143 S00002 97
T00001 A00143 S00003 23
T00001 A00144 S00001 56
T00001 A00144 S00002 15
T00001 A00144 S00003 32
T00001 A00145 S00001 17
T00001 A00145 S00002 47
T00001 A00145 S00003 89
T00001 A00146 S00001 3
T00001 A00146 S00002 78
T00001 A00146 S00003 39
T00001 A00147 S00001 51
T00001 A00147 S00002 2
T00001 A00147 S00003 26
T00001 A00148 S00001 13
T00001 A00148 S00002 59
T00001 A00148 S00003 16
T00001 A00149 S00001 18
T00001 A00149 S00002 91
T00001 A00149 S00003 29
T00001 A00150 S00001 7
T00001 A00150 S00002 58
T00001 A00150 S00003 82
T00001 A00151 S00001 80
T00001 A00151 S00002 70
T00001 A00151 S00003 75
T00001 A00152 S00001 63
T00001 A00152 S00002 64
T00001 A00152 S00003 72
T00001 A00153 S00001 62
T00001 A00153 S00002 10
T00001 A00153 S00003 62
T00001 A00154 S00001 71
T00001 A00154 S00002 38
T00001 A00154 S00003 46
T00001 A00155 S00001 17
T00001 A00155 S00002 84
T00001 A00155 S00003 21
T00001 A00156 S00001 4
T00001 A00156 S00002 2
T00001 A00156 S00003 93
T00001 A00157 S00001 55
T00001 A00157 S00002 12
T00001 A00157 S00003 58
T00001 A00158 S00001 69
T00001 A00158 S00002 51
T00001 A00158 S00003 43
T00001 A00159 S00001 28
T00001 A00159 S00002 68
T00001 A00159 S00003 5
T00001 A00160 S00001 39
T00001 A00160 S00002 80
T00001 A00160 S00003 88
T00001 A00161 S00001 22
T00001 A00161 S00002 79
T00001 A00161 S00003 14
T00001 A00162 S00001 89
T00001 A00162 S00002 10
T00001 A00162 S00003 70
T00001 A00163 S00001 12
T00001 A00163 S00002 20
T00001 A00163 S00003 42
T00001 A00164 S00001 37
T00001 A00164 S00002 48
T00001 A00164 S00003 78
T00001 A00165 S00001 14
T00001 A00165 S00002 78
T00001 A00165 S00003 72
T00001 A00166 S00001 37
T00001 A00166 S00002 27
T00001 A00166 S00003 49
T00001 A00167 S00001 31
T00001 A00167 S00002 79
T00001 A00167 S00003 61
T00001 A00168 S00001 56
T00001 A00168 S00002 13
T00001 A00168 S00003 99
T00001 A00169 S00001 21
T00001 A00169 S00002 73
T00001 A00169 S00003 50