Throwback to year 2012 at university, SQL exercise

sv.sql

/*****************
* INICIALIZATION *
******************/

DROP TABLE IMPARTE;
DROP TABLE CALIFICACION;
DROP TABLE USUARIO;
DROP TABLE ALUMNO;
DROP TABLE ASIGNATURA;
DROP TABLE MASTER;

/***********************
* DEFINICION DE TABLAS *
************************/

CREATE TABLE ALUMNO (
  DNI               VARCHAR2(20)          ,
  nombre            VARCHAR2(20)  NOT NULL,
  apellido1         VARCHAR2(20)  NOT NULL,
  apellido2         VARCHAR2(20)  NOT NULL,
  fecha_nacimiento  DATE          NOT NULL,
  direccion         VARCHAR2(20)  NOT NULL,

  PRIMARY KEY (DNI)
);

CREATE TABLE USUARIO (
  DNI       VARCHAR2(20)          ,
  login     VARCHAR2(10)  NOT NULL,
  password  DECIMAL(8, 0) NOT NULL,

  PRIMARY KEY (DNI)               ,

  UNIQUE (login)                  ,

  FOREIGN KEY (DNI) REFERENCES ALUMNO
    ON DELETE CASCADE
);

CREATE TABLE ASIGNATURA (
  codigo_asignatura CHAR(4)               ,
  nombre            VARCHAR2(40)  NOT NULL,
  creditos          REAL           NOT NULL,

  PRIMARY KEY (codigo_asignatura)         ,
  
  UNIQUE (nombre)
);

CREATE TABLE CALIFICACION (
  DNI                 VARCHAR2(20)                        ,
  codigo_asignatura   CHAR(4)                             ,
  fecha_convocatoria  DATE                                ,
  nota_teoria         INTEGER       NOT NULL              ,
  nota_practica       INTEGER       NOT NULL              ,

  PRIMARY KEY (DNI, codigo_asignatura, fecha_convocatoria),

  FOREIGN KEY (DNI) REFERENCES ALUMNO
    ON DELETE CASCADE                                     ,
  FOREIGN KEY (codigo_asignatura) REFERENCES ASIGNATURA
    ON DELETE CASCADE
);

CREATE TABLE MASTER (
  codigo_master   CHAR(4)               ,
  nombre          VARCHAR2(40)  NOT NULL,
  precio          REAL                  ,

  PRIMARY KEY (codigo_master)           ,
    
  UNIQUE (nombre)
);

CREATE TABLE IMPARTE (
  codigo_master       CHAR(4)                   ,
  codigo_asignatura   CHAR(4)                   ,

  PRIMARY KEY (codigo_master, codigo_asignatura),

  FOREIGN KEY (codigo_master) REFERENCES MASTER
    ON DELETE CASCADE                           ,
  FOREIGN KEY (codigo_asignatura) REFERENCES ASIGNATURA
    ON DELETE CASCADE
);

/**************
* EJERCICIO 4 *
***************/

SET SERVEROUTPUT ON SIZE 1000000;

CREATE OR REPLACE TRIGGER crear_usuario
  AFTER INSERT ON ALUMNO
FOR EACH ROW
DECLARE
  v_prefix  CHAR(1);
  v_sufix   VARCHAR2(7);
  v_login   CHAR(8);
  v_seq     BINARY_INTEGER;
  v_aux     BINARY_INTEGER;
  v_valido  BOOLEAN DEFAULT FALSE;
  v_pass    USUARIO.password%TYPE;
