Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF2: Llenguatges SQL»

De wikiserver
Dreceres ràpides: navegació, cerca
(Es crea la pàgina amb «==T1: Escriptura bàsica de sentències == ==T2: Restricció i classificació de les dades == ==T3: Funcions a nivell de fila == ==T4: Visualització de dades a partir…».)
 
(→‎Repàs Consultes BBDD HR)
 
(300 revisions intermèdies per 5 usuaris que no es mostren)
Línia 1: Línia 1:
 +
==Connectar-se a la base de dades==
 +
 +
'''Usuari SCOTT:'''
 +
<pre>
 +
SQL> CONNECT SCOTT
 +
 +
Password: TIGER
 +
 +
</pre>
 +
 +
'''Aixecar la base de dades:'''
 +
<pre>
 +
SQL> CONNECT SYS AS SYSDBA;
 +
 +
Password: MANAGER
 +
 +
SQL> STARTUP;
 +
 +
SQL> DISCONNECT;
 +
</pre>
 +
 +
==Presentació Taules Usuari SCOTT ==
 +
<pre>
 +
    Nom Usuari: scott
 +
    contrassenya: tiger
 +
</pre>
 +
SQL>'''DESC''' emp
 +
<pre>                         
 +
Nombre                        Nulo    Tipo
 +
------------------------------ -------- ----------------
 +
EMPNO                          NOT NULL NUMBER(4)      Codi d'empleat.
 +
ENAME                                  VARCHAR2(10)    Cognom.
 +
JOB                                    VARCHAR2(9)    Ofici.
 +
MGR                                    NUMBER(4)      Codi del cap de l'empleat.
 +
HIREDATE                                DATE            Data de contractació.
 +
SAL                                    NUMBER(7,2)    Salari.
 +
COMM                                    NUMBER(7,2)    Comissió del empleats comercials (SALESMAN).
 +
DEPTNO                                  NUMBER(2)      Codi del departament de l'empleat.
 +
</pre>
 +
SQL>'''SELECT '''* '''FROM''' emp
 +
<pre>
 +
EMPNO  ENAME  JOB        MGR  HIREDATE  SAL    COMM  DEPTNO
 +
------  ------  ---------  -----  ---------  ----  -----  ------
 +
7369 SMITH CLERK   7902   17/12/80    800  (null)    20
 +
7499 ALLEN SALESMAN  7698   20/02/81  1600    300      30
 +
7521 WARD SALESMAN  7698   22/02/81  1250    500      30
 +
7566 JONES MANAGER   7839   02/04/81  2975  (null)    20
 +
7654 MARTIN SALESMAN  7698   28/09/81  1250  1400      30
 +
7698 BLAKE MANAGER   7839   01/05/81  2850  (null)    30
 +
7782 CLARK MANAGER   7839   09/06/81  2450  (null)    10
 +
7788 SCOTT ANALYST   7566   19/04/87  3000  (null)    20
 +
7839 KING PRESIDENT (null)  17/11/81  5000  (null)    10
 +
7844 TURNER SALESMAN  7698   08/09/81  1500      0      30
 +
7876 ADAMS CLERK   7788   23/05/87  1100  (null)    20
 +
7900 JAMES CLERK   7698   03/12/81    950  (null)    30
 +
7902 FORD ANALYST   7566   03/12/81  3000  (null)    20
 +
7934 MILLER CLERK   7782   23/01/82  1300  (null)    10
 +
</pre>
 +
SQL>'''DESC''' dept
 +
<pre>                         
 +
Nombre                        Nulo    Tipo
 +
------------------------------ -------- ----------------
 +
DEPTNO                          NOT NULL NUMBER(2)      Codi departament
 +
DNAME                                    VARCHAR2(14)    Nom departament. 
 +
LOC                                      VARCHAR2(13)    Localització.
 +
</pre>
 +
SQL>'''SELECT '''* '''FROM''' dept
 +
<pre>
 +
DEPTNO      DNAME      LOC       
 +
------  -----------  ----------
 +
    10 ACCOUNTING    NEW YORK
 +
    20 RESEARCH      DALLAS
 +
    30 SALES       CHICAGO
 +
    40 OPERATIONS    BOSTON
 +
</pre>
 +
SQL>'''DESC''' salgrade
 +
<pre>                         
 +
Nombre                        Nulo    Tipo
 +
------------------------------ -------- ------------
 +
GRADE                                  NUMBER      Nivell salarial. Cada empleat pertany a un nivell en funció del seu salari.
 +
LOSAL                                  NUMBER      Salari mínim del nivell. 
 +
HISAL                                  NUMBER      Salari màxim del nivell.
 +
</pre>
 +
SQL>'''SELECT '''* '''FROM''' salgrade
 +
<pre>
 +
GRADE  LOSAL  HISAL       
 +
-----  -----  ----- 
 +
1 700 1200
 +
2 1201 1400
 +
3 1401 2000
 +
4 2001 3000
 +
5 3001 9999
 +
</pre>
 +
===Disseny de la BD SCOTT simple===
 +
 +
[[Fitxer:BD_SCOTT_SIMPLE.png|1200px|left]]
 +
 +
 +
La taula SALGRADE presenta un cas especial en el que una taula queda "connectada" a la resta mitjançant un non equi join (en aquest cas mitjançant un rang de valors de salaris).
 +
 
==T1: Escriptura bàsica de sentències ==
 
==T1: Escriptura bàsica de sentències ==
 +
 +
1. Inicii una sessió de SQL*Plus utilitzant l'ID d'usuari i password facilitat pel professor.
 +
 +
2. Les comandes SQL*PLUS accedeixen a la base de dades.
 +
 +
<pre>
 +
Cert / Fals
 +
</pre>
 +
3. S'executarà correctament la sentència SELECT?
 +
 +
<pre>
 +
Cert / Fals
 +
 +
SQL> SELECT rownum, ename, job, sal Salary
 +
  2  FROM  emp;
 +
</pre>
 +
 +
4. S'executarà correctament la sentència SELECT?
 +
 +
<pre>
 +
Cert / Fals
 +
 +
SQL> SELECT *
 +
  2  FROM  salgrade;
 +
</pre>
 +
 +
5. Hi ha tres errors de codi en aquesta sentència. Pots identificar-los?
 +
 +
<pre>
 +
SQL> SELECT empno, ename, salary x 12 ANNUAL SALARY
 +
  2  FROM  emp;
 +
</pre>
 +
 +
6. Mostra l'estructura de la taula DEPT. Selecciona totes les dades de la taula DEPT.
 +
 +
7. Mostra l'estructura de la taula EMP. Fes una consulta per visualitzar el número d'empleat, cognom, ofici i data d'alta.
 +
Guarda la sentència SQL en un fitxer anomenat plq7.sql.
 +
 +
8. Executeu la consulta del fitxer plq7.sql.
 +
 +
9. Fes una consulta per visualitzar només els oficis de la taula EMP.
 +
 +
<pre>
 +
JOB
 +
-----------
 +
ANALYST
 +
CLERK
 +
MANAGER
 +
PRESIDENT
 +
SALESMAN
 +
</pre>
 +
 +
10. Carrega plq7.sql en el búfer SQL. Posa-li nom als encapçalaments: Emp #, Employee, Job i Hire Date.
 +
 +
<pre>
 +
Emp #                  Employee  Job      Hire Date               
 +
---------------------- ---------- --------- -------------------------
 +
7369                  SMITH      CLERK    17/12/80                 
 +
7499                  ALLEN      SALESMAN  20/02/81                 
 +
7521                  WARD      SALESMAN  22/02/81                 
 +
7566                  JONES      MANAGER  02/04/81                 
 +
7654                  MARTIN    SALESMAN  28/09/81                 
 +
7698                  BLAKE      MANAGER  01/05/81                 
 +
7782                  CLARK      MANAGER  09/06/81                 
 +
7788                  SCOTT      ANALYST  19/04/87                 
 +
7839                  KING      PRESIDENT 17/11/81                 
 +
7844                  TURNER    SALESMAN  08/09/81                 
 +
7876                  ADAMS      CLERK    23/05/87                 
 +
7900                  JAMES      CLERK    03/12/81                 
 +
7902                  FORD      ANALYST  03/12/81                 
 +
7934                  MILLER    CLERK    23/01/82
 +
</pre>
 +
 +
11. Visualitza el nom concatenat amb l'ofici, separat per una coma i i un espai i etiquete la columna amb Employee and Title.
 +
 +
<pre>
 +
Employee and Title 
 +
-------------------
 +
SMITHCLERK         
 +
ALLENSALESMAN     
 +
WARDSALESMAN       
 +
JONESMANAGER       
 +
MARTINSALESMAN     
 +
BLAKEMANAGER       
 +
CLARKMANAGER       
 +
SCOTTANALYST       
 +
KINGPRESIDENT     
 +
TURNERSALESMAN     
 +
ADAMSCLERK         
 +
JAMESCLERK         
 +
FORDANALYST       
 +
MILLERCLERK
 +
</pre>
 +
 +
12. Fes una consulta per visualitzar totes les dades de la taula EMP. Separa cada columna amb una coma. Etiqueta la columna com THE_OUTPUT.
 +
 +
<pre>
 +
THE_OUTPUT
 +
---------------------------------------------
 +
7369,SMITH,CLERK,7902,17/12/80,800,,20
 +
7499,ALLEN,SALESMAN,7698,20/02/81,1600,300,30
 +
7521,WARD,SALESMAN,7698,22/02/81,1250,500,30
 +
7566,JONES,MANAGER,7839,02/04/81,2975,,20
 +
7654,MARTIN,SALESMAN,7698,28/09/81,1250,1400,30
 +
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
 +
7782,CLARK,MANAGER,7839,09/06/81,2450,,10
 +
7788,SCOTT,ANALYST,7566,19/04/87,3000,,20
 +
7839,KING,PRESIDENT,,17/11/81,5000,,10
 +
7844,TURNER,SALESMAN,7698,08/09/81,1500,0,30 
 +
7876,ADAMS,CLERK,7788,23/05/87,1100,,20
 +
7900,JAMES,CLERK,7698,03/12/81,950,,30 
 +
7902,FORD,ANALYST,7566,03/12/81,3000,,20
 +
7934,MILLER,CLERK,7782,23/01/82,1300,,10
 +
</pre>
 +
  
 
==T2: Restricció i classificació de les dades ==
 
==T2: 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$. Guarda la sentència SQL en un fitxer anomenat p2q1.sql. Executa la consulta.
 +
 +
<pre>
 +
ENAME      SAL
 +
-----      ----
 +
JONES    2975
 +
SCOTT      3000
 +
KING   5000
 +
FORD   3000           
 +
</pre>
 +
 +
2. Fes una consulta per visualitzar el nom del empleat i el codi del departament per l'empleat amb codi 7566.
 +
 +
<pre>
 +
ENAME      DEPTNO
 +
------    ------
 +
JONES     20
 +
           
 +
</pre>
 +
3. Modifica p2q1.sql per visualitzar el nom i el salari de tots els empleats que tinguin un salari entre 1500$ i 2850$. Guarda a questa sentència SQL en un fitxer anomenat p2q3.sql. Executa la consulta.
 +
 +
<pre>
 +
ENAME      SAL
 +
------    ----
 +
ALLEN   1600
 +
BLAKE   2850
 +
CLARK   2450
 +
TURNER   1500
 +
</pre>           
 +
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.
 +
 +
<pre>
 +
ENAME      JOB            HIREDATE
 +
------    --------        -------- 
 +
ALLEN   SALESMAN   20/02/81
 +
WARD   SALESMAN   22/02/81
 +
JONES   MANAGER   02/04/81
 +
BLAKE   MANAGER   01/05/81
 +
</pre>
 +
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.
 +
 +
<pre>
 +
ENAME      DEPTNO
 +
------    ------
 +
ALLEN       30
 +
BLAKE       30
 +
CLARK          10
 +
JAMES       30
 +
KING       10
 +
MARTIN        30
 +
MILLER       10
 +
TURNER       30
 +
WARD       30
 +
</pre>
 +
6. Modifica p2q3.sql 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. Tornar a guardar la sentència en un fitxer anomenat p2q6.sql. Executa la consulta.
 +
 +
<pre>
 +
Employee  Monthly Salary
 +
--------  --------------
 +
ALLEN             1600
 +
BLAKE             2850
 +
CLARK             2450
 +
KING             5000
 +
</pre>
 +
7. Mostra el nom i la data d'alta de cada empleat contractat durant l'any 1982.
 +
 +
<pre>
 +
ENAME      HIREDATE
 +
------    --------
 +
MILLER   23/01/82
 +
</pre>
 +
8. Mostra el nom i l'ofici de tots els empleats que no tenen un cap assignat.
 +
 +
<pre>
 +
ENAME      JOB         
 +
------    --------
 +
KING      PRESIDENT
 +
</pre>
 +
9. Mostra el nom, salari i comissió de tots els empleats que guanyen comissions. Ordeneu per salari i comissió en ordre descendent.
 +
 +
<pre>
 +
ENAME      SAL      COMM                 
 +
-------    ----    ----
 +
ALLEN      1600      300                   
 +
TURNER    1500        0                     
 +
MARTIN    1250    1400                 
 +
WARD      1250      500   
 +
</pre>
 +
10. Mostra els noms de tots els empleats que tinguin una A en la tercera lletra del seu nom.
 +
 +
<pre>
 +
ENAME     
 +
-------
 +
BLAKE     
 +
CLARK     
 +
ADAMS
 +
</pre>
 +
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.
 +
 +
<pre>
 +
ENAME     
 +
-------
 +
ALLEN     
 +
MILLER   
 +
</pre>
 +
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.
 +
 +
<pre>
 +
ENAME      JOB      SAL                   
 +
-------    -----    ----
 +
SMITH      CLERK      800                   
 +
ADAMS      CLERK    1100                 
 +
JAMES      CLERK      950                   
 +
MILLER    CLERK    1300
 +
</pre>
 +
13. Modifica p2q6.sql per mostrar el nom, salari i comissió de tots els empleats que tinguin una comissió superior al seu salari incrementat un 10%. Executa la consulta. Guarda-la com p2q13.sql.
 +
 +
<pre>
 +
Employee  Monthly Salary    COMM                 
 +
---------- --------------    ----
 +
MARTIN              1250    1400       
 +
</pre>
  
 
==T3: Funcions a nivell de fila ==
 
==T3: Funcions a nivell de fila ==
 +
 +
1. Fes una consulta que mostri la data actual. Etiqueta la columna com a Date.
 +
 +
<pre>
 +
Date                   
 +
--------
 +
10/12/13               
 +
</pre>
 +
 +
2. Mostra el número de l'empleat, nom, salari i increment del salari del 15% sense decimals. Etiqueta la columna com a New Salary. Guarda la sentència SQL en un fitxer anomenat p3q2.sql.
 +
 +
 +
3. Executa la consulta del fitxer p3q2.sql.
 +
 +
<pre>
 +
EMPNO                  ENAME      SAL                    New Salary           
 +
---------------------- ---------- ---------------------- ----------------------
 +
7369                  SMITH      800                    920                   
 +
7499                  ALLEN      1600                  1840                 
 +
7521                  WARD      1250                  1438                 
 +
7566                  JONES      2975                  3421                 
 +
7654                  MARTIN    1250                  1438                 
 +
7698                  BLAKE      2850                  3278                 
 +
7782                  CLARK      2450                  2818                 
 +
7788                  SCOTT      3000                  3450                 
 +
7839                  KING      5000                  5750                 
 +
7844                  TURNER    1500                  1725                 
 +
7876                  ADAMS      1100                  1265                 
 +
7900                  JAMES      950                  1093                 
 +
7902                  FORD      3000                  3450                 
 +
7934                  MILLER    1300                  1495                 
 +
</pre>
 +
 +
4. Modifica la consulta p3q2.sql per afegir una columna que resti el antic salari del nou. Etiqueteu la columna com Increase. Torna a executar la consulta.
 +
 +
<pre>
 +
EMPNO                  ENAME      SAL                    New Salary            Increase             
 +
