• Author / Uploaded
• sanietom

• Categories
• SQL
• Tabla (base de datos)
• Oracle Corporation
• Bases de datos
• Datos

Citation preview

Introducción a Oracle 10g: SQL Fundamentals II

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Prácticas Adicionales y Soluciones

as

ic

t ác

Pr es

s

ne

al on ci

di

A y

o ci

lu

So

1

Introducción a Oracle 10g: SQL Fundamentals II

Autor

Copyright © 2004, Oracle. Todos los derechos reservados.

Priya Vennapusa

Esta documentación contiene información propiedad de Oracle Corporation; se suministra bajo los términos de un contrato de licencia que contiene restricciones de uso y de revelación y está también protegida por la legislación de derechos de autor. Queda prohibida la ingeniería reversa del software. Si esta documentación se entrega a una agencia del Ministerio de Defensa del Gobierno de EE.UU., se aplicará la siguiente advertencia de “Restricted Rights”:

Colaboradores Técnicos y Revisores

Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). Este material ni ninguna parte del mismo podrá ser reproducido en cualquier forma o a través de cualquier medio sin el expreso consentimiento por escrito de Oracle Corporation. La reproducción es una violación de la ley de derechos de autor y puede tener consecuencias penales o civiles.

Editor

Si esta documentación se entrega a una agencia del Gobierno de EE.UU. no perteneciente al Ministerio de Defensa, se aplicará la advertencia de “Restricted Rights” definida en FAR 52.227-14, Rights in Data-General, incluido Alternate III (junio de 1987). La información contenida en este documento está sujeta a cambio sin previo aviso. Si detecta cualquier problema en la documentación, le agradeceremos lo comunique por escrito a Education Products, Oracle Corporation, 500 Oracle Parkway, Redwood Shores, CA 94065. Oracle Corporation no garantiza que este documento esté exento de errores.

t ác

Pr

Nancy Greenberg Priya Nathan Andrew Brannigan Angelika Krupp Brian Boxx Christopher Lawless Joel Goodman Malika Marghadi Marjolein Dekkers Stefan Grenstad Zarko Cesljas Rosita Hanoman Ruediger Steffan

Joseph Fernandez

ic

Toda referencia a Oracle y a los Productos de Oracle constituyen marcas comerciales o marcas comerciales registradas de Oracle Corporation.

as

Todos los demás nombres de compañías y productos mencionados se utilizan a los exclusivos fines de su identificación y pueden ser marcas comerciales de sus respectivos propietarios.

es

s

ne

al on ci

di

A y

o ci

lu

So

2

Introducción a Oracle 10g: SQL Fundamentals II

Contenido Prefacio Introducción Objetivos I-2 Objetivos del Curso I-3 Visión General del Curso I-4 Aplicación del Curso 1-5 Resumen I-6

1

Control de Acceso de Usuarios Objetivos 1-2 Control de Acceso de Usuarios 1-3 Privilegios 1-4 Privilegios del Sistema 1-5 Creación de Usuarios 1-6 Privilegios del Sistema de Usuario 1-7 Otorgamiento de Privilegios del Sistema 1-8 ¿Qué es un Rol? 1-9 Creación y Otorgamiento de Privilegios a un Rol 1-10 Cambio de Contraseñas 1-11 Privilegios de Objeto 1-12 Otorgamiento de Privilegios de Objeto 1-14 Transferencia de Privilegios 1-15 Confirmación de Privilegios Otorgados 1-16 Revocación de Privilegios de Objeto 1-17 Resumen 1-19 Práctica 1: Visión General 1-20

2

Gestión de Objetos de Esquema Objetivos 2-2 Sentencia ALTER TABLE 2-3 Adición de una Columna 2-5 Modificación de una Columna 2-6 Borrado de una Columna 2-7 Opción SET UNUSED 2-8 Adición de una Sintaxis de Restricción 2-10 Adición de una Restricción 2-11 ON DELETE CASCADE 2-12 Diferir Restricciones 2-13 Borrado de una Restricción 2-14 Desactivación de Restricciones 2-15

as

ic

t ác

Pr

I

es

s

ne

al on ci

di

A

y

o ci

lu

So

iii

3

Introducción a Oracle 10g: SQL Fundamentals II

Pr

Activación de Restricciones 2-16 Restricciones en Cascada 2-18 Visión General de Índices 2-20 CREATE INDEX con Sentencia CREATE TABLE 2-21 Índices Basados en Funciones 2-23 Eliminación de un Índice 2-24 DROP TABLE ...PURGE 2-25 Sentencia FLASHBACK TABLE 2-26 Tablas Externas 2-28 Creacisn de un Directorio para la Tabla Externa 2-30 Creación de una Tabla Externa 2-32 Creación de una Tabla Externa mediante ORACLE_LOADER 2-34 Consulta de Tablas Externas 2-36 Resumen 2-37 Práctica 2: Visión General 2-38

