SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE person ( person_code varchar(3) PRIMARY KEY, first_name varchar(15), last_name varchar(20), hiredate DATE ); CREATE TABLE purchase ( product_name varchar(25), salesperson varchar(3), purchase_date DATE, quantity numeric(4, 2) ); CREATE TABLE product ( product_name varchar(25) PRIMARY KEY, product_price numeric(8, 2), quantity_on_hand numeric(5, 0), laststockdate DATE ); CREATE TABLE old_item ( item_id CHAR(20), item_desc CHAR(25) ); CREATE TABLE purchase_archive ( product_name varchar (25), salesperson varchar(3), purchase_date DATE, quantity numeric (4, 2) ); ALTER TABLE purchase ADD CONSTRAINT purchase_fk_person FOREIGN KEY (salesperson) REFERENCES person; ALTER TABLE purchase ADD CONSTRAINT purchase_fk_product FOREIGN KEY (product_name) REFERENCES product; INSERT INTO product VALUES ('Small Widget', 99, 1, '15-JAN-2011'); INSERT INTO product VALUES ('Medium Widget', 75, 1000, '15-JAN-2010'); INSERT INTO product VALUES ('Chrome Phoobar', 50, 100, '15-JAN-2011'); INSERT INTO product VALUES ('Round Chrome Snaphoo', 25, 10000, NULL); INSERT INTO product VALUES ('Extra Huge Mega Phoobar +', 9.95, 1234, '15-JAN-2012'); INSERT INTO product VALUES ('Square Zinculator', 45, 1, '31-DEC-2010'); INSERT INTO product VALUES ('Large Widget', NULL, 5, '11-JAN-2011'); INSERT INTO person VALUES ( 'CA', 'Charlene', 'Atlas', '01-FEB-2010'); INSERT INTO person VALUES ('GA', 'Gary', 'Anderson', '15-FEB-2010'); INSERT INTO person VALUES ( 'BB', 'Bobby', 'Barkenhagen', '28-FEB-2010'); INSERT INTO person VALUES ( 'LB', 'Laren', 'Baxter', '01-MAR-2010'); INSERT INTO person VALUES ('JS', 'John', 'Smith', '15-FEB-2002'); INSERT INTO person VALUES ('DS', 'Dany', 'Smith', '15-FEB-2002'); INSERT INTO purchase VALUES ('Small Widget', 'CA', '14-JUL-2011', 1); INSERT INTO purchase VALUES ('Medium Widget', 'BB', '14-JUL-2011', 75); INSERT INTO purchase VALUES ('Chrome Phoobar', 'GA', '14-JUL-2011', 2); INSERT INTO purchase VALUES ('Small Widget', 'GA', '15-JUL-2011', 8); INSERT INTO purchase VALUES ('Medium Widget', 'LB', '15-JUL-2011', 20); INSERT INTO purchase VALUES ('Round Chrome Snaphoo', 'CA', '16-JUL-2011', 5); INSERT INTO old_item VALUES ('MSC-101', 'Bottle, Small'); INSERT INTO old_item VALUES ('MSC-102', 'Bottle, Large'); INSERT INTO old_item VALUES ('SPB-101', 'Box, Small'); INSERT INTO old_item VALUES ('SPB-102', 'Box, Large'); INSERT INTO purchase_archive VALUES ('Round Snaphoo', 'BB', '21-JUN-01', 10); INSERT INTO purchase_archive VALUES ('Large Harf linger', 'GA', '22-JUN-01', 50); INSERT INTO purchase_archive VALUES ( 'Medium Wodget', 'LB', '23-JUN-01', 20); INSERT INTO purchase_archive VALUES ('Small Widget', 'ZZ', '24-JUN-02 ', 80); INSERT INTO purchase_archive VALUES ('Chrome Phoobar', 'CA', '25-JUN-02', 2); INSERT INTO purchase_archive VALUES ('Small Widget', 'JT', '26-JUN-02', 50); ----------------------------------------------- /* 1. Напишите запрос, выводящий декартово произведение таблиц product и purchase. */ SELECT * FROM product, purchase; /* 2. Напишите запрос, выводящий: - наименование проданного товара product_name, - количество quantity (таблица purchase) и quantity_on_hand (таблица product). */ SELECT purchase.product_name, SUM(purchase.quantity) AS quantity, SUM(product.quantity_on_hand) AS quantity_on_hand FROM product INNER JOIN purchase ON product.product_name = purchase.product_name GROUP BY purchase.product_name; /* 3. Напишите запрос, выводящий: - наименование товара product_name (таблица purchase), - дату последней поставки laststockdate (таблица product), - фамилию продавца last_name (таблица person). */ SELECT purchase.product_name, product.laststockdate, person.last_name FROM product INNER JOIN purchase ON purchase.product_name = product.product_name INNER JOIN person ON purchase.salesperson = person.person_code; /* 4. Напишите запрос, выводящий - столбцы product_name, first_name, last_name внешнего объединения таблиц purchase и person. Используйте для таблиц короткие псевдонимы. */ SELECT pur.product_name, per.first_name, per.last_name FROM purchase AS pur LEFT JOIN person AS per ON pur.salesperson = per.person_code; SELECT 'PERSON:'; SELECT * FROM person; SELECT 'PRODUCT:'; SELECT * FROM product; SELECT 'PURCHASE:'; SELECT * FROM purchase; SELECT 'PURCHASE_ARCHIVE:'; SELECT * FROM purchase_archive; /* 5. Напишите запрос, который выводит коды продавцов salesperson из таблицы purchase_archive, которые не повторяются в таблице purchase. */ SELECT salesperson FROM purchase_archive EXCEPT SELECT salesperson FROM purchase; /* 6. Напишите запрос, который выводит коды только тех продавцов salesperson из таблицы purchase, которые так же содержатся в таблице purchase_archive. */ SELECT salesperson FROM purchase INTERSECT SELECT salesperson FROM purchase_archive; /* 7. Напишите запрос, который выводит все (в том числе повторяющиеся) коды продавцов salesperson из таблиц purchase и purchase_archive. */ SELECT salesperson as pers FROM purchase_archive as purch_a WHERE salesperson IN (SELECT EXCEPT salesperson FROM purchase as purch);