Provide working SQL DML statements for the following database schema and queries

Question

Provide working SQL DML statements for the following database schema and queries EXERCISE CUSTOMER , Type, Firstname, Lastname, Address, City) INVENTORY (ID, ItemName, Type, MadelnStore, SupplierName DailyAverageSold, Price) ORDERS (ID, Customer FK, Item FK, Quantity, DeliveryDate) 11. Find orders requesting 10 or more units of a product . Show: order id, deliver date, item name, quantity Find what products and how many units of each product 12. customers ordered in each order . Show: Lastname, firstname, item name, and quantity For each item in each order, find the item partial amount . Show: order id, lastname, quantity, price and partial-amountt . Hint: Partia!Amount = Quantity * Price 13.

Explanation / Answer

Creating tables to verify on

CUSTOMER TABLE

create table CUSTOMER(
ID NUMBER PRIMARY KEY,
Type varchar(20),
Firstname varchar(20),
Lastname varchar(20),
Address varchar(20),
City varchar(20)
);

INVENTORY TABLE

create table INVENTORY(
ID NUMBER PRIMARY KEY,
ItemName varchar(20),
Type varchar(20),
MadeInStore varchar(20),
SupplierName varchar(20),
DailyAverageSold varchar(20),
Price number
);

ORDER TABLE

create table ORDERS(
ID NUMBER PRIMARY KEY,
Quantity number,
DeliveryDate date,
Customer_FK NUMBER,
Item_FK NUMBER,
FOREIGN KEY (Customer_FK) REFERENCES CUSTOMER(ID),
FOREIGN KEY (Item_FK) REFERENCES INVENTORY(ID)
);

Queries:

11.

SELECT ORDER.id,ORDER.deliverydate,INVENTORYS.itemname,ORDER.quantity FROM ORDERS ORDER INNER JOIN ORDERS ORDER ON INVENTORY INVENTORYS ON ORDERS.item_fk=INVENTORY.id WHERE ORDERS.quntity >= 10;

12.

SELECT c.firstname,c.lastname,I.itemname,sum(o.quantity ) as quAntity from customer c inner join orders o on c.id=o.customer_fk INNER join inventory i on o.item_fk=i.id group by c.lastname,c.firstname,I.itemname

13.

SELECT o.id,c.lastname,o.quantity,(o.quantity*i.price) As partialamount from customer c INNER JOIN orders o on c.id=o.customer_fk INNER JOIN inventory i on o.item_fk=I.id group by o.id

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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