as

ic

t ác

3 Manipulación de Grandes Juegos de Datos Objetivos 3-2 Uso de Subconsultas para Manipular Datos 3-3 Copia de Filas de Otra Tabla 3-4 Inserción mediante una Subconsulta como Destino 3-5 Recuperación de Datos con una Subconsulta como Origen 3-7 Actualización de Dos Columnas con una Subconsulta 3-8 Actualización de Filas Basándose en Otra Tabla 3-9 Supresión de Filas Basándose en Otra Tabla 3-10 Uso de las Palabras Clave WITH CHECK OPTION en Sentencias DML 3-11 Visión General de la Función de Valor por Defecto Explícito 3-12 Uso de Valores Por Defecto Explícitos 3-13 Visión General de Sentencias INSERT de Varias Tablas 3-14 Tipos de Sentencias INSERT de Varias Tablas 3-16 Sentencias INSERT de Varias Tablas 3-17 INSERT ALL Incondicional 3-19 INSERT ALL Condicional 3-20 INSERT FIRST Condicional 3-22 INSERT de Pivoting 3-24 Sentencia MERGE 3-27 Sintaxis de la Sentencia MERGE 3-28 Fusión de Filas 3-29 Seguimiento de Cambios en los Datos 3-31 Ejemplo de Consulta de Versiones de Flashback 3-32 Cláusula VERSIONS BETWEEN 3-34 Resumen 3-35 Práctica 3: Visión General 3-36

es

s

ne

al on ci

di

A

y

o ci

lu

So

iv

4

Introducción a Oracle 10g: SQL Fundamentals II

Generación de Informes mediante la Agrupación de Datos Relacionados Objetivos 4-2 Revisión de Funciones de Grupo 4-3 Revisión de la Cláusula GROUP BY 4-4 Revisión de la Cláusula HAVING 4-5 GROUP BY con los Operadores ROLLUP y CUBE 4-6 Operador ROLLUP 4-7 Operador ROLLUP: Ejemplo 4-8 Operador CUBE 4-9 Operador CUBE: Ejemplo 4-10 Función GROUPING 4-11 Función GROUPING: Ejemplo 4-12 GROUPING SETS 4-13 GROUPING SETS: Ejemplo 4-15 Columnas Compuestas 4-17 Columnas Compuestas: Ejemplo 4-19 Agrupamientos Concatenados 4-21 Agrupamientos Concatenados: Ejemplo 4-22 Resumen 4-23 Práctica 4: Visión General 4-24

5

Gestión de Datos en Zonas Horarias Diferentes Objetivos 5-2 Zonas Horarias 5-3 Parámetro de Sesión TIME_ZONE 5-4 CURRENT_DATE, CURRENT_TIMESTAMP y LOCALTIMESTAMP 5-5 CURRENT_DATE 5-6 CURRENT_TIMESTAMP 5-7 LOCALTIMESTAMP 5-8 DBTIMEZONE y SESSIONTIMEZONE 5-9 Tipo de Datos TIMESTAMP 5-10 Tipos de Datos TIMESTAMP 5-11 Campos TIMESTAMP 5-12 Diferencia entre DATE y TIMESTAMP 5-13 Tipo de Datos TIMESTAMP WITH TIME ZONE 5-14 TIMESTAMP WITH TIMEZONE: Ejemplo 5-15 TIMESTAMP WITH LOCAL TIMEZONE 5-16 TIMESTAMP WITH LOCAL TIMEZONE: Ejemplo 5-17 Tipos de Datos INTERVAL 5-18 Campos INTERVAL 5-20 Tipo de Datos INTERVAL YEAR TO MONTH 5-21 INTERVAL YEAR TO MONTH: Ejemplo 5-22 Tipo de Datos INTERVAL DAY TO SECOND 5-23

as

ic

t ác

Pr

4

es

s

ne

al on ci

di

A

y

o ci

lu

So

v

5

Introducción a Oracle 10g: SQL Fundamentals II

Tipo de Datos INTERVAL DAY TO SECOND: Ejemplo 5-24 EXTRACT 5-25 TZ_OFFSET 5-26 Conversión de TIMESTAMP mediante FROM_TZ 5-28 Conversión a TIMESTAMP mediante TO_TIMESTAMP y TO_TIMESTAMP_TZ 5-29 Conversión de Intervalo de Tiempo con TO_YMINTERVAL 5-30 Uso de TO_DSINTERVAL: Ejemplo 5-31 Horario de Verano 5-32 Resumen 5-34 Práctica 5: Visión General 5-35 Recuperación de Datos mediante Subconsultas Objetivos 6-2 Subconsultas de Varias Columnas 6-3 Comparaciones de Columnas 6-4 Subconsulta de Comparación entre Pares 6-5 Subconsulta de Comparación entre No Pares 6-6 Expresiones de Subconsultas Escalares 6-7 Subconsultas Escalares: Ejemplos 6-8 Subconsultas Correlacionadas 6-10 Uso de Subconsultas Correlacionadas 6-12 Uso del Operador EXISTS 6-14 Búsqueda de Empleados que Tengan al Menos una Persona a sus Órdenes 6-15 Búsqueda de Todos los Departamentos que No Tengan Empleados 6-16 Consulta Correlacionada: UPDATE 6-17 Uso de Consultas Correlacionadas: UPDATE 6-18 Consulta Correlacionada: DELETE 6-20 Uso de Consultas Correlacionadas: DELETE 6-21 Cláusula WITH 6-22 Cláusula WITH: Ejemplo 6-23 Resumen 6-25 Práctica 6: Visión General 6-27

