eng5script

--
DROP TABLE purchases purge;
DROP TABLE customers purge;
DROP TABLE products purge;
--
CREATE TABLE customers(
customer_id NUMBER(22) primary key,
last_name VARCHAR2(20)
);
CREATE TABLE products(
product_id NUMBER(22) primary key,
name VARCHAR(22),
price NUMBER(5,2)
);
CREATE TABLE purchases(
product_id NUMBER(22),
customer_id NUMBER(22),
quantity NUMBER(22)
);

ALTER TABLE purchases add constraint purchases_pk primary key (product_id, customer_id);
ALTER TABLE purchases add constraint purchases_products_fk foreign key (product_id) references products(product_id);
ALTER TABLE purchases add constraint purchases_customers_fk foreign key (customer_id) references customers(customer_id);
--
INSERT INTO customers VALUES (1,'furuya');
INSERT INTO customers VALUES (2,'kayo');
INSERT INTO customers VALUES (3,'mashima');
INSERT INTO customers VALUES (4, 'brown');
--
INSERT INTO products VALUES (101,'nori -fresh- 5pcs', 1.3);
INSERT INTO products VALUES (89, 'beans' , 0.5);
INSERT INTO products VALUES (200, 'wraps macha', 2);
--
INSERT INTO purchases VALUES (101, 4, 2);
INSERT INTO purchases VALUES (101, 2, 10);
INSERT INTO purchases VALUES (89, 4, 10);
INSERT INTO purchases VALUES (89, 1, 1);
--
SELECT c.customer_id, c.last_name , pr.name , pu.quantity, pr.price
FROM customers c
    join purchases pu ON (pu.customer_id = c.customer_id)
    join products pr ON (pr.product_id = pr.product_id)
WHERE c.last_name = 'brown'