XD BHOUSZ

# RENZOZOZOZOZOZOZOZOZ DE LEONEOENOENOENEOEON
# 1
SELECT
    PL.PRODUCTLINE, COUNT(P.PRODUCTNAME)
FROM
    PRODUCTLINES PL
        JOIN
    PRODUCTS P ON PL.PRODUCTLINE = P.PRODUCTLINE
GROUP BY P.PRODUCTLINE
ORDER BY COUNT(P.PRODUCTNAME) DESC;

# 2
SELECT C.CUSTOMERNAME, SUM(P.AMOUNT)
FROM CUSTOMERS C JOIN PAYMENTS P ON C.CUSTOMERNUMBER = P.CUSTOMERNUMBER
WHERE P.AMOUNT NOT LIKE 'NULL'
GROUP BY C.CUSTOMERNAME
ORDER BY SUM(P.AMOUNT) DESC;

# 3
SELECT
    *
FROM
    ORDERS
WHERE
    STATUS LIKE 'SHIPPED'
        AND REQUIREDDATE < SHIPPEDDATE;

# 4
SELECT
    O.COUNTRY, O.TERRITORY, O.OFFICECODE, E.FIRSTNAME, E.LASTNAME
FROM
    OFFICES O RIGHT JOIN EMPLOYEES E ON O.OFFICECODE = E.OFFICECODE
ORDER BY O.TERRITORY ASC;

# 5
SELECT
    OD.ORDERNUMBER, SUM(OD.QUANTITYORDERED * OD.PRICEEACH) AS 'TOTAL LOSSES'
FROM
    ORDERS O
        JOIN
    ORDERDETAILS OD ON O.ORDERNUMBER = OD.ORDERNUMBER
        JOIN
    PRODUCTS P ON OD.PRODUCTCODE = P.PRODUCTCODE
WHERE O.STATUS LIKE 'CANCELLED'
GROUP BY OD.ORDERNUMBER
ORDER BY SUM(OD.QUANTITYORDERED * OD.PRICEEACH);

# 6
SELECT
    P.PRODUCTCODE, SUM(OD.QUANTITYORDERED * OD.PRICEEACH), C.COUNTRY
FROM
    PRODUCTS P
        JOIN
    ORDERDETAILS OD ON P.PRODUCTCODE = OD.PRODUCTCODE
        JOIN
    ORDERS O ON OD.ORDERNUMBER = O.ORDERNUMBER
        JOIN
    CUSTOMERS C ON O.CUSTOMERNUMBER = C.CUSTOMERNUMBER
WHERE
    C.COUNTRY LIKE 'FRANCE' AND O.STATUS NOT LIKE 'CANCELLED'
GROUP BY P.PRODUCTCODE
ORDER BY SUM(OD.QUANTITYORDERED * PRICEEACH) DESC
LIMIT 7;

# 7
SELECT
    O.ORDERNUMBER,
    P.PRODUCTCODE,
    SUM(OD.QUANTITYORDERED * (OD.PRICEEACH - P.BUYPRICE))
FROM
    ORDERS O
        JOIN
    ORDERDETAILS OD ON O.ORDERNUMBER = OD.ORDERNUMBER
        JOIN
    PRODUCTS P ON OD.PRODUCTCODE = P.PRODUCTCODE
WHERE O.STATUS NOT LIKE 'CANCELLED'
GROUP BY O.ORDERNUMBER
ORDER BY SUM(OD.QUANTITYORDERED * (OD.PRICEEACH - P.BUYPRICE)) DESC;

#8
SELECT
    C.CUSTOMERNUMBER,
    COUNT(O.CUSTOMERNUMBER) AS 'TOTAL AMOUNT OF ORDER/S',
    SUM(OD.QUANTITYORDERED * OD.PRICEEACH) AS 'TOTAL AMOUNT'
FROM
    CUSTOMERS C
        JOIN
    ORDERS O ON C.CUSTOMERNUMBER = O.CUSTOMERNUMBER
        JOIN
    ORDERDETAILS OD ON O.ORDERNUMBER = OD.ORDERNUMBER
WHERE
    C.COUNTRY LIKE 'USA'
        AND EXTRACT(YEAR FROM O.ORDERDATE) LIKE '2005'
GROUP BY C.CUSTOMERNUMBER
ORDER BY SUM(OD.QUANTITYORDERED * OD.PRICEEACH) DESC;

# 9
SELECT
    P.PRODUCTVENDOR, P.PRODUCTNAME, EXTRACT(YEAR FROM O.ORDERDATE), SUM(QUANTITYORDERED) AS 'TOTAL QUANTITY SOLD',AVG(OD.PRICEEACH) AS 'AVERAGE PRICE'
FROM
    PRODUCTS P
        JOIN
    ORDERDETAILS OD ON P.PRODUCTCODE = OD.PRODUCTCODE
        JOIN
    ORDERS O ON O.ORDERNUMBER = OD.ORDERNUMBER
GROUP BY P.PRODUCTVENDOR, P.PRODUCTNAME, EXTRACT(YEAR FROM O.ORDERDATE)
ORDER BY AVG(OD.PRICEEACH) DESC;
# 10
SELECT
    P.PRODUCTVENDOR, SUM(OD.QUANTITYORDERED) AS 'TOTAL QUANTITY'
FROM
    PRODUCTS P
        JOIN
    ORDERDETAILS OD ON P.PRODUCTCODE = OD.PRODUCTCODE
WHERE
    P.PRODUCTVENDOR LIKE 'Unimax Art Galleries'
        OR P.PRODUCTVENDOR LIKE 'Exoto Designs'
        OR P.PRODUCTVENDOR LIKE 'Gearbox Collectibles'
        OR P.PRODUCTVENDOR LIKE 'Red Start Diecast'
GROUP BY P.PRODUCTVENDOR;