Postgres - Scott

Fes una consulta per visualitzar el nom i el salari dels empleats que guanyen més de 2850$.

SELECT ename, sal
FROM emp
WHERE sal > 2850;

Modifica la consulta anterior per visualitzar el nom i el salari de tots els empleats que tinguin un salari entre 1500$ i 2850$.

SELECT ename, sal
FROM emp
WHERE sal BETWEEN 1500 AND 2850;

Fes una consulta per visualitzar el nom del empleat i el codi del departament per l’empleat amb codi 7566.

SELECT ename, deptno
FROM emp
WHERE empno = 7566;

Mostra el nom del empleat, ofici i data d’alta, dels empleats contractats entre el 20 de febrer del 1981 i el 1 de maig del 1981. Ordena la consulta en ordre ascendent per data d’alta.

SELECT ename, job, hiredate
FROM emp
WHERE hiredate BETWEEN '20/FEB/81' AND '01/MAY/81'
ORDER BY hiredate;

Mostra el cognom i número de departament de tots els empleats dels departaments 10 i 30. Ordena’ls de forma descendent per cognom.

SELECT ename, deptno
FROM emp
WHERE deptno IN (10,30)
ORDER BY ename DESC;

Treu un llistat del cognom i salari dels empleats que guanyen més de 1500$ dels departaments 10 o 30. Etiqueta les columnes “Employee” i “Monthly Salary”, respectivament.

SELECT ename "Employee", sal "Monthly Salary"
FROM emp
WHERE sal > 1500 AND deptno IN (10, 30)

Mostra el nom i la data d’alta de cada empleat contractat durant l’any 1982.

SELECT ename, hiredate
FROM emp
WHERE extract(YEAR FROM hiredate) = 1982;

Mostra el nom i l’ofici de tots els empleats que no tenen un cap assignat.

SELECT ename, job
FROM emp
WHERE mgr IS NULL;

Mostra el nom, salari i comissió de tots els empleats que guanyen comissions. Ordeneu per salari i comissió en ordre descendent.

SELECT ename, sal , comm
FROM emp
WHERE comm IS NOT NULL
ORDER BY sal DESC, comm DESC;

Mostra els noms de tots els empleats que tinguin una A en la tercera lletra del seu nom.

SELECT ename
FROM emp
WHERE ename LIKE '__A%';

Mostra el nom de tots els empleats que tinguin dos L en el seu nom i que siguin del departament 30 o que el seu cap sigui el 7782.

SELECT ename
FROM emp
WHERE ename LIKE '%L%L%' AND (deptno = 30 OR mgr = 7782);

Mostra el nom, ofici i salari de tots els empleats que tinguin com ofici Clerk o Analyst i el seu salari no sigui igual a 1000, 3000 o 5000 dòlars.

SELECT ename, job, sal
FROM emp
WHERE job IN ('CLERK', 'ANALYST') AND sal NOT IN (1000, 3000, 5000);

Mostra el nom, salari i comissió de tots els empleats que tinguin una comissió superior al seu salari incrementat un 10%.

SELECT ename "Employee", sal "Monthly Salary", comm
FROM emp
WHERE comm > sal * 1.1

Fes una consulta per mostrar el nom, número de departament i nom de departament de tots els empleats.

SELECT e.ename, e.deptno, d.dname
FROM emp e natural join dept d

Mostra els oficis diferents que hi ha al departament 30.

SELECT distinct e.job, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.deptno=30;

Fes una consulta per mostrar el nom de l’empleat, nom del departament i localitat de tots els empleats que tenen comissió.

SELECT e.ename, d.dname, d.loc
FROM emp e NATURAL JOIN dept d
WHERE e.comm IS NOT NULL;

Mostra el nom de l’empleat i nom del departament de tots els empleats que tenen una A en el seu nom..

SELECT e.ename, d.dname
FROM emp e NATURAL JOIN dept d
WHERE e.ename LIKE '%A%';

Fes una consulta per mostrar el nom, ofici, número del departament i nom del departament de tots els empleats que treballen a DALLAS.

SELECT e.ename, e.job, e.deptno, d.dname
FROM emp e NATURAL JOIN dept d
WHERE upper(d.loc) = 'DALLAS';

Mostra el nom de l’empleat i el número de l’empleat juntament amb el nom dels seus caps i el número del seu cap. Etiqueta les columnes com Employee, Emp#, Manager i Mgr#, respectivament.

SELECT e.ename "Employee", e.empno "Emp#", j.ename "Manager", j.empno "Mgr#"
FROM emp e JOIN emp j on e.mgr = j.empno;

