M2 - Bases de dades / Exercicis UF2: Llenguatges SQL
Contingut
- 1 Connectar-se a la base de dades
- 2 Presentació Taules Usuari SCOTT
- 3 T1: Escriptura bàsica de sentències
- 4 T2: Restricció i classificació de les dades
- 5 T3: Funcions a nivell de fila
- 6 T4: Visualització de dades a partir de varies taules
- 7 T5: Dades agregades. Funcions de grup
- 8 T6: Subconsultes
- 9 T7: Subconsultes multicolumna
- 10 T7-1: Exists i Operadors SET
- 11 T7-2: Exercicis consultes complexes
- 12 T8: Generació d'informes
- 13 T9: Manipulació de dades
- 14 T10: Creació i gestió de taules
- 15 T11: Restriccions
- 16 T12: Creació de vistes
- 17 T13: Altres objectes de la base de dades
- 18 Repàs Consultes BBDD HR
- 19 Solucions exercicis
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
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
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 | +------------------------+-----------------+-----------------+