---------------------- ---------- ---------------------- ---------------------- ----------------------
 +
7369                  SMITH      800                    920                  120                   
 +
7499                  ALLEN      1600                  1840                  240                   
 +
7521                  WARD      1250                  1438                  188                   
 +
7566                  JONES      2975                  3421                  446                   
 +
7654                  MARTIN    1250                  1438                  188                   
 +
7698                  BLAKE      2850                  3278                  428                   
 +
7782                  CLARK      2450                  2818                  368                   
 +
7788                  SCOTT      3000                  3450                  450                   
 +
7839                  KING      5000                  5750                  750                   
 +
7844                  TURNER    1500                  1725                  225                   
 +
7876                  ADAMS      1100                  1265                  165                   
 +
7900                  JAMES      950                  1093                  143                   
 +
7902                  FORD      3000                  3450                  450                   
 +
7934                  MILLER    1300                  1495                  195 
 +
</pre>
 +
 +
5. Mostra el nom de l'empleat, data de contractació i data de revisió del salari, la cual és el primer Dilluns desprès de sis mesos de servei. Etiqueu la columna com REVIEW. Formata les dates per a que apareixin similars a "Sunday, the Seventh of September, 1981".
 +
 +
<pre>
 +
EMPNO                  HIREDATE                  REVIEW
 +
---------------------- ------------------------- ------------------------------------------
 +
7369                  17/12/80                  Lunes, the Twenty-Second of Junio,  1981                                                                 
 +
7499                  20/02/81                  Lunes, the Twenty-Fourth of Agosto, 1981                                                                 
 +
7521                  22/02/81                  Lunes, the Twenty-Fourth of Agosto, 1981                                                                 
 +
7566                  02/04/81                  Lunes, the Fifth of Octubre, 1981                                                                         
 +
7654                  28/09/81                  Lunes, the Twenty-Ninth of Marzo, 1982                                                                   
 +
7698                  01/05/81                  Lunes, the Second of Noviembre, 1981                                                                     
 +
7782                  09/06/81                  Lunes, the Fourteenth of Diciembre, 1981                                                                 
 +
7788                  19/04/87                  Lunes, the Twenty-Sixth of Octubre, 1987                                                                 
 +
7839                  17/11/81                  Lunes, the Twenty-Fourth of Mayo, 1982                                                                   
 +
7844                  08/09/81                  Lunes, the Fifteenth of Marzo, 1982                                                                       
 +
7876                  23/05/87                  Lunes, the Thirtieth of Noviembre, 1987                                                                   
 +
7900                  03/12/81                  Lunes, the Seventh of Junio, 1982                                                                         
 +
7902                  03/12/81                  Lunes, the Seventh of Junio, 1982                                                                         
 +
7934                  23/01/82                  Lunes, the Twenty-Sixth of Julio, 1982                                                                   
 +
</pre>
 +
6. Per a cada empleat mostra el nom de l'empleat i calcula el nombre de mesos entre avui i la data de contractació. Etiqueta la consulta com MONTHS_WORKED. Ordena els resultats per el nombre de mesos treballats. Arrodoneix el nombre de mesos cap amunt fins al proper nombre enter.
 +
 +
<pre>
 +
ENAME      MONTHS_WORKED         
 +
---------- --------------
 +
ADAMS      319                   
 +
SCOTT      320                   
 +
MILLER    383                   
 +
FORD      384                   
 +
JAMES      384                   
 +
KING      385                   
 +
MARTIN    386                   
 +
TURNER    387                   
 +
CLARK      390                   
 +
BLAKE      391                   
 +
JONES      392                   
 +
WARD      394                   
 +
ALLEN      394                   
 +
SMITH      396                   
 +
</pre>
 +
 +
 +
7. Escriu una consulta que mostri la següent informació de cada empleat:
 +
<nom empleat> earns <salari> monthly but wants <3 vegades més>. Etiqueta la columna com Dream Salaries.
 +
 +
<pre>
 +
Dream Salaries                                             
 +
-----------------------------------------------------------
 +
SMITH earns $800.00 monthly but wants $2,400.00.           
 +
ALLEN earns $1,600.00 monthly but wants $4,800.00.         
 +
WARD earns $1,250.00 monthly but wants $3,750.00.         
 +
JONES earns $2,975.00 monthly but wants $8,925.00.         
 +
MARTIN earns $1,250.00 monthly but wants $3,750.00.       
 +
BLAKE earns $2,850.00 monthly but wants $8,550.00.         
 +
CLARK earns $2,450.00 monthly but wants $7,350.00.         
 +
SCOTT earns $3,000.00 monthly but wants $9,000.00.         
 +
KING earns $5,000.00 monthly but wants $15,000.00.         
 +
TURNER earns $1,500.00 monthly but wants $4,500.00.       
 +
ADAMS earns $1,100.00 monthly but wants $3,300.00.         
 +
JAMES earns $950.00 monthly but wants $2,850.00.           
 +
FORD earns $3,000.00 monthly but wants $9,000.00.         
 +
MILLER earns $1,300.00 monthly but wants $3,900.00.       
 +
</pre>
 +
 +
8. Fes una consulta per mostrar el nom i salari de tots els empleats. Formata el salari per a que tingui una longitud de 15 caràcters i ompli el costat esquerra amb $. Etiqueta la columna com SALARY.
 +
 +
<pre>
 +
ENAME      SALARY         
 +
---------- ---------------
 +
SMITH      $$$$$$$$$$$$800
 +
ALLEN      $$$$$$$$$$$1600
 +
WARD      $$$$$$$$$$$1250
 +
JONES      $$$$$$$$$$$2975
 +
MARTIN    $$$$$$$$$$$1250
 +
BLAKE      $$$$$$$$$$$2850
 +
CLARK      $$$$$$$$$$$2450
 +
SCOTT      $$$$$$$$$$$3000
 +
KING      $$$$$$$$$$$5000
 +
TURNER    $$$$$$$$$$$1500
 +
ADAMS      $$$$$$$$$$$1100
 +
JAMES      $$$$$$$$$$$$950
 +
FORD      $$$$$$$$$$$3000
 +
MILLER    $$$$$$$$$$$1300
 +
</pre>
 +
 +
9. Escriu una consulta que visualitzi el nom de l'empleat amb la primera lletra en majúscules, la resta en minúscules i la longitud dels seus noms, para tots els empleats que comencin per J, A o M. Doneu a les columnes una etiqueta apropiada.
 +
 +
<pre>
 +
Name      Length               
 +
---------- -----------
 +
Allen      5                     
 +
Jones      5                     
 +
Martin    6                     
 +
Adams      5                     
 +
James      5                     
 +
Miller    6       
 +
</pre>
 +
 +
10. Mostra el nom, la data de contractació i dia de la setmana que va començar l'empleat. Etiqueta la columna com DAY. Ordena els resultats per el dia de la setmana començant per Dilluns.
 +
 +
<pre>
 +
ENAME      HIREDATE                  DAY                                 
 +
---------- ------------------------- --------------
 +
MARTIN    28/09/81                  LUNES                               
 +
CLARK      09/06/81                  MARTES                             
 +
TURNER    08/09/81                  MARTES                             
 +
KING      17/11/81                  MARTES                             
 +
SMITH      17/12/80                  MIÉRCOLES                           
 +
JAMES      03/12/81                  JUEVES                             
 +
JONES      02/04/81                  JUEVES                             
 +
FORD      03/12/81                  JUEVES                             
 +
ALLEN      20/02/81                  VIERNES                             
 +
BLAKE      01/05/81                  VIERNES                             
 +
ADAMS      23/05/87                  SÁBADO                             
 +
MILLER    23/01/82                  SÁBADO                             
 +
WARD      22/02/81                  DOMINGO                             
 +
SCOTT      19/04/87                  DOMINGO
 +
</pre>
 +
 +
11. Fes una consulta que mostri el nom de l'empleat i importi de la comissió. Si el empleat no té comissió mostreu el literal "No Commission". Etiqueta la columna com COMM.
 +
 +
<pre>
 +
ENAME      COMM                                   
 +
---------- --------------
 +
SMITH      No Comission                           
 +
ALLEN      300                                     
 +
WARD      500                                     
 +
JONES      No Comission                           
 +
MARTIN    1400                                   
 +
BLAKE      No Comission                           
 +
CLARK      No Comission                           
 +
SCOTT      No Comission                           
 +
KING      No Comission                           
 +
TURNER    0                                       
 +
ADAMS      No Comission                           
 +
JAMES      No Comission                           
 +
FORD      No Comission                           
 +
MILLER    No Comission                           
 +
</pre>
 +
  
 
==T4: Visualització de dades a partir de varies taules ==
 
==T4: 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.
 +
 +
<pre>
 +
ENAME      DEPTNO                DNAME         
 +
---------- ---------------------- --------------
 +
SMITH      20                    RESEARCH     
 +
ALLEN      30                    SALES         
 +
WARD      30                    SALES         
 +
JONES      20                    RESEARCH     
 +
MARTIN    30                    SALES         
 +
BLAKE      30                    SALES         
 +
CLARK      10                    ACCOUNTING   
 +
SCOTT      20                    RESEARCH     
 +
KING      10                    ACCOUNTING   
 +
TURNER    30                    SALES         
 +
ADAMS      20                    RESEARCH     
 +
JAMES      30                    SALES         
 +
FORD      20                    RESEARCH     
 +
MILLER    10                    ACCOUNTING 
 +
</pre>
 +
2. Mostra els oficis diferents que hi ha al departament 30.
 +
 +
<pre>
 +
JOB      LOC         
 +
--------- -------------
 +
SALESMAN  CHICAGO     
 +
MANAGER  CHICAGO     
 +
CLERK    CHICAGO 
 +
</pre>
 +
3. Fes una consulta per mostrar el nom de l'empleat, nom del departament i localitat de tots els empleats que tenen comissió.
 +
 +
<pre>
 +
ENAME      DNAME          LOC         
 +
---------- -------------- -------------
 +
ALLEN      SALES          CHICAGO     
 +
WARD      SALES          CHICAGO     
 +
MARTIN    SALES          CHICAGO     
 +
TURNER    SALES          CHICAGO 
 +
</pre>
 +
4. Mostra el nom de l'empleat i nom del departament de tots els empleats que tenen una A en el  seu nom. Guarda la sentència SQL en un fitxer anomenat p4q4.sql.
 +
 +
<pre>
 +
ENAME      DNAME         
 +
---------- --------------
 +
ALLEN      SALES         
 +
WARD      SALES         
 +
MARTIN    SALES         
 +
BLAKE      SALES         
 +
CLARK      ACCOUNTING   
 +
ADAMS      RESEARCH     
 +
JAMES      SALES   
 +
</pre>
 +
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.
 +
 +
<pre>
 +
ENAME      JOB      DEPTNO                DNAME         
 +
---------- --------- ---------------------- --------------
 +
SMITH      CLERK    20                    RESEARCH     
 +
JONES      MANAGER  20                    RESEARCH     
 +
SCOTT      ANALYST  20                    RESEARCH     
 +
ADAMS      CLERK    20                    RESEARCH     
 +
FORD      ANALYST  20                    RESEARCH 
 +
</pre>
 +
6. Mostra el nom de l'empleat i el número del 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. Guarda la sentència SQL en un fitxer anomenat p4q6.sql.
 +
 +
<pre>
 +
Employee  Emp#                  Manager    Mgr#                 
 +
---------- ---------------------- ---------- ----------------------
 +
SMITH      7369                  FORD      7902                 
 +
ALLEN      7499                  BLAKE      7698                 
 +
WARD      7521                  BLAKE      7698                 
 +
JONES      7566                  KING      7839                 
 +
MARTIN    7654                  BLAKE      7698                 
 +
BLAKE      7698                  KING      7839                 
 +
CLARK      7782                  KING      7839                 
 +
SCOTT      7788                  JONES      7566                 
 +
TURNER    7844                  BLAKE      7698                 
 +
ADAMS      7876                  SCOTT      7788                 
 +
JAMES      7900                  BLAKE      7698                 
 +
FORD      7902                  JONES      7566                 
 +
MILLER    7934                  CLARK      7782 
 +
</pre>
 +
7. Modifica p4q6.sql per mostrar tots els empleats incloent a King, que no té cap. Torna a guardar la consulta com p4q7.sql. Executa la consulta.
 +
 +
<pre>
 +
Employee  Emp#                  Manager    Mgr#                 
 +
---------- ---------------------- ---------- ----------------------
 +
SMITH      7369                  FORD      7902                 
 +
ALLEN      7499                  BLAKE      7698                 
 +
WARD      7521                  BLAKE      7698                 
 +
JONES      7566                  KING      7839                 
 +
MARTIN    7654                  BLAKE      7698                 
 +
BLAKE      7698                  KING      7839                 
 +
CLARK      7782                  KING      7839                 
 +
SCOTT      7788                  JONES      7566                 
 +
KING      7839                                                   
 +
TURNER    7844                  BLAKE      7698                 
 +
ADAMS      7876                  SCOTT      7788                 
 +
JAMES      7900                  BLAKE      7698                 
 +
FORD      7902                  JONES      7566                 
 +
MILLER    7934                  CLARK      7782
 +
</pre>
 +
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.
 +
 +
<pre>
 +
DEPARTAMENT            EMPLOYEE  COLLEAGUE 
 +
---------------------- ---------- ----------
 +
20                    FORD      SMITH     
 +
20                    ADAMS      SMITH     
 +
20                    SCOTT      SMITH     
 +
20                    JONES      SMITH     
 +
30                    JAMES      ALLEN     
 +
30                    TURNER    ALLEN     
 +
30                    BLAKE      ALLEN     
 +
30                    MARTIN    ALLEN     
 +
30                    WARD      ALLEN     
 +
30                    JAMES      WARD     
 +
30                    TURNER    WARD     
 +
30                    BLAKE      WARD     
 +
30                    MARTIN    WARD     
 +
30                    ALLEN      WARD     
 +
20                    FORD      JONES     
 +
20                    ADAMS      JONES     
 +
20                    SCOTT      JONES     
 +
20                    SMITH      JONES     
 +
30                    JAMES      MARTIN   
 +
30                    TURNER    MARTIN   
 +
30                    BLAKE      MARTIN   
 +
30                    WARD      MARTIN   
 +
30                    ALLEN      MARTIN   
 +
30                    JAMES      BLAKE     
 +
30                    TURNER    BLAKE     
 +
30                    MARTIN    BLAKE     
 +
30                    WARD      BLAKE     
 +
30                    ALLEN      BLAKE     
 +
10                    MILLER    CLARK     
 +
10                    KING      CLARK     
 +
20                    FORD      SCOTT     
 +
20                    ADAMS      SCOTT     
 +
20                    JONES      SCOTT     
 +
20                    SMITH      SCOTT     
 +
10                    MILLER    KING     
 +
10                    CLARK      KING     
 +
30                    JAMES      TURNER   
 +
30                    BLAKE      TURNER   
 +
30                    MARTIN    TURNER   
 +
30                    WARD      TURNER   
 +
30                    ALLEN      TURNER   
 +
20                    FORD      ADAMS     
 +
20                    SCOTT      ADAMS     
 +
20                    JONES      ADAMS     
 +
20                    SMITH      ADAMS     
 +
30                    TURNER    JAMES     
 +
30                    BLAKE      JAMES     
 +
30                    MARTIN    JAMES     
 +
30                    WARD      JAMES     
 +
30                    ALLEN      JAMES     
 +
20                    ADAMS      FORD     
 +
20                    SCOTT      FORD     
 +
20                    JONES      FORD     
 +
20                    SMITH      FORD     
 +
10                    KING      MILLER   
 +
10                    CLARK      MILLER
 +
</pre>
 +
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.
 +
 +
<pre>
 +
ENAME      JOB      DNAME          SAL                    GRADE                 
 +
---------- --------- -------------- ---------------------- ----------------------
 +
SMITH      CLERK    RESEARCH      800                    1                     
 +
JAMES      CLERK    SALES          950                    1                     
 +
