SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY SupplierName

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY SupplierName DESC

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY Country, City

SELECT *
FROM suppliers
ORDER BY Country DESC, City DESC

SELECT o.*, od.Quantity
FROM orders o
JOIN orderdetails od
ON o.OrderID = od.OrderID
ORDER BY od.Quantity DESC;

SELECT *
FROM products
ORDER BY Price DESC
LIMIT 10

SELECT *
FROM products
ORDER BY Price DESC
LIMIT 6 offset 9;

SELECT DISTINCT Country
FROM suppliers
ORDER BY Country

SELECT *
FROM suppliers
WHERE Country='USA'

SELECT SUM(Quantity)
FROM orderdetails

SELECT AVG(Price)
FROM products

SELECT *
FROM customers
WHERE ContactName='Thomas Hardy'

SELECT *
FROM customers
WHERE Country='Spain' OR Country='France'

SELECT *
FROM customers
WHERE Country!='USA'

SELECT o.OrderDate, p.ProductID, p.ProductName, p.Price
FROM orders o
JOIN orderdetails od
ON o.OrderID = od.OrderID
JOIN products p
ON od.ProductID = p.ProductID
WHERE Price NOT BETWEEN 50 AND 15000

SELECT *
FROM products
WHERE Price BETWEEN 10 AND 20
ORDER BY Price

SELECT *
FROM products
WHERE Price NOT BETWEEN 10 AND 20
ORDER BY Price

SELECT *
FROM orders
WHERE OrderDate BETWEEN '1996-01-01' AND '1996-12-31';

SELECT *
FROM suppliers
WHERE Country='USA' OR Country='UK' OR Country='Japan'

SELECT *
FROM products
WHERE Price NOT IN(10,20,30,40,50)

SELECT c.CustomerName, s.SupplierName, c.Country, s.Country
FROM customers c
JOIN orders o
ON c.CustomerID = o.CustomerID
JOIN orderdetails od
ON o.OrderID = od.OrderID
JOIN products p
ON od.ProductID = p.ProductID
JOIN suppliers s
ON p.SupplierID = s.SupplierID
WHERE c.Country = s.Country

SELECT *
FROM products
WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_'

SELECT CustomerName, AVG(TotalAmount) AverageOrders
FROM 
(
SELECT CustomerName, SUM(od.Quantity * p.Price) TotalAmount
FROM customers c
JOIN orders o
ON c.CustomerID = o.CustomerID
JOIN orderdetails od
ON o.OrderID = od.OrderID
JOIN products p
ON od.ProductID = p.ProductID
GROUP BY od.OrderID
) A
GROUP BY CustomerName
HAVING AverageOrders
BETWEEN 1000 AND 1200
ORDER BY AverageOrders

SELECT Country, COUNT(CustomerName) TotalCustomer
FROM customers
GROUP BY Country

SELECT DISTINCT o.OrderID, c.*
FROM Orders o
INNER JOIN customers c
ON o.CustomerID = c.CustomerID
ORDER BY o.OrderID

SELECT DISTINCT o.OrderID, p.ProductName, od.Quantity, p.Price
FROM Orders o
INNER JOIN orderdetails od
ON od.OrderID = od.OrderID
INNER JOIN products p
ON od.ProductID = p.ProductID
ORDER BY o.OrderID

SELECT c.CustomerID, c.CustomerName, c.ContactName, o.OrderID
FROM customers c
LEFT JOIN orders o
ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerID

SELECT c.*
FROM customers c
WHERE c.CustomerID NOT IN (SELECT o.CustomerID
                           FROM orders o )
ORDER BY c.CustomerID

SELECT s.ContactName, 'Supplier' Type
FROM suppliers s
UNION
SELECT c.ContactName, 'Customer' Type
FROM customers c
ORDER BY ContactName

SELECT p.ProductID, p.ProductName, SUM(od.Quantity) AS 'Quantity'
FROM Products p
INNER JOIN OrderDetails od
ON p.ProductID = od.ProductID
GROUP BY p.ProductID
HAVING SUM(od.Quantity) > 80
ORDER BY p.ProductID

SELECT p.ProductID, p.ProductName, od.Quantity
FROM Products p
INNER JOIN OrderDetails od
ON p.ProductID = od.ProductID
WHERE od.Quantity = 1
ORDER BY p.ProductID

SELECT c.CustomerID, c.CustomerName, od.Quantity*p.Price as 'TotalAmount'
FROM customers c
JOIN orders o
ON c.CustomerID = o.CustomerID
JOIN orderdetails od
ON o.OrderID = od.OrderID
JOIN products p
ON od.ProductID = p.ProductID
WHERE od.Quantity*p.Price > ALL (
                                  SELECT AVG(od.Quantity*p.Price)
                                  FROM customers c
                                  JOIN orders o
                                  ON c.CustomerID = o.CustomerID 
                                  JOIN orderdetails od
                                  ON o.OrderID = od.OrderID
                                  JOIN products p
                                  ON od.ProductID = p.ProductID
                                  GROUP BY c.CustomerID
                                    )

