| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/nginx/html/JimMartinson/Classes/CST1600/Labs/ |
Upload File : |
Lab 6 - Perform multi-table query
Grading Criteria
Demonstrated assignment effectively: 10 points.
There are some things I expect. If they are incorrect you will lose the following:
Work submitted incorrectly: -1 pt. This covers errors such as incorrect files names, incorrect file formats, poor formatting, etc.
Second and subsequent submission of work for grading: -1 pt.
Work submitted late: -1 pt per week.
I reserve the right not to apply the deduction points at my absolute discretion.
-1 Work submitted late.
# LastOrder.sql
SELECT customers.customerNumber
, customerName
, orderDate AS LastOrderDate
, SUM(quantityOrdered * priceEach) AS OrderTotal
FROM `customers`
JOIN `orders` ON orders.customerNumber = customers.customerNumber
JOIN `orderdetails` ON orderdetails.orderNumber = orders.orderNumber
WHERE orderDate = ( SELECT MAX(orderDate)
FROM `orders` AS o
WHERE o.customerNumber = customers.customerNumber
GROUP BY o.customerNumber
)
GROUP BY customers.customerNumber
ORDER BY customerName
;
-- OR --
SELECT customers.customerNumber
, customerName
, orderDate AS LastOrderDate
, ( SELECT CAST( SUM(quantityOrdered * priceEach) AS DECIMAL(8,2) )
FROM `orderdetails` AS od
WHERE od.orderNumber = orders.orderNumber
) AS OrderTotal
FROM `customers`
JOIN `orders` ON orders.customerNumber = customers.customerNumber
WHERE orderDate = ( SELECT MAX(orderDate) AS LastOrderDate
FROM `orders` AS o
JOIN customers AS c ON c.customerNumber = o.customerNumber
WHERE c.customerNumber = customers.customerNumber
GROUP BY c.customerNumber
)
ORDER BY customerName
;
-- OR --
SELECT customers.customerNumber
, customerName
, orderDate AS LastOrderDate
, ROUND(SUM(quantityOrdered * priceEach),2) AS OrderTotal
FROM customers
JOIN orders ON orders.customerNumber = customers.customerNumber
JOIN orderdetails USING(orderNumber)
GROUP BY orderNumber
HAVING OrderTotal =
(
SELECT ROUND(SUM(quantityOrdered * priceEach),2) AS orderTotal
FROM orders o
JOIN orderdetails USING(orderNumber)
WHERE o.customerNumber = customers.customerNumber
GROUP BY orderNumber
ORDER BY orderDate DESC
LIMIT 1
)
ORDER BY customerName ASC
;
--OR--
SELECT customerNumber
, customerName
, orderDate
, MaxOrderTotal
FROM (
SELECT c.customerNumber
, c.customerName
, o.orderDate
, SUM(od.quantityOrdered * od.priceEach) AS MaxOrderTotal
, ROW_NUMBER() OVER (PARTITION BY c.customerNumber ORDER BY SUM(od.quantityOrdered * od.priceEach) DESC) AS rnk
FROM `Customers` c
INNER JOIN `orders` o ON c.customerNumber = o.customerNumber
INNER JOIN `orderDetails` od ON o.orderNumber = od.orderNumber
GROUP BY c.customerNumber, c.customerName, o.orderDate
) AS ranked_orders
WHERE rnk = 1
ORDER BY customerName
;
-1 Work submitted incorrectly.
Your folder was misnamed `LAB06` instead of `Lab06`.
# LastOrder.sql:
OK.
-10 Your query throws an error and returns no results.
Why are you turning this in when it clearly does not work?
=e
-8 Your query does not always return the correct LastOrderDate for each customer.
See customerName `Anna's Decorations, Ltd` as an example.
Do you need to link your subquery for max orderDate back to the customer from the outer query?
-8 Your query does not give the output required by the lab.
Instead you are returning the customerNumber, customerName, LastOrderDate, and OrderTotal for every order placed by a customer.
There should only be one row returned for each customer, with the last order date and its total.
I suggest you need to use one or more sub-queries.
You need to determine the last orderDate by customerNumber and only get a row that matches that orderDate.
The order total needs to be calculated for that last order and named OrderTotal.
-8 Your query does not give the output required by the lab.
It sometimes returns more than one result for a customer.
How can Anna's Decorations, Ltd have two orders with different LastOrderDates?
Your subquery to get the max orderDate is too general.
It returns one for each customer, not just for the customer we are trying to get the data for.
So, if another customer has a last order with the same date as any order by this customer you get data for that order as well.
-8 Your query does not give the OrderTotal for the last customer order.
Instead you are returning the total quantity of items ordered for all orders by the customer.
There should only be one row returned for each customer, with the last order date and its total.
I suggest you need two sub-queries:
1) In the SELECT clause get the order total where the orderNumber in this sub-query equals the orderNumber in the outer query.
2) In a WHERE clause test that the orderDate equals the last order date for the customer.
-8 Your query does not give the OrderTotal for the last customer order.
Instead you are returning the last payment made. This is sometime equal to the last order but not always.
See customer 114. The last order total was 41995.62 and your query returned 82261.22.
-8 Your query does not give the OrderTotal for the last customer order.
Instead you are returning the total for all orders by the customer.
With the group by customerNumber the sum is for all customer orders, not for each one.
You need to limit the result to only the last order.
I suggest you need to use one or more sub-queries (perhaps in a WHERE clause).
-8 Your query does not give the OrderTotal for the last customer order.
Instead you are returning the total for the first order by the customer.
You need to use a sub-query that returns the max order date.
You could use that in your WHERE clause to select only the order with the max order date.
-8 Your query does not give the 'OrderTotal' for the last customer order.
Instead you are returning the total for the first order by the customer.
What is happening is you are calculating the total from the first order and that is stored as `OrderTotal`.
Then the `LastOrderDate` is computed, but the `OrderTotal` is not re-calculated for that order.
You could add a WHERE clause and sub-query to select only the order with the max order date.
-8 Your query does not give the OrderTotal for the last customer order.
Instead it appears to give the OrderTotal for the one before that.
Please note that payments had nothing to do with their last order.
It is just a payment, which may or may not be the same as the previous order but not the last one.
-8 Your query does not return the last order date and total for each customer.
Instead it only returns data for customers who placed an order in the last order date from all customers.
I suggest you need two sub-queries:
1) In the SELECT clause get the order total where the orderNumber in this sub-query equals the orderNumber in the outer query.
2) In a WHERE clause test that the orderDate equals the last order date for the customer.
-2 Your IbrahimDahir_Lab06_LastOrder.sql file is empty.
-2 Your StudentName-Lab05-LastOrder.sql file does not contain the query from your StudentName-Lab05-LastOrder.png screenshot.
-2 Your StudentName-Lab05-LastOrder.png screenshot does not show the output from the StudentName-Lab05-LastOrder.sql script.
The total values shown do not match the output of the script.
The script is correct but the screenshot must have been taken from an earlier run before the script was corrected.
Also, it should GROUP BY customerNumber, not orderNumber. It still works because of the WHERE clause.
-1 line 8; Grouping by orderDate can be problimatic. If there are two orders on the same date they will have their totals combined.
-1 You forgot to alias your SUM as OrderTotal.
-1 You forgot to return the customerNumber.
-1 Your query does not display the results in the order requested.
The order should be customerNumber, customerName, LastOrderDate, and OrderTotal.
-1 You query has the wrong name for LastOrderDate (capitalization) and OrderTotal (you have totalOrder).
I suggest you need a sub-query:
In the WHERE clause test that the orderDate equals the last order date for the customer.
Perfect.