7

Recuperación Jerárquica Objetivos 7-2 Ejemplo de Datos de la Tabla EMPLOYEES 7-3 Estructura de Árbol Natural 7-4 Consultas Jerárquicas 7-5 Desplazamiento por el Árbol 7-6 Desplazamiento por el Árbol: De Abajo Arriba 7-8 Desplazamiento por el Árbol: De Arriba Abajo 7-9 Clasificación de Filas con la Pseudocolumna LEVEL 7-10 Formato de Informes Jerárquicos mediante LEVEL y LPAD 7-11 Eliminación de Ramas 7-13

as

ic

t ác

Pr

6

es

s

ne

al on ci

di

A

y

o ci

lu

So

vi

6

Introducción a Oracle 10g: SQL Fundamentals II

Resumen 7-14 Práctica 7: Visión General 7-15 8

t ác

Pr

Soporte de Expresiones Normales Objetivos 8-2 Visión General de Expresiones Normales 8-3 Metacaracteres 8-4 Uso de Metacaracteres 8-5 Funciones de Expresiones Normales 8-7 Sintaxis de la Función REGEXP 8-8 Realización de Búsquedas Básicas 8-9 Comprobación de la Presencia de un Patrón 8-10 Ejemplo de Extracción de Subcadenas 8-11 Sustitución de Patrones 8-12 Expresiones Normales y Restricciones de Control 8-13 Resumen 8-14 Práctica 8: Visión General 8-15

Apéndice A: Soluciones a la Práctica Apéndice B: Descripciones de Tabla

ic

as

Apéndice C: Escritura de Archivos de Comandos Avanzados Objetivos C-2 Uso de SQL para Generar SQL C-3 Creación de un Archivo de Comandos Básico C-4 Control del Entorno C-5 La Imagen Completa C-6 Volcado del Contenido de una Tabla en un Archivo C-7 Generación de un Predicado Dinámico C-9 Resumen C-11

s

ne

o ci

es

al on ci

di

A

y

lu

So

Apéndice D: Componentes de la Arquitectura Oracle Objetivos D-2 Arquitectura de la Base de Datos Oracle: Visión General D-3 Arquitectura Física de la Base de Datos D-4 Archivos de Control D-5 Archivos Redo Log D-6 Tablespaces y Archivos de Datos D-7 Segmentos, Extensiones y Bloques D-8 Gestión de Instancias Oracle D-9 Estructuras de Memoria Oracle D-10 Procesos Oracle D-12 Otras Estructuras Físicas Clave D-13 Procesamiento de una Sentencia SQL D-14

vii

7

Introducción a Oracle 10g: SQL Fundamentals II

Conexión a una Instancia D-15 Procesamiento de una Consulta D-17 Pool Compartido D-18 Caché de Buffers de Base de Datos D-20 PGA (Área Global de Programas) D-21 Procesamiento de una Sentencia DML D-22 Buffer de Redo Log D-24 Segmento de Rollback D-25 Procesamiento COMMIT D-26 Resumen D-28 Índice Prácticas Adicionales

as

ic

t ác

Pr

Soluciones a las Prácticas Adicionales

es

s

ne

al on ci

di

A y

o ci

lu

So

viii

8

Introducción a Oracle 10g: SQL Fundamentals II

_______________

Prácticas Adicionales _______________

as

ic

t ác

Pr es

s

ne

al on ci

di

A y

o ci

lu

So

9

Introducción a Oracle 10g: SQL Fundamentals II Prácticas Adicionales

as

SPECIAL_SAL

ic

t ác

Pr

