vypracoval Peter Paulis

/\/\/\/\/\/\/\/\/\/\/\/\/

1. Napiste dotaz, ktory vypise vsetky joby z tabulky EMP - kazdy prave raz.
select distinct job from emp;

2. Vypiste meno a plat vsetkych zamestnancov, ktori zarabaju viac ako $2850.
select ename, job from emp where sal>2850;

3. Vypiste meno a cislo oddelenia zamestnanca cislo 7566.
select ename, deptno from emp where empno=7566;

4. Vypiste meno a plat zamestnancov, ktorych plat nie je v rozsahu $1500 - $2850.
select ename, sal from emp where sal not between 1500 and 2850;

5. Vypiste meno a datum prijatia zamestnancov prijatych medzi 20. februarom 1981 a 1. majom 1981. Vystup zoradte 
vzostupne podla datumu prijatia.
select ename, hiredate from emp where hiredate >= '1981-02-20' and hiredate <= '1981-05-01' order by hiredate;

6. Vypiste meno a cislo oddelenia zamestnancov v oddeleni 10 a 30 v abecednom poradi podla mena.
select ename, deptno from emp where deptno in (10,30) order by ename;

7. Vypiste mena a platy tych zamestnancov, ktori zarabaju viac ako $1500 a pracuju v oddeleni 10 alebo 30. Stlpce pomenujete Employee a Monthly Salary.
select ename as Employee, sal as "Monthly Salary" from emp where sal>1500 and deptno in (10,30);

8. Vypiste meno a datum prijatia tych zamestnancov, ktori boli prijati v roku 1982.
select ename, hiredate from emp where hiredate like '1982%';

9. Vypiste meno a job tych zamestnancov, ktori nemaju manazera.
select ename, job from emp where mgr is NULL;

10. Vypiste mena tych zamestnancov, ktori maju v mene dve pismena L alebo tretie pismeno je A.
select ename from emp where ename like '%L%L%' or ename like '__A%';

11. Vypiste mena tych zamestnancov, ktorych job je CLERK alebo ANALYST a ktorych plat nie je $1000, $3000 ani $5000.
select ename from emp where job in ('CLERK', 'ANALYST') and sal not in (1000,3000,5000);


**************************************************************************************


1. Vypiste zoznam jobov, v ktorych pracuju ludia v Chicagu.
paulis=> SELECT DISTINCT job FROM emp NATURAL JOIN dept WHERE loc='CHICAGO';

2. Vypiste meno zamestenanca, meno oddelenia a lokalitu tych zamestnancov, ktori dostavaju premie.
paulis=> SELECT ename, dname, loc FROM emp NATURAL JOIN dept WHERE comm IS NOT null;

3. Vypiste meno zamestenanca a meno oddelenia tych zamestnancov, ktori maju v mene pismeno A.
paulis=> SELECT ename, dname FROM emp NATURAL JOIN dept WHERE ename LIKE '%A%';

4. Vypiste meno, job, cislo oddelenia a meno oddelenia tych zamestnancov, ktori pracuju v Dallase.
paulis=> SELECT job, deptno, ename FROM emp NATURAL JOIN dept WHERE loc='DALLAS';

5. Vypiste cislo oddelenia, meno zamestnanca a mena vsetkych kolegov, ktori pracuju v tom istom oddelenie ako on (pre kazdeho kolegu jeden riadok). Vystup zoradte podla cisla oddelenia.
paulis=> SELECT E1.deptno, E1.ename, E2.ename FROM emp AS E1, emp AS E2 WHERE E1.deptno=E2.deptno ORDER BY E1.deptno;

6. Vypiste meno zamestnanca, job, meno oddelenia, plat a platovy stupen pre vsetkych zamestnancov. Vystup zoradte podla platu.
paulis=> SELECT ename, job, dname, grade, sal FROM emp NATURAL JOIN dept RIGHT OUTER JOIN salgrade ON sal BETWEEN losal AND hisal;

7. Vypiste meno, cislo oddelenia a meno oddelenia pre vsetkych zamestnancov.
paulis=> SELECT ename, deptno, dname FROM emp NATURAL JOIN dept;

8. Pre vsetkych vypiste meno a cislo zamestnancaa meno jeho manazera.
paulis=> SELECT E1.ename, E1.empno, E2.ename FROM emp AS E1, emp AS E2 WHERE E1.mgr=E2.empno;

