Print the suppliers’ IDs and suppliers’ Names of suppliers who do not supply Tofu (i.e., if a supplier supplies Tofu, his id and name should not be in the result set).

Question 2: Print all the suppliers’ IDs, and suppliers’ Names who supply a product with a price greater than 30 and an order Quantity greater than 10. Write this query as a flat select statement.

Question 3: Write the same query in Q2 as nested select statement (i.e., use subqueries to avoid all JOIN clauses and JOIN conditions).

Question 4: Print the products’ IDs and average Quantities for products that are ordered with an average quantity greater than the average quantity ordered from product 5.

Question 5: Write an equivalent query for the following query:

SELECT orderid

FROM ORDERDETAILS WHERE EXISTS ( SELECT *

FROM PRODUCTS

WHERE PRODUCTS.productid = ORDERDETAILS.productid AND EXISTS ( SELECT *

FROM SUPPLIERS

WHERE SUPPLIERS.supplierid = PRODUCTS.supplierid

AND SUPPLIERS.country = 'UK' ));

Note: an equivalent query achieves the same semantic and results of the original query.

 

Transcribed Image Text:

EMPLOYEES PK EmployeelD LastName FirstName BirthDate Photo Notes ORDERS PK OrderiD FK1 CustomerlD FK2 EmployeelD FK3 ShipperlD OrderDate SHIPPERS PK ShippertD ShipperName Phone CUSTOMERS CustomerlD CustomerName ContactName Address City PK PostalCode Country H+ UREVER Database ORDERDETAILS PK OrderDetaillD FK1 OrderID FK2 ProductID Quantity SUPPLIERS PK SupplierID CATEGORIES PK CategoryID SupplierName ContactName HO Address City CategoryName Description PRODUCTS PK ProductID ProductName FK1 CategoryID HFK2 Supplier Unit Price Table name Customers Categories Employees Order Details Orders Products Shippers Suppliers Records 91 8 10 518 196 77 3 29 EMPLOYEES PK EmployeelD LastName FirstName BirthDate Photo Notes ORDERS PK OrderiD FK1 CustomerlD FK2 EmployeelD FK3 ShipperlD OrderDate SHIPPERS PK ShippertD ShipperName Phone CUSTOMERS CustomerlD CustomerName ContactName Address City PK PostalCode Country H+ UREVER Database ORDERDETAILS PK OrderDetaillD FK1 OrderID FK2 ProductID Quantity SUPPLIERS PK SupplierID CATEGORIES PK CategoryID SupplierName ContactName HO Address City CategoryName Description PRODUCTS PK ProductID ProductName FK1 CategoryID HFK2 Supplier Unit Price Table name Customers Categories Employees Order Details Orders Products Shippers Suppliers Records 91 8 10 518 196 77 3 29

Answer rating: 100% (QA)

To achieve this you would typically use a SQL query if youre working with a relational databa... View the full answer