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
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 alreadyStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started