BEGIN
  v_prefix := lower(substr(:new.nombre, 1, 1));
  v_sufix  := lower(substr(:new.apellido1, 1, 7));
  
  -- Se introducen 0s hasta que la longitud del apellido sea igual a 7
  WHILE length(v_sufix) < 7 LOOP
    v_sufix := v_sufix || '0';
  END LOOP;
  
  v_login := v_prefix || v_sufix;
  
  -- Se comprueba la unicidad del login
  -- TODO esto ahora mismo no soporta el que se tenga que introducir un número 
  -- de secuencia de más de un dígito
  WHILE NOT v_valido LOOP
    v_seq := 0;
    SELECT count(DNI) INTO v_aux FROM USUARIO WHERE login = v_login;
    IF v_aux <> 0 THEN
      -- Existe otro usuario con ese login
      v_seq    := v_seq + 1;
      v_login  := substr(v_login, 1, 7) || to_char(v_seq);
    ELSE
      v_valido := TRUE;
    END IF;
  END LOOP;
      
  -- Generamos un número aleatorio de 8 dígitos para utilizar como password
  v_pass := DBMS_RANDOM.VALUE(10000000, 99999999);
  
  -- DBMS_OUTPUT.PUT_LINE('Insert in users table: '||v_login||' '||v_pass);
  
  INSERT INTO USUARIO VALUES (:new.DNI, v_login, v_pass);
END;
/

INSERT INTO ALUMNO VALUES ('11111111-A', 'Juan', 'Salamanca', 'Mora', 
    '12/01/1974', 'C/ Abadía 20');
INSERT INTO ALUMNO VALUES ('22222222-B', 'Javier', 'Salamanca', 'Castillo', 
    '30/09/1975', 'C/ Teruel 15');
INSERT INTO ALUMNO VALUES ('33333333-C', 'Ana', 'Pazo', 'Blanco', 
    '20/02/1980', 'C/ Europa 4');
    
/**************
* EJERCICIO 5 *
***************/

-- Inserciones en MASTER, ASIGNATURA e IMPARTE

INSERT INTO MASTER VALUES ('M001', 'Máster en Administración de Empresas', NULL);
INSERT INTO MASTER VALUES ('M002', 'Máster en Programación', NULL);
INSERT INTO MASTER VALUES ('M003', 'Máster en Dirección de Proyectos', NULL);
INSERT INTO MASTER VALUES ('M004', 'Máster en Sistemas Informáticos', NULL);

INSERT INTO ASIGNATURA VALUES ('A001', 'Contabilidad Analítica', 10);
INSERT INTO ASIGNATURA VALUES ('A002', 'Planificación y Gestión de Activos', 25);
INSERT INTO ASIGNATURA VALUES ('A003', 'Microeconomía', 15);
INSERT INTO ASIGNATURA VALUES ('A004', 'Fundamentos de Programación', 5);
INSERT INTO ASIGNATURA VALUES ('A005', 'Estructura de Datos', 5);
INSERT INTO ASIGNATURA VALUES ('A006', 'Compiladores e Intérpretes', 10);
INSERT INTO ASIGNATURA VALUES ('A007', 'Diseño de Bases de Datos', 10);
INSERT INTO ASIGNATURA VALUES ('A008', 'Metodologías de Analísis', 5);
INSERT INTO ASIGNATURA VALUES ('A009', 'Planificación de Proyectos', 5);
INSERT INTO ASIGNATURA VALUES ('A010', 'Ingeniería del Sotfware', 10);
INSERT INTO ASIGNATURA VALUES ('A011', 'Seguimiento de Proyectos', 20);
INSERT INTO ASIGNATURA VALUES ('A012', 'Calidad y Control de Cambios', 10);
INSERT INTO ASIGNATURA VALUES ('A013', 'Programación', 5);
INSERT INTO ASIGNATURA VALUES ('A014', 'Análisis y Diseño de SI', 5);
INSERT INTO ASIGNATURA VALUES ('A015', 'Evolución de las TIC', 15);
INSERT INTO ASIGNATURA VALUES ('A016', 'Aplicaciones Informáticas', 5);
INSERT INTO ASIGNATURA VALUES ('A017', 'Ingeniería para WEB', 10);

