select COUNT(EmployeeID) from orders
where EmployeeID = 1 and YEAR(OrderDate) = 1996

select COUNT(EmployeeID) from orders
where EmployeeID = 2 and YEAR(OrderDate) = 1996

---------------------------------------------------
-- JOINS
---------------------------------------------------

SELECT *
FROM Products

SELECT *
FROM Categories

SELECT c.CategoryName, p.ProductName
FROM Categories c, Products p
ORDER BY 1

select 77 * 8

-- INNER JOIN --
-- We Use join condition in a special ON clause (Similar To The WHERE Clause)

Select * from Products
select * from Categories 

SELECT p.ProductName, c.CategoryName
FROM Products p INNER JOIN Categories c  
ON p.CategoryID = c.CategoryID

select * from Products
select * from Suppliers

SELECT CompanyName, ProductName
FROM Suppliers JOIN Products
ON Suppliers.SupplierID = Products.SupplierID

-- Use additional conditions in either way (produce the same result):
-- 1. In the ON clause
-- 2. In a WHERE clause

-- 1. In the ON clause
SELECT S.CompanyName, P.ProductName
FROM dbo.Suppliers S JOIN dbo.Products P
on S.SupplierID = P.SupplierID
AND P.ProductName LIKE 'C%'

-- 2. In a WHERE clause
SELECT S.CompanyName, P.ProductName
FROM dbo.Suppliers S INNER JOIN dbo.Products P
ON S.SupplierID = P.SupplierID
WHERE P.ProductName LIKE 'C%'

--joining 3 tables
-- To join n tables we need to use n-1 join conditions.
SELECT CompanyName, P.ProductName, OD.OrderID, OD.Quantity
FROM Suppliers S INNER JOIN Products P
ON S.SupplierID = P.SupplierID 
	INNER JOIN [Order Details] OD
ON OD.ProductID=P.ProductID

--joining 5 tables

--categories> products>order details>orders> employees

SELECT E.FirstName+ ' '+ E.LastName FullName, o.OrderID, C.CategoryName, p.ProductName, od.Quantity, od.UnitPrice, od.Quantity*od.UnitPrice TotalCost
FROM Categories c JOIN Products P 
ON C.CategoryID=P.CategoryID
INNER JOIN [Order Details] OD
ON P.ProductID=OD.ProductID
INNER JOIN Orders O
ON OD.OrderID=O.OrderID
INNER JOIN Employees E
ON O.EmployeeID=E.EmployeeID

--OUTER JOINS

--LEFT JOIN -- Null will be in the second column (orderid)

select count(*) from Orders
select COUNT(*) from customers
select COUNT(distinct CustomerID) from orders

SELECT c.customerid, o.orderid
FROM customers c  JOIN orders o
ON c.customerid=o.customerid

SELECT c.customerid, o.orderid
FROM customers c LEFT OUTER JOIN orders o
ON c.customerid=o.customerid

SELECT c.customerid, o.orderid
FROM customers c LEFT OUTER JOIN orders o
ON c.customerid=o.customerid
where o.orderid is null

--RIGHT JOIN -- Null will be in the first column (customerid)

SELECT c.customerid, o.orderid
FROM customers c RIGHT JOIN orders o
ON c.customerid=o.customerid

--FULL JOIN -- Null will be in the first column (customerid)
SELECT c.customerid, o.orderid
FROM customers c FULL JOIN orders o
ON c.customerid=o.customerid

-- If We want to recieve all the rows from a specific table in the join,
-- Including rows that did not meet the join condition:

DROP TABLE emp;
DROP TABLE dept;

CREATE TABLE emp 
(emp_id int,
emp_name VARCHAR(25),
dept_id int);

CREATE TABLE dept 
(dept_id int,
dept_name VARCHAR(25));

INSERT INTO dept
VALUES(10, 'Marketing');
INSERT INTO dept
VALUES(20,'Executive');
INSERT INTO dept
VALUES(30,'Shipping');

INSERT INTO emp
VALUES(1, 'Moshe',10);
INSERT INTO emp
VALUES(2, 'Shimon',20);
INSERT INTO emp
VALUES(3, 'Rachel',10);
INSERT INTO emp
VALUES(4, 'Yael',NULL);

SELECT * FROM dept;
SELECT * FROM emp;

-- Regular EQUIJOIN:
SELECT emp_name, dept_name
FROM emp e JOIN dept d
ON e.dept_id = d.dept_id

-- OUTER JOIN:

SELECT emp_name, dept_name
FROM emp e LEFT OUTER JOIN dept d
ON e.dept_id = d.dept_id

SELECT emp_name, dept_name
FROM emp e RIGHT OUTER JOIN dept d
ON e.dept_id = d.dept_id

SELECT emp_name, dept_name
FROM emp e FULL OUTER JOIN dept d
ON e.dept_id = d.dept_id

--SELF JOIN

SELECT EmployeeID, LastName, ReportsTo
FROM dbo.Employees

SELECT emp.employeeid, emp.FirstName+' '+emp.lastname AS Employee, mgr.employeeid AS Managerid, mgr.FirstName+' '+mgr.lastname AS Manager
FROM Employees emp inner JOIN Employees mgr
ON emp.reportsto = mgr.employeeid

SELECT emp.employeeid, emp.FirstName+' '+emp.lastname AS Employee, mgr.employeeid AS Managerid, mgr.FirstName+' '+mgr.lastname AS Manager
FROM Employees emp left JOIN Employees mgr
ON emp.reportsto = mgr.employeeid

--Cross Join
--A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join
SELECT c.CategoryName, p.ProductName
FROM Categories c cross join Products p
ORDER BY 1

-----------------------
--- Full Outer Join ---
-----------------------
select SUM(a.Freight) Emp1Freight, count(a.Freight) Emp1Count, SUM(b.Freight) Emp2Freight, count(b.Freight) Emp2Count
from
	(select * from Orders
	 where EmployeeID = 1 and YEAR(OrderDate) = 1996) a
full outer join
	(select * from Orders
	 where EmployeeID = 2 and YEAR(OrderDate) = 1996) b
on a.OrderID = b.OrderID