9. Vypiste meno zamestnanca, datum jeho prijatia, meno jeho manazera, datum prijatia manazera pre vsetkych zamestnancov, ktori boli prijati pred svojimi manazermi.
paulis=> SELECT E1.ename, E1.hiredate, E2.ename, E2.hiredate FROM emp AS E1, emp AS E2 WHERE E1.mgr=E2.empno AND E1.hiredate<E2.hiredate;


**************************************************************************************


1. Vypiste najnizsi, najvyssi, priemerny plat a sucet vsetkych platov. Vysledky zaokruhlite na cele cisla.
paulis=> SELECT MIN(sal), MAX(sal), ROUND(AVG(sal)), SUM(sal) FROM emp;

2. Vypiste min, max, avg, sum pre kazdy typ jobu.
paulis=> SELECT job, MIN(sal), MAX(sal), ROUND(AVG(sal)), SUM(sal) FROM emp GROUP BY job;

3. Vypiste dotaz, ktory vypise meno oddelenia a pocet pocet ludi pracujucich v tom istom oddeleni.
paulis=> SELECT dname, COUNT(E) FROM emp AS E FULL OUTER JOIN dept ON E.deptno=dept.deptno GROUP BY dname;

4. Vypiste pocet SALESMANOV.
paulis=> SELECT COUNT(*) FROM emp WHERE job='SALESMAN';

5. Vypiste rozdiel medzi najvyssim a najnizsim platom. Stlpec pomenujte rozdiel.
paulis=> SELECT MAX(sal)-MIN(sal) AS rozdiel FROM emp;

6. Vypiste cislo oddelenia, a mena tych zamestnancov, ktori pracuju v tom istom oddeleni ako SMITH. 
paulis=> SELECT emp.deptno, emp.ename FROM emp NATURAL JOIN dept, emp AS E WHERE E.ename='SMITH' AND emp.deptno=E.deptno;

7. Vypiste meno a datum prijatia zamestnancov, ktori boli prijati po BLAKEovi.
paulis=> SELECT emp.ename, emp.hiredate FROM emp, emp AS E WHERE E.ename='BLAKE' AND emp.hiredate>E.hiredate;


**************************************************************************************


1. Vypiste cislo managera a plat najmenej plateneho zamestnanca tohto managera. Vynechajte vsetkych, ktori managera nemaju. Vynechajte skupiny, kde je najnizsi plat nizsi ako 1000. Vystup zoradte vzostupne podla min, platu v skupine. (skupina=mnozina ludi s jednym managerom)
paulis=> SELECT DISTINCT E1.mgr, E1.sal FROM emp AS E1, emp AS E2 WHERE E1.mgr=E2.empno AND E1.sal=(SELECT MIN(sal) FROM emp WHERE emp.mgr=E2.empno) AND E1.mgr IS NOT null AND (SELECT MIN(sal) FROM emp WHERE emp.mgr=E2.empno)>=1000 ORDER BY E1.sal;

2. Vypiste meno a plat zvyseny o 500 pre vsetkych zamestnancov, ktori pracuju v oddeleni 20 a ich job je CLERK.
paulis=> SELECT empno, ename, (sal+500) FROM emp WHERE deptno='20' AND job='CLERK';

3. Vypiste cislo, meno a plat tych zamestnancov, ktori zarabaju viac ako je priemerny plat. Zoradte vzostupne podla platu.
paulis=> SELECT empno, ename, sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp) ORDER BY sal DESC;

4. Vypiste meno, cislo oddelenia a job tych zamestnancov, ktori pracju v jednom oddeleni s nejakym zamestnancom, ktoreho meno obsahuje pismeno T.
paulis=> SELECT DISTINCT E1.ename, E1.deptno, E1.job FROM emp AS E1, emp AS E2 WHERE E1.deptno=E2.deptno AND E2.ename LIKE '%T%';

5. Vypiste meno a plat vsetkych zamestnancov, ktorych managerom je KING.
paulis=> SELECT emp.ename, emp.sal FROM emp, emp AS E WHERE emp.mgr=E.empno AND E.ename='KING';

6. Vypiste meno, job a plat vsetkych zamestnancov v oddeleni SALES.
paulis=> SELECT ename, job, sal FROM emp NATURAL JOIN dept WHERE dname='SALES';

7. Vypiste cislo, meno a plat tych zamestnancov, ktorych plat je vyssi ako priemerny a pracuju v jednom oddeleni so zamestnancom, ktory ma v mene pismeno O.
paulis=> SELECT DISTINCT E1.empno, E1.ename, E1.sal FROM emp AS E1, emp AS E2 WHERE E1.sal>(SELECT AVG(emp.sal) FROM emp) AND E1.deptno=E2.deptno AND E2.ename LIKE '%O%';

