- Part A: SQL Queries (50 marks)
Use the ClassicModels database (ClassicModels.sql) from the ITC114 Interact Resources site. Answer the following queries from that database.
You are required to submit:
The appropriate SQL statements for each query, which should be copied from your SQL code in MySQL and pasted into your submission file; and
Screenshots of the resultant tables which are to be pasted into your submission file immediately after the SQL code for that query. Typing or manually drawing the results is NOT acceptable.
NOTE: Your SQL code should be correct for any data, not just any sample data that you create.
Write SQL SELECT statements to retrieve the following information from the ClassicModels database:
Select the product name, scale, vendor and product code for all models of classic cars built between 1948 and 1966. Display the results in order of manufacture. (5 marks
Select the product name, scale, vendor and product code for all models of Vintage cars built before 1930 and with a scale smaller than 1:20. Display the results in order of manufacture. (5 marks)
List the South East Asian (excluding Australian and New Zealand) customers of Classic Models. Show their Customer name, city, country, sales Rep name, email address and the sales office that supports them. Order the list by country and city. (5 marks)
Which customers cancelled orders, how many different products were in the order and what was the reason for cancellation? (8 marks)
Which employees work in a European office? List their office, their full name, their job title, the name and job title of the person that they report to. (7 marks)
Which orders over $30,000 in value have been disputed and then later resolved? Who were the customers involved; how many different products were in their order; what was the total value of the disputed order? What was the resolution of the problem? (12 marks)
How many models of the Titanic have been shipped? What was the date of the first shipment and when was the last shipment sent? (8 marks)
Part B. Create Tables using SQL DDL (40 marks)
The following questions are based on the Bicycle Rental ERD as shown below:
Tasks:
Write the SQL DDL to create the database that contains each of the relations shown in the above ERD. You will need to provide:
Your DDL code for each table that you create; (20 marks)
A screenshot showing each table that is created. (10 marks)
Write and execute SQL definition commands for each of the following queries:
Add the attributes Email, Comments and Send Newsletter to the Renters table. Give those attributes an appropriate size and data type (3 marks).
Change the attribute Renters.renterDOB from type Varchar(15) to type DATE. (2 marks)
Write and execute SQL commands for the following:
Add the following customers to the Renters table. (3 marks)
Write a command that will remove the Jones that lives in Orange from the Renters table (2 marks)
