Scenario Question(s) Question Transforming Nicky Motors database Below is data extracted from the dealership's products table...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 Müller 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 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 Müller 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
Expert Answer:
Answer rating: 100% (QA)
It seems you are being asked to write SQL queries for a series of questions Given the images include the questions but do not show an attempt to write ... View the full answer
Related Book For
Modern Database Management
ISBN: 978-0133544619
12th edition
Authors: Jeff Hoffer, Ramesh Venkataraman, Heikki Topi
Posted Date:
Students also viewed these programming questions
-
You are going to put an "equal amount" of money, every year, into a saving account that earns 3% interest per year. Your goal is to accumulate $50,000 in exactly 10 years. If you make your first...
-
Your release of information section has developed a one-month backlog in responding to routine requests. The employees say they need another photocopy machine because the one they have is always...
-
Use the energy method to determine the equations of motion and the natural frequencies of the systems shown in the following figures: k www 30 111 m
-
One type of systematic error arises because people tend to think of benefits in percentage terms rather than in absolute dollar amounts. As an example, Samir is willing to drive 20 minutes out of his...
-
The three power lines exert the forces shown on the truss joints, which in turn are pin-connected to the poles AH and EG. Determine the force in the guy cable AI and the pin reaction at the support...
-
4. The water in Earth's atmosphere blocks most of the infrared waves coming from space. In order to observe light of this wavelength, the Kuiper Airborne Observatory has been developed. The...
-
Consider the National Football League data in Table B.1. a. Use the forward selection algorithm to select a subset regression model. b. Use the backward elimination algorithm to select a subset...
-
Herb E. Vore is considering investing in a Salad Stop franchise that will require an initial outlay of $100,000. He conducted market research and found that after tax cash flows on this investment...
-
Hawkeye Inc. has signed a building contract with a customer. The contract price is $1,800,000, and construction costs are estimated to be $1,200,000. The actual costs and estimated future costs by...
-
Three entrepreneurs were looking to start a new brewpub near Sacramento, California, called Roseville Brewing Company (RBC). Brewpubs provide two products to customersfood from the restaurant segment...
-
Determining organizational priorities and needs for projects is important to project success factors. How do equity and SDOH fit in with your project Patient-Centered Communication Share a time when...
-
The mean finish time for a yearly amateur auto race was 185.13 minutes with a standard deviation of 0.353 minute. The winning car, driven by Perry, finished in 184.77 minutes. The previous years race...
-
Carriage inwards is charged to the trading account because (A) It is an expense connected with buying goods (B) It should not go in the statement of financial position (C) It is not part of motor...
-
log 2 5 = k Change each logarithmic expression to an equivalent expression involving an exponent.
-
IQ Test Scores in an IQ test have a bell-shaped distribution with a mean of 100 and a standard deviation of 19. (a) What percentage of people has an IQ score between 62 and 138? (b) What percentage...
-
Clarissa has just completed her second semester in college. She earned a grade of B in her 4-hour linear algebra course, a grade A in her 2-hour government course, a grade of D in her 3-hour physics...
-
CHCMHS001 - Final Assessment A Define the key issues of prejudice and discriminationand explain how they impact on people with mental healthneeds. What is the principle of recovery as it relates to...
-
A stock has had returns of 8 percent, 26 percent, 14 percent, 17 percent, 31 percent, and 1 percent over the last six years. What are the arithmetic and geometric average returns for the stock?
-
Explain the most common approach used to handle slowly changing dimensions.
-
Document your thought process around what changes you made to the model developed in Chapter 2, 2-60, to accommodate the new information. Pay particular attention to what changes you had to make to...
-
Fitchwood is a relatively small company (annual premium revenues less than $1B per year) that insures slightly more than 500,000 automobiles and about 200,000 homes. For what types of purposes might...
-
Name some characteristics that allow you to observe light diffraction.
-
Planar waves from a monochromatic light source are normally incident on a circular obstacle, which casts a shadow on a screen positioned behind the obstacle. What do the wave properties of light...
-
Which wavelength of electromagnetic radiation do you expect to be diffracted by a window screen? What is the frequency of this radiation?
Study smarter with the SolutionInn App