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
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)
Charles Sturt University Subject Outline
ITC114 201430 S I-12 February 2014-Version 1 Page 14 of 24
4. 5. 6.
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)