Archivo

Posts Tagged ‘SQL’

ORACLE PL/SQL Repaso

Cursores:

	-- Declarar Cursor
	CURSOR emp_cursor IS
		SELECT last_name, department_id
		FROM employees;
	emp_record emp_cursor%ROWTYPE;

	-- Recorrer Secursar
	FOR emp_record IN emp_cursor LOOP --implicitamente abre y recupera la ocurrencia
     	IF emp_record.department_id = 80 THEN
			DBMS_OUTPUT.PUT_LINE(TO_CHAR(emp_record.department_id)||' '||emp_record.last_name);
		END IF;
   	END LOOP; -- implícitamente cierra la ocurrencia

Funciones

CREATE OR REPLACE FUNCTION g_job (p_jobid IN jobs.job_id%TYPE)
RETURN VARCHAR2
IS
	v_jobtitle jobs.job_title%TYPE;
BEGIN
	SELECT job_title
	INTO v_jobtitle
	FROM jobs
	WHERE job_id = p_jobid;
	RETURN (v_jobtitle);
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		RETURN(NULL);
END g_job;
/
VARIABLE g_title VARCHAR2(30)
EXECUTE :g_title:= g_job('SA_REP')
PRINT g_title

Packages

CREATE OR REPLACE PACKAGE job_pack IS
	PROCEDURE add_job (p_job_id IN jobs.job_id%TYPE, p_job_title IN jobs.job_title%TYPE);
	PROCEDURE upd_job (p_job_id IN jobs.job_id%TYPE,p_job_title IN jobs.job_title%TYPE);
	PROCEDURE del_job (p_job_id IN jobs.job_id%TYPE);
	FUNCTION q_job    (p_job_id IN jobs.job_id%TYPE)RETURN VARCHAR2;
END job_pack;
/

CREATE OR REPLACE PACKAGE BODY job_pack IS
	PROCEDURE add_job (p_job_id IN jobs.job_id%TYPE, p_job_title IN jobs.job_title%TYPE) IS BEGIN
		INSERT INTO jobs(job_id, job_title)
		VALUES (p_jobid, p_jobtitle);
	END add_jobs;

	PROCEDURE upd_job (p_job_id IN jobs.job_id%TYPE, p_job_title IN jobs.job_title%TYPE) IS BEGIN
		UPDATE jobs
		SET job_title=p_jobtitle
		WHERE job_id=p_jobid;
		IF SQL%NOTFOUND THEN
			RAISE_APPLICATION_ERROR(-20202,'No Job Updated');
		END IF;
	END upd_job;

	PROCEDURE del_job (p_job_id IN jobs.job_id%TYPE) IS BEGIN
		DELETE FROM jobs
		WHERE job_id = p_job_id;
		IF SQL%NOTFOUND THEN
			RAISE_APPLICATION_ERROR(-20203,'No job deleted');
		END IF;
	END del_job;

	FUNCTION q_job (p_jobid IN jobs.job_id%TYPE) RETURN VARCHAR2 IS
		v_jobtitle jobs.job_title%TYPE;
	BEGIN
		SELECT job_title
		INTO v_jobtitle
		FROM jobs
		WHERE job_id = p_jobid;
		RETURN (v_jobtitle);
	END q_job
END job_pack;
/	


Procedimientos

create or replace procedure procedimiento (p_empid IN employees.employee_id%TYPE, p_sal OUT employees.salary%TYPE, p_job OUT employees.job_id%TYPE)
IS BEGIN
	SELECT salary,job_id
	INTO p_sal, p_job
	FROM employees
	WHERE employee_id = p_empid;
END procedimiento;
/

-- imprimir
VARIABLE g_sal NUMBER
VARIABLE g_job VARCHAR2(15)
EXECUTE procedimiento(120,:g_sal,:g_job)
PRINT g_sal
PRINT g_job


Triggers

create or replace trigger xxx
after update or insert or delete on employees for each row -- After:Antes
	   if updating  then

	elsif inserting then

	elsif deleting then

	end if;
end xxx;
/
Categorías:ORACLE, PL/SQL Etiquetas: , ,

PRACTICO 7 SQL SOLUTION

diciembre 6, 2008 1 comentario

Práctica 7

Determine si las siguientes dos preguntas son verdadera o falsa

1.- La siguiente sentencia es valida: (V) (F)
DEFINE & p_val = 100
F
2.- El comando DEFINE es un comando SQL (V) (F)
F
3.- Escriba un script para desplegar el apellido de un empleado, cargo y fecha de contrato para todos los empleados que se iniciaron entre un rango dado. Concatenar nombre y trabajo juntos, separado por un espacio y coma, y etiquetar la columna como Empleado. En un archivo Script SQL separado, use el comando DEFINE para proveer los dos rangos. Use el formato MM/DD/YYYY. Grabar los archivos script como lab7-3a.sql y lab7-3b.sql

