26. Refer the given tables and answer to the following questions. 1 TABLE :SUPPLIER Sup_ID S1024...

 

Transcribed Image Text:

26. Refer the given tables and answer to the following questions. 1 TABLE :SUPPLIER Sup_ID S1024 S1025 S2012 S2055 Sup_Name LULU LULU Pro_ID P1220 P1222 P1223 P1224 CARREFOUR SPAR TABLE:PRODUCT Pro_Name BUTTER MILK MUFFINS CAKE City MUSCAT BARKA BARKA MUSCAT Category DAIRY DAIRY BREAD BREAD Sup_Date 20-05-2020 01-01-2021 15-12-2019 28-09-2020 Price 50.555 20.500 35.750 25.255 Sup_ID S1024 S1024 S2012 S2055 a. Write an SQL query to display Product ID. Product Name and Number of years between sysdate and Sup Date. [Hint: Use FULL OUTER JOIN ] (2.5 Marks) b. Write an SQL query to display Sup_Name, Pro_Name from the tables SUPPLIER, PRODUCT where the Sup_date is 01-01-2021 or City is Barka. (use Natural Join) (2.5 Marks) c. Write an SQL query to display category, count price for each category where the maximum price for each group is less than 50. (2.5 Marks) d. Write an SQL query to display Sup_Name and Sup_Date of suppliers for the Product category Bread with price 25.255. [Hint: Use Subquery]. (2.5 Marks) 26. Refer the given tables and answer to the following questions. 1 TABLE :SUPPLIER Sup_ID S1024 S1025 S2012 S2055 Sup_Name LULU LULU Pro_ID P1220 P1222 P1223 P1224 CARREFOUR SPAR TABLE:PRODUCT Pro_Name BUTTER MILK MUFFINS CAKE City MUSCAT BARKA BARKA MUSCAT Category DAIRY DAIRY BREAD BREAD Sup_Date 20-05-2020 01-01-2021 15-12-2019 28-09-2020 Price 50.555 20.500 35.750 25.255 Sup_ID S1024 S1024 S2012 S2055 a. Write an SQL query to display Product ID. Product Name and Number of years between sysdate and Sup Date. [Hint: Use FULL OUTER JOIN ] (2.5 Marks) b. Write an SQL query to display Sup_Name, Pro_Name from the tables SUPPLIER, PRODUCT where the Sup_date is 01-01-2021 or City is Barka. (use Natural Join) (2.5 Marks) c. Write an SQL query to display category, count price for each category where the maximum price for each group is less than 50. (2.5 Marks) d. Write an SQL query to display Sup_Name and Sup_Date of suppliers for the Product category Bread with price 25.255. [Hint: Use Subquery]. (2.5 Marks)

Answer rating: 100% (QA)

a Write an SQL query to display Product ID Product Name and Number of years between sysdate and Sup ... View the full answer