Taula de continguts

Introducció

La base de dades Scott es va instal.lar en l'activitat Postgres.

Diagrama

classDiagram
direction LR

class EMP {
  # EMPNO
  ENAME
  JOB
  MGR
  HIREDATE
  SAL
  COMM
}

EMP ..> DEPT : DEPTNO

class CUSTOMER {
  # CUSTID
  NAME
  ADDRESS 
  CITY
  STATE
  ZIP
  AREA
  PHONE
}

CUSTOMER ..> EMP : REPID

class DEPT {
  # DEPTNO
  DNAME
  LOC
}

class ORD {
  # ORDID
  ORDERDATE
  COMMPLAN
  SHIPDATE
  TOTAL
}

ORD ..> CUSTOMER : CUSTID

class PRODUCT {
  # PRODID
  DESCRIP
}

class ITEM {
  # ITEMID
  ACTUALPRICE
  QTY
  ITEMTOT
}

ITEM --> ORD: # ORDID
ITEM ..> PRODUCT: PRODID

class PRICE {
  # STARTDATE
  STDPRICE
  MINPRICE
  ENDATE
}

PRICE --> PRODUCT : # PRODID

Restricció i classificació de les dades

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

Solució

SELECT ename, sal
FROM emp
WHERE sal > 2850;

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

Solució

SELECT ename, deptno
FROM emp
WHERE empno = 7566;

3.- Modifica la consulta (1) per visualitzar el nom i el salari de tots els empleats que tinguin un salari entre 1500$ i 2850$.

Solució

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

4.- 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.

Solució

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

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

Solució

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

6. Modifica la consulta (3) per treure 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.

Solució

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

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

Solució

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

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

Solució

SELECT ename, job
FROM emp
WHERE mgr IS NULL;

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

Solució

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

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

Solució

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

11.- 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.

Solució

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

12.- 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.

Solució

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

13.- Modifica la consulta (6) per mostrar el nom, salari i comissió de tots els empleats que tinguin una comissió superior al seu salari incrementat un 10%.

Solució

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

Visualització de dades a partir de varies taules

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

Solució

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

2.- Mostra els oficis diferents que hi ha al departament 30.

Solució

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

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

Solució

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

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

Solució

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

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

Solució

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

6.- 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.

Solució

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

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

Solució

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

8.- 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.

Solució

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

9.- 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.

Solució

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

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

Solució

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

11.- 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.

Solució

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;

12.- 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.

Solució

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

Dades agregades. Funcions de grup

Google Docs