DEFINE fechaInicio = 01/01/1987
DEFINE fechaTermino = 01/01/2007
SELECT last_name || ', ' || job_id AS "Empleado" , hire_date
FROM employees
WHERE hire_date BEETWEEN TO_DATE(&fechaInicio,'MM/DD/YYYY') AND TO_DATE(&fechaTermino,'MM/DD/YYYY');

4.- Escribir un script para desplegar el apellido, el identificador del cargo y nombre del departamento para cada empleado en una localización dada. Grabar el archivo script como lab7-4.sql

SELECT e.last_name , e.job_id, d.departament_name
FROM employees e, departaments d, locations l
WHERE e.departament_id = d.departament_id AND d.location_id = l.location_id
AND l.city = '&entrada';
Categorías:SQL Etiquetas: ,

PRACTICO 6 SQL SOLUTION

Práctica 6

1.- Escribir una consulta que despliegue el apellido y fecha de contrato de cualquier empleado que trabaje en el mismo departamento que Zlotkey. Excluir a Zlotkey

SELECT last_name, hire_date
FROM employees
WHERE departament_id = (SELECT departament_id FROM employees WHERE last_name = 'Zlotkey')
AND last_name  'Zlotkey';

2.- Crear una consulta que despliegue el número del empleado y su apellido de aquellos empleados que ganan más que el salario promedio. Ordenar ascendentemente por salario

SELECT employee_id, last_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary ASC;

3.- escribir una consulta que despliegue el número del empleado y su apellido de aquellos empleados que trabajan en un departamento con cualquier empleado que tenga una “u” en su apellido. Escribir tu consulta SQL en un texto llamado lab6-3.sql. Ejecutar tu consulta

SELECT employee_id, last_name
FROM employees
WHERE departament_id IN (SELECT departament_id FROM employees WHERE LOWER(last_name) LIKE '%u%');

4.- Desplegar el apellido, número de departamento e identificador del cargo de todos los empleados cuya localización de departamento es 1700

SELECT last_name, departament_id, job_id
FROM employees
WHERE departament_id in (SELECT departament_id FROM departaments WHERE location_id = 1700);

5.- Desplegar el apellido y salario para cada empleado que reporte a King

SELECT apellido, salary
FROM employees
WHERE manager_id in (SELECT manager_id FROM employees WHERE LOWER(last_name) = 'king'); 

6.- Desplegar el número de departamento, apellido e identificador del cargo para cada empleado en el departamento Executive

SELECT departament_id, last_name, job_id
FROM employees
WHERE departament_id = (SELECT departament_id FROM departaments WHERE departament_name= 'Executive');

7.- Modificar la consulta lab6-3.sql para desplegar el número del empleado, apellido y salario de todos aquellos empleados que ganan más que el salario promedio y que trabaja en un departamento con cualquier empleado en cuyo nombre tenga una “u”. Regrabar lab6-3.sql como lab6-7.sql. Ejecutar tu archivo lab6-7.sql

SELECT employee_id, last_name, salary
FROM employees
WHERE departament_id IN (SELECT departament_id FROM employees WHERE LOWER(last_name) LIKE '%u%')
AND salary > (SELECT AVG(salary) FROM employees);
Categorías:SQL Etiquetas: , ,

PRACTICO 5 SQL SOLUTION

Práctica 5

1.- Las funciones de grupo trabajan cruzando muchas filas para producir un resultado (V)(F)
V
2.- Las funciones de grupo incluyen nulos en sus cálculos (V)(F)
F
3.- La cláusula WHERE restringe filas antes de la inclusión en un cálculo de funciones de grupo (V)(F)
V
4.- Desplegar el salario máximo, mínimo, sumatoria, y promedio de todos los trabajadores. Etiquetar Máximo, Mínimo, Sumatoria, Promedio respectivamente. Redondear los resultados al entero más próximo. Guardar la consulta como Lab5_4.sql

SELECT ROUND(MAX(salary),0) AS "Maximo" , ROUND(MIN(salary),0) AS "Minimo", ROUND(SUM(salary),0) AS "Sumatoria", ROUND(AVG(salary),0) AS "Promedio"
FROM employees;

5.- Modificar la consulta Lab5_4 para que despliegue el salario máximo, mínimo, sumatoria y promedio para cada tipo de cargo. Guardar la consulta como Lab5_5.sql

SELECT ROUND(MAX(salary),0) AS "Maximo" , ROUND(MIN(salary),0) AS "Minimo", ROUND(SUM(salary),0) AS "Sumatoria", ROUND(AVG(salary),0) AS "Promedio"
FROM employees;
GROUP BY jod_id;

6.- Escribir una consulta que despliegue el número de trabajadores con el mismo cargo.

SELECT COUNT(*)
FROM employees
GROUP BY job_id;

7.- Determinar el número de administradores sin ellos en el listado. Etiquetar la columna como “Número de Administradores”

