51 lines
2.2 KiB
SQL
51 lines
2.2 KiB
SQL
CREATE TABLE item_category (
|
|
category_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'カテゴリID',
|
|
category_name VARCHAR(256) NOT NULL COMMENT 'カテゴリ名'
|
|
);
|
|
|
|
|
|
INSERT INTO item_category VALUES (1,'家具');
|
|
INSERT INTO item_category VALUES (2,'飲料');
|
|
INSERT INTO item_category VALUES (3,'食品');
|
|
|
|
|
|
CREATE TABLE items (
|
|
item_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'アイテムID',
|
|
name VARCHAR(256) NOT NULL COMMENT 'アイテム名',
|
|
price INT NOT NULL COMMENT '価格',
|
|
category_id INT COMMENT 'カテゴリID',
|
|
FOREIGN KEY (category_id) REFERENCES item_category(category_id)
|
|
);
|
|
|
|
INSERT INTO items VALUES (1,'ローマのテーブル',150000,1);
|
|
INSERT INTO items VALUES (2,'げーたるーど2000 500ml',370,2);
|
|
INSERT INTO items VALUES (3,'原始肉',2500,3);
|
|
INSERT INTO items VALUES (4,'魅惑の椅子',100000,1);
|
|
|
|
SELECT item_category.category_id,items.item_id,items.name,items.price,item_category.category_name AS name
|
|
FROM item_category LEFT OUTER JOIN items ON items.category_id = item_category.category_id;
|
|
|
|
UPDATE items SET price = 100000 WHERE items.name = '魅惑の椅子';
|
|
|
|
SELECT item_category.category_id,items.item_id,items.name,items.price,item_category.category_name AS name
|
|
FROM item_category LEFT OUTER JOIN items ON items.category_id = item_category.category_id
|
|
ORDER BY items.price;
|
|
|
|
SELECT item_category.category_id,items.item_id,items.name,items.price,item_category.category_name AS name
|
|
FROM item_category LEFT OUTER JOIN items ON items.category_id = item_category.category_id WHERE item_category.category_id = 1;
|
|
|
|
SELECT item_category.category_id,items.item_id,items.name,items.price,item_category.category_name AS name
|
|
FROM item_category LEFT OUTER JOIN items ON items.category_id = item_category.category_id WHERE items.price >=1000 and items.price <=50000;
|
|
|
|
SELECT COUNT(*)AS data_count FROM items;
|
|
|
|
--Q8--
|
|
SELECT item_category.category_id,items.item_id,items.name,items.price,item_category.category_name AS name
|
|
FROM item_category LEFT OUTER JOIN items ON items.category_id = item_category.category_id
|
|
WHERE items.price = (SELECT MAX(price) FROM items);
|
|
|
|
---Q9---
|
|
SELECT item_category.category_id,items.item_id,items.name,items.price,item_category.category_name AS name
|
|
FROM item_category LEFT OUTER JOIN items ON items.category_id = item_category.category_id
|
|
WHERE items.name LIKE '%の%';
|