80 lines
1.4 KiB
SQL
80 lines
1.4 KiB
SQL
--問１
|
|
CREATE TABLE item_category (
|
|
category_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
category_name VARCHAR(256) NOT NULL
|
|
);
|
|
|
|
INSERT INTO item_category(category_name) VALUES('家具'),('飲料'),('食品');
|
|
|
|
|
|
--問２
|
|
CREATE TABLE items(
|
|
item_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(256) NOT NULL,
|
|
price INT NOT NULL,
|
|
category_id INT,
|
|
FOREIGN KEY(category_id) REFERENCES item_category(category_id)
|
|
);
|
|
|
|
INSERT INTO items(name,price,category_id)
|
|
VALUES('ローマのテーブル',150000,1),('げーたるーど2000 500ml',370,2),('原始肉',2500,3),('魅惑の椅子',100000,1);
|
|
|
|
--問３
|
|
SELECT
|
|
a.item_id,
|
|
a.name,
|
|
a.price,
|
|
b.category_id,
|
|
b.category_name
|
|
FROM items a
|
|
INNER JOIN item_category b
|
|
ON a.category_id = b.category_id;
|
|
|
|
|
|
--問４
|
|
SELECT *
|
|
FROM items
|
|
ORDER BY price;
|
|
|
|
--問５
|
|
SELECT
|
|
a.item_id,
|
|
a.name,
|
|
a.price,
|
|
b.category_id,
|
|
b.category_name
|
|
FROM items a
|
|
INNER JOIN item_category b
|
|
ON a.category_id = b.category_id AND b.category_name = '家具';
|
|
|
|
--問６
|
|
SELECT
|
|
a.item_id,
|
|
a.name,
|
|
a.price,
|
|
b.category_id,
|
|
b.category_name
|
|
FROM items a
|
|
INNER JOIN item_category b
|
|
ON a.category_id = b.category_id AND a.price <= 50000 AND a.price >= 1000;
|
|
|
|
--問７
|
|
SELECT COUNT(*) AS data_count
|
|
FROM items;
|
|
|
|
--問８
|
|
SELECT * FROM items
|
|
WHERE price = (SELECT MAX(price) FROM items);
|
|
|
|
--問９
|
|
SELECT
|
|
a.item_id,
|
|
a.name,
|
|
a.price,
|
|
b.category_id,
|
|
b.category_name
|
|
FROM items a
|
|
INNER JOIN item_category b
|
|
ON a.category_id = b.category_id
|
|
WHERE a.name LIKE'%の%';
|