-- 1.4
SELECT ename, deptno, sal+isnull(comm,0) as income,
	(sal+isnull(comm,0))*0.2 as tax
FROM emp
ORDER BY ename;

-- 1.5
SELECT ename as workerName, job as workerJob, sal as workerSalary
FROM emp
-- WHERE sal<1500 OR sal>2850;
-- WHERE NOT(sal between 1500 and 2850);
WHERE sal not between 1500 and 2850;

-- 1.6
-- 'yyyy-mm-dd'
SELECT ename, job, sal, comm, deptno, hiredate
FROM emp
WHERE sal+isnull(comm, 0)>1000 AND 
	hiredate>='1981-03-01' AND
	hiredate<='1981-09-30';

-- 1.8
SELECT empno, ename, job, sal, comm
FROM emp
WHERE isnull(comm,0) > 0.5*sal;

-- 1.9
SELECT ename, job, sal, sal+isnull(comm,0) as income, hiredate
FROM emp
WHERE 
	-- (comm is null OR comm=0) AND
	isnull(comm,0)=0  AND
	-- hiredate>='1981-01-01' AND hiredate<'1982-01-01';
	-- YEAR(hiredate) = 1981;
	-- EXTRACT(year from hireadate) = 1981; -- not in tsql
	-- datepart(year, hiredate) = 1981;
	-- substring(convert(varchar, hiredate, 102), 1, 4) = '1981';
	format(hiredate, 'yyyy')='1981';

-- 1.13
SELECT ename, job
FROM emp
WHERE lower(job) like '%man%'
ORDER BY ename, job;

-- 1.16
SELECT ename, len(ename) as nameLength, upper(left(ename, 1))+lower(right(ename, len(ename)-1)) as goodName
FROM emp
WHERE lower(ename) like 'c%' OR lower(ename) like 'm%';

-- 1.19
SELECT ename, sal, cast(round(sal/1000, 0) as int) as salCat, convert(int, round(sal/1000, 0)) as salCat2
FROM emp;

-- 2.15
SELECT empno, ename, empno % 2 as parity, hiredate,
	iif(empno%2 = 0, 'Even', 'Odd') as parity2,
	CASE empno%2
		WHEN 0 THEN 'Even'
		WHEN 1 THEN 'Odd'
	END as parity3
FROM emp
WHERE year(hiredate)<=1981;