| 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.5 - Another 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.
# MaxOrder.sql:
OK.
# With derived table:
SELECT customerNumber
, customerName
, orderDate
, MAX(orderTotal) AS MaxOrderTotal
FROM
(
Select SUM(quantityOrdered * priceEach) AS orderTotal, customers.customerNumber, orderDate, customerName
FROM orderdetails
JOIN orders ON orders.orderNumber = orderdetails.orderNumber
JOIN customers ON orders.customerNumber = customers.customerNumber
Group BY orders.orderNumber
ORDER BY customerName, orderTotal DESC
) AS ot
GROUP BY customerNumber
Order BY customerName
;
-- OR --
# With CTE
WITH OrderTotals AS (
SELECT
o.customerNumber,
o.orderDate,
SUM(od.quantityOrdered * od.priceEach) AS OrderTotal
FROM
orders o
JOIN
orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY
o.customerNumber, o.orderDate
)
SELECT
c.customerNumber,
c.customerName,
ot.orderDate,
ot.OrderTotal AS MaxOrderTotal
FROM
customers c
JOIN
OrderTotals ot ON c.customerNumber = ot.customerNumber
WHERE
ot.OrderTotal = (
SELECT MAX(OrderTotal)
FROM OrderTotals
WHERE customerNumber = ot.customerNumber
)
ORDER BY
c.customerName
;
-- OR --
#!!!!!!!!!!!!!!!!!!!!!!! DOES NOT WORK !!!!!!!!!!!!!!!!!!!!!!!
# Without derived table:
SELECT customers.customerNumber
, customerName
, orderDate
, ( SELECT ROUND(SUM(quantityOrdered*priceEach),2) AS TotalPrice
FROM orderdetails
JOIN orders o ON o.orderNumber = orderdetails.orderNumber
WHERE customerNumber = customers.customerNumber
GROUP BY o.orderNumber
ORDER BY TotalPrice DESC
LIMIT 1
) AS MaxOrderTotal
FROM orders
JOIN customers ON customers.customerNumber = orders.customerNumber
GROUP BY customers.customerNumber
ORDER BY customerName
;
-1 Work submitted incorrectly.
You submission was not in a Lab05.5 folder.
-1 Work submitted incorrectly.
Your folder was misnamed `LAB06` instead of `Lab06.5`.
MaxOrder.sql:
-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 return the result required.
You return the customerNumber twice.
You do not return a row for each customer.
-8 Your query does not return the correct values required by the lab.
-8 Your query does not return the orderDate of the order with the largest total, nor does it return the correct largest (maximum) total.
Instead it returns the orderDate of their first order and then a total of all orders placed.
I suggest you need to use a sub-query in your FROM clause that creates a derived table that returns an order total for each customer order.
Then you can select the MAX of that order total in your query.
-8 Your query does not return results for ONLY the largest order for a customer.
The MaxOrderTotal amount is always correct but you return a row for each customer order.
-8 You have the same problems here.
The order total is not calculated and displayed as MaxOrderTotal.
I suggest you need to use a sub-query in your FROM clause that creates a derived table that returns an order total for each customer order.
Then you can select the MAX of that order total in your query.
-8 Your query does not give the MaxOrderTotal for the largest customer order.
Instead you are returning the largest price of items ordered in any order.
There should only be one row returned for each customer, with the order date and the total from the largest order.
I suggest you need to use a sub-query in your FROM clause that creates a derived table that returns an order total for each customer order.
Then you can select the MAX of that order total in your query.
-8 Your query does not give the MaxOrderTotal for the largest customer order.
Instead you are returning the total of all customer orders.
I suggest you need to use a sub-query in your FROM clause that creates a derived table that returns an order total for each customer order.
Then you can select the MAX of that order total in your query.
-8 Your query does not give the MaxOrderTotal for the largest customer order.
Instead you are returning the total of their first order.
-8 Your query does not give the MaxOrderTotal for the largest customer order.
Instead you are returning the largest price of items ordered in any customer order.
I suggest you need to use a sub-query in your FROM clause that creates a derived table that returns an order total for each customer order.
Then you can select the MAX of that order total in your query.
-8 Your query does not give the MaxOrderTotal for the largest customer order.
Instead you are returning the largest payment made. This is sometimes equal to the largest order, but not always.
See customer 114. The largest order was 45864.03 and your query returned 82261.22.
I suggest your sub-query in your FROM clause returns an order total for each customer order.
You should not use the payments table at all.
-8 Your query does not give the MaxOrderTotal for the largest 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 largest order was 45864.03 and your query returned 82261.22.
I suggest your sub-query in your FROM clause returns an order total for each customer order.
Then you can select the MAX of that order total in your main query.
You should not use the payments table at all.
max
-5 Your query does not return the orderDate of the order with the largest total.
Instead it returns the maximum orderDate of all their orders.
Look at the orderDate for Alpha Cognac.
You return an orderDate of 2005-03-28 but their largest order was placed on 2003-11-08.
Remember that customerNumber, customerName, and orderDate will be the first entry returned by the derived table.
You need to order the derived table so the first order returned has the largest total.
first
-5 Your query does not return the orderDate of the order with the largest total.
Instead it returns the orderDate of their first order.
Look at the orderDate for Alpha Cognac.
You return an orderDate of 2003-07-04 but their largest order was placed on 2003-11-08.
Remember that customerNumber, customerName, and orderDate will be the first entry returned by the derived table.
You need to order the derived table so the first order returned has the largest total.
last
-5 Your query does not return the orderDate of the order with the largest total.
Instead it returns the orderDate of their last order.
Look at the orderDate for Alpha Cognac.
You return an orderDate of 2005-03-28 but their largest order was placed on 2003-11-08.
Remember that customerNumber, customerName, and orderDate will be the first entry returned by the derived table.
You need to order the derived table so the first order returned has the largest total.
-5 Your query does not return the orderDate of the order with the largest total.
Instead it returns the orderDate of their last order.
Look at the orderDate for Alpha Cognac.
You return an orderDate of 2003-07-04 but their largest order was placed on 2003-11-08.
I doubt you can make this work without using a derived table.
-5 Your query does not give the OrderTotal for the largest customer order.
Instead you are returning the largest quantity of items ordered in any order.
I suggest you need to use a sub-query in your FROM clause that creates a derived table that returns an order total for each customer order.
Then you can select the MAX of that order total in your query.
-2 Your IbrahimDahir_Lab06_LastOrder.sql file is empty.
-1 You forgot to alias your MAX as MaxOrderTotal.
-1 Your StudentName-Lab05-MaxOrder.sql file does not contain the query you show in your AnnabelleSolomon-Lab05-MaxOrder.png screenshot.
The query I see in the screenshot is the correct one.
-15 Neither query runs at all on my system. The orderdetails table has no field named subtotal.
# LastOrder.sql
Note that the query shown does not return only one result per customer, nor does it return the date or total cost of the last order placed by a customer.
# MaxOrder.sql
Note that the query shown does not return only one result per customer, nor does it return the date or total cost of the largest order placed by a customer.
0 points
Note that both of these queries usually require the use of one or two sub-queries to get the correct results.
=e
Well Done. Nice use of a CTE.