Scenario Question(s) Question Transforming Nicky Motors database Below is data extracted from the dealership's products table |Stock Number | Make F 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 Ford Toyota | Tesla |Chevrolet | Honda 151 [$2 153 154 BMW | Audi | Nissan Mercedes | Subaru Model |F-150 | Camry | Model 3 | Silverado | Civic 13 Series |A4 Altima IC-Class [Outback Below is data extracted from the dealership's supplier table Supplier ID | Supplier Name | American Autos | Asian Car Masters | Electric Innovations European Luxury Year Color | Mileage 12022 Blue 10 12021 12023 White 10 12020 Red 130.000 2019 Silver 120.000 12022 Black 10 12021 | White 15,000 12018 |Blue 150.000 12022 Grey 10 12020 | Green 125.000 | Country | USA Japan | USA | Black 112.000 Germany Contact John Smith Yuki Tanaka Transmission PRG522-Formative Assessment 3 Semester 2 Paper 2023 | VID 100 Marks Automatic 500,000 Manual 280.000 Automatic 1450,000 Automatic 1350.000 Manual 180,000 Automatic 1550,000 Automatic 1400,000 Manual 150,000 Automatic 1600.000 Manual 1270.000 1555-1234 1555-5678 (555-9101 Emily Johnson Hans Mller 1555-1122 Price (R) Phone Number] Stock Number 1001, 004, 010 1002, 005,008 1003 1006, 007, 009 Question 1 This task includes preparing the data (2 Marks) 1.1 On Oracle, write SQL statements to create a database for Nicky Motors called "dealership" 12 Write SQL statements that will recreate the product table for Nicky Motors, this table should include an additional column called "Supplier ID which has a foreign key and the data can not be null. 13 Write SQL statements that will recreate the supplier table for Nicky Motors on Oracle. 14 Write SQL statements that will flood both tables with the data as shown above (9 Marks) (6 Marks) (8 Marks) [Total = 25 Marks] Question 2 This task includes learning unit 9: Using subqueries to solve queries. Write SQL statements to perform the following subqueries: 21 Display the price of a product if ANY records on the supplier table are from the USA (7 Marks) 22 Display the make of a product if ANY records on the supplier table are supplied by Electric Innovations (7 Marks) Page 3 of 4 (7 Marks) 2.3 Display the contact of the supplier if ANY records on the products table have a price greater than 500,000 2.4 Display the phone number of the supplier if ALL records on the products table have mileage equal to zero (4 Marks) [Total = 25 Marks] Question 3 This task includes learning unit 10: Managing Schema Objects. Write SQL statements to perform the following Views: 3.1 Create a simple view that shows all the columns from the Car Dealership Products Table. 3.2 Create a view that shows all cars with automatic transmission and their details. (3 Marks) (4 Marks) (6 Marks) 3.3 Create a view that lists all cars supplied by "American Autos" with their details. 3.4 Create a complex view that joins the Car Dealership Products Table and the Supplier Table to show the Make, Model, Year, and Supplier Name. 3.5 Create a view that lists cars from the year 2022 and their supplier details. (6 Marks) (6 Marks) [Total = 25 Marks] Question 4 This task includes learning unit 11: Using the Set Operators. Write SQL statements to use Set Operator to Combine Multiple Queries into a Single Query: 4.1 List all unique car makes available in the Car Dealership Products Table and Supplier countries in a single column. (4 Marks) (5 Marks) (8 Marks) (8 Marks) [Total = 25 Marks] 4.2 List cars with Automatic transmissions and cars from the year 2022. Eliminate duplicate rows. 4.3 Find car models that are both supplied by "American Autos" and have Automatic transmissions. 4.4 List car models that are supplied by "American Autos" but do not have Automatic transmissions. Submission: Students should submit the following: SQL script containing the database schema creation, including tables, relationships, and constraints. SQL queries addressing the requirements for each learning unit. End of Formative Assessment 3 Grand Total: 100