supplier (SID, sName, address, discount) 1 produce (pID,pName, type, price, supp

Question

supplier (SID, sName, address, discount) 1 produce (pID,pName, type, price, supplierID) branch (bID, address, managerName) orders (OID, produceID, qty, oDate, branchID) Details held on suppliers in the supplier table are a unique ID (sID), name, addres and the discount they give to the fast food restaurant (a percentage discount on each order). Each supplier supplies multiple produce but each unique produce is only supplied by one supplier. The produce table contains a unique ID per produce (pID), the name of the produce (e.g., chips, burger buns, etc.), the type of produce (e.g., fresh, frozen, beverage, etc.), the price of the produce per unit (e.g. price per box of salt portions) and the ID of the supplier of the produce (supplierID) which is a foreigrn key to sID in the supplier table. Details held on branches in the branch table are a unique branch ID (bID), branch address and the name of the branch manager. The orders table holds details of each order where an order may comprise multiple produce. Each entry has an order ID (oID), produce ID (produceID) and the branch ID (branchID) which is a foreign key to bID in the branch table. In addition, the quantity of the produce required is stored (qty) and the order date (oDate). The attribute produceID is a foreign key to pID in the produce table COMPULSORY: Using the order relational schema, write SQL queries to satisfy the following information needs: Choosing suitable data types, write the SQL code required to create the orders table. (4) List the price of the produce 'Iceberg lettuce. (4) Using an implicit join syntax, list the name and type of produce supplied by the supplier 'Mc Cambridge's'. (4) Using an explicit join syntax, list the name and quantity of all produce ordered by the branch located in Salthill. (4) List the details of the supplier who offers the highest discount. (4) (a) (b) (c) (d) (e) (f) Using a subquery approach, find the number of times the produce (g) For all suppliers, list the supplier details and the number of produce (h) Find the total cost of orders, before any supplier discount is applied, (i) List the branch address and manager for all branches that have less G) List the names of suppliers who are not involved in any orders. (4) Tomato Ketchup Portions' was part of an order. (4) each provide. (4) made by the branch located in ‘Oranmore". (4) than 10 orders for fresh produce. (4)

Explanation / Answer

Hi,
Answering the 1st 4 sub parts as per chegg policy, please post others as separate question.

1. the create table query is like below

CREATE TABLE CUSTOMERS(oID INT,

produceID INT,

qtyINT,
oDate date,

branchID INT ,

PRIMARY KEY (oID,produceID),-- combination of these both forms primary key

FOREIGN KEY (branchID ) REFERENCES branch(bID )-- foregin key constraint on branch

);
2.select price from produce where pName='Iceberg Lettuce';

simple where with the given pname comparison
3. SELECT x.pName, x.type FROM produce x,supplier y
WHERE x.supplierID = y.sID and sName='Mc Cambridge''s';

this the example of using implicit join of produce and supploer table, we need to escape ' with '' i.e doubling
4.

Thumbs up if this was helpful, otherwise let me know in comments

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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