ADAMS      CLERK    RESEARCH      1100                  1                     
 +
WARD      SALESMAN  SALES          1250                  2                     
 +
MARTIN    SALESMAN  SALES          1250                  2                     
 +
MILLER    CLERK    ACCOUNTING    1300                  2                     
 +
TURNER    SALESMAN  SALES          1500                  3                     
 +
ALLEN      SALESMAN  SALES          1600                  3                     
 +
CLARK      MANAGER  ACCOUNTING    2450                  4                     
 +
BLAKE      MANAGER  SALES          2850                  4                     
 +
JONES      MANAGER  RESEARCH      2975                  4                     
 +
SCOTT      ANALYST  RESEARCH      3000                  4                     
 +
FORD      ANALYST  RESEARCH      3000                  4                     
 +
KING      PRESIDENT ACCOUNTING    5000                  5
 +
</pre>
 +
10. Fes una consulta per mostrar el nom i data de contractació de qualsevol empleat contractat desprès de Blake.
 +
 +
<pre>
 +
ENAME      HIREDATE                 
 +
---------- -------------------------
 +
MARTIN    28/09/81                 
 +
CLARK      09/06/81                 
 +
SCOTT      19/04/87                 
 +
KING      17/11/81                 
 +
TURNER    08/09/81                 
 +
ADAMS      23/05/87                 
 +
JAMES      03/12/81                 
 +
FORD      03/12/81                 
 +
MILLER    23/01/82
 +
</pre>
 +
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. Etiquet les columnes com Employee. Emp Hiredate, Manager i Mgr respectivament.
 +
 +
<pre>
 +
Employee  Emp Hiredate              Manager    Mgr Hiredate             
 +
---------- ------------------------- ---------- -------------------------
 +
SMITH      17/12/80                  FORD      03/12/81                 
 +
ALLEN      20/02/81                  BLAKE      01/05/81                 
 +
WARD      22/02/81                  BLAKE      01/05/81                 
 +
JONES      02/04/81                  KING      17/11/81                 
 +
BLAKE      01/05/81                  KING      17/11/81                 
 +
CLARK      09/06/81                  KING      17/11/81
 +
</pre>
 +
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.
 +
 +
<pre>
 +
EMPLOYEE_AND_THEIR_SALARIS
 +
-------------------------------------------------------------
 +
KING      **************************************************
 +
FORD      ******************************
 +
SCOTT    ******************************
 +
JONES    *****************************
 +
BLAKE    ****************************
 +
CLARK    ************************
 +
ALLEN    ****************
 +
TURNER    ***************
 +
MILLER    *************
 +
WARD      ************
 +
MARTIN    ************
 +
ADAMS    ***********
 +
JAMES    *********
 +
SMITH    ********
 +
</pre>
  
 
==T5: Dades agregades. Funcions de grup ==
 
==T5: Dades agregades. Funcions de grup ==
 +
 +
1. Les funcions de grup treballen amb molts registres per a produir un resultat.
 +
 +
<pre>
 +
Cert / Fals
 +
</pre>
 +
2. Les funcions de grup inclouen nuls en els càlculs.
 +
 +
<pre>
 +
Cert / Fals
 +
</pre>
 +
3. La clàusula WHERE restringeix registres abans de la inclusió en un càlcul de grup.
 +
 +
<pre>
 +
Cert / Fals
 +
</pre>
 +
4. 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. Salva la sentència SQL en un fitxer anomenat p5q4.sql.
 +
 +
<pre>
 +
Maximum                Minimum                Sum                    Average               
 +
---------------------- ---------------------- ---------------------- ----------------------
 +
5000                  800                    29025                  2073   
 +
</pre>
 +
5. Modifica p5q4.sql per mostrar: màxim, mínim, suma i mitjana aritmètica de salaris per a cada ofici. Salva la sentència SQL en un fitxer anomenat p5q5.sql. Executa la consulta.
 +
 +
<pre>
 +
JOB      Maximum                Minimum                Sum                    Average               
 +
--------- ---------------------- ---------------------- ---------------------- ----------------------
 +
CLERK    1300                  800                    4150                  1038                 
 +
SALESMAN  1600                  1250                  5600                  1400                 
 +
PRESIDENT 5000                  5000                  5000                  5000                 
 +
MANAGER  2975                  2450                  8275                  2758                 
 +
ANALYST  3000                  3000                  6000                  3000 
 +
</pre>
 +
6. Escriu una consulta que mostri el nombre de persones que tenen el mateix ofici.
 +
 +
<pre>
 +
JOB      COUNT(*)             
 +
--------- ----------------------
 +
CLERK    4                     
 +
SALESMAN  4                     
 +
PRESIDENT 1                     
 +
MANAGER  3                     
 +
ANALYST  2 
 +
</pre>
 +
7. Determina el nombre total de directors. Etiqueta la columna com "nº de directors".
 +
 +
<pre>
 +
Number of Managers   
 +
----------------------
 +
6 
 +
</pre>
 +
8. 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".
 +
 +
<pre>
 +
DIFFERENCE           
 +
----------------------
 +
4200                 
 +
</pre>
 +
9. Mostri 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.
 +
 +
<pre>
 +
MGR                    MIN(SAL)             
 +
---------------------- ----------------------
 +
7566                  3000                 
 +
7839                  2450                 
 +
7782                  1300                 
 +
7788                  1100 
 +
</pre>
 +
10. 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.
 +
 +
<pre>
 +
DNAME          LOC          Number of People      Salary               
 +
-------------- ------------- ---------------------- ----------------------
 +
RESEARCH      DALLAS        5                      2175                 
 +
SALES          CHICAGO      6                      1566,67               
 +
ACCOUNTING    NEW YORK      3                      2916,67         
 +
</pre>
 +
11. Fes una consulta que mostri el nombre total d'empleats i d'aquest total el nombre dels que es van contractar en 1980, 1981, 1982 i 1983. Etiqueta les columnes com TOTAL, 1980, 1981, 1982 i 1983.
 +
 +
<pre>
 +
TOTAL                  1980                  1981                  1982                  1983                 
 +
---------------------- ---------------------- ---------------------- ---------------------- ----------------------
 +
14                    1                      10                    1                      0                   
 +
</pre>
 +
12. 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.
 +
 +
<pre>
 +
Job      Dept 10                Dept 20                Dept 30                Total                 
 +
--------- ---------------------- ---------------------- ---------------------- ----------------------
 +
CLERK    1300                  1900                  950                    4150                 
 +
SALESMAN                                                5600                  5600                 
 +
PRESIDENT 5000                                                                5000                 
 +
MANAGER  2450                  2975                  2850                  8275                 
 +
ANALYST                          6000                                          6000
 +
</pre>
 +
  
 
==T6: Subconsultes ==
 
==T6: Subconsultes ==
 +
 +
1. 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).
 +
 +
<pre>
 +
ENAME      HIREDATE                 
 +
---------- -------------------------
 +
JAMES      03/12/81                 
 +
TURNER    08/09/81                 
 +
MARTIN    28/09/81                 
 +
WARD      22/02/81                 
 +
ALLEN      20/02/81     
 +
</pre>
 +
2. Fes una subconsulta 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.
 +
 +
<pre>
 +
EMPNO                  ENAME     
 +
---------------------- ----------
 +
7839                  KING     
 +
7902                  FORD     
 +
7788                  SCOTT     
 +
7566                  JONES     
 +
7698                  BLAKE     
 +
7782                  CLARK   
 +
</pre>
 +
3. 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". Salva la sentència SQL en un fitxer anomenat p6q3.sql.
 +
 +
<pre>
 +
EMPNO                  ENAME     
 +
---------------------- ----------
 +
7902                  FORD     
 +
7876                  ADAMS     
 +
7788                  SCOTT     
 +
7566                  JONES     
 +
7369                  SMITH     
 +
7900                  JAMES     
 +
7844                  TURNER   
 +
7698                  BLAKE     
 +
7654                  MARTIN   
 +
7521                  WARD     
 +
7499                  ALLEN   
 +
</pre>
 +
4. Mostra el nom, número de departament i ofici de tots els empleats que treballen en un departament que es trobi a Dallas.
 +
 +
<pre>
 +
ENAME      DEPTNO                JOB     
 +
---------- ---------------------- ---------
 +
SMITH      20                    CLERK   
 +
JONES      20                    MANAGER 
 +
SCOTT      20                    ANALYST 
 +
ADAMS      20                    CLERK   
 +
FORD      20                    ANALYST 
 +
</pre>
 +
5. Mostra el nom i el salari de tots els empleats que depenguin de "King".
 +
 +
<pre>
 +
ENAME      SAL                   
 +
---------- ----------------------
 +
JONES      2975                 
 +
BLAKE      2850                 
 +
CLARK      2450                 
 +
</pre>
 +
6. Mostra el número, nom i ofici de tots els empleats del departament "Sales".
 +
 +
<pre>
 +
DEPTNO                ENAME      JOB     
 +
---------------------- ---------- ---------
 +
30                    ALLEN      SALESMAN 
 +
30                    WARD      SALESMAN 
 +
30                    MARTIN    SALESMAN 
 +
30                    BLAKE      MANAGER 
 +
30                    TURNER    SALESMAN 
 +
30                    JAMES      CLERK   
 +
</pre>
 +
7. Modifica el fitxer p6q3.sql 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. Salva el fitxer, com p6q7.sql.
 +
 +
<pre>
 +
EMPNO                  ENAME      SAL                   
 +
---------------------- ---------- ----------------------
 +
7902                  FORD      3000                 
 +
7788                  SCOTT      3000                 
 +
7566                  JONES      2975                 
 +
7698                  BLAKE      2850   
 +
</pre>
  
  
 
==T7: Subconsultes multicolumna ==
 
==T7: Subconsultes multicolumna ==
 +
 +
1. 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ó.
 +
 +
<pre>
 +
ENAME      DEPTNO                SAL                   
 +
---------- ---------------------- ----------------------
 +
ALLEN      30                    1600                 
 +
MARTIN    30                    1250                 
 +
WARD      30                    1250                 
 +
TURNER    30                    1500   
 +
</pre>
 +
2. 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.
 +
 +
<pre>
 +
ENAME      DNAME          SAL                   
 +
---------- -------------- ----------------------
 +
SMITH      RESEARCH      800                   
 +
JONES      RESEARCH      2975                 
 +
SCOTT      RESEARCH      3000                 
 +
FORD      RESEARCH      3000                 
 +
ADAMS      RESEARCH      1100 
 +
</pre>
 +
3. 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.
 +
 +
<pre>
 +
ENAME      HIREDATE                  SAL                   
 +
---------- ------------------------- ----------------------
 +
FORD      03/12/81                  3000                       
 +
</pre>
 +
4. Fes una consulta per mostrar als empleats que guanyin un salari superior al salari de qualsevol empleat "CLERK". Ordena el resultat pel salari de forma descendent.
 +
 +
<pre>
 +
ENAME      JOB      SAL                   
 +
---------- --------- ----------------------
 +
KING      PRESIDENT 5000                 
 +
FORD      ANALYST  3000                 
 +
SCOTT      ANALYST  3000                 
 +
JONES      MANAGER  2975                 
 +
BLAKE      MANAGER  2850                 
 +
CLARK      MANAGER  2450                 
 +
ALLEN      SALESMAN  1600                 
 +
TURNER    SALESMAN  1500 
 +
</pre>
 +
 +
 +
==T7-1: Exists i Operadors SET ==
 +
 +
1.Empleats que tinguin algun empleat al seu càrrec (EXISTS).
 +
   
 +
 +
2.Departaments que no tinguin empleats (NOT EXISTS).
 +
         
 +
 +
3.Empleats que no tenen cap empleat al seu càrrec (NOT EXISTS).
 +
 +
                   
 +
4.Cognoms dels empleats que tinguin un o més companys de feina en els seus departaments amb dates de contractació posteriors però salaris més alts (EXISTS).
 +
         
 +
 +
5.Mostreu l'identificador dels empleats i l'identificador de totes les feines que han tingut (UNION).
 +
         
 +
 +
6.El mateix que la 5, però considerant també el departament on han desenvolupat les feines (UNION).
 +
         
 +
 +
7.Mostreu l'identificador dels empleats i l'identificador de totes les feines que han tingut en cada departament (UNION ALL).
 +
     
 +
         
 +
8.Mostreu els identificadors dels empleats i del càrrec dels empleats que actualment tinguin un càrrec que varen ocupar en un altre període en la companyia (INTERSECT).
 +
 +
 +
9.Igual que la 8, però considerant també el departament on han treballat (INTERSECT).
 +
 +
 +
10.Mostreu els identificadors dels empleats i del càrrec dels empleats que no han canviat mai de càrrec (MINUS).
 +
 +
 +
11.Mostreu els identificadors dels departaments per a departaments que no continguin l'ID de càrrec ST_CLERK (MINUS).
 +
 +
 +
12.Mostreu l'identificador de país i el nom dels països que no tenen departaments ubicats en ells (MINUS).
 +
 +
 +
13.Mostreu els identificadors dels departaments i dels càrrecs dels departaments 10, 50 i 20 (UNION).
 +
 +
 +
==T7-2: Exercicis consultes complexes==
 +
 +
[[Fitxer:Esquema_scot_ampliada.png|800px|center]]
 +
 +
 +
 +
1.Quantitat de producte venuda a cada client (Descripcio producte i quantitat).
 +
 +
 +
2.Import total venut per cada empleat (Nom empleat i total).
 +
 +
 +
3.Empleats que han venut menys que la mitja de tots els empleats.
 +
 +
 +
4.Quantitat de producte venut. Ordenat per producte (Descripcio producte i quantitat).
 +
 +
 +
5.Quantitat de producte venut. Ordenat per quantitat de forma descendent. Han d'aparèixer tots els productes.
 +
 +
 +
6.Quants clients a cada estat?
 +
 +
 +
7.Quantes ordres per any i mes?
 +
 +
 +
8.Quantes ordres per any?
 +
 +
 +
9.En quants productes surt la paraula "TENNIS"?
 +
 +
 +
10.Increment del preus dels productes des del primer preu fins l'actual.
 +
 +
 +
11.Màximes quantitats de cada producte venudes en una sola comanda. Ordenades de forma ascendent.
 +
 +
 +
12.Mostrar quantitat de cada producte venut (Descripció i total unitats venudes). Només ens interessen aquells productes dels que s'hagin venut més de 8 unitats.
 +
 +
 +
13.Quantitat de producte venut (Descripció i total unitats venudes). Ordenat per producte. Han d'aparèixer tots els productes.
 +
 +
 +
14.Quantitat de producte venut per empleat (Nom empleat, Descripció del producte i quantitat venuda).
 +
 +
 +
15.Número d'ordres de cada COMMPLAN.
 +
 +
 +
16.Número de productes venuts a cada ordre. Han d'aparèixer totes les ordres.
 +
 +
17. Clients que han comprat més de 5 unitats d'algun producte en una comanda (EXISTS).
 +
 +
18. Quins clients han comprat més que la mitjana.
 +
 +
19. Quants clients han comprat més que la mitjana.
 +
 +
20. Quants empleats no cobren comissió a cada departament. Han d'apareixer tots els departaments.
 +
 +
21. Mostrar el prodid dels productes que no s'han venut mai (MINUS).
 +
 +
22. Mostrar el prodid i la descripció  dels productes que no s'han venut mai (MINUS).
  
 
==T8: Generació d'informes ==
 
==T8: Generació d'informes ==
 +
 +
'''Determina quina de les següents sentències són certes o falses:'''
 +
 +
1. Una variable d'ampersand simple només es demana una vegada?
 +
 +
<pre>
 +
Cert / Fals
 +
</pre>
 +
2. L'ordre ACCEPT és de SQL.
 +
 +
<pre>
 +
Cert / Fals
 +
</pre>
 +
'''Les següents preguntes utilitzen les taules EMP, CUSTOMER i PRODUCT.'''
 +
 +
<pre>
 +
Guardeu tots els scripts en la carpeta C:\oraclexe\app\oracle\product\10.2.0\server\BIN
 +