SELECT p.ProductID, p.ProductName, p.Price, SUM(od.Quantity) AS 'Quantity'
FROM Products p
JOIN Orderdetails od
ON p.ProductID = od.ProductID
GROUP BY ProductName
ORDER BY Quantity DESC
LIMIT 1;

SELECT p.ProductName, p.Price, od.Quantity, SUM(p.Price*od.Quantity) AS Revenue
FROM Products p
JOIN Orderdetails od
ON p.ProductID = od.ProductID
GROUP BY ProductName
ORDER BY Revenue DESC
LIMIT 1;

SELECT Country, ProductName, MAX(Revenue) AS 'Revenue' 
FROM(SELECT p.ProductName, c.Country, SUM(od.Quantity*p.Price) AS 'Revenue' 
FROM products p
JOIN orderdetails od
ON p.ProductID = od.ProductID
JOIN orders o
ON od.OrderID = o.OrderID
JOIN customers c
ON o.CustomerID = c.CustomerID
 GROUP BY c.Country, p.ProductName
 ORDER BY Country ASC, Revenue DESC) s
 GROUP BY Country

SELECT s.SupplierName, p.Price
FROM suppliers s
JOIN products p
ON s.SupplierID = p.SupplierID
WHERE p.Price < 50
GROUP BY s.SupplierName
ORDER BY s.SupplierName

SELECT CONCAT (FirstName,' ',LastName) AS 'Name', SUM(Quantity) AS 'Quantity'
FROM employees e
LEFT JOIN orders o
ON e.EmployeeID = o.EmployeeID
LEFT JOIN orderdetails od
ON o.OrderID = od.OrderID
GROUP BY e.EmployeeID
ORDER BY Quantity DESC
LIMIT 10;

SELECT s.Country, SUM(Quantity) AS 'Quantity'
FROM suppliers s
LEFT JOIN products p
ON s.SupplierID = p.SupplierID
LEFT JOIN orderdetails od
ON od.ProductID = p.ProductID
GROUP BY s.Country
ORDER BY Quantity DESC
LIMIT 10;

SELECT c.Country, sum(Quantity) AS 'Quantity'
FROM customers c
LEFT JOIN orders o
ON c.CustomerID = o.CustomerID
LEFT JOIN orderdetails od
ON o.OrderID = od.OrderID
GROUP BY c.Country
ORDER BY Quantity DESC
LIMIT 10;

(
SELECT p.ProductName, YEAR(o.OrderDate) AS 'Year', sum(od.Quantity) AS 'Quantity'
FROM products p
JOIN orderdetails od
ON p.ProductID = od.ProductID
JOIN orders o
ON od.OrderID = o.OrderID
GROUP BY Year, ProductName
HAVING Year = 1996
ORDER BY Year, Quantity DESC
LIMIT 10
)

UNION

(
SELECT p.ProductName, YEAR(o.OrderDate) AS 'Year', sum(od.Quantity) AS 'Quantity'
FROM products p
JOIN orderdetails od
ON p.ProductID = od.ProductID
JOIN orders o
ON od.OrderID = o.OrderID
GROUP BY Year, ProductName
HAVING Year = 1997
ORDER BY Year, Quantity DESC
LIMIT 10
)

SELECT Country, ProductName, AVG(quantity) AS 'quantity' 
FROM
(SELECT P.ProductName, C.Country, AVG(OD.Quantity) AS 'quantity' 
  FROM products P
    JOIN orderdetails OD
      ON P.ProductID = OD.ProductID
    JOIN orders O
      ON OD.OrderID = O.OrderID
    JOIN customers C
      ON O. CustomerID = C.CustomerID
    GROUP BY C. Country, P.ProductName
   ) S
    GROUP BY Country
    Order BY quantity

(SELECT P.ProductName, C.Country, YEAR(O.OrderDate) AS 'Year', AVG(OD.Quantity*P.Price) AS 'Revenue'
  FROM products P
    JOIN orderdetails OD
      ON P.ProductID = OD.ProductID
    JOIN orders O
      ON OD.OrderID = O.OrderID
      JOIN customers C
      ON O.CustomerID = C.CustomerID
       GROUP BY Country, Year
        HAVING Year = 1996
         ORDER BY Year, Quantity DESC)
UNION

(SELECT P.ProductName, C.Country, YEAR(O.OrderDate) AS 'Year', AVG(OD.Quantity*P.Price) AS 'Revenue'
  FROM products P
    JOIN orderdetails OD
      ON P.ProductID = OD.ProductID
    JOIN orders O
      ON OD.OrderID = O.OrderID
      JOIN customers C
      ON O.CustomerID = C.CustomerID
       GROUP BY Country, Year
        HAVING Year = 1997
         ORDER BY Year, Quantity DESC)
          ORDER BY Revenue, Year