29 lines
1.7 KiB
Plaintext
29 lines
1.7 KiB
Plaintext
問1
|
|
CREATE TABLE item_category(category_id INT PRIMARY KEY AUTO_INCREMENT, category_name VARCHAR(256) NOT NULL);
|
|
INSERT INTO item_category (category_name) VALUES(1,'家具');
|
|
INSERT INTO item_category (category_name) VALUES('飲料');
|
|
INSERT INTO item_category (category_name) VALUES('食品');
|
|
|
|
問2
|
|
CREATE TABLE item(item_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(256) NOT NULL, price INT NOT NULL,category_id INT, FOREIGN KEY(category_id) REFERENCES item_category(category_id));
|
|
|
|
問3
|
|
SELECT item.category_id, item.item_id, item.name, item.price,item_category.category_name FROM item INNER JOIN item_category ON item.category_id = item_category.category_id;
|
|
|
|
問4
|
|
SELECT item.category_id, item.item_id, item.name, item.price,item_category.category_name FROM item INNER JOIN item_category ON item.category_id = item_category.category_id ORDER BY price ASC;
|
|
|
|
問5
|
|
SELECT item.category_id, item.item_id, item.name, item.price,item_category.category_name FROM item INNER JOIN item_category ON item.category_id = item_category.category_id WHERE category_name ='家具';
|
|
|
|
問6
|
|
SELECT item.category_id, item.item_id, item.name, item.price,item_category.category_name FROM item INNER JOIN item_category ON item.category_id = item_category.category_id WHERE item.price >= 1000 AND item.price <= 50000;
|
|
|
|
問7
|
|
SELECT COUNT(*) AS data_count FROM item;
|
|
|
|
問8
|
|
SELECT item.category_id, item.item_id, item.name, item.price,item_category.category_name FROM item INNER JOIN item_category ON item.category_id = item_category.category_id WHERE item.price = 150000;
|
|
|
|
問9
|
|
SELECT item.category_id, item.item_id, item.name, item.price,item_category.category_name FROM item INNER JOIN item_category ON item.category_id = item_category.category_id WHERE item.name LIKE '%の%' |