This is all the code I have now, must use oracle sql. CREATE TABLE supplier( sup

Question

This is all the code I have now, must use 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);

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

/*C*/
SELECT * FROM customer, "order";

/*D*/
SELECT customer.customer_id, customer."name", product.product_id, product."name" as Purchased_Products
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*/
SELECT * FROM customer NATURAL JOIN "order";
/*Theta*/
SELECT * FROM customer , "order"
WHERE customer.customer_id < "order".customer_id;

/*F*/
SELECT * FROM customer LEFT OUTER JOIN "order" ON customer.customer_id = "order".customer_id;
SELECT * FROM customer RIGHT OUTER JOIN "order" ON customer.customer_id = "order".customer_id;

https//canvas.umn.edu/courses/61329/files/folder/Laboratories?preview 3188700 g) Find the names and ids of customers who have never placed an order. For this, write two queries so that each solves the task using a different approach. h) Retrieve the names of the products whose names start with "B" and end with "d". i) 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 1:57 PM O Type here to search 920/2018

Explanation / Answer

g) SELECT CUSTOMER.customer_id, CUSTOMER.name FROM CUSTOMER, ORDER WHERE CUSTOMER.customer_id <> ORDER.customer_id;

h) SELECT name FROM product where name like 'B%d';

i) SELECT product_id, name, MIN (price) as 'Cheapest_Price' from product;

j) select supplier.supplier_id, supplier.supplier_name from (select supplier.supplier_id, supplier.supplier_name, count(product_id) from supplier join product on supplier.supplier_id = product.supplier_id group by (supplier_id, supplier_name) having count(product_id) >= 2);

k) select customer.customer_id, customer.name, MIN(order.bill) AS 'Minimum_Order', AVG(order.bill) as 'AVERAGE_PRICE' from customer JOIN order on customer.customer_id = order.customer_id group by (customer.customer_id, customer.name);

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.