-- 1

SELECT ename, job, sal+isnull(comm,0) as income
FROM emp
WHERE deptno IN (
	SELECT deptno FROM dept WHERE loc='CHICAGO' OR loc='DALLAS'
);
-- subquery in the where???

SELECT ename, job, sal+isnull(comm, 0) as income
FROM emp INNER JOIN 
	( SELECT deptno FROM dept WHERE loc='CHICAGO' OR loc='DALLAS' ) subQuery 
			ON (emp.deptno=subQuery.deptno);
-- if indexed view
-- key-preserved view/subquery

SELECT ename, job, sal+isnull(comm, 0) as income
FROM emp INNER JOIN dept ON (emp.deptno=dept.deptno)
WHERE loc='CHICAGO' OR loc='DALLAS';

SELECT ename, job, sal+isnull(comm, 0) as income
FROM emp, dept 
WHERE emp.deptno=dept.deptno AND 
	(loc='CHICAGO' OR loc='DALLAS');

-- 2

IF object_id('workerData') is not null drop view workerData;
IF object_id('citySalaries') is not null drop view citySalaries;
GO
CREATE VIEW citySalaries AS
SELECT sum(sal) as sumSal, loc
FROM emp INNER JOIN dept ON (emp.deptno=dept.deptno)
GROUP BY loc;
GO

CREATE VIEW workerData AS
SELECT worker.ename as workerName, worker.sal as workerSalary,
	boss.ename as bossName, loc
FROM emp worker
	INNER JOIN emp boss ON (worker.mgr=boss.empno)
	INNER JOIN dept ON (boss.deptno=dept.deptno);
GO

-- SELECT * FROM citySalaries;
-- SELECT * FROM workerData;
SELECT workerData.*, citySalaries.sumSal
FROM workerData INNER JOIN citySalaries ON (workerData.loc=citySalaries.loc);

/*
3. SELECT expression
1. FROM tables
4. WHERE condition
2. GROUP BY expression
4. HAVING condition
5. ORDER BY expression

6. test!!! 
- vs spec
- vs data
- vs client/boss
*/

-- 3

IF object_id('bossData') is not null drop view bossData;
IF object_id('deptData') is not null drop view deptData;
GO

CREATE VIEW bossData AS
SELECT count(1) as cntWorkers, mgr
FROM emp
GROUP BY mgr;
GO

CREATE VIEW deptData AS
SELECT avg(sal+ISNULL(comm,0)) as avgInc, deptno
FROM emp
GROUP BY DEPTNO;
GO

-- SELECT * FROM bossData;
-- SELECT * FROM deptData;

-- A = dept of boss
SELECT ename as bossName, cntWorkers, boss.deptno as bossDeptno,
		avgInc, avgInc - sal - isnull(comm, 0) as diff 
FROM emp boss
	INNER JOIN bossData ON (boss.empno=bossData.mgr)
	INNER JOIN deptData ON (boss.deptno=deptData.deptno);

-- B = dept of worker
SELECT worker.ename as workerName, boss.ename as bossName,
		cntWorkers, worker.deptno as workDeptno,
		avgInc, avgInc - boss.sal - isnull(boss.comm, 0) as diff
FROM emp worker 
	INNER JOIN emp boss ON (worker.mgr=boss.empno)
	INNER JOIN bossData ON (boss.empno=bossData.mgr)
	INNER JOIN deptData ON (worker.deptno=deptData.deptno);

-- 4

-- A = stats of job+dept
SELECT deptno, job, 
	count(1) as cntWorkers, min(sal) as minSal, max(sal) as maxSal
FROM emp
GROUP BY deptno, job;

-- B = stats of job only
IF object_id('jobData') is not null drop view jobData;
IF object_id('jobsAndDepts') is not null drop view jobsAndDepts;
GO

CREATE VIEW jobData AS
SELECT job, count(1) as cntWorkers, min(sal) as minSal, max(sal) as maxSal
FROM emp
GROUP BY job;
GO

CREATE VIEW jobsAndDepts AS
SELECT DISTINCT deptno, job
FROM emp;
GO

SELECT * FROM jobsAndDepts INNER JOIN jobData ON (jobData.job = jobsAndDepts.job);

-- 5

IF object_id('jobAverages') is not null drop view jobAverages;
IF object_id('deptAverages') is not null drop view deptAverages;
GO

CREATE VIEW jobAverages AS
SELECT avg(sal) as avgSal, job
FROM emp
GROUP BY job;
GO

CREATE VIEW deptAverages AS
SELECT avg(sal) as avgSal, deptno
FROM emp
GROUP BY deptno;
GO

SELECT * FROM jobAverages;
SELECT * FROM deptAverages;
SELECT ename, emp.job, dname, sal,
	jobAverages.avgSal as avgOfJob,
	deptAverages.avgSal as avgOfDept,
	round(100*emp.sal/jobAverages.avgSal, 2) as ratioForJob,
	round(100*emp.sal/deptAverages.avgSal, 2) as ratioForDept
FROM emp 
	INNER JOIN dept ON (emp.deptno=dept.deptno)
	INNER JOIN jobAverages ON (emp.job=jobAverages.job)
	INNER JOIN deptAverages ON (emp.deptno=deptAverages.deptno);

-- 6
-- Display every worker's name, their department's name, their boss' name. 
-- The list should include the income parameter for both the boss and the worker.
-- Income parameter: (income) - (the average income of those who entered the company the same year)

-- A. yearAverages: year + averageIncome
-- B. incomeParameters = emp + yearAverages: empno + incomeParameter
-- C. workerData = emp worker + dept + emp boss: workerId, bossId, workerName, bossName, deptName
-- D. workerData + incomeParameters + incomeParameters: 
--				workerName, bossName, deptName, workerParam, bossParam

IF object_id('yearAverages') is not null drop view yearAverages;
IF object_id('incomeParameters') is not null drop view incomeParameters;
IF object_id('workerData') is not null drop view workerData;
GO

-- A
CREATE VIEW yearAverages AS
SELECT year(hiredate) as year,
      avg(sal+isnull(comm,0)) as avgIncome
FROM emp
GROUP BY year(hiredate);
GO
SELECT * FROM yearAverages;

-- B
GO
CREATE VIEW incomeParameters AS
SELECT empno, sal+isnull(comm,0) - avgIncome as param
FROM emp INNER JOIN yearAverages ON (year(hiredate) = yearAverages.year);
GO
SELECT * FROM incomeParameters;
GO

-- C
CREATE VIEW workerData AS
SELECT w.empno as workerId, b.empno as bossId, dname,
      w.ename as workerName, b.ename as bossname
FROM emp w LEFT JOIN emp b ON (w.mgr=b.empno) 
          LEFT JOIN dept ON (w.deptno=dept.deptno);
GO
SELECT * FROM workerData;

-- D
SELECT workerName, dname, bossName, wp.param as workerParam, bp.param as bossParam
FROM workerData INNER JOIN incomeParameters wp ON (workerId=wp.empno)
              INNER JOIN incomeParameters bp ON (bossId=bp.empno);