M2 - Bases de dades / Exercicis UF2: Llenguatges SQL

De wikiserver
Dreceres ràpides: navegació, cerca

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