Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
達人に学ぶDB設計演習問題用テーブル定義
DROP DATABASE `mick_sample`;
CREATE DATABASE IF NOT EXISTS `mick_sample` DEFAULT CHARACTER SET utf8mb4;
USE mick_sample;
CREATE TABLE IF NOT EXISTS `sisya` (
sisya_code VARCHAR(191),
sisya_name VARCHAR(191),
PRIMARY KEY (sisya_code)
);
CREATE TABLE IF NOT EXISTS `siten` (
sisya_code VARCHAR(191),
siten_code VARCHAR(191),
siten_name VARCHAR(191),
PRIMARY KEY (sisya_code, siten_code),
FOREIGN KEY (sisya_code)
REFERENCES sisya(sisya_code)
);
CREATE TABLE IF NOT EXISTS `syohin_group` (
syohin_group_code VARCHAR(191),
group_name VARCHAR(191),
PRIMARY KEY (syohin_group_code)
);
CREATE TABLE IF NOT EXISTS `syohin` (
syohin_code VARCHAR(191),
syohin_name VARCHAR(191),
syohin_group_code VARCHAR(191),
PRIMARY KEY (syohin_code),
FOREIGN KEY (syohin_group_code)
REFERENCES syohin_group(syohin_group_code)
);
CREATE TABLE IF NOT EXISTS `siten_syohin` (
sisya_code VARCHAR(191),
siten_code VARCHAR(191),
syohin_code VARCHAR(191),
PRIMARY KEY (sisya_code, siten_code, syohin_code),
FOREIGN KEY (sisya_code, siten_code)
REFERENCES siten(sisya_code, siten_code),
FOREIGN KEY (syohin_code)
REFERENCES syohin(syohin_code)
);
USE mick_sample;
INSERT INTO sisya (sisya_code, sisya_name) VALUES
('001', '東京'),
('002', '大阪');
INSERT INTO siten (sisya_code, siten_code, siten_name) VALUES
('001', '01', '渋谷'),
('001', '02', '八重洲'),
('002', '01', ''),
('002', '02', '豊中');
INSERT INTO syohin_group (syohin_group_code, group_name) VALUES
('C1', '水洗用品'),
('C2', '食器'),
('C3', '書籍'),
('C4', '日用雑貨');
INSERT INTO syohin (syohin_code, syohin_name, syohin_group_code) VALUES
('001', '石鹸', 'C1'),
('002', 'タオル', 'C1'),
('003', 'ハブラシ', 'C1'),
('004', 'コップ', 'C1'),
('005', '', 'C2'),
('006', 'スプーン', 'C2'),
('007', '雑誌', 'C3'),
('008', '爪切り', 'C4');
INSERT INTO siten_syohin (sisya_code, siten_code, syohin_code) VALUES
('001', '01', '001'),
('001', '01', '002'),
('001', '01', '003'),
('001', '02', '002'),
('001', '02', '003'),
('001', '02', '004'),
('001', '02', '005'),
('001', '02', '006'),
('002', '01', '001'),
('002', '01', '002'),
('002', '02', '007'),
('002', '02', '008');
SELECT syohin.syohin_group_code, group_name, COUNT(*) AS 'syohin_count'
FROM syohin_group INNER JOIN syohin
ON syohin_group.syohin_group_code = syohin.syohin_group_code
GROUP BY syohin_group_code;
SELECT sisya.sisya_name, siten.siten_name, syohin.syohin_name
FROM sisya INNER JOIN siten
ON sisya.sisya_code = siten.sisya_code
INNER JOIN siten_syohin
ON sisya.sisya_code = siten_syohin.sisya_code
AND siten.siten_code = siten_syohin.siten_code
INNER JOIN syohin
ON siten_syohin.syohin_code = syohin.syohin_code
SELECT siten.sisya_code, siten.siten_code, COUNT(*) AS '最大商品数'
FROM siten INNER JOIN siten_syohin
ON siten.sisya_code = siten_syohin.sisya_code
AND siten.siten_code = siten_syohin.siten_code
GROUP BY siten.sisya_code, siten.siten_code
HAVING COUNT(*) >= (
SELECT MAX(syohin_num)
FROM (
SELECT siten.sisya_code, siten.siten_code, COUNT(*) AS syohin_num
FROM siten INNER JOIN siten_syohin
ON siten.sisya_code = siten_syohin.sisya_code
AND siten.siten_code = siten_syohin.siten_code
GROUP BY siten.sisya_code, siten.siten_code) AS max
)
CREATE VIEW siten_syohin_num(sisya_code, siten_code, syohin_num)
AS
SELECT siten.sisya_code, siten.siten_code, COUNT(*) AS syohin_num
FROM siten INNER JOIN siten_syohin
ON siten.sisya_code = siten_syohin.sisya_code
AND siten.siten_code = siten_syohin.siten_code
GROUP BY siten.sisya_code, siten.siten_code;
SELECT sisya_code, siten_code, syohin_num AS '最大商品数'
FROM siten_syohin_num
HAVING syohin_num >= (
SELECT MAX(syohin_num)
FROM siten_syohin_num
)
@koyo-miyamura

This comment has been minimized.

Copy link
Owner Author

@koyo-miyamura koyo-miyamura commented Apr 7, 2019

こんな感じで流す

sudo mysql -uroot < mick_seed.sql

mysql中でsqlスクリプトの実行

mysql> use mick_sample;
mysql> source practice_5_1.sql
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment