DBS311 Assignment 1 solution

Original price was: $35.00.Current price is: $30.00.

Category: Tags: ,


5/5 - (3 votes)

Your submission will be a single text-based SQL file (.sql)

Your .sql file needs to contain appropriate header and commenting.

Please ensure your file runs when the entire file is executed in SQL Developer.

Create a new Worksheet in SQL Developer.

Save the file as: A1<Lastname><Firstname>.sql

Your tables should match the tables we have been working with. So if I run your statement, I should get the same result with my tables.

For each question, the columns’ title and the format of the output result must match the sample result given in that question.

Your submission will be an email with the SQL file as an attachment.

Your subject header would be   A1<Lastname><Firstname>

Due Date: Thursday Feb 23 at 9:00 AM

Late penalty: 10% per day. An email coming in at 9:01 AM is considered one day late.

Assignment Marking Scheme


Task Marks
Comments used,

instructions followed



Question 1 10
Question 2 5
Question 3 5
Question 4 10
Question 5 10
Question 6 5
Total 50



  1. Provide two statements.
  2. Looking at CUSTOMERS and ORDERS, supply the names of customers who have never made an order according to the current tables. (10 marks)
  3. Supply the count of customers who have never made an order in our current tables.
  4. Display the total number of orders per month from the ORDERS table. Show this in month order.

(5 marks)

  1. Show how many customers have made an order. (5 marks)



  1. Produce the following output based on CUSTOMERS, ORDERS and ORDERDETAILS. (10 marks)


If your average is off, remember we changed PRICEEACH from 195.33 to 214.30 for ORDERNUMBER 10185 and PRODUCTNUMBER S12_1108 in lab 3.

  1. Show all the customer names and their order total for everyone who had an order total more than $58,000 based on CUSTOMERS, ORDERS and ORDERDETAILS. (10 marks)
  2. Show me something interesting based on two or three of the ORDERS, ORDERDETAIL and CUSTOMER tables.

(5 marks)

You should have comments on what you are showing me

The statement should  illustrate something covered in the first half and not be a simple one, for example –


Your statement should not be an exact copy of something done in the lab

Your statement should be unique – no collaberation with any other students and if you put effort into it, probably no one else in the class came up with the same idea as you