Los siguientes ejercicios se pueden utilizar como práctica adicional después de haber estudiado las sentencias DML y DDL en la Lección 2 y la Lección 3. Nota: Ejecute los archivos de comandos lab_ap_cre_special_sal.sql, lab_ap_cre_sal_history.sql y lab_ap_cre_mgr_history.sql de la carpeta de prácticas para crear las tablas SPECIAL_SAL, SAL_HISTORY y MGR_HISTORY. 1. El departamento de recursos humanos desea obtener una lista de empleados mal pagados, el historial salarial de los empleados y el historial salarial de los supervisores basándose en una encuesta salarial de la industria. Le han pedido que haga lo siguiente: Escriba una sentencia para: - Recuperar los detalles de identificador de empleado, fecha de contratación, salario e identificador de supervisor de los empleados cuyo identificador es mayor o igual que 200 en la tabla EMPLOYEES. - Si el salario es menor que 5.000 dólares, insertar los detalles de identificador de empleado y salario en la tabla SPECIAL_SAL. - Insertar los detalles de identificador de empleado, fecha de contratación y salario en la tabla SAL_HISTORY. - Insertar los detalles de identificador de empleado, identificador de supervisor y salario en la tabla MGR_HISTORY. 2. Consulte las tablas SPECIAL_SAL, SAL_HISTORY y MGR_HISTORY para ver los registros insertados.

y

es

s

ne

o ci

lu

al on ci

di

A

So

SALARY_HISTORY

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Prácticas Adicionales-2

10

Introducción a Oracle 10g: SQL Fundamentals II Prácticas Adicionales MGR_HISTORY

Pr

3. La DBA necesita que cree una tabla, que tiene una restricción de clave primaria, pero quiere que al índice se le asigne un nombre diferente al de la restricción. Cree la tabla LOCATIONS_NAMED_INDEX basada en el siguiente diagrama de instancia de tabla. Asigne al índice de la columna PRIMARY KEY el nombre LOCATIONS_PK_IDX. Deptno

Dname

Primary Key

Yes

Data Type

Number

VARCHAR2

Length

4

30

ic

t ác

Column Name

as

4. Consulte la tabla USER_INDEXES para mostrar el INDEX_NAME de la tabla LOCATIONS_NAMED_INDEX.

es

s

ne

al on ci

di

A y

o ci

lu

So

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Prácticas Adicionales-3

11

Introducción a Oracle 10g: SQL Fundamentals II Prácticas Adicionales Los siguientes ejercicios se pueden utilizar como práctica adicional después de haber estudiado las mejoras de la cláusula GROUP BY.

as

ic

t ác

Pr

5. El departamento de recursos humanos necesita informes de ciertos departamentos. Éstos son los requisitos: Escriba una consulta para mostrar lo siguiente de los departamentos cuyo identificador sea mayor que 80: - Salario total para todos los puestos dentro de un departamento - Salario total - Salario total para las ciudades en que estén ubicados los departamentos - Salario total para todos los puestos, independientemente del departamento - Salario total para todos los departamentos, independientemente de la ciudad - Salario total de las ciudades en que estén ubicados los departamentos - Salario total de los departamentos, independientemente de los cargos y las ciudades

es

s

ne

al on ci

di

A y

o ci

lu

So

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Prácticas Adicionales-4

12

Introducción a Oracle 10g: SQL Fundamentals II Prácticas Adicionales 6. El departamento de contabilidad necesita un análisis sobre los salarios máximos y mínimos por departamento, puesto y supervisor. Le han pedido que haga lo siguiente: Escriba una consulta para mostrar los siguientes agrupamientos: - Identificador de departamento, identificador de puesto - Identificador de puesto, identificador de supervisor La consulta debe calcular los salarios máximos y mínimos para cada uno de estos grupos.

as

ic

t ác

Pr …

es

s

ne

al on ci

di

A y

o ci

lu

So

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Prácticas Adicionales-5

13

Introducción a Oracle 10g: SQL Fundamentals II Prácticas Adicionales Los siguientes ejercicios se pueden utilizar como práctica adicional después de haber estudiado las funciones de fecha/hora. Trabaja en una compañía global y el nuevo vicepresidente de operaciones quiere conocer las diferentes zonas horarias de todas las sucursales de la compañía. Ha solicitado la siguiente información: 7. Modifique la sesión para definir NLS_DATE_FORMAT en DD-MON-YYYY HH24:MI:SS. 8. a. Escriba consultas para mostrar los offsets de zona horaria (TZ_OFFSET), para las siguientes zonas horarias. Australia/Sydney

t ác

Pr

Chile/Easter Island

ic

b. Modifique la sesión para definir el valor del parámetro TIME_ZONE en el offset de zona horaria Australia/Sydney.

as

c. Muestre los valores de SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP y LOCALTIMESTAMP para esta sesión. Nota: La salida podría ser diferente, según la fecha en que se ejecute el comando.

di

A

So

es

al on ci

s

ne

o ci

lu

y

d. Modifique la sesión para definir el valor del parámetro TIME_ZONE en el offset de zona horaria Chile/Easter Island. Nota: Los resultados de la pregunta anterior se basan en una fecha diferente y en algunos casos no se corresponderán con los resultados reales obtenidos por los alumnos. Además, el offset de zona horaria de los diferentes países puede variar, según el horario de verano. e. Muestre los valores de SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP y LOCALTIMESTAMP para esta sesión. Nota: La salida puede ser diferente, según la fecha en que se ejecute el comando.