SELECT COUNT(DISTINCT manager_id) AS "Número de Administradores"
FROM employees;

8.- Escribir una consulta que despliegue la diferencia entre el salario máximo y mínimo. Etiquetar la columna como diferencia

SELECT (MAX(salary) - MIN(salary)) AS "diferencia"
FROM employees;

9.- Despliega el identificador del administrador y el salario del empleado que menos recibe por administrador. Excluir aquellos donde el administrador no es conocido. Excluir cualquier grupo donde el salario mínimo es 6000 o menor. Ordenar la consulta descendentemente por salario

SELECT manager_id ,salary
FROM emlpoyees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) > 6000
ORDER BY MIN(salary) DESC;

10.- Escribir una consulta que despliegue para cada departamento, nombre, número de empleados y salario promedio para todos los empleados del departamento. Etiquetar las columnas como: Nombre, Localización, Numero de personas y Salario respectivamente. Redondear el salario promedio a dos decimales.

SELECT d.departament_name AS "Nombre" , COUNT(*) AS "Numero de personas",  ROUND(AVG(salary),2) AS "Salario"
FROM departaments d, employees e
WHERE e.departament_id = d.departament_id
GROUP BY d.departament_name;

11.- Crear una consulta que despliegue el número total de empleados y el total de empleados contratado en 1995, 1996, 1997 y 1998. Etiquetar con encabezados apropiados cada columna.

SELECT COUNT(*) AS "Total" ,
SUM(DECODE(TO_CHAR(hire_date,'YYYY'),1995,1,0)) AS "1995" ,
SUM(DECODE(TO_CHAR(hire_date,'YYYY'),1996,1,0)) AS "1996" ,
SUM(DECODE(TO_CHAR(hire_date,'YYYY'),1997,1,0)) AS "1997" ,
SUM(DECODE(TO_CHAR(hire_date,'YYYY'),1998,1,0)) AS "1998"
FROM employees;

12.- Crear una consulta en una matriz que despliegue el cargo, salario, para cada cargo basado en un número de departamento y el salario total para cada cargo, para los departamentos 20, 50, 80, 90. Dar a cada columna un encabezado apropiado.

SELECT job_id,
SUM (DECODE(departament_id,20,salary)) AS "Departamento 20",
SUM (DECODE(departament_id,50,salary)) AS "Departamento 50",
SUM (DECODE(departament_id,80,salary)) AS "Departamento 80",
SUM (DECODE(departament_id,90,salary)) AS "Departamento 90",
SUM(salary) AS "Salario Total"
FROM employees
GROUP BY job_id;
Categorías:SQL Etiquetas: , ,

PRACTICO 4 SQL SOLUTION

Práctica Lección 4
Desplegando Datos de Múltiples Tablas

1.- Escriba una consulta que despliegue el apellido, número y nombre del departamento para todos los empleados

SELECT e.last_name, d.departament_id, d.departament_name
FROM employees e, departaments d
WHERE e.departament_id = d.departament_id;

2.- Crear una única lista de todos los identificadores de los cargos que están en el departamento 80. Incluir la localización del departamento en la salida

SELECT e.job_id , l.location_name
FROM employees e, locations l, departaments d
WHERE e.departament_id = d.departament_id AND d.location_id = l.location_id
AND e.departament_id = 80;

3.- Escribir una consulta que despliegue el apellido del empleado, nombre del departamento, localización y ciudad de todos los empleados que ganan comisión

SELECT e.last_name, d.departament_name, l.location_id, l.city
FROM employees e, departaments d , locations l
WHERE e.departament_id = d.departament_id AND d.location_id = l.location_id
AND commission_pct IS NOT NULL;

4.- Desplegar el apellido del empleado y nombre del departamento para todos los empleados que tienen una “a” minúscula en su apellido. Escribir tu sentencia SQL en un archivo de texto llamado lab4_4.sql

SELECT e.last_name, d.departament_name
FROM employees e, departaments d
WHERE e.departament_id = d.departament_id
AND e.last_name LIKE '%a%';

5.- Escribir una consulta que despliegue el apellido, cargo, número y nombre de departamento para todos los empleados que trabajan en Toronto

SELECT e.last_name, e.job_id, d.departament_id, d.departament_name
FROM employees e, departaments d, locations l
WHERE e.departament_id = d.departament_id AND d.location_id = l.location_id
AND LOWER(l.city) = LOWER('Toronto');

6.- Desplegar el apellido y número del empleado con el apellido de su administrador y número de este. Etiquetar las columnas como Empleado, Emp#, Administrador y Mgr#, respectivamente. Escribir tu sentencia SQL en un archivo de texto llamado lab4_6.sql

SELECT e.last_name AS "Empleado" , e.employee_id AS "Emp#", a.last_name AS "Administrador", a.manager_id AS "Mgr#"
FROM employees e, managers a
WHERE e.manager_id = a.manager_id;
Categorías:SQL Etiquetas: , ,
Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.