INSERT INTO IMPARTE VALUES ('M001', 'A001');
INSERT INTO IMPARTE VALUES ('M001', 'A002');
INSERT INTO IMPARTE VALUES ('M001', 'A003');
INSERT INTO IMPARTE VALUES ('M002', 'A004');
INSERT INTO IMPARTE VALUES ('M002', 'A005');
INSERT INTO IMPARTE VALUES ('M002', 'A006');
INSERT INTO IMPARTE VALUES ('M002', 'A007');
INSERT INTO IMPARTE VALUES ('M003', 'A008');
INSERT INTO IMPARTE VALUES ('M003', 'A009');
INSERT INTO IMPARTE VALUES ('M003', 'A010');
INSERT INTO IMPARTE VALUES ('M003', 'A011');
INSERT INTO IMPARTE VALUES ('M003', 'A012');
INSERT INTO IMPARTE VALUES ('M004', 'A013');
INSERT INTO IMPARTE VALUES ('M004', 'A014');
INSERT INTO IMPARTE VALUES ('M004', 'A015');
INSERT INTO IMPARTE VALUES ('M004', 'A016');
INSERT INTO IMPARTE VALUES ('M004', 'A017');

CREATE OR REPLACE PROCEDURE update_master_prices(
  arg_credit_price IN MASTER.precio%TYPE) IS
  
  CURSOR c_master IS
    SELECT *
    FROM MASTER
    FOR UPDATE OF precio;
 
  v_total_credits ASIGNATURA.creditos%TYPE;
  
BEGIN
  FOR vc_master IN c_master LOOP
  
    SELECT SUM(creditos)
    INTO v_total_credits
    FROM ASIGNATURA, IMPARTE
    WHERE (ASIGNATURA.codigo_asignatura = IMPARTE.codigo_asignatura AND
          IMPARTE.codigo_master = vc_master.codigo_master);
              
    UPDATE MASTER
    SET precio = v_total_credits * arg_credit_price
    WHERE CURRENT OF c_master;
    
  END LOOP;
END;
/

EXEC update_master_prices(20);

/*
SELECT *
FROM MASTER
WHERE precio = (SELECT MAX(precio) FROM MASTER);
*/

DECLARE

  CURSOR c_expensive_masters IS 
    SELECT *
    FROM MASTER
    WHERE precio = (SELECT MAX(precio) FROM MASTER);
    
BEGIN

  FOR v_exp_master IN c_expensive_masters LOOP
    DBMS_OUTPUT.PUT_LINE('El Máster más caro se llama '||v_exp_master.nombre);
  END LOOP;

END;
/

DECLARE
  v_avg_credits   ASIGNATURA.creditos%TYPE;
  v_avg_price     MASTER.precio%TYPE;
  v_credit_price  MASTER.precio%TYPE;
BEGIN
  SELECT AVG(creditos)
  INTO v_avg_credits
  FROM ASIGNATURA;
  
  v_avg_price := v_avg_credits * &v_credit_price;
  
  -- DBMS_OUTPUT.PUT_LINE('El # de créditos medio por asignatura es '||v_avg_credits);
  DBMS_OUTPUT.PUT_LINE('El precio medio por asignatura es de '||v_avg_price||'€');
END;
/

/**************
* EJERCICIO 6 *
***************/

CREATE OR REPLACE PROCEDURE insert_calificacion(
  arg_DNI             IN CALIFICACION.DNI%TYPE,
  arg_cod_asignatura  IN CALIFICACION.codigo_asignatura%TYPE,
  arg_nota_teoria     IN CALIFICACION.nota_teoria%TYPE,
  arg_nota_practica   IN CALIFICACION.nota_practica%TYPE,
  arg_fecha           IN CALIFICACION.fecha_convocatoria%TYPE DEFAULT SYSDATE
  ) IS
e_max_convocatorias EXCEPTION;
v_num_convocatorias INTEGER;
BEGIN
  
  -- Devolver una excepción si el número de convocatorias a las que se ha 
  -- presentado un alumno para una asignatura es mayor de 4

  -- Se consulta el número de veces que el alumno se ha presentado a la 
  -- asignatura
  SELECT COUNT(*)
  INTO v_num_convocatorias
  FROM CALIFICACION
  WHERE (DNI = arg_dni AND codigo_asignatura = arg_cod_asignatura); 

  IF v_num_convocatorias >= 4 THEN
    RAISE e_max_convocatorias;
  END IF;

  INSERT INTO CALIFICACION VALUES(arg_DNI, arg_cod_asignatura, arg_fecha,
      arg_nota_teoria, arg_nota_practica);
      