8. Vypiste meno, job a plat vsetkych zamestnancov, ktori maju rovnaky job ako ako zamestnanec cislo 7369 a zarabaju viac ako ako zamestnanec cislo 7876. 
paulis=> SELECT DISTINCT E1.ename, E1.job, E1.sal FROM emp AS E1, emp AS E2, emp AS E3 WHERE E2.empno=7369 AND E1.job=E2.job AND E3.empno=7876 AND E1.sal>E3.sal;

9. Vypiste cislo oddelenia, meno a plat tych zamestnancov, ktori zarabaju viac ako je priemerny plat v ich oddeleni. 
paulis=> SELECT E.deptno, E.ename, E.sal FROM emp AS E WHERE E.sal>(SELECT AVG(emp.sal) FROM emp WHERE emp.deptno=E.deptno);

10. Vypiste cislo oddelenia, meno, job a plat tych zamestnancov, ktori vo svojom oddeleni zarabaju najmenej.
paulis=> SELECT deptno, ename, job, sal FROM emp WHERE sal=(SELECT MIN(E.sal) FROM emp AS E WHERE E.deptno=emp.deptno);

11. Vypiste meno zamestnanca, cislo oddelenia a plat tych zamestnancov, ktori sa v cisle oddelenia aj v plate zhoduju s nejakym (tym istym), ktory dostava premie.
paulis=> SELECT DISTINCT E1.ename, E1.deptno, E1.sal FROM emp AS E1, emp AS E2 WHERE E2.comm IS NOT null AND E2.deptno=E1.deptno AND E2.sal=E1.sal;

12. Vypiste meno, job a plat tych zamestnancov, ktory zarabaju viac ako kazdy z CLERKov.
paulis=> SELECT ename, job, sal FROM emp WHERE sal>(SELECT MAX(E.sal) FROM emp AS E WHERE E.job='CLERK');

13. Vypiste meno, plat a platovy stupen vsetkych zamestnancov, ktori pracuju v DALLASE.
paulis=> SELECT sal, grade FROM emp NATURAL JOIN dept RIGHT OUTER JOIN salgrade ON sal BETWEEN losal AND hisal WHERE loc='DALLAS';

14. Vypiste meno, job a premie (ak ich nedostavaju, napiste "ziadne premie") pre vsetkych pracovnikov oddelenia 30.
paulis=> SELECT ename, job, COALESCE(comm, 0) FROM emp WHERE deptno=30;


**************************************************************************************


1. Vypiste cislo, meno a plat tych zamestnancov, ktori zarabaju viac, ako je priemerny plat.
paulis=> SELECT empno, ename, sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);

2. Vypiste meno, cislo oddelenia a job tych zamestnancov, ktori pracuju v jednom oddeleni s nejakym zamestnancom, ktoreho meno obsahuje pismeno T.
paulis=> SELECT DISTINCT E1.ename, E1.deptno, E1.job FROM emp AS E1, emp AS E2 WHERE E1.deptno=E2.deptno AND E2.ename LIKE '%T%';

3. Vypiste meno a plat vsetkych zamestnancov, ktorych manazerom je KING.
paulis=> SELECT E1.ename, E1.sal FROM emp AS E1, emp AS E2 WHERE E2.ename='KING' AND E1.mgr=E2.empno;

4. Vypiste meno a plat vsetkych zamestnancov v oddeleni Sales.
paulis=> SELECT ename, sal FROM emp NATURAL JOIN dept WHERE dname='SALES';

5. Vypiste meno a plat tych zamestnancov, ktorych plat je vyssi	ako priemerny a pracuju v jednom oddeleni so zamestnancom, ktory ma v mene pismeno O.
paulis=> SELECT DISTINCT E1.ename, E1.sal FROM emp AS E1, emp AS E2 WHERE E1.sal>(SELECT AVG(sal) FROM emp) AND E1.deptno=E2.deptno AND E2.ename LIKE '%O%';

6. Vypiste meno, job a plat vsetkych zamestnancov, ktori maju rovnaky job ako zamestnanec cislo 7369 a zarabaju viac ako zamestnanec cislo 7876.
paulis=> SELECT DISTINCT E1.ename, E1.job, E1.sal FROM emp AS E1, emp AS E2, emp AS E3 WHERE E2.empno=7369 AND E1.job=E2.job AND E3.empno=7876 AND E1.sal>E3.sal;

