This is the code I have for the tables. (In oracle sql) CREATE TABLE supplier( s

Question

This is the code I have for the tables. (In oracle sql)

CREATE TABLE supplier(
supplier_id VARCHAR2(32CHAR) PRIMARY KEY,
supplier_name VARCHAR2(32CHAR),
phone VARCHAR2(32CHAR)
);

INSERT INTO supplier
VALUES('0025','Affiliated Foods', '253-7823562');
INSERT INTO supplier
VALUES('0038','Aldi','264-5689420');
INSERT INTO supplier
VALUES('0012','Franklins','312-5387460');
INSERT INTO supplier
VALUES('0059','Coop Market','218-6597534');
INSERT INTO supplier
VALUES('0046','Loeb Foods','237-9837547');

CREATE TABLE product(
product_id INT PRIMARY KEY,
supplier_id VARCHAR2(32CHAR),
"name" VARCHAR2(32CHAR),
"price($)" NUMERIC(*,2),
FOREIGN KEY(supplier_id) references supplier
);

INSERT INTO product
VALUES(11353,'0025','Spring Water',3.99);
INSERT INTO product
VALUES(11958,'0059','Milk',1.68);
INSERT INTO product
VALUES(11789,'0046','Bread',2.55);
INSERT INTO product
VALUES(11005,'0012','Peaunt Butter',5.97);

CREATE TABLE customer(
customer_id INT PRIMARY KEY,
"name" VARCHAR2(32CHAR),
member_since INT
);

INSERT INTO customer
VALUES(102385,'Bob',2015);
INSERT INTO customer
VALUES(103569,'Janna',2018);
INSERT INTO customer
VALUES(112538,'Ruperti',2013);
INSERT INTO customer
VALUES(27873,'Schlomo',1999);

CREATE TABLE "order"(
order_id INT PRIMARY KEY,
status VARCHAR2(32CHAR),
customer_id INT,
paid_for_yn VARCHAR2(32CHAR),
"bill($)" INT,
FOREIGN KEY(customer_id) references customer,
CHECK("bill($)" > 0)
);

INSERT INTO "order"
VALUES(2536,'shipped', 112538,'Yes',35);
INSERT INTO "order"
VALUES(2544,'processing',102385,'Yes',47);
INSERT INTO "order"
VALUES(2689,'processing',102385,'Yes',12);
INSERT INTO "order"
VALUES(2705,'shipped',103569,'Yes',125);

CREATE TABLE order_contents(
order_id INT, FOREIGN KEY(order_id) references "order",
product_id INT
);

INSERT INTO order_contents
VALUES(2536,11353);
INSERT INTO order_contents
VALUES(2536,11789);
INSERT INTO order_contents
VALUES(2689,11005);
INSERT INTO order_contents
VALUES(2705,11958);

UPDATE supplier
SET phone = '555-555-5555'
WHERE supplier_name ='Aldi';

c) (Proof of Completion Due 09/17) Find the Cartesian product between customer and order (Proof of Completion Due 09/17) (Join of several tables) Find the names and ids of customers along with the names and ids of the products that they have purchased d) e) (Proof of Completion Due 09/17) (Natural joins and theta joins) Do a natural join of customer and order. Then do a theta-join between customer and order on customer id. Compare the difference in the output f) (Proof of Completion Due 09/17) (Outer joins) Do a natural left outer join g) Find the names and ids of customers who have never placed an order. For this, h) Retrieve the names of the products whose names start with "B" and end with "d between customer and order. Then do a natural right outer join between them write two queries so that each solves the task using a different approach. ) Find the names and ids of the cheapest products. j) Find the names and ids of the suppliers that supply at least 2 different products k) ) For every customer, output their id, name, the smallest bill amount he/she has paid, and his/her average bill amount 1) ) Find the names of those customers who have purchased a product that is more expensive than the average cost of the products m) () (Top-K) Retrieve the names and ids of the 2 most expensive products n) (Deletion query) Delete all those orders that have been shipped already.

Explanation / Answer

c) Select * from Customer, order;

d) Select Customer.customer_id, Customer.name, product.product_id, product.name from Customer JOIN ORDER ON CUSTOMER.CUSTOMER_ID = ORDER.customer_id JOIN order_contents ON order_contents.ORDER_ID = ORDER.order_id JOIN PRODUCT ON PRODUCT.product_id = order_contents.product_id;

e) Natural Join between Customers and Orders:-

SELECT * FROM CUSTOMER NATURAL JOIN ORDER;

Theta - Join

SELECT * FROM CUSTOMER , ORDER WHERE CUSTOMER.customer_id < ORDER.customer_id;

f) Natural Left Outer JOIN:-

SELECT * FROM CUSTOMER LEFT OUTER JOIN ORDER ON CUSTOMER.customer_id = ORDER.customer_id;

Natural Right Outer JOIN:-

SELECT * FROM CUSTOMER RIGHT OUTER JOIN ORDER ON CUSTOMER.customer_id = ORDER.customer_id;

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.