USE classicmodels;

-- 1
SELECT city
    FROM customers
    WHERE country = 'Australia'
    ORDER BY city asc;

-- 2
SELECT EmployeeNumber, LastName, FirstName, Extension
    FROM employees
    WHERE officeCode = 6;

-- 3
SELECT ProductCode, ProductName, ProductVendor, QuantityInStock, ProductLine
    FROM products
    WHERE (quantityInStock > 4000 AND quantityInStock < 5000);

-- 4
SELECT ProductCode, ProductName, ProductVendor, BuyPrice, max(MSRP) as MSRP
    FROM products;

-- 5
SELECT ProductName, MSRP, BuyPrice, (MSRP - BuyPrice) as Margin
    FROM products
    ORDER BY Margin ASC
    LIMIT 1;

-- 6
SELECT country, count(customerNumber) as Customers
    FROM customers
    GROUP BY country
    HAVING count(country) > 5
    ORDER BY Customers ASC;

-- 7
SELECT O.productCode, P.productName, SUM(O.quantityOrdered) AS OrderCount
    FROM OrderDetails O, Products P
    GROUP BY P.productCode, O.productCode
    ORDER BY OrderCount DESC
    LIMIT 1;

-- 8
SELECT CONCAT(managers.FirstName, ' ', managers.LastName) AS `Managers`, CONCAT(employees.FirstName, ' ', employees.LastName) AS `Workers`
    FROM employees
        LEFT JOIN employees AS managers
        ON employees.ReportsTo = managers.employeeNumber
    ORDER BY managers.employeeNumber;

-- 9
SELECT EmployeeNumber, LastName, FirstName
    FROM Employees
    WHERE ReportsTo IS NULL;

-- 10
SELECT ProductName
    FROM Products
    WHERE ProductName LIKE '196%';

-- 11
SELECT monthname(orderDate) as Month, year(orderDate) as Year, count(*) as orderCount
    FROM Orders
    GROUP BY monthname(orderDate), year(orderDate)
    ORDER BY count(*) DESC
    LIMIT 2;

-- 12
SELECT DISTINCT E.firstName, E.lastName
    FROM Employees E LEFT OUTER JOIN Customers C
    ON E.employeeNumber = C.salesRepEmployeeNumber
    WHERE (C.salesRepEmployeeNumber IS NULL
        AND E.jobTitle = 'Sales Rep');

-- 13
SELECT customerName
    FROM customers
    WHERE creditLimit = 0 AND country = 'Switzerland';

-- 14
SELECT customerName, sum(quantityOrdered)
    FROM OrderDetails, Customers, Orders
    WHERE (OrderDetails.orderNumber = Orders.orderNumber
        AND Orders.customerNumber = Customers.customerNumber)
    GROUP BY customerName
    HAVING sum(quantityOrdered) < 500;

-- 15
CREATE TABLE LowCustomers(
    CustomerNumber int(45) NOT NULL,
    ContactDate date NOT NULL,
    OrderTotal decimal(7, 2) NOT NULL,
    CONSTRAINT LowCustomer_PK PRIMARY KEY (CustomerNumber));

-- 16
INSERT INTO LowCustomers (CustomerNumber, ContactDate, OrderTotal)
    SELECT o.customerNumber, CURDATE(), SUM(od.priceEach * od.quantityOrdered) as TotalValue
        FROM Orders o, OrderDetails od
        WHERE o.orderNumber = od.orderNumber
        GROUP BY o.customerNumber
        HAVING SUM(od.priceEach * od.quantityOrdered) < 50000;

-- 17
SELECT *
    FROM LowCustomers
    ORDER BY orderTotal DESC;

-- 18
ALTER TABLE LowCustomers
    ADD orderCount int(45)
    AFTER orderTotal;

-- 19
UPDATE LowCustomers
    SET orderCount = FLOOR(RAND()*(18-1+1)+1)
    WHERE orderCount IS NULL;

-- 20
SELECT *
    FROM LowCustomers
    ORDER BY orderCount DESC;

-- 21
DROP TABLE LowCustomers;