Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF2: Llenguatges SQL»
m |
(→T3: Funcions a nivell de fila) |
||
| Línia 351: | Línia 351: | ||
---------------------- ------------------------- ------------------------------------------ | ---------------------- ------------------------- ------------------------------------------ | ||
7369 17/12/80 Lunes, the Twenty-Second of Junio, 1981 | 7369 17/12/80 Lunes, the Twenty-Second of Junio, 1981 | ||
| + | 7499 20/02/81 Lunes, the Twenty-Fourth of Agosto, 1981 | ||
| + | 7521 22/02/81 Lunes, the Twenty-Fourth of Agosto, 1981 | ||
| + | 7566 02/04/81 Lunes, the Fifth of Octubre, 1981 | ||
| + | 7654 28/09/81 Lunes, the Twenty-Ninth of Marzo, 1982 | ||
| + | 7698 01/05/81 Lunes, the Second of Noviembre, 1981 | ||
| + | 7782 09/06/81 Lunes, the Fourteenth of Diciembre, 1981 | ||
| + | 7788 19/04/87 Lunes, the Twenty-Sixth of Octubre, 1987 | ||
| + | 7839 17/11/81 Lunes, the Twenty-Fourth of Mayo, 1982 | ||
| + | 7844 08/09/81 Lunes, the Fifteenth of Marzo, 1982 | ||
| + | 7876 23/05/87 Lunes, the Thirtieth of Noviembre, 1987 | ||
| + | 7900 03/12/81 Lunes, the Seventh of Junio, 1982 | ||
| + | 7902 03/12/81 Lunes, the Seventh of Junio, 1982 | ||
| + | 7934 23/01/82 Lunes, the Twenty-Sixth of Julio, 1982 | ||
</pre> | </pre> | ||
Revisió del 18:52, 10 des 2013
Contingut
- 1 Presentació Taules Usuari SCOTT
- 2 T1: Escriptura bàsica de sentències
- 3 T2: Restricció i classificació de les dades
- 4 T3: Funcions a nivell de fila
- 5 T4: Visualització de dades a partir de varies taules
- 6 T5: Dades agregades. Funcions de grup
- 7 T6: Subconsultes
- 8 T7: Subconsultes multicolumna
- 9 T8: Generació d'informes
- 10 T9: Manipulació de dades
- 11 T10: Creació i gestió de taules
- 12 T11: Restriccions
- 13 T12: Creació de vistes
- 14 T13: Altres objectes de la base de dades
- 15 T14: Exercici Resum
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
----- --------- ---------- ---------------
7839 KING PRESIDENT 17-NOV-81
7698 BLAKE MANAGER 01-MAY-81
...
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
-------------------
KING, PRESIDENT
BLAKE, MANAGER
...
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
---------------------------------------------
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
...
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.
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.
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.
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.
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.
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.
