Given the database description below: 

 

Tb_Supplier(Supp_ID, Name, City, State) 

Tb_Consumer(Con_ID, Name, City, State) 

Tb_Product(Prod_ID, Name, Product_Category, Product_Line, Product_Packaging) 

Tb_Offers(Supp_ID, Prod_ID, Quantity, Price) 

Tb_Requests(Con_ID, Prod_ID, Quantity, Price) 

Tb_Transactions(Tran_ID, Supp_ID, Con_ID, Prod_ID, Quantity, Price) 

Tb_Date(DateKey, DateYear, DateMonth, DateMonthName, DateWeek, DateDayOfMonth, DateDayOfWeek, FiscalWeek, IsWeekEnd, IsHoliday, HolidayName) 

Tb_Time(TimeKey, TheHour, IsLunchHour, IsBusinessHour, ShiftNumber)

 

 

 

Solve the following queries:

 

1) List full transaction data relating to suppliers from Madison and consumers from Stevens Point where transaction value is higher than $10,000 (show supplier, consumer and product names, quantity and price)? 

2. Name of suppliers offering both computers and oranges? (do not use set operations) 

3. Name of suppliers from Wausau or offering computers or offering oranges? 

4. Name of suppliers offering computer, auto and orange? 

5. Name of products not offered in Chicago? 

6. Name of consumers requesting only computers? 

7. Name of supplier cities where none of the suppliers has any offer? 

8. Name of products requested by all consumers? 

9. Product name and supplier having the largest offer (as quantity) for that product? 

10. Product name and city where that product sold best, as in largest total quantity? 

 

(Extra Credit 2%) Name of products requested in all consumer cities other than Stevens Point? 

 

Return: 

Please post your solutions to the queries above in properly formatted SQL syntax: 

- each SQL clause on its own line; 

- capitalize keywords and operators; 

- precede each SQL query by a comment line that contains the query number and the wording of the query as listed above. 

 

Submit all queries in one single .sql file.

Transcribed Image Text:

Represented by the diagram: Tb Supplier Supp ID Name City State Tb_Transactions TranJD Supp ID Con ID Prod ID Price Quantity Tb_Date DateKey Date Year DateMonth DateMonthName DateWeek Date DayOfMonth DateDayOfWeek Fisca Week IsWeekEnd IsHoliday HolidayName Tb Consumer Con ID Name City State Tb Requests ConID Prod ID Price Quantity Tb Offers oo DateKey Supp ID po TimeKey oo Prod ID co Price Tb Time TimeKey TheHour Quantity Tb_Product ProdID Name Product Category Product Line Product Packaging IsLunchHour IsBusiness Hour Shift Number Represented by the diagram: Tb Supplier Supp ID Name City State Tb_Transactions TranJD Supp ID Con ID Prod ID Price Quantity Tb_Date DateKey Date Year DateMonth DateMonthName DateWeek Date DayOfMonth DateDayOfWeek Fisca Week IsWeekEnd IsHoliday HolidayName Tb Consumer Con ID Name City State Tb Requests ConID Prod ID Price Quantity Tb Offers oo DateKey Supp ID po TimeKey oo Prod ID co Price Tb Time TimeKey TheHour Quantity Tb_Product ProdID Name Product Category Product Line Product Packaging IsLunchHour IsBusiness Hour Shift Number

Answer rating: 100% (QA)

Solutions Source code for mainsql Option Problem 1 a Based on the tables in the database given by the description below TbSupplierSuppID Name City State TbConsumerConID Name City State TbProductProdID ... View the full answer