達人に学ぶDB設計演習問題用テーブル定義

mick_sample.sql

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)
);

mick_seed.sql

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');

practice_5_1.sql

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;

practice_5_2.sql

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

practice_5_3.sql

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
)

practice_5_3_VIEW.sql

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
)

こんな感じで流す

sudo mysql -uroot < mick_seed.sql

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

mysql> use mick_sample;
mysql> source practice_5_1.sql