f. Modifique la sesión para definir NLS_DATE_FORMAT en DD-MON-YYYY.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Prácticas Adicionales-6

14

Introducción a Oracle 10g: SQL Fundamentals II Prácticas Adicionales Nota • Observe en la pregunta que CURRENT_DATE, CURRENT_TIMESTAMP y LOCALTIMESTAMP son todos sensibles a la zona horaria de la sesión. Observe que SYSDATE no es sensible a la zona horaria de la sesión. • Los resultados de la pregunta anterior se basan en una fecha diferente y en algunos casos no se corresponderán con los resultados reales obtenidos por los alumnos. Además, el offset de zona horaria de los diferentes países puede variar, según el horario de verano. 9. El departamento de recursos humanos quiere una lista de empleados a la espera de revisión en enero; así que le han solicitado lo siguiente: Escriba una consulta para mostrar los apellidos, el mes de la fecha de contratación y la fecha de contratación de los empleados que han sido contratados en el mes de enero, independientemente del año de contratación.

as

ic

t ác

Pr es

s

ne

al on ci

di

A y

o ci

lu

So

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Prácticas Adicionales-7

15

Introducción a Oracle 10g: SQL Fundamentals II Prácticas Adicionales Los siguientes ejercicios se pueden utilizar como práctica adicional después de haber estudiado las subconsultas avanzadas. 10. El director general necesita un informe sobre las tres personas que más ganan en la compañía para el reparto de beneficios. Le ha pedido que le proporcione una lista. Escriba una consulta para mostrar las tres personas que más ganan de la tabla EMPLOYEES. Muestre los apellidos y los salarios.

as

ic

t ác

Pr

11. Los beneficios para el estado de California han cambiado por culpa de nuevas leyes locales. Por ese motivo, el representante de beneficios le ha pedido que compile una lista de las personas afectadas. Escriba una consulta para mostrar el identificador de empleado y los apellidos de los empleados que trabajan en el estado de California. Indicación: Utilice subconsultas escalares.

es

s

ne

al on ci

di

A y

o ci

lu

So

…

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Prácticas Adicionales-8

16

Introducción a Oracle 10g: SQL Fundamentals II Prácticas Adicionales

as

ic

t ác

Pr

12. La DBA quiere eliminar información antigua de la base de datos. Una de las cosas que considera innecesarias son los registros de empleados antiguos. Le ha pedido que haga lo siguiente: Escriba una consulta para suprimir la fila JOB_HISTORY más antigua de un empleado buscando MIN(START_DATE) en la tabla JOB_HISTORY del empleado. Suprima los registros únicamente de los empleados que han trabajado al menos en dos puestos diferentes. Indicación: Utilice un comando DELETE correlacionado. 13. El vicepresidente de recursos humanos necesita los registros de empleo completos para su discurso anual de reconocimiento a los empleados. Recibe una rápida llamada telefónica suya en la que le dice que no siga las órdenes de la DBA. Haga rollback de la transacción. 14. La inactividad en la economía está obligando a los supervisores a tomar medidas de reducción de costos. El director general desea revisar los puestos mejor pagados de la compañía. Le ha solicitado una lista que se base en las siguientes especificaciones: Escriba una consulta para mostrar los identificadores de puesto de los puestos cuyo salario máximo esté por encima de la mitad del salario máximo de toda la compañía. Utilice la cláusula WITH para escribir esta consulta. Asigne a la consulta el nombre MAX_SAL_CALC.

al on ci

di

A

y

o ci

lu

So

Los siguientes ejercicios se pueden utilizar como práctica adicional después de haber estudiado la recuperación jerárquica. 15. Lex De Haan va a dejar la compañía. Su sustituto quiere informes de sus subordinados directos. Escriba una sentencia SQL para mostrar el número de empleado, el apellido, la fecha de inicio y el salario, que muestre: a. Subordinados directos de De Haan

es

s

ne

b. Árbol de la organización por debajo de De Haan (número de empleado 102)

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Prácticas Adicionales-9

17

Introducción a Oracle 10g: SQL Fundamentals II Prácticas Adicionales 16. Escriba una consulta jerárquica para mostrar el número de empleado, el número de supervisor y el apellido de empleado de todos los empleados que estén dos niveles por debajo del empleado De Haan (empleado número 102). Muestre también el nivel del empleado.

as

ic

t ác

Pr

17. El director general necesita un informe jerárquico sobre todos los empleados. Le ha proporcionado los siguientes requisitos: Genere un informe jerárquico para mostrar el número de empleado, el número de supervisor, la pseudocolumna LEVEL y el apellido del empleado. Para todas las filas de la tabla EMPLOYEES, debe imprimir una estructura de árbol que muestre el empleado, el supervisor del empleado, el supervisor de ese supervisor y así sucesivamente. Utilice sangrados para la columna NAME.

al on ci

di