</pre>
 +
 +
3. Escriu un arxiu d'ordres que mostri el nom de l'empleat concatenat amb l'ofici i la data d'ingrés per als empleats contractats en un rang de dates específiques. Demana a l'usuari el rang utilitzant l'ordre ACCEPT. Utilitzeu el format MM/DD/YY. Salva la sentència en un fitxer anomenat p8q3.sql. El resultat s'ha de veure com a continuació:
 +
 +
<pre>
 +
    Please enter the low date range ("MM/DD/YYYY"): 01/01/1981
 +
    Please enter the high date range ("MM/DD/YYYY"): 01/01/1982
 +
 +
    EMPLOYEES                HIREDATE                 
 +
    ------------------------- -------------------------
 +
    ALLEN, SALESMAN          20/02/81                 
 +
    WARD, SALESMAN            22/02/81                 
 +
    JONES, MANAGER            02/04/81                 
 +
    MARTIN, SALESMAN          28/09/81                 
 +
    BLAKE, MANAGER            01/05/81                 
 +
    CLARK, MANAGER            09/06/81                 
 +
    KING, PRESIDENT          17/11/81                 
 +
    TURNER, SALESMAN          08/09/81                 
 +
    JAMES, CLERK              03/12/81                 
 +
    FORD, ANALYST            03/12/81                 
 +
 +
      10 filas seleccionadas
 +
</pre>
 +
 +
 +
4. Fes un script que mostri el nom, ofici i nom del departament. La condició de cerca no serà sensible a majúscules/minúscules. Salva el script com p8q4.sql.
 +
 +
<pre>
 +
    Please enter the location name: Dallas   
 +
 +
    EMPLOYEE NAME  JOB          DEPARTAMENT NAME         
 +
    --------------- ------------- ----------------
 +
    SMITH     CLERK   RESEARCH
 +
    JONES     MANAGER   RESEARCH
 +
    SCOTT     ANALYST   RESEARCH
 +
    ADAMS     CLERK   RESEARCH
 +
    FORD     ANALYST   RESEARCH
 +
</pre>
 +
 +
5. Modifica el fitxer p8q4.sql per crear un informe amb el nom del departament, nom, data d'alta i salari anual, per a tots els empleats de determinada localitat. Pregunta a l'usuari per la localitat. Etiqueta les columnes segons el model. Salva el fitxer com p8q5.sql.
 +
 +
<pre>
 +
    Please enter location name: Chicago
 +
   
 +
    DEPARTMENT  EMPLOYEE      START                    ANNUAL
 +
    NAME        NAME          DATE          SALARY      SALARY
 +
    ----------  -------------  ---------  ---------  ----------
 +
    SALES      BLAKE          01-MAY-81  $2,850.00  $34,200.00
 +
                MARTIN        28-SEP-81  $1,250.00  $15,000.00
 +
                ALLEN          20-FEB-81  $1,600.00  $19,200.00
 +
                TURNER        08-SEP-81  $1,500.00  $18.000.00
 +
                JAMES          03-DEC-81    $950.00  $11,400.00
 +
                WARD          22-FEB-81  $1,250.00  $15,000.00
 +
</pre>
 +
 +
Fes els següents informes utilitzant l'ordre BREAK.
 +
 +
6.
 +
 +
<pre>
 +
       
 +
    DEPARTMENT  EMPLOYEE     
 +
    NAME        NAME         
 +
    ----------  ------------- 
 +
    ACCOUNTING  CLARK
 +
                KING
 +
                MILLER
 +
    RESEARCH    JONES
 +
                FORD
 +
                ADAM
 +
                SMITH
 +
                SCOTT
 +
    SALES      WARD
 +
                TURNER
 +
                ALLEN
 +
                JAMES
 +
                BLAKE         
 +
                MARTIN                       
 +
</pre>
 +
 +
7.
 +
<pre>
 +
       
 +
    DEPARTMENT  EMPLOYEE     
 +
    NAME        NAME         
 +
    ----------  ------------- 
 +
    ACCOUNTING  CLARK
 +
                KING
 +
                MILLER
 +
 +
    RESEARCH    JONES
 +
                FORD
 +
                ADAM
 +
                SMITH
 +
                SCOTT
 +
 +
    SALES      WARD
 +
                TURNER
 +
                ALLEN
 +
                JAMES
 +
                BLAKE         
 +
                MARTIN                       
 +
</pre>
 +
8.
 +
<pre>
 +
       
 +
    DEPARTMENT  EMPLOYEE      EMPLOYEE
 +
    NAME        JOB            NAME         
 +
    ----------  -------------  -----------
 +
    ACCOUNTING  CLERK          MILLER
 +
 +
                MANAGER        CLARK
 +
 +
                PRESIDENT      KING
 +
               
 +
 +
 +
    RESEARCH    ANALYST        SCOTT
 +
                                FORD
 +
           
 +
                CLERK          ADAMS
 +
                                SMITH
 +
               
 +
                MANAGER        JONES
 +
 +
 +
 +
    SALES      CLERK          JAMES
 +
 +
                MANAGER        BLAKE
 +
 +
                SALESMAN      MARTIN
 +
                                WARD
 +
                                ALLEN
 +
                                TURNER
 +
</pre>
  
 
==T9: Manipulació de dades ==
 
==T9: Manipulació de dades ==
 +
 +
1. Crea la taula MY_EMPLOYEE:
 +
<pre>
 +
CREATE TABLE MY_EMPLOYEE(
 +
    ID          NUMBER(4) PRIMARY KEY,
 +
    LAST_NAME VARCHAR2(25),
 +
    FIRST_NAME  VARCHAR2(25),
 +
    USERID VARCHAR2(25),
 +
    SALARY NUMBER(9,2));
 +
</pre>
 +
2. Mostra l'estructura de la taula MY_EMPLOYEE:
 +
 +
3. Afegeix el primer registre del exemple següent. No explicitis les columnes en la clàusula INSERT.
 +
 +
{|border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; background: #f9f9f9; border: 1px #aaa solid; border-collapse: collapse; font-size: 95%;"
 +
! align="left" width="100 px" style="background:Lavender; color:Black"|ID
 +
! align="left" width="100 px" style="background:Lavender; color:Black"|LAST_NAME
 +
! align="left" width="120 px" style="background:Lavender; color:Black"|FIRST_NAME
 +
! align="left" width="85 px" style="background:Lavender; color:Black"|USERID
 +
! align="left" width="85 px" style="background:Lavender; color:Black"|SALARY
 +
|-
 +
|1||Patel||Ralph||rpatel||795
 +
|-
 +
|2||Dancs||Betty||bdancs||860
 +
|-
 +
|3||Biri||Ben||bbiri||1100
 +
|-
 +
|4||Newman||Chad||cnewman||750
 +
|-
 +
|5||Ropeburn||Audry||aropebur||1550
 +
|}
 +
 +
4. Afegeix el segon registre del exemple anterior. Explicita les columnes en la clàusula INSERT.
 +
 +
5. Mostra les dades afegides i comprova que són correctes.
 +
 +
6. Fes un script anomenat loademp.sql per afegir registres en la taula MY_EMPLOYEE de forma interactiva. Pregunta a l'usuari pel identificador, nom, cognom i salari de l'empleat. Concatena la primera lletra del nom i les primeres set lletres del cognom, per obtenir al seu "userid".
 +
 +
7. Afegeix a la taula MY_EMPLOYEE els dos registres següents del exemple anterior, executant el script.
 +
 +
8. Confirma que les dades s'han afegit correctament.
 +
 +
9. Feu commit per fer que les insercions siguin permanents.
 +
 +
'''Modifica i elimina dades de la taula MY_EMPLOYEE:'''
 +
 +
10. Canvia el cognom del empleat número 3 a "Drexter".
 +
 +
11. Per a tots els empleats amb salari menor a 900 $, assigna'ls un salari de 1000 $.
 +
 +
12. Comprova els canvis realitzat a la taula MY_EMPLOYEE.
 +
 +
13. Elimina a "Betty Dancs" de la taula MY_EMPLOYEE.
 +
 +
14. Comprova els canvis realitzat a la taula MY_EMPLOYEE.
 +
 +
15. Fes commit dels canvis pendents.
 +
 +
'''Controla les transaccions de la taula MY_EMPLOYEE:'''
 +
 +
16. Afegeix a la taula l'últim registre del exemple, executant el script creat en l'apartat 6.
 +
 +
17. Comprova que les dades s'han afegit correctament.
 +
 +
18. Marca un punt intermedi en el procés de la transacció.
 +
 +
19. Elimina tots els registres de la taula MY_EMPLOYEE.
 +
 +
20. Comprova que no hi ha cap registre a la taula.
 +
 +
21. Descarta l'operació de eliminació més recent, sense descarta la inserció prèvia.
 +
 +
22. Comprova l'estat de la taula.
 +
 +
23. Fes permanents els canvis pendents.
  
 
==T10: Creació i gestió de taules ==
 
==T10: Creació i gestió de taules ==
 +
 +
1. Fes un script, anomenant p10q1.sql, amb el codi per crear la  taula DEPARTMENT segons el següent model. Executa el script i comprova que la taula s'ha creat correctament.
 +
 +
{|border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; background: #f9f9f9; border: 1px #aaa solid; border-collapse: collapse; font-size: 95%;"
 +
! align="left" width="180 px" style="background:Lavender; color:Black"|Column Name||align="left" style="width:150px" | Id||align="left" style="width:150px"|Name
 +
|-
 +
! align="left" style="background:Lavender; color:Black"|Key Type||align="left"| ||align="left"|
 +
|-
 +
! align="left" style="background:Lavender; color:Black"|Nulls/Unique||align="left"| ||align="left"|
 +
|-
 +
! align="left" style="background:Lavender; color:Black"|FK Table||align="left"| ||align="left"|
 +
|-
 +
! align="left" style="background:Lavender; color:Black"|Fk Column||align="left"| ||align="left"|
 +
|-
 +
! align="left" style="background:Lavender; color:Black"|DataType||align="left"|Number||align="left"|Varchar2
 +
|-
 +
! align="left" style="background:Lavender; color:Black"|Length||align="left"|7||align="left"|25
 +
|}
 +
 +
<pre>
 +
          Name      Null?    Type
 +
          ---------- -------- ----------
 +
          ID                  NUMBER(7)
 +
          NAME                VARCHAR2(25)
 +
</pre>
 +
 +
 +
2. Omple la taula DEPARTMENT amb les dades de la taula DEPT. Inclou només les columnes que precisis.
 +
 +
 +
3.  Fes un script, anomenant p10q3.sql, amb el codi per crear la  taula EMPLOYEE segons el següent model. Executa el script i comprova que la taula s'ha creat correctament.
 +
 +
{|border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0; background: #f9f9f9; border: 1px #aaa solid; border-collapse: collapse; font-size: 95%;"
 +
! align="left" width="180 px" style="background:Lavender; color:Black"|Column Name||align="left" style="width:150px" | Id||align="left" style="width:150px"|Last_Name||align="left" style="width:150px"|First_Name||align="left" style="width:150px"|Dept_Id
 +
|-
 +
! align="left" style="background:Lavender; color:Black"|Key Type||align="left"| ||align="left"| ||align="left"| ||align="left"|
 +
|-
 +
! align="left" style="background:Lavender; color:Black"|Nulls/Unique||align="left"| ||align="left"| ||align="left"| ||align="left"|
 +
|-
 +
! align="left" style="background:Lavender; color:Black"|FK Table||align="left"| ||align="left"| ||align="left"| ||align="left"|
 +
|-
 +
! align="left" style="background:Lavender; color:Black"|Fk Column||align="left"| ||align="left"| ||align="left"| ||align="left"|
 +
|-
 +
! align="left" style="background:Lavender; color:Black"|DataType||align="left"|Number||align="left"|Varchar2|| align="left" | Varchar2||align="left"|Number
 +
|-
 +
! align="left" style="background:Lavender; color:Black"|Length||align="left"|7||align="left"|25 ||align="left"|25||align="left"|7
 +
|}
 +
 +
<pre>
 +
          Name      Null?    Type
 +
          ---------- -------- ----------
 +
          ID                  NUMBER(7)
 +
          LAST_NAME          VARCHAR2(25)
 +
          FIRST_NAME          VARCHAR2(25) 
 +
          DEPT_ID            NUMBER(7)
 +
</pre>
 +
 +
4. Modifica la taula EMPLOYEE per permetre noms llargs dels empleats. Comprova els canvis realitzats.
 +
 +
<pre>
 +
          Name      Null?    Type
 +
          ---------- -------- ----------
 +
          ID                  NUMBER(7)
 +
          LAST_NAME          VARCHAR2(50)
 +
          FIRST_NAME          VARCHAR2(25) 
 +
          DEPT_ID            NUMBER(7)
 +
</pre>
 +
 +
5. Comprova que les taules DEPPARTMENT i EMPLOYEE han estat reconegudes pel diccionari de dades (utilitza USER_TABLES).
 +
 +
<pre>
 +
          TABLE_NAME
 +
          ------------
 +
          DEPARTMENT
 +
          EMPLOYEE
 +
</pre>
 +
 +
6. Crea la taula EMPLOYEE2 basada en l'estructura de la taula EMP. Inclou només les columnes: EMPNO, ENAME i DEPTNO. Etiqueta les columnes en la nova taula, respectivament: ID, LAST_NAME i DEPT_ID.
 +
 +
 +
7. Elimina la taula EMPLOYEE.
 +
 +
8. Reanomena la taula EMPLOYEE2 com EMPLOYEE.
 +
 +
9. Afegeix comentaris a les taules DEPARTMENT i EMPLOYEE, descrivint les taules. Comprova la informació en el diccionari de dades.
  
 
==T11: Restriccions ==
 
==T11: Restriccions ==
 +
 +
1. Afegeix una restricció PRIMARY KEY a nivell de la taula EMPLOYEE usant la columna ID. La restricció hauria d'estar activada (enabled) quan es crea.
 +
 +
2. Crea una restricció PRIMARY KEY sobre la taula DEPARTMENT usant la columna ID. La restricció hauria d'estar activada (enabled) quan es crea.
 +
 +
3. Afegeix una referència FOREIGN KEY a la taula EMPLOYEE, que asseguri que l'empleat no està assignat a un departament que no existeix.
 +
 +
4. Verifica que las restriccions anteriors s'han afegit correctament, consultant USER_CONSTRAINTS. Observa els tipus i noms de les restriccions. Salva la sentència en un fitxer anomenat p11q4.sql.
 +
 +
<pre>
 +
    CONSTRAINT_NAME            C
 +
    ------------------------  --
 +
    DEPARTMENT_ID_PK          P
 +
    EMPLOYEE_ID_PK            P
 +
    EMPLOYEE_DEPT_ID_FK        R
 +
</pre>
 +
 +
5. Mostra els noms i tipus d'objectes consultant la vista del diccionari de dades USER_OBJECTS per a les taules EMPLOYEE i DEPARTMENT. Formata les columnes per a una lectura més adient. Observa l'existència d'índexs per a les noves taules.
 +
 +
<pre>
 +
    OBJECT_NAME            OBJECT_TYPE
 +
    --------------------  -----------
 +
    DEPARTMENT            TABLE   
 +
    DEPARTMENT_ID_PK      INDEX
 +
    EMPLOYEE              TABLE
 +
    EMPLOYEE_ID_PK        INDEX
 +
</pre>
 +
 +
6. Modifica la taula EMPLOYEE, afegint la columna SALARY de tipus NUMBER(7).
 +
  
 
==T12: Creació de vistes ==
 
==T12: Creació de vistes ==
 +
 +
1. Crea una vista anomenada EMP_VU basada en el número d'empleat, nom de l'empleat i número de departament des de la taula EMP. Canvia la capçalera del nom del empleat a EMPLOYEE.
 +
 +
2. Mostra el contingut de la vista EMP_VU.
 +
 +
<pre>
 +
EMPNO  EMPLOYEE  DEPTNO
 +
------  ---------  ------
 +
7369 SMITH       20
 +
7499 ALLEN       30
 +