EXCEPTION
  WHEN e_max_convocatorias THEN
    RAISE_APPLICATION_ERROR(-20000, 'Un alumno no se puede presentar más de 4 veces a una misma asignatura');
END;
/

EXEC insert_calificacion('11111111-A', 'A001', 5, 6, '01/12/2011');
EXEC insert_calificacion('11111111-A', 'A002', 4, 6, '01/12/2011');
EXEC insert_calificacion('11111111-A', 'A002', 5, 6, '01/02/2012');
EXEC insert_calificacion('11111111-A', 'A003', 2, 8, '01/12/2011');
EXEC insert_calificacion('22222222-B', 'A001', 5, 5, '01/12/2011');
EXEC insert_calificacion('22222222-B', 'A002', 6, 5, '01/12/2011');
EXEC insert_calificacion('33333333-C', 'A001', 6, 4, '01/12/2011');
EXEC insert_calificacion('33333333-C', 'A002', 5, 6, '01/12/2011');
EXEC insert_calificacion('33333333-C', 'A003', 5, 5, '01/12/2011');

-- Se añaden algunas inserciones más para comprobar que la excepción se eleva
/*
EXEC insert_calificacion('11111111-A', 'A001', 2, 3);
EXEC insert_calificacion('11111111-A', 'A001', 1, 4, SYSDATE+365);
EXEC insert_calificacion('11111111-A', 'A001', 4, 4, SYSDATE+2*365);
EXEC insert_calificacion('11111111-A', 'A001', 4, 4, SYSDATE-365);
*/

/**************
* EJERCICIO 7 *
***************/

/** Empleando cursores */

/*
CREATE OR REPLACE FUNCTION count_passed_students
RETURN BINARY_INTEGER IS

  CURSOR c_trials_student_subject(
      v_cod_asignatura  ASIGNATURA.codigo_asignatura%TYPE,
      v_dni             ALUMNO.DNI%TYPE
  ) IS
    SELECT CALIFICACION.fecha_convocatoria, CALIFICACION.nota_teoria, CALIFICACION.nota_practica
    FROM ASIGNATURA, ALUMNO, CALIFICACION
    WHERE (ALUMNO.DNI = CALIFICACION.DNI AND 
            ASIGNATURA.codigo_asignatura = CALIFICACION.codigo_asignatura);

BEGIN

  RETURN 0;

END;
/

DECLARE
  v_ret BINARY_INTEGER;
BEGIN
  v_ret := count_passed_students();
END;
/
*/

SELECT *
FROM CALIFICACION;

DROP VIEW Primeras_Convocatorias;
DROP VIEW Asignaturas_Presentadas;
DROP VIEW Asignaturas_Aprobadas;

/** Sin utilizar cursores */

CREATE VIEW Primeras_Convocatorias AS (
  SELECT *
  FROM CALIFICACION
  WHERE (dni, codigo_asignatura, fecha_convocatoria) IN (
    SELECT dni, codigo_asignatura, MIN(fecha_convocatoria)
    FROM CALIFICACION
    GROUP BY dni, codigo_asignatura));

CREATE VIEW Asignaturas_Presentadas AS (
  SELECT dni, COUNT(*) total
  FROM Primeras_Convocatorias
  GROUP BY dni);

CREATE VIEW Asignaturas_Aprobadas AS (  
  SELECT dni, COUNT(*) total
  FROM Primeras_Convocatorias
  WHERE nota_teoria >= 5 AND nota_practica >= 5
  GROUP BY dni);

CREATE OR REPLACE FUNCTION count_first_passed2
RETURN BINARY_INTEGER IS
  v_ret BINARY_INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO v_ret
  FROM Asignaturas_Presentadas ap, Asignaturas_Aprobadas aa
  WHERE ap.dni = aa.dni AND ap.total = aa.total;
  
  return v_ret;
END;
/

CREATE OR REPLACE PROCEDURE Dummy IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(count_first_passed2() || ' alumno(s) ha(n) aprobado a la primera');
END;
/

SET SERVEROUTPUT ON;
EXEC Dummy;

I thought it was more or less OK except for exercise 7 which was quite WIP.