7. Vypiste meno zamestnanca, cislo oddelenia a plat tych zamestnancov, ktori maju cislo oddelenia aj plat rovnaky, ako niekto, kto dostava premie.
paulis=> SELECT DISTINCT E1.ename, E1.deptno, E1.sal FROM emp AS E1, emp AS E2 WHERE E2.comm IS NOT null AND E2.deptno=E1.deptno AND E2.sal=E1.sal AND E2.empno!=E1.empno;

8. Vypiste cislo oddelenia, meno a plat tych zamestnancov, ktori zarabaju viac, ako je priemerny plat v ich oddeleni.
paulis=> SELECT deptno, ename, sal FROM emp WHERE sal>(SELECT AVG(E.sal) FROM emp AS E WHERE E.deptno=emp.deptno);

9. Vypiste cislo oddelenia, meno, job a plat tych zamestnancov, ktori vo svojom oddeleni zarabaju najmenej.
paulis=> SELECT deptno, ename, job, sal FROM emp WHERE sal=(SELECT MIN(E.sal) FROM emp AS E WHERE E.deptno=emp.deptno);

10. Vypiste meno, job a plat tych zamestnancov, ktori zarabaju viac ako kazdy z CLERKov.
paulis=> SELECT ename, job, sal FROM emp WHERE sal>(SELECT MAX(E.sal) FROM emp AS E WHERE E.job='CLERK');

11. Vypiste plat a platovy stupen vsetkych zamestnancov, ktori pracuju v DALLASE.
paulis=> SELECT sal, grade FROM emp NATURAL JOIN dept RIGHT OUTER JOIN salgrade ON sal BETWEEN losal AND hisal WHERE loc='DALLAS';


**************************************************************************************


1.Vypiste mena a osobne cisla zamestnancov, ktori pracuju v Chicagu.
paulis=> SELECT ename, empno FROM emp NATURAL JOIN dept WHERE loc='CHICAGO';

2.Vypiste mena vsetkych managerov z Dallasu.
paulis=> SELECT ename FROM emp NATURAL JOIN dept WHERE loc='CHICAGO' AND job='MANAGER';

3.Vypiste meno zamestnanca, meno oddelenia a lokalitu tych zamestnancov, ktori dostavaju premie.
paulis=> SELECT ename, dname, loc FROM emp NATURAL JOIN dept WHERE comm IS NOT null;

4.Vypiste mena zamestnancov, ktori maju najviac jedneho nadriadeneho.
paulis=> SELECT ename FROM emp WHERE NOT EXISTS (SELECT E1 FROM emp AS E1, emp AS E2, emp AS E3 WHERE E1.mgr=E2.empno AND E2.empno=E3.mgr AND E2.mgr IS NOT null AND E3.mgr IS NOT null AND E1.empno=emp.empno);

5.Vypiste mena zamestnancov, ktori nie su podriadenymi zamestnanca Ford.
paulis=> SELECT E.ename FROM emp AS E WHERE NOT E.mgr=(SELECT empno FROM emp WHERE ename='FORD');

6.Vypiste mena nadriadenych vsetkych zamestnancov, ktori pracuju v Dallase.
paulis=> SELECT E2.ename FROM emp AS E1 NATURAL JOIN dept, emp AS E2 WHERE Dept.loc='CHICAGO' AND E1.mgr=E2.empno;

!!! 7.Vypiste mena zamestnancov, ktori su v podnikovej hierarchii "rovnako vysoko" (t.j. maju rovnaku "vzdialenost od korena stromu", t.j. od prezidenta) ako zamestnanec Ford.

8.Vypiste mena zamestnancov, ktori boli prijati skor ako ich priami nadriadeni.
paulis=> SELECT E1.ename FROM emp AS E1, emp AS E2 WHERE E1.mgr=E2.empno AND E1.hiredate<E2.hiredate;

9.Vypiste meno a lokalitu oddelenia, v ktorom pracuje zamestnanec s najvacsim platom.
paulis=> SELECT ename, dname, loc FROM emp NATURAL JOIN dept WHERE sal=(SELECT MAX(sal) FROM emp);

10.Vypiste meno a lokalitu oddelenia, ktore zamestnava len zamestnancov s najvyssim platovym zaradenim.
SELECT ename, dname, loc FROM emp NATURAL JOIN dept, salgrade AS S WHERE S.grade=5 AND sal BETWEEN S.losal AND S.hisal;