7521 WARD       30
 +
7566 JONES       20
 +
7654 MARTIN       30
 +
7698 BLAKE       30
 +
7782 CLARK       10
 +
7788 SCOTT       20
 +
7839 KING       10
 +
7844 TURNER       30
 +
7876 ADAMS       20
 +
7900 JAMES       30
 +
7902 FORD       20
 +
7934 MILLER       10
 +
</pre>
 +
 +
3. Mostra el nom de la vista (columna view_name) i el text de la vista anterior emmagatzemada en el diccionari de dades (USER_VIEWS).
 +
 +
<pre>
 +
    VIEW_NAME  TEXT
 +
    ----------- ---------------------------------------
 +
    EMP_VU      SELECT empno, ename employee, deptno
 +
                FROM emp
 +
</pre>
 +
 +
4. Fes una consulta que mostri el nom dels empleats i els números de departament, mitjançant la vista EMP_VU.
 +
 +
<pre>
 +
EMPLOYEE  DEPTNO
 +
---------  ------
 +
SMITH       20
 +
ALLEN       30
 +
WARD       30
 +
JONES       20
 +
MARTIN       30
 +
BLAKE       30
 +
CLARK       10
 +
SCOTT       20
 +
KING       10
 +
TURNER       30
 +
ADAMS       20
 +
JAMES       30
 +
FORD       20
 +
MILLER       10
 +
</pre>
 +
 +
5. Crea una vista anomenada DEPT20 que contingui el número d'empleat, nom d'empleat i número de departament de tots els empleats del departament 20. Etiqueta les columnes de la vista com EMPLOYEE_ID, EMPLOYEE i DEPARTMENT_ID. No permetis que un empleat pugui ser assignat a un altra departament mitjançant la vista.
 +
 +
6. Mostra l'estructura i el contingut de la vista DEPT20.
 +
 +
<pre>                         
 +
Nombre                        Null?    Tipo
 +
------------------------------ --------  ------------
 +
EMPLOYEE_ID                    NOT NULL  NUMBER(4)     
 +
EMPLOYEE                                VARCHAR2(10)   
 +
DEPARTMENT_ID                  NOT NULL  NUMBER(2)     
 +
 +
 +
EMPPLOYEE_ID  EMPLOYEE  DEPARTMENT_ID
 +
------------  ---------  -------------
 +
        7566  JONES             20
 +
        7902  FORD             20
 +
        7369  SMITH             20
 +
        7788  SCOTT             20
 +
        7876  ADAMS             20
 +
</pre>
 +
 +
7. Intenta assignar a Smith al departament 30.
 +
 +
8. Crea una vista anomenada SALARY_VU basada e el nom de l'empleat, nom del departament, salari i grau de salari de tots els empleats. Etiqueta les columnes com Employee, Departament, Salary i Grade, respectivament.
 +
  
 
==T13: Altres objectes de la base de dades ==
 
==T13: Altres objectes de la base de dades ==
  
−
==T14: Exercici Resum ==
+
1. Crea una seqüència per a ser utilitzada en la clau primària de la taula DEPARTMENT. La seqüència començara en el número 60 i el seu màxim serà 200. Tindrà un increment de 10 en 10 i el nom DEPT_ID_SEQ.
 +
 
 +
2. Fes un script que mostri la següent informació sobre les teves seqüències: nom, valor màxim, increment i darrer registre. Anomena el script p13q2.sql. Executa el script.
 +
<pre>
 +
SEQUENCE_NAME  MAX_VAUE    INCREMENT_BY  LAST_NUMBER
 +
-------------  ----------  ------------  -----------
 +
CUSTID        1.000E+27              1          109
 +
DEPT_ID_SEQ          200              1          60
 +
ORDID          1.000E+27              1          622
 +
PRODID        1.000E+27              1      200381
 +
</pre>
 +
3. Escriu un script interactiu per afegir un registre en la taula DEPARTMENT. Anomena'l p13q3.sql. Utilitza la seqüència DEPT_ID_SEQ per a la columna ID. Crea un missatge personalitzat per introduir el nom del departament. Executa el script. Afegeix els departament "Educació", i "Administració". Comprova les insercions.
 +
 
 +
4. Crea un índex no únic sobre FOREIGN KEY de la taula EMPLOYEE.
 +
 
 +
5. Mostra els índexs i la seva unicitat de la taula EMPLOYEE, utilitzant el diccionari de dades. Salva la sentència en un script anomenat pl13q5.sql.
 +
 
 +
<pre>
 +
INDEX_NAME            TABLE_NAME  UNIQUENESS
 +
--------------------  ----------  ------------
 +
EMPLOYEE_DEPT_ID_IDX  EMPLOYEE    NONUNIQUE
 +
EMPLOYEE_ID_PK        EMPLOYEE    UNIQUE
 +
</pre>
 +
6. Crea una restricció PRIMARY KEY sobre la taula DEPARTMENT. Comprova la creació de la restricció en el diccionari de dades executant el script p11q3.sql. Modifica i comprova la unicitat del índex en el diccionari de dades, executant el script p13q5.sql.
 +
 
 +
<pre>
 +
CONSTRAINT_NAME        C
 +
--------------------  --
 +
DEPARTMENT_ID_PK      P
 +
EMPLOYEE_ID_PK        P
 +
EMPLOYEE_DEPT_ID_FK  R
 +
</pre>
 +
 
 +
<pre>
 +
INDEX_NAME            TABLE_NAME  UNIQUENES
 +
--------------------  ----------  ------------
 +
DEPARTMENT_ID_PK      DEPARTMENT  UNIQUE
 +
EMPLOYEE_DEPT_ID_IDX  EMPLOYEE    NONUNIQUE
 +
EMPLOYEE_ID_PK        EMPLOYEE    UNIQUE
 +
</pre>
 +
 
 +
 
 +
==Repàs Consultes BBDD HR==
 +
<!--
 +
Esquema base de dades usuari HR
 +
 
 +
[[Fitxer:HR.gif|600px|center]]
 +
-->
 +
 
 +
 
 +
1. Muestra el nombre, apellido i salario mensual  de los empleados que cobren mas que Jennifer ordenado por salario descendiente
 +
 
 +
  +--------------------+-------------------+
 +
  | Nombre i apellido  | Salario Mensual  |
 +
  +--------------------+-------------------+
 +
 
 +
 +
 
 +
2. Para cada departamento Muestra el nombre del empleado/s que cobran menos.
 +
 
 +
  +------------------------+-------------------+
 +
  | Nombre departamentodo  | Nombre empleado  |
 +
  +------------------------+-------------------+
 +
 
 +
 
 +
3. Para cada región  muestra cuantos países hay. Ordena por numero de países ascendentemente.
 +
<!--
 +
  +-----------------+-------------------+==Repàs Consultes BBDD HR==
 +
 
 +
Esquema base de dades usuari HR
 +
 
 +
[[Fitxer:HR.gif|600px|center]]
 +
 
 +
  | Nombre Región  | Numero de paises  |
 +
  +-----------------+-------------------+
 +
 
 +
-->
 +
 
 +
4. Muestra el nombre de los departamentos i nombre de su jefe, que se encuentran fuera de EEUU.
 +
 
 +
  +--------------+-------+-----------------+
 +
  | Nombre Dep  | Jefe  |  Sueldo Mensual |
 +
  +------------- +-------+-----------------+
 +
 
 +
 
 +
5. Mostrar el nombre de los países que empiezan o termina en una “a" junto con el nombre de la region a la que pertencen.
 +
 
 +
  +-------------+--------+
 +
  | Nombre pais | Region |
 +
  +-------------+--------+
 +
 
 +
6. Muestra el nombre de cada empleado en mayúsculas junto con el de su respectivo jefe.
 +
 
 +
  +-----------------+-------------+
 +
  | Nombre empleado | Nombre jefe |
 +
  +-----------------+-------------+
 +
 
 +
7. Para cada jefe muestra cuantos empleados tiene a su cargo.
 +
 
 +
  +-----------------+------------------+
 +
  | Nombre jefe    | Numero empleados |
 +
  +-----------------+------------------+
 +
 
 +
8. Cantidad de empleados que han ingresado en la empresa en cada año.
 +
 
 +
  +-------+------------------+
 +
  |  Año  | Numero empleados |
 +
  +-------+------------------+
 +
 
 +
9. Para cada empleado y cada puesto ocupado los meses que ha trabajado en dicho puesto.
 +
 
 +
  +--------------+---------+------------------+
 +
  |  Nombre emp  |  Puesto | Meses trabajados |
 +
  +--------------+---------+------------------+
 +
 
 +
10. El nombre de los jefes de cada departamento y su telefono.
 +
 
 +
  +------------------------+------------------+---------------+
 +
  |  Nombre departamento  |  Nombre jefe    | Telefono jefe |
 +
  +------------------------+------------------+---------------+
 +
 
 +
11. El costo de cada departamento en el ultimo año (suma de salario *12 de todos los empleados mas comisiones)
 +
 
 +
  +------------------------+-----------------+
 +
  |  Nombre departamento  |  Coste año YYYY  |  |
 +
  +-----------------------+------------------+
 +
 
 +
 
 +
12. Para cada departamento el promedio de sueldo, el salario maximo i el mínimo pero sólo mostrar los departamentos con promedio superior a la media de todos los departamentos.
 +
 
 +
  +------------------------+-----------------+-----------------+
 +
  |  Nombre departamento  |  Sueldo medio  | Salario maximo  |
 +
  +------------------------+-----------------+-----------------+
 +
<!---->
 +
 
 +
==Solucions exercicis ==
 +
<!--
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T1 | Solucions T1]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T2 | Solucions T2]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T3 | Solucions T3]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T4 | Solucions T4]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T5 | Solucions T5]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T6 | Solucions T6]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T7 | Solucions T7]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T7-1 | Solucions T7-1]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T7-2 | Solucions T7-2]]-->
 +
<!--
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T8 | Solucions T8]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T9 | Solucions T9]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T10 | Solucions T10]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T11 | Solucions T11]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T12 | Solucions T12]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T13 | Solucions T13]]
 +
 
 +
:*[[M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions Repàs consultes HR | Solucions Repàs consultes HR]]
 +
-->

Revisió de 19:53, 14 nov 2023

Connectar-se a la base de dades

Usuari SCOTT: 

SQL> CONNECT SCOTT

Password: TIGER

Aixecar la base de dades: 

SQL> CONNECT SYS AS SYSDBA;

Password: MANAGER

SQL> STARTUP;

SQL> DISCONNECT;

Presentació Taules Usuari SCOTT

     Nom Usuari: scott
     contrassenya: tiger

SQL>DESC emp 

                           
Nombre                         Nulo     Tipo 
------------------------------ -------- ----------------
EMPNO                          NOT NULL NUMBER(4)       Codi d'empleat.
ENAME                                   VARCHAR2(10)    Cognom. 
JOB                                     VARCHAR2(9)     Ofici.
MGR                                     NUMBER(4)       Codi del cap de l'empleat.
HIREDATE                                DATE            Data de contractació.
SAL                                     NUMBER(7,2)     Salari.
COMM                                    NUMBER(7,2)     Comissió del empleats comercials (SALESMAN).
DEPTNO                                  NUMBER(2)       Codi del departament de l'empleat.

SQL>SELECT * FROM emp 

EMPNO   ENAME   JOB         MGR   HIREDATE   SAL    COMM  DEPTNO 
------  ------  ---------  -----  ---------  ----  -----  ------
7369	SMITH	CLERK	   7902	  17/12/80    800  (null)     20
7499 	ALLEN	SALESMAN   7698	  20/02/81   1600    300      30
7521 	WARD	SALESMAN   7698	  22/02/81   1250    500      30
7566	JONES	MANAGER	   7839	  02/04/81   2975  (null)     20
7654 	MARTIN	SALESMAN   7698	  28/09/81   1250   1400      30
7698	BLAKE	MANAGER	   7839	  01/05/81   2850  (null)     30
7782	CLARK	MANAGER	   7839	  09/06/81   2450  (null)     10
7788	SCOTT	ANALYST	   7566	  19/04/87   3000  (null)     20
7839	KING	PRESIDENT (null)  17/11/81   5000  (null)     10
7844	TURNER	SALESMAN   7698	  08/09/81   1500      0      30
7876	ADAMS	CLERK	   7788	  23/05/87   1100  (null)     20
7900	JAMES	CLERK	   7698	  03/12/81    950  (null)     30
7902	FORD	ANALYST	   7566	  03/12/81   3000  (null)     20
7934	MILLER	CLERK	   7782	  23/01/82   1300  (null)     10

SQL>DESC dept 

                           
Nombre                         Nulo     Tipo 
------------------------------ -------- ----------------
DEPTNO                          NOT NULL NUMBER(2)       Codi departament
DNAME                                    VARCHAR2(14)    Nom departament.  
LOC                                      VARCHAR2(13)    Localització.

SQL>SELECT * FROM dept 

DEPTNO      DNAME      LOC         
------  -----------   ----------
    10	ACCOUNTING    NEW YORK
    20	RESEARCH      DALLAS
    30	SALES	      CHICAGO
    40	OPERATIONS    BOSTON

SQL>DESC salgrade 

                           
Nombre                         Nulo     Tipo 
------------------------------ -------- ------------
GRADE                                   NUMBER      Nivell salarial. Cada empleat pertany a un nivell en funció del seu salari.
LOSAL                                   NUMBER      Salari mínim del nivell.  
HISAL                                   NUMBER      Salari màxim del nivell.

SQL>SELECT * FROM salgrade 

GRADE  LOSAL   HISAL         
-----  -----   -----   
1	 700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999

Disseny de la BD SCOTT simple

BD SCOTT SIMPLE.png


La taula SALGRADE presenta un cas especial en el que una taula queda "connectada" a la resta mitjançant un non equi join (en aquest cas mitjançant un rang de valors de salaris).

T1: Escriptura bàsica de sentències

1. Inicii una sessió de SQL*Plus utilitzant l'ID d'usuari i password facilitat pel professor.

2. Les comandes SQL*PLUS accedeixen a la base de dades. 

Cert / Fals

3. S'executarà correctament la sentència SELECT? 

Cert / Fals

SQL> SELECT rownum, ename, job, sal Salary
  2  FROM   emp;

4. S'executarà correctament la sentència SELECT? 

Cert / Fals

SQL> SELECT *
  2  FROM   salgrade;

5. Hi ha tres errors de codi en aquesta sentència. Pots identificar-los? 

SQL> SELECT empno, ename, salary x 12 ANNUAL SALARY
  2  FROM   emp;

6. Mostra l'estructura de la taula DEPT. Selecciona totes les dades de la taula DEPT.

7. Mostra l'estructura de la taula EMP. Fes una consulta per visualitzar el número d'empleat, cognom, ofici i data d'alta. Guarda la sentència SQL en un fitxer anomenat plq7.sql.

8. Executeu la consulta del fitxer plq7.sql.

9. Fes una consulta per visualitzar només els oficis de la taula EMP. 

JOB
-----------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN

10. Carrega plq7.sql en el búfer SQL. Posa-li nom als encapçalaments: Emp #, Employee, Job i Hire Date. 

Emp #                  Employee   Job       Hire Date                 
---------------------- ---------- --------- ------------------------- 
7369                   SMITH      CLERK     17/12/80                  
7499                   ALLEN      SALESMAN  20/02/81                  
7521                   WARD       SALESMAN  22/02/81                  
7566                   JONES      MANAGER   02/04/81                  
7654                   MARTIN     SALESMAN  28/09/81                  
7698                   BLAKE      MANAGER   01/05/81                  
7782                   CLARK      MANAGER   09/06/81                  
7788                   SCOTT      ANALYST   19/04/87                  
7839                   KING       PRESIDENT 17/11/81                  
7844                   TURNER     SALESMAN  08/09/81                  
7876                   ADAMS      CLERK     23/05/87                  
7900                   JAMES      CLERK     03/12/81                  
7902                   FORD       ANALYST   03/12/81                  
7934                   MILLER     CLERK     23/01/82

