BD1_LAB6

/* lab 5.a */
SELECT COUNT(*) "TOTAL",
COUNT(CASE WHEN to_char(hiredate,'yyyy')='1980' THEN empno ELSE NULL END) "1980",
COUNT(CASE to_char(hiredate,'yyyy') WHEN '1981' THEN empno ELSE NULL END) "1981",
COUNT(CASE to_char(hiredate,'yyyy') WHEN '1982' THEN empno END) "1982",
COUNT(decode (to_char(hiredate,'yyyy'), '1983', empno, NULL)) "1983",
SUM(decode (to_char(hiredate,'yyyy'), '1984', 1, 0)) "1984"
FROM emp

SELECT job, SUM(sal) AS "Total",
SUM(CASE WHEN deptno = 10 THEN sal ELSE 0 END) AS "Dept. 10",
SUM(decode(deptno, 20, sal, 0)) AS "Dept. 20",
SUM(decode(deptno, 30, sal, 0)) AS "Dept. 30"
FROM emp GROUP BY job ORDER BY job
/* lav 6 */
/* 1.cross join
- afis. nume ang si nume departament */

SELECT ename, dname
FROM emp,dept

SELECT ename, dname
FROM emp CROSS JOIN dept

/* 2.equijoin (inner join) */

SELECT ename, dname, emp.deptno
FROM emp, dept
WHERE emp.deptno=dept.deptno

SELECT ename, dname
FROM emp JOIN dept
ON emp.deptno=dept.deptno

SELECT ename, dname
FROM emp JOIN dept USING(deptno)

SELECT ename, dname
FROM emp NATURAL JOIN dept

/* 3. non equijoin
- afis nume angajat si grupa de salarizare a fiecarui angajat uwu */

SELECT ename, grade, sal, losal, hisal
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal

SELECT ename, grade, sal, losal, hisal
FROM emp JOIN salgrade
ON sal BETWEEN losal AND hisal

/* 4. self-join
afisare nume angajat si numele sefului direct*/
SELECT ang.ename "nume ang.", sefi.ename "Nume sef"
FROM emp ang, emp sefi
WHERE ang.mgr=sefi.empno

SELECT ang.ename "nume ang.", sefi.ename "Nume sef"
FROM emp ang JOIN emp sefi
ON ang.mgr=sefi.empno

/* 5. outer join (left,right,full) */

SELECT ang.ename "nume ang.", sefi.ename "Nume sef"
FROM emp ang, emp sefi
WHERE ang.mgr=sefi.empno(+)

SELECT ang.ename "nume ang.", sefi.ename "Nume sef"
FROM emp ang LEFT JOIN emp sefi
ON ang.mgr=sefi.empno

SELECT ename, dname, emp.deptno
FROM emp, dept
WHERE emp.deptno(+)=dept.deptno

SELECT ename, dname
FROM emp RIGHT JOIN dept
ON emp.deptno=dept.deptno