Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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,

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)

 

 

image.png 

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.

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

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 ... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Artificial Intelligence Structures And Strategies For Complex Problem Solving

Authors: George Luger

6th Edition

0321545893, 9780321545893

More Books

Students also viewed these Operating System questions