Create and query databases using SQL

Value:20%

Submission method options

Alternative submission method

Task

Part A: SQL Queries(50 marks)

Use the ClassicModelsdatabase (ClassicModels.sql) from the ITC114 Interact Resources site. Answer the following queries from that database.

You are required to submit:

  1. The appropriate SQL statements for each query, which should be copied from your SQL code in MySQL and pasted into your submission file; and
  2. 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:

  1. 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
  2. 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)
  3. 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)
  4. Which customers cancelled orders, how many different products were in the order and what was the reason for cancellation? (8 marks)
  5. 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)
  6. 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)
  7. 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:

  1. Write the SQL DDL to create the database that contains each of the relations shown in the above ERD. You will need to provide:
  2. Your DDL code for each table that you create; (20 marks)
  3. A screenshot showing each table that is created. (10 marks)
  4. Write and execute SQL definition commands for each of the following queries:
  5. Add the attributes Email, Comments and Send Newsletter to the Renters table. Give those attributes an appropriate size and data type (3 marks).
  6. Change the attribute Renters.renterDOB from type Varchar(15) to type DATE. (2 marks)
  7. Write and execute SQL commands for the following:
  8. Add the following customers to the Renters table. (3 marks)
  9. Write a command that will remove the Jones that lives in Orange from the Renters table (2 marks)

id_renters / renter_lname / renter_fname / renter_address / renter_town / renter_state / renter_pcode / renter_phone / renter_dob
12345 / Jones / George / 123 Bentinck St / Bathurst / NSW / 2795 / 0212345678 / 01/05/1964
23456 / Jones / Pauline / 48 Summer St / Orange / NSW / 2800 / 0223456789 / 11/09/1986
28765 / Wilson / Paul / 23 Long St / Blayney / NSW / 2799 / 0298745621 / 22/12/1977

You are required to submit:

  1. The appropriate SQL command which should be copied from your source code in MySQL and pasted into your submission file; and
  2. The resultant tables, which must be screenshots to show the change due to the execution of the SQL commands.

Typing or manually drawing the results willNOTbe accepted.

Online submission via Turnitin is required for this assignment.

Rationale

This assignment will assess your knowledge and ability to use SQL to create database tables. It will also test your ability to write both simple and complex queries given a set of results that are required and to the run those queries to return the requested data.

Marking criteria

The following criteria will be used to assess Assignment 4:

Question / High Distinction / Distinction / Credit / Pass / Fail
Part A, Q1 / Accurate and elegant SQL that answers all parts of the question / Good SQL that answers >85% but <100% of the question / SQL that answers >75% but < 85% of the question / SQL that answers >50% but < 75% of the question / Incomplete or irrelevant SQL that answers <50% of the question
Part A, Q2 / Accurate and elegant SQL that answers all parts of the question / Good SQL that answers >85% but <100% of the question / SQL that answers >75% but < 85% of the question / SQL that answers >50% but < 75% of the question / Incomplete or irrelevant SQL that answers <50% of the question
Part A, Q3 / Accurate and elegant SQL that answers all parts of the question / Good SQL that answers >85% but <100% of the question / SQL that answers >75% but < 85% of the question / SQL that answers >50% but < 75% of the question / Incomplete or irrelevant SQL that answers <50% of the question
Part A, Q4 / Accurate and elegant SQL that answers all parts of the question / Good SQL that answers >85% but <100% of the question / SQL that answers >75% but < 85% of the question / SQL that answers >50% but < 75% of the question / Incomplete or irrelevant SQL that answers <50% of the question
Part A, Q5 / Accurate and elegant SQL that answers all parts of the question / Good SQL that answers >85% but <100% of the question / SQL that answers >75% but < 85% of the question / SQL that answers >50% but < 75% of the question / Incomplete or irrelevant SQL that answers <50% of the question
Part A, Q6 / Accurate and elegant SQL that answers all parts of the question / Good SQL that answers >85% but <100% of the question / SQL that answers >75% but < 85% of the question / SQL that answers >50% but < 75% of the question / Incomplete or irrelevant SQL that answers <50% of the question
Part A, Q7 / Accurate and elegant SQL that answers all parts of the question / Good SQL that answers >85% but <100% of the question / SQL that answers >75% but < 85% of the question / SQL that answers >50% but < 75% of the question / Incomplete or irrelevant SQL that answers <50% of the question
Part B, Q1 / Accurate and elegant SQL that describes each table accurately with complete diagrams / Accurate SQL that describes each table with >85% but <100% accuracy with complete diagrams / SQL that describes each table with >75% but <85% accuracy with diagrams / SQL that describes each table with >50% but <75% accuracy with diagrams / SQL that describes each table with <50% accuracyORdoes not supply diagrams
Part B, Q2 / Accurate and elegant SQL that answers all parts of the question / Good SQL that answers >85% but <100% of the question / SQL that answers >75% but < 85% of the question / SQL that answers >50% but < 75% of the question / Incomplete or irrelevant SQL that answers <50% of the question
Part B, Q3 / Accurate and elegant SQL that answers all parts of the question / Good SQL that answers >85% but <100% of the question / SQL that answers >75% but < 85% of the question / SQL that answers >50% but < 75% of the question / Incomplete or irrelevant SQL that answers <50% of the question