Modifica la consulta perquè també inclogui a King (que no té cap).

SELECT e.ename "Employee", e.empno "Emp#", j.ename "Manager", j.empno "Mgr#"
FROM emp e LEFT JOIN emp j on e.mgr = j.empno

Fes una consulta que mostri el nom de l’empleat, número del departament juntament amb el nom dels seus companys de departament. Poseu etiquetes apropiades.

SELECT e.deptno DEPARTAMENT, e.ename EMPLOYEE, c.ename COLLEAGUE
FROM emp e JOIN emp c USING(deptno)
WHERE e.empno <> c.empno;

Mostra l’estructura de la taula SALGRADE. Crea una consulta que mostri el nom, ofici, nom del departament, salari i grau de tots els empleats.

SELECT e.ename, e.job, d.dname, e.sal, s.grade
FROM emp e join dept d using(deptno), salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal

Fes una consulta per mostrar el nom i data de contractació de qualsevol empleat contractat després de Blake.

SELECT e.ename, e.hiredate
FROM emp e, emp b
WHERE b.ename = 'BLAKE' AND e.hiredate > b.hiredate;

Mostra tots els noms dels empleats i les dates de contractació juntament amb el nom dels seus caps i dates de contractació de tots els empleats contractats abans que els seus caps. Etiqueta les columnes com Employee, Emp Hiredate, Manager i Mgr Hiredate respectivament.

SELECT e.ename "Employee", e.hiredate "Emp Hiredate", j.ename "Manager", j.hiredate "Mgr Hiredate"
FROM emp e, emp j
WHERE e.mgr = j.empno AND e.hiredate < j.hiredate;

Fes una consulta que mostri els noms dels empleats i el salari mitjançant asteriscs. Cada asterisc significa un centenar de dòlars. Ordena les dades en ordre descendent per salari. Mostra el resultat en una sola columna, amb l’etiqueta EMPLOYEE_AND_THEIR_SALARIES.

SELECT rpad(ename,10,' ') || lpad(' ',(sal/100) + 1,'*') EMPLOYEE_AND_THEIR_SALARIS
FROM emp
ORDER BY sal DESC;

Les funcions de grup treballen amb molts registres per a produir un resultat.

Les funcions de grup inclouen nuls en els càlculs.

La cláusula WHERE restringeix registres abans de la inclusió en un càlcul de grup.

Mostra sobre el salari: màxim, mínim, suma i mitjana aritmètica, per a tots els empleats. Arrodoneix els resultats a la posició decimal.

SELECT ROUND(MAX(sal),0) "Maximum",
    ROUND(MIN(sal),0) "Minimum",
    ROUND(SUM(sal),0) "Sum",
    ROUND(AVG(sal),0) "Average"
FROM emp;
</Solution>

Modifica la consulta per mostrar: màxim, mínim, suma i mitjana aritmètica de salaris per a cada ofici.

<Solution>
```sql
SELECT job, ROUND(MAX(sal),0) "Maximum",
    ROUND(MIN(sal),0) "Minimum",
    ROUND(SUM(sal),0) "Sum",
    ROUND(AVG(sal),0) "Average"
FROM emp
GROUP BY job;
</Solution>


Escriu una consulta que mostri el nombre de persones que tenen el mateix ofici.

<Solution>
```sql
SELECT job, COUNT(*)
FROM emp
GROUP BY job;

Determina el nombre total de directors. Etiqueta la columna com “nº de directors”.

SELECT COUNT(DISTINCT mgr) "Number of Managers"
FROM emp;

Escriu una consulta que mostri la diferència entre el salari més alt i el més baix de la empresa. Etiqueta la columna com “DIFFERENCE”.

SELECT MAX(sal)-MIN(sal) DIFFERENCE
FROM emp;

Mostra el número del director i salari de l’empleat amb menor salari amb dependència d’aquest director. Exclou a qualsevol empleat que no tingui director. Exclou qualsevol grup, que tingui un salari mínim inferior a 1000$. Classifica el resultat en ordre descendent de salaris.

SELECT mgr, MIN(sal)
FROM emp
WHERE mgr IS NOT NULL
GROUP BY mgr
HAVING MIN(sal) > 1000
ORDER BY MIN(sal) DESC;

Escriu una consulta que mostri el nom del departament, localitat, nombre d’empleats i la mitjana de salaris, per a tots els empleats de cada departament. Etiqueta les columnes com DNAME, LOC, Number of People i Salari, respectivament.

