Funciones SQL y PL/SQL en Oracle: Ejercicios Prácticos con Tablas Empleado y Préstamo

Creación de Tablas en Oracle

Tabla Empleado

Se define la tabla Empleado con su clave primaria sobre el campo rut:

CREATE TABLE Empleado (
  rut VARCHAR2(15),
  nombre VARCHAR2(20),
  direccion VARCHAR2(30),
  sueldo NUMBER,
  tipo VARCHAR2(15),
  profesion VARCHAR(15),
  comuna VARCHAR2(15),
  fecha_contrato DATE,
  CONSTRAINT Empleado_rut_pk PRIMARY KEY(rut)
);

Tabla Prestamo

La tabla Prestamo incluye una clave foránea que referencia a Empleado(rut):

CREATE TABLE Prestamo (
  codigo VARCHAR(30),
  libro VARCHAR2(50),
  categoria VARCHAR2(20),
  fecha_prestamo DATE,
  rut VARCHAR2(15),
  CONSTRAINT Prestamo_codigo_pk PRIMARY KEY(codigo),
  CONSTRAINT Empleado_Prestamo_rut_fk FOREIGN KEY(rut) REFERENCES Empleado(rut)
);

Inserción de Datos

Datos de la tabla Empleado

INSERT INTO Empleado VALUES('12345-7','miguel castro','las margaritas 1322',267900,'ventas','vendedor','lo prado',TO_DATE('22-12-2000','dd-mm-yyyy'));
INSERT INTO Empleado VALUES('10500-4','pablo paredes','la esperanza 764',367800,'finanzas','contador','santiago',TO_DATE('10-03-2006','dd-mm-yyyy'));
INSERT INTO Empleado VALUES('13450-5','daniela vera','miguel carrera 1020',523000,'informatico','programador','maipu',TO_DATE('15-05-2008','dd-mm-yyyy'));
INSERT INTO Empleado VALUES('12345-8','luis lopez','los trabajadores 345',309800,'ventas','vendedor','maipu',TO_DATE('29-09-2005','dd-mm-yyyy'));
INSERT INTO Empleado VALUES('14356-3','marcela cubillos','san pablo 7866',501000,'informatico','programador','santiago',TO_DATE('19-04-2006','dd-mm-yyyy'));
INSERT INTO Empleado VALUES('15990-1','camila rojas','maipu 134',289000,'ventas','vendedor','lo prado',TO_DATE('14-07-2010','dd-mm-yyyy'));

Datos de la tabla Prestamo

INSERT INTO Prestamo VALUES(709,'cronicas de narnia','fantasia',TO_DATE('20-12-2010','dd-mm-yyyy'),'12345-7');
INSERT INTO Prestamo VALUES(718,'harry potter 5','fantasia',TO_DATE('21-03-2011','dd-mm-yyyy'),'12345-8');
INSERT INTO Prestamo VALUES(800,'el ojo de la luna','drama',TO_DATE('07-04-2011','dd-mm-yyyy'),'13450-5');
INSERT INTO Prestamo VALUES(820,'cronicas de narnia','fantasia',TO_DATE('14-05-2011','dd-mm-yyyy'),'10500-4');
INSERT INTO Prestamo VALUES(840,'comer,rezar,amar','drama',TO_DATE('15-12-2010','dd-mm-yyyy'),'12345-7');
INSERT INTO Prestamo VALUES(900,'harry potter 5','fantasia',TO_DATE('29-01-2011','dd-mm-yyyy'),'10500-4');
INSERT INTO Prestamo VALUES(910,'cronicas de narnia','fantasia',TO_DATE('11-12-2010','dd-mm-yyyy'),'15990-1');

Ejercicios PL/SQL

Ejercicio 1: Conversión de fecha a cadena

DECLARE
  fecha VARCHAR(15);
BEGIN
  SELECT TO_CHAR(fecha_prestamo,'dd-mm-yyyy') INTO fecha
  FROM Prestamo, Empleado
  WHERE Empleado.rut = Prestamo.rut AND Empleado.nombre = 'daniela vera';
  BEGIN
    DBMS_OUTPUT.PUT_LINE('fecha cambiada de tipo: ' || fecha);
  END;
END;

Ejercicio 2: Longitud de una cadena

DECLARE
  contar NUMBER;
  dire VARCHAR(30);
BEGIN
  SELECT LENGTH(direccion), Empleado.direccion INTO contar, dire
  FROM Empleado, Prestamo
  WHERE Empleado.rut = Prestamo.rut AND Prestamo.libro = 'comer,rezar,amar';
  BEGIN
    DBMS_OUTPUT.PUT_LINE('la cantidad de caracteres es: ' || contar);
    DBMS_OUTPUT.PUT_LINE('la dirección es: ' || dire);
  END;