11. Visualitza el nom concatenat amb l'ofici, separat per una coma i i un espai i etiquete la columna amb Employee and Title. 

Employee and Title  
------------------- 
SMITHCLERK          
ALLENSALESMAN       
WARDSALESMAN        
JONESMANAGER        
MARTINSALESMAN      
BLAKEMANAGER        
CLARKMANAGER        
SCOTTANALYST        
KINGPRESIDENT       
TURNERSALESMAN      
ADAMSCLERK          
JAMESCLERK          
FORDANALYST         
MILLERCLERK

12. Fes una consulta per visualitzar totes les dades de la taula EMP. Separa cada columna amb una coma. Etiqueta la columna com THE_OUTPUT. 

THE_OUTPUT
---------------------------------------------
7369,SMITH,CLERK,7902,17/12/80,800,,20 
7499,ALLEN,SALESMAN,7698,20/02/81,1600,300,30 
7521,WARD,SALESMAN,7698,22/02/81,1250,500,30 
7566,JONES,MANAGER,7839,02/04/81,2975,,20 
7654,MARTIN,SALESMAN,7698,28/09/81,1250,1400,30 
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09/06/81,2450,,10 
7788,SCOTT,ANALYST,7566,19/04/87,3000,,20 
7839,KING,PRESIDENT,,17/11/81,5000,,10 
7844,TURNER,SALESMAN,7698,08/09/81,1500,0,30  
7876,ADAMS,CLERK,7788,23/05/87,1100,,20
7900,JAMES,CLERK,7698,03/12/81,950,,30  
7902,FORD,ANALYST,7566,03/12/81,3000,,20 
7934,MILLER,CLERK,7782,23/01/82,1300,,10


T2: 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$. Guarda la sentència SQL en un fitxer anomenat p2q1.sql. Executa la consulta. 

ENAME      SAL
-----      ---- 
JONES  	   2975 
SCOTT      3000
KING	   5000
FORD	   3000

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

ENAME      DEPTNO
------     ------
JONES	    20

3. Modifica p2q1.sql per visualitzar el nom i el salari de tots els empleats que tinguin un salari entre 1500$ i 2850$. Guarda a questa sentència SQL en un fitxer anomenat p2q3.sql. Executa la consulta. 

ENAME      SAL
------     ----
ALLEN	   1600
BLAKE	   2850
CLARK	   2450
TURNER	   1500

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. 

ENAME      JOB             HIREDATE
------     --------        --------  
ALLEN	   SALESMAN	   20/02/81
WARD	   SALESMAN	   22/02/81
JONES	   MANAGER	   02/04/81
BLAKE	   MANAGER	   01/05/81

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. 

ENAME      DEPTNO
------     ------
ALLEN	       30
BLAKE	       30
CLARK          10
JAMES	       30
KING	       10
MARTIN         30
MILLER	       10
TURNER	       30
WARD	       30

6. Modifica p2q3.sql 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. Tornar a guardar la sentència en un fitxer anomenat p2q6.sql. Executa la consulta. 

Employee   Monthly Salary
--------   --------------
ALLEN	             1600
BLAKE	             2850
CLARK	             2450
KING	             5000

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

ENAME      HIREDATE
------     --------
MILLER	   23/01/82

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

ENAME      JOB           
------     --------
KING       PRESIDENT

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

ENAME      SAL      COMM                   
-------    ----     ---- 
ALLEN      1600      300                    
TURNER     1500        0                      
MARTIN     1250     1400                   
WARD       1250      500

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

ENAME      
------- 
BLAKE      
CLARK      
ADAMS

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. 

ENAME      
-------
ALLEN      
MILLER

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. 

ENAME      JOB       SAL                    
-------    -----     ----
SMITH      CLERK      800                    
ADAMS      CLERK     1100                   
JAMES      CLERK      950                    
MILLER     CLERK     1300

13. Modifica p2q6.sql per mostrar el nom, salari i comissió de tots els empleats que tinguin una comissió superior al seu salari incrementat un 10%. Executa la consulta. Guarda-la com p2q13.sql. 

Employee   Monthly Salary    COMM                   
---------- --------------    ---- 
MARTIN               1250    1400

T3: Funcions a nivell de fila

1. Fes una consulta que mostri la data actual. Etiqueta la columna com a Date. 

Date                     
--------
10/12/13

2. Mostra el número de l'empleat, nom, salari i increment del salari del 15% sense decimals. Etiqueta la columna com a New Salary. Guarda la sentència SQL en un fitxer anomenat p3q2.sql.


3. Executa la consulta del fitxer p3q2.sql. 

EMPNO                  ENAME      SAL                    New Salary             
---------------------- ---------- ---------------------- ---------------------- 
7369                   SMITH       800                    920                    
7499                   ALLEN      1600                   1840                   
7521                   WARD       1250                   1438                   
7566                   JONES      2975                   3421                   
7654                   MARTIN     1250                   1438                   
7698                   BLAKE      2850                   3278                   
7782                   CLARK      2450                   2818                   
7788                   SCOTT      3000                   3450                   
7839                   KING       5000                   5750                   
7844                   TURNER     1500                   1725                   
7876                   ADAMS      1100                   1265                   
7900                   JAMES       950                   1093                   
7902                   FORD       3000                   3450                   
7934                   MILLER     1300                   1495

4. Modifica la consulta p3q2.sql per afegir una columna que resti el antic salari del nou. Etiqueteu la columna com Increase. Torna a executar la consulta. 

EMPNO                  ENAME      SAL                    New Salary             Increase               
---------------------- ---------- ---------------------- ---------------------- ---------------------- 
7369                   SMITH       800                    920                   120                    
7499                   ALLEN      1600                   1840                   240                    
7521                   WARD       1250                   1438                   188                    
7566                   JONES      2975                   3421                   446                    
7654                   MARTIN     1250                   1438                   188                    
7698                   BLAKE      2850                   3278                   428                    
7782                   CLARK      2450                   2818                   368                    
7788                   SCOTT      3000                   3450                   450                    
7839                   KING       5000                   5750                   750                    
7844                   TURNER     1500                   1725                   225                    
7876                   ADAMS      1100                   1265                   165                    
7900                   JAMES       950                   1093                   143                    
7902                   FORD       3000                   3450                   450                    
7934                   MILLER     1300                   1495                   195

5. Mostra el nom de l'empleat, data de contractació i data de revisió del salari, la cual és el primer Dilluns desprès de sis mesos de servei. Etiqueu la columna com REVIEW. Formata les dates per a que apareixin similars a "Sunday, the Seventh of September, 1981". 

EMPNO                  HIREDATE                  REVIEW
---------------------- ------------------------- ------------------------------------------
7369                   17/12/80                  Lunes, the Twenty-Second of Junio,  1981                                                                   
7499                   20/02/81                  Lunes, the Twenty-Fourth of Agosto, 1981                                                                   
7521                   22/02/81                  Lunes, the Twenty-Fourth of Agosto, 1981                                                                   
7566                   02/04/81                  Lunes, the Fifth of Octubre, 1981                                                                          
7654                   28/09/81                  Lunes, the Twenty-Ninth of Marzo, 1982                                                                     
7698                   01/05/81                  Lunes, the Second of Noviembre, 1981                                                                       
7782                   09/06/81                  Lunes, the Fourteenth of Diciembre, 1981                                                                   
7788                   19/04/87                  Lunes, the Twenty-Sixth of Octubre, 1987                                                                   
7839                   17/11/81                  Lunes, the Twenty-Fourth of Mayo, 1982                                                                     
7844                   08/09/81                  Lunes, the Fifteenth of Marzo, 1982                                                                        
7876                   23/05/87                  Lunes, the Thirtieth of Noviembre, 1987                                                                    
7900                   03/12/81                  Lunes, the Seventh of Junio, 1982                                                                          
7902                   03/12/81                  Lunes, the Seventh of Junio, 1982                                                                          
7934                   23/01/82                  Lunes, the Twenty-Sixth of Julio, 1982

6. Per a cada empleat mostra el nom de l'empleat i calcula el nombre de mesos entre avui i la data de contractació. Etiqueta la consulta com MONTHS_WORKED. Ordena els resultats per el nombre de mesos treballats. Arrodoneix el nombre de mesos cap amunt fins al proper nombre enter. 

ENAME      MONTHS_WORKED          
---------- --------------
ADAMS      319                    
SCOTT      320                    
MILLER     383                    
FORD       384                    
JAMES      384                    
KING       385                    
MARTIN     386                    
TURNER     387                    
CLARK      390                    
BLAKE      391                    
JONES      392                    
WARD       394                    
ALLEN      394                    
SMITH      396


7. Escriu una consulta que mostri la següent informació de cada empleat: <nom empleat> earns <salari> monthly but wants <3 vegades més>. Etiqueta la columna com Dream Salaries. 

Dream Salaries                                              
----------------------------------------------------------- 
SMITH earns $800.00 monthly but wants $2,400.00.            
ALLEN earns $1,600.00 monthly but wants $4,800.00.          
WARD earns $1,250.00 monthly but wants $3,750.00.           
JONES earns $2,975.00 monthly but wants $8,925.00.          
MARTIN earns $1,250.00 monthly but wants $3,750.00.         
BLAKE earns $2,850.00 monthly but wants $8,550.00.          
CLARK earns $2,450.00 monthly but wants $7,350.00.          
SCOTT earns $3,000.00 monthly but wants $9,000.00.          
KING earns $5,000.00 monthly but wants $15,000.00.          
TURNER earns $1,500.00 monthly but wants $4,500.00.         
ADAMS earns $1,100.00 monthly but wants $3,300.00.          
JAMES earns $950.00 monthly but wants $2,850.00.            
FORD earns $3,000.00 monthly but wants $9,000.00.           
MILLER earns $1,300.00 monthly but wants $3,900.00.

8. Fes una consulta per mostrar el nom i salari de tots els empleats. Formata el salari per a que tingui una longitud de 15 caràcters i ompli el costat esquerra amb $. Etiqueta la columna com SALARY. 

ENAME      SALARY          
---------- --------------- 
SMITH      $$$$$$$$$$$$800 
ALLEN      $$$$$$$$$$$1600 
WARD       $$$$$$$$$$$1250 
JONES      $$$$$$$$$$$2975 
MARTIN     $$$$$$$$$$$1250 
BLAKE      $$$$$$$$$$$2850 
CLARK      $$$$$$$$$$$2450 
SCOTT      $$$$$$$$$$$3000 
KING       $$$$$$$$$$$5000 
TURNER     $$$$$$$$$$$1500 
ADAMS      $$$$$$$$$$$1100 
JAMES      $$$$$$$$$$$$950 
FORD       $$$$$$$$$$$3000 
MILLER     $$$$$$$$$$$1300

9. Escriu una consulta que visualitzi el nom de l'empleat amb la primera lletra en majúscules, la resta en minúscules i la longitud dels seus noms, para tots els empleats que comencin per J, A o M. Doneu a les columnes una etiqueta apropiada. 

Name       Length                 
---------- -----------
Allen      5                      
Jones      5                      
Martin     6                      
Adams      5                      
James      5                      
Miller     6

10. Mostra el nom, la data de contractació i dia de la setmana que va començar l'empleat. Etiqueta la columna com DAY. Ordena els resultats per el dia de la setmana començant per Dilluns. 

ENAME      HIREDATE                  DAY                                  
---------- ------------------------- -------------- 
MARTIN     28/09/81                  LUNES                                
CLARK      09/06/81                  MARTES                               
TURNER     08/09/81                  MARTES                               
KING       17/11/81                  MARTES                               
SMITH      17/12/80                  MIÉRCOLES                            
JAMES      03/12/81                  JUEVES                               
JONES      02/04/81                  JUEVES                               
FORD       03/12/81                  JUEVES                               
ALLEN      20/02/81                  VIERNES                              
BLAKE      01/05/81                  VIERNES                              
ADAMS      23/05/87                  SÁBADO                               
MILLER     23/01/82                  SÁBADO                               
WARD       22/02/81                  DOMINGO                              
SCOTT      19/04/87                  DOMINGO

11. Fes una consulta que mostri el nom de l'empleat i importi de la comissió. Si el empleat no té comissió mostreu el literal "No Commission". Etiqueta la columna com COMM. 

ENAME      COMM                                     
---------- --------------
SMITH      No Comission                             
ALLEN      300                                      
WARD       500                                      
JONES      No Comission                             
MARTIN     1400                                     
BLAKE      No Comission                             
CLARK      No Comission                             
SCOTT      No Comission                             
KING       No Comission                             
TURNER     0                                        
ADAMS      No Comission                             
JAMES      No Comission                             
FORD       No Comission                             
MILLER     No Comission


T4: 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. 

ENAME      DEPTNO                 DNAME          
---------- ---------------------- -------------- 
SMITH      20                     RESEARCH       
ALLEN      30                     SALES          
WARD       30                     SALES          
JONES      20                     RESEARCH       
MARTIN     30                     SALES          
BLAKE      30                     SALES          
CLARK      10                     ACCOUNTING     
SCOTT      20                     RESEARCH       
KING       10                     ACCOUNTING     
TURNER     30                     SALES          
ADAMS      20                     RESEARCH       
JAMES      30                     SALES          
FORD       20                     RESEARCH       
MILLER     10                     ACCOUNTING

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

JOB       LOC           
--------- ------------- 
SALESMAN  CHICAGO       
MANAGER   CHICAGO       
CLERK     CHICAGO

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

ENAME      DNAME          LOC           
---------- -------------- ------------- 
ALLEN      SALES          CHICAGO       
WARD       SALES          CHICAGO       
MARTIN     SALES          CHICAGO       
TURNER     SALES          CHICAGO

4. Mostra el nom de l'empleat i nom del departament de tots els empleats que tenen una A en el seu nom. Guarda la sentència SQL en un fitxer anomenat p4q4.sql. 

ENAME      DNAME          
---------- -------------- 
ALLEN      SALES          
WARD       SALES          
MARTIN     SALES          
BLAKE      SALES          
CLARK      ACCOUNTING     
ADAMS      RESEARCH       
JAMES      SALES

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. 

ENAME      JOB       DEPTNO                 DNAME          
---------- --------- ---------------------- -------------- 
SMITH      CLERK     20                     RESEARCH       
JONES      MANAGER   20                     RESEARCH       
SCOTT      ANALYST   20                     RESEARCH       
ADAMS      CLERK     20                     RESEARCH       
FORD       ANALYST   20                     RESEARCH

6. Mostra el nom de l'empleat i el número del 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. Guarda la sentència SQL en un fitxer anomenat p4q6.sql. 

Employee   Emp#                   Manager    Mgr#                   
---------- ---------------------- ---------- ---------------------- 
SMITH      7369                   FORD       7902                   
ALLEN      7499                   BLAKE      7698                   
WARD       7521                   BLAKE      7698                   
JONES      7566                   KING       7839                   
MARTIN     7654                   BLAKE      7698                   
BLAKE      7698                   KING       7839                   
CLARK      7782                   KING       7839                   
SCOTT      7788                   JONES      7566                   
TURNER     7844                   BLAKE      7698                   
ADAMS      7876                   SCOTT      7788                   
JAMES      7900                   BLAKE      7698                   
FORD       7902                   JONES      7566                   
MILLER     7934                   CLARK      7782

7. Modifica p4q6.sql per mostrar tots els empleats incloent a King, que no té cap. Torna a guardar la consulta com p4q7.sql. Executa la consulta. 

Employee   Emp#                   Manager    Mgr#                   
---------- ---------------------- ---------- ---------------------- 
SMITH      7369                   FORD       7902                   
ALLEN      7499                   BLAKE      7698                   
WARD       7521                   BLAKE      7698                   
JONES      7566                   KING       7839                   
MARTIN     7654                   BLAKE      7698                   
BLAKE      7698                   KING       7839                   
CLARK      7782                   KING       7839                   
SCOTT      7788                   JONES      7566                   
KING       7839                                                     
TURNER     7844                   BLAKE      7698                   
ADAMS      7876                   SCOTT      7788                   
JAMES      7900                   BLAKE      7698                   
FORD       7902                   JONES      7566                   
MILLER     7934                   CLARK      7782

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. 

