総合演習1

セットアップ用SQLはこちら

以下のSQLをコピーして実行してください。新しいデータベースdbkiso1-2017examが出来ます。データベース一覧に表示されない場合は、一覧画面を更新してください。

SQL

-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- ホスト: 127.0.0.1
-- 生成日時: 2024-06-20 16:07:44
-- サーバのバージョン： 10.4.32-MariaDB
-- PHP のバージョン: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- データベース: `dbkiso1-2017exam`
--
CREATE DATABASE IF NOT EXISTS `dbkiso1-2017exam` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `dbkiso1-2017exam`;

-- --------------------------------------------------------

--
-- テーブルの構造 `exam_member`
--

DROP TABLE IF EXISTS `exam_member`;
CREATE TABLE `exam_member` (
  `id` varchar(5) NOT NULL DEFAULT '',
  `class` int(11) DEFAULT NULL,
  `name_family` varchar(10) DEFAULT NULL,
  `name_given` varchar(10) DEFAULT NULL,
  `ruby_family` varchar(20) DEFAULT NULL,
  `ruby_given` varchar(20) DEFAULT NULL,
  `gender` varchar(3) DEFAULT NULL,
  `prefecture` varchar(10) DEFAULT NULL,
  `blood_type` varchar(3) DEFAULT NULL,
  `height` double DEFAULT NULL,
  `weight` double DEFAULT NULL,
  `score_japanese` int(11) DEFAULT NULL,
  `score_mathematics` int(11) DEFAULT NULL,
  `score_english` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- テーブルのデータのダンプ `exam_member`
--

INSERT INTO `exam_member` (`id`, `class`, `name_family`, `name_given`, `ruby_family`, `ruby_given`, `gender`, `prefecture`, `blood_type`, `height`, `weight`, `score_japanese`, `score_mathematics`, `score_english`) VALUES
('71001', 1, '磯野', '勝男', 'イソノ', 'カツオ', '男', '山梨県', 'B', 158.1, 60.2, 44, 65, 25),
('71002', 1, '市原', '季衣', 'イチハラ', 'トシエ', '女', '兵庫県', 'A', 154.6, 47.3, 78, 85, 43),
('71003', 1, '今川', '真希', 'イマガワ', 'マキ', '女', '和歌山県', 'A', 156.1, 53.1, 46, 23, 54),
('71004', 1, '上村', '英光', 'ウエムラ', 'ヒデミツ', '男', '三重県', 'B', 169.1, 56.6, 38, 81, 56),
('71005', 1, '内山', '剛', 'ウチヤマ', 'タケシ', '男', '千葉県', 'A', 158.3, 57.6, 96, 81, 25),
('71006', 1, '遠藤', '正浩', 'エンドウ', 'マサヒロ', '男', '秋田県', 'O', 176.8, 76.6, 33, 95, 55),
('71007', 1, '大友', '由貴', 'オオトモ', 'ユキ', '女', '山口県', 'A', 157.7, 53.5, 62, 21, 51),
('71008', 1, '大谷', '治男', 'オオヤ', 'ハルオ', '男', '香川県', 'A', 176.2, 68.9, 46, 80, 62),
('71009', 1, '小椋', '寛', 'オグラ', 'ヒロシ', '男', '京都府', 'O', 174.6, 68, 57, 31, 45),
('71010', 1, '金山', '茂子', 'カナヤマ', 'シゲコ', '女', '鹿児島県', 'O', 165.9, 60.8, 49, 74, 40),
('71011', 1, '岸', '律子', 'キシ', 'リツコ', '女', '青森県', 'B', 161.5, 56.9, 55, 83, 73),
('71012', 1, '小泉', '花陽', 'コイズミ', 'ハナヨ', '女', '大分県', 'A', 163.7, 54.4, 44, 47, 76),
('71013', 1, '古代', '進', 'コダイ', 'ススム', '男', '東京都', 'B', 168.1, 65.3, 56, 27, 74),
('71014', 1, '佐川', '桃佳', 'サガワ', 'モモカ', '女', '岡山県', 'A', 169.3, 61.9, 90, 32, 51),
('71015', 1, '下田', '俊史', 'シモダ', 'トシフミ', '男', '岐阜県', 'A', 167.7, 63, 67, 50, 55),
('71016', 1, '高梨', '桃香', 'タカナシ', 'モモカ', '女', '佐賀県', 'AB', 160.9, 53.8, 78, 96, 52),
('71017', 1, '竹村', '吉夫', 'タケムラ', 'ヨシオ', '男', '愛知県', 'B', 179.3, 73.6, 43, 64, 54),
('71018', 1, '田代', '新吉', 'タシロ', 'シンキチ', '男', '大阪府', 'B', 168.4, 69.5, 51, 66, 67),
('71019', 1, '田所', '真里', 'タドコロ', 'マリ', '女', '青森県', 'O', 163.5, 63.9, 100, 25, 63),
('71020', 1, '露木', '孝志', 'ツユキ', 'タカシ', '男', '高知県', 'O', 180.3, 78.7, 83, 46, 82),
('71021', 1, '西木野', '真姫', 'ニシキノ', 'マキ', '女', '島根県', 'O', 154.1, 49.4, 88, 85, 100),
('71022', 1, '西原', '敏子', 'ニシハラ', 'トシコ', '女', '富山県', 'A', 161.2, 46.3, 89, 61, 41),
('71023', 1, '二宮', '佳音', 'ニノミヤ', 'カノン', '女', '熊本県', 'A', 151.9, 56.3, 20, 47, 82),
('71024', 1, '林', '正', 'ハヤシ', 'タダシ', '男', '山梨県', 'O', 178.1, 74.5, 100, 75, 78),
('71025', 1, '平沢', '康生', 'ヒラサワ', 'ヤスオ', '男', '広島県', 'A', 168.6, 69.6, 72, 56, 53),
('71026', 1, '深沢', '絢子', 'フカザワ', 'アヤコ', '女', '福井県', 'A', 162.1, 61.5, 58, 42, 86),
('71027', 1, '古川', '市郎', 'フルカワ', 'イチロウ', '男', '茨城県', 'O', 171.6, 62.4, 68, 100, 30),
('71028', 1, '星空', '凛', 'ホシゾラ', 'リン', '女', '神奈川県', 'B', 162.4, 55.1, 81, 52, 52),
('71029', 1, '細田', '春代', 'ホソダ', 'ハルヨ', '女', '静岡県', 'O', 159.2, 55.8, 55, 72, 71),
('71030', 1, '松木', '守彦', 'マツキ', 'モリヒコ', '男', '長崎県', 'O', 163, 57.4, 100, 68, 92),
('71031', 1, '的場', '結奈', 'マトバ', 'ユウナ', '女', '兵庫県', 'A', 157.8, 51.5, 72, 55, 66),
('71032', 1, '森', '優那', 'モリ', 'ユウナ', '女', '埼玉県', 'B', 154.3, 55, 29, 62, 48),
('71033', 1, '山村', '麻衣子', 'ヤマムラ', 'マイコ', '女', '岩手県', 'A', 155.4, 59.2, 84, 65, 27),
('71034', 1, '米倉', '隆介', 'ヨネクラ', 'リュウスケ', '男', '長崎県', 'O', 164.8, 61.7, 60, 69, 54),
('71035', 2, '東', '来実', 'アズマ', 'クルミ', '女', '栃木県', 'O', 164, 53.3, 54, 86, 53),
('71036', 2, '安室', '零', 'アムロ', 'レイ', '男', '岐阜県', 'O', 171.8, 68.8, 65, 95, 75),
('71037', 2, '石原', '慶治', 'イシハラ', 'ケイジ', '男', '福島県', 'A', 166.7, 52.8, 42, 70, 86),
('71038', 2, '磯崎', '清助', 'イソザキ', 'セイスケ', '男', '滋賀県', 'AB', 178.7, 73.8, 70, 38, 83),
('71039', 2, '岩永', '徳一', 'イワナガ', 'トクイチ', '男', '和歌山県', 'B', 168.1, 57.6, 40, 28, 59),
('71040', 2, '鵜飼', '真', 'ウカイ', 'マコト', '男', '高知県', 'A', 168.4, 63.5, 67, 72, 43),
('71041', 2, '大滝', '松雄', 'オオタキ', 'マツオ', '男', '奈良県', 'O', 161.6, 64.2, 63, 75, 76),
('71042', 2, '小沢', '雅美', 'オザワ', 'マサミ', '女', '広島県', 'AB', 158.6, 58.1, 51, 29, 0),
('71043', 2, '門脇', '優華', 'カドワキ', 'ユウカ', '女', '大分県', 'O', 161.9, 54.8, 77, 76, 28),
('71044', 2, '金谷', '良夫', 'カナヤ', 'ヨシオ', '男', '栃木県', 'O', 170.1, 67.1, 49, 64, 44),
('71045', 2, '金井', '正紀', 'カネイ', 'マサキ', '男', '岩手県', 'B', 169.3, 53.3, 27, 89, 74),
('71046', 2, '神崎', '乃愛', 'カンザキ', 'ノア', '女', '鳥取県', 'AB', 147.3, 48, 47, 50, 62),
('71047', 2, '栗林', '有紗', 'クリバヤシ', 'アリサ', '女', '山形県', 'A', 153, 49.4, 85, 55, 86),
('71048', 2, '高坂', '穂乃課', 'コウサカ', 'ホノカ', '女', '青森県', 'B', 166.3, 63.9, 63, 52, 51),
('71049', 2, '志賀', '洋一', 'シガ', 'ヨウイチ', '男', '東京都', 'O', 167.5, 58.6, 73, 83, 65),
('71050', 2, '篠田', '文二', 'シノダ', 'ブンジ', '男', '静岡県', 'AB', 168.7, 70.3, 27, 11, 47),
('71051', 2, '園田', '海未', 'ソノダ', 'ウミ', '女', '茨城県', 'O', 164.8, 63.6, 97, 85, 90),
('71052', 2, '中西', '礼', 'ナカニシ', 'レイ', '男', '福井県', 'O', 178.5, 80, 46, 100, 62),
('71053', 2, '永山', '喜代治', 'ナガヤマ', 'キヨジ', '男', '広島県', 'B', 164.5, 63.6, 18, 82, 76),
('71054', 2, '野沢', '栄三', 'ノザワ', 'エイゾウ', '男', '山口県', 'O', 165.9, 65, 60, 57, 50),
('71055', 2, '火野', '玲', 'ヒノ', 'レイ', '女', '宮城県', 'A', 169.4, 56.2, 87, 78, 95),
('71056', 2, '平沢', '唯', 'ヒラサワ', 'ユイ', '女', '宮崎県', 'O', 161.5, 59.5, 53, 56, 59),
('71057', 2, '深井', '松太郎', 'フカイ', 'マツタロウ', '男', '埼玉県', 'O', 176.8, 69.4, 43, 75, 51),
('71058', 2, '藤木', '剛', 'フジキ', 'タケシ', '男', '神奈川県', 'O', 180.2, 77, 51, 54, 66),
('71059', 2, '堀川', '沙和', 'ホリカワ', 'サワ', '女', '和歌山県', 'A', 158.9, 52.8, 73, 87, 60),
('71060', 2, '真鍋', '睦', 'マナベ', 'ムツミ', '女', '長崎県', 'B', 156, 63.5, 80, 61, 57),
('71061', 2, '南', '小鳥', 'ミナミ', 'コトリ', '女', '大分県', 'AB', 161.2, 58.5, 72, 75, 85),
('71062', 2, '守屋', '紗季', 'モリヤ', 'サキ', '女', '宮城県', 'A', 157.1, 58, 67, 34, 63),
('71063', 2, '柳田', '庄一', 'ヤナギダ', 'ショウイチ', '男', '長野県', 'O', 179.3, 76.2, 54, 56, 55),
('71064', 2, '山形', '清香', 'ヤマガタ', 'サヤカ', '女', '宮崎県', 'O', 165.9, 63.3, 12, 38, 67),
('71065', 2, '山口', '寧音', 'ヤマグチ', 'ネネ', '女', '岩手県', 'B', 154.4, 47.7, 100, 41, 47),
('71066', 2, '横田', '義明', 'ヨコタ', 'ヨシアキ', '男', '愛媛県', 'O', 177.7, 85.9, 27, 40, 47),
('71067', 2, '渡辺', '鉄男', 'ワタナベ', 'テツオ', '男', '神奈川県', 'A', 171, 61.4, 61, 47, 16),
('71068', 3, '綾瀬', '絵里', 'アヤセ', 'エリ', '女', '山梨県', 'AB', 157.8, 44.3, 95, 92, 100),
('71069', 3, '綾波', '玲', 'アヤナミ', 'レイ', '女', '埼玉県', 'A', 152.3, 48, 100, 100, 100),
('71070', 3, '新谷', '風香', 'アラヤ', 'フウカ', '女', '大分県', 'O', 150, 49.7, 34, 70, 86),
('71071', 3, '一堂', '礼', 'イチドウ', 'レイ', '男', '福井県', 'O', 174, 74.5, 26, 15, 7),
('71072', 3, '市原', '富士雄', 'イチハラ', 'フジオ', '男', '新潟県', 'A', 170.4, 75.8, 60, 56, 50),
('71073', 3, '岩井', '初音', 'イワイ', 'ハツネ', '女', '沖縄県', 'O', 149.3, 54.8, 100, 75, 37),
('71074', 3, '江頭', '政子', 'エガシラ', 'マサコ', '女', '鳥取県', 'B', 166.5, 62.4, 49, 48, 35),
('71075', 3, '江川', '友彦', 'エガワ', 'トモヒコ', '男', '広島県', 'B', 161.6, 56.9, 58, 74, 70),
('71076', 3, '大嶋', '靖夫', 'オオシマ', 'ヤスオ', '男', '大阪府', 'A', 172.8, 57.6, 67, 75, 55),
('71077', 3, '片岡', '千鶴', 'カタオカ', 'チヅル', '女', '香川県', 'O', 154.3, 52.4, 49, 80, 93),
('71078', 3, '河口', '久男', 'カワグチ', 'ヒサオ', '男', '三重県', 'A', 172.8, 74.4, 39, 88, 51),
('71079', 3, '川原', '亘', 'カワハラ', 'ワタル', '男', '北海道', 'A', 174.4, 63.9, 54, 71, 65),
('71080', 3, '神崎', '佳代', 'カンザキ', 'カヨ', '女', '群馬県', 'O', 156, 59.6, 68, 17, 52),
('71081', 3, '窪田', '清香', 'クボタ', 'サヤカ', '女', '大阪府', 'B', 148.9, 55.2, 55, 29, 56),
('71082', 3, '栗本', '昌枝', 'クリモト', 'マサエ', '女', '香川県', 'O', 170.2, 68.7, 74, 60, 65),
('71083', 3, '国分', '隆二', 'コクブ', 'リュウジ', '男', '青森県', 'AB', 175, 62.8, 64, 77, 57),
('71084', 3, '斉藤', '昌己', 'サイトウ', 'マサミ', '女', '香川県', 'O', 155.7, 58.2, 58, 65, 33),
('71085', 3, '笹森', '美沙', 'ササモリ', 'ミサ', '女', '宮崎県', 'O', 155.7, 56.7, 46, 71, 43),
('71086', 3, '須賀', '彩夏', 'スガ', 'サイカ', '女', '福岡県', 'B', 164.2, 57.4, 36, 49, 56),
('71087', 3, '瀬川', '彩夏', 'セガワ', 'サイカ', '女', '静岡県', 'A', 156, 49.6, 49, 0, 83),
('71088', 3, '田上', '瑞穂', 'タウエ', 'ミズホ', '女', '兵庫県', 'AB', 150.2, 59.6, 68, 73, 28),
('71089', 3, '田辺', '公子', 'タナベ', 'キミコ', '男', '富山県', 'O', 174.5, 62.7, 100, 72, 61),
('71090', 3, '谷村', '柚', 'タニムラ', 'ユズ', '女', '岩手県', 'A', 153.7, 53.2, 63, 50, 49),
('71091', 3, '寺島', '胡春', 'テラシマ', 'コハル', '女', '福島県', 'B', 155.3, 60.8, 79, 48, 84),
('71092', 3, '東條', '希', 'トウジョウ', 'ノゾミ', '女', '福島県', 'B', 159.7, 54.6, 90, 88, 95),
('71093', 3, '飛田', '祐奈', 'トビタ', 'ユウナ', '女', '和歌山県', 'O', 157.7, 54.7, 59, 73, 37),
('71094', 3, '中里', '恒男', 'ナカザト', 'ツネオ', '男', '北海道', 'A', 164.8, 66.3, 62, 37, 80),
('71095', 3, '福永', '由紀江', 'フクナガ', 'ユキエ', '女', '奈良県', 'B', 164.1, 53.9, 80, 74, 83),
('71096', 3, '福元', '宣彦', 'フクモト', 'ノブヒコ', '男', '京都府', 'O', 168.7, 70.6, 43, 57, 27),
('71097', 3, '藤木', '五郎', 'フジキ', 'ゴロウ', '男', '徳島県', 'O', 168.9, 74.2, 96, 82, 85),
('71098', 3, '本多', '莉央', 'ホンダ', 'リオ', '女', '東京都', 'A', 159.5, 57.5, 80, 64, 64),
('71099', 3, '矢澤', 'にこ', 'ヤザワ', 'ニコ', '女', '新潟県', 'B', 168.7, 60.9, 65, 55, 60),
('71100', 3, '山崎', '時子', 'ヤマザキ', 'トキコ', '女', '長崎県', 'A', 162.1, 72.8, 78, 17, 74);

--
-- ダンプしたテーブルのインデックス
--

--
-- テーブルのインデックス `exam_member`
--
ALTER TABLE `exam_member`
  ADD PRIMARY KEY (`id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

問題1
問題2

問題1

テーブル exam_member は以下のような構造である。

カラム名	型	意味
id	VARCHAR(5)	学籍番号
name_family	VARCHAR(10)	姓（漢字）
name_given	VARCHAR(10)	名（漢字）
ruby_family	VARCHAR(20)	姓（フリガナ）
ruby_given	VARCHAR(20)	名（フリガナ）
gender	VARCHAR(3)	性別
prefecture	VARCHAR(10)	出身地
blood_type	VARCHAR(3)	血液型
height	DOUBLE	身長
weight	DOUBLE	体重
socre_japanese	INT	国語の試験得点
score_mathematics	INT	数学の試験得点
score_english	INT	英語の試験得点

ただし主キーは id である（auto_incrementではない）

(1) 血液型がA型のレコードを抽出し、学籍番号順に並べる。すべてのカラムを表示すること。

(2) 名（name_given）が、『善子』『梨子』のように『～子』で終わるレコードを抽出し、学籍番号順に並べる。すべてのカラムを表示すること。

(3) 国語、数学、英語のすべての試験得点が80点以上のレコードを抽出し、3科目の合計得点を求め（score_totalというカラム名で表示すること）、合計得点の降順（大きい順）に並べる。表示するカラムは学籍番号、姓（漢字）、名（漢字）、国語の試験得点、数学の試験得点、英語の試験得点、合計得点とすること。

(4) 出身地が東北地方（青森県、岩手県、秋田県、宮城県、山形県、福島県のいずれか）のレコードを抽出する。表示するカラムは学籍番号、姓（漢字）、名（漢字）、出身地とすること。

(5) 全レコードのBMI（体重kg÷身長m²）を小数第1位まで求め（小数第2位を四捨五入）、学籍番号順に表示する。表示するカラムは学籍番号、姓（漢字）、名（漢字）、身長、体重、BMIとすること。

(6) クラス別・男女別に各科目の平均点を小数第1位まで求める。なお国語・数学・英語の平均点のカラム名はそれぞれavg_japanese、avg_mathematics、avg_englishとすること。

(7) 出身地（prefecture）ごとに人数（カラム名はninzuとすること）を合計し、人数の多い順に出力する

(8) 男女別・血液型別に人数（カラム名はninzuとすること）を出力する

問題2

(1) 以下のようなテーブルを作成するSQLを記述しなさい。

テーブル名：teacher

カラム名	型	意味
id	VARCHAR(5)	番号
name_family	VARCHAR(10)	姓（漢字）
name_given	VARCHAR(10)	名（漢字）
ruby_family	VARCHAR(20)	姓（フリガナ）
ruby_given	VARCHAR(20)	名（フリガナ）
gender	VARCHAR(3)	性別
prefecture	VARCHAR(10)	出身地
subject	VARCHAR(20)	担当講義名