Práctica 1: descripción general Práctica 1: descripción general En esta práctica, escribe consultas SELECT simples. Las consultas cubren la mayoría de las cláusulas SELECT y las operaciones que aprendió en esta lección. Practica 1 Parte 1 Pon a prueba tus conocimientos: 1. La siguiente instrucción SELECT se ejecuta con éxito: SELECT last_name, job_id, salary AS Sal FROM employees;
Verdad Utiliza el As para cambiar a salario por sal en la tabla empleados 3. Hay cuatro errores de codificación en la siguiente declaración. ¿Puedes identificarlos? SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROM employees; SELECT employee_id, last_name , salary * 12 AS "ANNUAL SALARY" FROM employees; Parte 2 El departamento de RR.HH. quiere una consulta para mostrar el apellido, la identificación del trabajo, la fecha de contratación y la identificación del empleado para cada empleado, con la identificación del empleado que aparece primero. Proporcione un alias STARTDATE para la columna HIRE_DATE. Guarde su declaración SQL en un u n archivo llamado lab_01_05.sql para que pueda enviar este archivo al departamento de Recursos Humanos. 5.
SELECT employee_id, last_name, job_id, hire_date StartDate FROM employees;
El departamento de recursos humanos desea una consulta para mostrar todas las ID de trabajo únicas de la tabla EMPLOYEES. SELECT DISTINCT job_id FROM employees; 7.
9. El departamento de recursos humanos ha solicitado un informe de todos los empleados y sus identificaciones laborales. Muestre el apellido concatenado con la ID del trabajo (separados por una coma y espacio) y nombre la columna Empleado y Título. SELECT last_name||', '||job_id "EMPLEADO Y TITULO" FROM employees;
PRACTICA 2 DESCRIPCION GENERAL 1. 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. Guarde su declaración SQL como un archivo llamado lab_02_01.sql. Ejecute su consulta.
SELECT last_name, salary FROM employees WHERE salary > 12000; 3. Los departamentos de recursos humanos deben encontrar empleados con salarios altos y salarios bajos. Modifique lab_02_01.sql para mostrar el apellido y el salario de todos los empleados cuyo salario no esté en el rango de $ 5,000 a $ 12,000. Coloque su instrucción SQL en un archivo de texto llamado lab_02_03.sql. SELECT last_name, salary FROM
employees WHERE salary NOT BETWEEN 5000 AND 12000;
5. Visualice el apellido y el número de departamento de todos los empleados en los departamentos 20 o 50 en orden alfabético ascendente por nombre. SELECT last_name, department_id FROM BY last_name ASC;
employees WHERE department_id IN (20, 50) ORDER
7. El departamento de recursos humanos necesita un informe que muestre el apellido y la fecha de contratación de todos los empleados que fueron contratados en 1994. SELECT last_name, hire_date FROM
employees WHERE hire_date LIKE '%94';
9. Muestre el apellido, el salario y la comisión de todos los empleados que ganan comisiones. Clasifica los datos en orden descendente de salario y comisiones. SELECT last_name, salary, commission_pct FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC, commission_pct DESC; 11. El departamento de recursos humanos desea ejecutar informes basados en un gerente. Cree una consulta que solicite al usuario una ID de administrador y genere la ID de empleado, el apellido, el salario y el departamento para los empleados de ese gerente. El departamento de recursos humanos desea la capacidad de ordenar el informe en una columna seleccionada. Puede probar los datos con los siguientes valores: manager_id = 103, sorted by last_name: manager_id = 201, sorted by salary: manager_id = 124, sorted by employee_id: SELECT employee_id, last_name, salary, department_id FROM employees WHERE manager_id = &mgr_num ORDER BY &order_col; 13. Muestre los apellidos de todos los empleados que tienen una "a" y una "e" en su apellido. SELECT last_name FROM '%e%';
employees WHERE last_name LIKE '%a%' AND
last_name LIKE
15. Modifique lab_02_06.sql para mostrar el apellido, el salario y la comisión de todos los empleados cuya comisión sea del 20%. Resave lab_02_06.sql como lab_02_15.sql. Vuelva a ejecutar la instrucción en lab_02_15.sql.
SELECT last_name "Employee", salary "Monthly Salary", WHERE commission_pct = .20;
commission_pct FROM
employees
PRACTICA 3 DESCRIPCION GENERAL 1. Escribe una consulta para mostrar la fecha actual. Etiqueta la columna Fecha.
SELECT sysdate "Date" FROM dual; 3. Ejecute su consulta en el archivo lab_03_02.sql. SELECT employee_id, last_name, salary, ROUND(salary * 1.155, 0) "New Salary" FROM employees; 5. Escriba una consulta que muestre el apellido (con la primera letra en mayúscula y todas las demás letras en minúscula) y la longitud del apellido de todos los empleados cuyo nombre comience con las letras J, A o M. Dé a cada columna un nombre apropiado etiqueta. Ordene los resultados por los apellidos 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 ingrese una letra que inicie el apellido. Por ejemplo, si el usuario ingresa H cuando se le solicita una letra, la salida debe mostrar a todos los empleados cuyo apellido comienza con la letra H. SELECT INITCAP(last_name) "Name", LENGTH(last_name) "Length" FROM employees WHERE last_name LIKE '&start_letter%' ORDER BY last_name; 7. Cree un informe que produzca lo siguiente para cada empleado:
gana mensualmente pero quiere <3 veces salario>. SELECT last_name || ' earns ' || TO_CHAR(salary, 'fm$99,999.00') || ' monthly but wants ' || TO_CHAR(salary * 3, 'fm$99,999.00') || '.' "Dream Salaries" FROM employees; 9. Muestre el apellido, la fecha de contratación y la fecha de revisión salarial de cada empleado, que es el primer lunes después de seis meses de servicio. Etiqueta la columna REVIEW. Formatee las fechas para que aparezcan en el formato similar a "Lunes, el 31 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; 11. Create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, show “No Commission.” Label the column COMM.
SELECT last_name, employees;
NVL(TO_CHAR(commission_pct), 'No Commission') COMM FROM
13. Utilizando la función DECODE, escriba una consulta que muestre el grado de todos los empleados en función del valor de la columna JOB_ID, utilizando los siguientes datos: Grado de trabajo AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E Ninguno de los anteriores 0 SELECT job_id, decode (job_id, 'ST_CLERK', 'E', 'SA_REP', 'D', 'IT_PROG', 'C', 'ST_MAN', 'B', 'AD_PRES', 'A', '0')GRADE FROM employees;
PRACTICA 4 Solucion 1. Las funciones grupales funcionan en muchas filas para producir un resultado por grupo. Verdadero / Falso 3. La cláusula WHERE restringe las filas antes de la inclusión en un cálculo grupal. Verdadero / Falso 5. Modifique la consulta en lab_04_04.sql para mostrar el salario mínimo, máximo, suma y promedio para cada tipo de trabajo. Resave lab_04_04.sql como lab_04_05.sql. Ejecute la instrucción en lab_04_05.sql
SELECT job_id, ROUND(MAX(salary),0) "Maximum", ROUND(MIN(salary),0) "Minimum", ROUND(SUM(salary),0) "Sum", ROUND(AVG(salary),0) "Average" FROM employees GROUP BY job_id; 7. Determine la cantidad de gerentes sin enumerarlos. Etiquete la columna Número de administradores. Sugerencia: utilice la columna MANAGER_ID para determinar la cantidad de administradores. SELECT COUNT(DISTINCT manager_id) "Number of Managers" FROM employees 9. Cree un informe para mostrar el número de administrador y el salario del empleado con el salario más bajo para ese gerente. Excluya a cualquier persona cuyo gerente no sea conocido. Excluya cualquier grupo donde el salario mínimo sea de $ 6,000 o menos. Ordenar el resultado en orden descendente de salario SELECT manager_id, MIN(salary) FROM
employees WHERE manager_id IS NOT NULL GROUP
BY manager_id HAVING MIN(salary) > 6000 ORDER BY MIN(salary) DESC;
11. Cree una consulta matricial para mostrar el trabajo, el salario de ese trabajo en función del número de departamento y el salario total para ese trabajo, para los departamentos 20, 50, 80 y 90, dando a cada columna un título apropiado. SELECT job_id "Job", SUM(DECODE(department_id , 20, salary)) "Dept 20", SUM(DECODE(department_id , 50, salary)) "Dept 50", SUM(DECODE(department_id , 80, salary)) "Dept 80", SUM(DECODE(department_id , 90, salary)) "Dept 90", SUM(salary) "Total" FROM employees GROUP BY job_id;
PRACTICA 5 Solucion 1. Escriba una consulta para el departamento de recursos humanos para generar las direcciones de todos los departamentos. Use las tablas LOCATIONS y COUNTRIES. Muestre la ID de ubicación, la dirección de la calle, la ciudad, el estado o la provincia y el país en la salida. Use una UNIÓN NATURAL para producir los resultados. SELECT location_id, street_address, city, state_province, country_name FROM locations NATURAL JOIN countries; 3. El departamento de recursos humanos necesita un informe de los empleados en Toronto. Muestre el apellido, el trabajo, el número de departamento y el nombre del 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'; 5. Modifique lab_05_04.sql para mostrar todos los empleados, incluido King, que no tiene administrador. Ordene los resultados por el número de empleado. Coloque su instrucción SQL en un archivo de texto llamado lab_05_05.sql. Ejecute la consulta en lab_05_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);
7. El departamento de recursos humanos necesita un informe sobre las calificaciones y los salarios del trabajo. Para familiarizarse con la tabla JOB_GRADES, primero muestre la estructura de la tabla JOB_GRADES. Luego, cree una consulta que muestre el nombre, el trabajo, el nombre del departamento, el salario y la calificación para 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); 9. El departamento de recursos humanos necesita encontrar los nombres y las fechas de contratación de todos los empleados que fueron contratados antes que sus gerentes, junto con los nombres de sus gerentes y las fechas de contratación. Guarde el script en un archivo llamado lab_05_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;
PRACTICA 6 Solucion 1. El departamento de recursos humanos necesita una consulta que solicite al usuario el apellido de un empleado. La consulta muestra el apellido y la fecha de contratación de cualquier empleado en el mismo departamento que el empleado cuyo nombre suministra el usuario (excluyendo a ese empleado). Por ejemplo, si el usuario ingresa Zlotkey, encuentre todos los empleados que trabajen con Zlotkey (excluyendo 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' 3. Escriba una consulta que muestre el número de empleado y el apellido de todos los empleados que trabajan en un departamento con cualquier empleado cuyo apellido contenga un u. Coloque su instrucción SQL en un archivo de texto llamado lab_06_03.sql. Ejecute su consulta. SELECT employee_id, last_name FROM employees WHERE department_id IN (SELECT department_id FROM employees WHERE last_name like '%u%'); 5. Cree un informe para el departamento de Recursos Humanos que muestre el apellido y el salario de cada empleado que se reporte a King. SELECT last_name, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE last_name = 'King'); 7. Modifique la consulta en lab_06_03.sql para mostrar el número de empleado, el apellido y el salario de todos los empleados que ganan más que el salario promedio y que trabajan en un departamento con cualquier empleado cuyo apellido contenga un u. Resave lab_06_03.sql a lab_06_07.sql. Ejecute la instrucción en lab_06_07.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);
PRACTICA 7 Solucion 1. El departamento de recursos humanos necesita una lista de ID de departamento para los departamentos que no contienen el ID de trabajo ST_CLERK. Use set operators para crear este informe. SELECT department_id FROM departments MINUS SELECT department_id FROM employees WHERE job_id = 'ST_CLERK'; 3. Produzca una lista de trabajos para los departamentos 10, 50 y 20, en ese orden. Visualice la identificación del trabajo y la ID del departamento usando los operadores establecidos. COLUMN dummy NOPRINT SELECT job_id, department_id, 'x' dummy FROM employees WHERE department_id = 10 UNION SELECT job_id, department_id, 'y' dummy FROM employees WHERE department_id = 50 UNION SELECT job_id, department_id, 'z' dummy FROM employees WHERE department_id = 20 ORDER BY dummy; COLUMN dummy PRINT 5. El departamento de Recursos Humanos necesita un informe con las siguientes especificaciones: •
Apellido e ID del departamento de todos los empleados de la tabla EMPLEADOS, independientemente de si pertenecen o no a un departamento. • Identificación del departamento y nombre del departamento de todos los departamentos del DEPARTAMENTOS, independientemente de si tienen empleados trabajando en ellos. Escriba una consulta compuesta para lograr esto. SELECT last_name,department_id,TO_CHAR(null) FROM employees UNION SELECT TO_CHAR(null),department_id,department_name FROM departments;
PRACTICA 8 Solucion El departamento de recursos humanos desea que cree declaraciones SQL para insertar, actualizar y eliminar datos de los empleados. Como prototipo, utiliza la tabla MY_EMPLOYEE antes de enviar las declaraciones al departamento de recursos humanos. Inserta datos en la tabla MY_EMPLOYEE . 1. Ejecute la instrucción en el script lab_08_01.sql para compilar la tabla MY_EMPLOYEE que se utilizará para el laboratorio.
CREATE TABLE my_employee (id NUMBER(4) CONSTRAINT my_employee_id_nn NOT NULL, last_name VARCHAR2(25), first_name VARCHAR2(25), userid VARCHAR2(8), salary NUMBER(9,2)); 3. Cree una instrucción INSERT para agregar la primera fila de datos a la tabla MY_EMPLOYEE de los siguientes datos de muestra. No liste las columnas en la cláusula INSERTAR. INSERT INTO my_employee VALUES (1, 'Patel', 'Ralph', 'rpatel', 895); 5. Confirma tu adición a la mesa SELECT * FROM my_employee; 7. Llene la tabla con las dos filas siguientes de datos de muestra enumerados en el paso 3 ejecutando la instrucción INSERT en el script que creó. 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 9. Haga las adiciones de datos permanentes. COMMIT; Actualice y elimine datos en la tabla MY_EMPLOYEE. 11. Cambie el salario a $ 1,000 para todos los empleados con un salario de menos de $ 900.
UPDATE my_employee SET
salary = 1000 WHERE salary < 900;
13. Elimine Betty Dancs de la tabla MY_EMPLOYEE.
