--- SQL 1
SELECT *
FROM Customers
WHERE Country='Canada' or Country='Germany';

--- SQL 2
SELECT distinct CustomerID  --, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2043-11-01 00:00:00' and '2043-11-30 24:00:00' 

--- SQL 3
SELECT CustomerID
FROM Orders
WHERE OrderID IN (Select OrderID FROM OrderDetails WHERE ProductID IN (select ProductID FROM Products WHERE ProductName='Ravioli Angelo')) order by CustomerID;

--- SQL 3 con JOIN
Select CustomerID, Orders.OrderID 
FROM Orders, OrderDetails, Products 
WHERE 
-- JOINS:
      Orders.OrderID = OrderDetails.OrderID 
  AND Products.ProductID = OrderDetails.ProductID 
-- conditions
  AND ProductName='Ravioli Angelo' 
ORDER BY CustomerID

--- SQL 3 con INNER JOIN
Select CustomerID, Orders.OrderID 
FROM Orders 
  INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID 
  INNER JOIN Products ON Products.ProductID = OrderDetails.ProductID 
WHERE 
  ProductName='Ravioli Angelo' 
ORDER BY CustomerID

--- SQL 4
SELECT CustomerID, count (OrderID)
FROM Orders
WHERE (((Orders.EmployeeID) In (SELECT Employees.EmployeeID
FROM Employees
WHERE LastName='Suyama' AND Employees.FirstName='Michael')))
GROUP BY CustomerID
HAVING count (*) >2;

--- SQL 4  sin subquery
SELECT CustomerID, count (OrderID)
FROM Orders, Employees
WHERE Orders.EmployeeID = Employees.EmployeeID
  AND LastName='Suyama' AND Employees.FirstName='Michael'
GROUP BY CustomerID
HAVING count (*) >2;

--- SQL 5
SELECT CustomerID
FROM orders
WHERE shipvia in (SELECT ShipperID From Shippers WHERE CompanyName='Federal Shipping')
GROUP BY CustomerID
ORDER BY CustomerID;

--- SQL 5
SELECT distinct CustomerID
FROM orders, Shippers
WHERE shipvia = ShipperID 
  AND CompanyName='Federal Shipping'
ORDER BY CustomerID;

--- SQL 6
SELECT EmployeeID, count(OrderID)
FROM Orders
GROUP BY EmployeeID;

--- SQL 7
SELECT Employeeid, Sum(quantity*discount) AS SumaPedidos , count(orders.orderid) AS Count
FROM OrderDetails, orders
WHERE OrderDetails.orderid = orders.orderid
GROUP BY orders.EmployeeID;

--- SQL 8
SELECT suppliers.Supplierid, CompanyName
FROM suppliers, products, categories
WHERE categories.categoryid=products.categoryid 
  AND suppliers.Supplierid=products.supplierid
  AND categoryname='Seafood'
GROUP BY suppliers.supplierid, CompanyName;

--- SQL 9 -Por categoría
SELECT orders.employeeid, Sum(OrderDetails.quantity) AS SumOfquantity
FROM OrderDetails, orders, categories, products
WHERE OrderDetails.orderid=orders.orderid
  AND categories.categoryid=products.categoryid
  AND OrderDetails.productid=products.productid
  AND categoryname='Seafood'
GROUP BY orders.employeeid;

--- SQL 9 - Por producto
SELECT orders.employeeid, OrderDetails.productId, Sum(OrderDetails.quantity) AS SumOfquantity
FROM OrderDetails, orders, categories, products
WHERE OrderDetails.orderid=orders.orderid
  AND categories.categoryid=products.categoryid
  AND OrderDetails.productid=products.productid
  AND categoryname='Seafood'
GROUP BY orders.employeeid, OrderDetails.productId;

--- SQL 10 de orderDetails
SELECT products.categoryid, products.productid, sum(quantity)
FROM suppliers, products, OrderDetails
WHERE suppliers.supplierid=products.supplierid
  AND products.productid=OrderDetails.productid
  AND suppliers.country='USA'
GROUP BY products.categoryid, products.productid;

--- SQL 10 de Products
SELECT products.categoryid, count(*)
FROM suppliers, products
WHERE suppliers.supplierid=products.supplierid
  AND suppliers.country='USA'
GROUP BY products.categoryid;

--- SQL 11
SELECT orders.orderid, COUNT(OrderDetails.productId)
FROM OrderDetails, orders
WHERE OrderDetails.orderid=orders.orderid
GROUP BY orders.orderid
HAVING COUNT(*) > 5

--- SQL 12
SELECT productid, PRODUCTNAME
FROM products
WHERE products.productid NOT IN (Select productid from OrderDetails);

-- Insertar producto nuevo
INSERT INTO "APP"."PRODUCTS" (PRODUCTID,PRODUCTNAME,SUPPLIERID,CATEGORYID,QUANTITYPERUNIT,UNITPRICE,UNITSINSTOCK,UNITSONORDER,REORDERLEVEL,DISCONSTINUED) 
                      VALUES (99999,'New product for testing',1,1,'s',0,0,0,0,0);

SELECT PRODUCTID,PRODUCTNAME,SUPPLIERID,CATEGORYID,QUANTITYPERUNIT,UNITPRICE,UNITSINSTOCK,UNITSONORDER,REORDERLEVEL,DISCONSTINUED FROM "APP"."PRODUCTS"
WHERE PRODUCTID=99999;

--- SQL 13
SELECT OrderID, CustomerID, EmployeeID
FROM orders
WHERE EmployeeID = 
  (SELECT EmployeeID FROM Employees WHERE BirthDate = (SELECT Min(BirthDate) FROM Employees))

--- SQL 14
SELECT DISTINCT Region FROM Customers
UNION
SELECT DISTINCT Region FROM Suppliers

--- SQL 15
SELECT categoryid, sum(quantity) AS Suma
FROM OrderDetails, products
WHERE OrderDetails.productid= products.productid
GROUP BY categoryid
HAVING sum(quantity)=(SELECT Min(suma) AS MinOfsuma FROM (Select categoryid, sum(quantity) AS Suma FROM OrderDetails, products where OrderDetails.productid= products.productid group by categoryid) B);
--- B required in Derby to define alias

--- SQL 16
SELECT EmployeeID, COUNT(*) as quantity
  FROM
    (SELECT EmployeeID,orders.customerID
    FROM Orders
    --- WHERE Orders.employeeID = employees.employeeID
    GROUP BY EmployeeID, orders.customerID) B
  GROUP BY EmployeeID HAVING COUNT(*) = 
(SELECT MAX(quantity)
FROM (
  SELECT EmployeeID, COUNT(*) as quantity
  FROM
    (SELECT EmployeeID,orders.customerID
    FROM Orders
    --- WHERE Orders.employeeID = employees.employeeID
    GROUP BY EmployeeID, orders.customerID) B
  GROUP BY EmployeeID) C)