A y

o ci

lu

So

…

Nota: La salida que se muestra no es más que un ejemplo. No se incluyen todas las filas de la salida real.

es

s

ne

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Prácticas Adicionales-10

18

Introducción a Oracle 10g: SQL Fundamentals II

_______________

Soluciones a las Prácticas Adicionales _______________

as

ic

t ác

Pr es

s

ne

al on ci

di

A y

o ci

lu

So

19

Introducción a Oracle 10g: SQL Fundamentals II

t ác

Pr

Los siguientes ejercicios se pueden utilizar como práctica adicional después de haber estudiado las sentencias DML y DDL en la Lección 2 y la Lección 3. Nota: Ejecute los archivos de comandos lab_ap_cre_special_sal.sql, lab_ap_cre_sal_history.sql y lab_ap_cre_mgr_history.sql de la carpeta de prácticas para crear las tablas SPECIAL_SAL, SAL_HISTORY y MGR_HISTORY. 1. El departamento de recursos humanos desea obtener una lista de empleados mal pagados, el historial salarial de los empleados y el historial salarial de los supervisores basándose en una encuesta salarial de la industria. Le han pedido que haga lo siguiente: Escriba una sentencia para: - Recuperar los detalles de identificador de empleado, fecha de contratación, salario e identificador de supervisor de los empleados cuyo identificador es mayor o igual que 200 en la tabla EMPLOYEES. - Si el salario es menor que 5.000 dólares, insertar los detalles de identificador de empleado y salario en la tabla SPECIAL_SAL. - Insertar los detalles de identificador de empleado, fecha de contratación y salario en la tabla SAL_HISTORY. - Insertar los detalles de identificador de empleado, identificador de supervisor y salario en la tabla MGR_HISTORY.

as

ic

INSERT ALL WHEN SAL < 5000 THEN INTO special_sal VALUES (EMPID, SAL) ELSE INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id >=200;

al on ci

di

A

y

o ci

lu

So

2. Consulte las tablas SPECIAL_SAL, SAL_HISTORY y MGR_HISTORY para ver los registros insertados.

es

s

ne

SELECT * FROM special_sal; SELECT * FROM sal_history; SELECT * FROM mgr_history;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Soluciones a las Prácticas Adicionales-2 20

Introducción a Oracle 10g: SQL Fundamentals II

3. La DBA necesita que cree una tabla, que tiene una restricción de clave primaria, pero quiere que al índice se le asigne un nombre diferente al de la restricción. Cree la tabla LOCATIONS_NAMED_INDEX basada en el siguiente diagrama de instancia de tabla. Asigne al índice de la columna PRIMARY KEY el nombre LOCATIONS_PK_IDX. Column Name

Deptno

Dname

Primary Key

Yes

Data Type

Number

VARCHAR2

Length

4

30

t ác

Pr

CREATE TABLE LOCATIONS_NAMED_INDEX (location_id NUMBER(4) PRIMARY KEY USING INDEX (CREATE INDEX locations_pk_idx ON LOCATIONS_NAMED_INDEX(location_id)), location_name VARCHAR2(20));

4. Consulte la tabla USER_INDEXES para mostrar el INDEX_NAME de la tabla LOCATIONS_NAMED_INDEX.

ic

as

SELECT INDEX_NAME, TABLE_NAME FROM USER_INDEXES WHERE TABLE_NAME = ‘LOCATIONS_NAMED_INDEX’;

A

di

y

So

Los siguientes ejercicios se pueden utilizar como práctica adicional después de haber estudiado las mejoras de la cláusula GROUP BY.

es

al on ci

s

ne

o ci

lu

5. El departamento de recursos humanos necesita informes de ciertos departamentos. Éstos son los requisitos: Escriba una consulta para mostrar lo siguiente de los departamentos cuyo identificador sea mayor que 80: - Salario total para todos los puestos dentro de un departamento - Salario total - Salario total para las ciudades en que estén ubicados los departamentos - Salario total para todos los puestos, independientemente del departamento - Salario total para todos los departamentos, independientemente de la ciudad - Salario total de las ciudades en que estén ubicados los departamentos - Salario total de los departamentos, independientemente de los cargos y las ciudades

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Soluciones a las Prácticas Adicionales-3 21

Introducción a Oracle 10g: SQL Fundamentals II

COLUMN COLUMN COLUMN COLUMN

city FORMAT A25 Heading CITY department_name FORMAT A15 Heading DNAME job_id FORMAT A10 Heading JOB SUM(salary) FORMAT $99,99,999.00 Heading SUM(SALARY)

SELECT

l.city, d.department_name, e.job_id, SUM(e.salary) FROM locations l, employees e, departments d WHERE d.location_id = l.location_id AND e.department_id = d.department_id AND e.department_id > 80 GROUP BY CUBE( l.city, d.department_name, e.job_id);

Pr

as

ic

t ác