DEPARTAMENT            EMPLOYEE   COLLEAGUE  
---------------------- ---------- ---------- 
20                     FORD       SMITH      
20                     ADAMS      SMITH      
20                     SCOTT      SMITH      
20                     JONES      SMITH      
30                     JAMES      ALLEN      
30                     TURNER     ALLEN      
30                     BLAKE      ALLEN      
30                     MARTIN     ALLEN      
30                     WARD       ALLEN      
30                     JAMES      WARD       
30                     TURNER     WARD       
30                     BLAKE      WARD       
30                     MARTIN     WARD       
30                     ALLEN      WARD       
20                     FORD       JONES      
20                     ADAMS      JONES      
20                     SCOTT      JONES      
20                     SMITH      JONES      
30                     JAMES      MARTIN     
30                     TURNER     MARTIN     
30                     BLAKE      MARTIN     
30                     WARD       MARTIN     
30                     ALLEN      MARTIN     
30                     JAMES      BLAKE      
30                     TURNER     BLAKE      
30                     MARTIN     BLAKE      
30                     WARD       BLAKE      
30                     ALLEN      BLAKE      
10                     MILLER     CLARK      
10                     KING       CLARK      
20                     FORD       SCOTT      
20                     ADAMS      SCOTT      
20                     JONES      SCOTT      
20                     SMITH      SCOTT      
10                     MILLER     KING       
10                     CLARK      KING       
30                     JAMES      TURNER     
30                     BLAKE      TURNER     
30                     MARTIN     TURNER     
30                     WARD       TURNER     
30                     ALLEN      TURNER     
20                     FORD       ADAMS      
20                     SCOTT      ADAMS      
20                     JONES      ADAMS      
20                     SMITH      ADAMS      
30                     TURNER     JAMES      
30                     BLAKE      JAMES      
30                     MARTIN     JAMES      
30                     WARD       JAMES      
30                     ALLEN      JAMES      
20                     ADAMS      FORD       
20                     SCOTT      FORD       
20                     JONES      FORD       
20                     SMITH      FORD       
10                     KING       MILLER     
10                     CLARK      MILLER

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. 

ENAME      JOB       DNAME          SAL                    GRADE                  
---------- --------- -------------- ---------------------- ---------------------- 
SMITH      CLERK     RESEARCH       800                    1                      
JAMES      CLERK     SALES          950                    1                      
ADAMS      CLERK     RESEARCH       1100                   1                      
WARD       SALESMAN  SALES          1250                   2                      
MARTIN     SALESMAN  SALES          1250                   2                      
MILLER     CLERK     ACCOUNTING     1300                   2                      
TURNER     SALESMAN  SALES          1500                   3                      
ALLEN      SALESMAN  SALES          1600                   3                      
CLARK      MANAGER   ACCOUNTING     2450                   4                      
BLAKE      MANAGER   SALES          2850                   4                      
JONES      MANAGER   RESEARCH       2975                   4                      
SCOTT      ANALYST   RESEARCH       3000                   4                      
FORD       ANALYST   RESEARCH       3000                   4                      
KING       PRESIDENT ACCOUNTING     5000                   5

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

ENAME      HIREDATE                  
---------- ------------------------- 
MARTIN     28/09/81                  
CLARK      09/06/81                  
SCOTT      19/04/87                  
KING       17/11/81                  
TURNER     08/09/81                  
ADAMS      23/05/87                  
JAMES      03/12/81                  
FORD       03/12/81                  
MILLER     23/01/82

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. Etiquet les columnes com Employee. Emp Hiredate, Manager i Mgr respectivament. 

Employee   Emp Hiredate              Manager    Mgr Hiredate              
---------- ------------------------- ---------- ------------------------- 
SMITH      17/12/80                  FORD       03/12/81                  
ALLEN      20/02/81                  BLAKE      01/05/81                  
WARD       22/02/81                  BLAKE      01/05/81                  
JONES      02/04/81                  KING       17/11/81                  
BLAKE      01/05/81                  KING       17/11/81                  
CLARK      09/06/81                  KING       17/11/81

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. 

EMPLOYEE_AND_THEIR_SALARIS 
-------------------------------------------------------------
KING      **************************************************
FORD      ******************************
SCOTT     ******************************
JONES     *****************************
BLAKE     ****************************
CLARK     ************************
ALLEN     ****************
TURNER    ***************
MILLER    *************
WARD      ************
MARTIN    ************
ADAMS     ***********
JAMES     *********
SMITH     ********

T5: Dades agregades. Funcions de grup

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

Cert / Fals

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

Cert / Fals

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

Cert / Fals

4. 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. Salva la sentència SQL en un fitxer anomenat p5q4.sql. 

Maximum                Minimum                Sum                    Average                
---------------------- ---------------------- ---------------------- ---------------------- 
5000                   800                    29025                  2073

5. Modifica p5q4.sql per mostrar: màxim, mínim, suma i mitjana aritmètica de salaris per a cada ofici. Salva la sentència SQL en un fitxer anomenat p5q5.sql. Executa la consulta. 

JOB       Maximum                Minimum                Sum                    Average                
--------- ---------------------- ---------------------- ---------------------- ---------------------- 
CLERK     1300                   800                    4150                   1038                   
SALESMAN  1600                   1250                   5600                   1400                   
PRESIDENT 5000                   5000                   5000                   5000                   
MANAGER   2975                   2450                   8275                   2758                   
ANALYST   3000                   3000                   6000                   3000

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

JOB       COUNT(*)               
--------- ---------------------- 
CLERK     4                      
SALESMAN  4                      
PRESIDENT 1                      
MANAGER   3                      
ANALYST   2

7. Determina el nombre total de directors. Etiqueta la columna com "nº de directors". 

Number of Managers     
---------------------- 
6

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

DIFFERENCE             
---------------------- 
4200

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

MGR                    MIN(SAL)               
---------------------- ---------------------- 
7566                   3000                   
7839                   2450                   
7782                   1300                   
7788                   1100

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

DNAME          LOC           Number of People       Salary                 
-------------- ------------- ---------------------- ---------------------- 
RESEARCH       DALLAS        5                      2175                   
SALES          CHICAGO       6                      1566,67                
ACCOUNTING     NEW YORK      3                      2916,67

11. Fes una consulta que mostri el nombre total d'empleats i d'aquest total el nombre dels que es van contractar en 1980, 1981, 1982 i 1983. Etiqueta les columnes com TOTAL, 1980, 1981, 1982 i 1983. 

TOTAL                  1980                   1981                   1982                   1983                   
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
14                     1                      10                     1                      0

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

Job       Dept 10                Dept 20                Dept 30                Total                  
--------- ---------------------- ---------------------- ---------------------- ---------------------- 
CLERK     1300                   1900                   950                    4150                   
SALESMAN                                                5600                   5600                   
PRESIDENT 5000                                                                 5000                   
MANAGER   2450                   2975                   2850                   8275                   
ANALYST                          6000                                          6000


T6: Subconsultes

1. 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). 

ENAME      HIREDATE                  
---------- ------------------------- 
JAMES      03/12/81                  
TURNER     08/09/81                  
MARTIN     28/09/81                  
WARD       22/02/81                  
ALLEN      20/02/81

2. Fes una subconsulta 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. 

EMPNO                  ENAME      
---------------------- ---------- 
7839                   KING       
7902                   FORD       
7788                   SCOTT      
7566                   JONES      
7698                   BLAKE      
7782                   CLARK

3. 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". Salva la sentència SQL en un fitxer anomenat p6q3.sql. 

EMPNO                  ENAME      
---------------------- ---------- 
7902                   FORD       
7876                   ADAMS      
7788                   SCOTT      
7566                   JONES      
7369                   SMITH      
7900                   JAMES      
7844                   TURNER     
7698                   BLAKE      
7654                   MARTIN     
7521                   WARD       
7499                   ALLEN

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

ENAME      DEPTNO                 JOB       
---------- ---------------------- --------- 
SMITH      20                     CLERK     
JONES      20                     MANAGER   
SCOTT      20                     ANALYST   
ADAMS      20                     CLERK     
FORD       20                     ANALYST

5. Mostra el nom i el salari de tots els empleats que depenguin de "King". 

ENAME      SAL                    
---------- ---------------------- 
JONES      2975                   
BLAKE      2850                   
CLARK      2450

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

DEPTNO                 ENAME      JOB       
---------------------- ---------- --------- 
30                     ALLEN      SALESMAN  
30                     WARD       SALESMAN  
30                     MARTIN     SALESMAN  
30                     BLAKE      MANAGER   
30                     TURNER     SALESMAN  
30                     JAMES      CLERK

7. Modifica el fitxer p6q3.sql 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. Salva el fitxer, com p6q7.sql. 

EMPNO                  ENAME      SAL                    
---------------------- ---------- ---------------------- 
7902                   FORD       3000                   
7788                   SCOTT      3000                   
7566                   JONES      2975                   
7698                   BLAKE      2850


T7: Subconsultes multicolumna

1. 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ó. 

ENAME      DEPTNO                 SAL                    
---------- ---------------------- ---------------------- 
ALLEN      30                     1600                   
MARTIN     30                     1250                   
WARD       30                     1250                   
TURNER     30                     1500

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

ENAME      DNAME          SAL                    
---------- -------------- ---------------------- 
SMITH      RESEARCH       800                    
JONES      RESEARCH       2975                   
SCOTT      RESEARCH       3000                   
FORD       RESEARCH       3000                   
ADAMS      RESEARCH       1100

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

ENAME      HIREDATE                  SAL                    
---------- ------------------------- ---------------------- 
FORD       03/12/81                  3000

4. Fes una consulta per mostrar als empleats que guanyin un salari superior al salari de qualsevol empleat "CLERK". Ordena el resultat pel salari de forma descendent. 

ENAME      JOB       SAL                    
---------- --------- ---------------------- 
KING       PRESIDENT 5000                   
FORD       ANALYST   3000                   
SCOTT      ANALYST   3000                   
JONES      MANAGER   2975                   
BLAKE      MANAGER   2850                   
CLARK      MANAGER   2450                   
ALLEN      SALESMAN  1600                   
TURNER     SALESMAN  1500


T7-1: Exists i Operadors SET

1.Empleats que tinguin algun empleat al seu càrrec (EXISTS).


2.Departaments que no tinguin empleats (NOT EXISTS).


3.Empleats que no tenen cap empleat al seu càrrec (NOT EXISTS).


4.Cognoms dels empleats que tinguin un o més companys de feina en els seus departaments amb dates de contractació posteriors però salaris més alts (EXISTS).


5.Mostreu l'identificador dels empleats i l'identificador de totes les feines que han tingut (UNION).


6.El mateix que la 5, però considerant també el departament on han desenvolupat les feines (UNION).


7.Mostreu l'identificador dels empleats i l'identificador de totes les feines que han tingut en cada departament (UNION ALL).


8.Mostreu els identificadors dels empleats i del càrrec dels empleats que actualment tinguin un càrrec que varen ocupar en un altre període en la companyia (INTERSECT).


9.Igual que la 8, però considerant també el departament on han treballat (INTERSECT).


10.Mostreu els identificadors dels empleats i del càrrec dels empleats que no han canviat mai de càrrec (MINUS).


11.Mostreu els identificadors dels departaments per a departaments que no continguin l'ID de càrrec ST_CLERK (MINUS).


12.Mostreu l'identificador de país i el nom dels països que no tenen departaments ubicats en ells (MINUS).


13.Mostreu els identificadors dels departaments i dels càrrecs dels departaments 10, 50 i 20 (UNION).


T7-2: Exercicis consultes complexes

Esquema scot ampliada.png


1.Quantitat de producte venuda a cada client (Descripcio producte i quantitat).


2.Import total venut per cada empleat (Nom empleat i total).


3.Empleats que han venut menys que la mitja de tots els empleats.


4.Quantitat de producte venut. Ordenat per producte (Descripcio producte i quantitat).


5.Quantitat de producte venut. Ordenat per quantitat de forma descendent. Han d'aparèixer tots els productes.


6.Quants clients a cada estat?


7.Quantes ordres per any i mes?


8.Quantes ordres per any?


9.En quants productes surt la paraula "TENNIS"?


10.Increment del preus dels productes des del primer preu fins l'actual.


11.Màximes quantitats de cada producte venudes en una sola comanda. Ordenades de forma ascendent.


12.Mostrar quantitat de cada producte venut (Descripció i total unitats venudes). Només ens interessen aquells productes dels que s'hagin venut més de 8 unitats.


13.Quantitat de producte venut (Descripció i total unitats venudes). Ordenat per producte. Han d'aparèixer tots els productes.


14.Quantitat de producte venut per empleat (Nom empleat, Descripció del producte i quantitat venuda).


15.Número d'ordres de cada COMMPLAN.


16.Número de productes venuts a cada ordre. Han d'aparèixer totes les ordres.

17. Clients que han comprat més de 5 unitats d'algun producte en una comanda (EXISTS).

18. Quins clients han comprat més que la mitjana.

19. Quants clients han comprat més que la mitjana.

20. Quants empleats no cobren comissió a cada departament. Han d'apareixer tots els departaments.

21. Mostrar el prodid dels productes que no s'han venut mai (MINUS).

22. Mostrar el prodid i la descripció dels productes que no s'han venut mai (MINUS).

T8: Generació d'informes

Determina quina de les següents sentències són certes o falses:

1. Una variable d'ampersand simple només es demana una vegada? 

Cert / Fals

2. L'ordre ACCEPT és de SQL. 

Cert / Fals

Les següents preguntes utilitzen les taules EMP, CUSTOMER i PRODUCT. 

Guardeu tots els scripts en la carpeta C:\oraclexe\app\oracle\product\10.2.0\server\BIN

3. Escriu un arxiu d'ordres que mostri el nom de l'empleat concatenat amb l'ofici i la data d'ingrés per als empleats contractats en un rang de dates específiques. Demana a l'usuari el rang utilitzant l'ordre ACCEPT. Utilitzeu el format MM/DD/YY. Salva la sentència en un fitxer anomenat p8q3.sql. El resultat s'ha de veure com a continuació: 

     Please enter the low date range ("MM/DD/YYYY"): 01/01/1981
     Please enter the high date range ("MM/DD/YYYY"): 01/01/1982

     EMPLOYEES                 HIREDATE                  
     ------------------------- ------------------------- 
     ALLEN, SALESMAN           20/02/81                  
     WARD, SALESMAN            22/02/81                  
     JONES, MANAGER            02/04/81                  
     MARTIN, SALESMAN          28/09/81                  
     BLAKE, MANAGER            01/05/81                  
     CLARK, MANAGER            09/06/81                  
     KING, PRESIDENT           17/11/81                  
     TURNER, SALESMAN          08/09/81                  
     JAMES, CLERK              03/12/81                  
     FORD, ANALYST             03/12/81                  

      10 filas seleccionadas


4. Fes un script que mostri el nom, ofici i nom del departament. La condició de cerca no serà sensible a majúscules/minúscules. Salva el script com p8q4.sql. 

     Please enter the location name: Dallas     

     EMPLOYEE NAME   JOB           DEPARTAMENT NAME          
     --------------- ------------- ---------------- 
     SMITH	     CLERK	   RESEARCH
     JONES	     MANAGER	   RESEARCH
     SCOTT	     ANALYST	   RESEARCH
     ADAMS	     CLERK	   RESEARCH
     FORD	     ANALYST	   RESEARCH

5. Modifica el fitxer p8q4.sql per crear un informe amb el nom del departament, nom, data d'alta i salari anual, per a tots els empleats de determinada localitat. Pregunta a l'usuari per la localitat. Etiqueta les columnes segons el model. Salva el fitxer com p8q5.sql. 

     Please enter location name: Chicago
     
     DEPARTMENT  EMPLOYEE       START                     ANNUAL
     NAME        NAME           DATE          SALARY      SALARY
     ----------  -------------  ---------  ---------  ----------
     SALES       BLAKE          01-MAY-81  $2,850.00  $34,200.00
                 MARTIN         28-SEP-81  $1,250.00  $15,000.00
                 ALLEN          20-FEB-81  $1,600.00  $19,200.00
                 TURNER         08-SEP-81  $1,500.00  $18.000.00
                 JAMES          03-DEC-81    $950.00  $11,400.00
                 WARD           22-FEB-81  $1,250.00  $15,000.00

