SQL
家計簿テーブルから、出金額列を取得する
SELECT 出金額 FROM 家計簿
家計簿テーブルの全ての列を取得する
SELECT 日付, 費目, メモ, 入金額, 出金額 FROM 家計簿
家計簿テーブルの出金額が3000円を超えるレコードだけを取得する
SELECT 日付, 費目, 出金額 FROM 家計簿 WHERE 出金額 > 3000
3月の家賃の支払いを行として挿入する
INSERT INTO 家計簿 VALUES ('2022-02-25', '居住費', '3月の家賃', 0, 85000)
2022年2月25日の出金額を90000円に更新
UPDATE 家計簿 SET 出金額 = 90000 WHERE 日付 = '2022-02-25'
日付が2022年2月25日の3月の家賃の行を削除
DELETE FROM 家計簿 WHERE 日付 = '2022-02-25'
入金額が50000円に等しい行を検索してすべての列を表示する
SELECT * FROM 家計簿 WHERE 入金額 = 50000
出金額が4000円を超える行をすべて削除する
DELETE FROM 家計簿 WHERE 出金額 > 4000
2022年2月3日のメモを「カフェラテを購入」に変更する
UPDATE 家計簿 SET メモ = 'カフェラテを購入' WHERE 日付 = '2022-02-03'
1月に関連する行を取得する
SELECT * FROM 家計簿 WHERE メモ LIKE '%1月%'
100～3,000円の出費を取得する
SELECT * FROM 家計簿 WHERE 出金額 BETWEEN 100 AND 3000
食費・交際費を取得する
SELECT * FROM 家計簿 WHERE 費目 IN ('食費', '交際費')
複合主キー・・・複数の列を1つの主キーとして扱うもの
・・・
【テーブル構成】
気象観測テーブル
列名｜データ型｜備考
月｜INTEGER｜1～12のいずれかの値
降水量｜INTEGER｜観測データがない場合はNULL
最高気温｜INTEGER｜観測データがない場合はNULL
最低気温｜INTEGER｜観測データがない場合はNULL
湿度｜INTEGER｜観測データがない場合はNULL
1.6月のデータ
SELECT * FROM 気象観測テーブル WHERE 月 = 6
2.6月以外のデータ
SELECT * FROM 気象観測テーブル WHERE 月 <> 6
3.降水量が100未満のデータ
SELECT * FROM 気象観測テーブル WHERE 降水量 < 100
4.降水量が200より多いデータ
SELECT * FROM 気象観測テーブル WHERE 降水量 > 200
5.最高気温が30以上のデータ
SELECT * FROM 気象観測テーブル WHERE 最高気温 >= 30
6.最低気温が0以上のデータ
SELECT * FROM 気象観測テーブル WHERE 最低気温 >= 0
7.3月、5月、7月のデータ
SELECT * FROM 気象観測テーブル WHERE 月 IN (3, 5, 7)
SELECT * FROM 気象観測テーブル = ANY (3, 5, 7)
8.3月、5月、7月以外のデータ
SELECT * FROM 気象観測テーブル WHERE 月 NOT IN (3, 5, 7)
SELECT * FROM 気象観測テーブル <> ANY (3, 5, 7)
9.降水量が100以下で、湿度が50より低いデータ
SELECT * FROM 気象観測テーブル WHERE 降水量 <= 100 AND 湿度 < 50
10.最低気温が5未満か、最高気温が35より高いデータ
SELECT * FROM 気象観測テーブル WHERE 最低気温 < 5 OR 最高気温 > 35
11.湿度が60～79の範囲にあるデータ
SELECT * FROM 気象観測テーブル WHERE 湿度 BETWEEN 60 AND 79
SELECT * FROM 気象観測テーブル WHERE 湿度 >= 60 AND 湿度 <= 79
12.観測データのない列にある月のデータ
SELECT * FROM 気象観測テーブル WHERE 降水量 IS NULL OR 最高気温 IS NULL OR 最低気温 IS NULL OR 湿度 IS NULL
・・・
【テーブル構成】
都道府県テーブル
列名｜データ型｜内容
コード｜CHAR(2)｜'01'～'47'の都道府県コード
地域｜VARCHAR(10)｜'関東'や'九州'など
都道府県名｜VARCHAR(10)｜'千葉'や'兵庫'など
県庁所在地｜VARCHAR(20)｜'千葉'や'神戸'など
面積｜INTEGER｜都道府県の面積(km2)
SELECT 都道府県名 FROM 都道府県テーブル WHERE 都道府県名 LIKE '%川'
SELECT 都道府県名 FROM 都道府県テーブル WHERE 都道府県名 LIKE '%島%'
SELECT 都道府県名 FROM 都道府県テーブル WHERE 都道府県名 LIKE '愛%'
4.都道府県名と県庁所在地が一致するデータ
SELECT * FROM 都道府県テーブル WHERE 都道府県名 = 県庁所在地
5.都道府県名と県庁所在地が一致しないデータ
SELECT * FROM 都道府県テーブル WHERE 都道府県名 <> 県庁所在地
・・・
【テーブル構成】
成績表テーブル
列名｜データ型｜備考
学籍番号｜CHAR(4)｜学生の学籍番号
学生名｜VARCHAR(20)｜学生の名前
法学｜INTEGER｜法学の点数
経済学｜INTEGER｜経済学の点数
哲学｜INTEGER｜哲学の点数
外国語｜INTEGER｜外国語の点数
総合成績｜CHAR(1)｜総合評価
1.登録されている全データを取得し、テーブルの内容を確認する
SELECT * FROM 成績表テーブル
2.次ページの表にある学生の成績データを追加する
INSERT INTO 成績表テーブル VALUES ('S001', '織田信長', 77, 55, 80, 75, 93, NULL)
INSERT INTO 成績表テーブル VALUES ('A002', '豊臣秀吉', 64, 69, 70, 0, 59, NULL)
INSERT INTO 成績表テーブル VALUES ('E003', '徳川家康', 80, 83, 85, 90, 79, NULL)
3.2で登録した学籍番号S001の学生の法学を85、哲学を67に修正する
UPDATE 成績表テーブル SET 法学 = 85, 哲学 = 67 WHERE 学籍番号 = 'S001'
4.2で登録した学籍番号A002の学生と学籍番号E003の学生の外国語を81に修正する
UPDATE 成績表テーブル SET 外国語 = 81 WHERE 学籍番号 IN ('A002', 'E003')
UPDATE 成績表テーブル SET 外国語 = 81 WHERE 学籍番号 = ANY ('A002', 'E003')
5.
（1）全科目が80以上の学生は「A」とする
UPDATE 成績表テーブル SET 総合成績 = 'A' WHERE 法学 >= 80 AND 経済学 >= 80 AND 哲学 >= 80 AND 情報理論 >= 80 AND 外国語 >= 80
（2）法学と外国語のどちらかが80以上、かつ経済学と哲学のどちらかが80以上の学生は「B」とする
UPDATE 成績表テーブル SET 総合成績 = 'B' WHERE (法学 >= 80 OR 外国語 >= 80) AND (経済学 >= 80 OR 哲学 >= 80) AND 総合成績 IS NULL
（3）全科目が50未満の学生は「D」とする
UPDATE 成績表テーブル SET 総合成績 = 'D' WHERE 法学 < 50 AND 経済学 < 50 AND 哲学 < 50 AND 情報理論 < 50 AND 外国語 <50 AND 総合成績 IS NULL
（4）それ以外の学生を「C」とする
UPDATE 成績表テーブル SET 総合成績 = 'C' WHERE 総合成績 IS NULL
6.いずれかの科目に0がある学生を、成績表テーブルから削除する
DELETE 成績表テーブル WHERE 法学 = 0 OR 経済学 = 0 OR 哲学 = 0 OR 情報理論 = 0 OR 外国語 = 0
・・・
出金額の高い順に3件を取得する
SELECT 費目, 出金額 FROM 家計簿テーブル ORDER BY 出金額 DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
3番目に高い出金額だけを取得する
SELECT 費目,出金額 FROM 家計簿テーブル ORDER BY 出金額 DESC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY
・・・
【注文履歴テーブル】
列名｜データ型｜備考
日付｜DATE｜
注文番号｜INTEGER｜注文順に振られた連番（主キー）
注文枝番｜INTEGER｜注文ごとの明細番号（主キー）
商品名｜VARCHAR(50)
分類｜CHAR(1)｜1:ドリンク 2:フード 3:その他
サイズ｜CHAR(1)｜S、M、L（ドリンクのみ）X:サイズなし（ドリンク以外）
単価｜INTEGER｜
数量｜INTEGER｜
注文金額｜INTEGER｜
1.注文順かつその明細順に、すべての注文データを取得する
SELECT * FROM 注文履歴テーブル ORDER BY 注文番号 ASC, 注文枝番 ASC
2.2022年1月に注文のあった商品名の一覧を商品名順に取得する
SELECT DISTINCT 商品名 FROM 注文履歴テーブル WHERE 日付 >= 2022-01-01 AND 日付 <= 2022-01-31 ORDER BY 商品名
3.ドリンクの商品を対象に、注文金額の低いほうから2～4番目の注文の注文番号と注文枝番、注文金額を取得する
SELECT 注文番号, 注文枝番, 注文金額 FROM 注文履歴テーブル WHERE 分類 = '1' ORDER BY 注文金額 OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY
4.その他の商品について、2つ以上同時に購入された商品を取得し、日付、商品名、単価、数量、注文金額を購入日順に表示する。ただし、同日に売り上げたものは、数量の多い順に表示する
SELECT 日付, 商品名, 単価, 数量, 注文金額 FROM 注文履歴テーブル WHERE 分類 = '3' AND 数量 >= 2 ORDER BY 日付, 数量 DESC
5.商品の分類ごとに、分類、商品名、サイズ、単価を1つの表として取得する。また、サイズはドリンクの商品についてのみ表示し、分類と商品名順に並べること。
SELECT DISTINCT 分類, 商品名, サイズ, 単価 WHERE 分類 = '1'
UNION
SELECT DISTINCT 分類, 商品名, NULL, 単価 WHERE 分類 = '2'
UNION
SELECT DISTINCT 分類, 商品名, NULL, 単価 WHERE 分類 = '3'
ORDER BY 分類, 商品名
・・・
列名｜データ型｜備考
値｜INTEGER｜テーブル名に応じた-10～10の値
1.和集合の結果、整数テーブルと等しくなる
SELECT * FROM 奇数テーブル UNION SELECT * FROM 偶数テーブル
2.差集合の結果、奇数テーブルと等しくなる
SELECT * FROM 整数テーブル EXCEPT SELECT * FROM 偶数テーブル
3.積集合の結果、偶数テーブルと等しくなる
SELECT * FROM 整数テーブル INTERSECT SELECT * FROM 偶数テーブル
4.検索結果なし
SELECT * FROM 奇数テーブル INTERSECT SELECT * FROM 偶数テーブル
・・・
計算式に別名を付ける
SELECT 出金額, 出金額 + 100 AS 百円増しの出金額 FROM 家計簿
INSERT文での計算式
INSERT INTO 家計簿 (出金額) VALUES (1000 + 105)
・・・
2025/3/6
・・・
CASE 演算子を使ったSELECT文
SELECT 費目, 出金額,
CASE 費目 WHEN '居住費' THEN '固定費'
WHEN '水道光熱費' THEN '固定費'
ELSE '変動費'
END AS 出費の分類
FROM 家計簿 WHERE 出金額 > 0
・・・
CASE 演算子を使ったSELECT文（２）
SELECT 費目, 入金額,
CASE WHEN 入金額 < 5000 THEN 'お小遣い'
WHEN 入金額 < 100000 THEN '一時入金'
WHEN 入金額 < 300000 THEN '給料出たー！'
ELSE '想定外の収入です！'
END AS 収入の分類
FROM 家計簿
WHERE 入金額 > 0
・・・
メモとメモの長さを併せて表示する
SELECT メモ, LENGTH(メモ) AS メモの長さ FROM 家計簿
・・・
10文字以下のメモだけを取得する
SELECT メモ, LENGTH(メモ) AS メモの長さ FROM 家計簿 WHERE LENGTH(メモ) <= 10
・・・
メモの一部を置換する
UPDATE 家計簿 SET メモ = REPLACE(メモ, '購入', '買った')
・・・
費目列の1～3文字目に「費」があるものを抽出
SELECT * FROM 家計簿
WHERE SUBSTRING(費目, 1, 3) LIKE '%費%'
・・・
費目とメモをつなげて抽出する
SELECT CONCAT(費目, '：' || メモ) FROM 家計簿
・・・
百円単位の出金額を取得する
SELECT 出金額, ROUND(出金額, -2) AS 百円単位の出金額 FROM 家計簿
・・・
現在の日付を取得して登録する
INSERT INTO 家計簿 VALUES (CURRENT_DATE, '食費', 'ドーナツを買った', 0, 260)
・・・
NULLを明示的に表示する
SELECT 日付, 費目, COALESCE(メモ, '（メモはNULLです）') AS メモ, 入金額, 出金額 FROM 家計簿
・・・
家計簿で入出金の差額も表示したい
SELECT 日付, 費目, メモ, 入金額, 出金額, 入金額 - 出金額 AS 入出金差額 FROM 家計簿
・・・
8文字以上のメモは、「…」で末尾を省略したい
SELECT 日付, 費目,
CASE WHEN LENGTH(メモ) >= 8 THEN SUBSTRING(メモ, 1, 8) || '…'
ELSE メモ
END AS メモ, 入金額, 出金額
FROM 家計簿
・・・
1ドル=110円と仮定して、入出金をドルで表示（小数点以下切り捨て）したい。
SELECT 日付, TRUNC(入金額/110.0, 0) AS 入金ドル, TRUNC(出金額/110.0, 0) AS 出金ドル FROM 家計簿
・・・
間違って未来の日付で登録されている行を探したい
SELECT * FROM 家計簿 WHERE 日付 > CURRENT_DATE
・・・
家計簿のメモを表示したい。メモが未登録の行では代わりに費目を、費目も未登録の場合は、'不明'と表示したい
SELECT 日付, COALESCE(メモ, 費目, '不明') AS 備考 FROM 家計簿
・・・
【試験結果テーブル】
受験者ID｜午前｜午後1｜午後2｜論述｜平均点｜
SW1046｜86｜(A)｜68｜91｜80｜
SW1350｜65｜53｜70｜(B)｜68｜
SW1877｜(C)｜59｜56｜36｜56｜
1.現在登録されているデータをもとに、(A)～(C)に当てはまる点数をそれぞれ受験者IDごとに計算して登録する
UPDATE 試験結果テーブル SET 午後1 = (80 * 4) - (86 + 68 + 91) WHERE 受験者ID = 'SW1046'
UPDATE 試験結果テーブル SET 論述 = (68 * 4) - (65 + 53 + 70) WHERE 受験者ID = 'SW1350'
UPDATE 試験結果テーブル SET 午前 = (56 * 4) - (59 + 56 + 36) WHERE 受験者ID = 'SW1877'
2.
SELECT 受験者ID AS 合格者ID FROM 試験結果テーブル WHERE 午前 >= 60 AND 午後1 + 午後2 >= 120 AND 論述 >= (午前 + 午後1 + 午後2 + 論述) * 0.3
・・・
【回答者テーブル】
メールアドレス｜国名｜住居｜年齢｜
test.jp｜NULL｜D｜51｜
test.uk｜NULL｜C｜26｜
test.cn｜NULL｜C｜35｜
test.fr｜NULL｜D｜43｜
test.vn｜NULL｜D｜22｜
1.メールアドレスの最後の2文字が国コードであることを利用して、国名を登録したい。国コードを日本語の国名に変換のうえ、国名列を更新する。ただし、1つのSQL文で全行を更新すること。
UPDATE 回答者テーブル SET 国名 =
CASE SUBSTRING(TRIM(メールアドレス), LENGTH(TRIM(メールアドレス))-1, 2)
WHEN 'jp' THEN '日本'
WHEN 'uk' THEN 'イギリス'
WHEN 'cn' THEN '中国'
WHEN 'fr' THEN 'フランス'
WHEN 'vn' THEN 'ベトナム'
END
・・・
メールアドレスと住居、年齢を一覧表示する。ただし、次の条件を満たした形で表示すること。
(1)メールアドレスの余分な空白は除去する
(2)住居と年齢は1つの項目とし、年齢は年代として表示する。ただし、20～50代のみ考慮する。
(3)項目の見出しはそれぞれ「メールアドレス」「属性」とする。なお、住居はDが戸建て、Cが集合住宅を表す
SELECT TRIM(メールアドレス) AS メールアドレス,
CASE SUBSTRING(年齢, 1, 1)
WHEN '2' THEN '20代'
WHEN '3' THEN '30代'
WHEN '4' THEN '40代'
WHEN '5' THEN '50代'
END
|| ':' ||
CASE 住居
WHEN 'D' THEN '戸建て'
WHEN 'C' THEN '集合住宅',
END
AS 属性
FROM 回答者テーブル
・・・
【受注テーブル】
｜受注日｜受注ID｜文字｜文字数｜書体コード｜
｜2021-12-05｜101｜Satou｜NULL｜2｜
｜2021-12-05｜102｜鈴木一郎｜NULL｜3｜
｜2021-12-05｜113｜横浜BASEBALLCLUB｜NULL｜1｜
｜2021-12-08｜140｜N.R.｜NULL｜NULL｜
1.依頼された文字は、何文字の刺繍が必要かを求める。「文字」列のデータをもとに、1つのSQL文で「文字数」列の全行を更新する。
UPDATE 受注テーブル SET 文字数 = LENGTH(TRIM(文字))
2.受注内容を一覧表示する。一覧には、受注日、受注ID、文字数、書体名、単価、特別加工料を受注日および受注ID順に表示したい。
SELECT 受注日, 受注ID, 文字数,
CASE COALESCE(書体コード, '1')
WHEN '1' THEN 'ブロック体'
WHEN '2' THEN '筆記体'
WHEN '3' THEN '草書体'
END AS 書体名,
CASE COALESCE(書体コード, '1')
WHEN '1' THEN 100
WHEN '2' THEN 150
WHEN '3' THEN 200
END AS 単価,
CASE WHEN 文字数 > 10 THEN 500
ELSE 0
END AS 特別加工料
FROM 受注テーブル
ORDER BY 受注日 ASC, 受注ID ASC
3.受注IDが113の注文に対して、文字の一部を変更したいという依頼があった。登録されている文字を次の依頼内容に合わせて更新する
UPDATE 受注テーブル SET 文字 = REPLACE(文字, ' ', '★') WHERE 受注ID = '113'
・・・
集計結果で絞り込む
SELECT 費目, SUM(出金額) AS 費目別の出金額合計
FROM 家計簿
GOUP BY 費目
HAVING SUM(出金額) > 0
・・・
今月の収入と支出の合計額を知りたい
SELECT SUM(収入), SUM(支出) FROM 家計簿
・・・
今月の食費を支払った回数を知りたい
SELECT COUNT(費目) FROM 家計簿 GROUP BY 費目 HAVING 費目 = '食費'
SELECT COUNT(費目) FROM 家計簿 WHERE 費目 = '食費'
・・・
先月までの水道光熱費で、最も高かった額と低かった額を知りたい
SELECT MAX(費目), MIN(費目) FROM 家計簿 WHERE 費目 = '水道光熱費'
・・・
先月までの給料の平均額を知りたい
SELECT AVG(費目) FROM 家計簿 WHERE 費目 = '給料' AND 日付 < 2025-03-01
・・・
先月までの費目ごとの出費額を知りたい
SELECT 費目, SUM(出費額) FROM 家計簿アーカイブ GROUP BY 費目
・・・
今月の出費のうち、平均が5000円以上の費目とその最大額を知りたい
SELECT 費目 AS 平均が5000円以上の費目, MAX(出費) AS 最大額 FROM 家計簿
WHERE 出費 > 0
GROUP BY 費目
HAVING AVG(出費) >= 5000
・・・
｜列名｜データ型｜備考｜
｜都市名｜VARCHAR(20)｜「熊谷」「博多」など｜
｜月｜INTEGER｜1～12のいずれかの数値｜
｜降水量｜INTEGER｜観測データがないものはNULL｜
｜最高気温｜INTEGER｜観測データがないものはNULL｜
｜最低気温｜INTEGER｜観測データがないものはNULL｜
1.日本全体としての年間降水量の合計と、年間の最高気温・最低気温の平均
SELECT SUM(降水量), AVG(最高気温), AVG(最低気温)
FROM 都市別気象観測テーブル
2.都市名「東京」の年間降水量と、各月の最高気温、最低気温の平均
SELECT SUM(降水量) AS 年間降水量, AVG(最高気温) AS 最高気温の平均, AVG(最低気温) AS 最低気温の平均 FROM 都市別気象観測テーブル WHERE 都市名 = '東京'
3.各都市の降水量の平均と、最も低かった最高気温、最も高かった最低気温
SELECT 都市名, AVG(降水量), MIN(最高気温), MAX(最低気温) FROM 都市別気象観測テーブル GROUP BY 都市名
4.月別の降水量、最高気温、最低気温の平均
SELECT 月, AVG(降水量), AVG(最高気温), AVG(最低気温) FROM 都市別気象観測テーブル GROUP BY 月
5.1年間で最も高い最高気温が38度以上を記録した月のある都市名とその気温
SELECT 都市名, MAX(最高気温) FROM 都市別気象観測テーブル WHERE 最高気温 >= 38 GROUP BY 都市名
SELECT 都市名, MAX(最高気温) FROM 都市別気象観測テーブル GROUP BY 都市名 HAVING MAX(最高気温) >= 38
6.1年間で最も低い最低気温が-10度以下を記録した月のある都市名とその気温
SELECT 都市名, MIN(最低気温) FROM 都市別気象観測テーブル WHERE 最低気温 <= -10 GROUP BY 都市名
SELECT 都市名, MIN(最低気温) FROM 都市別気象観測テーブル GROUP BY 都市名 HAVING MIN(最低気温) <= -10
・・・
入退室管理テーブル
｜列名｜データ型｜備考｜
｜日付｜DATE｜入室した日付｜
｜退室｜CHAR(1)｜NULL：入室中, 1：退室済み｜
｜社員名｜VARCHAR(20)｜入室した社員名｜
｜事由区分｜CHAR(1)｜入室事由を表すコード, 1：メンテナンス, 2：リリース作業, 3：障害対応, 9：その他｜
1.現在入室中の社員数を取得する
SELECT COUNT(*) AS 現在入室中の社員数 FROM 入退室管理テーブル WHERE 退室 is NULL
2.社員ごとの入室回数を、回数の多い順に取得する
SELECT 社員名, COUNT(社員名) AS 入室回数 FROM 入室管理テーブル GROUP BY 社員名 ORDER BY 2 DESK
3.事由区分ごとの入室回数を取得する（事由区分はわかりやすく表示する）
SELECT
CASE 事由区分
WHEN '1' THEN 'メンテナンス'
WHEN '2' THEN 'リリース作業'
WHEN '3' THEN '障害対応'
WHEN '9' THEN 'その他'
END AS 事由,
COUNT(*) AS 入室回数
FROM 入退室管理テーブル GROUP BY 事由区分
4.入室回数が10回を超過する社員について、社員名と入室回数を取得する
SELECT 社員名, COUNT(*) AS 入室回数 FROM 入室管理テーブル GROUP BY 社員名 HAVING COUNT(*) > 10
5.これまでに障害対応が発生した日付と、それに対応した社員数を取得する
SELECT 日付, COUNT(社員名) AS 社員数 FROM 入室管理テーブル WHERE 事由区分 = '3' GROUP BY 日付
・・・
最も大きな出費の費目と金額を求める
SELECT 費目, 出金額 FROM 家計簿 WHERE 出金額 = (SELECT MAX(出金額) FROM 家計簿)
・・・
FROM句で副問い合わせを利用する
SELECT SUM(SUB.出金額) AS 出金額合計
FROM (SELECT 日付, 費目, 出金額 FROM 家計簿 UNION SELECT 日付, 費目, 出金額 FROM 家計簿アーカイブ WHERE 日付>= '2022-01-01' AND 日付 <= '2022-01-31') AS SUB
・・・
1.
SELECT 価格 * (SELECT SUM(レンタル日数) FROM レンタル WHERE 車種コード = 'E01') AS 金額 FROM 料金 WHERE 車種コード = 'E01'
副問い合わせ：SUM(レンタル日数)｜3
全体：金額｜25200
2.
SELECT 車種コード,車種名 FROM 料金 WHERE 車種コード IN (SELECT 車種コード FROM レンタル WHERE レンタル日数 > 1) ORDER BY 車種コード
副問い合わせ：車種コード｜S01｜E01｜S02｜
全体：車種コード｜車種名｜E01｜エコカー｜S01｜軽自動車｜S02｜ハッチバック｜
3.
SELECT SUM(SUB.日数) AS 合計日数, COUNT(SUB.車種コード) AS 車種数 FROM (SELECT 車種コード, SUM(レンタル日数) AS 日数 FROM レンタル GROUP BY 車種コード) AS SUB
副問い合わせ：車種コード｜日数｜S02｜6｜S01｜3｜E01｜3｜
全体：合計日数｜車種数｜12｜3｜
・・・
個体識別テーブル
｜列名｜データ型｜備考｜
｜個体識別番号｜CHAR(4)｜牛を一意に管理する番号｜
｜出生日｜DATE｜その牛が出生した日付｜
｜雌雄コード｜CHAR(1)｜牛の性別を表すコード 1:雄 2:雌｜
｜母牛番号｜CHAR(4)｜母牛の個体識別番号｜
｜品種コード｜CHAR(2)｜牛の品種を表すコード 01:乳用種 02:肉用種 03:交雑種｜
｜飼育県｜VARCHAR(10)｜牛を飼育している都道府県名｜
1.
飼育県別に飼育頭数をカウントし、その結果を次の頭数集計テーブルに登録する
INSERT INTO 頭数集計テーブル (SELECT 飼育県, COUNT(*) FROM 個体識別テーブル GROUP BY 飼育県)
2.
1.で作成した頭数集計テーブルで、飼育頭数の多いほうから3つの都道府県で飼育されている牛のデータを、個体識別テーブルより抽出する。抽出する項目は、都道府県名、個体識別番号、雌雄とする。ただし、雌雄はコードではなく「雄」、「雌」の日本語表記とする。
SELECT 飼育県 AS 都道府県名,
個体識別番号,
CASE 雌雄コード WHEN '1' THEN '雄'
WHEN '2' THEN '雌'
END AS 雌雄
FROM 個体識別テーブル WHERE 飼育県 IN (SELECT 飼育県 FROM 頭数集計テーブル ORDER BY 頭数 DESK OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY)
3.
個体識別テーブルには母牛についてもデータ登録されており、母牛が乳用種である牛の一覧を個体識別テーブルより抽出したい。抽出する項目は、個体識別番号、品種、出生日、母牛番号とする。なお、品種は、コードではなく、乳用種、肉用種、交雑種、に日本語表記とする
SELECT 個体識別番号,
CASE 品種コード WHEN '01' THEN '乳用種'
WHEN '02' THEN '肉用種'
WHEN '03' THEN '交雑種'
END AS 品種,
出生日, 母牛番号 FROM 個体識別テーブル WHERE 母牛番号 IN (SELECT 個体識別番号 FROM 個体識別テーブル WHERE 品種コード = '01')
・・・
次のようなテーブルAとテーブルBがあります。これらを用いて、下のSQL文を実行したときの結果表を記述してください
テーブルA
｜A1｜A2｜
｜1｜3｜
｜2｜4｜
テーブルB
｜B1｜B2｜
｜1｜2｜
｜3｜NULL｜
1.
SELECT A1,A2,B1,B2 FROM A JOIN B ON A.A1 = B.B1
｜A1｜A2｜B1｜B2｜
｜1｜3｜1｜2｜
2.
SELECT A1,A2,B1,B2 FROM B JOIN A ON B.B2 = A.A1
｜A1｜A2｜B1｜B2｜
｜2｜4｜1｜2｜
3.
SELECT A1,A2,B1,B2 FROM B LEFT JOIN A ON B.B2 = A.A1
｜B1｜B2｜A1｜A2｜
｜1｜2｜2｜4｜
｜3｜NULL｜NULL｜NULL｜
｜A1｜A2｜B1｜B2｜
｜2｜4｜1｜2｜
｜NULL｜NULL｜3｜NULL｜
4.
SELECT A.A1,C.A2,B1,B2 FROM A JOIN B ON A.A1 = B.B1 JOIN A AS C ON B.B1 = C.A1
｜A1｜A2｜B1｜B2｜A1｜A2｜
｜1｜3｜1｜2｜1｜3｜
｜A.A1｜,C.A2｜B1｜B2｜
｜1｜3｜1｜2｜
・・・
1.部署名が入った全社員の一覧表
SELECT 社員.社員番号, 社員.名前 AS 名前, 部署.名前 AS 部署名 FROM 社員 LEFT JOIN 部署 ON 社員.部署ID = 部署.部署ID
2.上司の名前が入った全社員の一覧表
SELECT S1.社員番号, S1.名前 AS 名前, S2.名前 AS 上司名 FROM 社員 AS S1 LEFT JOIN 社員 AS S2 ON S1.上司ID = S2.社員番号
3.部署名と勤務地が入った社員一覧表
SELECT S.社員番号 AS 社員番号, S.名前 AS 名前, B.名前 AS 部署名, S2.名前 AS 勤務地 FROM 社員 AS S JOIN 部署 AS B ON S.部署ID = B.部署ID JOIN 支店 AS S2 ON S.勤務地ID = S2.支店ID
4.支店ごとの支店長名と社員数の一覧表
5.上司と違う勤務地（離れて勤務している）社員の一覧表
・・・
CREATE TABLE 家計簿 (
日付 DATE,
費目ID INTEGER,
メモ VARCHAR(100) DEFAULT '不明',
入金額 INTEGER DEFAULT 0,
出金額 INTEGER DEFAULT 0
)
・・・
CREATE TABLE 家計簿 (
日付 DATE NOT NULL,
費目ID INTEGER,
メモ VARCHAR(100) DEFAULT '不明' NOT NULL,
入金額 INTEGER DEFAULT 0 CHECK(入金額 >= 0),
出金額 INTEGER DEFAULT 0 CHECK(出金額 >= 0)
);
CREATE TABLE 費目 (
ID INTEGER,
名前 VARCHAR(40) UNIQUE
);
・・・
学生テーブル
｜列名｜データ型｜備考｜
｜学籍番号｜CHAR(8)｜学生を一意に特定する番号(必須)｜
｜名前｜VARCHAR(30)｜学生の名前(必須)｜
｜生年月日｜DATE｜学生の生年月日(必須)｜
｜血液型｜CHAR(2)｜学生の血液型｜
｜学部ID｜CHAR(1)｜学部テーブルのID列の値を格納する外部キー｜
CREATE TABLE 学生 (
学籍番号 CHAR(8) PRIMARY KEY,
名前 VARCHAR(30) NOT NULL,
生年月日 DATE NOT NULL,
血液型 CHAR(2) CHECK(
血液型 IN ('A', 'B', 'O', 'AB') OR 血液型 IS NULL
),
学部ID CHAR(1) REFERENCES 学部(ID)
)
・・・
CREATE VIEW 家計簿4月 AS
SELECT * FROM 家計簿
WHERE 日付 >= '2022-04-01'
AND 日付 <= '2022-04-30'
・・・
SELECT ＊ FROM 家計簿4月;
SELECT DISTINCT 費目ID FROM 家計簿4月;
・・・
1.口座テーブルのすべてのデータを「＊」を用いずに抽出する
SELECT 口座番号,名義,種別,残高,更新日 FROM 口座
2.口座テーブルのすべての口座番号を抽出する
SELECT 口座番号 FROM 口座
3.口座テーブルのすべての口座番号と残高を抽出する
SELECT 口座番号,残高 FROM 口座
4.口座テーブルのすべてのデータを「＊」を用いて抽出する
SELECT * FROM 口座
5.口座テーブルのすべての名義を「XXXXX」に更新する
UPDATE 口座 SET 名義 = 'XXXXX'
6.口座テーブルのすべての残高を99999999、更新日を「2022-03-01」に更新する
UPDATE 口座 SET 残高=99999999, 更新日=2022-03-01
7.口座テーブルに次の3つのデータを1回の実行ごとに1つずつ登録する
INSERT INTO 口座 (口座番号,名義,種別,残高,更新日) VALUES ('0642191','アオキ ハルカ','1',3640551,2022-03-13)
・・・
8.口座テーブルのすべてのデータを削除する
DELETE FROM 口座
9.口座テーブルから、口座番号が「0037651」のデータを抽出する
SELECT * FROM 口座 WHERE 口座番号='0037651'
10.口座テーブルから、残高が0より大きいデータを抽出する
SELECT * FROM 口座 WHERE 残高 > 0
11.口座テーブルから、口座番号が「1000000」番より前のデータを抽出する
SELECT * FROM 口座 WHERE 口座番号 < '1000000'
12.口座テーブルから、更新日が2021年以前のデータを抽出する
SELECT * FROM 口座 WHERE 更新日 < 2021-01-01
13.口座テーブルから、残高が100万円以上のデータを抽出する
SELECT * FROM 口座 WHERE 残高 >= 1000000
14.口座テーブルから、種別が「普通」ではないデータを抽出する
SELECT * FROM 口座 WHERE 種別 <> '1'
15.口座テーブルから、更新日が登録されていないデータを抽出する
SELECT ＊FROM 口座 WHERE 更新日 IS NULL
16.口座テーブルから、「ハシ」を含む名義のデータを抽出する
SELECT * FROM 口座 WHERE 名義 LIKE '%ハシ%'
17.口座テーブルから、更新日が2022年1月の日付であるデータを抽出する。ただし、記述する条件式は1つであること
SELECT * FROM 口座 WHERE 更新日 BETWEEN '2022-01-01' AND '2022-01-31'
18.口座テーブルから、種別が「当座」または「別段」のデータを抽出する。ただし、記述する条件式は1つであること。
SELECT * FROM 口座 WHERE 種別 IN ('2','3')
19.口座テーブルから、名義が「サカタ リョウヘイ」「マツモト ミワコ」「ハマダ サトシ」のデータを抽出する
SELECT * FROM 口座 WHERE 名義 IN ('サカタ リョウヘイ','マツモト ミワコ','ハマダ サトシ')
・・・
20.口座テーブルから、更新日が2021年12月30日から2022年1月4日であるデータを抽出する
SELECT * FROM 口座 WHERE 更新日 BETWEEN 2021-12-30 AND 2022-01-04
21.口座テーブルから、残高が1万円未満で、更新日が登録されているデータを抽出する
SELECT * FROM 口座 WHERE 残高 < 10000 AND 更新日 IS NOT NULL
22.口座テーブルから、次の条件のいずれかに当てはまるデータを抽出する
・口座番号が「2000000」番台
・名義の姓が「エ」から始まる3文字で、名が「コ」で終わる
SELECT * FROM 口座 WHERE 口座番号 LIKE'2______' OR 名義 LIKE 'エ__ %コ'
23.口座テーブル、取引テーブル、取引事由テーブルにおいて主キーの役割を果たしている列名を日本語で解答する
・・・
24.口座テーブルから、口座番号順にすべてのデータを抽出する。ただし、並び替えには列名を指定し、昇順にすること。
SELECT * FROM 口座 ORDER BY 口座番号
25.口座テーブルから、名義の一覧を取得する。データの重複を除外し、名義の昇順にすること
SELECT DISTINCT 名義 FROM 口座 ORDER BY 名義
26.口座テーブルから、残高の大きい順にすべてのデータを抽出する。残高が同額の場合には口座番号の昇順にし、並び替えには列番号を指定すること
SELECT * FROM 口座 ORDER BY 4 DESK, 1
27.口座テーブルから、更新日を過去の日付順に10件抽出する。ただし、更新日の設定がないデータは除くこと
SELECT 更新日 FROM 口座 WHERE 更新日 IS NOT NULL ORDER BY 更新日 OFFSET 0 ROWS FETCH NEXT 10ROWS ONLY
28.口座テーブルから、更新日と残高を、残高の小さい順に11～20件目のみを抽出する。ただし、残高が0円または更新日の設定がないデータは除外し、残高が同額の場合には更新日の新しい順（降順）とする
SELECT 更新日,残高 FROM 口座 WHERE 残高 > 0 AND 更新日 IS NOT NULL ORDER BY 残高, 更新日 DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
29.口座テーブルと廃止口座テーブルに登録されている口座番号を昇順に抽出する
SELECT 口座番号 FROM 口座 UNION
SELECT 口座番号 FROM 廃止口座 ORDER BY 1
30.口座テーブルに登録されている名義のうち、廃止口座テーブルには存在しない名義を抽出する。重複したデータは除き、降順で並べること
SELECT 名義 FROM 口座 EXCEPT SELECT 名義 FROM 廃止口座 ORDER BY 1 DESC
31.口座テーブルと廃止口座テーブルの両方に登録されている名義を昇順に抽出する
SELECT 名義 FROM 口座 INTERSECT SELECT 名義 FROM 廃止口座 ORDER BY 1
32.口座テーブルと廃止口座テーブルに登録されている口座番号と残高の一覧を取得する。ただし、口座テーブルは残高が0のもの、廃止口座テーブルは解約時残高が0でないものを抽出の対象とする。一覧は口座番号順とする。
SELECT 口座番号,残高 FROM 口座 WHERE 残高 = 0 UNION SELECT 口座番号,解約時残高 FROM 廃止口座 WHERE 解約時残高 <> 0 ORDER BY 1
33.口座テーブルと廃止口座テーブルに登録されている口座番号と名義の一覧を取得する。一覧は名義の昇順にし、その口座の状況がわかるように、有効な口座には「〇」を、廃止した口座には「×」を一覧に付記すること
SELECT 口座番号,名義,'〇' AS 口座区分 FROM 口座 UNION SELECT 口座番号,名義,'×' AS 口座区分FROM 廃止口座 ORDER BY 名義
34.口座テーブルから、残高が100万円以上の口座番号と残高を抽出する。ただし、残高は千円単位で表記し、見出しを「千円単位の残高」とする
SELECT 口座番号,残高 / 1000 AS 千円単位の残高 FROM 口座 WHERE 残高 >= 1000000
35.口座テーブルに次の3つのデータを1回の実行ごとに1つずつ登録する。ただし、キャンペーンにより登録時に残高を3000円プラスする
INSERT INTO 口座 (口座番号,名義,種別,残高,更新日) VALUES ('0652281','タカギ ノブオ','1',100000 + 3000,'2022-04-01');
・・・
36.35の問題で登録したデータについて、キャンペーンの価格が間違っていたことが判明した。該当するデータの残高それぞれから3000円を差し引き、あらためて残高の0.3%を上乗せした金額になるよう更新する
UPDATE 口座 SET 残高 = (残高 - 3000) * 1.003 WHERE 口座番号 IN ('0652281','1026413','2239710')
37.口座テーブルから、更新日が2020年以前のデータを対象に、口座番号、更新日、通帳期限日を抽出する。通帳期限日は、更新日の180日後とする。
SELECT 口座番号, 更新日, 更新日 + 180 AS 通帳期限日 FROM 口座 WHERE 更新日 < '2021-01-01'
38.口座テーブルから、種別が「別段」のデータについて、口座番号と名義を抽出する。ただし、名義の前に「カ)」を付記すること
SELECT 口座番号, 'カ)' || 名義 AS 名義 FROM 口座 WHERE 種別 = '3'
39.口座テーブルから、登録されている種別の一覧を取得する。見出しは「種別コード」と「種別名」とし、種別名には日本語名を表記する
SELECT DISTINCT 種別 AS 種別コード,
CASE 種別 WHEN '1' THEN '普通'
WHEN '2' THEN '当座'
WHEN '3' THEN '別段'
END AS 種別名
FROM 口座
40.口座テーブルから、口座番号、名義、残高ランクを抽出する。残高ランクは、残高が10万円未満を「C」、10万円以上100万円未満を「B」、それ以外を「A」とする
SELECT 口座番号, 名義, CASE WHEN 残高 < 100000 THEN 'C' WHEN 残高 >= 100000 AND 残高 < 1000000 THEN 'B' ELSE 'A' END AS 残高ランク FROM 口座
41.口座テーブルから、口座番号、名義、残高の文字数を抽出する。ただし、名義の姓名の間の全角スペースは除外すること
SELECT LENGTH(口座番号),LENGTH(REPLACE(名義, ' ', '')),LENGTH(CAST(残高 AS VARCHAR)) FROM 口座
42.口座テーブルから、名義の1～5文字目に「カワ」が含まれるデータを抽出する
SELECT * FROM 口座 WHERE SUBSTRING(名義, 1, 5) LIKE '%カワ%'
43.口座テーブルから、残高の桁数が4桁以上で、1000円未満の端数がないデータを抽出する。ただし、どちらの条件も文字数を求める関数を使って判定すること
SELECT * FROM 口座 WHERE LENGTH(CAST(残高 AS VARCHAR)) >= 4 AND SUBSTRING(CAST(残高 AS VARCHAR), LENGTH(CAST(残高 AS VARCHAR)) - 2, 3) = '000'
44.口座テーブルから、口座番号、残高、利息を残高の降順に抽出する。利息は、残高に普通預金利息0.02%を掛けて求め、1円未満を切り捨てること
SELECT 口座番号, 残高, TRUNC(残高 * 0.0002, 0) AS 利息 FROM 口座 ORDER BY 残高 DESC
45.口座テーブルから、口座番号、残高、残高別利息を抽出する。残高別利息は、残高が50万円未満を0.01%、50万円以上200万円未満を0.02%、200万円以上を0.03%として計算し、1円未満を切り捨てる。一覧は、残高別利息の降順、口座番号の昇順に並べること
SELECT 口座番号,残高,CASE
WHEN 残高 < 500000 THEN TRUNC(残高 * 0.0001, 0)
WHEN 残高 >= 500000 AND 残高 < 2000000 THEN TRUNC(残高 * 0.0002, 0)
WHEN 残高 >= 2000000 THEN TRUNC(残高 * 0.0003, 0)
END AS 残高別利息
FROM 口座 ORDER BY 残高別利息 DESC, 口座番号
46.口座テーブルに以下にある3つのデータを1回の実行ごとに1つずつ登録する。ただし、更新日は現在の日付を求める関数を利用して指定すること
INSERT INTO 口座 (口座番号,名義,種別,残高,更新日) VALUES ('0351262','イトカワ ダイ','2',635110,CURRENT_DATE);
・・・
47.口座テーブルから更新日が2022年以降のデータを抽出する。その際、更新日は「2022年01月01日」のような形式で抽出すること
SELECT 口座番号,名義,種別,残高, SUBSTRING(CAST(更新日 AS VARCHAR),1,4) || '年' || SUBSTRING(CAST(更新日 AS VARCHAR),6,2) || '月' || SUBSTRING(CAST(更新日 AS VARCHAR),9,2) || '日' AS 更新日 FROM 口座 WHERE 更新日 > '2021-01-31'
48.口座テーブルから更新日を抽出する。更新日が登録されていない場合は、「設定なし」と表記すること
SELECT COALESCE(CAST(更新日 AS VARCHAR), '設定なし') AS 更新日 FROM口座
49.口座テーブルから、残高の合計、最大、最小、平均、登録データ件数を求める
SELECT SUM(残高) AS 残高の合計, MAX(残高) AS 最大, MIN(残高) AS 最小, AVG(残高) AS 平均, COUNT(*) AS 登録データ件数 FROM 口座
50.口座テーブルから、種別が「普通」以外、残高が100万円以上、更新日が2021年以前のデータ件数を求める
SELECT COUNT(*) AS 件数 FROM 口座 WHERE 種別 <> '1' AND 残高 >= 1000000 AND 更新日 < '2022-01-01'
51.口座テーブルから、更新日が登録されていないデータ件数を求める。ただし、条件式は用いないこと
SELECT COUNT(*) - COUNT(更新日) AS 更新日が登録されていない件数 FROM 口座
52.口座テーブルから、名義の最大値と最小値を求める
SELECT MAX(名義) AS 最大値, MIN(名義) AS 最小値 FROM 口座
53.口座テーブルから、最も新しい更新日と最も古い更新日を求める
SELECT MAX(更新日) AS 新しい更新日, MIN(更新日) AS 古い更新日 FROM 口座
54.口座テーブルから、種別ごとの残高の合計、最大、最小、平均、および登録されているデータ件数を求める
SELECT 種別, SUM(残高) AS 残高の合計, MAX(残高) AS 最大, MIN(残高) AS 最小, AVG(残高) AS 平均, COUNT(*) AS データ件数 FROM 口座 GOUP BY 種別
55.口座テーブルから、口座番号の下1桁目が同じ数字であるものを同じグループとし、それぞれのデータ件数を求める。ただし、件数の多い順に並べること。
SELECT SUBSTRING(口座番号, 7, 1) AS 口座番号下1桁, COUNT(*) AS 件数 FROM 口座 GROUP BY SUBSTRING(口座番号, 7, 1) ORDER BY 件数 DESC
56.口座テーブルから、更新日の年ごとの残高の合計、最大、最小、平均、登録データ件数を求める。ただし、更新日の登録がないデータは、「XXXX年」として集計する
SELECT SUBSTRING(COALESCE(CAST(更新日 AS VARCHAR), 'XXXX'),1,4) AS 更新年,SUM(残高) AS 残高の合計,MAX(残高) AS 最大,MIN(残高) AS 最小,AVG(残高) AS 平均, COUNT(*) AS 登録データ件数 FROM 口座 GROUP BY SUBSTRING(COALESCE(CAST(更新日 AS VARCHAR), 'XXXX年'),1,4)
57.口座テーブルから、種別ごとの残高の合計とデータ件数を求める。ただし、合計が300万円以下のものは一覧から取り除く
SELECT 種別, SUM(残高) AS 残高の合計, COUNT(*) AS 件数 FROM 口座 GOUP BY 種別 HAVING SUM(残高) > 3000000
58.口座テーブルから、名義の1文字目が同じグループごとに、データ件数と名義文字数の平均を求める。ただし、件数が10件以上、または文字数の平均が5文字より多いものを抽出の対象とする。なお、名義の全角スペースは文字数に含めない
SELECT SUBSTRING(名義, 1, 1) AS 名義の1文字目, COUNT(名義) AS 件数, AVG(LENGTH(REPLACE(名義, ' ', ''))) AS 名義文字数の平均 FROM 口座 GROUP BY SUBSTRING(名義, 1, 1) HAVING COUNT(名義) >= 10 OR AVG(LENGTH(REPLACE(名義, ' ', ''))) > 5
・・・
59.次の口座について、取引日の取引結果を口座テーブルの残高に反映する。更新には、SET句にて取引テーブルを副問い合わせするUPDATE文を用いること
・口座番号：0351333、取引日：2022-01-11
UPDATE 口座 SET 残高 = 残高 + (SELECT COALESCE(SUM(入金額), 0) - COALESCE(SUM(出金額), 0) FROM 取引 WHERE 口座番号 = '0351333' AND 日付 = '2022-01-11'), 更新日 = '2022-01-11' WHERE 口座番号 = '0351333'
60.次の口座について、現在の残高と、取引日に発生した取引による入出金額それぞれの合計金額を取得する。取得には、選択列リストにて取引テーブルを副問い合わせするSELECT文を用いること。
・口座番号：1115600、取引日：2021-12-28
SELECT 残高 AS 現在の残高, (SELECT SUM(COALESCE(入金額, 0)) FROM 取引 WHERE 口座番号 = '1115600’ AND 日付 = '2021-12-28') AS 入金額, (SELECT SUM(COALESCE(出金額, 0)) FROM 取引 WHERE 口座番号 = '1115600’ AND 日付 = '2021-12-28') AS 出金額 FROM 口座 WHERE 口座番号 = '1115600’
61.これまで1回の取引で100万円以上の入金があった口座について、口座番号、名義、残高を取得する。ただし、WHERE句でIN演算子を利用した副問い合わせを用いること
SELECT 口座番号, 名義, 残高 FROM 口座 WHERE 口座番号 IN (SELECT DISTINCT 口座番号 FROM 取引 WHERE 入金額 >= 1000000)
62.取引テーブルの日付よりも未来の更新日を持つ口座テーブルのデータを抽出する。ただし、WHERE句でALL演算子を利用した問い合わせを用いること
SELECT * FROM 口座 WHERE 更新日 > ALL (SELECT 日付 FROM 取引)
・・・
63.次の口座について、入金と出金の両方が発生した日付を抽出する。また、これまでの入金と出金それぞれの最大額もあわせて抽出する。FROM句で副問い合わせを用いること
・口座番号：3104451
SELECT A.日付, (SELECT MAX(入金額) FROM 取引 WHERE 口座番号 = '3104451') AS 最大入金額, (SELECT MAX(出金額) FROM 取引 WHERE 口座番号 = '3104451') AS 最大出金額 FROM (SELECT 日付 FROM 取引 WHERE 口座番号 = '3104451' GROUP BY 日付 HAVING SUM(入金額) > 0 AND SUM(出金額) > 0) AS A
・・・
64.次の口座について解約の申し出があった。副問い合わせを使って口座テーブルから廃止口座テーブルにデータを登録する。また、口座テーブルの該当データを削除する。ただし、データの整合性を保つことについては考慮
しなくてよい。
・口座番号：2761055
INSERT INTO 廃止口座 VALUES (SELECT * FROM 口座 WHERE 口座番号 = '2761055');
DELETE FROM 口座 WHERE 口座番号 = '2761055';
・・・
65.次の口座について、これまでの取引の記録を取引テーブルから抽出する。抽出する項目は口座番号、日付、取引事由名、取引金額とする。口座番号ごとに取引番号順で表示し、取引事由名については取引事由テーブルから日本語名を取得する。取引金額には、取引に応じて入金額か出金額のいずれか適切なほうを表示すること
・口座番号：0311240、1234161、2750902
SELECT T.口座番号, T.日付, J.取引事由名, COALESCE(T.入金額, T.出金額) AS 取引金額 FROM 取引 AS T JOIN 取引事由 AS J ON T.取引事由ID = J.取引事由ID WHERE T.口座番号 IN ('0311240', '1234161', '2750902') ORDER BY T.口座番号, T.取引番号
66.次の口座について、口座情報（口座番号、名義、残高）とこれまでの取引情報（日付、入金額、出金額）を一覧として抽出する。一覧は、取引の古い順に表示すること
・口座番号：0887132
SELECT K.口座番号,K.名義,K.残高,T.日付,T.入金額,T.出金額 FROM 口座 AS K JOIN 取引 AS T ON K.口座番号 = T.口座番号 WHERE K.口座番号 = '0887132' ORDER BY T.取引番号
67.2020年3月1日に取引のあった口座番号の一覧を取得する。一覧には、口座テーブルより名義と残高も表示すること。ただし、解約された口座については抽出しない
SELECT K.口座番号,K.名義,K.残高 FROM 口座 AS K JOIN 取引 AS T ON K.口座番号 = T.口座番号 WHERE T.日付 = '2020-03-01'
68.問題67では、すでに解約された口座については、該当の日付に取引があったにも関わらず抽出されなかった。解約された口座ももれなく一覧に記載されるよう、SQL文を変更する。なお、解約口座については、名義に「解約済み」、残高に0を表示すること
SELECT T.口座番号,COALESCE(K.名義, '解約済み') AS 名義,COALESCE(K.残高, 0) AS 残高 FROM 取引 AS T LEFT JOIN 口座 AS K ON T.口座番号 = K.口座番号 WHERE T.日付 = '2020-03-01'
69.取引テーブルのデータを抽出する。取引事由は「取引事由ID：取引事由名」の形式で表示し、これまでに発生しなかった取引事由についても併せて記載されるようにすること
SELECT T.取引番号,CAST(J.取引事由ID AS VARCHAR) || '：' || J.取引事由名 AS 取引事由,T.日付,T.口座番号,T.入金額,T.出金額 FROM 取引 AS T RIGHT JOIN 取引事由 AS J ON T.取引事由ID = J.取引事由ID
70.取引テーブルと取引事由テーブルから、取引事由の一覧を抽出する。一覧には、取引事由IDと取引事由名を記載する。なお、取引事由テーブルに存在しない事由で取引されている可能性、および取引の実績のない事由が存在する可能性を考慮すること
SELECT DISTINCT COALESCE(T.取引事由ID, J.取引事由ID), J.取引事由名 FROM 取引 AS T FULL JOIN 取引事由 AS J ON T.取引事由ID = J.取引事由ID
71.問題66について、取引事由名についても一覧に表示するよう、SQL文を変更する。取引事由名は取引情報（日付、取引事由名、入金額、出金額）に表示する
SELECT K.口座番号,K.名義,K.残高,T.日付,J.取引事由名,T.入金額,T.出金額 FROM 口座 AS K JOIN 取引 AS T ON K.口座番号 = T.口座番号 JOIN 取引事由 AS J ON T.取引事由ID = J.取引事由ID WHERE K.口座番号 = '0887132' ORDER BY T.取引番号
72.現在の残高が500万円以上の口座について、2022年以降に1回の取引で100万円以上の金額が入出金された実績を抽出する。抽出する項目は、口座番号、名義、残高、取引の日付、取引事由ID、入金額、出金額とする。ただし副問い合わせは用いないこと
SELECT K.口座番号,K.名義,K.残高,T.日付 AS 取引の日付,T.取引事由ID,T.入金額,T.出金額 FROM 口座 AS K JOIN 取引 AS T ON K.口座番号 = T.口座番号 WHERE K.残高 >= 5000000 AND T.日付 > '2021-01-31' AND (T.入金額 >= 1000000 OR T.出金額 >= 1000000)
73.問題72で作成したSQL文について、結合相手に副問い合わせを利用するようSQL文を変更する
SELECT K.口座番号,K.名義,K.残高,T.日付,T.取引事由ID,T.入金額,T.出金額 FROM 口座 AS K JOIN (SELECT 口座番号,日付,取引事由ID,入金額,出金額 FROM 取引 WHERE (入金額 >= 1000000 OR 出金額 >= 1000000) AND 日付 >= '2022-01-01') AS T ON K.口座番号 = T.口座番号 WHERE K.残高 >= 5000000
74.取引テーブルから、同一の口座で同じ日に3回以上取引された実績のある口座番号とその回数を抽出する。併せて、口座テーブルから名義を表示すること
SELECT K.口座番号, T.回数, K.名義 FROM 口座 AS K JOIN (SELECT 口座番号, COUNT(*) AS 回数 FROM 取引 GROUP BY 口座番号, 日付 HAVING COUNT(*) >= 3) AS T ON K.口座番号 = T.口座番号
75.この銀行では、口座テーブルの名寄せを行うことになった。同じ名義で複数の口座番号を持つ顧客について、次の項目を持つ一覧を取得する。
・名義、口座番号、種別、残高、更新日
一覧は名義のアイウエオ順、口座番号の小さい順に並べること
【集計関数と結合】
SELECT K1.名義, K1.口座番号, K1.種別, K1.残高, K1.更新日 FROM 口座 AS K1 WHERE K1.名義 IN (SELECT K2.名義 FROM 口座 AS K2 GROUP BY K2.名義 HAVING COUNT(K2.口座番号) > 1)
・・・
1.商品テーブルのすべてのデータを「*」を用いずに抽出する
SELECT 商品コード,商品名,単価,商品区分,関連商品コード FROM 商品
2.商品テーブルのすべての商品名を抽出する
SELECT 商品名 FROM 商品
3.注文テーブルのすべてのデータを「*」を用いて抽出する
SELECT * FROM 注文
4.注文テーブルのすべての注文番号、注文枝番、商品コードを抽出する
SELECT 注文番号,注文枝番,商品コード FROM 注文
5.商品テーブルに次の3つのデータを1回の実行ごとに1つずつ追加する
INSERT INTO 商品 (商品コード,商品名,単価,商品区分) VALUES ('W0461','冬のあったかコート',12800,'1');
INSERT INTO 商品 (商品コード,商品名,単価,商品区分) VALUES ('S0331','春のさわやかコート',6800,'1');
INSERT INTO 商品 (商品コード,商品名,単価,商品区分) VALUES ('A0582','秋のシックなコート',9800,'1')
6.商品テーブルから、商品コードが「W1252」のデータを抽出する
SELECT * FROM 商品 WHERE 商品コード = 'W1252'
7.商品コードが「S0023」の商品について、商品テーブルの単価を500円に変更する
UPDATE 商品 SET 単価 = 500 WHERE 商品コード = 'S0023'
8.商品テーブルから、単価が千円以下の商品データを抽出する
SELECT * FROM 商品 WHERE 単価 <= 1000
9.商品テーブルから、単価が5万円以上の商品データを抽出する
SELECT * FROM 商品 WHERE 単価 >= 50000
10.注文テーブルから、2022年以降の注文データを抽出する
SELECT * FROM 注文 WHERE 注文日 > '2021-12-31'
11.注文テーブルから、2021年11月以前の注文データを抽出する
SELECT * FROM 注文 WHERE 注文日 < 2021-12-01
12.商品テーブルから、「衣類」でない商品データを抽出する
SELECT * FROM 商品 WHERE 商品区分 <> '1'
13.注文テーブルから、クーポン割引を利用していない注文データを抽出する
SELECT ＊FROM 注文 WHERE クーポン割引料 IS NULL
14.商品テーブルから、商品コードが「N」で始まる商品を削除する
DELETE 商品 WHERE SUBSTRING(商品コード,1,1) = 'N'
DELETE FROM 商品 WHERE 商品コード LIKE 'N%'
15.商品テーブルから、商品名に「コート」が含まれてる商品について、商品コード、商品名、単価を抽出する
SELECT 商品コード,商品名,単価 FROM 商品 WHERE 商品名 LIKE '%コート%'
16.「靴」または「雑貨」もしくは「未分類」の商品について、商品コード、商品区分を抽出する。ただし、記述する条件式は1つであること
SELECT 商品コード,商品区分 FROM 商品 WHERE 商品区分 IN ('2','3','9')
・・・
17.商品テーブルから、商品コードが「A0100」～「A0500」に当てはまる商品データを抽出する。記述する条件式は1つであること
SELECT * FROM 商品 WHERE 商品コード >= 'A0100' AND 商品コード <= 'A0500'
SELECT * FROM 商品 WHERE 商品コード BETWEEN 'A0100' AND 'A0500'
18.注文テーブルから、商品コードが「N0501」「N1021」「N0223」のいずれかを注文した注文データを抽出する
SELECT * FROM 注文 WHERE 商品コード IN ('N0501','N1021','N0223')
19.商品テーブルから、「雑貨」で商品名に「水玉」が含まれる商品データを抽出する
SELECT * FROM 商品 WHERE 商品区分 = '3' AND 商品名 LIKE '% 水玉 %'
20.商品テーブルから、商品名に「軽い」または「ゆるふわ」のどちらかが含まれる商品データを抽出する
SELECT * FROM 商品 WHERE 商品名 LIKE '% 軽い %' OR 商品名 LIKE '% ゆるふわ %'
21.商品テーブルから、「衣類」で単価が3千円以下、または「雑貨」で単価が1万円以上の商品データを抽出する
SELECT * FROM 商品 WHERE (商品区分 = '1' AND 単価 <= 3000) OR (商品区分 = '3' AND 単価 >= 10000)
22.注文テーブルから、2022年3月中に、一度の注文で数量3個以上の注文があった商品コードを抽出する
SELECT 商品コード FROM 注文 WHERE (日付 BETWEEN '2022-03-01' AND '2022-03-31') AND 数量 >= 3
23.注文テーブルから、一度の注文で数量10個以上を注文したか、クーポン割引を利用した注文データを抽出する
SELECT * FROM 注文 WHERE 数量 >= 10 OR クーポン割引 IS NOT NULL
24.商品テーブルと注文テーブルそれぞれについて、主キーの役割を果たしている列名を日本語で解答する
・・・
25.商品区分「衣類」の商品について、商品コードの降順に商品コードと商品名の一覧を取得する
SELECT 商品コード, 商品名 FROM 商品 WHERE 商品区分 = '1' ORDER BY 商品コード DESC
26.注文テーブルから、主キーの昇順に2022年3月以降の注文一覧を取得する。取得する項目は、注文日、注文番号、注文枝番、商品コード、数量とする
SELECT 注文日,注文番号,注文枝番,商品コード,数量 FROM 注文 WHERE 注文日 >= 2022-03-01 ORDER BY ORDER BY 注文日, 注文番号, 注文枝番
27.注文テーブルから、これまでに注文のあった商品コードを抽出する。重複は除外し、商品コードの昇順に抽出すること
SELECT DISTINCT 商品コード FROM 注文 ORDER BY 商品コード
28.注文テーブルから、注文のあった日付を新しい順に10行抽出する（同一日付が複数回登場してもよい）
SELECT 注文日 FROM 注文 ORDER BY 注文日 DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
29.商品テーブルから、単価の低い順に並べて6～20行目に当たる商品データを抽出する。同一の単価の場合は、商品区分、商品コードの昇順に並ぶように抽出すること
SELECT * FROM 商品 ORDER BY 単価, 商品区分, 商品コード OFFSET 5 ROWS FETCH NEXT 15 ROWS ONLY
30.廃番商品テーブルから、2020年12月に廃番されたものと、売上個数が100を超えるものを併せて抽出する。一覧は、売上個数の多い順に並べること
SELECT * FROM 廃番商品 WHERE (廃番日 BETWEEN '2020-12-01' AND '2020-12-31') OR 売上個数 > 100 ORDER BY 売上個数
SELECT * FROM 廃番商品 WHERE 廃番日 >= '2020-12-01' AND 廃番日 < '2021-01-01' UNION SELECT * FROM 廃番商品 WHERE 売上個数 > 100 ORDER BY 6 DESC
31.商品テーブルから、これまでに注文されたことのない商品コードを昇順に抽出する
SELECT DISTINCT 商品コード FROM 商品 EXCEPT SELECT DISTINCT 商品コード FROM 注文 ORDER BY 1
32.商品テーブルから、これまでに注文された実績のある商品コードを降順に抽出する
SELECT 商品コード FROM 商品 INTERSECT SELECT DISTINCT 商品コード FROM 注文 ORDER BY 1 DESC
33.商品区分が「未分類」で、単価が千円以下と1万円を超える商品について、商品コード、商品名、単価を抽出する。単価の低い順に並べ、同額の場合は商品コードの昇順とする。
SELECT 商品コード,商品名,単価 FROM 商品 WHERE 商品区分 = '9' 単価 <= 1000 UNION SELECT 商品コード,商品名,単価 FROM 商品 WHERE 商品区分 = '9' 単価 > 10000 ORDER BY 3,1
34.商品テーブルの商品区分「未分類」の商品について、商品コード、単価、キャンペーン価格の一覧を取得する。キャンペーン価格は単価の5%引きであり、1円未満の端数は考慮しなくてよい。一覧は商品コード順に並べること
SELECT 商品コード,単価,単価 * 0.95 AS キャンペーン価格 FROM 商品 WHERE 商品区分 = '9' ORDER BY 商品コード
35.注文日が2022年3月12～14日で、同じ商品を2個以上注文し、すでにクーポン割引を利用している注文について、さらに300円を割り引きすることになった。該当データのクーポン割引料を更新する
UPDATE 注文 SET クーポン割引料 = クーポン割引料 + 300 WHERE 注文日 >= '2022-03-12' AND 注文日 <= '2022-03-14' AND 数量 >= 2 AND クーポン割引料 IS NOT NULL
36.注文番号「202202250126」について、商品コード「W0156」の注文数を1つ減らすよう更新する
UPDATE 注文 SET 数量 = 数量 - 1 WHERE 注文番号 = '202202250126' AND 商品コード = 'W0156'
37.注文テーブルから、注文番号「202110010001」～「202110319999」の注文データを抽出する。注文番号と枝番は、「-」（ハイフン）でつなげて1つの項目として抽出する
SELECT 注文番号 || '-' || CAST(注文枝番 AS VARCHAR) FROM 注文 WHERE 注文番号 >= '202110010001' AND 注文番号 <= '202110319999'
38.商品テーブルから、商品区分の一覧を取得する。見出しは「区分」と「区分名」とし、区分名には日本語名を表記する
SELECT DISTINCT 商品区分 AS 区分, CASE 商品区分 WHEN '1' THEN '衣類' WHEN '2' THEN '靴' WHEN '3' THEN '雑貨' WHEN '9' THEN '未分類' END AS 区分名 FROM 商品
39.商品テーブルから、商品コード、商品名、単価、販売価格ランク、商品区分を抽出する。販売価格ランクは、3千円未満を「S」、3千円以上1万円未満を「M」、1万円以上を「L」とする。また、商品区分はコードと日本語名称を「：」（コロン）で連結して表記する。一覧は、単価の昇順に並べ、同額の場合は商品コードの昇順に並べること
SELECT 商品コード, 商品名, 単価, CASE WHEN 単価 < 3000 THEN 'S' WHEN 単価 >= 3000 AND 単価 < 10000 THEN 'M' ELSE 'L' END AS 販売価格ランク, 商品区分 || '：' || CASE 商品区分 WHEN '1' THEN '衣類' WHEN '2' THEN '靴' WHEN '3' THEN '雑貨' WHEN '9' THEN '未分類' END AS 商品区分 FROM 商品 ORDER BY 単価, 商品コード
40.商品テーブルから、商品名が10文字を超過する商品名とその文字数を抽出する。文字数の昇順に並べること
SELECT 商品名, LENGTH(商品名) AS 文字数 FROM 商品 WHERE LENGTH(商品名) > 10 ORDER BY 文字数
41.注文テーブルから、注文日と注文番号の一覧を抽出する。注文番号は日付の部分を取り除き、4桁の連番部分だけを表記すること
SELECT 注文日, SUBSTRING(注文番号, 9, 4) AS 注文番号 FROM 注文
42.商品テーブルについて、商品コードの1文字目が「M」の商品の商品コードを「E」で始まるよう更新する
UPDATE 商品 SET 商品コード = 'E' || SUBSTRING(商品コード, 2, 4) WHERE SUBSTRING(商品コード, 1, 1) = 'M'
43.注文番号の連番部分が「1000」～「2000」の注文番号を抽出する。連番部分4桁を昇順で抽出すること
SELECT SUBSTRING(注文番号,9,4) FROM 注文 WHERE SUBSTRING(注文番号,9,4) >= '1000' AND SUBSTRING(注文番号,9,4) <= '2000' ORDER BY SUBSTRING(注文番号,9,4)
44. 商品コード「S1990」の廃番日を、関数を使って本日の日付に修正する
UPDATE 廃番商品 SET 廃番日 = CURRENT_DATE WHERE 商品コード = 'S1990'
45.1万円以上の商品の一覧を取得する。ただし、30%値下げしたときの単価を、商品コード、商品名、現在の単価と併せて取得する。値下げ後の単価の見出しは、「値下げした単価」とし、1円未満は切り捨てること
SELECT 商品コード,商品名,単価,TRUNC(単価*0.7,0) AS 値下げした単価 FROM 商品 WHERE 単価 >= 10000
46.これまでに注文された数量の合計を求める
SELECT SUM(数量) FROM 注文
47.注文日順に、注文日ごとの数量の合計を求める
SELECT 注文日,SUM(数量) AS 数量合計 FROM 注文 GROUP BY 注文日 ORDER BY 注文日
48.商品区分順に、商品区分ごとの単価の最少額と最高額を求める
SELECT 商品区分,MIN(単価) AS 最少額,MAX(単価) AS 最高額 FROM 商品 GROUP BY 商品区分 ORDER BY 商品区分
49.商品コードごとに、これまで注文された数量の合計を商品コード順に求める
SELECT 商品コード,SUM(数量) AS 数量の合計 FROM 注文 GROUP BY 商品コード ORDER BY 商品コード
50.これまでに最もよく売れた商品を10位まで抽出する。商品コードと販売した数量を数量の多い順に並べ、数量が同じ商品については、商品コードの昇順にすること
SELECT 商品コード, SUM(数量) AS 数量 FROM 注文 GROUP BY 商品コード ORDER BY 数量 DESC,商品コード OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
51.これまでに売れた数量が5個未満の商品コードとその数量を抽出する
SELECT 商品コード,SUM(数量) AS 数量 FROM 注文 GROUP BY 商品コード HAVING SUM(数量) < 5
52.これまでにクーポン割引をした注文件数と、割引額の合計を求める。ただし、WHERE句による絞り込み条件は指定しないこと
SELECT COUNT(クーポン割引料) AS 注文件数, SUM(クーポン割引料) AS 合計 FROM 注文
53.月ごとの注文件数を求める。抽出する列の名前は「年月」と「注文件数」とし、年月列の内容は「202201」のような形式で、日付の新しい順で抽出すること。なお、1件の注文には、必ず注文枝番「1」の注文明細が含まれていることが保証されている
SELECT SUBSTRING(注文番号,1,6) AS 年月, COUNT(注文番号) AS 注文件数 FROM 注文 WHERE 注文枝番 = 1 GROUP BY SUBSTRING(注文番号,1,6) ORDER BY SUBSTRING(注文番号,1,6) DESC
54.注文テーブルから、「Z」から始まる商品コードのうち、これまでに売れた数量が100個以上の商品コードを抽出する
SELECT 商品コード FROM 注文 WHERE 商品コード LIKE 'Z %' GROUP BY 商品コード HAVING SUM(数量) >= 100
55.商品コード「S0604」の商品について、商品コード、商品名、単価、これまでに販売した数量を抽出する。ただし、抽出には、選択列リストにて注文テーブルを副問い合わせするSELECT文を用いること
SELECT 商品コード,商品名,単価, (SELECT SUM(数量) FROM 注文 WHERE 商品コード = 'S0604') AS 販売数量 FROM 商品 WHERE 商品コード = 'S0604'
56.次の注文について、商品コードを間違って登録したことがわかった。商品テーブルより条件に合致する商品コードを取得し、該当の注文テーブルを更新する。ただし、注文テーブルの更新には、SET句にて商品テーブルを副問い合わせするUPDATE文を用いること。
・注文日：2022-03-15 注文番号：202203150014 注文枝番：1
・正しい商品の条件：商品区分が「靴」で、商品名に「ブーツ」「雨」「安心」を含む
UPDATE 注文 SET 商品コード = (SELECT 商品コード FROM 商品 WHERE 商品区分 = '2' AND 商品名 LIKE '%ブーツ%' AND 商品名 LIKE '%雨%' AND 商品名 LIKE '%安心%') WHERE 注文日 = '2022-03-15' AND 注文番号 = '202203150014' AND 注文枝番 = 1
57.商品名に「あったか」が含まれる商品が売れた日付とその商品コードを過去の日付順に抽出する。ただし、WHERE句でIN演算子を利用した副問い合わせを用いること
SELECT 注文日,商品コード FROM 注文 WHERE 商品コード IN (SELECT 商品コード FROM 商品 WHERE 商品名 LIKE ’％あったか％’) ORDER BY 注文日
58.商品ごとにそれぞれ平均販売数量を求め、どの商品の平均販売数量よりも多い数が売れた商品を探し、その商品コードと販売数量を抽出する。ただし、ALL演算子を利用した副問い合わせを用いること
SELECT 商品コード,SUM(数量) AS 販売数量 FROM 注文 GROUP BY 商品コード HAVING SUM(数量) > ALL (SELECT AVG(数量) FROM 注文 GROUP BY 商品コード)
59.クーポン割引を利用して販売した商品コード「W0746」の商品について、その販売数量と、商品1個あたりの平均割引額を抽出する。列名は「割引による販売数」と「平均割引額」とし、1円未満は切り捨てる。抽出にはFROM句で副問い合わせを利用すること
SELECT A.販売数 AS 割引による販売数, TRUNC(A.割引額 / A.販売数, 0) AS 平均割引額 FROM (SELECT SUM(数量) AS 販売数, SUM(クーポン割引料) AS 割引額 FROM 注文 WHERE 商品コード = 'W0746' AND クーポン割引 IS NOT NULL) AS A
60.次の注文について、内容を追加したいという依頼があった。追加分の注文を注文テーブルに登録する。使用する注文枝番は、該当の注文番号を副問い合わせにて参照し、1を加算した番号を採番する。なお、登録のSQL文は注文ごとに1つずつ作成すること
・注文日・2022-03-21、注文番号：20220320080、商品コード：S1003、数量：1、クーポン割引：なし
・注文日：2022-03-22、注文番号：202203220901、商品コード：A0052、数量：2、クーポン割引：500円
INSERT INTO 注文 SELECT 注文日,注文番号,MAX(注文枝番) + 1,'S1003',1,NULL FROM 注文 WHERE 注文日 = '2022-03-21' AND 注文番号 = '20220320080' GROUP BY 注文日,注文番号
INSERT INTO 注文 SELECT 注文日,注文番号,MAX(注文枝番) + 1,'A0052',2,500 FROM 注文 WHERE 注文日 = '2022-03-22' AND 注文番号 = '202203220901' GROUP BY 注文日,注文番号
・・・
61.注文番号「202201130115」について、注文番号、注文枝番、商品コード、商品名、数量の一覧を注文番号および注文枝番の順に抽出する。商品名は商品テーブルより取得すること
SELECT 注文番号,注文枝番,注文.商品コード,商品名,数量 FROM 注文 LEFT JOIN 商品 ON 注文.商品コード = 商品.商品コード WHERE 注文番号 = '202201130115' ORDER BY 注文番号,注文枝番
62.廃番となった商品コード「A0009」について、廃番日より後に注文された注文情報（注文日、注文番号、注文枝番、数量、注文金額）を抽出する。注文金額は単価と数量より算出すること
SELECT T.注文日,T.注文番号,T.注文枝番,T.数量,T.数量 * H.単価 AS 注文金額 FROM 注文 AS T JOIN 廃番商品 AS H ON T.商品コード = H.商品コード WHERE T.商品コード = 'A0009' AND T.注文日 > H.廃番日
63.商品コード「S0604」について、商品情報（商品コード、商品名、単価）とこれまでの注文情報（注文日、注文番号、数量）、さらに単価と数量から売上金額を求め、一覧として抽出する。一覧は、注文のあった順に表示すること
SELECT S.商品コード, S.商品名, S.単価, T.注文日, T.注文番号, T.数量, S.単価 * T.数量 AS 売上金額 FROM 商品 AS S JOIN 注文 AS T ON S.商品コード = T.商品コード WHERE S.商品コード = 'S0604' ORDER BY T.注文番号
64.2020年8月に注文のあった商品コードの一覧を抽出する。一覧には、商品名も表示する必要がある。すでに廃番となっている商品に関しては特に考慮しなくてよい（一覧に含まれなくてよい）
SELECT DISTINCT T.商品コード, S.商品名 FROM 注文 AS T JOIN 商品 AS S ON T.商品コード = S.商品コード WHERE SUBSTRING(T.注文番号, 1, 6) = '202008'
65.問題64では、すでに廃番となっている商品は抽出されなかった。廃番となった商品ももれなく一覧に記載されるよう、SQL文を変更する。なお、廃番商品の商品名には「廃番」と表示すること
SELECT DISTINCT T.商品コード, COALESCE(S.商品名, '廃番') AS 商品名 FROM 注文 AS T LEFT JOIN 商品 AS S ON T.商品コード = S.商品コード WHERE SUBSTRING(T.注文番号, 1, 6) = '202008'
66.商品区分「雑貨」の商品について、注文日、商品コード、商品名、数量を抽出する。商品については、「商品コード：商品名」の形式で表示する。ただし、注文のなかった「雑貨」商品についてももれなく一覧に記載し、数量は0とすること。
SELECT T.注文日,S.商品コード || '：' || S.商品名 AS 商品名,COALESCE(T.数量, 0) AS 数量 FROM 注文 AS T RIGHT JOIN 商品 AS S ON T.商品コード = S.商品コード WHERE S.商品区分 = '3'
67.問題66について、注文のあった「雑貨」商品がすでに廃番になっている可能性も考慮し、一覧を抽出する。廃番になった商品は、「商品コード：（廃番済み）」のように表示する。
SELECT T.注文日,S.商品コード || '：' || COALESCE(S.商品名, '（廃番済み）') AS 商品名,COALESCE(T.数量, 0) AS 数量 FROM 注文 AS T FULL JOIN 商品 AS S ON T.商品コード = S.商品コード WHERE S.商品区分 = '3'
SELECT T.注文日,S.商品コード || '：' || S.商品名 AS 商品名,COALESCE(T.数量, 0) AS 数量 FROM 注文 AS T RIGHT JOIN (SELECT 商品コード,商品名,商品区分 FROM 商品 UNION SELECT 商品コード,'（廃番済み）' AS 商品名,商品区分 FROM 廃番商品) AS S ON T.商品コード = S.商品コード WHERE S.商品区分 = '3'
68.注文番号「202104030010」について、注文日、注文番号、注文枝番、商品コード、商品名、単価、数量、注文金額を抽出する。注文金額は単価と数量より算出し、その総額からクーポン割引料を差し引いたものとする。また、商品が廃番になっている場合は、廃番商品テーブルから必要な情報を取得すること
SELECT T.注文日,T.注文番号,T.注文枝番,T.商品コード,S.商品名,S.単価,T.数量, T.数量 * S.単価 - T.クーポン割引料 AS 注文金額 FROM 注文 AS T LEFT JOIN (SELECT 商品名,単価,商品コード FROM 商品 UNION SELECT 商品名,単価,商品コード FROM 廃番商品) AS S ON T.商品コード = S.商品コード WHERE T.注文番号 = '202104030010'
SELECT T.注文日,T.注文番号,T.注文枝番,T.商品コード,COALESCE(S.商品名,H.商品名) AS 商品名,COALESCE(S.単価,H.単価) AS 単価,T.数量,T.数量 * COALESCE(S.単価,H.単価) - COALESCE(T.クーポン割引,0) AS 注文金額 FROM 注文 AS T LEFT JOIN 商品 AS S ON T.商品コード = S.商品コード LEFT JOIN 廃番商品 AS H ON T.商品コード = H.商品コード WHERE 注文番号 = '202104030010'
69.商品コードが「B」で始まる商品について、商品テーブルから商品コード、、商品名、単価を、注文テーブルからこれまでに売り上げた個数をそれぞれ抽出する。併せて、単価と個数からこれまでの総売上金額を計算する（クーポン割引は考慮しなくてよい）。一覧は、商品コード順に表示すること
SELECT S.商品コード,S.商品名,S.単価,COALESCE(T.合計数量,0) AS 売上数量, S.単価 * COALESCE(T.合計数量, 0) AS 総売上金額 FROM 商品 AS S LEFT JOIN (SELECT 商品コード,SUM(数量) AS 合計数量 FROM 注文 WHERE 商品コード LIKE 'B%' GROUP BY 商品コード) AS T ON S.商品コード = T.商品コード WHERE 商品コード LIKE 'B%' ORDER BY S.商品コード
70.現在販売中の商品について、関連している商品のある一覧を抽出する。一覧には、商品コード、商品名、関連商品コード、関連商品名を記載する
SELECT S1.商品コード,S1.商品名,S1.関連商品コード,S2.商品名 AS 関連商品名 FROM 商品 AS S1 JOIN 商品 AS S2 ON S1.関連商品コード = S2.商品コード
・・・
1.主人公のパーティーにいるキャラクターの全データをパーティーテーブルから「*」を用いずに抽出する
SELECT ID,名称,職業コード,HP,MP,状態コード FROM パーティー
2.パーティーテーブルから、名称、HP、MPの一覧を取得する。各見出しは次のように表示すること
・なまえ ・現在のHP ・現在のMP
SELECT 名称 AS なまえ, HP AS 現在のHP, MP AS 現在のMP FROM パーティー
3.イベントの全データをイベントテーブルから「*」を用いて抽出する
SELECT * FROM イベント
4.イベントテーブルから、イベント番号とイベント名称の一覧を取得する。各見出しは次のように表示すること
・番号 ・場面
SELECT イベント番号 AS 番号, イベント名称 AS 場面 FROM イベント
5.パーティーテーブルに、次の3つのデータを1回の実行ごとに1つずつ追加する
INSERT INTO パーティー VALUES ('A01','スガワラ','21',131,232,'03')
6.パーティーテーブルから、IDが「C02」のデータを抽出する
SELECT * FROM パーティー WHERE ID = 'C02'
7.パーティーテーブルのID「A01」のデータについて、HPを120に更新する
UPDATE パーティー SET HP = 120 WHERE ID = 'A01'
8.パーティーテーブルから、HPが100未満のデータについて、ID、名称、HPの一覧を抽出する
SELECT ID,名称,HP FROM パーティー WHERE HP < 100
9.パーティーテーブルから、MPが100以上のデータについて、ID、名称、MPの一覧を抽出する
SELECT ID,名称,MP FROM パーティー WHERE MP >= 100
10.イベントテーブルから、タイプが「特殊」でないデータについて、イベント番号、イベント名称、タイプの一覧を抽出する
SELECT イベント番号,イベント名称,タイプ FROM イベント WHERE タイプ <> '3'
11.イベントテーブルから、イベント番号が5以下のデータについて、イベント番号とイベント名称を抽出する
SELECT イベント番号,イベント名称 FROM イベント WHERE イベント番号 <= 5
・・・
12.イベントテーブルから、イベント番号が20を超過しているデータについて、イベント番号とイベント名称を抽出する
SELECT イベント番号,イベント名称 FROM イベント WHERE イベント番号 > 20
13.イベントテーブルから、別のイベントのクリアを前提としないイベントについて、イベント番号とイベント名称を抽出する
SELECT イベント番号,イベント名称 FROM イベント WHERE 前提イベント番号 IS NULL
14.イベントテーブルから、次に発生するイベントが決められているイベントについて、イベント番号、イベント名称、後続イベント番号を抽出する
SELECT イベント番号,イベント名称,後続イベント番号 FROM イベント WHERE 後続イベント番号 IS NOT NULL
15.名称に「ミ」が含まれるパーティーテーブルのデータについて、状態コードを「眠り」に更新する
UPDATE パーティー SET 状態コード = '01' WHERE 名称 LIKE '%ミ%'
16.HPが120～160の範囲にあるパーティーテーブルのデータについて、ID、名称、HPの一覧を抽出する。ただし、記述する条件式は1つであること
SELECT ID,名称,HP FROM パーティー WHERE HP >= 120 AND HP <= 160
SELECT ID,名称,HP FROM パーティー WHERE HP BETWEEN 120 AND 160
17.職業が「勇者」、「戦士」、「武道家」のいずれかであるパーティーテーブルのデータについて、名称と職業コードを抽出する。ただし、記述する条件式は1つであること
SELECT 名称,職業コード FROM パーティー WHERE 職業コード IN ('01','10','11')
・・・
18.状態コードが「異常なし」と「気絶」のどちらでもないパーティーテーブルのデータについて、名称と状態コードを抽出する。ただし、記述する条件式は1つであること
SELECT 名称,状態コード FROM パーティー WHERE 状態コード NOT IN ('00','09')
19.パーティーテーブルから、HPとMPがともに100を超えているデータを抽出する
SELECT * FROM パーティー WHERE HP > 100 AND MP > 100
20.パーティーテーブルから、IDが「A」で始まり、職業コードの1文字目が「2」であるデータを抽出する
SELECT ＊FROM パーティー WHERE ID LIKE 'A%' AND 職業コード LIKE '2%'
21.イベントテーブルから、タイプが「強制」で、事前にクリアが必要なイベントかつ次に発生するイベントが設定されているデータを抽出する
SELECT * FROM イベント WHERE タイプ = '1' AND 前提イベント番号 IS NOT NULL AND 後続イベント番号 IS NOT NULL
22.パーティーテーブルとイベントテーブルそれぞれについて、主キーの役割を果たしている列名を日本語で解答する
パーティー：ID
イベント：イベント番号
・・・
23.パーティーテーブルから、パーティーの現在の状態コード一覧を取得する。重複は除外すること
SELECT DISTINCT 状態コード FROM パーティー
24.パーティーテーブルから、IDと名称をIDの昇順に抽出する
SELECT ID,名称 FROM パーティー ORDER BY ID
25.パーティーテーブルから、名称と職業コードを名称の降順に抽出する
SELECT 名称,職業コード FROM パーティー ORDER BY 名称 DESC
・・・
26.パーティーテーブルから、名称、HP、状態コードを、状態コードの昇順かつHPの高い順（降順）に抽出する
SELECT 名称,HP,状態コード FROM パーティー ORDER BY 状態コード,HP DESC
27.イベントテーブルから、タイプ、イベント番号、イベント名称、前提イベント番号、後続イベント番号を、タイプの昇順かつイベント番号の昇順に抽出する。並び替えには列番号を用いること
SELECT タイプ,イベント番号,イベント名称,前提イベント番号,後続イベント番号 FROM イベント ORDER BY 1,2
28.パーティーテーブルから、HPの高い順に3件抽出する
SELECT ＊FROM パーティー ORDER BY HP DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
29.パーティーテーブルから、MPが3番目に高いデータを抽出する
SELECT * FROM パーティー ORDER BY MP DESC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY
30.イベントテーブルと経験イベントテーブルから、まだ参加していないイベントの番号を抽出する。イベント番号順に表示すること
SELECT イベント番号 FROM イベント EXCEPT SELECT イベント番号 FROM 経験イベント ORDER BY 1
31.イベントテーブルと経験イベントテーブルから、すでにクリアされたイベントのうち、タイプがフリーのイベントの番号を抽出する。集合演算子を用いること
SELECT イベント番号 FROM イベント WHERE タイプ = '2' INTERSECT SELECT イベント番号 FROM 経験イベント WHERE クリア区分 = '1'
・・・
32.パーティーテーブルから、次の形式の一覧を取得する
・職業区分 ・職業コード ・ID ・名称
職業区分は、物理攻撃の得意なもの（職業コードが1から始まる）を「S」、魔法攻撃の得意なもの（職業コードが2から始まる）を「M」、それ以外を「A」と表示すること。また、一覧は職業コード順とすること
SELECT CASE WHEN 職業コード LIKE '1%' THEN 'S' WHEN 職業コード LIKE '2%' THEN 'M' ELSE 'A' END AS 職業区分,職業コード,ID,名称 FROM パーティー ORDER BY 職業コード
33.アイテム「勇気の鈴」を装備すると、HPが50ポイントアップする。このアイテムを装備したときの各キャラクターのHPを適切な列を用いて次の別名で取得する。ただし、このアイテムは「武道家」と「学者」しか装備できない
・なまえ ・現在のHP ・装備後のHP
SELECT 名称 AS なまえ,HP AS 現在のHP,CASE WHEN 職業コード IN ('11','21') THEN HP + 50 ELSE HP END AS 装備後のHP FROM パーティー
・・・
34.ID「A01」と「A03」のキャラクターがアイテム「知恵の指輪」を装備し、MPが20ポイントアップした。その該当データのMPを更新する
UPDATE パーティー SET MP = MP + 20 WHERE ID IN ('A01','A03')
35.武道家の技「スッキリパンチ」は、自分のHPを2倍したポイントのダメージを敵に与える。この技を使ったときのダメージを適切な列を用いて次の別名で抽出する
・なまえ ・現在のHP ・予想されるダメージ
SELECT 名称 AS なまえ, HP AS 現在のHP, HP * 2 AS 予想されるダメージ FROM パーティー WHERE 職業コード = '11'
36.現在、主人公のパーティーにいるキャラクターの状況について、適切な列を用いて次の別名で一覧を取得する
・なまえ ・HPとMP ・ステータス
「HPとMP」はHPとMPを「／」でつなげたものとする。ステータスには状態コードを日本語で置き換えたものを表示するが、ステータスに異常がない場合は、何も表示しなくてよい
SELECT 名称 AS なまえ, HP || '／' || MP AS HPとMP,CASE 状態コード WHEN '00' THEN NULL WHEN '01' THEN '眠り' WHEN '02' THEN '毒' WHEN '03' THEN '沈黙' WHEN '04' THEN '混乱' WHEN '09' THEN '気絶' END AS ステータス FROM パーティー
37.イベントテーブルから、次の形式でイベント一覧を取得する
・イベント番号 ・イベント名称 ・タイプ ・発生時期
タイプはコードを日本語で置き換えたもの、発生時期は次の条件に応じたものを表示すること
・イベント番号が1～10なら「序盤」
・イベント番号が11～17なら「中盤」
・上記以外なら「終盤」
SELECT イベント番号,イベント名称,CASE タイプ WHEN '1' THEN '強制' WHEN '2' THEN 'フリー' WHEN '3' THEN '特殊' END AS タイプ,CASE WHEN イベント番号 BETWEEN 1 AND 10 THEN '序盤' WHEN イベント番号 BETWEEN 11 AND 17 THEN '中盤' ELSE '終盤' AS 発生時期 FROM イベント
38.敵の攻撃「ネームバリュー」は、名前の文字数を10倍したポイントのダメージがある。この攻撃を受けたときの各キャラクターの予想ダメージを適切な列を用いて次の別名で取得する。
・なまえ ・現在のHP ・予想ダメージ
SELECT 名称 AS なまえ,HP AS 現在のHP,LENGTH(名称) * 10 AS 予想ダメージ FROM パーティー
39.敵の攻撃「四苦八苦」を受け、HPまたはMPが4で割り切れるキャラクターは混乱した。該当データの状態コードを更新する。なお、剰余の計算には%演算子かMOD関数を用いる
UPDATE パーティー SET 状態コード = '04' WHERE HP % 4 = 0 OR MP % 4 = 0
40.町の道具屋で売値が777のアイテム「女神の祝福」を買ったところ、会員証を持っていたため30%割引で購入できた。この際に支払った金額を求める。端数は切り捨て。
SELECT TRUNK(777 * 0.7,0) AS 支払った金額
41.戦闘中にアイテム「女神の祝福」を使ったところ、全員のHPとMPがそれまでの値に対して3割ほど回復した。該当するデータを更新する。ただし、端数は四捨五入すること
UPDATE パーティー SET HP = ROUND(HP * 1.3, 0), MP = ROUND(MP * 1.3, 0)
42.戦士の技「Step by Step」は、攻撃の回数に応じて自分のHPをべき乗したポイントのダメージを与える。3回攻撃したときの、各回の攻撃ポイントを適切な例を用いて次の別名で取得する。ただし、1回目は0乗から始まる。
・なまえ ・HP ・攻撃1回目 ・攻撃2回目 ・攻撃3回目
SELECT 名称 AS なまえ, HP AS HP, POWER(HP,0) AS 攻撃1回目, POWER(HP,1) AS 攻撃2回目, POWER(HP,2) AS 攻撃3回目 FROM パーティー WHERE 職業コード = '10'
43.現在、主人公のパーティーにいるキャラクターの状況について、HPと状態コードから、リスクを厚み付けした一覧を適切な列を用いて次の別名で取得する。
・なまえ ・HP ・状態コード ・リスク値
リスク値には、次の条件に従った値を算出する
・HPが50以下ならリスク値3
・HPが51以上100以下ならリスク値2
・HPが101以上150以上ならリスク値1
・HPがそれ以外ならリスク値0
・状態コードの値をリスク値に加算
リスクの高い順かつHPの低い順にキャラクターを表示する
SELECT 名称 AS なまえ,HP,状態コード,CASE WHEN HP <= 50 TEHN 3 + CAST(状態コード AS INTEGER) WHEN HP BETWEEN 51 AND 100 THEN 2 + CAST(状態コード AS INTEGER) WHEN HP BETWEEN 101 AND 150 THEN 1 + CAST(状態コード AS INTEGER) ELSE 0 + CAST(状態コード AS INTEGER) END AS リスク値 FROM パーティー ORDER BY リスク値 DESC,HP
44.イベントテーブルより、イベントの一覧をイベント番号順に次の形式で取得する
・前提イベント番号 ・イベント番号 ・後続イベント番号
前提または後続イベントがない場合は、それぞれ「前提なし」「後続なし」と表示すること
SELECT COALESCE(前提イベント番号, '前提なし') AS 前提イベント番号,イベント番号,COALESCE(後続イベント番号, '後続なし') AS 後続イベント番号 FROM イベント ORDER BY イベント番号
・・・
45.主人公のパーティーにいるキャラクターのHPとMPについて、最大値、最小値、平均値をそれぞれ求める
SELECT MAX(HP) AS HP最大値, MIN(HP) AS HP最小値, AVG(HP) AS HP平均値, MAX(MP) AS MP最大値, MIN(MP) AS MP最小値, AVG(MP) AS MP平均値 FROM パーティー
46.イベントテーブルから、タイプ別にイベントの数を取得する。ただし、タイプは日本語で表示すること
SELECT CASE タイプ WHEN '1' THEN '強制' WHEN '2' THEN 'フリー' WHEN '3' THEN '特殊' END AS タイプ, COUNT(イベント番号) AS イベント数 FROM イベント GROUP BY タイプ
47.経験イベントテーブルから、クリアの結果別にクリアしたイベントの数を取得する。クリア結果順に表示すること
SELECT クリア結果, COUNT(イベント番号) AS クリア回数 FROM 経験イベント WHERE クリア区分 = '1' GROUP BY クリア結果 ORDER BY クリア結果
48.攻撃魔法「小さな奇跡」は、パーティー全員のMPによって敵の行動が異なる。次の条件に従って、現在のパーティーがこの魔法を使ったときの敵の行動を表示する
・パーティー全員のMPが500未満なら
「敵は見とれている！」
・パーティー全員のMPが500以上1000未満なら
「敵は呆然としている！」
・パーティー全員のMPが1000以上なら
「敵はひれ伏している！」
SELECT CASE WHEN SUM(MP) < 500 THEN '「敵は見とれている！」' WHEN SUM(MP) >= 500 AND SUM(MP) < 1000 THEN '「敵は呆然としている！」' WHEN SUM(MP) >= 1000 THEN '「敵はひれ伏している！」' END AS 敵の行動 FROM パーティー
49.経験イベントテーブルから、クリアしたイベント数と参加したもののまだクリアしていないイベントの数を次の形式で表示する
SELECT CASE クリア区分 WHEN '0' THEN '参加したがクリアしていない' WHEN '1' THEN 'クリアした' END AS 区分, COUNT(イベント番号) AS イベント数 FROM 経験イベント GROUP BY クリア区分 ORDER BY 区分
50.職業タイプごとのHPとMPの最大値、最小値、平均値を抽出する。ただし、職業タイプは職業コードの1文字目によって分類すること
SELECT SUBSTRING(職業コード,1,1) AS 職業タイプ,MAX(HP) AS HPの最大値,MIN(HP) AS HPの最小値,AVG(HP) AS HPの平均値,MAX(MP) AS MPの最大値,MIN(MP) AS MPの最小値,AVG(MP) AS MPの平均値 FROM パーティー GROUP BY SUBSTRING(職業コード,1,1)
51.IDの1文字目によってパーティーを分類し、HPの平均が100を超えているデータを抽出する。次の項目を抽出すること
・IDによる分類 ・HPの平均 ・MPの平均
SELECT SUBSTRING(ID,1,1) AS IDによる分類, AVG(HP) AS HPの平均, AVG(MP) AS MPの平均 FROM パーティー GROUP BY SUBSTRING(ID,1,1) HAVING AVG(HP) > 100
52.ある洞窟に存在する「力の扉」は、キャラクターのHPによって開けることのできる扉の数が決まっている。次の条件によってその数が決まるとき、現在のパーティーで開けることのできる扉の合計数を求める
・HPが100未満のキャラクター 1枚
・HPが100以上150未満のキャラクター 2枚
・HPが150以上200未満のキャラクター 3枚
・HPが200以上のキャラクター 5枚
SELECT SUM(CASE WHEN HP < 100 THEN 1 WHEN HP >= 100 AND HP < 150 THEN 2 WHEN HP >= 150 AND HP < 200 THEN 3 WHEN HP >= 200 THEN 5 END) AS 合計数 FROM パーティー
・・・
53.勇者の現在のHPが、パーティー全員のHPの何パーセントに当たるかを求めたい。適切な列を用いて次の別名で抽出する。ただし、割合は少数点第2位を四捨五入し、小数点第1位まで求めること
・なまえ ・現在のHP ・パーティーでの割合
SELECT 名称 AS なまえ, HP AS 現在のHP, ROUND(CAST(HP AS NUMERIC) / (SELECT SUM(HP) FROM パーティー) * 100, 1 ) AS パーティーでの割合 FROM パーティー
54.魔法使いは回復魔法「みんなからお裾分け」を使ってMPを回復した。この魔法は、本人を除くパーティー全員のMP合計値の10%をもらうことができる。端数は四捨五入して魔法使いのMPを更新する。なお、魔法使い以外のMPは更新しなくてよいものとする
UPDATE パーティー SET MP = MP + (SELECT ROUND(CAST(SUM(MP) AS NUMERIC) * 0.1,0) FROM パーティー WHERE 職業コード <> '20') WHERE 職業コード = '20'
55.経験イベントテーブルから、これまでにクリアしたイベントのうち、タイプが「強制」または「特殊」であるものについて、次の形式で抽出する
・イベント番号 ・クリア結果
抽出には、副問い合わせを用いること
SELECT イベント番号,クリア結果 FROM 経験イベント WHERE クリア区分 = '1' AND イベント番号 IN (SELECT イベント番号 FROM イベントテーブル WHERE タイプ IN ('1', '3'))
56.パーティーテーブルから、パーティー内で最も高いMPを持つキャラクター名とそのMPを抽出する。抽出には、副問い合わせを用いること
SELECT 名称,MP FROM パーティー WHERE MP = (SELECT MAX(MP) FROM パーティー)
57.これまでに着手していないイベントの数を抽出する。抽出には、副問い合わせを用いること
SELECT COUNT(イベント番号) FROM イベント WHERE イベント番号 NOT IN (SELECT イベント番号 FROM 経験イベント)
58.これまでに着手していないイベントの数を抽出する。抽出には、副問い合わせを用いること
・・・
59.5番目にクリアしたイベントのイベント番号よりも小さい番号を持つすべてのイベントについて、イベント番号とイベント名称を抽出する
SELECT イベント番号, イベント名称 FROM イベント WHERE イベント番号 < (SELECT イベント番号 FROM 経験イベント WHERE クリア区分 = '1' AND ルート番号 = 5)
60.これまでにパーティーがクリアしたイベントを前提としているイベントの一覧を次の形式で抽出する
・イベント番号 ・イベント名称 ・前提イベント番号
SELECT イベント番号,イベント名称,前提イベント番号 FROM イベント WHERE 前提イベント番号 IN (SELECT イベント番号 FROM 経験イベント WHERE クリア区分 = '1')
61.パーティーは、イベント番号「9」のイベントを結果「B」でクリアし、その次に発生するイベントに参加した。これを経験イベントテーブルに登録する。なお、更新と追加の両方を2つのSQL文で記述すること
UPDATE 経験イベント SET クリア区分 = '1', クリア結果 = 'B', ルート番号 = (SELECT MAX(ルート番号) + 1 FROM 経験イベント) WHERE イベント番号 = 9
INSERT INTO 経験イベント (イベント番号,クリア区分,クリア結果,ルート番号) VALUES ((SELECT 後続イベント番号 FROM イベント WHERE イベント番号 = 9),'0',NULL,NULL)
・・・
62.すでにクリアしたイベントについて、次の形式の一覧を抽出する
・ルート番号 ・イベント番号 ・イベント名称 ・クリア結果
一覧は、クリアした順番に表示すること
SELECT ルート番号,I.イベント番号,I.イベント名称,クリア結果 FROM 経験イベント LEFT JOIN イベント AS I ON 経験イベント.イベント番号 = I.イベント番号 WHERE クリア区分 = '1' ORDER BY ルート番号
63.イベントテーブルから、タイプ「強制」のイベントについて、イベント番号とイベント名称、パーティーのクリア区分を抽出する。ただし、これまでに未着手のイベントは考慮しなくてよい
SELECT イベント.イベント番号,イベント名称,クリア区分 FROM イベント LEFT JOIN 経験イベント ON イベント.イベント番号 = 経験イベント.イベント番号 WHERE タイプ = '1'
64.問題63では、着手していないイベントについては抽出されなかった。未着手のイベントについてももれなく抽出できるよう、SQL文を変更する。なお、クリアしていないイベントについては、クリア区分に「未クリア」と表示する
SELECT イベント.イベント番号,イベント名称,COALESCE(クリア区分,'未クリア') AS クリア区分 FROM イベント LEFT JOIN 経験イベント ON イベント.イベント番号 = 経験イベント.イベント番号 WHERE タイプ = '1'
65.次のようなコードテーブルを新しく作成し、職業コードと状態コードを登録した。
このテーブルを使って、現在のパーティーに参加しているキャラクターの一覧を適切な例を用いて次の別名で、ID順に抽出する
・ID ・なまえ ・職業 ・状態
SELECT P.ID, P.名称 AS なまえ, S.コード名称 AS 職業, J.コード名称 AS 状態 FROM パーティー AS P JOIN (SELECT コード値, コード名称 FROM コード WHERE コード種別='1') AS S ON P.職業コード = S.コード値 JOIN (SELECT コード値, コード名称 FROM コード WHERE コード種別 = '2') AS J ON P.状態コード = J.コード値 ORDER BY ID
66.パーティーテーブルから、現在のパーティーに参加しているキャラクターの一覧を次の形式で抽出する。職業はコードテーブルより日本語で表示する。また、現在のパーティーにいない職業についてももれなく一覧に記載し、名称の項目に「（仲間になっていない！）」と表示すること
・ID ・なまえ ・職業
SELECT P.ID,COALESCE(P.名称,'仲間になっていない！') AS なまえ,C.コード名称 AS 職業 FROM パーティー AS P RIGTH JOIN (SELECT コード値,コード名称 FROM コード WHERE コード種別 = '1') AS C ON P.職業コード = C.コード値
67.経験イベントテーブルから、参加済みイベントのクリア結果一覧を次の形式で抽出する。クリア結果は「コード値：コード名称」のように表示し、クリア未済のイベントも記載されるよう考慮する
・イベント番号 ・クリア区分 ・クリア結果
また、まだ記録していないクリア結果のすべてのコード値についても一覧に記載する
SELECT K.イベント番号, K.クリア区分, C.コード値 || '：' || C.コード名称 AS クリア結果 FROM 経験イベント AS K LEFT JOIN (SELECT コード値,コード名称 FROM コード WHERE コード種別 = '4') AS C ON K.クリア結果 = C.コード値
68.イベントテーブルから、前提イベントが設定されているイベントについて、次の形式の一覧を抽出する
・イベント番号 ・イベント名称 ・前提イベント番号 ・前提イベント名称
SELECT E1.イベント番号, E1.イベント名称, E1.前提イベント番号, E2.イベント名称 AS 前提イベント名称 FROM イベント AS E1 JOIN イベント AS E2 ON E1.前提イベント番号 = E2.イベント番号 WHERE E1.前提イベント番号 IS NOT NULL
69.イベントテーブルから、前提イベントまたは後続イベントが設定されているイベントについて、次の形式の一覧を抽出する
・イベント番号 ・イベント名称 ・前提イベント番号 ・前提イベント名称 ・後続イベント番号 ・後続イベント名称
SELECT E1.イベント番号,E1.イベント名称,E1.前提イベント番号,E2.イベント名称 AS 前提イベント名称,E1.後続イベント番号,E3.イベント名称 AS 後続イベント名称 FROM イベント AS E1 LEFT JOIN イベント E2 ON E1.前提イベント番号 = E2.イベント番号 LEFT JOIN イベント E3 ON E1.後続イベント番号 = E3.イベント番号 WHERE E1.前提イベント番号 IS NOT NULL OR E1.後続イベント番号 IS NOT NULL
70.ほかのイベントの前提となっているイベントについて、次の形式の一覧を抽出する。一覧はイベント番号順とする。
・イベント番号 ・イベント名称 ・前提イベント数
なお、前提イベント数は、そのイベントを前提としているイベントの数を表す
SELECT イベント番号, イベント名称, E3.イベント数 AS 前提イベント数 FROM イベント LEFT JOIN (SELECT E1.イベント番号 AS イベント番号, COUNT(E2.イベント番号) AS イベント数 FROM イベント AS E1 LEFT JOIN イベント AS E2 ON E1.イベント番号 = E2.前提イベント番号 GROUP BY E1.イベント番号) AS E3 ON イベント.イベント番号 = E3.イベント番号 ORDER BY イベント番号
