Apéndice A Prácticas y Soluciones
Tabla de Contenido Prácticas para la Lección I ........................................................................................................ 3 Práctica I-1: Introducción ............................................................................................... 4 Soluciones a la Práctica I-1: Introducción ...................................................................... 5 Prácticas de la Lección 1......................................................................................................... 11 Práctica 1-1: Recuperación de Datos mediante la Sentencia SQL SELECT ................ 12 Soluciones a la Práctica 1-1: Recuperación de Datos mediante la Sentencia SQL SELECT ........................................................................................................................ 16 Prácticas de la Lección 2......................................................................................................... 19 Práctica 2-1: Restricción y Ordenación de Datos ......................................................... 20 Soluciones a la Práctica 2-1: Restricción y Ordenación de Datos ................................ 24 Prácticas de la Lección 3......................................................................................................... 27 Práctica 3-1: Uso de Funciones de Una Sola Fila para Personalizar la Salida ............. 28 Soluciones a la Práctica 3-1: Uso de Funciones de Una Sola Fila para Personalizar la Salida ................................................. .................................................................................................... ................................................... 32 Prácticas de la Lección 4......................................................................................................... 35 Práctica 4-1: Uso de Funciones de Conversión Convers ión y Expresiones Condicionales ............. 36 Soluciones a la Práctica 4-1: Uso de Funciones de Conversión y Expresiones Condicionales ................................................... ........................................................................................................ ............................................................. ........ 39 Prácticas de la Lección 5......................................................................................................... 41 Práctica 5-1: Informes de Datos Agregados con Funciones de Grupo ......................... 42 Soluciones a la Práctica 5-1: Informes de Datos Agregados con Funciones de Grupo ............................................................................................................................ 45 Prácticas de la Lección 6......................................................................................................... 48 Práctica 6-1: Visualización de Datos de Varias Tablas Ta blas Utilizando Uniones ................ 49 Soluciones a la Práctica 6-1: Visualización de Datos de Varias Tablas Utilizando Uniones ......................................................................................................................... 52 Prácticas de la Lección 7......................................................................................................... 54 Práctica 7-1: Uso de Subconsultas para Solucionar Soluciona r Consultas ..................................... 55 Soluciones a la Práctica 7-1: Uso de Subconsultas Subco nsultas para Solucionar Consultas Cons ultas ............ 57 Prácticas de la Lección 8......................................................................................................... 59 Práctica 8-1: Uso de los Operadores de Definición ................................................ ...................................................... ...... 60 Soluciones a la Práctica 8-1: Uso de los Operadores de Definición Def inición ............................. 62 Prácticas de la Lección 9......................................................................................................... 64 Práctica 9-1: Manipulación de Datos ................................................... ............................................................................ ......................... 65 Soluciones a la Práctica 9-1: 9 -1: Manipulación de Datos ................................................... 69 Prácticas de la Lección 10....................................................................................................... 73 Práctica 10-1: Uso de Sentencias DDL para Crear Cr ear y Gestionar Tablas ........................ 74 Soluciones a la Práctica 10-1: Uso de Sentencias DDL para Crear y Gestionar Tablas ...................................................... ............................................................................................................ ...................................................................... ................ 76 Prácticas de la Lección 11....................................................................................................... 79 Práctica 11-1: Creación de Otros Objetos de Esquema ................................................ 80 Soluciones a la Práctica 11-1: Creación de Otros Otro s Objetos de Esquema ....................... 82 Prácticas del Apéndice Apénd ice F ...................................................... ......................................................................................................... ................................................... 84 Práctica F-1: Sintaxis de Unión en Oracle .................................................... .................................................................... ................ 85 Soluciones a las Prácticas F-1: F-1 : Sintaxis de Unión en Oracle ........................................ 88
Oracle Database: Conceptos Fundamentales de SQL I A-2
Prácticas para la Lección I En esta práctica, realizará las siguientes tareas: Iniciar Oracle SQL Developer y crear una nueva conexión a la cuenta ora1. Utilizar Oracle SQL Developer para examinar objetos de datos en la cuenta ora1. La cuenta ora1 contiene las tablas de esquema HR.
Observe la siguiente ubicación de los archivos de los ejercicios prácticos: \home\oracle\labs\sql1\labs
Si se le pide que guarde algún archivo de los ejercicios prácticos, guárdelo en esta ubicación. En las prácticas, puede que haya ejercicios que estén precedidos por las frases “Si tiene tiempo” o “Si desea superarse a sí mismo”. Realice estos ejercicios sólo si ha terminado
el resto de ejercicios en el tiempo asignado y si desea poner a prueba sus habilidades. Realice las prácticas despacio y de forma precisa. Puede experimentar guardando y ejecutando los archivos de comandos. Si tiene alguna duda en cualquier momento, pregunte a su instructor. Nota
1) Todas las prácticas escritas utilizan Oracle SQL Developer como entorno de desarrollo. Aunque se recomienda utilizar Oracle SQL Developer, también puede usar SQL*Plus, disponible en este curso. 2) Para cualquier consulta, la secuencia de filas recuperadas de la base de datos puede variar con respecto a la que se muestra en las capturas de pantalla.
Oracle Database: Conceptos Fundamentales de SQL I A-3
Práctic a I-1: Introducci ón Ésta es la primera de varias prácticas de este curso. Las soluciones (si las necesita) están al final de esta práctica. Las prácticas están destinadas a abarcar la mayoría de los temas que se presentan en la lección correspondiente. Inicio de Oracle SQL Developer
1) Inicie Oracle SQL Developer mediante el icono del escritorio de SQL Developer. Creación de Nuevas Conexiones a la Base de Datos de Oracle SQL Developer
2) Para crear una nueva conexión a la base de datos, en el navegador de conexiones, haga clic con el botón derecho del mouse en Connections. Seleccione New Connection en el menú. Aparece el cuadro de diálogo New/Select Database Connection. 3) Cree una conexión a la base de datos con la siguiente información: a) Connection Name: myconnection b) Username: ora1 c) Password: ora1 d) Hostname: localhost e) Port: 1521 f) SID: ORCL Asegúrese de activar la casilla de control Save Password. Prueba y Conexión mediante la Conexión a la Base de Datos de Oracle SQL Developer
4) Pruebe la nueva conexión. 5) Si el estado es Success, conéctese a la base de datos mediante esta nueva conexión. Examen de Tablas en el Navegador de Conexiones
6) En el navegador de conexiones, visualice los objetos disponibles en el nodo Tables. Verifique que las siguientes tablas están presentes: COUNTRIES DEPARTMENTS EMPLOYEES JOB_GRADES JOB_HISTORY JOBS LOCATIONS REGIONS
7) Examine la estructura de la tabla EMPLOYEES. 8) Visualice los datos de la tabla DEPARTMENTS.
Oracle Database: Conceptos Fundamentales de SQL I A-4
S oluciones a la Práctic a I-1: I ntroducción Inicio de Oracle SQL Developer
1) Inicie Oracle SQL Developer mediante el icono del escritorio de SQL Developer. a) Haga doble clic en el icono del escritorio de SQL Developer.
Aparece la interfaz de SQL Developer.
Creación de Nuevas Conexiones a la Base de Datos de Oracle SQL Developer
2) Para crear una nueva conexión a la base de datos, en el navegador de conexiones, haga clic con el botón derecho del mouse en Connections y seleccione New Connection en el menú.
Oracle Database: Conceptos Fundamentales de SQL I A-5
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) Aparece el cuadro de diálogo New/Select Database Connection.
3) Cree una conexión a la base de datos con la siguiente información: a) Connection Name: myconnection b) Username: ora1 c) Password: ora1 d) Hostname: localhost e) Port: 1521 f) SID: ORCL Asegúrese de activar la casilla de control Save Password.
Oracle Database: Conceptos Fundamentales de SQL I A-6
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
Prueba y Conexión mediante la Conexión a la Base de Datos de Oracle SQL Developer
4) Pruebe la nueva conexión.
5) Si el estado es Success, conéctese a la base de datos mediante esta nueva conexión.
Oracle Database: Conceptos Fundamentales de SQL I A-7
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
Al crear una conexión, automáticamente se abre una hoja de trabajo de SQL para dicha conexión.
Examen de Tablas en el Navegador de Conexiones
6) En el navegador de conexiones, visualice los objetos disponibles en el nodo Tables. Verifique que las siguientes tablas están presentes: COUNTRIES DEPARTMENTS EMPLOYEES JOB_GRADES JOB_HISTORY JOBS
Oracle Database: Conceptos Fundamentales de SQL I A-8
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) LOCATIONS REGIONS
7) Examine la estructura de la tabla EMPLOYEES.
8) Visualice los datos de la tabla DEPARTMENTS.
Oracle Database: Conceptos Fundamentales de SQL I A-9
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
Oracle Database: Conceptos Fundamentales de SQL I A-10
Prácticas de la Lección 1 En esta práctica, escribirá consultas simples SELECT. Las consultas tratan la mayoría de las operaciones y cláusulas SELECT que ha aprendido en esta lección.
Oracle Database: Conceptos Fundamentales de SQL I A-11
Práctica 1-1: R ecuperación de Datos mediante la S entencia S QL SELECT
Parte 1
Ponga a prueba sus conocimientos: 1) La siguiente sentencia SELECT se ejecuta correctamente: SELECT last_name, job_id, salary AS Sal FROM employees;
Verdadero/Falso 2) La siguiente sentencia SELECT se ejecuta correctamente: SELECT * FROM job_grades;
Verdadero/Falso 3) En la siguiente sentencia hay cuatro errores de codificación. ¿Puede identificarlos? SELECT sal x 12 FROM
employee_id, last_name ANNUAL SALARY employees;
Parte 2
Tenga en cuenta los siguientes puntos antes de iniciar las prácticas:
Guarde todos los archivos de los ejercicios prácticos en la siguiente ubicación: /home/oracle/labs/sql1/labs
Introduzca las sentencias SQL en una hoja de trabajo de SQL. Para guardar un script en SQL Developer, asegúrese de que la hoja de trabajo de SQL necesaria está activa y, a continuación, en el menú File, seleccione Save As para guardar la sentencia SQL como un script lab_
_.sql. Cuando esté modificando un script existente, asegúrese de que utiliza Save As para guardarlo con un nombre de archivo diferente. Para ejecutar una consulta, haga clic en el icono Execute Statement en la hoja de trabajo de SQL. Como alternativa, puede pulsar [F9]. Para sentencias DML y DDL, utilice el icono Run Script o pulse [F5]. Una vez ejecutada la consulta, asegúrese de que no introduce la siguiente consulta en la misma hoja de trabajo. Abra una hoja de trabajo nueva. Se le ha contratado como programador de SQL para Acme Corporation. Su primera tarea es crear algunos informes según los datos de las tablas de recursos humanos.
Oracle Database: Conceptos Fundamentales de SQL I A-12
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 4) Su primera tarea es determinar la estructura de la tabla DEPARTMENTS y su contenido.
5) Determine la estructura de la tabla EMPLOYEES.
El departamento de recursos humanos desea que una consulta muestre el apellido, ID de cargo, fecha de contratación e ID de empleado de cada empleado, con el ID de empleado en primer lugar. Proporcione un alias STARTDATE para la columna HIRE_DATE. Guarde la sentencia SQL en un archivo con el nombre lab_01_05.sql para distribuirlo al departamento de recursos humanos. 6) Pruebe la consulta en el archivo lab_01_05.sql para asegurarse de que se ejecuta correctamente. Nota: una vez ejecutada la consulta, asegúrese de que no introduce la siguiente
consulta en la misma hoja de trabajo. Abra una hoja de trabajo nueva.
Oracle Database: Conceptos Fundamentales de SQL I A-13
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
…
7) El departamento de recursos humanos desea una consulta para mostrar todos los ID de cargo únicos de la tabla EMPLOYEES.
Parte 3
Si tiene tiempo, realice los siguientes ejercicios: 8) El departamento de recursos humanos desea cabeceras de columna más descriptivas para su informe sobre empleados. Copie la sentencia de lab_01_05.sql en una nueva hoja de trabajo de SQL. Asigne a las cabeceras de columna los nombres Emp #, Employee, Job y Hire Date, respectivamente. A continuación, vuelva a ejecutar la consulta.
…
Oracle Database: Conceptos Fundamentales de SQL I A-14
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
9) El departamento de recursos humanos ha solicitado un informe de todos los empleados y sus ID de cargo. Muestre el apellido concatenado con el ID de cargo (separado por una coma y un espacio) y asigne a la columna el nombre Employee and Title.
…
Si desea superarse a sí mismo, complete el siguiente ejercicio: 10) Para familiarizarse con los datos de la tabla EMPLOYEES, cree una consulta para mostrar todos los datos de dicha tabla. Separe cada salida de la columna por una coma. Asigne a la columna el título THE_OUTPUT.
…
Oracle Database: Conceptos Fundamentales de SQL I A-15
S oluciones a la Práctic a 1-1: R ecuperación de Datos mediante la S entencia S QL SELECT Parte 1
Ponga a prueba sus conocimientos: 1) La siguiente sentencia SELECT se ejecuta correctamente: SELECT last_name, job_id, salary AS Sal FROM employees;
Verdadero/Falso
2) La siguiente sentencia SELECT se ejecuta correctamente: SELECT * FROM job_grades;
Verdadero/Falso
3) En la siguiente sentencia hay cuatro errores de codificación. ¿Puede identificarlos? SELECT sal x 12 FROM
employee_id, last_name ANNUAL SALARY employees;
La tabla EMPLOYEES no contiene ninguna columna denominada sal. La columna se denomina SALARY. El operador de multiplicación es *, no x, como se muestra en la línea 2. El alias ANNUAL SALARY no puede incluir espacios. El alias debe ser ANNUAL_SALARY o se deben incluir entre comillas dobles. Falta una coma tras la columna LAST_NAME.
Parte 2
Se le ha contratado como programador de SQL para Acme Corporation. Su primera tarea es crear algunos informes según los datos de las tablas de recursos humanos. 4) Su primera tarea es determinar la estructura de la tabla DEPARTMENTS y su contenido. a. Para determinar la estructura de la tabla DEPARTMENTS : DESCRIBE departments
Oracle Database: Conceptos Fundamentales de SQL I A-16
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) b. Para ver los datos que contiene la tabla DEPARTMENTS : SELECT * FROM departments;
5) Determine la estructura de la tabla EMPLOYEES. DESCRIBE employees
El departamento de recursos humanos desea que una consulta muestre el apellido, ID de cargo, fecha de contratación e ID de empleado de cada empleado, con el ID de empleado en primer lugar. Proporcione un alias STARTDATE para la columna HIRE_DATE . Guarde la sentencia SQL en un archivo con el nombre lab_01_05.sql para distribuirlo al departamento de recursos humanos. SELECT employee_id, last_name, job_id, hire_date StartDate FROM employees;
6) Pruebe la consulta en el archivo lab_01_05.sql para asegurarse de que se ejecuta correctamente. SELECT employee_id, last_name, job_id, hire_date StartDate FROM employees;
7) El departamento de recursos humanos desea una consulta para mostrar todos los ID de cargo únicos de la tabla EMPLOYEES. SELECT DISTINCT job_id FROM employees;
Parte 3
Si tiene tiempo, realice los siguientes ejercicios: 8) El departamento de recursos humanos desea cabeceras de columna más descriptivas para su informe sobre empleados. Copie la sentencia de lab_01_05.sql en una nueva hoja de trabajo de SQL. Asigne a las cabeceras de columna los nombres Emp #, Employee, Job y Hire Date, respectivamente. A continuación, vuelva a ejecutar la consulta. SELECT employee_id "Emp #", last_name "Employee", job_id "Job", hire_date "Hire Date" FROM employees;
9) El departamento de recursos humanos ha solicitado un informe de todos los empleados y sus ID de cargo. Muestre el apellido concatenado con el ID de cargo (separado por una coma y un espacio) y asigne a la columna el nombre Employee and Title. SELECT last_name||', '||job_id "Employee and Title" FROM employees;
Oracle Database: Conceptos Fundamentales de SQL I A-17
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) Si desea superarse a sí mismo, complete el siguiente ejercicio:
10) Para familiarizarse con los datos de la tabla EMPLOYEES, cree una consulta para mostrar todos los datos de dicha tabla. Separe cada salida de la columna por una coma. Asigne a la columna el título THE_OUTPUT. SELECT employee_id || ',' || first_name || ',' || last_name || ',' || email || ',' || phone_number || ','|| job_id || ',' || manager_id || ',' || hire_date || ',' || salary || ',' || commission_pct || ',' || department_id THE_OUTPUT FROM employees;
Oracle Database: Conceptos Fundamentales de SQL I A-18
Prácticas de la Lección 2 En esta práctica se crean más informes, incluyendo sentencias que utilizan las cláusulas WHERE y ORDER BY. Puede hacer que las sentencias SQL sean más reutilizables y genéricas incluyendo la sustitución con ampersand.
Oracle Database: Conceptos Fundamentales de SQL I A-19
Práctic a 2-1: R estricción y Ordenación de Datos El departamento de recursos humanos necesita su ayuda para crear algunas consultas. 1) Debido a problemas presupuestarios, el departamento de recursos humanos necesita un informe que muestre el apellido y el salario de los empleados que ganan más de 12.000 dólares. Guarde la sentencia SQL como un archivo denominado lab_02_01.sql. Ejecute la consulta.
2) Abra una hoja de trabajo de SQL nueva. Cree un informe que muestre el apellido y el número de departamento para el número de empleado 176. Ejecute la consulta.
3) El departamento de recursos humanos necesita encontrar los empleados con salarios tanto altos como bajos. Modifique lab_02_01.sql para mostrar el apellido y el salario de cualquier empleado cuyo salario no esté entre 5.000 y 12.000 dólares. Guarde la sentencia SQL como lab_02_03.sql.
4) Cree un informe para mostrar el apellido, ID de cargo y fecha de contratación de los empleados cuyos apellidos sean Matos y Taylor. Ordene la consulta en orden ascendente por fecha de contratación.
Oracle Database: Conceptos Fundamentales de SQL I A-20
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 5) Muestre el apellido y el ID de departamento de todos los empleados de los departamentos 20 o 50 en orden alfabético ascendente por nombre.
6) Modifique el archivo lab_02_03.sql para mostrar el apellido y el salario de los empleados que ganan entre 5.000 y 12.000 dólares y están en el departamento 20 o 50. Etiquete las columnas Employee y Monthly Salary, respectivamente. Vuelva a guardar lab_02_03.sql como lab_02_06.sql. Ejecute la sentencia en el archivo lab_02_06.sql.
7) El departamento de recursos humanos necesita un informe que muestre el apellido y la fecha de contratación de todos los empleados contratados durante el año 1994.
8) Cree un informe para mostrar el apellido y el puesto de todos los empleados que no tienen un supervisor.
9) Cree un informe para mostrar el apellido, salario y comisión de todos los empleados que perciben comisiones. Ordene los datos en orden descendente de salario y comisiones. Utilice la posición numérica de la columna en la cláusula ORDER BY.
Oracle Database: Conceptos Fundamentales de SQL I A-21
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
10) Los miembros del departamento de recursos humanos desean tener más flexibilidad con las consultas que está creando. Les gustaría tener un informe que muestre el apellido y el salario de los empleados que ganen más de una cantidad especificada por el usuario después de una solicitud. Guarde esta consulta en un archivo denominado lab_02_10.sql. Si introduce 12000 cuando se le solicite, el informe mostrará los siguientes resultados:
11) El departamento de recursos humanos desea ejecutar informes basados en un supervisor. Cree una consulta que solicite al usuario un ID de supervisor y genere el ID de empleado, apellido, salario y departamento de los empleados de ese supervisor. El departamento de recursos humanos desea ordenar el informe en una columna seleccionada. Puede probar los datos con los siguientes valores: manager_id = 103, ordenado por last_name:
manager_id = 201, ordenado por salary:
manager_id = 124, ordenado por employee_id:
Si tiene tiempo, realice los siguientes ejercicios: 12) Muestre todos los apellidos de los empleados cuya tercera letra sea una "a".
Oracle Database: Conceptos Fundamentales de SQL I A-22
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
13) Muestre los apellidos de los empleados que tengan una "a" y una "e" en su apellido.
Si desea superarse a sí mismo, complete los siguientes ejercicios: 14) Muestre el apellido, cargo y salario de todos los empleados que sean vendedores u oficinistas en el departamento de stock y cuyos salarios no sean iguales que 2.500, 3.500 ó 7.000 dólares.
15) Modifique el archivo lab_02_06.sql para mostrar el apellido, salario y comisión de todos los empleados cuya comisión sea del 20%. Vuelva a guardar lab_02_06.sql como lab_02_15.sql. Vuelva a ejecutar la sentencia en el archivo lab_02_15.sql.
Oracle Database: Conceptos Fundamentales de SQL I A-23
S oluciones a la Práctic a 2-1: R es tricción y Ordenación de Datos El departamento de recursos humanos necesita su ayuda para crear algunas consultas. 1) Debido a problemas presupuestarios, el departamento de recursos humanos necesita un informe que muestre el apellido y el salario de los empleados que ganan más de 12.000 dólares. Guarde la sentencia SQL como un archivo denominado lab_02_01.sql . Ejecute la consulta. SELECT FROM WHERE
last_name, salary employees salary > 12000;
2) Abra una hoja de trabajo de SQL nueva. Cree un informe que muestre el apellido y el número de departamento para el número de empleado 176. SELECT FROM WHERE
last_name, department_id employees employee_id = 176;
3) El departamento de recursos humanos necesita encontrar los empleados con salarios tanto altos como bajos. Modifique lab_02_01.sql para mostrar el apellido y el salario de todos los empleados cuyo salario no esté entre 5.000 y 12.000 dólares. Guarde la sentencia SQL como lab_02_03.sql. SELECT FROM WHERE
last_name, salary employees salary NOT BETWEEN 5000 AND 12000;
4) Cree un informe para mostrar el apellido, ID de cargo y fecha de contratación de los empleados cuyos apellidos sean Matos y Taylor. Ordene la consulta en orden ascendente por fecha de contratación. SELECT last_name, job_id, hire_date FROM employees WHERE last_name IN ('Matos', 'Taylor') ORDER BY hire_date;
5) Muestre el apellido y el ID de departamento de todos los empleados de los departamentos 20 o 50 en orden alfabético ascendente por nombre. SELECT last_name, department_id FROM employees WHERE department_id IN (20, 50) ORDER BY last_name ASC;
6) Modifique el archivo lab_02_03.sql para mostrar el apellido y el salario de los empleados que ganan entre 5.000 y 12.000 dólares y están en el departamento 20 o 50. Etiquete las columnas Employee y Monthly Salary, respectivamente. Vuelva a guardar lab_02_03.sql como lab_02_06.sql. Ejecute la sentencia en el archivo lab_02_06.sql. SELECT FROM WHERE AND
last_name "Employee", salary "Monthly Salary" employees salary BETWEEN 5000 AND 12000 department_id IN (20, 50);
Oracle Database: Conceptos Fundamentales de SQL I A-24
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 7) El departamento de recursos humanos necesita un informe que muestre el apellido y la fecha de contratación de todos los empleados contratados durante el año 1994. SELECT FROM WHERE
last_name, hire_date employees hire_date LIKE '%94';
8) Cree un informe para mostrar el apellido y el puesto de todos los empleados que no tienen un supervisor. SELECT FROM WHERE
last_name, job_id employees manager_id IS NULL;
9) Cree un informe para mostrar el apellido, salario y comisión de todos los empleados que perciben comisiones. Ordene los datos en orden descendente de salario y comisiones. Utilice la posición numérica de la columna en la cláusula ORDER BY. SELECT FROM WHERE ORDER BY
last_name, salary, commission_pct employees commission_pct IS NOT NULL 2 DESC, 3 DESC;
10) Los miembros del departamento de recursos humanos desean tener más flexibilidad con las consultas que está creando. Les gustaría tener un informe que muestre el apellido y el salario de los empleados que ganen más de una cantidad especificada por el usuario después de una solicitud. (Puede utilizar la consulta creada en el ejercicio práctico 1 y modificarla). Guarde esta consulta en un archivo denominado lab_02_10.sql. SELECT FROM WHERE
last_name, salary employees salary > &sal_amt;
Introduzca 12000 cuando se le solicite un valor en un cuadro de diálogo. Haga clic en OK.
11) El departamento de recursos humanos desea ejecutar informes basados en un supervisor. Cree una consulta que solicite al usuario un ID de supervisor y genere el ID de empleado, apellido, salario y departamento de los empleados de ese supervisor. El departamento de recursos humanos desea ordenar el informe en una columna seleccionada. Puede probar los datos con los siguientes valores: Oracle Database: Conceptos Fundamentales de SQL I A-25
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) manager _id = 103, ordenado por last_name manager_id = 201, ordenado por salary manager_id = 124, ordenado por employee_id SELECT employee_id, last_name, salary, department_id FROM employees WHERE manager_id = &mgr_num ORDER BY &order_col;
Si tiene tiempo, realice los siguientes ejercicios: 12) Muestre todos los apellidos de los empleados cuya tercera letra sea una "a". SELECT FROM WHERE
last_name employees last_name LIKE '__a%';
13) Muestre los apellidos de los empleados que tengan una "a" y una "e" en su apellido. SELECT FROM WHERE AND
last_name employees last_name LIKE '%a%' last_name LIKE '%e%';
Si desea superarse a sí mismo, complete los siguientes ejercicios: 14) Muestre el apellido, cargo y salario de todos los empleados que sean vendedores u oficinistas en el departamento de stock y cuyo salario no sea igual que 2.500, 3.500 o 7.000 dólares. SELECT FROM WHERE AND
last_name, job_id, salary employees job_id IN ('SA_REP', 'ST_CLERK') salary NOT IN (2500, 3500, 7000);
15) Modifique el archivo lab_02_06.sql para mostrar el apellido, salario y comisión de todos los empleados cuya comisión sea del 20%. Vuelva a guardar lab_02_06.sql como lab_02_15.sql . Vuelva a ejecutar la sentencia en el archivo lab_02_15.sql . SELECT FROM WHERE
last_name "Employee", salary "Monthly Salary", commission_pct employees commission_pct = .20;
Oracle Database: Conceptos Fundamentales de SQL I A-26
Prácticas de la Lección 3 Esta práctica proporciona una variedad de ejercicios que utilizan diferentes funciones que están disponibles para tipos de dato de carácter, de número y de fecha.
Oracle Database: Conceptos Fundamentales de SQL I A-27
Práctica 3-1: Us o de Funciones de Una S ola Fi la para Pers onalizar la Salida 1) Escriba una consulta para mostrar la fecha del sistema. Etiquete la columna como Date. Nota: si la base de datos se ubica de forma remota en una zona horaria diferente, la
salida será la fecha del sistema operativo en el que reside la base de datos.
2) El departamento de recursos humanos necesita un informe que muestre el número de empleado, apellido, salario y salario aumentado en un 15,5% (expresado como número entero) para cada empleado. Etiquete la columna como New Salary. Guarde la sentencia SQL en un archivo denominado lab_03_02.sql. 3) Ejecute la consulta en el archivo lab_03_02.sql.
…
4) Modifique la consulta lab_03_02.sql para agregar una columna que reste el salario antiguo del nuevo. Etiquete la columna como Increase. Guarde el contenido del archivo como lab_03_04.sql. Ejecute la consulta revisada.
…
Oracle Database: Conceptos Fundamentales de SQL I A-28
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 5) Escriba una consulta que muestre el apellido (con la primera letra en mayúsculas y el resto en minúsculas) y la longitud del apellido de todos los empleados cuyos nombres empiecen por las letras "J", "A" o "M". Proporcione a cada columna una etiqueta adecuada. Ordene los resultados por el apellido de los empleados.
Vuelva a escribir la consulta para que se le solicite al usuario que introduzca la letra por la que empieza el apellido. Por ejemplo, si el usuario introduce "H" (en mayúscula) cuando se le solicita que introduzca una letra, la salida debe mostrar a todos los empleados cuyos apellidos empiecen por la letra "H".
Modifique la consulta de forma que la mayúscula/minúscula de la letra introducida no afecte a la salida. La letra introducida debe estar en mayúscula antes de que la procese la consulta SELECT.
6) El departamento de recursos humanos desea buscar la duración del contrato de cada empleado. Para cada empleado, muestre el apellido y calcule el número de meses entre el día de hoy y la fecha de contratación del empleado. Etiquete la columna como MONTHS_WORKED. Ordene los resultados por el número de meses durante los que ha trabajado. Redondee el número de meses hasta el número entero más cercano. Nota: debido a que esta consulta depende de la fecha de ejecución, los valores de la columna MONTHS_WORKED serán diferentes.
Oracle Database: Conceptos Fundamentales de SQL I A-29
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
…
Si tiene tiempo, realice los siguientes ejercicios: 7) Cree una consulta para mostrar el apellido y el salario de todos los empleados. Formatee el salario para que tenga 15 caracteres de longitud y tenga un relleno hacia la izquierda con el símbolo $. Etiquete la columna como SALARY.
…
8) Cree una consulta que muestre los primeros ocho caracteres de los apellidos de los empleados y que indique las cantidades de sus salarios con asteriscos. Cada asterisco significa mil dólares. Ordene los datos en orden descendente de salarios. Etiquete la columna como EMPLOYEES_AND_THEIR_SALARIES.
…
Oracle Database: Conceptos Fundamentales de SQL I A-30
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 9) Cree una consulta para mostrar el apellido y el número de semanas durante las que han trabajado todos los empleados del departamento 90. Etiquete la columna de número de semanas como TENURE. Trunque el valor del número de semana en 0 decimales. Muestre los registros en orden descendente de antigüedad del empleado. Nota: el valor TENURE variará ya que depende de la fecha de ejecución de la
consulta.
Oracle Database: Conceptos Fundamentales de SQL I A-31
S oluciones a la Práctic a 3-1: Us o de Funciones de Una S ola Fila para Pers onalizar la S alida 1) Escriba una consulta para mostrar la fecha del sistema. Etiquete la columna como Date. Nota: si la base de datos se ubica de forma remota en una zona horaria diferente, la
salida será la fecha del sistema operativo en el que reside la base de datos. SELECT FROM
sysdate "Date" dual;
2) El departamento de recursos humanos necesita un informe que muestre el número de empleado, apellido, salario y salario aumentado en un 15,5% (expresado como número entero) para cada empleado. Etiquete la columna como New Salary. Guarde la sentencia SQL en un archivo denominado lab_03_02.sql . SELECT FROM
employee_id, last_name, salary, ROUND(salary * 1.155, 0) "New Salary" employees;
3) Ejecute la consulta en el archivo lab_03_02.sql . SELECT FROM
employee_id, last_name, salary, ROUND(salary * 1.155, 0) "New Salary" employees;
4) Modifique la consulta lab_03_02.sql para agregar una columna que reste el salario antiguo del nuevo. Etiquete la columna como Increase . Guarde el contenido del archivo como lab_03_04.sql . Ejecute la consulta revisada. SELECT
FROM
employee_id, last_name, salary, ROUND(salary * 1.155, 0) "New Salary", ROUND(salary * 1.155, 0) - salary "Increase" employees;
5) Escriba una consulta que muestre el apellido (con la primera letra en mayúsculas y el resto en minúsculas) y la longitud del apellido de todos los empleados cuyos nombres empiecen por las letras "J", "A" o "M". Proporcione a cada columna una etiqueta adecuada. Ordene los resultados por el apellido de los empleados. SELECT
INITCAP(last_name) "Name", LENGTH(last_name) "Length" FROM employees WHERE last_name LIKE 'J%' OR last_name LIKE 'M%' OR last_name LIKE 'A%' ORDER BY last_name ;
Vuelva a escribir la consulta para que se le solicite al usuario que introduzca la letra por la que empieza el apellido. Por ejemplo, si el usuario introduce H (en mayúscula) cuando se le solicita que introduzca una letra, la salida debe mostrar a todos los empleados cuyos apellidos empiecen por la letra "H". Oracle Database: Conceptos Fundamentales de SQL I A-32
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (cont ( continua inuaci ción) ón) SELECT
INITCAP(last_name) "Name", INITCAP(last_name) LENGTH(last_name) "Length" FROM employees WHERE last_name LIKE '&start_letter%' ORDER BY last_name;
Modifique la consulta de forma que la mayúscula/minúscula de la letra introducida no afecte a la salida. La letra introducida debe estar en mayúscula antes de que la procese la consulta SELECT. SELECT INITCAP(last_name INITCAP(last_name) ) "Name", LENGTH(last_name) "Length" FROM employees WHERE last_name LIKE UPPER('&start_le UPPER('&start_letter%' tter%' ) ORDER BY last_name;
6) El departamento de recursos humanos desea buscar la duración del contrato de cada empleado. Para cada empleado, muestre el apellido y calcule el número de meses entre el día de hoy y la fecha de contratación del empleado. Etiquete la columna como MONTHS_WORKED . Ordene los resultados por el número de meses durante los que ha trabajado. Redondee el número de meses hasta el número entero más cercano. Nota: debido a que esta consulta depende de la fecha de ejecución, los valores de la
columna MONTHS_WORKED serán diferentes. SELECT last_name, ROUND(MONTHS_BET ROUND(MONTHS_BETWEEN( WEEN( SYSDATE, hire_date)) MONTHS_WORKED FROM employees ORDER BY months_worked;
Si tiene tiempo, realice los siguientes ejercicios: 7) Cree una consulta para mostrar el apellido y el salario de todos los empleados. Formatee el salario para que tenga 15 caracteres de longitud y tenga un relleno hacia la izquierda con el símbolo $. Etiquete la columna como SALARY. SELECT last_name, LPAD(salary, 15, '$') SALARY FROM employees;
8) Cree una consulta que muestre los primeros ocho caracteres de los apellidos de los empleados y que indique las cantidades de sus salarios con asteriscos. Cada asterisco significa mil dólares. Ordene los datos en orden descendente de salarios. Etiquete la columna como EMPLOYEES_AND_THEIR_SALARIES . SELECT rpad(last_name, 8)||' '|| rpad(' ', salary/1000+1, '*') EMPLOYEES_AND_THEIR_SALARIES FROM employees ORDER BY salary DESC;
Oracle Database: Conceptos Fundamentales de SQL I A-33
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (cont ( continua inuaci ción) ón) 9) Cree una consulta para mostrar el apellido y el número de semanas durante las que han trabajado todos los empleados del departamento 90. Etiquete la columna de número de semanas como TENURE. Trunque el valor del número de semana en 0 decimales. Muestre los registros en orden descendente de antigüedad del empleado. Nota: el valor TENURE variará ya que depende de la fecha de ejecución de la
consulta.
SELECT last_name, trunc((SYSDATE-hire_date)/7) trunc((SYSDATE-hire_date)/7) AS TENURE FROM employees WHERE department_id = 90 ORDER BY TENURE DESC
Oracle Database: Conceptos Fundamentales de SQL I A-34
Prácticas de la Lección 4 Esta práctica proporciona una variedad de ejercicios que utilizan las funciones TO_CHAR y TO_DATE y expresiones condicionales como DECODE y CASE. Recuerde que para las funciones anidadas, los resultados se evalúan desde la función más profunda hasta la función menos profunda.
Oracle Database: Conceptos Fundamentales de SQL I A-35
P ráctica ráctica 4-1: 4-1: Us o de de Funci Funcione oness de C onvers onvers ión y E xpres iones iones Condicionales 1) Cree un informe que produzca los siguientes resultados para cada empleado: earns monthly but wants <3 veces el salario.>. Etiquete la columna como Dream Salaries.
…
2) Muestre el apellido, fecha de contratación y fecha de revisión de salario de cada empleado, que es el primer lunes después de seis meses de contrato. Etiquete la columna como REVIEW. Formatee las fechas para que aparezcan en un formato similar a “Lunes treinta y uno de julio de d e 2000”.
…
3) Muestre el apellido, fecha de contratación y día de la semana en el que empezó a trabajar el empleado. Etiquete la columna como DAY. Ordene los resultados por el día de la semana, empezando por el lunes.
…
Oracle Database: Conceptos Fundamentales de SQL I A-36
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (cont ( continua inuaci ción) ón) 4) Cree una consulta que muestre los apellidos y comisiones de los empleados. Si un empleado no obtiene ninguna comisión, indique "No Commission". Etiquete la columna como COMM.
…
Si tiene tiempo, realice los siguientes ejercicios: 5) Con la función DECODE, escriba una consulta que muestre el grado de todos los empleados según el valor de la columna JOB_ID, utilizando los siguientes datos: Cargo AD_PRES ST_MAN IT_PROG SA_REP ST_CLERK None of the above
Grado A B C D E 0
…
…
Oracle Database: Conceptos Fundamentales de SQL I A-37
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 6) Vuelva a escribir la sentencia del ejercicio anterior utilizando la sintaxis CASE.
…
…
Oracle Database: Conceptos Fundamentales de SQL I A-38
S oluciones a la Práctic a 4-1: Us o de Funciones de Convers ión y E xpresi ones C ondicionales 1) Cree un informe que produzca los siguientes resultados para cada empleado: earns < salario> mensual pero desea<3 veces el salario.>. Etiquete la columna como Dream Salaries . SELECT
FROM
last_name || ' earns ' || TO_CHAR(salary, 'fm$99,999.00') || ' monthly but wants ' || TO_CHAR(salary * 3, 'fm$99,999.00') || '.' "Dream Salaries" employees;
2) Muestre el apellido, fecha de contratación y fecha de revisión de salario de cada empleado, que es el primer lunes después de seis meses de contrato. Etiquete la columna como REVIEW . Formatee las fechas para que aparezcan en un formato similar a “Lunes treinta y uno de julio de 2000”. SELECT last_name, hire_date, TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6),'MONDAY'), 'fmDay, "the" Ddspth "of" Month, YYYY') REVIEW FROM employees;
3) Muestre el apellido, fecha de contratación y día de la semana en el que empezó a trabajar el empleado. Etiquete la columna como DAY. Ordene los resultados por el día de la semana, empezando por el lunes. SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') DAY FROM employees ORDER BY TO_CHAR(hire_date - 1, 'd');
4) Cree una consulta que muestre los apellidos y comisiones de los empleados. Si un empleado no obtiene ninguna comisión, indique "No Commission". Etiquete la columna como COMM. SELECT last_name, NVL(TO_CHAR(commission_pct), 'No Commission') COMM FROM employees;
5) Con la función DECODE, escriba una consulta que muestre el grado de todos los empleados según el valor de la columna JOB_ID, utilizando los siguientes datos: Cargo
Grado
AD_PRES ST_MAN IT_PROG SA_REP ST_CLERK None of the above
A B C D E 0
Oracle Database: Conceptos Fundamentales de SQL I A-39
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) SELECT job_id, decode (job_id, 'ST_CLERK', 'SA_REP', 'IT_PROG', 'ST_MAN', 'AD_PRES', '0')GRADE FROM employees;
'E', 'D', 'C', 'B', 'A',
6) Vuelva a escribir la sentencia del ejercicio anterior utilizando la sintaxis CASE. SELECT job_id, CASE WHEN WHEN WHEN WHEN WHEN ELSE FROM employees;
job_id 'ST_CLERK' THEN 'SA_REP' THEN 'IT_PROG' THEN 'ST_MAN' THEN 'AD_PRES' THEN '0' END GRADE
'E' 'D' 'C' 'B' 'A'
Oracle Database: Conceptos Fundamentales de SQL I A-40
Prácticas de la Lección 5 Al finalizar esta práctica, debe estar familiarizado con el uso de funciones de grupo y la selección de grupos de datos.
Oracle Database: Conceptos Fundamentales de SQL I A-41
Práctica 5-1: Informes de Datos A g reg ados con Funciones de Grupo Determine la validez de las tres sentencias siguientes. Seleccione Verdadero o Falso. 1) Las funciones de grupo funcionan en varias filas para producir un resultado por grupo. Verdadero/Falso 2) Las funciones de grupo incluyen valores nulos en los cálculos. Verdadero/Falso 3) La cláusula WHERE restringe las filas antes de incluirlas en un cálculo de grupo. Verdadero/Falso El departamento de recursos humanos necesita los siguientes informes: 4) Encontrar el valor más alto, el valor más bajo, la suma y la media del salario de todos los empleados. Etiquete las columnas como Maximum, Minimum, Sum y Average, respectivamente. Redondee los resultados al número entero más cercano. Guarde la sentencia SQL como lab_05_04.sql. Ejecute la consulta.
5) Modifique la consulta del archivo lab_05_04.sql para mostrar el valor mínimo, el valor máximo, la suma y la media del salario de cada tipo de cargo. Vuelva a guardar lab_05_04.sql como lab_05_05.sql. Ejecute la sentencia en el archivo lab_05_05.sql.
Oracle Database: Conceptos Fundamentales de SQL I A-42
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 6) Escriba una consulta para mostrar el número de personas con el mismo cargo.
Generalice la consulta para preguntar al usuario del departamento de recursos humanos cuál es su puesto. Guarde el script en un archivo denominado lab_05_06.sql. Ejecute la consulta. Introduzca IT_PROG cuando se le solicite.
7) Determine el número de gestores sin enumerarlos en una lista. Etiquete la columna Number of Managers. Indicación: utilice la columna MANAGER_ID para determinar el número de gestores.
8) Busque la diferencia entre los salarios más altos y más bajos. Etiquete la columna como DIFFERENCE.
Si tiene tiempo, realice los siguientes ejercicios: 9) Cree un informe para mostrar el número de gestor y el salario del empleado con menor sueldo de ese gestor. Excluya a cualquier trabajador del que desconozca su gestor. Excluya cualquier grupo en el que el salario mínimo sea 6.000 dólares o menos. Ordene la salida en orden descendente de salarios.
Oracle Database: Conceptos Fundamentales de SQL I A-43
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) Si desea superarse a sí mismo, complete los siguientes ejercicios:
10) Cree una consulta para mostrar el número total de empleados y, de ese total, el número de empleados contratados en 1995, 1996, 1997 y 1998. Cree las cabeceras de columna adecuadas.
11) Cree una consulta de matriz para mostrar el cargo, el salario de ese cargo según el número de departamento y el salario total del cargo para los departamentos 20, 50, 80 y 90, proporcionando a cada columna una cabecera adecuada.
Oracle Database: Conceptos Fundamentales de SQL I A-44
S oluciones a la Práctic a 5-1: Infor mes de Datos A g reg ados con Funciones de Gr upo Determine la validez de las tres sentencias siguientes. Seleccione Verdadero o Falso. 1) Las funciones de grupo funcionan en varias filas para producir un resultado por grupo. Verdadero/Falso 2) Las funciones de grupo incluyen valores nulos en los cálculos. Verdadero/Falso 3) La cláusula WHERE restringe las filas antes de incluirlas en un cálculo de grupo. Verdadero/Falso El departamento de recursos humanos necesita los siguientes informes: 4) Encontrar el valor más alto, el valor más bajo, la suma y la media del salario de todos los empleados. Etiquete las columnas como Maximum , Minimum, Sum y Average , respectivamente. Redondee los resultados al número entero más cercano. Guarde la sentencia SQL como lab_05_04.sql . Ejecute la consulta. SELECT ROUND(MAX(salary),0) ROUND(MIN(salary),0) ROUND(SUM(salary),0) ROUND(AVG(salary),0) FROM employees;
"Maximum", "Minimum", "Sum", "Average"
5) Modifique la consulta del archivo lab_05_04.sql para mostrar el valor mínimo, el valor máximo, la suma y la media del salario de cada tipo de cargo. Vuelva a guardar lab_05_04.sql como lab_05_05.sql . Ejecute la sentencia en el archivo lab_05_05.sql . SELECT job_id, ROUND(MAX(salary),0) ROUND(MIN(salary),0) ROUND(SUM(salary),0) ROUND(AVG(salary),0) FROM employees GROUP BY job_id;
"Maximum", "Minimum", "Sum", "Average"
6) Escriba una consulta para mostrar el número de personas con el mismo cargo. SELECT job_id, COUNT(*) FROM employees GROUP BY job_id;
Generalice la consulta para preguntar al usuario del departamento de recursos humanos cuál es su puesto. Guarde el script en un archivo denominado lab_05_06.sql . Ejecute la consulta. Introduzca IT_PROG cuando se le solicite y haga clic en OK. SELECT job_id, COUNT(*) FROM employees WHERE job_id = '&job_title' GROUP BY job_id;
Oracle Database: Conceptos Fundamentales de SQL I A-45
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 7) Determine el número de gestores sin enumerarlos en una lista. Etiquete la columna como Number of Managers . Indicación: utilice la columna MANAGER_ID para determinar el número de gestores. SELECT COUNT(DISTINCT manager_id) "Number of Managers" FROM employees;
8) Busque la diferencia entre los salarios más altos y más bajos. Etiquete la columna como DIFFERENCE . SELECT FROM
MAX(salary) - MIN(salary) DIFFERENCE employees;
Si tiene tiempo, realice los siguientes ejercicios: 9) Cree un informe para mostrar el número de gestor y el salario del empleado con menor sueldo de ese gestor. Excluya a cualquier trabajador del que desconozca su gestor. Excluya cualquier grupo en el que el salario mínimo sea 6.000 dólares o menos. Ordene la salida en orden descendente de salarios. SELECT FROM WHERE GROUP BY HAVING ORDER BY
manager_id, MIN(salary) employees manager_id IS NOT NULL manager_id MIN(salary) > 6000 MIN(salary) DESC;
Si desea superarse a sí mismo, complete los siguientes ejercicios: 10) Cree una consulta que muestre el número total de empleados y, de ese total, el número de empleados contratados en 1995, 1996, 1997 y 1998. Cree las cabeceras de columna adecuadas. SELECT
COUNT(*) total, SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1995,1,0))"1995", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1996,1,0))"1996", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1997,1,0))"1997", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1998,1,0))"1998" FROM employees;
Oracle Database: Conceptos Fundamentales de SQL I A-46
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 11) Cree una consulta de matriz para mostrar el cargo, el salario de ese cargo según el número de departamento y el salario total del cargo para los departamentos 20, 50, 80 y 90, proporcionando a cada columna una cabecera adecuada. SELECT
job_id "Job", SUM(DECODE(department_id SUM(DECODE(department_id SUM(DECODE(department_id SUM(DECODE(department_id SUM(salary) "Total" FROM employees GROUP BY job_id;
, , , ,
20, 50, 80, 90,
salary)) salary)) salary)) salary))
"Dept "Dept "Dept "Dept
Oracle Database: Conceptos Fundamentales de SQL I A-47
20", 50", 80", 90",
Prácticas de la Lección 6 Esta práctica está destinada a proporcionarle experiencia en la extracción de datos desde más de una tabla utilizando uniones compatibles con SQL:1999.
Oracle Database: Conceptos Fundamentales de SQL I A-48
Práctica 6-1: Vis ualización de Datos de Vari as Tablas Utilizando Uniones 1) Escriba una consulta para que el departamento de recursos humanos genere las direcciones de todos los departamentos. Utilice las tablas LOCATIONS y COUNTRIES. Muestre el ID de ubicación, dirección, ciudad, estado o provincia y país en la salida. Utilice NATURAL JOIN para producir los resultados.
2) El departamento de recursos humanos necesita un informe sólo de los empleados con los departamentos correspondientes. Escriba una consulta para mostrar el apellido, número y nombre de departamento de estos empleados.
…
3) El departamento de recursos humanos necesita un informe de todos los empleados de Toronto. Muestre el apellido, cargo, número y nombre de departamento de todos los empleados que trabajan en Toronto.
4) Cree un informe para mostrar el apellido y número de empleado junto con el apellido y número de gestor del empleado. Etiquete las columnas como Employee, Emp#, Manager y Mgr#, respectivamente. Guarde la sentencia SQL como lab_06_04.sql. Ejecute la consulta.
…
Oracle Database: Conceptos Fundamentales de SQL I A-49
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
5) Modifique lab_06_04.sql para mostrar todos los empleados, incluido King, que no tienen gestor. Ordene los resultados por número de empleado. Guarde la sentencia SQL como lab_06_05.sql . Ejecute la consulta en el archivo lab_06_05.sql .
…
6) Cree un informe del departamento de recursos humanos que muestre los apellidos y números de empleado y todos los empleados que trabajen en el mismo departamento como un empleado determinado. Proporcione a cada columna una etiqueta adecuada. Guarde el script en un archivo denominado lab_06_06.sql.
…
7) El departamento de recursos humanos necesita un informe sobre los salarios y grados de cargo. Para familiarizarse con la tabla JOB_GRADES, en primer lugar, muestre la estructura de la tabla JOB_GRADES. A continuación, cree una consulta que muestre el apellido, cargo, nombre de departamento, salario y grado de todos los empleados.
Oracle Database: Conceptos Fundamentales de SQL I A-50
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
…
Si desea superarse a sí mismo, complete los siguientes ejercicios: 8) El departamento de recursos humanos desea determinar los nombres de todos los empleados contratados después de Davies. Cree una consulta para mostrar el nombre y la fecha de contratación de cualquier empleado contratado después del empleado Davies.
9) El departamento de recursos humanos necesita buscar el nombre y la fecha de contratación de todos los empleados contratados antes que sus gestores, junto con el nombre y fecha de contratación del gestor. Guarde el script en un archivo denominado lab_06_09.sql.
Oracle Database: Conceptos Fundamentales de SQL I A-51
S oluciones a la Práctic a 6-1: Vis ualizaci ón de Datos de Varias Tablas Utilizando Uniones 1) Escriba una consulta para que el departamento de recursos humanos genere las direcciones de todos los departamentos. Utilice las tablas LOCATIONS y COUNTRIES. Muestre el ID de ubicación, dirección, ciudad, estado o provincia y país en la salida. Utilice NATURAL JOIN para producir los resultados. SELECT location_id, street_address, city, state_province, country_name FROM locations NATURAL JOIN countries;
2) El departamento de recursos humanos necesita un informe de todos los empleados. Escriba una consulta para mostrar el apellido, número y nombre de departamento de todos los empleados. SELECT last_name, department_id, department_name FROM employees JOIN departments USING (department_id);
3) El departamento de recursos humanos necesita un informe de todos los empleados de Toronto. Muestre el apellido, cargo, número y nombre de departamento de todos los empleados que trabajan en Toronto. SELECT e.last_name, e.job_id, e.department_id, d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) WHERE LOWER(l.city) = 'toronto';
4) Cree un informe para mostrar el apellido y número de empleado junto con el apellido y número de gestor de sus gestores. Etiquete las columnas como Employee, Emp#, Manager y Mgr#, respectivamente. Guarde la sentencia SQL como lab_06_04.sql. Ejecute la consulta. SELECT w.last_name "Employee", w.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#" FROM employees w join employees m ON (w.manager_id = m.employee_id);
5) Modifique lab_06_04.sql para mostrar todos los empleados, incluido King, que no tienen gestor. Ordene los resultados por número de empleado. Guarde la sentencia SQL como lab_06_05.sql. Ejecute la consulta en el archivo lab_06_05.sql. SELECT w.last_name "Employee", w.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#" FROM employees w LEFT OUTER JOIN employees m ON (w.manager_id = m.employee_id) ORDER BY 2;
Oracle Database: Conceptos Fundamentales de SQL I A-52
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 6) Cree un informe del departamento de recursos humanos que muestre los apellidos y números de empleado y todos los empleados que trabajen en el mismo departamento como un empleado determinado. Proporcione a cada columna una etiqueta adecuada. Guarde el script en un archivo denominado lab_06_06.sql. Ejecute la consulta. SELECT e.department_id department, e.last_name employee, c.last_name colleague FROM employees e JOIN employees c ON (e.department_id = c.department_id) WHERE e.employee_id <> c.employee_id ORDER BY e.department_id, e.last_name, c.last_name;
7) El departamento de recursos humanos necesita un informe sobre los salarios y grados de cargo. Para familiarizarse con la tabla JOB_GRADES, en primer lugar, muestre la estructura de la tabla JOB_GRADES. A continuación, cree una consulta que muestre el apellido, cargo, nombre de departamento, salario y grado de todos los empleados. DESC JOB_GRADES SELECT e.last_name, e.job_id, d.department_name, e.salary, j.grade_level FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN job_grades j ON (e.salary BETWEEN j.lowest_sal AND j.highest_sal);
Si desea superarse a sí mismo, complete los siguientes ejercicios: 8) El departamento de recursos humanos desea determinar los nombres de todos los empleados contratados después de Davies. Cree una consulta para mostrar el nombre y la fecha de contratación de cualquier empleado contratado después del empleado Davies. SELECT FROM ON WHERE
e.last_name, e.hire_date employees e JOIN employees davies (davies.last_name = 'Davies') davies.hire_date < e.hire_date;
9) El departamento de recursos humanos necesita buscar el nombre y la fecha de contratación de todos los empleados contratados antes que sus gestores, junto con el nombre y fecha de contratación del gestor. Guarde el script en un archivo denominado lab_06_09.sql. SELECT w.last_name, w.hire_date, m.last_name, m.hire_date FROM employees w JOIN employees m ON (w.manager_id = m.employee_id) WHERE w.hire_date < m.hire_date;
Oracle Database: Conceptos Fundamentales de SQL I A-53
Prácticas de la Lección 7 En esta práctica escribirá consultas complejas utilizando sentencias SELECT anidadas. Para las preguntas de la práctica, puede crear la consulta interna primero. Antes de codificar la consulta externa, asegúrese de que la consulta interna se ejecuta y produce los datos que anticipe.
Oracle Database: Conceptos Fundamentales de SQL I A-54
Práctic a 7-1: Us o de S ubconsultas para Solucionar C onsultas 1) El departamento de recursos humanos necesita una consulta que solicite al usuario el apellido de un empleado. A continuación, la consulta muestra el apellido y la fecha de contratación de cualquier empleado en el mismo departamento del empleado cuyo nombre se proporciona (excepto ese empleado). Por ejemplo, si el usuario introduce Zlotkey, se buscarán todos los empleados que trabajen con Zlotkey (excepto Zlotkey).
2) Cree un informe que muestre el número de empleado, apellido y salario de todos los empleados que ganan más del salario medio. Ordene los resultados en orden ascendente de salario.
3) Escriba una consulta que muestre el número de empleado y el apellido de todos los empleados que trabajan en un departamento con un empleado cuyo apellido contiene una "u". Guarde la sentencia SQL en un archivo denominado lab_07_03.sql. Ejecute la consulta.
Oracle Database: Conceptos Fundamentales de SQL I A-55
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 4) El departamento de recursos humanos necesita un informe que muestre el apellido, número de departamento e ID de cargo de todos los empleados cuyo ID de ubicación de departamento sea 1700.
Modifique la consulta para que se solicite al usuario un ID de ubicación. Guarde estos datos en un archivo denominado lab_07_04.sql. 5) Cree un informe para el departamento de recursos humanos que muestre el apellido y el salario de cada empleado que realice informes para King.
6) Cree un informe para el departamento de recursos humanos que muestre el número de departamento, apellido e ID de cargo de cada empleado del departamento Executive.
7) Cree un informe que muestre una lista de todos los empleados cuyo salario sea mayor que el salario de los empleados del departamento 60. Si tiene tiempo, realice el siguiente ejercicio: 8) Modifique la consulta del archivo lab_07_03.sql para mostrar el número de empleado, apellido y salario de todos los empleados que ganan más del salario medio y que trabajan en un departamento con cualquier empleado cuyo apellido contiene una "u". Vuelva a guardar lab_07_03.sql como lab_07_08.sql. Ejecute la sentencia en el archivo lab_07_08.sql.
Oracle Database: Conceptos Fundamentales de SQL I A-56
S oluciones a la Práctic a 7-1: Us o de S ubcons ultas para S olucionar Cons ultas 1) El departamento de recursos humanos necesita una consulta que solicite al usuario el apellido de un empleado. A continuación, la consulta muestra el apellido y la fecha de contratación de cualquier empleado en el mismo departamento del empleado cuyo nombre se proporciona (excepto ese empleado). Por ejemplo, si el usuario introduce Zlotkey, se buscarán todos los empleados que trabajen con Zlotkey (excepto Zlotkey). UNDEFINE Enter_name SELECT last_name, hire_date FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE last_name = '&&Enter_name') AND last_name <> '&Enter_name';
2) Cree un informe que muestre el número de empleado, apellido y salario de todos los empleados que ganan más del salario medio. Ordene los resultados en orden ascendente de salario. SELECT employee_id, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) ORDER BY salary;
3) Escriba una consulta que muestre el número de empleado y el apellido de todos los empleados que trabajan en un departamento con un empleado cuyo apellido contiene una "u". Guarde la sentencia SQL en un archivo denominado lab_07_03.sql. Ejecute la consulta. SELECT employee_id, last_name FROM employees WHERE department_id IN (SELECT department_id FROM employees WHERE last_name like '%u%');
4) El departamento de recursos humanos necesita un informe que muestre el apellido, número de departamento e ID de cargo de todos los empleados cuyo ID de ubicación de departamento sea 1700. SELECT last_name, department_id, job_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
Oracle Database: Conceptos Fundamentales de SQL I A-57
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) Modifique la consulta para que se solicite al usuario un ID de ubicación. Guarde estos datos en un archivo denominado lab_07_04.sql. SELECT last_name, department_id, job_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = &Enter_location);
5) Cree un informe para el departamento de recursos humanos que muestre el apellido y el salario de cada empleado que realice informes para King. SELECT last_name, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE last_name = 'King');
6) Cree un informe para el departamento de recursos humanos que muestre el número de departamento, apellido e ID de cargo de cada empleado del departamento Executive. SELECT department_id, last_name, job_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Executive');
7) Cree un informe que muestre una lista de todos los empleados cuyo salario sea mayor que el salario de los empleados del departamento 60. SELECT last_name FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id=60);
Si tiene tiempo, realice el siguiente ejercicio: 8) Modifique la consulta del archivo lab_07_03.sql para mostrar el número de empleado, apellido y salario de todos los empleados que ganan más del salario medio y que trabajan en un departamento con cualquier empleado cuyo apellido contiene una "u". Vuelva a guardar lab_07_03.sql como lab_07_08.sql. Ejecute la sentencia en el archivo lab_07_08.sql. SELECT employee_id, last_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM employees WHERE last_name like '%u%') AND salary > (SELECT AVG(salary) FROM employees);
Oracle Database: Conceptos Fundamentales de SQL I A-58
Prácticas de la Lección 8 En esta práctica escribirá consultas utilizando los operadores de definición.
Oracle Database: Conceptos Fundamentales de SQL I A-59
Práctic a 8-1: Us o de los Operadores de Definición 1) El departamento de recursos humanos necesita una lista de ID de departamento que no contienen el ID de cargo ST_CLERK. Utilice los operadores de definición para crear este informe.
2) El departamento de recursos humanos necesita una lista de países que no tienen ningún departamento. Muestre el ID de país y el nombre de los países. Utilice los operadores de definición para crear este informe.
3) Cree una lista de cargos para los departamentos 10, 50 y 20, en ese orden. Muestre los ID de cargo y departamento mediante los operadores de definición.
4) Cree un informe que muestre los ID de empleado y de cargo de los empleados que actualmente tienen un puesto que es el mismo que tenían cuando fueron contratados (es decir, han cambiado de cargo pero ahora han vuelto a su puesto original).
5) El departamento de recursos humanos necesita un informe con las siguientes especificaciones:
ID de departamento y apellido de todos los empleados de la tabla EMPLOYEES, independientemente de si pertenecen o no a un departamento. El nombre e ID de departamento de todos los departamentos de la tabla DEPARTMENTS, independientemente de si hay o no empleados trabajando en dichos departamentos.
Para ello, escriba una consulta compuesta.
Oracle Database: Conceptos Fundamentales de SQL I A-60
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
Oracle Database: Conceptos Fundamentales de SQL I A-61
S oluciones a la Práctic a 8-1: Us o de los Operadores de Definición 1) El departamento de recursos humanos necesita una lista de ID de departamento que no contienen el ID de cargo ST_CLERK. Utilice los operadores de definición para crear este informe. SELECT FROM MINUS SELECT FROM WHERE
department_id departments department_id employees job_id = 'ST_CLERK'
2) El departamento de recursos humanos necesita una lista de países que no tienen ningún departamento. Muestre el ID de país y el nombre de los países. Utilice los operadores de definición para crear este informe. SELECT country_id,country_name FROM countries MINUS SELECT l.country_id,c.country_name FROM locations l JOIN countries c ON (l.country_id = c.country_id) JOIN departments d ON d.location_id=l.location_id;
3) Cree una lista de cargos para los departamentos 10, 50 y 20, en ese orden. Muestre los ID de cargo y departamento mediante los operadores de definición. SELECT distinct job_id, department_id FROM employees WHERE department_id = 10 UNION ALL SELECT DISTINCT job_id, department_id FROM employees WHERE department_id = 50 UNION ALL SELECT DISTINCT job_id, department_id FROM employees WHERE department_id = 20
4) Cree un informe que muestre los ID de empleado y de cargo de los empleados que actualmente tienen un puesto que es el mismo que tenían cuando fueron contratados (es decir, han cambiado de cargo pero ahora han vuelto a su puesto original). SELECT employee_id,job_id FROM employees INTERSECT SELECT employee_id,job_id FROM job_history;
Oracle Database: Conceptos Fundamentales de SQL I A-62
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 5) El departamento de recursos humanos necesita un informe con las siguientes especificaciones:
ID de departamento y apellido de todos los empleados de la tabla EMPLOYEES, independientemente de si pertenecen o no a un departamento. El nombre e ID de departamento de todos los departamentos de la tabla DEPARTMENTS, independientemente de si hay o no empleados trabajando en dichos departamentos.
Para ello, escriba una consulta compuesta. SELECT last_name,department_id,TO_CHAR(null) FROM employees UNION SELECT TO_CHAR(null),department_id,department_name FROM departments;
Oracle Database: Conceptos Fundamentales de SQL I A-63
Prácticas de la Lección 9 En esta práctica, agregará filas a la tabla MY_EMPLOYEE, actualizará y suprimirá datos de la tabla y controlará las transacciones. Ejecute un script para crear la tabla MY_EMPLOYEE.
Oracle Database: Conceptos Fundamentales de SQL I A-64
Práctica 9-1: Manipulaci ón de Datos El departamento de recursos humanos desea que cree sentencias SQL para insertar, actualizar y suprimir datos de los empleados. Como prototipo, utilizará la tabla MY_EMPLOYEE, antes de proporcionar las sentencias al departamento. Nota: para todas las sentencias DML, utilice el icono Run Script (o pulse [F5]) para ejecutar la consulta. De esta forma, ve los mensajes de comentarios en la página con separadores Script Output. Para consultas SELECT, siga utilizando el icono Execute Statement o pulse [F9] para obtener la salida con formato en la página con separadores Results. Inserte los datos en la tabla MY_EMPLOYEE.
1) Ejecute la sentencia en el script lab_09_01.sql para crear la tabla MY_EMPLOYEE utilizada en esta práctica. 2) Describa la estructura de la tabla MY_EMPLOYEE para identificar los nombres de las columnas.
ID
LAST_NAME
FIRST_NAME
USERID
SALARY
1
Patel
Ralph
rpatel
895
2
Dancs
Betty
bdancs
860
3
Biri
Ben
bbiri
1100
4
Newman
Chad
cnewman
750
5
Ropeburn
Audrey
aropebur
1550
3) Cree una sentencia INSERT para agregar la primera fila de datos a la tabla MY_EMPLOYEE a partir de los siguientes datos de ejemplo. No muestre la lista de columnas en la cláusula INSERT. No introduzca aún todas las filas.
Oracle Database: Conceptos Fundamentales de SQL I A-65
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 4) Rellene la tabla MY_EMPLOYEE con la segunda fila de datos de ejemplo de la lista anterior. En esta ocasión, muestre explícitamente la lista de columnas en la cláusula INSERT.
5) Confirme la adición a la tabla.
6) Escriba una sentencia INSERT en un archivo de script reutilizable para cargar las filas restantes en la tabla MY_EMPLOYEE. El script se debe solicitar para todas las columnas (ID, LAST_NAME, FIRST_NAME, USERID y SALARY). Guarde este script en un archivo lab_09_06.sql. 7) Rellene la tabla con las dos siguientes filas de ejemplo mostradas en el paso 3 mediante la ejecución de la sentencia INSERT en el script que ha creado. 8) Confirme las adiciones a la tabla.
9) Convierta las adiciones de datos en permanentes. Actualice y suprima datos de la tabla MY_EMPLOYEE.
10) Cambie el apellido del empleado 3 a Drexler. 11) Cambie el salario a 1.000 dólares para todos los empleados con un salario inferior a 900. 12) Verifique los cambios en la tabla.
13) Suprima Betty Dancs de la tabla MY_EMPLOYEE. 14) Confirme los cambios en la tabla.
Oracle Database: Conceptos Fundamentales de SQL I A-66
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
15) Confirme todos los cambios pendientes. Controle la transacción de datos a la tabla MY_EMPLOYEE.
16) Rellene la tabla con la última fila de datos de ejemplo mostrada en el paso 3 mediante las sentencias del script que ha creado en el paso 6. Ejecute las sentencias en el script. 17) Confirme la adición a la tabla.
18) Marque un punto intermedio en el procesamiento de la transacción. 19) Suprima todas las filas de la tabla MY_EMPLOYEE. 20) Confirme que la tabla está vacía. 21) Deseche la operación DELETE más reciente sin desechar la operación INSERT anterior. 22) Confirme que la nueva fila esté intacta.
23) Convierta la adición de datos en permanente. Si tiene tiempo, realice el siguiente ejercicio: 24) Modifique el script lab_09_06.sql de forma que USERID se genere automáticamente concatenando la primera letra del primer nombre y de los primeros siete caracteres del apellido. El USERID generado debe estar en minúscula. Por lo tanto, no se debe solicitar el script para el USERID. Guarde este script en un archivo denominado lab_09_24.sql. ID
LAST_NAME
FIRST_NAME
USERID
SALARY
6
Anthony
Mark
manthony
1230
Oracle Database: Conceptos Fundamentales de SQL I A-67
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
25) Ejecute el script lab_09_24.sql para insertar el siguiente registro: 26) Confirme que se ha agregado la nueva fila con el USERID correcto.
Oracle Database: Conceptos Fundamentales de SQL I A-68
S oluciones a la Práctic a 9-1: Manipulación de Datos Inserte los datos en la tabla MY_EMPLOYEE. 1) Ejecute la sentencia en el script lab_09_01.sql para crear la tabla MY_EMPLOYEE utilizada en esta práctica. a) En el menú File, seleccione Open. En el cuadro de diálogo Open, navegue a la carpeta /home/oracle/labs/sql1/labs y haga doble clic en lab_09_01.sql. b) Una vez abierta la sentencia en una hoja de trabajo de SQL, haga clic en el icono Run Script para ejecutar el script. Aparece un mensaje que indica que la creación de la tabla se ha realizado correctamente en la página con separadores Script Output. 2) Describa la estructura de la tabla MY_EMPLOYEE para identificar los nombres de las columnas. DESCRIBE my_employee
3) Cree una sentencia INSERT para agregar la primera fila de datos a la tabla MY_EMPLOYEE a partir de los siguientes datos de ejemplo. No muestre la lista de columnas en la cláusula INSERT. ID
LAST_NAME
FIRST_NAME
USERID
SALARY
1
Patel
Ralph
rpatel
895
2
Dancs
Betty
bdancs
860
3
Biri
Ben
bbiri
1100
4
Newman
Chad
cnewman
750
5
Ropeburn
Audrey
aropebur
1550
INSERT INTO my_employee VALUES (1, 'Patel', 'Ralph', 'rpatel', 895);
4) Rellene la tabla MY_EMPLOYEE con la segunda fila de datos de ejemplo de la lista anterior. En esta ocasión, muestre explícitamente la lista de columnas en la cláusula INSERT. INSERT INTO my_employee (id, last_name, first_name, userid, salary)
Oracle Database: Conceptos Fundamentales de SQL I A-69
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) VALUES (2, 'Dancs', 'Betty', 'bdancs', 860);
5) Confirme las adiciones a la tabla. SELECT FROM
* my_employee;
6) Escriba una sentencia INSERT en un archivo de script reutilizable para cargar las filas restantes en la tabla MY_EMPLOYEE. El script se debe solicitar para todas las columnas (ID, LAST_NAME, FIRST_NAME, USERID y SALARY). Guarde este script en un archivo denominado lab_09_06.sql. INSERT INTO my_employee VALUES (&p_id, '&p_last_name', '&p_first_name', '&p_userid', &p_salary);
7) Rellene la tabla con las dos siguientes filas de ejemplo mostradas en el paso 3 mediante la ejecución de la sentencia INSERT en el script que ha creado. INSERT INTO my_employee VALUES (&p_id, '&p_last_name', '&p_first_name', '&p_userid', &p_salary);
8) Confirme las adiciones a la tabla. SELECT * FROM my_employee;
9) Convierta las adiciones de datos en permanentes. COMMIT;
Actualice y suprima datos de la tabla MY_EMPLOYEE.
10) Cambie el apellido del empleado 3 a Drexler. UPDATE SET WHERE
my_employee last_name = 'Drexler' id = 3;
11) Cambie el salario a 1.000 dólares para todos los empleados con un salario inferior a 900. UPDATE SET WHERE
my_employee salary = 1000 salary < 900;
12) Verifique los cambios en la tabla. SELECT FROM
* my_employee;
13) Suprima Betty Dancs de la tabla MY_EMPLOYEE. DELETE
Oracle Database: Conceptos Fundamentales de SQL I A-70
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) FROM my_employee WHERE last_name = 'Dancs';
14) Confirme los cambios en la tabla. SELECT FROM
* my_employee;
15) Confirme todos los cambios pendientes. COMMIT;
Controle la transacción de datos a la tabla MY_EMPLOYEE.
16) Rellene la tabla con la última fila de datos de ejemplo mostrada en el paso 3 mediante las sentencias del script que ha creado en el paso 6. Ejecute las sentencias en el script. INSERT INTO my_employee VALUES (&p_id, '&p_last_name', '&p_first_name', '&p_userid', &p_salary);
17) Confirme la adición a la tabla. SELECT FROM
* my_employee;
18) Marque un punto intermedio en el procesamiento de la transacción. SAVEPOINT step_17;
19) Suprima todas las filas de la tabla MY_EMPLOYEE. DELETE FROM my_employee;
20) Confirme que la tabla está vacía. SELECT * FROM my_employee;
21) Deseche la operación DELETE más reciente sin desechar la operación INSERT anterior. ROLLBACK TO step_17;
22) Confirme que la nueva fila esté intacta. SELECT * FROM my_employee;
23) Convierta la adición de datos en permanente. COMMIT;
Oracle Database: Conceptos Fundamentales de SQL I A-71
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) Si tiene tiempo, realice el siguiente ejercicio:
24) Modifique el script lab_09_06.sql de forma que USERID se genere automáticamente concatenando la primera letra del primer nombre y de los primeros siete caracteres del apellido. El USERID generado debe estar en minúscula. Por lo tanto, no se debe solicitar el script para el USERID. Guarde este script en un archivo denominado lab_09_24.sql. SET ECHO OFF SET VERIFY OFF INSERT INTO my_employee VALUES (&p_id, '&&p_last_name', '&&p_first_name', lower(substr('&p_first_name', 1, 1) || substr('&p_last_name', 1, 7)), &p_salary); SET VERIFY ON SET ECHO ON UNDEFINE p_first_name UNDEFINE p_last_name
25) Ejecute el script lab_09_24.sql para insertar el siguiente registro: ID
LAST_NAME
FIRST_NAME
USERID
SALARY
6
Anthony
Mark
manthony
1230
26) Confirme que se ha agregado la nueva fila con el USERID correcto. SELECT * FROM my_employee WHERE ID='6';
Oracle Database: Conceptos Fundamentales de SQL I A-72
Prácticas de la Lección 10 Cree nuevas tablas mediante la sentencia CREATE TABLE. Confirme que la nueva tabla se ha agregado a la base de datos. También aprenderá a definir el estado de una tabla como READ ONLY y, a continuación, revertir a READ/WRITE. Nota: para todas las sentencias DDL y DML, haga clic en el icono Run Script (o pulse [F5]) para ejecutar la consulta en SQL Developer. De esta forma, ve los mensajes de comentarios en la página con separadores Script Output. Para consultas SELECT, siga haciendo clic en el icono Execute Statement o pulse [F9] para obtener la salida con formato en la página con separadores Results.
Oracle Database: Conceptos Fundamentales de SQL I A-73
Práctic a 10-1: Us o de S entencias DD L para Crear y G estionar Tablas Column Name
ID
NAME
Key Type
Primary key
Nulls/Unique FK Table FK Column Data type
NUMBER
VARCHAR2
Length
7
25
1) Cree la tabla DEPT según el siguiente gráfico de instancias de tabla. Guarde la sentencia en un script denominado lab_10_01.sql y, a continuación, ejecute el script para crear la tabla. Confirme que se ha creado la tabla.
2) Rellene la tabla DEPT con datos de la tabla DEPARTMENTS. Incluya sólo las columnas que necesite. 3) Cree la tabla EMP según el siguiente gráfico de instancias de tabla. Guarde la sentencia en un script denominado lab_10_03.sql y, a continuación, ejecute el script para crear la tabla. Confirme que se ha creado la tabla. Column Name
ID
LAST_NAME
FIRST_NAME
DEPT_ID
Key Type Nulls/Unique FK Table
DEPT
FK Column
ID
Data type
NUMBER
VARCHAR2
VARCHAR2
NUMBER
Length
7
25
25
7
Oracle Database: Conceptos Fundamentales de SQL I A-74
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 4) Cree la tabla EMPLOYEES2 según la estructura de la tabla EMPLOYEES. Incluya sólo las columnas EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY y DEPARTMENT_ID. Asigne a las columnas de la tabla los nombres ID, FIRST_NAME, LAST_NAME, SALARY y DEPT_ID, respectivamente.
5) Modifique el estado de la tabla EMPLOYEES2 a sólo lectura. Tenga en cuenta que esta opción está soportada en Oracle Database 11 g . ID
FIRST_NAME
LAST_NAME
SALARY
DEPT_ID
34
Grant
Marcie
5678
10
6) Intente insertar la siguiente fila en la tabla EMPLOYEES2: Aparecerá el siguiente mensaje de error:
7) Revierta la tabla EMPLOYEES2 al estado de lectura/escritura. Ahora, vuelva a intentar insertar la misma fila. Tenga en cuenta que esta opción está soportada en Oracle Database 11 g . Deben aparecer los siguientes mensajes:
8) Borre la tabla EMPLOYEES2.
Oracle Database: Conceptos Fundamentales de SQL I A-75
S oluciones a la Práctic a 10-1: Us o de S entencias D DL para Crear y G es tionar Tablas Column Name
ID
NAME
Key Type
Primary key
Nulls/Unique FK Table FK Column Data type
NUMBER
VARCHAR2
Length
7
25
1) Cree la tabla DEPT según el siguiente gráfico de instancias de tabla. Guarde la sentencia en un script denominado lab_10_01.sql y, a continuación, ejecute el script para crear la tabla. Confirme que se ha creado la tabla. CREATE TABLE dept (id NUMBER(7)CONSTRAINT department_id_pk PRIMARY KEY, name VARCHAR2(25));
Para confirmar que se ha creado la tabla y visualizar su estructura, ejecute el siguiente comando: DESCRIBE dept
2) Rellene la tabla DEPT con datos de la tabla DEPARTMENTS. Incluya sólo aquellas columnas que necesite. INSERT INTO dept SELECT department_id, department_name FROM departments;
3) Cree la tabla EMP según el siguiente gráfico de instancias de tabla. Guarde la sentencia en un script denominado lab_10_03.sql y, a continuación, ejecute el script para crear la tabla. Confirme que se ha creado la tabla. Column Name
ID
LAST_NAME
FIRST_NAME
DEPT_ID
Key Type Nulls/Unique FK Table
DEPT
FK Column
ID
Data type
NUMBER
VARCHAR2
VARCHAR2
NUMBER
Length
7
25
25
7
Oracle Database: Conceptos Fundamentales de SQL I A-76
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) CREATE TABLE emp (id NUMBER(7), last_name VARCHAR2(25), first_name VARCHAR2(25), dept_id NUMBER(7) CONSTRAINT emp_dept_id_FK REFERENCES dept (id) );
Para confirmar que se ha creado la tabla y ver su estructura: DESCRIBE emp
4) Cree la tabla EMPLOYEES2 según la estructura de la tabla EMPLOYEES. Incluya sólo las columnas EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY y DEPARTMENT_ID. Asigne a las columnas de la tabla los nombres ID, FIRST_NAME, LAST_NAME, SALARY y DEPT_ID, respectivamente. CREATE TABLE employees2 AS SELECT employee_id id, first_name, last_name, salary, department_id dept_id FROM employees;
5) Modifique el estado de la tabla EMPLOYEES2 a sólo lectura. ALTER TABLE employees2 READ ONLY
6) Intente insertar la siguiente fila en la tabla EMPLOYEES2. ID
FIRST_NAME
LAST_NAME
SALARY
DEPT_ID
34
Grant
Marcie
5678
10
Aparecerá el mensaje de error “Update operation not allowed on table”. Por lo tanto,
no se le permitirá insertar ninguna fila en la tabla porque se ha asignado un estado de sólo lectura. INSERT INTO employees2 VALUES (34, 'Grant','Marcie',5678,10)
7) Revierta la tabla EMPLOYEES2 al estado de lectura/escritura. Ahora, vuelva a intentar insertar la misma fila. Ahora, debido a que a la tabla se le ha asignado un estado READ WRITE, podrá insertar una fila en la tabla. ALTER TABLE employees2 READ WRITE INSERT INTO employees2 VALUES (34, 'Grant','Marcie',5678,10)
Oracle Database: Conceptos Fundamentales de SQL I A-77
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 8) Borre la tabla EMPLOYEES2. Nota: si es necesario, puede borrar una tabla con modo READ ONLY. Para probar esto, vuelva a modificar la tabla al estado READ ONLY y, a continuación, ejecute el comando DROP TABLE. Se borrará la tabla EMPLOYEES2. DROP TABLE employees2;
Oracle Database: Conceptos Fundamentales de SQL I A-78
Prácticas de la Lección 11 La Parte 1 de la práctica de esta lección ofrece varios ejercicios de creación, uso y eliminación de vistas. Complete las preguntas de la 1 a la 6 de esta lección. La Parte 2 de la práctica de esta lección ofrece varios ejercicios de creación y uso de una secuencia, un índice y un sinónimo. Complete las preguntas de la 7 a la 10 de esta lección.
Oracle Database: Conceptos Fundamentales de SQL I A-79
Práctic a 11-1: C reaci ón de Otros Objetos de E s quema Parte 1
1) El personal del departamento de recursos humanos desea ocultar algunos de los datos de la tabla EMPLOYEES . Cree una vista denominada EMPLOYEES_VU basada en los números y los apellidos de los empleados y en los números de departamento de la tabla EMPLOYEES . La cabecera del nombre de empleado debe ser EMPLOYEE . 2) Confirme que la vista funciona. Visualice el contenido de la vista EMPLOYEES_VU.
…
3) Con la vista EMPLOYEES_VU, escriba una consulta para el departamento de recursos humanos para visualizar todos los nombres de empleados y números de departamento.
…
4) El departamento 50 necesita acceso a los datos de los empleados. Cree una vista con el nombre DEPT50 que contenga los números y apellidos de los empleados y los números de departamento de todos los empleados del departamento 50. Se le ha pedido que etiquete las columnas de la vista como EMPNO, EMPLOYEE y DEPTNO. Por motivos de seguridad, no permita la reasignación de un empleado a otro departamento a través de la vista. 5) Visualice la estructura y el contenido de la vista DEPT50.
Oracle Database: Conceptos Fundamentales de SQL I A-80
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
6) Pruebe la vista. Intente reasignar Matos al departamento 80. Parte 2
7) Necesita una secuencia que se pueda utilizar con la columna PRIMARY KEY de la tabla DEPT. La secuencia debe empezar en 200 y tener un valor máximo de 1.000. Aplique incrementos de 10 a la secuencia. Asigne a la secuencia el nombre DEPT_ID_SEQ. 8) Para probar la secuencia, escriba un script para insertar dos filas en la tabla DEPT. Asigne al script el nombre lab_11_08.sql. Asegúrese de utilizar la secuencia que ha creado para la columna ID. Agregue dos departamentos: Education y Administration. Confirme las adiciones. Ejecute los comandos del script. 9) Cree un índice no único en la columna NAME de la tabla DEPT. 10) Cree un sinónimo para la tabla EMPLOYEES. Llámelo EMP.
Oracle Database: Conceptos Fundamentales de SQL I A-81
S oluciones a la Práctic a 11-1: C reación de Otros Objetos de Esquema Parte 1
1) El personal del departamento de recursos humanos desea ocultar algunos de los datos de la tabla EMPLOYEES . Cree una vista denominada EMPLOYEES_VU basada en los números y los apellidos de los empleados y en los números de departamento de la tabla EMPLOYEES . La cabecera del nombre de empleado debe ser EMPLOYEE . CREATE OR REPLACE VIEW employees_vu AS SELECT employee_id, last_name employee, department_id FROM employees;
2) Confirme que la vista funciona. Visualice el contenido de la vista EMPLOYEES_VU. SELECT FROM
* employees_vu;
3) Con la vista EMPLOYEES_VU, escriba una consulta para el departamento de recursos humanos para visualizar todos los nombres de empleados y números de departamento. SELECT FROM
employee, department_id employees_vu;
4) El departamento 50 necesita acceso a los datos de los empleados. Cree una vista con el nombre DEPT50 que contenga los números y apellidos de los empleados y los números de departamento de todos los empleados del departamento 50. Se le ha solicitado que etiquete las columnas de la vista como EMPNO, EMPLOYEE y DEPTNO. Por motivos de seguridad, no permita la reasignación de un empleado a otro departamento a través de la vista. CREATE VIEW dept50 AS SELECT employee_id empno, last_name employee, department_id deptno FROM employees WHERE department_id = 50 WITH CHECK OPTION CONSTRAINT emp_dept_50;
5) Visualice la estructura y el contenido de la vista DEPT50. DESCRIBE dept50 SELECT FROM
* dept50;
6) Pruebe la vista. Intente reasignar Matos al departamento 80. UPDATE SET WHERE
dept50 deptno = 80 employee = 'Matos';
El error se debe a que la vista DEPT50 se ha creado con la restricción WITH CHECK OPTION. De esta forma, se garantiza que la columna DEPTNO de la vista esté protegida contra cambios.
Oracle Database: Conceptos Fundamentales de SQL I A-82
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) Parte 2
7) Necesita una secuencia que se pueda utilizar con la columna de clave primaria de la tabla DEPT. La secuencia debe empezar en 200 y tener un valor máximo de 1.000. Aplique incrementos de 10 a la secuencia. Asigne a la secuencia el nombre DEPT_ID_SEQ. CREATE SEQUENCE dept_id_seq START WITH 200 INCREMENT BY 10 MAXVALUE 1000;
8) Para probar la secuencia, escriba un script para insertar dos filas en la tabla DEPT. Asigne al script el nombre lab_11_08.sql. Asegúrese de utilizar la secuencia que ha creado para la columna ID. Agregue dos departamentos: Education y Administration. Confirme las adiciones. Ejecute los comandos del script. INSERT INTO dept VALUES (dept_id_seq.nextval, 'Education'); INSERT INTO dept VALUES (dept_id_seq.nextval, 'Administration');
9) Cree un índice no único en la columna NAME de la tabla DEPT. CREATE INDEX dept_name_idx ON dept (name);
10) Cree un sinónimo para la tabla EMPLOYEES. Llámelo EMP. CREATE SYNONYM emp FOR EMPLOYEES;
Oracle Database: Conceptos Fundamentales de SQL I A-83
Prácticas del Apéndice F Esta práctica está destinada a proporcionarle experiencia práctica en la extracción de datos desde más de una tabla utilizando sintaxis de unión en Oracle.
Oracle Database: Conceptos Fundamentales de SQL I A-84
Práctic a F-1: S intaxi s de Unión en Oracle 1) Escriba una consulta para que el departamento de recursos humanos genere las direcciones de todos los departamentos. Utilice las tablas LOCATIONS y COUNTRIES. Muestre el ID de ubicación, dirección, ciudad, estado o provincia y país en la salida. Ejecute la consulta.
2) El departamento de recursos humanos necesita un informe de todos los empleados. Escriba una consulta para mostrar el apellido, número y nombre de departamento de todos los empleados. Ejecute la consulta.
…
3) El departamento de recursos humanos necesita un informe de todos los empleados de Toronto. Muestre el apellido, cargo, número y nombre de departamento de todos los empleados que trabajan en Toronto.
4) Cree un informe para mostrar el apellido y número de empleado junto con el apellido y número de gestor de sus gestores. Etiquete las columnas como Employee, Emp#, Manager y Mgr#, respectivamente. Guarde la sentencia SQL como lab_f_04.sql.
…
Oracle Database: Conceptos Fundamentales de SQL I A-85
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 5) Modifique lab_f_04.sql para mostrar todos los empleados, incluido King, que no tienen gestor. Ordene los resultados por número de empleado. Guarde la sentencia SQL como lab_f_05.sql. Ejecute la consulta en el archivo lab_f_05.sql.
…
6) Cree un informe del departamento de recursos humanos que muestre los apellidos y números de empleado y todos los empleados que trabajen en el mismo departamento como un empleado determinado. Proporcione a cada columna una etiqueta adecuada. Guarde el script en un archivo denominado lab_f_06.sql.
…
7) El departamento de recursos humanos necesita un informe sobre los salarios y grados de cargo. Para familiarizarse con la tabla JOB_GRADES, en primer lugar, muestre la estructura de la tabla JOB_GRADES. A continuación, cree una consulta que muestre el apellido, cargo, nombre de departamento, salario y grado de todos los empleados.
Oracle Database: Conceptos Fundamentales de SQL I A-86
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación)
…
Si desea superarse a sí mismo, complete los siguientes ejercicios: 8) El departamento de recursos humanos desea determinar los nombres de todos los empleados contratados después de Davies. Cree una consulta para mostrar el nombre y la fecha de contratación de cualquier empleado contratado después del empleado Davies.
9) El departamento de recursos humanos necesita buscar el nombre y la fecha de contratación de todos los empleados contratados antes que sus gestores, junto con el nombre y fecha de contratación del gestor. Guarde el script en un archivo denominado lab_f_09.sql.
Oracle Database: Conceptos Fundamentales de SQL I A-87
S oluciones a las Práctic as F -1: S intaxis de Unión en Or acle 1) Escriba una consulta para que el departamento de recursos humanos genere las direcciones de todos los departamentos. Utilice las tablas LOCATIONS y COUNTRIES. Muestre el ID de ubicación, dirección, ciudad, estado o provincia y país en la salida. Ejecute la consulta. SELECT location_id, street_address, city, state_province, country_name FROM locations, countries WHERE locations.country_id = countries.country_id;
2) El departamento de recursos humanos necesita un informe de todos los empleados. Escriba una consulta para mostrar el apellido, número y nombre de departamento de todos los empleados. Ejecute la consulta. SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id;
3) El departamento de recursos humanos necesita un informe de todos los empleados de Toronto. Muestre el apellido, cargo, número y nombre de departamento de todos los empleados que trabajan en Toronto. SELECT e.last_name, e.job_id, e.department_id, d.department_name FROM employees e, departments d , locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND LOWER(l.city) = 'toronto';
4) Cree un informe para mostrar el apellido y número de empleado junto con el apellido del gestor del empleado y el número de gestor. Etiquete las columnas como Employee, Emp#, Manager y Mgr#, respectivamente. Guarde la sentencia SQL como lab_f_04.sql. SELECT w.last_name "Employee", w.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#" FROM employees w , employees m WHERE w.manager_id = m.employee_id;
5) Modifique lab_f_04.sql para mostrar todos los empleados, incluido King, que no tienen gestor. Ordene los resultados por número de empleado. Guarde la sentencia SQL como lab_f_05.sql. Ejecute la consulta en el archivo lab_f_05.sql. SELECT w.last_name "Employee", w.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#" FROM employees w, employees m WHERE w.manager_id = m.employee_id (+);
Oracle Database: Conceptos Fundamentales de SQL I A-88
¡Error! Utilice la pestaña Inicio para aplicar Heading 2 al texto que desea que aparezca aquí. (continuación) 6) Cree un informe del departamento de recursos humanos que muestre los apellidos y números de empleado y todos los empleados que trabajen en el mismo departamento como un empleado determinado. Proporcione a cada columna una etiqueta adecuada. Guarde el script en un archivo denominado lab_f_06.sql. SELECT e1.department_id department, e1.last_name employee, e2.last_name colleague FROM employees e1, employees e2 WHERE e1.department_id = e2.department_id AND e1.employee_id <> e2.employee_id ORDER BY e1.department_id, e1.last_name, e2.last_name;
7) El departamento de recursos humanos necesita un informe sobre los salarios y grados de cargo. Para familiarizarse con la tabla JOB_GRADES, en primer lugar, muestre la estructura de la tabla JOB_GRADES. A continuación, cree una consulta que muestre el apellido, cargo, nombre de departamento, salario y grado de todos los empleados. DESC JOB_GRADES SELECT e.last_name, e.job_id, d.department_name, e.salary, j.grade_level FROM employees e, departments d, job_grades j WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal AND j.highest_sal;
Si desea superarse a sí mismo, complete los siguientes ejercicios: 8) El departamento de recursos humanos desea determinar los nombre de todos los empleados contratados después de Davies. Cree una consulta para mostrar el nombre y la fecha de contratación de cualquier empleado contratado después de Davies. SELECT FROM WHERE AND
e.last_name, e.hire_date employees e , employees davies davies.last_name = 'Davies' davies.hire_date < e.hire_date;
9) El departamento de recursos humanos necesita buscar el nombre y la fecha de contratación de todos los empleados contratados antes que sus gestores, junto con el nombre y fecha de contratación del gestor. Etiquete las columnas como Employee, Emp Hired, Manager y Mgr Hired, respectivamente. Guarde el script en un archivo denominado lab_f_09.sql. SELECT FROM WHERE AND
w.last_name, w.hire_date, m.last_name, m.hire_date employees w , employees m w.manager_id = m.employee_id w.hire_date < m.hire_date;
Oracle Database: Conceptos Fundamentales de SQL I A-89