DESARROLLO DE APLICACIONES EN ENTORNOS DE 4ª GENERACIÓN Y CON HERRAMIENTAS CASE E J E R C I C I OS
SELECT ... FROM WHERE. .. a) Ejemp los de consultas simples con opera dore s a ritméti cos y rela ciona le s.
1.- Selecionar los empleados con salario superior a 2000 SELECT APELLIDO FROM EMPLE WHERE SALARIO> 2000;
2.- Selecionar los empleados con salario superior a 2000 y con el trabajo de DIRECTOR SELECT APELLIDO FROM EMPLE WHERE SALARIO> 2000 AND OFICIO LIKE ‘DIR ECTOR’;
3.- Selecionar el nº de departamento que tengan distinto nombre SELECT DISTINCT DNOMBRE, DEPT_NO FROM D EPART;
4.- Selecionar todos los empleados del departamento nº 20 y ademas debe de estar ordenado por el apellido y tienen que aparecer en la consulta el nº del empleado, el apellido, el oficio y el nº de departa mento. SELECT EMP_NO, APELLIDO, OFICIO, DEPT_NO FROM EMPLE WHERE DEPT_NO = 20 ORDER BY AP ELLIDO;
5.- Empleados cuyo oficio sea analista, ordenado por el número del empleado. SELECT APELLIDO FROM EMPLE WHERE OFICIO LIKE ‘ANALISTA’ ORDER BY EMP_NO;
6.- Seleccionar las filas del departamento 10 cuyo oficio sea analista. La consulta debe de estar ordenada descendentemente por el apellido y tambien descendentemente por el nº empleado. SELECT DEPT_NO FROM EMPLE WHERE (OFICIO LIKE 'AN ALIST A' AND ORDER BY EMP_NO DESC , ORDER BY APELLIDO D ESC);
7.- Seleccionar los nombres de los alumnos y su nota de la tabla notas_alumnos. SELECT NOMBRE_ALUMNO, NOTAS FROM NOTAS_ALUMNOS;
SELECT ... FROM WHERE. .. b) Ejemp los de c onsultas simples con opera dor es aritméticos y rela ciona les.
8.- Seleccionar los nombres de los alumnos y su nota de la tabla notas_alumnos siendo scott el propietario de la tabla. SELECT NOMBRE_ALUMNO, NOTAS FROM SCO TT.NOT AS_ ALUM N OS ;
9.-Seleccionar la nota_media de los alumnos de la tabla notas_alumnos. SELECT NOMBRE_ALUMNO, (N OTA1 +N OTA2 +N OTA3 )/3 FROM N OTAS_ ALUMNOS;
10.- Seleccionar los alumnos que de la nota1 hayan sacado un 7 y que la media de las 3 notas sea mayor que 6 SELECT NOTA_ALUMNO FROM NOTAS_ALUMNOS WHERE NOTA1=7 AN D (N OTA1 +N OTA 2+N OT A3 )/3>6;
11.- Seleccionar los nombres de los alumnos y su nota siendo esta mayor o igual a 5 y su curso primero. SELECT NOMBRE_ALUMNO , NOTAS FROM NOTAS_ALUMNOS WHERE NOTA >=5 AND CURSO LIKE 'PRIMERO';
12.- Seleccionar los empelados que sean analistas SELECT APELLIDO FROM EMPLE WHERE OFICIO LIKE 'AN ALIST A';
13.- Seleccionar los empleados cuyo apellido tenga una A en la 2 posición. ª
SELECT APELLIDO FROM EMPLE WHERE APELLIDOLIKE '_A%';
14.- Seleccionar el apellido de los empleados cuyo oficio sea vendedor,analista o empleado SELECT APELLIDO FROM EMPLE WHERE OFICIOLIKE 'VE N DEDOR ' OR OFICIO LIKE 'ANALISTA' OR OFICIO LIKE 'EMPLEADO';
15.- Seleccionar el apellido, el salario y el nº de departamento de los empleados cuyo salario sea mayor o igual a 125000 en los departamentos 10 o 20. SELECT APELLIDO, SALARIO, DEPT_NO FROM EMPLE WHERE SALARIO > 125000 AND
DEPT_NO IN (10,20);
SELECT ... FROM WHERE. .. c .- Ejemp los de consultas simples con opera dores a ritméticos y relaciona les.
16.- Seleccionar la lista de empleados indicando para cada uno su apellido, oficio, fecha de alta y el salario con un aumento del 16%. SELECT APELLIDO, OFICIO, FECHA_ALT, SALARIO* 1.16 FROM EMPLE;
17.- De cada departamento saber el nombre y la localidad. SELECT DNOMBRE , LOC FROM DEP ART;
18.- seleccionar los departamentos agrupados por el nº departamento. SELECT * FROM DEPART GROUP BY DEPART_NO ASC;
19.- seleccionar los empleados ordenados alfabeticamente por el nombre y por el oficio. SELECT * FROM EMPLE ORDER BY APELLIDO ASC, ORDER BY OFICIO ASC;
20.- seleccionar los empleados que no tienen comision. SELECT APELLIDO FROM EMPLE WHERE COMISION = 0;
21.- Seleccionar los empleados de apellido muñoz. SELECT APELLIDO FROM EMPLE WHERE APELLIDOLIKE 'MUÑOZ';
22.- Seleccionar los departamentos cuyo nombre acabe en 'on' SELECT DNAME FROM DEPART WHERE DNAME LIKE '%ON';
23.- Seleccionar los empleados cuyo nº de departamento no sea ni 30, ni 20 ni 40. a) SELECT APELLIDO FROM EMPLE WHERE DEPT_NO<>30, DEPT_NO<>20, DEPT_N O<>40; b) SELECT APELLIDO FROM EMPLE WHERE DEPT_NO NOT IN(30,20,40)
24.- Seleccionar los departamentos cuya localidad no sea ni madrid ni bilbao. SELECT DNOME FROM DEPART WHERE LOC LIKE NOT 'MADRID ',LOC LIKE NOT 'BILBAO');
25.- Seleccionar los empleados cuya fecha de alta este entre entre el 8/9/61 y el 27/2/82. SELECT APELLIDO FROM EMPLE WHERE FECHA_ALT BETWEEN'8/9/61' AND '27/2/82');
26.- seleccionar los departamentos que terminen en n y tengan una o en su interior. SELECT DNOMBRE FROM DEPART WHERE DNOMBRE LIKE '%O %N' OR DNOMBRE LIKE 'O%N';
27.- Seleccionar los empleados que tengan una A en el apellido y una V en su oficio en la posicion que sea. SELECT APELLIDO FROM EMPLE WHERE APELLIDO LIKE '% A%' AND POBLACION LIKE '% V%';
28.- seleccionar los empelados cuyo salario sea mayor de 20000 y menor que 50000. SELECT APELLIDO FROM EMPLE WHERE SALARIO>20000 AND SALARIO < 50000;
SELECT ... FROM WHERE. ..IN (SELECT... a) Ejemp los de subconsultas con opera dores aritm éti cos y relaciona les.
1.- Seleccionar el apellido de los empleados que trabajen en madrid o barcelona. SELECT APELLIDO FROM EMPLE WHERE DEPT_NO IN (SELECT DEPT_NO FROM DEPA RT WHERE LOC IN ('MADRID','BARCELON A') );
2.- Seleccionar el apellido y el oficio de todos los empleados del departamento 20 cuyo trabajo sea identico al de los empleados del departamento ventas. SELECT APELLIDO,OFICIO FROM EMPLE WHERE DEPT_NO=20 AND OFICIO IN( SELECT OFICIO FROM EMPLE WHERE DEPT_NO =(SELECT DEPT_NO FROM DEPART WHERE D N OMBRE='V EN TAS')) ;
3.- Obtener el departamento de los empleados con el mismo oficio y el salario de 'Gil'. SELECT APELL IDO, SALARIO FROM EMPLE WHERE (OFICIO,SALARIO)= (SELECT OFI CIO, SALARIO FROM EMPLE WHERE AP ELLIDO='GI L');
4.- Obtener el nombre de alumno, su asignatura y su nota. SELECT APENOM, NOMBRE, NOTA FROM ALUMNOS WHERE ALUM NOS. D N I =NOTAS.DNI AND NOTAS.COD= ASIGNATUR AS.C OD ;
5.- Nombre de los alumnos matriculados en FOL. SELECT APENOM FROM ALUMNOS,ASIGNATURAS, NOTAS WHERE ALUMNOS.DNI = NOTAS.DNI AND NOTAS.COD = ASIGNATURAS.COD AND N OMBRE='FOL';
SELECT ... FROM WHERE. ..IN(S ELECT... b) Ejemp los de subconsultas con opera dore s aritméti cos y relaciona les.
1.- seleccionar de la tabla emple aquellas filas cuyo apellido empiece por A y el oficio tenga una e en cualquier posición. SELECT APELLIDO,OFICIO FROM EM PLE WHERE APELLIDO LIKE '%A' OR OFICIO LIKE '%E%';
2.- Seleccionar el apellido, el oficio y la localidad de los departamentos donde trabajan los analistas. SELECT APELLIDO,OFICIO,LOC FROM EMPLE,DEPART WHERE OFICIO='AN ALISTA';
3.- Mostrar los empleados (nombre, oficio, salario y fecha de alta) que desempeñen el mismo oficio que JIMENEZ o que tengan el salario mayor o igual a FERNANDEZ. SELECT APELLIDO,OFICIO,SALARIO,FECHA_ALT FROM EMPLE WHERE OFICIO = (SELECT OFICIO FROM EMPLE WHERE APELLIDO = 'JIMENEZ') OR SALARIO >= (SELECT SALARI O FROM EMPLE WHERE APELLIDO = 'FERN AN DEZ ');
4.- Mostar por pantalla el nombre, el oficio y el salario de los empleados del departamento de FERNANDEZ que tengan su mismo salario. SELECT APELLIDO,SALARIO,OFICIO FROM EMPLE WHERE (DEPT_ NO, SALARIO) IN (SELECT DEPT_NO,SALARIO FROM EMPLE WHERE APELLIDO = 'FERN AND EZ' );
5.- Presentar los nombres y oficios de los empleados que tienen el mismo trabajo que JIMEN EZ. SELECT APELLIDO,OFICIO FROM EMPLE WHERE= (SELECT OFICIO FROM EMPLE WHERE APELLIDO = 'JIMEN EZ ');
TABLA LIBRERÍA
6.- Visualizar el tema, estante y ejemplares de las filas de libreria de ejemplares comprendidos entre 8 y 15. SELECT * FROM LIBRERIA WHERE EJEMPLARES BETWEEN 8 AND 15;
7.- Visualizar las columnas tema, estante y ejemplares de las filas cuyo estante no este comprendido entre la B y la D. SELECT * FROM LIBRERIA WHERE ESTANTE NOT BETWEEN
'B' AND 'D';
8.- Visualizar con una sola orden select todos los temas de libreria cuyo numero de ejemplares sea inferior a los que hay en medicina. SELECT TEMA FROM LIBRERIA WHERE EJMEPLARES <(SELECT EJEMPLARES LIBRERIA WHERE TENA LIKE 'MEDICINA ');
FROM
9.- Visualizar los temas de libreria cuyos numeros de ejemplares no este entre 15 y 20 , ambos incluidos. SELECT TEMA FROM LIBRERIA WHERE EJEMPLARES NOT BETWEEN
(15) AND (20);
SELECT ... FROM WHERE. ..IN(S ELECT... c) Ejemp los de subconsultas con operadore s aritméti cos y rela ciona le s.
10.- Visualizar todas las asignaturas que contengan en su interior tres letras 'o' y tengan alumnos matriculados en madrid. SELECT NOMBRE FROM ASIGNATURAS, ALUMNOS,NOTAS WHERE NOMBRE LIKE '%O%O% O%' AND POBLA ='MAD RID' AND ALUMNOS.COD= NOTAS.COD AND NOTAS .D N I=ALUMN OS.DN I;
11.- Visualizar los nombres de los alumnos que tengan una nota entre 7 y 8 en la asignatura de FOL. SELECT APENOM,NOMBRE,NOTA FROM ALUMNOS ,ASIGN ATURAS,N OTA SWHER E NOMBRE ='FOL' AND NOTA BETWEEN 7 AND 8 AND ALUMNOS.COD=NOTAS .COD AND N OTAS .DN I=ALUMNOS. DN I;
12.- Visualizar los nombres de asignaturas que no tengan susp ensos. SELECT NOMBRE FROM ASIGNATURAS, NOTAS WHERE NOTA BETWEEN5 AND 10;
13.- Visualizar los nombres de alumnos de madrid que tengan alguna asignatura suspendida. SELECT APENOM,DIREC,NOTA FROM ALUMNOS , NOTAS WHERE DIR EC=' MADRI D' AND NOTA BETWEEN 3 AND 4;
14.- Mostrar los nombres de alumnos que tengan la misma nota que tiene "Díaz Fernández, María" en FOL en alguna asignatura. SELECT APENOM FROM ALUMNOS WHERE DNI IN (SELECT DNI FROM N OTAS WHERE NOTA = (SELECT NOTA FROM NOTAS WHERE DNI = (SELECT DNI FROM ALUMNOS WHERE APENOM = 'DÍAZ FERNÁNDE Z. MARÍA') AND COD = (SELECT COD FROM ASIGN ATUR AS WHERE NOMBRE = 'FOL')));
SELECT ... FROM ...GROUP BY...H A VIN G... a) Ejemp los de consultas simples con opera dore s a ritméti cos y rela ciona le s.
1) Visualizar los departamentos en los que el salario medio es mayor o igual que la media de todos los salarios; SELECT DEPT_NO, AVG(SALARIO) FROM EMPLE GROUP BY DEPT_NO HAVIN G AVG(SALARIO) >= (SELECT AVG(SALARIO) FROM EMPLE);
2) A partir de la tabla emple, visualizar el número de venderores del departamentos 'VENTAS'; SELECT COUNT(*) FROM EMPLE WHERE DEPT_NO = (SELECT DEPT_NO FROM DEP ART WHERE DNOMBRE ='VENTAS' AND OFICIO = 'VE N DEDOR ');
3) Partiendo de la tabla EMPLE, visualizar por cada oficio de los empleados del departamento 'VENTAS' la suma de salarios. SELECT SUM(SALARIO), OFICIO FROM EMPLE WHERE DEPT_NO IN (SELECT DEP T_NO FROM DEPART WHERE DNOMBRE LIKE 'VENTAS') GROUP BY OFICIO;
4) seleccionar aquellos pedidos de la tabla EMPLE cuyo salario sea igual a la media de su salario en su departa mento. SELECT APELL IDO, SALARIO FROM EMPLE WHERE (SALARIO,DEPT_NO) IN (SELECT AVG(SALARIO),DEPT_NO FROM EMPLE GROUP BY DEP T_NO);
5) A partir de la tabla emple, visualizar el numero de empleados de cada departamento cuyo oficio sea 'EMPLEADO' SELECT DEPT_NO,COUNT(*) FROM EMPLE WHERE OFICIO LIKE 'EMPLEADO' GROUP BY DE PT_N O;
6) Desde la tabla EMPLE, visualizar el departamento que tenga más empleados cuyo oficio sea 'EMPLEADO' SELECT DEPT_NO, COUNT(*) FROM EMPLE WHERE OFICIO LIKE 'EMPLEADO' GROUP BY DEPT_NO HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM EMPLE WHERE OFICIO LIKE 'EMPLEADO'GROUP BY DEP T_NO);
7) A partir de las tablas EMPLE y DEPART, visualizar el número de departamento y el nombre de departamento que tenga más empleados cuyo oficio sea 'EMPLEADO' SELECT DEPT_NO,DNOMBRE FROM DEPART WHERE DEP T_NO=(SE LECT DEPT_NO FROM EMPLE WHERE OFICIO= 'EMPLEADO' GROUP BY DEPT_NO HAVING CO UN T(*)= (SELE CT MAX(COUNT(*)) FROM EM PLE WHERE OFICIO ='EMPLEADO ' GROUP BY DEP T_NO));
8) Buscar los departamentos que tienen más de dos personas trabajando en la misma profesión SELECT DEPT_NO,COUNT(*) FROM EMPLE GROUP BY DEPT_NO,OFICIO HAVING COUNT(*)>2;
9) Dada la tabla LIBRERIA, visualizar por cada estante la suma de los ejemplares. SELECT ESTANTE, SUM(EJEMPLARES) FROM LIBRERIA GROUP BY ESTANTE;
10) Visualizar el estante con más ejemplares de la tabla libreria. SELECT ESTAN TE,SUM(EJEMPLARES) FROM LIBRERIA GROUP BY ESTANTE HAVING SU M(EJEMPLARES)=( SELECT MAX(SU M(EJEMPLARES)) FROM LIBRERIA GROUP BY ESTAN TE);
SELECT ... FROM ...GROUP BY...H A VIN G... b) Ejemp los de c onsultas simples con opera dor es aritméticos y rela ciona les.
11) Visualizar los nombres de los alumnos de la tabla ALUM que aparezcan en alguna de estas tablas: NUEVOS y ANTIGUO S. SELECT NOMBRE FROM ALUM INTERSECT (SELECT NOMBRE FROM NUEVOS UNION SELECT NOMBRE FROM ANTIGUOS);
12) Escribir las distintas formas en que se puede poner la consulta anterior llegando al mismo resultado SELECT NOMBRE FROM ALUM WHERE NOMBRE IN(SELECT NOMBRE FROM N UEVO) UNION SELECT NOMBRE FROM ANTIGUO); SELECT NOMBRE FROM ALUM WHERE NOMBRE IN (SELECT NOMBRE FROM N UEVO) OR NOMBRE IN (SELECT NOMBRE FROM A N TIGUO);
13) Visualizar los nombres de los alumnos de la tabla alum que aparezcan en estas dos tablas: antiguos y nuevos SELECT NOMBRE FROM ALUM INTERSECT SELECTNOMBRE FROM ANTIGUOS INTERSECT SELECTNOMBRE FROM N UEVOS;
14) Escribir las distintas formas en que se puede poner la consulta anterior llegando al mismo resultado. SELECT NOMBRE FROM ALUM WHERE NOMBRE IN(SELECT NOMBRE FROM NUEVOS)AND NOMBRE IN(SELECT NOMBRE FROM ANTIGUOS);
15) Visualizar aquellos nombres de la tabla alum que no esten en la tabla antiguos ni en la tabla nuevos SELECT NOMBRE FROM ALUM MINUS SELECT NOMBRE FROM ANTIGUOS MINUS SELECT NOMBRE FROM N UEVOS;
16) Realizar una consulta en la que aparezca por cada centro y en cada especialidad el numero de profesores. Si el centro no tiene profesores debe aparecer un 0 en la columna de profesores. SELECT NOMBRE,ESPECIALIDAD,COUNT(*) FROM CENTROS,PROFESORES GROUP BY COD_CEN TRO ,ES PE CIALID AD; SELECT NOMBRE,ESPECIALIDAD,COUNT(DNI) FROM CENTROS,PROFESORES GROUP BY COD_CENTRO HAVING (CENTROS.CO D_CENTRO = PROFESORES.C OD _CEN T RO );
17) Obtener la especialidad con menos empleados SELECT ESPECIALIDAD FROM PROFESORES WHERE APELLIDOS= (SELECT MAX(APELLIDOS) FROM PROFESORES);
SELECT ... FROM ...GROUP BY...H A VIN G... c) Ejemp los de consult as simples con opera dores a ritméti cos y relacionales.
1.- mostrar el oficio y media de salarios de aquellos empleados cuya media de salario sea mayor que 200000 SELECT AVG(SALARIO),OFICIO FROM EMPLE GROUP BY OFICIO HAVING AV G(S ALARIO) >200000;
2.- mostrar el nombre y la comision de aquellos empleados que tengan una comision mayor que la de sanchez SELECT APELLIDO,COMISION FROM EMPLE WHERE COMISION > (SELECT CO MISION FROM EMPLE WHERE APELLIDO LIKE 'SÁN CHEZ ');
3.- mostrar el nombre salario y nº de departamento de aquellos empleados que ganan el salario maximo de su departamento SELECT APELLIDO,SALARIO, DEPT_NO FROM EMPLE WHERE SALARIO IN (SELECT MAX(SALARIO) FROM EMPLE GROUP BY DEPT_N O);
4.- mostrar el nombre del departamento que tanga más empleados cuyo oficio sea presidente SELECT DNOMBRE FROM DEPART GROUP BY DNOMBRE HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM EMPLE WHERE OFI CIO LIKE 'PRESIDENTE' GROUP BY DEP T_N O);
5.- mostrar el número de directores de la tabla emple que sean dep departamento producción SELECT COUNT(OFICIO) FROM EMPLE WHERE OFICIO = 'DIR ECTOR ' AND DEPT_NO = (SELECT DEPT_NO FROM DEPART WHERE DNOMBRE = 'PRODUCCIÓN');
SELECT ... FROM WHERE. ..FUNCIO N a) Ejemp los de consultas con funciones numéricas y de caract eres.
2.- A partir de la tabla emple, visualizar cuantos apellidos de los empleados empiez an por la letra ‘A’ SELECT COUNT (APELLIDO) EMPLE WHERE APELLIDOLIKE ‘A%’;
3.- Dada la tabla emple, obtener el sueldo medio, el número de comisiones no nulas , el máximo sueldo y el minimo sueldo de los empleados del departamento 30.Emplear el formato adecuado para la salida y las cantidades adecuadas. SELECT AVG(SALARIO), COUNT(COMISION), MAX(SALARIO), MIN(SALARIO) FROM EMPLE WHERE DEPT_NO= 30;
4.- Contar las filas de librería cuyo tema tenga por lo menos una ‘A’. SELECT COUNT (TEMA) FROM LIBRERÍA WHERE TEMA
LIKE ‘%A% ’;
5.- Visualizar los temas con mayor número de ejemplares de la tabla librería y que tengan al menos una ‘E’ (pueden ser un tema o varios). SELECT TEMA FROM LIBRERIA WHERE EJEMPLARES =(SELECT MAX(EJEMPLARES) FRO M LIBRERÍA )AND TEMA LIKE (‘%E%’);
6.- Visualizar el número de estantes diferen tes que hay en la tabla librería. SELECT COUNT (DISTINCT ESTANTE) FROM LIBRERÍA;
7.- Visualizar el número de estantes distintos que hay en la tabla librería de aquellos temas que contienen al menos una ‘E’. SELECT COUNT (DISTINCT ESTANTE) FROM LIBRERIA WHERE TEMA LIKE (‘%E%’ );
8.- ¿Qué sentencia select se debe ejecutar para tener el siguiente resultado? SELECT RPAD(LTRIM (RTRIM(TITULO ,’.’),’”’),4 5,’-1’)FR OM LIBROS;
9.- Visualizar los titulos de la tabla MISTEXTOS sin los caracteres punto y comillas, y en minusculas de dos formas conocidas. SELECT LOWER (LTRIM (RTRIM(TITULO ,’.”’ ),’”’) FROM MISTE XTOS;
10.- Escribir una sentencia select que visualice dos columnas, una con el autor y o tra con el apellido del au tor. SELECT AUTOR,SUBSTR(AUTOR,0,INSTR(AUTOR,',',1)-1) FROM
LIBROS;
11.- Escribir la sentencia select que visualice las columnas de autor y otra columna con el nombre del autor (sin el apellido) de la tabla libros. SELECT AUTOR,SUBSTR(AUTOR,INS TR(AUTOR,',',1 )+1) FROM LIBROS;
SELECT ... FROM WHERE. ..FUNCIO N b) Ejemp los de consulta s con funciones numéricas y de caract eres.
12.- A partir de la tabla libros, realizar una sentencia select que visualice en una columna, primero el nombre del autor y luego el apellido. SELECT SUBSTR(AUTOR,INSTR(AUTOR,' ,',1)+1), SUB STR(AUTOR,0,INS TR(AUTOR ,',',1)-1) FROM LIBROS;
13.- A partir de la tabla libros , realizar una sentecia select que visualice los titulos ordenados por su numero de caracteres. SELECT TITULO FROM LIBROS ORDER BY LENGTH( TITULO);
14.- Realizar una select que obtenga el siguiente resultado: SELECT NOMBRE, FECHANAC, TO_CHAR(FECHANAC, ‘”Nacio el”dd” de ”month” de ”y yyy ’) from nacimie ntos;
15.- Dada la tabla librería, hacer una sentencia select que visualice el tema, el ultimo carácter del tema que no sea blanco y el número de caracteres de tema (sin contar los blancos de la derecha) ordenados por tema. SELECT TEMA, SUBSTR(TEMA,INSTR(TEM A,’ ’)-1),INSTR(TEM A,’ ’)-1 FROM LIBRERIA ORDER BY T EM A;
16.- a partir de la tabla nacimientos, visualizar en una columna el nombre seguido de su fecha de nacimiento formateada (quitar blancos del nombre). SELECT NOMBRE,TO_CHAR(FECHANAC,’DA Y,DD MONTH
YYYY’)
FROM N ACIMIENTOS;
17.- Convertir la cadena ‘01051998’ a fecha y visualizar su nombre de mes en mayúsculas. SELECT UPPER(TO_CHAR(TO_DAT E(‘0 105199 8’),’ MONTH ’)) FROM DUAL ;
18.- A partir de la tabla emple, obtener el apellido de los empleados que lleven más de 19 años trabajando. SELECT APELLIDO FROM EMPLE WHERE 2004-TO_N UMBER( TO_CHAR(FECHA_A LT,’YYYY’) ) >19;
19.- Seleccionar el apellido de los empleados de la tabla emple que lleven mas de 18 años trabajando en el en el departamento ‘ventas’. SELECT APELLIDO FROM EMPLE WHERE 2004 – TO_NUMBER (TO_CH AR (FECHA_A LT,’YYYY’))>19 AND DEPT_NO = (SELECT DEPT_NO FROM DEPART WHERE DNOMBRE = ‘VEN TAS’);
20.- Visualizar el apellido, el salario y el número de departamento de aquellos empleados de la tabla emple cuyo salario sea el mayor de su departa men to. SELECT APELL IDO, SALARIO , DEPT_NO FROM EMPLE E WHERE SALARIO IN (SELECT MAX(SALARIO) FROM EMPLE DEP T_NO=E. DEP T_NO);
21.- Visualizar el apellido, el salario y el número de departamento de aquellos empleados de la tabla emple cuyo salario supere a la media en su d epartamento. SELECT APELLIDO, SALARIO, DEPT_NO FROM EMPLE E WHERE SALARIO > (SELECT AVG(SALARIO) FROM EMPLE WHERE DEP T_NO=E.D EPT _N O);
SELECT ... FROM WHERE. ..FUNCIO N c) Ejemp los de consultas con funciones numéricas y de caract eres.
1.- visualizar los departamentos con mayor número de empleados y que tengan un salario mayor que 20000. SELECT DNOMBRE FROM DEPART WHERE DEPT_NO = (SELECT DEPT_NO FROM EM PLE WHERE EMP_NO = (SELECT MAX(EMP_NO) FROM EMPLE WHERE SALARIO = (SELECT SALARIO FROM EMPLE WHERE SALARIO > 20000 )));
2.- visualizar los titulos de la tabla libros entre los caracteres * y en minuscula.Ejemplo: *la colmena* SELECT LOWER (CONCAT ('*',CONCAT (TITULO,'*'))) FROM LIBROS;
3.- visualizar los libros que comiencen por 'L' y sean de la editorial 'Planeta'. SELECT TITULO FROM LIBROS WHERE TITULO LIKE 'L%' AND EDITORIAL = 'PLAN ET A';
4.- Si hoy es 22/03/2010 del lunes que dia sera el lunes de la semana que viene SELECT NEXT_DAY (SYSDATE,'SÁBADO') FROM DUAL;
5.- Visualizar los autores que no sean de la editorial 'planet a' SELECT AUTOR FROM LIBROS WHERE EDITORIAL NOT IN'PLA N ETA';
6.- a partir de la tabla mis textos realizar una select para que aparezcan los autores ordenados por el numero de caracteres de la columna titulo. SELECT AUTOR FROM MIS TEXTOS ORDER BY LENGHT
(TITULO);
7.- seleccionar los empleados que hayan trabajado más de 5 años y sumarles 10000 pts a su salario. SELECT APELL IDO, SALARIO + 10000 FROM EMPLE WHERE MONTHS_BET WEEN (SYSDATE, FECHA_A LT)/12>5;
8.- obtener la fecha de hoy con el siguiente formato (en mayusculas) hoy es v eintidós de marzo de dosmil diez. SELECT TO_CHAR (SYSDATE, "HOY ES"DAY, DD "DE" MONTHS "DE" YEAR) FROM DUAL;
9.- escribir una consultilla que visualice en una sola columna el nombre y apellido de las personas de la tabla nacimiento y la fecha de nacimiento en un formato específico. SELECT CONCAT (CONCAT(NOMBRE,APELLIDO), TO_CHAR (FECHANAC, 'DAY DD MONTH YY')) FROM N ACIMIENTO;
SELECT ... FROM WHERE. ..FUNCIO N d) Ejemp los de c onsultas con funciones de fec has.
1.- Dada la tabla emple, sumar dos meses a la fecha de alta. SELECT ADD_MONTHS(FECHA_ALT,2) FROM EMPLE;
2.- Dada la tabla emple, restar dos meses a la fecha de alta. SELECT MONTHS_BETWEE N(FECHA_ALT,FECH A_ALT-2) FROM EM PLE;
3.- Obtener de la tabla emple el último dia del mes para cada uno de las fechas de alta. SELECT LAST_DAY(FECHA_ALT) FROM EMPLE;
4.- Obtener la diferencia de meses que hay entre la fecha 5/5/200 y la fecha 1/1/2000. SELECT MONTHS_BET WE EN (5/ 5/200 * 1/1/2000) FROM DUAL;
5. - Calcular vuestra edad. SELECT MONTHS_BET WE EN (17/5/ 1981,SYSD ATE)/12 FROM DUAL;
6.- Si hoy es domingo 25 de julio de 1999 (fecha del sistema "sysdate"). ¿Qué fecha será el proximo jueves? SELECT NEWT_DAY(SYSD ATE,'JUEVES')FR OM D UA L;
7.- A partir de la tabla emple, obtener la fecha de alta (columna FECHA_ALT) fromateada, de manera que aparezca el nombre del mes con todas sus letras (month), el numero del dia del mes(dd) y el año (yyyy). SELECT T O_CH AR(FECHA_A LT,'MONTH,D DYYYY') FROM EM PLE;
8.- A partir de la tabla emple, obtener la fecha de lata de forma que aparezca el nombre del mes con tres letras (month), el numero del dia del año (ddd), el ultimo digito del año(y)y los tres digitos ultimos del año(yyy). SELECT TO_CHAR(SECHA_ALT,'MON DDD
Y YYY')
FRO M EM PLE;
9.- Obtener la fecha de hoy con el siguiente fromato: Hoy es NOMBRE_DIA,DIA_MES de NOMBRE_MES de AÑO. SELECT TO_CHA R(SYSDATE,'"HOY ES " DAY "," DD "DE" MONTH "DE" YYYY') FROM DUA L;
10.- Visualizar la suma de salarios de la tabla emple de manera formateada, tal que aparezca el simbolo de la moneda local, el punto para los miles y la coma para los decimales. SELECT TO_CHAR(SUM(SALARIO),'L9,999,999.99') FROM EM PLE;
11.- Convertir la cadena 01012001 a tipo date. SELECT TO_DATE('01012001')FROM D UAL ;
12.- Obtener el nombre del mes a partir de la cadena '01012001'. Antes hay que convertir la cadena a tipo fecha. SELECT TO_CHAR(TO_DATE('01012001'),MONTH) FROM
DUAL;
13.- Obtener el nombre del dia, el nombre del me, el dia y el año en ingles a partir de la fecha '12121997' SELECT T O_CH AR(TO_DATE('12121997')),'DAY,MONTH DD YYYY'FROM DUAL;
CREATE TABLE... a) Crea ción de tablas depart y emp le.
REM ******** TABLA DEPART: *********** DROP TABLE DEPART cascade constraints; CREATE TABLE DEPART ( DEP T_N O NUMBER(2) NOT N ULL, DNOMBRE VARCH AR2(1 4), LOC VARCHAR2(14) ) ;
REM ******** TABLA EMPLE: ************* ALTER SESSION SET N LS_DATE_ FOR MAT='DD/MM/YYYY'; DROP TABLE EMPLE cascade constraints; CREATE TABLE EMPLE ( EMP_NO NUMBER(4) NOT N ULL, APELLIDO VARCHAR2(10) , OFI CIO VARCHAR2(10) , DIR NUMBER(4) , FECHA_ALT DATE , SALARIO N UMBER(10), COMISION N UMBER(10), DEPT_NO NUMBER(2) NOT NULL) ;
CO MMIT;
CREATE TABLE... b) Creación de tabl as alumnos, asignaturas y notas.
REM ******** TABLA NOTAS_ALUMNOS: *********** Drop table notas_alumnos cascadeconstraints; create table notas_alumnos (
NOMBRE_ALUMNO VARCHAR2(25) NOT NUL L , nota1 number(2), nota2 number(2), nota3 number(2) );
REM ******** TABLA LIBRERIA: ************* Drop table LIBRERIA cascade constraints; create table LIBRERIA (TEMA CHAR(15) NOT NULL , ESTANTE CH AR(1), EJEMPLARES N UMBER(2) );
REM ******** TABLAS ALUMNOS, ASIGNATURAS, NOTAS: *********** DROP TABLE ALUMNOS cascade constraints; CREATE TABLE ALUM N OS ( DNI VARCHAR2(10) NOT N ULL, APENOM VARCHAR2(30), DIREC VARCHAR2(30), POBLA VARCHAR2(15), TELEF VARCHAR2(10) ); DROP TABLE ASIGNATURAS cascade constraints; CREATE TABLE ASIGN ATUR AS ( COD NUMBER(2) NOT N ULL, NOMBRE VARCHAR2(25) ); DROP TABLE NOTAS cascade constraints; CREATE TABLE N OTAS ( DNI VARCHAR2(10) NOT N ULL, COD NUMBER(2) NOT N ULL, NOTA NU MBER(2) ); commit ;
CREATE TABLE... c) Creación de tablas nomb res, mistextos, libros y nacimientos.
REM ********** TABLA NOMBRES: *********** DROP TABLE NOMBRES cascade constraints; CREATE TABLE N OMBRES ( NOMBRE VARCHAR2(15), EDAD N UMBER(2) );
CO MMIT;
REM ********** TABLA MISTEXTOS: ************* DROP TABLE MISTEXTOS cascade constraints; CREATE TABLE MISTEXTOS ( TITULO VARCHAR2(32), AUTOR VARCH AR2(22), EDITORIAL VARCHAR2(15), PAGINA N UMBER(3) );
CO MMIT; REM ********** TABLA LIBROS: ************* DROP TABLE LIBROS cascade constraints; CREATE TABLE LIBROS ( TITULO VARCHAR2(32), AUTOR VARCH AR2(22), EDITORIAL VARCHAR2(15), PAGINA N UMBER(3) );
REM ********** TABLA N ACI MIENTOS:****************** DROP TABLE NACIMIENTOS cascade constraints; ALTER SESSION SET N LS_DATE_ FOR MAT='DD/MM/YYYY'; CREATE TABLE NACIMIENTOS ( NOMBRE CHAR(15), APELLIDO CHAR(15), FECHANAC DATE, EDAD N UMBER );
CO MMIT;
CREATE TABLE... d) Creación de tablas paraleer y leidos, alum, nuevos y antiguos, perso nal profesores y cent ros.
REM ************ TABLAS PARALEER Y LEIDOS ************** DROP TABLE PARALEER cascade constraints; CREATE TABLE PA RALEER ( COD_LIBRO N UMBER(3), NOMBRE_LIBRO VARCHAR2(40) ); DROP TABLE LEIDOS cascade constraints; ALTER SESSION SET N LS_DATE_ FOR MAT='DD/MM/YYYY';
CREATE TABLE LEID OS ( COD_LIBRO N UMBER(3), FECHA DATE );
CO MMIT; REM ************ TABLA ALUM, NUEVOS, ANTIGUOS ************ DROP TABLE ALUM cascade constraints; DROP TABLE NUEVOS cascade constraints; DROP TABLE ANTIGUOS cascade constraints; Create TABLE ALUM ( NOMBRE VARCHAR2(20), EDAD N UMBER(2), LOCALIDAD V ARCH AR2(15) ); Create TABLE N UEVOS ( NOMBRE VARCHAR2(20), EDAD N UMBER(2), LOCALIDAD V ARCH AR2(15) );
Create TABLE ANTIGUOS ( NOMBRE VARCHAR2(20), EDAD N UMBER(2), LOCALIDAD V ARCHAR2(15) );
CO MMIT; REM ************ TABLA PERSONAL, PROFESORES, CENTROS *********** DROP TABLE PERSONAL cascade constraints; CREATE TABLE PERSONA L ( COD_CENTRO NUMBER(4) NOT N ULL, DNI N UMBER(10), APELLIDOS VARCH AR2(3 0), FUNCION VARCHAR2(15), SALARIO NUMBER (10) ); DROP TABLE PROFESORES cascade constraints; CREATE TABLE PROFESORES ( COD_CENTRO NUMBER(4) NOT N ULL, DNI NUMBER(10), AP ELLIDOS VARCHAR2(3 0), ESP ECIALID AD VARCHAR2(16) );
DROP TABLE CENTROS cascade constraints; CREATE TABLE CENTROS ( COD_CENTRO NUMBER(4) NOT N ULL, TIPO_CENTRO CH AR(1), NOMBRE VARCHAR2(30), DIRECCION VARCHAR2(26), TELEFONO VARCH AR2(1 0), NUM_PLAZAS N UMBER(4) ); commit ;
REM ************ FIN ****************
CREATE TABLE... e) Crea ción de tablas emple30 y coches, artículos, tiendas, fabrican tes, pedidos y ven tas.
REM *************** TABLA EMPLE30: ************** DROP TABLE EMPLE30 cascade constraints; CREATE TABLE EMPLE30 ( EMP_NO NUMBER(4) NOT N ULL, APELLIDO VARCHAR(10) , OFICIO VARCHAR(10) , DIR NUMBER(4) , FECHA_ALT DATE , SALARIO N UMBER(10), COMISION N UMBER(10), DEPT_NO NUMBER(2) NOT NULL) ;
REM *************** TABLA COCHES: ************* DROP TABLE COCHES cascade constraints; CREATE TABLE COCHES (NOMBRE VARCHAR2(15)) TABLESPACE USER_D ATA; INSERT INTO COCHES VALUES('SEA T');
CO MMIT; REM *************** TABLAS ARTICULOS, TIENDAS, FABRICANTES, PEDIDOSy VEN TAS:******** DROP TABLE ARTICULOS cascade constraints; DROP TABLE TIENDAS cascade constraints; DRO P TABLE FABRICANTES cascade constraints; DROP TABLE PEDIDOS cascade constraints; DROP TABLE VENTAS cascade constraints; CREATE TABLE ARTICULO S( ARTIC ULO VARCHAR2(20)NOT N ULL, COD_FABRICANTE NUMBER(3) NOT N ULL, PESO NUMBER(3) NOT NULL , CATEGORIA VARCHAR2(10) NOT N ULL, PRECIO_VENTA NUMBER (4), PRECIO_COSTO NUMBER (4), EXISTENCIAS NUMBER (5) );
CREATE TABLE FABRICANTES( COD_FABRICANTE NUMBER(3) NOT N ULL, NOMBRE VARCHAR2(15), PAIS VARCHAR2(15)) ; CREATE TABLE TIE N DAS( NIF VARCHAR2(10) NOT N ULL, NOMBRE VARCHAR2(20), DIRECCIÓN VARCHAR2(20), POBLACIÓN VARCHAR2( 20), PROVINCIA VARCH AR2(20), CODPOSTAL NUMBER(5) ) ; CREATE TABLE PEDID OS( NIF VARCHAR2(10) NOT NULL, ARTICULO VARCHAR2(20) NOT N ULL, COD_FABRICANTE NUMBER(3) NOT N ULL, PESO NUMBER(3) NOT N ULL, CATEGORIA VARCHAR2(10) NOT N ULL, FECHA_PEDIDO DATE NOT N ULL, UNIDADES_PEDIDAS NU MBER(4) ); ALTER SESSION SET N LS_DATE_ FOR MAT='DD/MM/YYYY';
CREATE TABLE V EN TAS( NIF VARCHAR2(10) NOT NULL, ARTICULO VARCHAR2(20) NOT N ULL, COD_FABRICANTE NUMBER(3) NOT N ULL, PESO NUMBER(3) NOT N ULL, CATEGORIA VARCHAR2(10) NOT N ULL, FECHA_VENTA DATE NOT N ULL, UNIDADES_VENDIDAS N UMBER(4) );
commit ;
REM *************** FIN *************************
INSERT INTO .... VALUES.... a) Ejemp los de inserción de datos en tablas.
INSERT INTO DEPART VALUES (10,'CONTABILIDAD','SEV ILLA') ; INSERT INTO DEPART VALUES (20,'IN VESTIGACI ÓON ','MADRI D'); INSERT INTO DEPART VALUES (30,'VE N TAS','B ARCELON A') ; INSERT INTO DEPART VALUES (40,'PRODUCCIÓN','BILBAO' ); INSERT INTO EMPLE VALUES (7369,'SÁNCHEZ ','EMPLEADO',79 02,'17/12/1980', 104000,NULL,2 0); INSERT INTO EMPLE VALUES (7499,'ARRO YO','VEN DEDOR ',7698,'20/02/1980', 208000,39000,30); INSERT INTO EMPLE VALUES (7521,'SALA','V END EDOR ',7698,'22/02/ 1981', 162500,65000,30); INSERT INTO EMPLE VALUES (7566,'JIMÉN EZ','DIRECTOR',7839,'02/04/1981', 386750,NULL,2 0); insert into N OTAS _A LUMN OS VALUES insert into NOTAS_ALUMNOS VALUES insert into NOTAS_ALUMNOS VALUES insert into NOTAS_ALUMNOS VALUES insert into NOTAS_ALUMNOS VALUES
('Alcalde García, M. Luisa',5,5,5); ('Ben ito Martín, Luis',7,6,8); ('Casas Martínez, Manuel',7,5,5); ('Corregidor Sánchez, Ana',6,9,8); ('Díaz Sánchez, María',N ULL,NULL,7 );
INSERT INTO ASIGNATURAS VALUES (1,'Prog. Leng. Estr.'); INSERT INTO ASIGNATURAS VALUES (2,'Sist. Informáticos'); INSERT INTO ASIGNATURAS VALUES (3,'Análisis'); INSERT INTO ASIGNATURAS VALUES (4,'FOL');
INSERT INTO ALUMNOS VALUES ('12344345','Alcalde García, Elena', 'C /Las Matas, 24','Madrid ','917766545'); INSERT INTO ALUMNOS VALUES ('4448242','Cerrato Vela, Luis', 'C/Mina 28 - 3A', 'Madrid','916566545'); INSERT INTO ALUMNOS VALUES ('56882942','Díaz Fernández, María', 'C /Lu is Vives 25', 'Móstoles','915577545'); INSERT INTO NOTAS VALUES(' 12344345', 1,6); INSERT INTO NOTAS VALUES(' 12344345', 2,5); INSERT INTO NOTAS VALUES(' 12344345', 3,6); INSERT INTO NOMBRES VALUES('PE DRO ', 17); INSERT INTO NOMBRES VALUES('JUAN', 17); INSERT INTO NOMBRES VALUES('MARÍA', 16);
DROP TABLE... CASCADE CO NSTRAIN TS b) Ejemp los de eliminación de tablas.
REM*** EJEMPLOS DE ELIMINACION DE TABLAS REM===================================== DROP TABLE DEPART cascade constraints; DROP TABLE EMPLE cascade constraints; Drop table notas_alumnos cascadeconstraints; Drop table LIBRERIA cascade constraints; DROP TABLE ALUMNOS cascade constraints; DROP TABLE ASIGNATURAS cascade constraints; DROP TABLE NOTAS cascade constraints;
DROP TABLE NOMBRES cascade constraints; DROP TABLE MISTEXTOS cascade constraints; DROP TABLE LIBROS cascade constraints; DROP TABLE NACIMIENTOS cascade constraints; DROP TABLE PARALEER cascade constraints; DROP TABLE LEIDOS cascade constraints; DROP TABLE ALUM cascade constraints; DROP TABLE NUEVOS cascade constraints; DROP TABLE ANTIGUOS cascade constraints; DROP TABLE PERSONAL cascade constraints; DROP TABLE PROFESORES cascade constraints; DROP TABLE CENTROS cascade constraints; DROP TABLE EMPLE30 cascade constraints; DROP TABLE COCHES cascade constraints;
INSERT, DROP Y UPDATE MEDIANTE SELECT c) Ejemp los de Inser ción, eliminación y actualización mediante consultas.
1- Dadas las tablas ALUM y NUEVOS, insertar en la tabla ALUM los nuevos alumnos. INSERT INTO ALUM (SELECT * FROM NUEVOS MINUS SELECT* FROM ALUM );
2- Borrar de la tabla ALUM los ANTIGUOS alumnos. DELETE FROM ALUM WHERE NOMBRE IN (SELECT NOMBRE FROM ANTIGUOS) ;
3- Insertar a un empleado de apellido 'SAAVEDRA' con número 2000. La fecha de alta será la actual, el SALARIO será el mismo salario de 'SALA' mas el 20 por 100 y el resto de datos serán los mismos que los datos de 'SALA'. INSERT INTO EMPLE (SELECT2000,'SAAVEDRA',OFICIO,DIR,SYSDATE,SALARIO + SALARIO*0.2,COMISION,DEPT_NO FROM EMPLE WHERE APELLIDO LIKE 'SALA') ;
4- Modificar el número de departamentos de 'SAAVEDRA'. El nuevo departamento será el departamento donde hay más empleados cuyo oficio sea 'EMPLEADO' UPDATE EMPLE SET DEPT_NO = (SELECT DEPT_NO FROM EMPLE WHERE OFICIO LIKE 'EMPLEADO' GROUP BY DEPT_NO HAVING COUNT (*) = (SELECT MAX(COUNT(*)) FROM EMPLE WHERE OFICIO LIKE 'EMPLEADO' GROUP BY DEPT_NO)) WHERE APELLIDO LIKE 'SAAVEDRA';
5- Borrar todos los departamentos de la tabla DEPART para los cuales no existan empleados en emple. DELETE FROM DEPART WHERE DEPT_NO IN (SELECT DEPT_NO FROM DEPART MIN US SELECT DEPT_NO FROM EM PLE);
6- Modificar el número de plazas con un valor igual a la mitad en aquellos centros con menos de dos profesores . UPDATE CENTROS SET NUM_PLAZAS = NUM_PLAZAS/2 WHERE COD_CENTRO IN (SELECT COD_CENTRO FROM PROFESORES GROUP BY COD_CEN TRO HAVING COUNT (*)<2);
7- Eliminar los centros que no tengan personal. DELETE CENTROS WHER E COD_CENTRO IN (SELECT COD_CENTRO FROM CENTRO S MINUS SELECT COD_CENTRO FR OM PERSO NAL);
8- Añadir un profesor en el centro o en los centros cuyo número de administrativos sea 2en la especialidad de 'IDIOMA', con DNI 8790055 y de nombre 'Clara Salas'. INSERT INTO PROFESORES SELECT DISTINCT COD _CENTRO , 8790055,'SALAS,CLAR A','IDIOMA ' FROM PERSONAL WH ERE COD_CENTRO IN (SELECT COD_CENTRO FROM PERSONAL WHERE FUNCION = 'A DMINISTRAT IVO' GROUP BY COD_CENTRO HAVING COUNT(*) = 1);
9- Borrar al personal que esté en centros de menos de 300 plazas y con menos de dos profesores. DELETE PERSONAL WHERE COD_CENTRO IN (SELECT COD_CENTRO FROM CENTROS WHERE N UM_PLAZAS<300) AND COD_CENTRO IN (SELECT COD_CENTRO FROM PROFESORES GROUP BY COD_CENTRO HAVING COUNT(*)<2);
10- Borrar a los profesores que estén en la tabla PROFESORES y que no estén en la tabal PERS ONAL. DELETE PROFESORES WHERE DNI NOT IN (SELECT DNI FROM PERSO NAL);
11- Dar de alta un artículo de 'Primera' categoría para los fabricantes de 'Francia' y abastecer con 5 unidades de ese artículo a todas las tiendas y en la fecha de hoy. INSERT INTO ARTICULOS SELECT 'YOGUR FRESA ', COD_FABRICANTE, 4, 'PRIMERA', 120,100,190 FROM FABRICANTES WHERE PAIS = 'FRANCIA'; SELECT NIF, 'YOG UR FRESA', COD_FABRICANTE, 4, 'PRIMERA', SYSDATE, 5 FROM TIENDAS, FABRICANTES WHERE PAIS LIKE 'FR AN CIA';
12- Insertar un pedido de 20 unidades en la tienda '111-A' con el artículo que mayor número de ventas haya realizado. INSERT INTO PEDIDOS SELECT DISTINCT '1111-A', ARTICULO, COD_FABRICANTE, PESO, CATEGORIA, SYSDATE, 20 FROM VEN TAS WHERE (ARTICULO, COD_FABRICANTE,PESO,CATEGORIA) IN (SELECT ARTICULO,COD_FABRICANTE,PESO,CATEGORIA FROM VENTAS GROUP BY ARTICULO, COD_FABRICANTE, PESO, CATEGORIA HAVING COUNT (*) =(SELECT MAX(COUNT(*)) FROM VENTAS GROUP BY ARTIC ULO, COD_FABRICANTE, PESO, CATEGO RIA)) ;
13- Dar de alta una tienda en la provincia de 'MADRID' y abasteerla con 20 unidades de cada uno de los artículos existentes. INSERT INTO TIENDAS VALUES ('1010-C', 'LA CESTA', 'C /JUAN MAZO 30', 'ALCALA','M ADRI D',28809); INSERT INTO PEDIDOS SELECT'1010-C',ARTICULO, COD_FABRICANTE, PE SO, CATEGORIA, SYSDATE, 20 FROM ARTIC ULO S;
14- Dar de alta dos tiendas en la provincia de 'SEVILLA' y abastecerlas con 30 unidades de artículos de la marca 'GALLO' INSERT INTO TIENDAS VALUES('4501-B','LA ECO N ÓMICA','C\SEVILLAN AS 130','DOS H EM AN AS','SE VILLA', 44003); INSERT INTO TIENDAS VALUES('4501- B','COMETIBLES PE TER','C \SEVILLAN AS 130','DOS H EM AN AS','SE VILLA', 44009);
INSERT INTO PEDIDOS SELECT NIF,ARTICULO,A.COD_FABRICANTE,PESO,CATEGORIA,SYSDATE,30 FROM TIENDAS,ARTICULOS A, FABRICANTES F WHERE PROVINCIA='SEVILL A' AND F.NOMBRE='G ALLO' AN D A.C OD _FA BRI CANT E=F.C OD _FA BRI CANTE;
15- Realizar una venta para todas las tiendas de 'TOLEDO' de 10 unidades en los artículos de 'Primera' categoría. INSERT INTO VENTAS SE LECT NIF,ARTICULO,COD_FABRICANTE,PESO,CATEGORIA,SYSDATE,10 FROM TIENDAS,ARTICULOS WHERE PROVINCIA='TOLEDO' AND CATEGO RI A='P RI MERA';
16- Para aquellos artículos de los que se hayan vendido más de 30 unidades, realizar un pedido de 10 unidades para la tienda con NIF '5555-B' con la fecha actual. INSERT INTO PEDIDOS SELECT DISTINCT '5555B',ARTICU LO,COD_FABRICANTE,PE SO,CATEGORIA, SYSDATE,10FROM VENTAS WHERE (ARTICULO,COD_FABRICANTE,PESO,CATEGORIA)IN (SELECT ARTICULO,COD_FABRICANTE,PESO,CATEGORIA FROM VENTAS GROUP BY ARTIC U LO, COD_FABRICANTE,PESO,CATEGORIA HAVING SUM(UNID ADES_VEN DIDAS)<30);
17- Cambiar los datos de la tienda con NIF '1111-A' igualandolos a los de la tienda N IF '2222-A'. UPDATE TIEN DAS SET(N OMBRE,DIR ECCIÓ N,POB LACIÓN,PRO VINCIA,C OD POST AL)=(SELECT N OMBRE,DIRECCIÓ N,POB LACIÓN,PRO VINCIA, CODPOSTAL FROM TIENDAS WHERE PA IS='ITALIA');
18- Cambiar todos los artículos de 'Primera' categoria a 'Segunda' categoria del pais 'ITALIA'. UPDATE ARTICULOS SET CATEGO RI A='S egunda' WHERE CATEGO RI A='P rimera' AND COD_FABRICANTE = (SELECT COD_FABRICANTE FROM FABRICANTES WHERE PAIS='ITALIA');
19- Modificar aquellos pedidos en los que la cantidad pedida sea superior a las existencias del artículo, asignando el 20% de las existencias a la cantidad que se ha pedido. UPDATE PEDIDOS P SET UNID ADES_PED IDAS= (SELECT EXISTENCIAS*0.2 FROM ARTICULOS WHERE ARTICULO=P. ARTICULO AND COD_FABRICANTE=P. COD_FABRICANTE AN D PESO=P. PESO AND CATEGORIA=P. CATEGORIA ) WHERE UNIDADES_PED IDAS > (SELECT EXISTENCIAS FROM ARTICULOS WHERE ARTICULO=P. ARTICULO AND COD_FABRICANTE=P. COD_FABRICANTE AN D PESO=P. PESO AND CATEGORIA=P. CATEGOR IA);
20- Eliminar aquellas tiendas que no han realizado ventas. DELETE T IEN DAS WHERE NIF NOT IN (SELECT DISTINCT NIF FROM VEN TAS);
21- Eliminar los artículos que no hayan tenido ni compras ni ventas. DELETE ARTIC ULO S WHERE (ARTICULO, COD_FABRICANTE, PESO, CATEGOR IA) NOT IN (SELECT DISTINCT ARTICULO, COD_FABRICANTE, PESO, CATEGORIA FROM VEN TAS) AND (ARTICULO, COD_FABRICANTE, PESO, CATEGO RI A) NOT IN (SELECT DISTINCT ARTICULO, COD_FABRICANTE, PESO, CATEGORIA FROM PED IDOS) ;
22- Borrar los pedidos de 'Primera' categoria cuyo pais de procedencia sea 'BELGICA'. DELETE PED IDOS WHERE (ARTICULO, COD_FABRICANTE, PESO, CATEGOR IA) IN (SELECT ARTICULO, COD_FABRICANTE, PESO, CATEGOR IA FROM ARTICULOS WHERE COD_FA BRI CANT E= (SELECT COD_FABRICANTE FROM FABRICANTES WHERE PAIS ='B ELGIC A') ) AND CATEGORIA='P rimera';
23- Borrar los pedidos que no tengan tienda. DELETE PEDIDOS WHERE NIF NOT IN(SELECT NIF FROMTIENDAS);
24- Restar uno a las unidades de los últimos pedidos de la tienda con NIF '5555-B'. UPDATE PEDIDOS SET UN IDADES_PED IDAS= UN IDAD ES_PED IDAS-1 WHERE N IF='5555-B' AND FECHA_P EDIDO= (SELECT MAX(FECHA_PEDIDO) FROM PEDIDOS WHERE NIF='5555-B');
CREATE TABLE...,ALTER TABLE... d) Ejemp los de creación de tablas y modificación de las tablas.
1. Crear la tabla fabricantes con las siguientes condiciones: - la clave primaria es cod_fabricante - las columnas nombre y pais han de almacenarse en mayuculas CREATE TABLE FABRICANTES( COD_FABRICANTE NUMBER(3) CONSTRAINT PK_FA PRIMARY KEY, NOMBRE VARCHAR2(15) CONSTRAINT CK_NO CH ECK( N OMBRE=UPP ER(N OMBRE)), PAIS VARCH AR2(15) CONSTRAINT CK_PA CH ECK( PAIS=UPP ER(PA IS)) );
2. Crear la tabla articulos con las siguientes condiciones: - la clave primaria esta formada por las columnas: articulo, cod_fabricante,pero y categoria. - cod_fabricante es clave ajena que referencia a la tabla fabricantes. - precio_venta, precio_costo han de ser >0. - categoria ha de ser 'primera','segunda ' o 'tercera'. CREATE TABLE ARTICULO S( ARTIC ULO VARCHAR2(20)NOT N ULL, COD_FABRICANTE NUMBER(3) NOT N ULL, PESO NUMBER(3) NOT N ULL CONSTRAINT CK1_AR CHECK (PE SO>0), CATEGORIA VARCHAR2(10) NOT N ULL, PRECIO_VENTA NUMBER (4) CONSTRAINT CK2_AR CHECK (PRECIO_V EN TA>0 ), PRECIO_COSTO NUMBER (4) CONSTRAINT CK3_AR CHECK (PRECIO_COSTO>0), EXISTENCIAS NUMBER (5), CONSTRAINT PK_ART PRIMARY KEY (ARTICULO, COD_FABRICANTE, PESO, CATEGO RIA), CONSTRAINT FK_ARFA FOREIGN KEY (COD_FABRICANTE) REFERENCES FABRI CANTES, CONSTRAINT CK_CAT CHECK(CATEGORIA IN('Primera','Segunda', 'Tercera ')) );
3. Crear la tabla tiendas con las siguientes condiciones: - la clave primaria es NIF - provincia ha de almacenarse en mayuculas. CREATE TABLE TIE N DAS( NIF VARCHAR2(10) NOT NULL CONSTRAINT PK_TI PRIMARY KEY, NOMBRE VARCHAR2(20), DIRECCIÓN VARCHAR2(20), POBLACIÓN VARCHAR2( 20), PROVINCIA VARCH AR2(20) CONSTRAINT CK_PRO CH ECK( PROVIN CIA=UPPE R(PROVIN CIA)), CODPOSTAL NU MBER(5) );
4. Crear la tabla pedidos con las siguientes condiciones: - la clave primaria esta formada por las columnas:NIF, articulo,cod_fabricante,peso,categoria y fecha de pedido. - cod_febricante es clave ajena que referencia a la tabla fabricantes. - unidades pedidas ha de ser: >0 - categoria ha de ser 'primera','segunda ' o 'tercera'. - las columnas articulo, cod_fabricante, peso y categoria son clave ajena y referencia a la tabla articulos.Realizar un borrado en cascada. - NIf es clave ajena y refencia a la tabla tiendas. CREATE TABLE PEDID OS( NIF VARCHAR2(10) NOT N ULL CONSTRAINT FK_PETI REFERENCES TIEND AS, ARTICULO VARCHAR2(20) NOT N ULL, COD_FABRICANTE NUMBER(3) NOT N ULL CONSTRAINT FK_PEFA REFERENCES FABRIC ANTES, PESO NUMBER(3) NOT N ULL, CATEGORIA VARCHAR2(10) NOT N ULL, FECHA_PEDIDO DATE NOT N ULL, UNIDADES_PEDIDAS NU MBER(4) CONSTRAINT CK_PEUP CHECK (UNID ADES_PED IDAS> 0), CONSTRAINT PK_PED PRIMARY KEY (NIF,ARTICULO, COD_FABRICANTE, PESO, CATEGORIA, FECHA_P EDID O), CONSTRAINT CK_CATPE CHECK(CATEGORIA IN('Primera ','Segunda','Tercer a')), CONSTRAINT FK_PEAR FOREIGN KEY (ARTICULO, COD_FABRICANTE, PESO, CATEGORIA) REFERENCES ARTIC ULO S ON DELETE CASCADE );
5. Crear la tabla ventas con las siguientes condiciones: - la clave primaria esta formada por la por las columnas: nif, articulo,cod_fabricantepeso,categoria y fecha venta. - cod fabricante es clave ajena que referencia a la tabla fabricantes. - unidades_vendidas ha de ser >0 - categoria ha de ser 'primera','segunda ' o 'tercera'. - Las columnas articulo, cod_fabricante, peso, y categoria son clave ajena y referencia de la tabla articulos. Realizar un borrado en cascada. - NIF es clave ajena y referencia a la tabla tiendas CREATE TABLE V EN TAS( NIF VARCHAR2(10) NOT N ULL CONSTRAINT FK_VETI REFERENCES TIENDAS, ARTICULO VARCHAR2(20) NOT N ULL, COD_FABRICANTE NUMBER(3) NOT N ULL CONSTRAINT FK_VEFA REFERENCES FABRIC ANTES, PESO NUMBER(3) NOT N ULL, CATEGORIA VARCHAR2(10) NOT N ULL CONSTRAINT CK_CATVE CHECK(CATEGORIA IN('Primera ','Segunda','Tercer a')), FECHA_VENTA DATE NOT N ULL, UNIDADES_VENDIDAS N UMBER(4) CONSTRAINT CK_VEUV CHECK (UNID ADES_ VEND IDAS>0), CONSTRAINT PK_VEN PRIMARY KEY (NIF,ARTICULO, COD_FABRICANTE, PESO, CATEGORIA, FECHA_V EN TA), CONSTRAINT FK_VEAR FOREIGN KEY (ARTICULO, COD_FABRICANTE, PESO, CATEGORIA) REFERENCES ARTIC ULO S ON DELETE CASCADE );
6. Añadir una restricción a la tabla tiendas para que el nombre de la tienda sea de tipo título ALTER TABLE TIENDAS ADD CO N STRAI N T NOMBRETITU CHECK (N OMBRE=INITCAP(N OMBRE));
7. Visualizar las constraint definidas para las tablas anterio res. SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINT S WHERE TABLE_NAME IN('ARTICULOS','TIENDAS', 'FABRICANTES', 'PEDIDOS' ,'VE N TAS');
8. Modificar las columnas de las tablas pedidos y ventas para que las unidades vendidas y las unidades pedidas puedan almacenar cantidades numericas de 6 dígitos. ALTER TABLE PEDIDOS MODIFY (UNIDADES_PEDIDAS NU MBER(6)); ALTER TABLE VENTAS MODIFY (UNIDADES_VENDIDAS NU MBER(6));
9. Impedir que se den mas den de alta mas tiendas en la provindia de toledo. ALTER TABL E TIENDAS ADD CONSTRAINT CK_T IEN TO CHECK (PROVINCIA !='T OLEDO');
10. Añadir a las tablas pedidos y ventas una nueva columna para que almacenen el pvp del artículo. ALTER TABLE PEDIDOS ADD(PVP NU MBER(4)); ALTER TABLE V ENTAS ADD(PVP N UMBER(4)); TABLAS PERSONAL, PROFESORES y CENTROS:
11. Añadir a la tabla profesores una columna llamada cod_asig con dos posiciones numéricas. ALTER TABLE PROFESORES ADD(COD_ASIG NU MBER(2));
12. Crear la tabla tasig con las siguientes columnas: cod_asig numerico, 2 posiciones y nom_asig cadena de 20 caracteres. CREATE TABLE TASIG ( COD_ASIG N UMBER(2), NOM_ASIG VARCHAR2(20) );
13. Añadir la restricción de clave primaria a la columna cod_asig de la tabla tasig. ALTER TABLE TASIG ADD CONSTRAINT PK_TASIG PRIMARY KEY (COD _ASIG) ;
14. Añadir la restricción de la clave ajena a la columna cod_asig de la tabla profesores. ALTER TABLE PROFESOR ES ADD CONSTRAINT FK_TASIG FOREIGN KEY (COD_ASIG) REFERENCES TASIG;
15. Visualizar los nombres de las constraint y las columnas afectadas de las tablas tasig y profesores. SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN _NAM E FROM USER_CONS_COLUMNS WHERE TABLE_NAME IN('TAS IG','PROFESOR ES');
16. Cambiar los nombres de las tablas profesores y llamarlas profes RENAME PROFESORES TO PRO FES;
17. Borrar la tabla tasig DROP TABLE TASIG CASCADE CO N STRAI N TS;
18. Devolver la tabla profesores a su situación inicial CREATE TABLE PROFESORES AS SELECT COD_CENTRO, DNI, APELLIDOS, ESP ECIALID AD FROM PROFES;
CREATE VIEW ...AS... a) Ejemp los de creación de vistas mediante consulta s así como de sinónimos.
1º) Crear una vista que se llame conserjes que contenga el nombre del centro y el nombre de sus conserjes. CREATE VIEW CONSERJES AS SELECT NOMBRE,APELLIDOS FROM CENTROS , PERSO N AL WHERE FUNCIO N='CON SERJE' AND CEN TRO S.C OD _CEN TRO =PERSONA L.C OD _CEN TRO ;
2º) Crear un sinonimo asociado a la vista creada anteriormente llamado conser CREATE SYNONYM CONSER FOR CONSERJES;
CREATE VIEW ...AS... b) Ejemp los de op eraciones sobre vistas.
1º) Crear una vista que se llame dep30 que contiene el apellido, oficio y el salario de los empleados de la tabla emple del departamento 30. CREATE VIEW DEP30 AS SELECT APELLIDO, OFICIO ,SALARIO FROM EMPLE WHERE DEP T_N O=30;
2º) Hacer una descripción de la vista creada anterio rmente. DESC DEP30;
3º) Hacer una consulta que muestre el contenido de la vista dep30. SELECT * FROM DEP30;
4º) Crear una vista o reemplazarla dando nombre a las columnas ape, ofi y sal: CREATE OR REPLACE VIEW DEP30 (APE, OFI , SAL) AS SELECT APELLIDO, OF ICIO ,SALARIO FROM EMPLE WHERE DEP T_NO=30;
5º) Hacer una consulta de las vistas creadas junto con sus textos: SELECT VIEW_NAME, TEXT FROM USER_VIEWS;
DROP VIEW ...UPDA TE...SELECT... c) Ejemp los borrados, inserciones y consultas sobre vistas.
1º) Borrar la vista dep30 DROP VIEW DEP30;
2º) consultar los apellidos de los vendedores dela vista dep30: SELECT APE FROM DEP30 WHERE OFI='VEND EDOR ';
3º) Modificar la vista dep30, modificamos el apellido 'MARTIN', almacenandolo en minucuslas y cambiamos el salario a 200000; UPDATE DEPT30 SET AP E='MARTIN ', SAL=200000 WHERE AP E='MARTIN ';
4º) Consultamos la vista y la tabla asocidad para comprobar los resultados. SELECT APE, SALFROM DEP30 WHERE AP E='MARTIN'; SELECT APELLIDO, SALARIO FROM EMPLE WHERE AP ELLIDO='MARTI N ';
5º) Crear una vista a partir de la tabla d epart CREATE VIEW VDEP AS SELECTDEPT_NO, DNOMBRE FROM DEP ART;
6º) insertar valores en la vista vdep. INSERT INTO VDEP VALUES (66,'DESARROLLO');
7º) borramos el departamento 66 y observamos como este departamento desaparece de la tabla depart. DELETE VDEP WHERE DEPT_NO = 66;
8º) A partir de las tablas depart y emple creamos una vista que contenga el emp_no, apellido, dept_no y dnombre. CREATE VIEW DEPT_EMP(EMP_NO, APELLIDO, DEPT_NO ,DN OMBRE) AS SELECT EMP_NO, APELLIDO, EMPLE.DEPT_NO, DNOMBRE FROM EMPLE , DEP ART WHERE EM PLE.D EP T_N O=DEP ART.D EP T_N O;
9º) Insertar una fila en la vista creada INSERT INTO DEPT_EMP VALUES(22 22,'SUELA', 20,'IN VESTIGACI ÓN');
LISTADO S 1.- Ejemp los de creación informes.
A partir de las tablas emple y depart, hacer un listado en el que se produzca una suma de salarios por localidad y dentro de la localidad una suma de salarios por oficios. - el titulo superios de la lista es: LISTADO DE LOS EMPLEADOS DE LA TABLA EMPLE - El titulo inferior del listado es:Con ruptura por departamento y por oficio calculando totales - La cabecera estara formada por los siguientes titulos: Localidad, apellidos, Nº de empleado, oficio, salario y comisión. ALTER SESSION SET N LS_N UMERI C_CH ARACTERS=',.'; REM Nombre : LISTADO3.SQL REM Descripción: Listado de Empleados. TTITLE 'LISTADO DE LOS EMPLEADOS DE LA TABLA EM PLE' skip 2 BTITLE 'Con ruptura por departamento y por oficio calculando totales' skip 2
COLUMN LOC HEADING 'Localidad' COLUMN APELLIDO HEADING 'Apellidos' COLUMN EMP_NO HEADING 'Nº de empleado' COLUMN OFICIO HEADING 'Oficio' COLUMN SALARIO HEADING 'Salario' FORMAT 9G999G999 COLUMN COMISION HEADING 'Comisión' FORMAT 9G999G999 BREAK ON LOC SKIP 2 ON OFICIO SKIP 1 COMPUTE SUM LABEL'Suma por loc.' OF SALARIO ON LOC COMPUTE SUM LABEL'Sum por ofi.' OF SALARIO ON oficio
SET LINESIZE 79 SET PAGESIZE 45 SET NEWPAGE 0
SP OOL LISTADO3.LST
SELECT LOC, APELLIDO, EMP_NO, OFICIO, SALARIO, CO MISIO N FROM EMPLE, DEP ART WHERE EM PLE.D EP T_N O=DEP ART.D EP T_N O ORDER BY LOC, OFICIO; TTITLE OFF BTITLE OFF CLEAR BREAKS CLEAR CO MPUTES CLEAR COLUMN S SP OOL OFF
FORMATEAR LISTADO S 2 .- Ejemp los de creación de inform es dando formatos.
Realizar un informe en el que aparezca por cada departamento el salario maximo, el minimo, el total junto con la media y el numero de empleados. - el titulo superios de la lista es: RESUMEN DEPARTAMEN TAL REM Nombre : LISTADO5.SQL REM Descripción: RESUMEN DEP ARTAM EN TAL SET HEADSEP | TTITLE CENTER 'RESUMEN DEPARTAMENTAL' skip 2
COLUMN DNOMBRE HEADING 'N ombre|Departamento' COLUMN MAXIMO HEADING 'Máximo|Salario' FORMAT 9,999,999 COLUMN MINIMO HEADING 'Mínimo|Salario' FORMAT 9, 999,999 COLUMN SUMA HEADING 'Suma|S alarios' FORMAT 9,999,999 COLUMN MEDIO HEADING 'Salario|Medio' FORMAT 9,999,999 COLUMN CUENTA HEADING 'Número|Empleados' FORMAT 9,999 SET LINESIZE 79 SET PAGESIZE 60 SET NEWPAGE 0
SP OOL LISTADO5.LST SELECT DNOMBRE, max(salario) MAXIMO, min(salario) MINIMO, sum(salario) SUMA, avg(salario) MEDIO, count(*) CU EN TA FROM EMPLE, DEP ART WHERE EM PLE.D EP T_N O=DEP ART.D EP T_N O GROUP BY D N OMBRE;
TTITLE OFF BTITLE OFF CLEAR BREAKS CLEAR CO MPUTES CLEAR COLUMN S SP OOL OFF
ACCEPT...PRO MPT 3 .- Ejemp losde creación de inform es introdu ciendo pa rá metros de ent rada pa ra generar el informe
Obtener el listado anterior formateando las salidas numericas de tal forma que el punto represente los miles y la coma los decimales . ALTER SESSION SET N LS_N UMERI C_CH ARACTERS=',.';
REM Nombre : LISTADO6.SQL REM Descripción: RESUMEN DEP ARTAM EN TAL SET HEADSEP | TTITLE CENTER 'RESUMEN DEPARTAMENTAL' skip 2
COLUMN DNOMBRE HEADING 'N ombre|Departamento' COLUMN MAXIMO HEADING 'Máximo|Salario' FORMAT 9G999G999D99 COLUMN MINIMO HEADING 'Mínimo|Salario' FORMAT 9G999G999D99 COLUMN SUMA HEADING 'Suma|S alarios' FORMAT 9G999G999D99 COLUMN MEDIO HEADING 'Salario|Medio' FORMAT 9G999G999D99 COLUMN CUENTA HEADING 'Número|Empleados' FORMAT 9G999 SET LINESIZE 90 SET PAGESIZE 60 SET NEWPAGE 0 SP OOL LISTADO6.LST SELECT DNOMBRE, max(salario) MAXIMO, min(salario) MINIMO, sum(salario) SUMA, avg(salario) MEDIO, count(*) CU EN TA FROM EMPLE, DEP ART WHERE EM PLE.D EP T_N O=DEP ART.D EP T_N O GROUP BY D N OMBRE; TTITLE OFF BTITLE OFF CLEAR CO MPUTES CLEAR COLUMN S SP OOL OFF
Hacer el mismo informe que se pide en el ejercicio 2, con la diferencia que nos pida introducir por teclado el nombre del departamento que se va a listar. No se han de obtener los calculos por report ni imprimir el titulo inferior. REM Nombre : LISTADO7.SQL REM Descripción: Listado de SALARIOS - INTRODUCIMOS DEP ARTAMEN TO SET HEADSEP | TTITLE CENTER 'LISTADO DE LOS SALARIOS DE LOS EMPLEADOS' skip 2 COLUMN DNOMBRE HEADING 'N ombre|Departamento' COLUMN APELLIDO HEADING 'Apellido' COLUMN SALARIO HEADING 'Salario' FORMAT 9, 999,999 BREAK ON DNOMBRE SKIP 1 COMPUTE SUM MAX MIN AVG OF SALARIO ON D N OMBRE SET LINESIZE 79 SET PAGESIZE 30 SET NEWPAGE 0 SP OOL LISTADO7.LST ACCEPT NOM PROMPT "Nombre de departamento a listar:" SELECT DNOMBRE, APELLIDO, SALARIO FROM EMPLE, DEP ART WHERE EMPLE.DEPT_NO=DEPART.DEPT_NO AND D N OMBRE='&NOM ' ORDER BY DN OMBRE; TTITLE OFF BTITLE OFF CLEAR BREAKS CLEAR CO MPUTES CLEAR COLUMN S SP OOL OFF
CREATE TABLESPA CE... a) Ejemp los de creación de tabl espa ces.
1º) Crear un tablespace de nombre COMPRAS asociandole un fichero en disco llamado COMPRAS.ORAde 5 Mb. CREATE TABLESPACE COMPRAS DATAFILE 'COMPRAS.ORA' SIZE 5M;
2º) Modificar el tablespace del ejercicio1 para que pueda autoextenderse automaticamente sin limite de espacio en el disco. ALTER TABLESPACE COMPRAS ADD DATAFILE 'COMPRAS1 .ORA' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIM ITE D;
CREATE USE R... b) Ejemp los de creación de usua rios.
3º) Crear un rol que tenga los siguientes privilegios, insert select en depart y emple, create session, create database link, create table y create view. CREATE ROLE UN_RO L; GRANT INSERT, SELECT ON DEPART TOUN _ROL; GRANT INSERT, SELECT ON EMPLE TOUN _ROL; GRANT CREATE SESSION, CREATE DATABASE LINK,CREATE TABLE, CREATE VIEW TO UN_RO L;
4º) Crea un usuario llamado 'COMPRADOR'. El tableespace por defecto es 'COMPRAS' y se le asigna un mega en el tableespace COMPRAS y 0 de cuota en el tablespace SYSTEM. El tablespace temporl sera TEMPORARY_DATA.Se le asigna un rol anterior. CREATE USER COMPRADOR IDENTIFIED BY CO MPRAD OR DEFAULT TABLESPACE COMPRAS TEMPORAR Y TABLESPACE TEMPORAR Y_DATA QUOTA 1M ON COMPRAS QUOTA 0 ON SYSTEM ; GRANT UN_RO L TO COMPRADOR;
GRANT... CONNECT... CREATE RO LE... c) Ejemp los de usuarios con derech os de administración, roles...
5º)Realiza la siguiente secuencia de instrucciones en el orden indicado: - Crear un usuario de la BD que tenga funciones de administrador CREATE USER ADMINISTRADOR IDENTIFIED BY ADMINISTRADOR ;
GRANT DBA TO AD MINISTRADOR ;
- conectarse con el nombre del usuario creado CONNECT ADMINISTRADOR @DAINT
- crear varias tablas en el propio esquema CREATE TABLE TABLA1 (N NU MBER(10)); CREATE TABLE TABLA2 (N NU MBER(10)); CREATE TABLE TABLA3 (N NU MBER(10));
- Crear 5 usuarios nuevos asignandoles un tablespace por defecto y cuota en (USU1,USU2, USU3...5). CREATE USER USU1 IDENTIFIED BY USU1 DEFAULT TABLESPACE CO MPRAS QUOTA 2M ON COMPRAS; CREATE USER USU2 IDENTIFIED BY USU2 DEFAULT TABLESPACE CO MPRAS QUOTA 2M ON COMPRAS; CREATE USER USU3 IDENTIFIED BY USU3 DEFAULT TABLESPACE CO MPRAS QUOTA 2M ON COMPRAS; CREATE USER USU4 IDENTIFIED BY USU4 DEFAULT TABLESPACE CO MPRAS QUOTA 2M ON COMPRAS; CREATE USER USU5 IDENTIFIED BY USU5 DEFAULT TABLESPACE CO MPRAS QUOTA 2M ON COMPRAS;
- Dar permiso al usuario USU1 solo para q pueda conectarse a la BD. GRANT CREATE SESSION TO USU1;
- Crear un rol q permita conectarse a la bd y hacer SELECT sobre algunas tablas. CREATE ROLE ROL_USU; GRANT CREATE SESSION TO ROL_USU; GRANT SELECT ON TABLA1 TO ROL_USU ; GRANT SELECT ON TABLA2 TO ROL_USU ; GRANT SELECT ON TABLA3 TO ROL_USU ;
- Conceder al USU4 privilegios sobre qalgunas tablas con la opción de poder concederselos a otros usuarios.
GRANT ROL_USU TO USU2, USU3; - Conceder al usuario USU5 cuatro privilegios del sistema, dos de ellos, con la opción de poder concederselos a otros usuarios. GRANT INSERT, DELETE ON TABLA1 TO USU4 WITH GRANT OPTIO N ; GRANT INSERT, DELETE ON TABLA2 TO USU4 WITH GRANT OPTIO N ;
- Conceder a todos los usuarios de la BD privilegios para q puedan modificar ciertas columnas de algunas tablas. GRANT CREATE ANY TABLE,CREATE USER TOUSU5 WITH ADMIN OPT IO N ; GRANT ALTER TABLESPACE, CREATE TABLESPACE TO USU5; GRANT UPDATE(N) ON TABLA1 TO PUB LIC; GRANT UPDATE(N) ON TABLA2 TO PUB LIC;
- Quitar a los usuarios USU3 y USU4 todos los privilegios q tenían asignados. REVOKE ROL_USU FROM USU3; REVOKE ALL ON TABLA1 FROM USU 4; REVOKE ALL ON TABLA2 FROM USU 4;
- Hacer q USU5 solo pueda conectarse en dos sesiones concurrentes a la vez. CREATE PROFILE LIM_SESION LIMIT SESSIONS_PER_USER 2; ALTER USER USU5 IDENTIFIED BY USU5 PROFILE LIM_SESION;
- Limitar el tiempo de conexión a la BD a 5 minutos a los usuarios USU2 y USU3. CREATE PROFILE TIEMPOLIMITE LIMIT CONNECT_TIME 5; ALTER USER USU2 IDENTIFIED BY USU2 PROFILE TIEMPOLIMITE;
ALTER USER USU3 IDENTIFIED BY USU3 PROFILE TIEMPOLIMITE;
DECLARE. ..BEGIN...END a) Ejemp los de creación de un bloque PL/SQ L.
1.- Escribir un bloque PL/SQL que escriba el texto ‘Hola’ SQL> BEGIN 2 DBMS_OUTPUT.PUT _LINE('H OLA') ; 3 END; 4/
SAVE... b) Ejemp los de como guardar un bloque PL/SQ L.
2.- Escribir un bloque PL/SQL que cuente el número de filas que hay en la tabla productos, deposita el resultado en la variable v_num, y visualiza su contenido. SQL>DECLAR E 2 v_num N UMBER; 3 BEGIN 4 SELECT count(*) INTO v_num 5 FROM productos; 6 DBMS_OUTPUT.PUT _LIN E(v _num); 7 END; 8/
START...,GE T...,RUN c) Ejemp los como ejecu ta r o arrancar un bloque PL/SQ L.
3.- Cargar y ejecutar el bloque guardado en el archivo PROG01.SQLde la unidad A. SQL> START A:\PROG 01.SQL O bien: SQL> GET A:\PROG 01.SQL SQL> RUN
CREATE OR REPLACE PROCED URE. .. 1.- Ejemp los de creación p rocedi m ientos.
1) Escribir un procedimiento que reciba dos números y visualice su suma. CREATE OR REPLACE PROCEDURE sumar_numeros ( num1 NUMBER, num2 NU MBER) IS suma NU MBER(6); BEGIN suma := num1 + num2; DBMS_OUTPUT.PUT_LINE('Suma: '|| suma); END s umar_numero s;
2) Codificar un procedimiento que reciba una cadena y la visualice al revés. CREATE OR REPLACE PROCEDURE cadena_reves( vcadena VARCHAR2) AS vcad_reves VARCH AR2(8 0); BEGIN FOR i IN REVERSE 1..LENGTH(vcadena) LOOP vcad_reves := vcad_reves || SUB STR(vcadena,i,1 ); END LOOP; DBMS_OUTPUT .PUT _LIN E(v cad_reves); END cadena_reves;
3) Escribir una función que reciba una fecha y devuelva el año, en nú mero, correspondiente a esa fecha. CREATE OR REPLACE FUNCTION anio ( fecha DATE) RETURN N UMBER AS v_anio N UMBER(4); BEGIN v_anio := TO_NUMBER(TO_CHAR(fecha, 'YYYY')); RETURN v_anio; END anio;
CREATE OR REPLACE FU NCTION.... 2 .- Ejemp los de creación de funciones.
4) Escribir un bloque PL/SQL que haga uso de la función anterior.
DECLAR E n NU MBER(4); BEGIN n := anio(SYSD ATE); DBMS_OUTPUT.PUT_LINE('AÑO : '|| n); END;
5) Dado el siguiente procedimiento: CREATE OR REPLACE PROCEDURE crear_depart ( v_num_dept depart.dept_no%TYPE , v_dnombre depart.dnombre%TYPE DEFAULT 'PRO VISION AL', v_loc depart.loc%TYPE DEFAULT ‘PROVISI ON AL’) IS BEGIN INSERT INTO depart VALUES (v_num_dept, v_dnombre, v_loc); END crear_depart;
Indicar cuáles de las siguientes llamadas son correctas y cuáles incorrectas, en este último caso escribir la llamada correcta usando la notación posicional (en los casos que se pueda): 1º. crear_depart; 2º. crear_depart(50); 3º. crear_depart('COMPRAS'); 4º. crear_depart(50,'COMPRAS'); 5º. crear_depart('COMPRAS', 50); 6º. crear_depart('COMPRAS', 'VA LENCI A') ; 7º. crear_depart(50, 'COMPRAS', 'V ALENCI A') ; 8º. crear_depart('COMPRAS' , 50, 'VA LENCIA') ; 9º. crear_depart('VA LEN CIA', ‘COMPRAS’); 10º. crear_depart('VA LEN CIA', 50); 1º Incorrecta: hay que pasar al menos el número dedepartamento. 2º Corre cta. 3º Incorrecta: hay que pasar también el número dedepartamento. 4º Corre cta.
5º Incorrecta: los argumentos están en orden inverso. Solución: crear_depart(50, 'COMPRAS'); 6º Incorrecta: hay que pasar también elnúmero. 7º Corre cta. 8º Incorrecta: el orden de los argumentos es incorrecto.Solución: crear_depart(50, 'COMPRAS', 'V ALENCIA' ); 9º Incorrecta: hay que pasar también el número dedepartamento. 10º Incorrecta: los argumentos están en ordeninverso.Solución:
crear_depart(50, NULL, 'VALENCI A') ;
6) Desarrollar una función que devuelva el número de años completos que hay entre dos fechas que se pasan como argumentos. CREATE OR REPLACE FUNCTION anios_dif ( fecha1 DATE, fecha2 DATE) RETURN N UMBER AS v_anios_dif N UMBER(6); BEGIN v_anios_dif := ABS(TRU N C(MONTH S_BET WEE N (fecha2,fecha1) / 12)); RETURN v_anios_dif; END anios_dif;
7) Escribir una función que, haciendo uso de la función anterior devuelva los trienios que hay entre dos fechas. (Un trienio son tres años completos). CREATE OR REPLACE FUNCTION trienios ( fecha1 DATE, fecha2 DATE) RETURN N UMBER AS v_trienios NU MBER(6); BEGIN v_trienios := TRU N C(anios_dif(fecha1,fecha2) / 3); RETURN v_t rienios; END;
PROCEDIMIENTOS Y FUNCIO NES 3 .- Ejemp los de proce dimientos y funciones.
8) Codificar un procedimiento que reciba una lista de hasta 5 números y visualice su suma. CREATE OR REPLACE PROCEDURE sumar_5numeros ( Num1 NUMBER DEFAULT 0, Num2 NUMBER DEFAULT 0, Num3 NUMBER DEFAULT 0, Num4 NUMBER DEFAULT 0, Num5 NUMBER DEFAULT 0) AS BEGIN DBMS_OUTPUT.PUT_LINE(Num1 + Num2 + Num3 + Num4 + Num5); END s umar_5numeros;
9) Escribir una función que devuelva solamente caracteres alfabéticos sustituyendo cualquier otro carácter por blancos a partir de una cadena que se pasará en la llam ada. CREATE OR REPLACE FUNCTION sust_por_blancos( cad VARCHAR2) RETURN VARCHAR2 AS nueva_cad VARCH AR2(3 0); car CHARACTER; BEGIN FOR i IN 1..LENGTH( cad) LOO P car:=SUB STR(cad,i,1); IF (ASCII(car) NOT BETWEEN 65 AND 90) AND (ASCII(car) NOT BETWEEN 97 AND 122) THEN car :=' '; END IF; nueva_cad := nueva_cad || car; END LOOP; RETURN nueva_cad; END s ust _por_blancos;
10) Implementar un procedimiento que reciba un importe y visualice el desglose del cambio en unidades monetarias de 1, 5, 10, 25, 50, 100, 200, 500, 1000, 2000, 5000 Ptas. en orden inverso al que aparecen aquí enumeradas. CREATE OR REPLACE PROCEDURE desglose_cambio( importe N UMBER) AS cambio NATURAL := imp orte; moneda NATURAL; v_uni_moneda N ATUR AL; BEGIN DBMS_OUTPUT.PUT_LINE('***** DESGLOSE DE: ' || importe ); WHILE cambio > 0 LOOP IF cambio >= 5000 THEN moneda := 5000; ELSIF cambio >= 2000 THEN moneda := 2000; ELSIF cambio >= 1000 THEN moneda := 1000; ELSIF cambio >= 500 THEN moneda := 500; ELSIF cambio >= 200 THEN moneda := 200; ELSIF cambio >= 100 THEN moneda := 100; ELSIF cambio >= 50 THEN moneda := 50; ELSIF cambio >= 25 THEN moneda := 25; ELSIF cambio >= 10 THEN moneda := 10; ELSIF cambio >= 5 THEN moneda := 5; ELSE moneda := 1; END IF; v_uni_moneda := TRUNC(cambio / moned a); DBMS_OUTPUT.PUT_LINE(v_uni_moneda || ' Unidades de: ' || moneda || ' Ptas. '); cambio := MOD(cambio, moneda); END LOOP; END desglose_cambio;
11) Codificar un procedimiento que permita borrar un empleado cuyo número se pasará en la llam ada. CREATE OR REPLACE PROCEDURE borrar_emple( num_emple emple.emp_no%TYPE ) AS BEGIN DELETE FROM emple WHERE emp_no = num_emple; END borrar_emple;
Nota: El procedimiento anterior devolverá el mensaje PL/SQL terminado con éxito >> aunque no exista el número y, por tanto, no se borre el empleado. Para evitarlo se puede escribir: << Procedimiento
CREATE OR REPLACE PROCEDURE borrar_emple( num_emple emple.emp_no%TYPE ) AS v_row ROWID ; BEGIN SELECT ROWID INTO v_row FROM emple WHERE emp_no = num_emple; DELETE FROM emple WHERE ROWID = v_row; END borrar_emple;
12) Escribir un procedimiento que modifique la localidad de un departamento. El procedimiento recibirá como parámetros el número del departamento y la localidad nueva. CREATE OR REPLACE PROCEDURE modificar_localidad( num_depart N UMBER, localidad VARCHAR2) AS BEGIN UPDATE depart SET loc = localidad WHERE dept_no = num_depart; END modificar_localidad;
Nota: Lo indicado en la nota del ejercicio anterior se puede aplicar también a este. 13) Visualizar todos los procedimientos y funciones del usuario almacenados en la base de datos y su situación (valid o invalid). SELECT OBJECT_NAME, OB JECT_T YPE , STATUS FROM USER_OB JECTS WHERE OB JECT_T YPE IN ('PROCEDUR E','FUNCTIO N ');
Nota: También se puede utilizar la vista ALL_OB JECTS.
CURSO R...IS... 1.- Ejemp los de creación de procedim ientos con cu rsores.
1) Desarrollar un procedimiento que visualice el apellido y la fecha de alta de todos los empleados ordenados por apellido. CREATE OR REPLACE PROCEDURE ver_emp le AS CURSOR c_emple IS SELECT APELLIDO, FECHA_A LT FROM EM PLE ORDER BY AP ELLIDO; v_apellido VARCHAR2(10); v_fecha DATE; BEGIN OPEN c_emple; FETCH c_emple into v_apellido, v_fecha; WHILE c_emple%FOUND LOOP D BMS_OUTPUT .PUT _LINE( v_apellido||' * '||v _fecha); FETCH c_emple into v_apellido,v_fecha; END LOOP; CLOSE c_emple; END ver_emple;
2) Codificar un procedimiento que muestre el nombre de cada departamento y el número de empleados que tiene. CREATE OR REPLACE PROCEDURE ver_emp le_depart AS CURSOR c_emple IS SELECT dnombre, CO UN T(emp _no) FROM emple e, depart d WHERE d.dept_no = e.dept_no(+) GROUP BY dnombre; v_dnombre depart.dnombre%TYPE; v_num_emple BINARY_IN TEGER; BEGIN OPEN c_emple; FETCH c_emple into v_dnombre, v_num_emple;WHILE c_emple%FOUND LOOP DBMS_OUTPUT .PUT _LIN E(v _dnombre||' * '||v _num_emple); c_emple into v_dnombre,v_num_emple; FETCH END LOOP; CLOSE c_emple; END ver_emple_depart;
3) Escribir un procedimiento que reciba una cadena y visualice el apellido y el número de empleado de todos los empleados cuyo apellido contenga la cadena especificada. Al finalizar visualizar el número de empleados mostrados. CREATE OR REPLACE PROCEDURE ver_emp le_apell( cadena VARCHAR2) AS cad VARCHAR2(10); CURSOR c_emple IS SELECT apellido, emp_no FROM emple WHERE apellido LIKE cad; vr_emple c_emple%ROWTY PE ; BEGIN cad :='%'||caden a||'% '; OPEN c_emple; FETCH c_emple INTO vr_emple; WHILE (c_emple%FOUND) LOOP DBMS_OUTPUT .PUT _LIN E(v r_emp le.emp_no||' * '||v r_emple.apellido); FETCH c_emple INTO vr_emple; END LOOP; DBMS_OUTPUT.PUT_LINE ('NUMERO DE EMPLEADOS: '|| c_emple%ROWC OUN T); CLOSE c_emple; END ver_emple_apell;
4) Escribir un programa que visualice el apellido y el salario de los cinco empleados que tienen el salario más alto. CREATE OR REPLACE PROCEDURE emp_5maxsal AS CURSOR c_emp IS SELECT apellido, salario FROM emp le ORDER BY salario D ESC; vr_emp c_emp%RO WT YPE ; i N UMBER; BEGIN i:=1; OPEN c_emp; FETCH c_emp INTO vr_emp; WHILE c_emp%FOUND AND i<=5 LOOP DBMS_OUTPUT.PUT_LINE(vr_emp.apellido ||' * '|| vr_emp.salario); FETCH c_emp INTO vr_emp; i:=I+1; END LOOP; CLOSE c_emp; END emp _5maxsal;
OPEN ...FETCH. .. 2 .- Ejemp los de como como recorrer un cu rsor.
5) Codificar un programa que visualice los dos empleados que ganan menos de cada oficio. CREATE OR REPLACE PROCEDURE emp_2minsal AS CURSOR c_emp IS SELECT apellido, oficio, salario FROM emp le ORDER BY oficio, salario; vr_emp c_emp%RO WT YPE ; oficio_ant EM PLE.OFIC IO% TYPE ; i N UMBER; BEGIN OPEN c_emp; oficio_ant:='*'; FETCH c_emp INTO vr_emp; WHILE c_emp%FOUND LOOP IF oficio_ant <> vr_emp.oficio THEN oficio_ant := vr_emp.oficio; i := 1; END IF; IF i <= 2 THEN DBMS_OUTPUT .PUT _LIN E(v r_emp.oficio||' * ' ||vr_e mp.apellido||' * ' ||vr_e mp.salario); END IF; FETCH c_emp INTO vr_emp; i:=I+1;
END LOOP; CLOSE c_emp; END emp _2minsal;
6) Escribir un programa que muestre, en formato similar a las rupturas de control o secuencia vistas en SQL*plus los siguientes datos: - Para cada empleado: apellido y salario. - Para cada departamento: Número de empleados y suma de los salarios del departamento. - Al final del listado: Número total de empleados y suma de todos los salarios. CREATE OR REPLACE PROCEDURE listar_emple AS CURSOR c1 IS SELECT apellido, salario, dept_no FROM emple ORDER BY dept_no, apellido; vr_emp c1%ROWTYPE; dep_ant EM PLE.D EPT _NO% TYPE ; cont_emple NUMBER(4) DEFAULT 0; sum_sal NUMBER(9) DEFAULT 0; tot_emp le NUMBER(4) DEFAULT 0; tot_sal NUMBER(10) DEFAULT 0; BEGIN OPEN c1; FETCH c1 INTO vr_emp; IF c1%FOUND THEN dep_ant := vr_emp.dept_no; END IF; WHILE c1%FOUND LOOP /* Comprobación nuevo departamento y resumen */ IF
dep_ant <> vr_emp.dept_no THEN
DBMS_OUTPUT.PUT_LINE('*** DEPTO: ' || dep_ant || ' NUM. EMPLEADOS: '||cont_emp le || ' SUM. SALARIOS: '||sum _sal ); dep_ant := vr_emp.dept_no; tot_emp le := tot_emple + cont_emple; tot_sal:= tot_sal + sum_sal; cont_emple:=0; sum_sal:=0; END IF; /* Líneas de detalle */
DBMS_OUTPUT .PUT _LIN E(RPAD (vr_emp.apellido, 10)|| ' * ' ||LPA D(TO_CH AR(vr_emp.salario, '9,999,999'),12));
/* Incrementar y acumular */
cont_emple := cont_emple + 1; sum_sal:= sum_sal + vr_emp.salario;
FETCH c1 INTO vr_emp; END LOOP; CLOSE c1; IF cont_emple > 0 THEN /* Escribir datos del último departamento */ DBMS_OUTPUT.PUT_LINE('*** DEPTO: ' || dep_ant || ' NUM EMPLEADOS: '|| cont_emp le || ' SUM. SALARIOS: '||sum _sal); dep_ant := vr_emp.dept_no; tot_emp le := tot_emple + cont_emple; tot_sal:= tot_sal + sum_sal; cont_emple:=0;
sum_sal:=0; /* Escribir totales informe */
DBMS_OUTPUT.PUT_LINE(' ****** NUMERO TOTAL EMPLEADOS: ' ||tot_emple || ' TOTAL SALARIOS: '|| tot_sal); END IF; END listar_emple; /* Nota: este procedimiento puede escribirse de forma que la visualización de los resultados resulte mas clara incluyendo líneas de separación, cabeceras decolum nas, etcétera. Por razones didácticas no se han incluido estos elementos ya que pueden distraer y dificultar la comprensión del código.*/
7) Desarrollar un procedimiento que permita insertar nuevos departamentos según las siguientes especificaciones: Se pasará al procedimiento el nombre del departamento y la localidad. El procedimiento insertará la fila nueva asignando como número de departamento la decena siguiente al número mayor de la tabla. Se incluirá gestión de posibles errores. CREATE OR REPLACE PROCEDURE insertar_depart( nombre_dep VARCHAR2, loc VARCHAR2) AS CURSOR c_dep IS SELECT dnombre FROM depart WHERE dnombre = nombre _dep; v_dummy DEP ART.DN OMBRE%T YPE DEFAULT N ULL; v_ulti_num DEPA RT.D EPT_NO% TYPE ; nombre_duplicado EXCEPTION;
BEGIN /* Comprobación de que eldepartamento no está duplicado */ OPEN c_dep; FETCH c_dep INTO v_dummy; CLOSE c_dep; IF v_dummy IS NOT NULL THEN RAISE nombre _duplic ado; END IF; /* Captura del último número y cálculo del siguiente */ SELECT MAX(dept_no) INTO v_ulti_num FROM depart; /* Inserción de la nueva fila */
INSERT INTO depart VALUES ((TRUNC(v_ulti_num, -1)+10) , nombre_dep, loc); EXCEPTION WHEN nombre_duplicado THEN DBMS_OUTPUT.PUT_LINE('Err. departamento dup lic ado'); RAI SE; WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20005,'Err. Operación cancelada’); END insertar_depart;
8) Escribir un procedimiento que reciba todos los datos de un nuevo empleado p rocese la transacción de alta, gestionando posibles errores. CREATE OR REPLACE PROCEDURE alta_emp( num emple.emp _no%TYPE , ape emple.apellido%TYPE, ofi emple.oficio%TY PE , jef emple.dir% TYPE , fec emple.f echa_alt% TYPE, sal emple.salario%TYPE , com emple.comisio n% TYPE DEFAULT N ULL, dep emple.dept_no%TYPE) AS v_dummy_jef EMPLE.D IR %TYPE DEFAULT NULL; v_dummy_dep DEPA RT.D EP T_NO% T YPE DEFAULT N ULL; BEGIN /* Comprobación de que existe el departamento */ SELECT dept_no INTO v_dummy_ dep FROM depart WHERE dept_no = dep; /* Comprobación de que existe el jefe del emp leado */
SELECT emp_no INTO v_dummy_jef FROM emple WHERE emp_no = jef;
/* Inserción de la fila */
INSERT INTO EMPLE VALUES (num, ape, ofi, jef, fec, sal, com, dep); EXCEPTION WHEN NO_DATA_FOUND THEN IF v_dummy_dep IS NULL T HEN RAI SE_A PPLICATIO N_E RRO R(-20005, 'Err. Departamento inexistente'); ELSIF v_dummy_jef IS NULL T HEN RAI SE_A PPLICATIO N_E RRO R(-20005, 'Err. No existe el jefe'); ELSE RAI SE_A PPLICATIO N_E RRO R(-20005, 'Err. Datos no encontrados(*)'); END IF; WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT .PUT _LIN E ('Err.numero de empleado duplic ado'); RAI SE; END alta_emp;
WHILE...FOUN D...LOOP.. . 3 .- Ejemp los como proc edim ientos con cursores y parám etros de entrada.
9) Codificar un procedimiento reciba como parámetros un numero de departamento, un importe y un porcentaje; y suba el salario a todos los empleados del departamento indicado en la llamada. La subida será el porcentaje o el importe indicado en la llam ada (el que sea más beneficioso para el empleado en cada caso empleado). CREATE OR REPLACE PROCEDURE subida_sal1( num_depar emple.dept_no% TYPE , importe N UMBER, porcentaje N UMBER) AS CURSOR c_sal IS SELECT salario,ROWID FROM emple WHERE dept_no = num_depar; vr_sal c_sal%RO WT YPE ; v_imp_pct NU MBER(10);
BEGIN OPEN c_sal; FETCH c_sal INTO vr_sal; WHILE c_sal%FOUND LOO P /* Guardar en v_imp_pct el importe mayor*/
v_imp_pct := GR EATEST((vr_sal.salario /100)*porcentaje, v_imp_pct); /* Actualizar */
UPDATE EMPLE SET SALARIO=SALARIO + v_imp_ pct WHERE ROWID = vr_sal.rowid; FETCH c_sal INTO vr_sal; END LOOP; CLOSE c_sal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Err. ninguna fila actualizada'); END s ubida_sal1 ;
10) Escribir un procedimiento que suba el sueldo de todos los empleados que ganen menos que el salario medio de su oficio. La subida será del 50% de la diferencia entre el salario del empleado y la media de su oficio. Se deberá asegurar que la transacción no se quede a medias, y se gestionarán los posibles errores. CREATE OR REPLACE PROCEDURE subida_50pct AS CURSOR c_ofi_sal IS SELECT oficio, AVG(salario)salario FROM emple GROUP BY oficio; CURSOR c_emp_sal IS SELECT oficio, salario FROM emple E1 WHERE salario < (SELECT AVG(salario) FROM emple E2 WHERE E2.oficio = E1.oficio) ORDER BY oficio, salario FOR UPDATE OF salario; vr_ofi_sal c_ofi_sal%RO WT YPE ; vr_emp_sal c_emp_sal%RO WT YPE ; v_incremento emp le.salario% TYPE;
BEGIN CO MMIT; OPEN c_emp_sal; FETCH c_emp_sal INTO vr_emp_sal ; OPEN c_ofi_sal; FETCH c_ofi_sal INTO vr_ofi_sal; WHILE c_ofi_sal%FOUND AND c_emp_sal%FOUND LOOP /* calcular incremento */
v_incremento := (vr_ofi_sal.salario - vr_emp_sal.salario)/ 2; /* actualizar */ UPDATE emp le SET salario = salario + v_incremento WHERE CURRENT OF c_emp_sal; /* siguien te empleado */
FETCH c_emp_sal INTO vr_emp_sal ; /* comprobar si es otro oficio */ IF c_ofi_sal%FOUND and
vr_ofi_sal.oficio <>vr_emp_sal.oficio THEN FETCH c_ofi_sal INTO vr_ofi_sal; END IF; END LOO P; CLOSE c_emp_sal; CLOSE c_ofi_sal; CO MMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK WORK; RAI SE; END s ubida_50pct;
11) Diseñar una aplicación que simule un listado de liquidación de los empleados según las siguient es especificaciones: - El listado tendrá el siguiente formato para cada empleado: ********************************************************************** Liquidación del empleado:...................(1) Dpto:.................(2) Oficio:...........(3) Salario : ............(4) Trienios :.............(5) Comp. Responsabil :.............(6) Comisión :.............(7) -----------Total :.............(8) **********************************************************************
Donde:
1 ,2, 3 y 4 Corresponden al apellido, departamento, oficio y salario del empleado. 5 Es el importe en concepto de trienios. Cada trienio son tres años completos desde la fecha de alta hasta la de emisión y supone 50€. 6 Es el complemento por responsabilidad. Será de 100€ por cada empleado que se encuentre directamente a cargo del empleado en cuestión. 7 Es la comisión. Los valores nulos serán sustituidos por ceros. 8 Suma de todos los conceptos anteriores.
El listado irá ordenado por Apellido. CREATE OR REPLACE PROCEDURE liquidar AS CURSOR c_emp IS SELECT apellido, emp_no, oficio, salario, N VL(comisio n,0) comision, dept_no, fecha_alt FROM emple ORDER BY apellido; vr_emp c_emp%RO WT YPE ; v_trien NUMBER(9) DEFAULT 0; v_comp_r N UMBER(9); v_total NU MBER(10);
BEGIN FOR vr_emp in c_emp LOOP /* Calcular trienios. Llama a la función trien ios creada en el ejercicio 11.8 */ v_trien := trien ios(vr_emp.fecha_alt,SY SDATE)* 50; /* Calcular complemento de responsabilidad. Se encierra en un bloque pues levantará N O_DATA_F OUN D*/
BEGIN SELECT COUNT(*) INTO v_comp_r FROM EMPLE WHERE DIR = vr_emp.emp_no; v_comp_r := v_comp_r *100; EXCEPTION WHEN NO_DATA_FOUND THEN v_comp_r:=0; END; /* Calcular el total del empleado */
v_total := vr_emp.salario + vr_emp. comision + v_trien + v_comp_r; /* Visualizar datos del empleado */
DBMS_OUTPUT .PUT _LIN E('*************************************'); DBMS_OUTPUT.PUT_LINE(' Liquidacion de : '|| vr_emp.apellido ||' Dpto: ' || vr_emp .dept_no || ' Oficio: ' || vr_emp.oficio);
DBMS_OUTPUT .PUT _LIN E(RPAD ('Salario :',16) ||LPA D(TO_CH AR(vr_emp.salario, '9,999,999'),12)); DBMS_OUTPUT.PUT_LINE(RPAD('Trienios: ',16) || LPAD(TO_CH AR(v_trien,'9,999,999'),12)); DBMS_OUTPUT.PUT_LINE('Comp. Respons: ' ||LPA D(TO_CH AR(v_comp_r,'9,999,999'),12)); DBMS_OUTPUT .PUT _LIN E(RPAD ('Comision: ' ,16) ||LPA D(TO_CH AR(vr_emp.comisio n,'9,999,999'),12)); DBMS_OUTPUT .PUT _LIN E('------------------'); DBMS_OUTPUT .PUT _LIN E(RPAD (' Total : ',16) ||LPA D(TO_CH AR(v_total,'9,999,999') ,12)); DBMS_OUTPUT .PUT _LIN E('************************************* *'); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No se ha encontrado ninguna fila'); END liquidar;
/* Nota: También se puede utilizar una cláusula SELECT más compleja:
CURSOR c_emp IS SELECT APELLIDO, EMP_NO, OFICIO, (EMP_CARGO * 10000) COM_RESPONSABILIDAD, SALARI O, NVL(COMISION, 0) COMISION, DEP T_NO, TRIENIOS(FECHA_ALT, SYSDATE) * 5000 TOT_TRIENIOS FROM EMPLE,(SELECT DIR,COUNT(*) EMP_CARGO FROM EM PLE GROUP BY DIR) DIREC WHERE EMPLE.EMP_NO = DIR EC.DIR(+) ORDER BY AP ELLIDO; de esta forma se simplifica el programa y se evita la utilización de variables de trabajo. */
12) Crear la tabla T_liquidacion con las columnas apellido, departamento, oficio, salario, trienios, comp_responsabilidad, comisión y total; y modificar la aplicación anterior para que en lugar de realizar el listado directamente en pantalla, guarde los datos en la tabla. Se controlarán todas las posibles incidencias que puedan ocurrir durante el proceso. CREATE TABLE t_liquidacion ( APELLIDO VARCHAR2(10), DEPARTAMENTO NU MBER(2), OFICIO VARCH AR2(10), SALARIO N UMBER(10), TRIENIOS N UMBER(10), COMP_RESPONSABILIDAD NU MBER(10), COMISION N UMBER(10), TOTAL NU MBER(10) ); CREATE OR REPLACE PROCEDURE liquidar2 AS CURSOR c_emp IS SELECT apellido, emp_no, oficio, salario, N VL(comisio n,0) comision, dept_no, fecha_alt FROM emple ORDER BY apellido; vr_emp c_emp%RO WT YPE ; v_trien NUMBER(9) DEFAULT 0; v_comp_r N UMBER(9); v_total NU MBER(10);
BEGIN COMMIT WOR K; FOR vr_emp in c_emp LOOP /* Calcular trienios. Llama a la función trien ios creada en el ejercicio 11.8 */ v_trien := trienios(vr_emp.fecha_alt,SY SDATE)* 5000; /* Calcular complemento de responsabilidad. Se encierra en un bloque pues levantará N O_DATA_F OUN D*/
BEGIN SELECT COUNT(*) INTO v_comp_r FROM EMPLE WHERE DIR = vr_emp.emp_no; v_comp_r := v_comp_r*10000; EXCEPTION WHEN NO_DATA_FOUND THEN v_comp_r:=0; END; /* Calcular el total del empleado */
v_total := vr_emp.salario + vr_emp. comision + v_trien + v_comp_r; /* Insertar los datos en la tabla T_liquidacio n */ INSERT INTO t_liquidacion
(APELLIDO, OFICIO, SALARIO, TRI ENIOS, COMP_RESPONSABILIDAD, COMISION, TOT AL) VALUES (vr_emp.apellido, vr_emp.oficio, vr_emp.salario, v_trien, v_comp_r, vr_emp.comision, v_total); END LOOP; EXCEPTION WHEN OTHERS THEN ROLLBACK WORK; END liquidar2;
CREATE OR REPLACE TRIGGER... a) Ejemp lo de como crear un trigg er.
1.- Construir un disparador de base de datos que permita auditar las operaciones de inserción o borrado de datos que se realicen en la tabla emple según las siguientes especificaciones: - En primer lugar se creará desde SQL*Plus la tabla auditaremple con la columna col1 VARCHAR2(200). - Cuando se produzca cualquier manipulación se insertará una fila en dicha tabla que contendrá: - Fecha y hora - Número de empleado - Apellido - La operación de actualización INSERCIÓN o BORRADO CREATE TABLE auditaremple ( col1 VARCHAR2(200) ); CREATE OR REPLACE TRIGGER auditar_act_emp BEFORE INSERT OR DELETE ON EM PLE FOR EACH RO W BEGIN IF DELETING THEN INSERT INTO AUD ITARE MPLE VALUES(TO_CH AR(sysda te,'D D/MM/YY*HH24:MI*') || :OLD .EMP_NO|| '*' || :OLD.APELLIDO || '* BORRADO '); ELSIF INSERTING THEN INSERT INTO AUD ITARE MPLE VALUES(TO_CH AR(sysda te,'D D/MM/YY*HH 24:MI*') || :NEW.EMP_NO || '*' || :NEW.AP ELLIDO||'* INSERCION '); END IF; END;
CREATE OR REPLACE TRIGGER.... b) Ejemp lo de como crear un trigger cuando actualizamos en la tabla datos.
2.- Escribir un trigger de base de datos un que permita auditar las modificaciones en la tabla empleados insertado en la tabla auditaremple los siguientes datos: - Fecha y hora - Número de empleado - Apellido - La operación de actualización: MOD IFICACIÓN. - El valor anterior y el valor nuevo de cada columna modificada. (solo las columnas modificadas) CREATE OR REPLACE TRIGGER audit_modif BEFORE UPDATE ON EMPLE FOR EACH ROW DECLAR E v_cad_inser auditaremple.col1%TYPE ; BEGIN v_cad_inser := TO_CH AR(sysdat e,'DD/MM/YY*HH24:MI*') ||:OLD.EMP_NO ||'* MODIFICACION *'; IF UPDATING ('EMP_NO') THEN v_cad_inser :=v_cad_inser ||:OLD.EMP_N O|| '*'|| :NEW.EMP_N O; END IF;
IF UPDATING ('APELLIDO') THEN v_cad_inser :=v_cad_inser ||:OLD.APELLIDO|| '*'|| :NEW.AP ELLIDO; END IF; IF UPDATING ('OFICIO') THEN v_cad_inser :=v_cad_inser ||:OLD.OFICIO || '*'||:N EW.OFI CIO; END IF; IF UPDATING ('DIR') THEN v_cad_inser :=v_cad_inser ||:OLD.D IR|| '*'||:N EW.DIR ; END IF; IF UPDATING ('FECHA_ALT') THEN v_cad_inser :=v_cad_inser ||:OLD.FECH A_A LT||:NEW.FECHA_A LT; END IF;
IF UPDATING ('SALARIO') THEN v_cad_inser :=v_cad_inser ||:OLD.SALARI O|| '*'||:NEW.SALARIO ; END IF;
IF UPDATING ('COMISION') THEN v_cad_inser :=v_cad_inser ||:OLD.COMISION || '*'||:NEW.C OMISIO N ; END IF; IF UPDATING ('DEPT_NO') THEN v_cad_inser :=v_cad_inser ||:OLD.D EP T_NO|| '*'||:NEW.D EPT _N O; END IF; INSERT INTO AUDITAREMPLE VALUES(v_c ad_inser); END;
3.- Escribir un disparador de base de datos que haga fallar cualquier operación de modificación del apellido o del número de un empleado, o que suponga una subida de sueldo superior al 10%. CREATE OR REPLACE TRIGGER fallo_modif BEFORE UPDATE OF apellido, emp_no, salario ON emple FOR EACH RO W BEGIN IF UPD ATING('emp _no') OR UPDATING(' apellido') OR (UPDATING ('salario') AND :new.salario>:old.salario*1.1) THEN RAI SE_A PPLICATIO N_E RRO R (-20001,'Err. Modificacion no permitida'); END IF; END;
CREATE OR REPLACE TRIGGER... c) Ejemp lo de como crear un trigger a partir de una vista.
4.- Suponiendo que disponemos de la vista CREATE VIEW DEPARTAM AS SELECT DEPART.DEPT_NO, DNOMBRE, LOC, COUNT(EMP_NO) TOT_EM PLE FROM EMPLE, DEP ART WHERE EMPLE.DEPT_NO (+) = DEP ART.D EPT _N O GROUP BY DEPART.DEPT_NO, DNOMBRE, LOC;
Construir un disparador que permita realizar operaciones de actualización en la tabla depart a partir de la vista dptos, de forma similar al ejemplo del trigger t_ges_emplead. Se contemplarán las siguientes operaciones: - Insertar departamento. - Borrar departamento. - Modificar la localidad de un departamento. CREATE OR REPLACE TRIGGER ges_depart INSTEAD OF DELETE OR INSERT OR UPDATE ON DEPARTAM FOR EACH RO W BEGIN IF DELETING THEN DELETE FROM depart WHERE dept_no = :old.dept_no; ELSIF INSERTING THEN INSERT INTO depart VALUES(:n ew.dept_no, :new.dnombre, :new.loc); ELSIF UPDATING('loc') THEN UPDATE depart SET loc = :new.loc WHERE dept_no = :old.dept_no; ELSE RAI SE_A PPLICATIO N_E RRO R (-20001,'Error en la actualización'); END IF; END;
CREATE OR REPLACE PA CKAGE.. . 1.- Ejemp lo de cómo crear un pa qu ete.
Escribir un paquete completo para gestionar los departamentos. El paquete se llamará gest_depart y deberá incluir, al menos, los siguientes subprogramas: - i nser tar_nuevo_depart: permite insertar un departamento nuevo. El procedimiento recibe el nombre y la localidad del nuevo departamento. Creará el nuevo departamento comprobando que el nombre no se duplique y le asignará como número de departamento la decena siguiente al último número de departamento utilizado. - borr ar_depart: permite borrar un departamento. El procedimiento recibirá dos números de departamento de los cuales el primero corresponde al departamento que queremos borrar y el segundo al departamento al que pasarán los empleados del departamento que se va eliminar. El procedimiento se encargará de realizar los cambios oportunos en los números de departamento de los empleados correspondientes. - modi fi car_loc_depart: modifica la localidad del departamento. El procedimiento recibirá el número del departamento a modificar y la nueva localidad, y realizará el cambio solicitado. - visuali zar_datos_depart: visualizará los datos de un departamento cuyo número se pasará en la llamada. Además de los datos relativos al departamento, se visualizará el número de empleados que pertenecen actualmente al departamento. - visuali zar_datos_depart: versión sobrecargada del procedimiento anterior que, en lugar del número del departamento, recibirá el nombre del departamento. Realizará una llamada a la función buscar_depart_por_nombre que se indica en el apartado siguiente. - buscar_depar t_por_nombre: función local al paquete. Recibe el nombre de un departamento y devuelve el número del mismo. /************* Cabecera o especificación del paquete ********** /
CREATE OR REPLACE PACKAGE gest_depart AS PROCEDURE insert _depart (v_nom_dep VARCHAR2, v_loc VARCH AR2); PROCEDURE borrar_depar (v_dep_borrar N UMBER, v_dep_nue NU MBER); PROCEDURE cambiar_localid ad (v_num_dep N UMBER, v_loc VARCH AR2); PROCEDURE visualizar_datos_depart (v_num_dep vis BER); N UM PROCEDURE ualiz ar_datos_depart (v_nom_dep VARCHAR2); END gest_depa rt; /
/******************* Cuerpo del paquete **********************/
CREATE OR REPLACE PACKAGE BODYgest_depart AS FUNCTION buscar_depart_por_nombre /* Función privada */ (v_nom_dep VARCHAR2) RETURN N UMBER; /************************************************************* /
PROCEDURE insert _depart( v_nom_dep VARCHAR2, v_loc VARCHAR2) AS ultimo_dep DEP ART.D EP T_NO% TYPE ; nombre_repetido EXCEPTIO N ; BEGIN /*Comp robar dpt rep etido(Puede levantar
N O_DATA_F OUN D)*/
DECLAR E nom_dep depart .D N OMBRE%T YPE ; nombre_repetido EXCEPTIO N ; BEGIN SELECT dnombre INTO nom_dep FROM depart WHERE dnombre = v_nom_dep; RAISE insert_depart.nombre_repetido; EXCEPTION WHEN NO_DATA_FOUND THEN N ULL; WHEN TOO_MAN Y_ROWS THEN RAISE insert_Depart.nombre_repetido; END; /* Fin del bloque de comprobación de departamento repetido */ /* Calcular el número de departamento e insertar */ SELECT MAX(DEPT_NO) INTO ultimo_dep FROM DEPART; INSERT
INTO DEPART VALUES ((TRUNC(ultimo_dep, -1) +10), v_nom_dep,v_loc); EXCEPTION WHEN nombre_repetido THEN DBMS_OUTPUT .PUT _LIN E ('Err. Nombre de departamento dup lic ado'); WHEN NO_DATA_FOUND THEN /* Si no había ningún departamento */ INSERT INTO DEPART VALUES (10,v_nom_dep,v_loc); END insert_depart; /************************************************************* */
PROCEDURE borrar_depar (v_dep_borrar N UMBER, v_dep_nue NU MBER) AS BEGIN UPDATE emp le SET dept_no = v_dep_nue WHERE DEP T_NO=v_dep_borrar; DELETE FROM depart WHERE dept_no = v_d ep_borrar; END borrar_depar; /************************************************************* / PROCEDURE visualizar_datos_depart (v_num_dep N UMBER) AS vr_dep depart%RO WT YPE ; v_num_empleados N UMBER(4); BEGIN SELECT * INTO vr_dep FROM depart WHERE DEP T_NO=v_num_dep; SELECT COUNT(*) INTO v_num_empleados FRO M EMPLE WHERE DEPT _N O=v_num_dep;
DBMS_OUTPUT .PUT _LIN E ('Número de departamento: '||v r_dep .dept_no); DBMS_OUTPUT .PUT _LIN E ('Nombre del departamento: '||v r_dep .dnombre); DBMS_OUTPUT .PUT_LIN E ('Localidad : '||v r_dep.loc); DBMS_OUTPUT .PUT _LIN E ('Numero de empleados : '||v _num_empleados); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Err departamento no encontrado'); END visualizar_datos_depart; /************************************************************* /
PROCEDURE visualizar_datos_depart /* Versión sobrecargada */ (v_nom_dep VARCHAR2) AS v_num_dep depart.dept_no%TYPE ; vr_dep depart%RO WT YPE ; v_num_empleados N UMBER(4); BEGIN v_n um_dep:=buscar_depart_por_nombre (v_n om_d ep); SELECT * INTO vr_dep FROM depart WHERE dept_no=v_num _dep;
SELECT COUNT(*) INTO v_num_empleados FROM EM PLE WHERE dept_no=v_num _dep;
DBMS_OUTPUT .PUT _LIN E ('Número de departamento: '||v r_dep .dept_no); DBMS_OUTPUT .PUT _LIN E ('Nombre del departamento: '||v r_dep .dnombre); DBMS_OUTPUT .PUT _LIN E ('Localidad : '||v r_dep.loc); DBMS_OUTPUT .PUT _LIN E ('Numero de empleados : '||v _num_empleados);
EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Err departamento no encontrado'); END visualizar_datos_depart; /************************************************************* /
FUNCTION buscar_depart_por_nombre (v_nom_dep VARCHAR2) RETURN N UMBER AS v_num_dep depart.dept_no%TYPE ; BEGIN SELECT dept_no INTO v_num_dep FROM depa rt WHERE DNOMBRE = v_n om_dep; RETURN v_num_d ep; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Err departamento no encontrado'); END buscar_depart_por_nombre ; /************************************************************* / PROCEDURE cambiar_localidad( v_num_dep NUMBER, v_loc VARCHAR2) AS BEGIN UPDATE depart SET LOC=v_loc WHERE dept_no=v_num _dep; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Err departamento no encontrado'); END cambiar_localid ad; END gest_depa rt;
CREATE OR REPLACE PACKAGE BODY.... 2 .- Ejemp lo de como crear un pa qu ete.
Escribir un paquete completo para gestionar los empleados. El paquete se llamará gest_emple e incluirá, al menos los siguientes subprogramas: - i nser tar_nuevo_emple - borrar_emple . Cuando se borra un empleado todos los empleados que dependían de él pasarán a depender del director del empleado borrado. - modificar_oficio_emple - modi fi car_dept_emple - modificar_dir_emple - modi fi car_salari o_emple - modificar_comision_emple - visualizar_datos_emple . También se incluirá una versión sobrecargada del procedimiento que recibirá el nombre del empleado. - buscar_emple_por_nombre . Función local que recibe el nombre y devuelve el número. Todos los procedimientos recibirán el número del empleado seguido de los demás datos necesarios. También se incluirán en el paquete cursores y declaraciones de tipo registro, así como siguientes procedimientos que afectarán a todos los empleados: - subi da_salari o_pct: incrementará el salario de todos los empleados el porcentaje indicado en la llamada que no podrá ser superior al 25%. - subi da_salari o_i mp: sumará al salario de todos los empleados el importe indicado en la llamada. Antes de proceder a la incrementar los salarios se comprobará que el importe indicado no supera el 25% del salario medio. /******************
Cabecera del paquete ********************* / CREATE OR REPLACE PACKAGE gest_emple AS
CURSOR c_sal RETURN EM PLE%RO WT YPE ; PROCEDURE insert ar_nuevo_emple( v_num_emp EM PLE.EM P_NO% TYPE , v_apell EMPLE.APE LLIDO %TYPE, v_oficio EMPLE.OFICIO% TYPE , v_dir EMPLE.D IR%TYPE , v_fecha_al EM PLE.F ECHA_A LT%TYPE , v_sal EM PLE.SALARI O% TYPE , v_comision EMPLE.COMISION% TYPE DEFAULT NU LL, v_num_dep EM PLE.D EPT _NO% TYPE ); PROCEDURE borrar_emple( v_num_emple NU MBER);
PROCEDURE modificar_oficio_emple( v_num_emp EM PLE.EM P_NO% TYPE , v_oficio EMPLE.OFICIO% TYPE ); PROCEDURE modificar_dept_emple( v_num_emp EM PLE.EM P_NO% TYPE , v_dept EMPLE.D EP T_N O%TYPE ); PROCEDURE modificar_dir_emple( v_num_emp EM PLE.EM P_NO% TYPE , v_direccion EM PLE.D IR %TYPE ); PROCEDURE modificar_salario_ emple( v_num_emp EM PLE.EM P_NO% TYPE , v_salario EMPLE.SALARI O% TYPE ); PROCEDURE modificar_comisio n_emp le( v_num_emp EM PLE.EM P_NO% TYPE , v_comis EM PLE.COMISIO N%TYPE); PROCEDURE visualizar_datos_emple( v_num_emp EM PLE.EM P_NO% TYPE ); PROCEDURE visualizar_datos_emple( v_nombre_emp EMPLE.APELLIDO% TYPE ); PROCEDURE subida_salario _pct( v_pct_subida N UMBER); PROCEDURE subida_salario_im p( v_imp_subida N UMBER); END gest_emple; /******************** Cuerpo del paquete *********************/
CREATE OR REPLACE PACKAGE BODYgest_emple AS
CURSOR c_sal RETURN EM PLE%RO WT YPE IS SELECT * FROM EMPLE;
FUNCTION buscar_emp le_por_nombre (n_emp VARCHAR2) RETURN N UMBER; /************************************************************* /
PROCEDURE insert ar_nuevo_emple( v_num_emp EM PLE.EM P_NO% TYPE , v_apell EMPLE.APE LLIDO %TYPE, v_oficio EMPLE.OFICIO% TYPE , v_dir EMPLE.D IR%TYPE , v_fecha_al EM PLE.FECH A_A LT%TYPE , v_sal EM PLE.SALARI O% TYPE , v_comision EMPLE.COMISION% TYPE DEFAULT NU LL, v_num_dep EM PLE.D EPT _NO% TYPE ) IS dir_no_existe EXCEPTIO N ; BEGIN DECLAR E v_num_emple EM PLE.EMP_NO% TYPE ; BEGIN SELECT EMP_NO INTO v_num_emple FROM EMPLE WHERE EMP_NO=v_dir; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE insertar_nuevo_emple.dir_no_existe; END; INSERT INTO EMPLE VALUES (v_num_emp, v_apell, v_oficio, v_dir, v_fecha_al, v_sal, v_comision, v_num_dep); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Err. Número de emp leado duplic ado'); WHEN dir_no_existe THEN DBMS_OUTPUT.PUT_LINE('Err. No existe el director'); END insertar_nuevo_emple; /************************************************************* / PROCEDURE borrar_emple(
v_num_emple NU MBER) IS emp_dir EMPLE.D IR% TYPE ; BEGIN SELECT DIR INTO emp_dir FROM EMPLE WHERE EMP_NO = v_num_emple; DELETE FROM EMPLE WHER E EMP_NO = v_num_emple; UPDATE EMPLE SET DIR = emp_dir WHERE DIR = v_num_emple; END borrar_emple; /************************************************************* /
DESARROLLO DE APLICACIONES EN ENTORNOS DE 4ª GENERACI N Y CON HERRAMIENTAS CASE
PROCEDURE modificar_oficio_emple( v_num_emp EM PLE.EM P_NO% TYPE , v_oficio EMPLE.OFICIO% TYPE ) IS BEGIN UPDATE EMPLE SET OFICIO = v_oficio WHERE EMP_NO = v_num_emp; END modificar_oficio_emple; /************************************************************* / PROCEDURE modificar_dept_emple(
v_num_emp EM PLE.EM P_NO% TYPE , v_dept EMPLE.D EP T_N O%TYPE ) IS BEGIN UPDATE EMPLE SET DEPT_NO = v_dept WHERE EMP_NO = v_num_emp; END modificar_dept_emple; /************************************************************* / PROCEDURE modificar_dir_emple(
v_num_emp EM PLE.EM P_NO% TYPE , v_direccion EM PLE.D IR %TYPE ) IS BEGIN UPDATE EMPLE SET DIR = v_direccion WHERE EMP_NO = v_num_emp; END modificar_dir_emple;
/************************************************************* / PROCEDURE modificar_salario_ emple(
v_num_emp EM PLE.EM P_NO% TYPE , v_salario EMPLE.SALARI O% TYPE ) IS BEGIN UPDATE EMPLE SET SALARIO = v_salario WHERE EMP_NO = v_num_emp; END modificar_salari o_emple; /************************************************************* / PROCEDURE modificar_comisio n_emp le(
v_num_emp EM PLE.EM P_NO% TYPE , v_comis EM PLE.COMISIO N%TYPE ) IS BEGIN UPDATE EMPLE SET COMISION = v_comis WHERE EMP_NO = v_num_emp; END modificar_comision_emple; /************************************************************* /
DESARROLLO DE APLICACIONES EN ENTORNOS DE 4ª GENERACI N Y CON HERRAMIENTAS CASE
PROCEDURE visualizar_dat os_emple( v_num_emp EM PLE.EM P_NO% TYPE ) IS re g_emple EMPLE%RO WT YPE ; BEGIN SELECT * INTO reg_emp le FROM EMPLE WHERE EMP_NO = v_num_emp; DBMS_OUTPUT.PUT_LINE('NUMERO EMPLEADO: '||reg_ emple.EMP_N O); DBMS_OUTPUT.PUT_LINE('APELLIDO: '||reg_ emple.APELLIDO); DBMS_OUTPUT.PUT_LINE('OFICIO: '||reg_ emple.OFICI O); DBMS_OUTPUT.PUT_LINE('DIRECTOR: '||reg_ emple.DIR); DBMS_OUTPUT.PUT_LINE('FECHA ALTA): '||reg_ emple.FECHA_A LT); DBMS_OUTPUT.PUT_LINE('SALARIO: '||reg_ emp le.SALARI O); DBMS_OUTPUT.PUT_LINE('COMISION: '||reg_ emple.COMISION ); DBMS_OUTPUT.PUT_LINE('NUMERO DEPARTAMENTO: '||reg_ emp le.DEP T_NO); END visualizar_datos_emple; /************************************************************* / PROCEDURE visualizar_datos_emple( v_nombre_emp EMPLE.APELLIDO% TYPE ) IS v_num_emp EM PLE.EM P_NO% TYPE ; re g_emple EMPLE%RO WT YPE ; BEGIN v_num_emp:=buscar_emple_por_nombre (v_nombre _emp); SELECT * INTO reg_emp le FROM EMPLE WHERE EMP_NO = v_num_emp; DBMS_OUTPUT.PUT_LINE('NUMERO EMPLEADO: '||reg_ emple.EMP_N O); DBMS_OUTPUT.PUT_LINE('APELLIDO : '||reg_emple.APELLIDO); DBMS_OUTPUT.PUT_LINE('OFICIO : '||reg_ emple.OFICIO ); DBMS_OUTPUT.PUT_LINE('DIRECTOR : '||reg_ emple.DIR ); DBMS_OUTPUT.PUT_LINE('FECHA ALTA: '||reg_ emple.FECHA_A LT); DBMS_OUTPUT.PUT_LINE('SALARIO : '||reg_ emp le.SALARI O); DBMS_OUTPUT.PUT_LINE('COMISION : '||reg_ emple.COMISIO N ); DBMS_OUTPUT.PUT_LINE('NUM DEPART: '||reg_ emple.DEPT _N O); END visualizar_datos_emple; /************************************************************ */
FUNCTION buscar_emp le_por_nombre( n_emp VARCHAR2) RETURN N UMBER IS numero EMPLE.EMP_NO% TYPE ; BEGIN SELECT EMP_NO INTO numero FROM EMPLE WHERE APELLIDO = n_emp; RETURN numero; END buscar_emple_por_nombre; /************************************************************* /
DESARROLLO DE APLICACIONES EN ENTORNOS DE 4ª GENERACI N Y CON HERRAMIENTAS CASE
PROCEDURE subida_salario _pct( v_pct_subida N UMBER) IS subida_mayor EXCEPTION ; BEGIN IF v_pct_subida > 25 THEN RAISE subida_mayor; END IF; FOR vr_c_sal IN c_sal LOOP UPDATE EMPLE SET SALARIO = SALARIO + (SALARIO * v_pct_subida / 100) WHERE EMP_NO = vr_c_ sal.emp_no; END LOOP; EXCEPTION WHEN subida_mayor THEN DBMS_OUTPUT.PUT_LINE('Subida superior a la permitida'); END s ubida_salari o_pct; /************************************************************* / PROCEDURE subida_salario_im p( v_imp_subida N UMBER) IS subida_mayor EXCEPTION ; sueldo_med io NU MBER(10); BEGIN SELECT AVG(SALARIO) INTO sueldo_medio FROM EM PLE; IF v_imp_subida>sueldo_medio THEN RAISE subida_mayor; END IF; FOR vr_c_sal in c_sal LOOP UPDATE EMPLE SET SALARIO = SALARIO + v_imp_subida WHERE EMP_NO = vr_c_ sal.emp_no; END LOOP; EXCEPTION WHEN subida_mayor THEN DBMS_OUTPUT.PUT_LINE('Subida superior a la permitida'); END s ubida_salari o_im p; END gest_emple;
DESARROLLO DE APLICACIONES EN ENTORNOS DE 4ª GENERACI N Y CON HERRAMIENTAS CASE
SQ L DINÁMICO – Paquete DBMS.SQ L 1.- Crear un procedimiento que permita consultar todos los datos de la tabla depart a partir de una condición que se indicará en la llamada al procedimiento. CREATE OR REPLACE PROCEDURE consultar_depart (condicion VARCHAR2, valor VARCHAR2) AS id_cursor INTEGER; v_comando VARCHAR2(2000); v_dummy N UMBER; v_dept_no depart.dept_no%TYPE ; v_dnombre depart.dnombre%TYPE; v_loc depart.loc%TYPE; BEGIN id_cursor := DBMS_SQL. OPEN_CU RSOR; v_comando := ‘ SELECT dept_no, dnombre, loc FROM depart WHERE ‘ || condicion || ‘:val_1’; DBMS_OUTPUT .PUT _LIN E(v _comando); DBMS_SQL.PARSE(id_cursor,v_comando, DBMS_SQL.V7); DBMS_SQL.BIND_VARIABLE(id_cursor, ‘:va l_1 ’, valor); /* A continuación se especifican las variables que recibirán los valores de la selección*/ DBMS_SQL.DEFINE_COLUMN(id_cursor, 1, v_dept_no); DBMS_SQL.DEFINE_COLUMN(id_cursor, 2, v_dnombre,14); DBMS_SQL.DEFINE_COLUMN(id_cursor, 3, v_loc, 14); v_dummy := DBMS_SQL. EXECUTE(id _cursor); /* La función FETCH_ROWS recupera filas y retorna el número de filas que quedan */ WHILE DBMS_SQL.FETCH_ROWS(id_cursor)>0LOOP /* A continuación se depositarán los valores DBMS_SQL.COLUMN_VALUE(id_cursor, dept_no); en las variables PL/SQL */ 1, v_recuperados DBMS_SQL.COLUMN_VALUE(id_cursor, 2, v_dnombre);
DBMS_SQL.COLUMN_VALUE(id_cursor, 3, v_loc); DBMS_OUTPUT .PUT _LIN E(v _dept_no || ’*’ || v_dnombre || ’*’ || v_loc); END LOOP; DBMS_SQL. CLOSE_CU RSOR (id_cursor); EXCEPTION WHEN OTHERS THEN DBMS_SQL. CLOSE_CU RSOR (id_cursor); RAI SE; END consultar_depa rt;
