Query - Scott

Introducció

La base de dades Scott …

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

{% sol %}

SELECT ename, sal
FROM emp
WHERE sal > 2850;

{% endsol %}

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

{% sol %}

SELECT ename, deptno
FROM emp
WHERE empno = 7566;

{% endsol %}

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

{% sol %}

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

{% endsol %}

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.

{% sol %}

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

{% endsol %}

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.

{% sol %}

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

{% endsol %}

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.

{% sol %}

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

{% endsol %}

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

{% sol %}

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

{% endsol %}

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

{% sol %}

SELECT ename, job
FROM emp
WHERE mgr IS NULL;

{% endsol %}

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

{% sol %}

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

{% endsol %}

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

{% sol %}

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

{% endsol %}

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.

{% sol %}

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

{% endsol %}

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.

{% sol %}

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

{% endsol %}

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

{% sol %}

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

{% endsol %}

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.

{% sol %}

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

{% endsol %}

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

{% sol %}

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

{% endsol %}

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

{% sol %}

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

{% endsol %}

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

{% sol %}

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

{% endsol %}

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.

{% sol %}

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

{% endsol %}

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.

{% sol %}

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

{% endsol %}

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

{% sol %}

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

{% endsol %}

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.

{% sol %}

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

{% endsol %}

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.

{% sol %}

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

{% endsol %}

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

{% sol %}

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

{% endsol %}

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.

{% sol %}

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;

{% endsol %}

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.

{% sol %}

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

{% endsol %}

Dades agregades. Funcions de grup

Google Docs