END;

Ejercicio 3: Conversión de cadena a número

DECLARE
  numero NUMBER;
  codi VARCHAR2(30);
BEGIN
  SELECT TO_NUMBER(codigo), Prestamo.codigo INTO numero, codi
  FROM Empleado, Prestamo
  WHERE Empleado.rut = Prestamo.rut AND Empleado.sueldo > 270000 AND Empleado.comuna = 'lo prado';
  DBMS_OUTPUT.PUT_LINE('código cambiado de tipo numérico: ' || numero);
  DBMS_OUTPUT.PUT_LINE('el código es: ' || codi);
END;

Ejercicio 4: Conversión a mayúsculas

DECLARE
  mayuscula VARCHAR(50);
  lib VARCHAR2(50);
BEGIN
  SELECT UPPER(libro), Prestamo.libro INTO mayuscula, lib
  FROM Empleado, Prestamo
  WHERE Empleado.rut = Prestamo.rut
    AND Prestamo.categoria = 'fantasia'
    AND Empleado.fecha_contrato = TO_DATE('29-09-2005','dd-mm-yyyy');
  DBMS_OUTPUT.PUT_LINE('en mayúscula: ' || mayuscula);
  DBMS_OUTPUT.PUT_LINE('el libro es: ' || lib);
END;

Ejercicio 5: Extracción de subcadenas

DECLARE
  cadena VARCHAR2(50);
  dire VARCHAR(50);
BEGIN
  SELECT SUBSTR(direccion,3,9), Empleado.direccion INTO cadena, dire
  FROM Empleado, Prestamo
  WHERE Empleado.rut = Prestamo.rut
    AND Prestamo.fecha_prestamo = TO_DATE('29-01-2011','dd-mm-yyyy');
  BEGIN
    DBMS_OUTPUT.PUT_LINE('los caracteres son: ' || cadena);
    DBMS_OUTPUT.PUT_LINE('la dirección es: ' || dire);
  END;
END;

Ejercicio 6: Suma de meses a una fecha

DECLARE
  incre DATE;
  fecha DATE;
BEGIN
  SELECT ADD_MONTHS(fecha_prestamo,6), Prestamo.fecha_prestamo INTO incre, fecha
  FROM Empleado, Prestamo
  WHERE Empleado.rut = Prestamo.rut AND Empleado.nombre = 'luis lopez';
  BEGIN
    DBMS_OUTPUT.PUT_LINE('la fecha después de 6 meses: ' || incre);
    DBMS_OUTPUT.PUT_LINE('la fecha es: ' || fecha);
  END;
END;

Ejercicio 7: Diferencia en meses entre fechas

DECLARE
  diferencia NUMBER;
BEGIN
  SELECT MONTHS_BETWEEN(fecha_prestamo, TO_DATE('24-08-2011','dd-mm-yyyy'))
  INTO diferencia
  FROM Prestamo
  WHERE libro = 'el ojo de la luna';
  DBMS_OUTPUT.PUT_LINE('la diferencia de meses es: ' || diferencia);
END;

Ejercicio 8: Consulta con múltiples variables

DECLARE
  mayuscula VARCHAR(15);
  tip VARCHAR2(15);
  cate VARCHAR2(20);
BEGIN
  SELECT UPPER(profesion), Empleado.tipo, Prestamo.categoria INTO mayuscula, tip, cate
  FROM Empleado, Prestamo
  WHERE Empleado.rut = Prestamo.rut AND Prestamo.codigo = '820';
  DBMS_OUTPUT.PUT_LINE('en mayúscula es: ' || mayuscula);
  DBMS_OUTPUT.PUT_LINE('el tipo es: ' || tip);
  DBMS_OUTPUT.PUT_LINE('la categoría es: ' || cate);
END;

Ejercicio 9: Combinación de funciones

DECLARE
  codi NUMBER;
  profe VARCHAR2(15);
  lib NUMBER;
  suel NUMBER;
BEGIN
  SELECT TO_NUMBER(codigo), UPPER(profesion), LENGTH(libro), Empleado.sueldo
  INTO codi, profe, lib, suel
  FROM Empleado, Prestamo
  WHERE Empleado.rut = Prestamo.rut AND Empleado.nombre = 'camila rojas';
  DBMS_OUTPUT.PUT_LINE('el código cambiado a número: ' || codi);
  DBMS_OUTPUT.PUT_LINE('la profesión en mayúscula: ' || profe);
  DBMS_OUTPUT.PUT_LINE('la cantidad de caracteres de libro: ' || lib);
  DBMS_OUTPUT.PUT_LINE('el sueldo es: ' || suel);
END;