6. El departamento de contabilidad necesita un análisis sobre los salarios máximos y mínimos por departamento, puesto y supervisor. Le han pedido que haga lo siguiente: Escriba una consulta para mostrar los siguientes agrupamientos: - Identificador de departamento, identificador de puesto - Identificador de puesto, identificador de supervisor La consulta debe calcular los salarios máximos y mínimos para cada uno de estos grupos.

al on ci

di

A

y

lu

So

SELECT department_id,job_id,manager_id,max(salary), min(salary) FROM employees GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));

es

s

ne

o ci

Los siguientes ejercicios se pueden utilizar como práctica adicional después de haber estudiado las funciones de fecha/hora. Trabaja en una compañía global y el nuevo vicepresidente de operaciones quiere conocer las diferentes zonas horarias de todas las sucursales de la compañía. Ha solicitado la siguiente información: 7. Modifique la sesión para definir NLS_DATE_FORMAT en DD-MON-YYYY HH24:MI:SS. ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS’;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Soluciones a las Prácticas Adicionales-4 22

Introducción a Oracle 10g: SQL Fundamentals II

8. a.

Escriba consultas para mostrar los offsets de zona horaria (TZ_OFFSET) para las siguientes zonas horarias. - Australia/Sydney SELECT TZ_OFFSET (‘Australia/Sydney’) from dual;

-

Chile/Easter Island SELECT TZ_OFFSET (‘Chile/EasterIsland’) from dual;

b.

Modifique la sesión para definir el valor del parámetro TIME_ZONE en el offset de zona horaria Australia/Sydney.

c.

Pr

ALTER SESSION SET TIME_ZONE = ‘+10:00’;

t ác

Muestre los valores de SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP y LOCALTIMESTAMP para esta sesión. Nota: La salida puede ser diferente, según la fecha en que se ejecute el comando.

Modifique la sesión para definir el valor del parámetro TIME_ZONE en el offset de zona horaria Chile/Easter Island. Nota: Los resultados de la pregunta anterior se basan en una fecha diferente y en algunos casos no se corresponderán con los resultados reales obtenidos por los alumnos. Además, el offset de zona horaria de los diferentes países puede variar, según el horario de verano.

as

d.

ic

SELECT SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

di

A

So

al on ci

lu

e.

y

ALTER SESSION SET TIME_ZONE = ‘-06:00’;

o ci

Muestre los valores de SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP y LOCALTIMESTAMP para esta sesión. Nota: La salida puede ser diferente, según la fecha en que se ejecute el comando.

es

Modifique la sesión para definir NLS_DATE_FORMAT en DD-MON-YYYY.

s

f.

ne

SELECT SYSDATE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY’;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Soluciones a las Prácticas Adicionales-5 23

Introducción a Oracle 10g: SQL Fundamentals II

Nota • Observe en la pregunta que CURRENT_DATE, CURRENT_TIMESTAMP y LOCALTIMESTAMP son todos sensibles a la zona horaria de la sesión. Observe que SYSDATE no es sensible a la zona horaria de la sesión. • Los resultados de la pregunta anterior se basan en una fecha diferente y en algunos casos no se corresponderán con los resultados reales obtenidos por los alumnos. Además, el offset de zona horaria de los diferentes países puede variar, según el horario de verano. 9. El departamento de recursos humanos quiere una lista de empleados a la espera de revisión en enero; así que le han solicitado lo siguiente: Escriba una consulta para mostrar los apellidos, el mes de la fecha de contratación y la fecha de contratación de los empleados que han sido contratados en el mes de enero, independientemente del año de contratación.

t ác

Pr

SELECT last_name, EXTRACT (MONTH FROM HIRE_DATE), HIRE_DATE FROM employees WHERE EXTRACT (MONTH FROM HIRE_DATE) = 1;

ic

Los siguientes ejercicios se pueden utilizar como práctica adicional después de haber estudiado las subconsultas avanzadas.

as

10. El director general necesita un informe sobre las tres personas que más ganan en la compañía para el reparto de beneficios. Le ha pedido que le proporcione una lista. Escriba una consulta para mostrar las tres personas que más ganan de la tabla EMPLOYEES. Muestre los apellidos y los salarios.

A

So

al on ci

o ci

lu

y

di

SELECT last_name, salary FROM employees e WHERE 3 > (SELECT COUNT (*) FROM employees WHERE e.salary < salary);

es

s

ne

11. Los beneficios para el estado de California han cambiado por culpa de nuevas leyes locales. Por ese motivo, el representante de beneficios le ha pedido que compile una lista de las personas afectadas. Escriba una consulta para mostrar el identificador de empleado y los apellidos de los empleados que trabajan en el estado de California. Indicación: Utilice subconsultas escalares.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Soluciones a las Prácticas Adicionales-6 24

Introducción a Oracle 10g: SQL Fundamentals II