SELECT d.dname, d.loc, COUNT(*) "Number of People", ROUND(AVG(sal),2) "Salary"
FROM emp e natural join dept d
GROUP BY d.dname, d.loc;

Fes una consulta que mostri per cada any el número d’empleats que es van contractar.

select extract(year from hiredate), count(*)
from emp
group by extract(year from hiredate)

Crea una matriu que mostri l’ofici, salari corresponent segons departament i el salari total per aquest ofici de tots els departaments. Etiqueta les columnes com Job, Dept 10, Dept 20, Dept 30 i Total.

SELECT job "Job", SUM(DECODE(deptno, 10, sal)) "Dept 10",
SUM(DECODE(deptno, 20, sal)) "Dept 20",
SUM(DECODE(deptno, 30, sal)) "Dept 30",
SUM(sal) "Total"
FROM emp
GROUP BY job;

Escriu una consulta que mostri nom i data d’alta de tots els empleats que treballen en el mateix departament que Blake (exclou a Blake).

SELECT ename, hiredate
FROM emp
WHERE deptno = (   // si només hi ha un blake
    SELECT deptno
    FROM emp
    WHERE ename='BLAKE')
AND ename != 'BLAKE';

Fes una consulta que mostri el número i nom de tots els empleats que guanyen més que la mitjana de salaris. Classifica el resultat en ordre descendent de salaris.

SELECT empno, ename
FROM emp
WHERE sal > (
    SELECT AVG(sal)
    FROM emp )
ORDER BY sal DESC;

Escriu una consulta que mostri el número i nom de tots els empleats que treballen en un departament amb qualsevol empleat que tingui un nom que contingui una “T”.

SELECT empno, ename
FROM emp
WHERE deptno IN (
    SELECT deptno // distinct?
    FROM emp
    WHERE ename LIKE '%T%');

Mostra el nom, número de departament i ofici de tots els empleats que treballen en un departament que es trobi a Dallas.

SELECT ename, deptno, job
FROM emp
WHERE deptno IN (
    SELECT deptno
    FROM dept
    WHERE loc='DALLAS');

Mostra el nom i el salari de tots els empleats que depenguin de “King”.

SELECT ename, sal
FROM emp
WHERE mgr IN (
    SELECT empno
    FROM emp
    WHERE ename='KING');

Mostra el número, nom i ofici de tots els empleats del departament “Sales”.

SELECT deptno, ename, job
FROM emp
WHERE deptno IN (
    SELECT deptno
    FROM dept
    WHERE dname = 'SALES');

Modifica la consulta (3) per a que mostri el número, nom i salari de tots els empleats que guanyin més que la mitjana de salaris i que treballin en un departament en el que hi hagi algun empleat que contingui una “T” en el seu nom.

SELECT empno, ename, sal
FROM emp
WHERE sal > (
    SELECT AVG(sal)
    FROM emp)
AND deptno IN (
    SELECT deptno
    FROM emp
    WHERE ename LIKE '%T%');

Escriu una consulta que mostri el nom, número de departament i salari de qualsevol empleat, que tinguin un número de departament i salari que es corresponguin -els dos- amb el número de departament i salari de qualsevol empleat que tingui comissió.

SELECT ename, deptno, sal
FROM emp
WHERE (deptno, sal) IN (
    SELECT deptno, sal
    FROM emp
    WHERE comm IS NOT NULL);

Mostra el nom, nom del departament i salari, de qualsevol empleat que tingui un salari i comissió que es corresponguin -els dos-, amb el salari i comissió de qualsevol empleat de Dallas.

SELECT ename, dname, sal
FROM emp natural join dept
WHERE (sal, coalesce(comm,0)) IN (
    SELECT sal , coalesce(comm,0)
    FROM emp natural join dept
WHERE loc = 'DALLAS');

Fes una consulta per mostrar el nom, data d’alta i salari de tots els empleats que tinguin el mateix salari i comissió que Scott.

SELECT ename, hiredate, sal
FROM emp
WHERE (sal, coalesce(comm,0)) = (
    SELECT sal , coalesce(comm,0)
    FROM emp
    WHERE upper(ename) = 'SCOTT')
AND upper(ename) <> 'SCOTT';

Fes una consulta per mostrar els empleats que guanyin un salari superior al salari de qualsevol empleat “CLERK”. Ordena el resultat pel salari de forma descendent.

SELECT ename, job, sal
FROM emp
WHERE sal > ANY (
    SELECT sal
    FROM emp
    WHERE job = 'CLERK')
AND job != 'CLERK'
ORDER BY sal DESC;