SQL ORACLE Problems with three queries-CodePudding

I have three tables orders, orderdetails and customers.

The orders table has these columns:

ORDERID, CUSTOMERID, EMPLOYEEID, TERRITORYID,
ORDERDATE, REQUIREDDATE, SHIPPEDDATE,
SHIPVIA, FREIGHT, SHIPNAME,
SHIPADDRESS, SHIPCITY, SHIPREGION,
SHIPPOSTALCODE, SHIPCOUNTRY

The orderdetails table has these columns:

ORDERID, PRODUCTID, UNITPRICE, QUANTITY, DISCOUNT

And the customers table has these columns:

CUSTOMERID, COMPANYNAME, CONTACTNAME,
CONTACTTITLE, ADDRESS, CITY, REGION, 
POSTALCODE, COUNTRY, PHONE, FAX

I have to do three queries:

1 List of orders with a higher total amount (unitprice * quantity * (1 - discount)) provided that the total amount is greater than 10000

I did it like this:

SELECT orderid  
FROM orders  
JOIN orderdetails USING (orderid)
GROUP BY orderid
HAVING SUM (unitprice * quantity * (1 - discount)) > 10000;

2 Order with the highest total amount (including discount)

I did it like this:

SELECT MAX (orderid),
       SUM (unitprice * quantity * (1 - discount)) as price
FROM orders  
JOIN orderdetails USING (orderid)
ORDER BY price DESC;

3 show the customerid and the freight (transport) of the customers who have spent more than 2000 freight on all their orders

I have not been able to do this

SELECT customerid,freight
FROM customers  
JOIN orders USING (customerid)
HAVING Count (freight) > 2000;

I want to know if I did #1 and #2 correctly or is there a better way to do it and how can I solve #3.

Thank you very much to all.

CodePudding user response：

You don't need the customers table (since the orders table contains both freight and customerid) and need to have a GROUP BY clause and aggregate the freight column in the SELECT clause (and probably want to use SUM rather than COUNT but you haven't provided any sample data so it is difficult to tell):

SELECT customerid,
       SUM(freight)
FROM   orders
GROUP BY
       customerid
HAVING SUM(freight) > 2000;

CodePudding user response：

With Sample data like here:

WITH 
    orders (ORDERID, CUSTOMERID, EMPLOYEEID, TERRITORYID, ORDERDATE, REQUIREDDATE, SHIPPEDDATE, SHIPVIA, FREIGHT, SHIPNAME,
            SHIPADDRESS, SHIPCITY, SHIPREGION, SHIPPOSTALCODE, SHIPCOUNTRY) AS
        (
            Select 101, 1, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A', 1500, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 102, 1, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A',  250, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 103, 1, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A',  500, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 104, 2, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A',  750, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 105, 2, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A',  950, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 106, 2, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A', 1250, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 107, 3, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A', 2500, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 108, 4, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A',  850, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 109, 4, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A',  950, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual Union All
            Select 110, 5, 11, 'T1', DATE '2023-01-03', DATE '2023-01-24', DATE '2023-01-04', 'via A', 1150, 'A SHIP', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY' From Dual 
        ),
    details (ORDERID, PRODUCTID, UNITPRICE, QUANTITY, DISCOUNT) AS
        (
            Select 101, 1001, 700, 5, 0.1 From Dual Union All
            Select 101, 1002, 700, 5, 0.1 From Dual Union All
            Select 102, 1003, 800, 2, 0.1 From Dual Union All
            Select 102, 1004, 700, 2, 0.1 From Dual Union All
            Select 103, 1003, 900, 5, 0.3 From Dual Union All
            Select 103, 1004, 700, 6, 0.1 From Dual Union All
            Select 103, 1005, 700, 7, 0.1 From Dual Union All
            Select 104, 1001, 700, 1, 0.1 From Dual Union All
            Select 104, 1002, 900, 1, 0.2 From Dual Union All
            Select 105, 1002, 900, 3, 0.1 From Dual Union All
            Select 106, 1005, 700, 1, 0.1 From Dual Union All
            Select 107, 1003, 700, 7, 0.1 From Dual Union All
            Select 108, 1004, 700, 8, 0.2 From Dual Union All
            Select 109, 1003, 900, 8, 0.1 From Dual Union All
            Select 109, 1004, 900, 9, 0.3 From Dual Union All
            Select 110, 1005, 700, 9, 0.1 From Dual 
        ),
    customers(CUSTOMERID, COMPANYNAME, CONTACTNAME, CONTACTTITLE, ADDRESS, CITY, REGION, POSTALCODE, COUNTRY, FAX) AS
        (
            Select 1, 'A COMPANY', 'A CONTACT', 'A TITLE', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY', 'A FAX' From Dual Union All
            Select 2, 'A COMPANY', 'A CONTACT', 'A TITLE', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY', 'A FAX' From Dual Union All
            Select 3, 'A COMPANY', 'A CONTACT', 'A TITLE', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY', 'A FAX' From Dual Union All
            Select 4, 'A COMPANY', 'A CONTACT', 'A TITLE', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY', 'A FAX' From Dual Union All
            Select 5, 'A COMPANY', 'A CONTACT', 'A TITLE', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY', 'A FAX' From Dual Union All
            Select 6, 'A COMPANY', 'A CONTACT', 'A TITLE', 'Address 1', 'A CITY', 'A REGION', 'A ZIP', 'A COUNTRY', 'A FAX' From Dual 
        )

your 3 answers would be like below:

--  1. 
SELECT  ORDERID, Sum(UNITPRICE * QUANTITY * (1 - DISCOUNT)) "TOTAL_10K_PLUS"
FROM    orders  
JOIN    details  USING  (ORDERID)
GROUP BY ORDERID
HAVING SUM (UNITPRICE * QUANTITY * (1 - DISCOUNT)) > 10000;
/*
   ORDERID TOTAL_10K_PLUS
---------- --------------
       109          12150 
       103          11340
*/

--  2. Changed
SELECT ORDERID, TOTAL "HIGHEST_TOTAL"
FROM
    (   SELECT o.ORDERID,
               MAX(SUM(UNITPRICE * QUANTITY * (1 - DISCOUNT))) OVER(Partition By o.ORDERID) as TOTAL
        FROM   orders o  JOIN details d  ON  (d.ORDERID = o.ORDERID)
        GROUP BY o.ORDERID
        ORDER BY TOTAL DESC
    )
WHERE ROWNUM = 1
/*
   ORDERID HIGHEST_TOTAL
---------- -------------
       109         12150
*/

--  3. Changed
SELECT  CUSTOMERID, Sum(FREIGHT) "TOTAL_FREIGHT"
FROM    orders 
GROUP BY CUSTOMERID
HAVING  Sum(FREIGHT) > 2000;
/*
CUSTOMERID TOTAL_FREIGHT
---------- -------------
         1          2250 
         2          2950 
         3          2500 
*/