SELECT employee_id, last_name FROM employees e WHERE ((SELECT location_id FROM departments d WHERE e.department_id = d.department_id ) IN (SELECT location_id FROM locations l WHERE state_province = ‘California’));

t ác

Pr

12. La DBA quiere eliminar información antigua de la base de datos. Una de las cosas que considera innecesarias son los registros de empleados antiguos. Le ha pedido que haga lo siguiente: Escriba una consulta para suprimir la fila JOB_HISTORY más antigua de un empleado buscando MIN(START_DATE) en la tabla JOB_HISTORY del empleado. Suprima los registros únicamente de los empleados que han trabajado al menos en dos puestos diferentes. Indicación: Utilice un comando DELETE correlacionado.

as

ic

DELETE FROM job_history JH WHERE employee_id = (SELECT employee_id FROM employees E WHERE JH.employee_id = E.employee_id AND START_DATE = (SELECT MIN(start_date) FROM job_history JH WHERE JH.employee_id = E.employee_id) AND 3 > (SELECT COUNT(*) FROM job_history JH WHERE JH.employee_id = E.employee_id GROUP BY EMPLOYEE_ID HAVING COUNT(*) >= 2));

al on ci

di

A

y

o ci

lu

So

es

ROLLBACK;

s

ne

13. El vicepresidente de recursos humanos necesita los registros de empleo completos para su discurso anual de reconocimiento a los empleados. Recibe una rápida llamada telefónica suya en la que le dice que no siga las órdenes de la DBA. Haga rollback de la transacción.

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Soluciones a las Prácticas Adicionales-7 25

Introducción a Oracle 10g: SQL Fundamentals II

14. La inactividad en la economía está obligando a los supervisores a tomar medidas de reducción de costos. El director general desea revisar los puestos mejor pagados de la compañía. Le ha solicitado una lista que se base en las siguientes especificaciones: Escriba una consulta para mostrar los identificadores de puesto de los puestos cuyo salario máximo esté por encima de la mitad del salario máximo de toda la compañía. Utilice la cláusula WITH para escribir esta consulta. Asigne a la consulta el nombre MAX_SAL_CALC.

t ác

Pr

WITH MAX_SAL_CALC AS (SELECT job_title, MAX(salary) AS job_total FROM employees, jobs WHERE employees.job_id = jobs.job_id GROUP BY job_title) SELECT job_title, job_total FROM MAX_SAL_CALC WHERE job_total > (SELECT MAX(job_total) * 1/2 FROM MAX_SAL_CALC) ORDER BY job_total DESC;

as

ic

Los siguientes ejercicios se pueden utilizar como práctica adicional después de haber estudiado la recuperación jerárquica. 15. Lex De Haan va a dejar la compañía. Su sustituto quiere informes de sus subordinados directos. Escriba una sentencia SQL para mostrar el número de empleado, el apellido, la fecha de inicio y el salario, que muestre: a. Subordinados directos de De Haan

A

al on ci

di

y

b.

o ci

lu

So

SELECT employee_id, last_name, hire_date, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE last_name = ‘De Haan’);

Árbol de la organización por debajo de De Haan (número de empleado 102)

es

s

ne

SELECT employee_id, last_name, hire_date, salary FROM employees WHERE employee_id != 102 CONNECT BY manager_id = PRIOR employee_id START WITH employee_id = 102;

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Soluciones a las Prácticas Adicionales-8 26

Introducción a Oracle 10g: SQL Fundamentals II

16. Escriba una consulta jerárquica para mostrar el número de empleado, el número de supervisor y el apellido de empleado de todos los empleados que estén dos niveles por debajo del empleado De Haan (empleado número 102). Muestre también el nivel del empleado. SELECT employee_id, manager_id, level, last_name FROM employees WHERE LEVEL = 3 CONNECT BY manager_id = PRIOR employee_id START WITH employee_id = 102;

t ác

Pr

17. El director general necesita un informe jerárquico sobre todos los empleados. Le ha proporcionado los siguientes requisitos: Genere un informe jerárquico para mostrar el número de empleado, el número de supervisor, la pseudocolumna LEVEL y el apellido del empleado. Para todas las filas de la tabla EMPLOYEES, debe imprimir una estructura de árbol que muestre el empleado, el supervisor del empleado, el supervisor de ese supervisor y así sucesivamente. Utilice sangrados para la columna NAME.

as

ic

COLUMN name FORMAT A25 SELECT employee_id, manager_id, LEVEL, LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') LAST_NAME FROM employees CONNECT BY employee_id = PRIOR manager_id; COLUMN name CLEAR

es

s

ne

al on ci

di

A y

o ci

lu

So

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Soluciones a las Prácticas Adicionales-9 27

Introducción a Oracle 10g: SQL Fundamentals II

as

ic

t ác

Pr es

s

ne

al on ci

di

A y

o ci

lu

So

Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II Soluciones a las Prácticas Adicionales-10 28