Fes els següents informes utilitzant l'ordre BREAK.

6. 

        
     DEPARTMENT  EMPLOYEE       
     NAME        NAME           
     ----------  -------------  
     ACCOUNTING  CLARK
                 KING
                 MILLER
     RESEARCH    JONES
                 FORD
                 ADAM
                 SMITH
                 SCOTT
     SALES       WARD
                 TURNER
                 ALLEN
                 JAMES
                 BLAKE          
                 MARTIN

7. 

        
     DEPARTMENT  EMPLOYEE       
     NAME        NAME           
     ----------  -------------  
     ACCOUNTING  CLARK
                 KING
                 MILLER

     RESEARCH    JONES
                 FORD
                 ADAM
                 SMITH
                 SCOTT

     SALES       WARD
                 TURNER
                 ALLEN
                 JAMES
                 BLAKE          
                 MARTIN

8. 

        
     DEPARTMENT  EMPLOYEE       EMPLOYEE
     NAME        JOB            NAME           
     ----------  -------------  -----------
     ACCOUNTING  CLERK          MILLER

                 MANAGER        CLARK

                 PRESIDENT      KING
                 


     RESEARCH    ANALYST        SCOTT
                                FORD
            
                 CLERK          ADAMS
                                SMITH
                
                 MANAGER        JONES



     SALES       CLERK          JAMES

                 MANAGER        BLAKE

                 SALESMAN       MARTIN
                                WARD
                                ALLEN
                                TURNER

T9: Manipulació de dades

1. Crea la taula MY_EMPLOYEE: 

CREATE TABLE MY_EMPLOYEE(
     ID          NUMBER(4) PRIMARY KEY,
     LAST_NAME	 VARCHAR2(25),
     FIRST_NAME  VARCHAR2(25),
     USERID	 VARCHAR2(25),
     SALARY	 NUMBER(9,2));

2. Mostra l'estructura de la taula MY_EMPLOYEE:

3. Afegeix el primer registre del exemple següent. No explicitis les columnes en la clàusula INSERT.

ID LAST_NAME FIRST_NAME USERID SALARY
1 Patel Ralph rpatel 795
2 Dancs Betty bdancs 860
3 Biri Ben bbiri 1100
4 Newman Chad cnewman 750
5 Ropeburn Audry aropebur 1550

4. Afegeix el segon registre del exemple anterior. Explicita les columnes en la clàusula INSERT.

5. Mostra les dades afegides i comprova que són correctes.

6. Fes un script anomenat loademp.sql per afegir registres en la taula MY_EMPLOYEE de forma interactiva. Pregunta a l'usuari pel identificador, nom, cognom i salari de l'empleat. Concatena la primera lletra del nom i les primeres set lletres del cognom, per obtenir al seu "userid".

7. Afegeix a la taula MY_EMPLOYEE els dos registres següents del exemple anterior, executant el script.

8. Confirma que les dades s'han afegit correctament.

9. Feu commit per fer que les insercions siguin permanents.

Modifica i elimina dades de la taula MY_EMPLOYEE:

10. Canvia el cognom del empleat número 3 a "Drexter".

11. Per a tots els empleats amb salari menor a 900 $, assigna'ls un salari de 1000 $.

12. Comprova els canvis realitzat a la taula MY_EMPLOYEE.

13. Elimina a "Betty Dancs" de la taula MY_EMPLOYEE.

14. Comprova els canvis realitzat a la taula MY_EMPLOYEE.

15. Fes commit dels canvis pendents.

Controla les transaccions de la taula MY_EMPLOYEE:

16. Afegeix a la taula l'últim registre del exemple, executant el script creat en l'apartat 6.

17. Comprova que les dades s'han afegit correctament.

18. Marca un punt intermedi en el procés de la transacció.

19. Elimina tots els registres de la taula MY_EMPLOYEE.

20. Comprova que no hi ha cap registre a la taula.

21. Descarta l'operació de eliminació més recent, sense descarta la inserció prèvia.

22. Comprova l'estat de la taula.

23. Fes permanents els canvis pendents.

T10: Creació i gestió de taules

1. Fes un script, anomenant p10q1.sql, amb el codi per crear la taula DEPARTMENT segons el següent model. Executa el script i comprova que la taula s'ha creat correctament.

Column Name Id Name
Key Type
Nulls/Unique
FK Table
Fk Column
DataType Number Varchar2
Length 7 25 
          Name       Null?    Type
          ---------- -------- ----------
          ID                  NUMBER(7)
          NAME                VARCHAR2(25)


2. Omple la taula DEPARTMENT amb les dades de la taula DEPT. Inclou només les columnes que precisis.


3. Fes un script, anomenant p10q3.sql, amb el codi per crear la taula EMPLOYEE segons el següent model. Executa el script i comprova que la taula s'ha creat correctament.

Column Name Id Last_Name First_Name Dept_Id
Key Type
Nulls/Unique
FK Table
Fk Column
DataType Number Varchar2 Varchar2 Number
Length 7 25 25 7 
          Name       Null?    Type
          ---------- -------- ----------
          ID                  NUMBER(7)
          LAST_NAME           VARCHAR2(25)
          FIRST_NAME          VARCHAR2(25)  
          DEPT_ID             NUMBER(7)

4. Modifica la taula EMPLOYEE per permetre noms llargs dels empleats. Comprova els canvis realitzats. 

          Name       Null?    Type
          ---------- -------- ----------
          ID                  NUMBER(7)
          LAST_NAME           VARCHAR2(50)
          FIRST_NAME          VARCHAR2(25)  
          DEPT_ID             NUMBER(7)

5. Comprova que les taules DEPPARTMENT i EMPLOYEE han estat reconegudes pel diccionari de dades (utilitza USER_TABLES). 

          TABLE_NAME
          ------------
          DEPARTMENT
          EMPLOYEE

6. Crea la taula EMPLOYEE2 basada en l'estructura de la taula EMP. Inclou només les columnes: EMPNO, ENAME i DEPTNO. Etiqueta les columnes en la nova taula, respectivament: ID, LAST_NAME i DEPT_ID.


7. Elimina la taula EMPLOYEE.

8. Reanomena la taula EMPLOYEE2 com EMPLOYEE.

9. Afegeix comentaris a les taules DEPARTMENT i EMPLOYEE, descrivint les taules. Comprova la informació en el diccionari de dades.

T11: Restriccions

1. Afegeix una restricció PRIMARY KEY a nivell de la taula EMPLOYEE usant la columna ID. La restricció hauria d'estar activada (enabled) quan es crea.

2. Crea una restricció PRIMARY KEY sobre la taula DEPARTMENT usant la columna ID. La restricció hauria d'estar activada (enabled) quan es crea.

3. Afegeix una referència FOREIGN KEY a la taula EMPLOYEE, que asseguri que l'empleat no està assignat a un departament que no existeix.

4. Verifica que las restriccions anteriors s'han afegit correctament, consultant USER_CONSTRAINTS. Observa els tipus i noms de les restriccions. Salva la sentència en un fitxer anomenat p11q4.sql. 

     CONSTRAINT_NAME            C
     ------------------------   --
     DEPARTMENT_ID_PK           P
     EMPLOYEE_ID_PK             P
     EMPLOYEE_DEPT_ID_FK        R

5. Mostra els noms i tipus d'objectes consultant la vista del diccionari de dades USER_OBJECTS per a les taules EMPLOYEE i DEPARTMENT. Formata les columnes per a una lectura més adient. Observa l'existència d'índexs per a les noves taules. 

     OBJECT_NAME            OBJECT_TYPE
     --------------------   -----------
     DEPARTMENT             TABLE    
     DEPARTMENT_ID_PK       INDEX
     EMPLOYEE               TABLE
     EMPLOYEE_ID_PK         INDEX

6. Modifica la taula EMPLOYEE, afegint la columna SALARY de tipus NUMBER(7).


T12: Creació de vistes

1. Crea una vista anomenada EMP_VU basada en el número d'empleat, nom de l'empleat i número de departament des de la taula EMP. Canvia la capçalera del nom del empleat a EMPLOYEE.

2. Mostra el contingut de la vista EMP_VU. 

EMPNO   EMPLOYEE   DEPTNO 
------  ---------  ------
7369	SMITH	       20
7499 	ALLEN	       30
7521 	WARD	       30
7566	JONES	       20
7654 	MARTIN	       30
7698	BLAKE	       30
7782	CLARK	       10
7788	SCOTT	       20
7839	KING	       10
7844	TURNER	       30
7876	ADAMS	       20
7900	JAMES	       30
7902	FORD	       20
7934	MILLER	       10

3. Mostra el nom de la vista (columna view_name) i el text de la vista anterior emmagatzemada en el diccionari de dades (USER_VIEWS). 

     VIEW_NAME   TEXT
     ----------- ---------------------------------------
     EMP_VU      SELECT empno, ename employee, deptno
                 FROM emp

4. Fes una consulta que mostri el nom dels empleats i els números de departament, mitjançant la vista EMP_VU. 

EMPLOYEE   DEPTNO 
---------  ------
SMITH	       20
ALLEN	       30
WARD	       30
JONES	       20
MARTIN	       30
BLAKE	       30
CLARK	       10
SCOTT	       20
KING	       10
TURNER	       30
ADAMS	       20
JAMES	       30
FORD	       20
MILLER	       10

5. Crea una vista anomenada DEPT20 que contingui el número d'empleat, nom d'empleat i número de departament de tots els empleats del departament 20. Etiqueta les columnes de la vista com EMPLOYEE_ID, EMPLOYEE i DEPARTMENT_ID. No permetis que un empleat pugui ser assignat a un altra departament mitjançant la vista.

6. Mostra l'estructura i el contingut de la vista DEPT20. 

                           
Nombre                         Null?     Tipo 
------------------------------ --------  ------------
EMPLOYEE_ID                    NOT NULL  NUMBER(4)      
EMPLOYEE                                 VARCHAR2(10)    
DEPARTMENT_ID                  NOT NULL  NUMBER(2)      


EMPPLOYEE_ID  EMPLOYEE   DEPARTMENT_ID
------------  ---------  -------------
        7566  JONES	            20
        7902  FORD	            20
        7369  SMITH	            20
        7788  SCOTT	            20
        7876  ADAMS	            20

7. Intenta assignar a Smith al departament 30.

8. Crea una vista anomenada SALARY_VU basada e el nom de l'empleat, nom del departament, salari i grau de salari de tots els empleats. Etiqueta les columnes com Employee, Departament, Salary i Grade, respectivament.


T13: Altres objectes de la base de dades

1. Crea una seqüència per a ser utilitzada en la clau primària de la taula DEPARTMENT. La seqüència començara en el número 60 i el seu màxim serà 200. Tindrà un increment de 10 en 10 i el nom DEPT_ID_SEQ.

2. Fes un script que mostri la següent informació sobre les teves seqüències: nom, valor màxim, increment i darrer registre. Anomena el script p13q2.sql. Executa el script. 

SEQUENCE_NAME  MAX_VAUE    INCREMENT_BY  LAST_NUMBER
-------------  ----------  ------------  -----------
CUSTID         1.000E+27              1          109
DEPT_ID_SEQ          200              1           60
ORDID          1.000E+27              1          622
PRODID         1.000E+27              1       200381

3. Escriu un script interactiu per afegir un registre en la taula DEPARTMENT. Anomena'l p13q3.sql. Utilitza la seqüència DEPT_ID_SEQ per a la columna ID. Crea un missatge personalitzat per introduir el nom del departament. Executa el script. Afegeix els departament "Educació", i "Administració". Comprova les insercions.

4. Crea un índex no únic sobre FOREIGN KEY de la taula EMPLOYEE.

5. Mostra els índexs i la seva unicitat de la taula EMPLOYEE, utilitzant el diccionari de dades. Salva la sentència en un script anomenat pl13q5.sql. 

INDEX_NAME            TABLE_NAME  UNIQUENESS
--------------------  ----------  ------------ 
EMPLOYEE_DEPT_ID_IDX  EMPLOYEE    NONUNIQUE
EMPLOYEE_ID_PK        EMPLOYEE    UNIQUE

6. Crea una restricció PRIMARY KEY sobre la taula DEPARTMENT. Comprova la creació de la restricció en el diccionari de dades executant el script p11q3.sql. Modifica i comprova la unicitat del índex en el diccionari de dades, executant el script p13q5.sql. 

CONSTRAINT_NAME         C
--------------------   --
DEPARTMENT_ID_PK      P
EMPLOYEE_ID_PK        P
EMPLOYEE_DEPT_ID_FK   R

INDEX_NAME            TABLE_NAME  UNIQUENES
--------------------  ----------  ------------ 
DEPARTMENT_ID_PK      DEPARTMENT  UNIQUE
EMPLOYEE_DEPT_ID_IDX  EMPLOYEE    NONUNIQUE
EMPLOYEE_ID_PK        EMPLOYEE    UNIQUE


Repàs Consultes BBDD HR

1. Muestra el nombre, apellido i salario mensual de los empleados que cobren mas que Jennifer ordenado por salario descendiente 

 +--------------------+-------------------+
 | Nombre i apellido  | Salario Mensual   | 
 +--------------------+-------------------+


2. Para cada departamento Muestra el nombre del empleado/s que cobran menos. 

 +------------------------+-------------------+
 | Nombre departamentodo  | Nombre empleado   | 
 +------------------------+-------------------+


3. Para cada región muestra cuantos países hay. Ordena por numero de países ascendentemente.

4. Muestra el nombre de los departamentos i nombre de su jefe, que se encuentran fuera de EEUU. 

 +--------------+-------+-----------------+
 | Nombre Dep   | Jefe  |  Sueldo Mensual | 
 +------------- +-------+-----------------+


5. Mostrar el nombre de los países que empiezan o termina en una “a" junto con el nombre de la region a la que pertencen. 

 +-------------+--------+
 | Nombre pais | Region | 
 +-------------+--------+

6. Muestra el nombre de cada empleado en mayúsculas junto con el de su respectivo jefe. 

 +-----------------+-------------+
 | Nombre empleado | Nombre jefe | 
 +-----------------+-------------+

7. Para cada jefe muestra cuantos empleados tiene a su cargo. 

 +-----------------+------------------+
 | Nombre jefe     | Numero empleados | 
 +-----------------+------------------+

8. Cantidad de empleados que han ingresado en la empresa en cada año. 

 +-------+------------------+
 |  Año  | Numero empleados | 
 +-------+------------------+

9. Para cada empleado y cada puesto ocupado los meses que ha trabajado en dicho puesto. 

 +--------------+---------+------------------+
 |  Nombre emp  |  Puesto | Meses trabajados |
 +--------------+---------+------------------+

10. El nombre de los jefes de cada departamento y su telefono. 

 +------------------------+------------------+---------------+
 |  Nombre departamento   |  Nombre jefe     | Telefono jefe |
 +------------------------+------------------+---------------+

11. El costo de cada departamento en el ultimo año (suma de salario *12 de todos los empleados mas comisiones) 

 +------------------------+-----------------+
 |  Nombre departamento  |  Coste año YYYY  |   | 
 +-----------------------+------------------+


12. Para cada departamento el promedio de sueldo, el salario maximo i el mínimo pero sólo mostrar los departamentos con promedio superior a la media de todos los departamentos. 

 +------------------------+-----------------+-----------------+ 
 |  Nombre departamento   |  Sueldo medio   | Salario maximo  | 
 +------------------------+-----------------+-----------------+

Solucions exercicis

Obtingut de «http://wikiserver.infomerce.es/index.php?title=M2_-_Bases_de_dades_/_Exercicis_UF2:_Llenguatges_SQL&oldid=17540»