Untitled

-- # SQL

/*1. Crear el Script que crea la BBDD para (1,5 puntos):
	a. Crear las BBDD barcos y apuntar a esa BBDD.
	b. Crea las 3 tablas con los tipos de datos que se muestran y las relaciones que se establecen.
	c. ‘Compannia’ es un dato no nulo que por defecto se crea a 'Naviera Mediterránea', al igual ‘Numparadas’ que es un dato entero no nulo que por defecto se crea a 5.
	d. Crea dos índices en la tabla cruceros sobre el campo referencia y sobre la tabla puertos sobre el campo código y llámales respectivamente: crucero_idx y puertos_idx .
	e. Modifica las tablas con ALTER table y crea las claves ajenas con dos nombres de restricción llamados referencia_ibfk_1 y codigo_ibfk_1 que corresponden a la referencia sobre la tabla recorridos y sobre la tabla puertos y que en ambos casos se actualice en borrado y actualización en cascada.

2. Crea una vista llamada ‘mostrarcompannia’ y ejecútala, en la que muestre todas las compañías y sus precios ordenados por precio de mayor a menor y dónde el país sea Grecia y tengan más de 5 paradas (0,5 puntos).

3. Crea una vista llamada ‘mostrarbaratos ’ y ejecútala muestra los cruceros más baratos cuyo puerto sea ‘Barcelona’ y que tengan más de 5 paradas (0,5 puntos):

4. Crea una vista llamada ‘incrementar25’, donde incremente un 25% el precio de los cruceros cuyos números de paradas sean mayores de 5 (0,5 puntos); has de gestionar una transacción y arrancar y al final has de hacer un rollback al final. Es importante que antes de lanzar la transacción establezcas la variable global a que maneja los COMMITS de forma que quites la validación de transacción automática (1 punto):*/
----------------------------------------------------
-- 1. Crear el Script que crea la BBDD para (1,5 puntos):
-- 	a. Crear las BBDD barcos y apuntar a esa BBDD.
-- 	b. Crea las 3 tablas con los tipos de datos que se muestran y las relaciones que se establecen.
-- 	c. ‘Compannia’ es un dato no nulo que por defecto se crea a 'Naviera Mediterránea', al igual ‘Numparadas’ que es un dato entero no nulo que por defecto se crea a 5

CREATE DATABASE IF NOT EXISTS barcos;
USE barcos;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS cruceros (
    referencia VARCHAR(30) NOT NULL PRIMARY KEY,
    compannia VARCHAR(255) NOT NULL DEFAULT 'Naviera Mediterránea',
    precio FLOAT(8,2) NOT NULL
);

CREATE TABLE IF NOT EXISTS recorridos (
    referencia VARCHAR(30) NOT NULL,
    codigo VARCHAR(30) NOT NULL PRIMARY KEY,
    Numparadas INT NOT NULL DEFAULT 5
);

CREATE TABLE IF NOT EXISTS puertos (
    codigo VARCHAR(30) NOT NULL PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    pais VARCHAR(50) NOT NULL
);

-- Crear índices
CREATE INDEX crucero_idx ON cruceros (referencia);
CREATE INDEX puertos_idx ON puertos (codigo);

-- Insertar datos en las tablas
INSERT INTO cruceros VALUES 
    ('M2024ISLASGRIEGAS', 'Naviera Norte', 3500.30),
    ('M2024ISLASCIES', 'Naviera Tunez', 1500.80),
    ('M2024ISLASCORATAS', 'Sea Mar', 2500.20),
    ('M2024ISLASMALTA', 'Naviera Norte 1', 1800.89);

INSERT INTO recorridos VALUES 
    ('M2024ISLASGRIEGAS', 'Lujo Islas Griegas', 12),
    ('M2024ISLASCIES', 'Tunez y sus desiertos', 10),
    ('M2024ISLASCORATAS', 'Croacia y sus encantos', 2),
    ('M2024ISLASMALTA', 'Malta, Gozo y Comino', 8);

INSERT INTO puertos VALUES 
    ('Lujo Islas Griegas', 'Naviera Norte', 'Grecia'),
    ('Tunez y sus desiertos', 'Naviera Tunez', 'Tunez'),
    ('Croacia y sus encantos', 'Sea Mar', 'Croacia'),
    ('Malta, Gozo y Comino', 'Naviera Norte 1', 'Malta');

-- Añadir restricciones de claves foráneas
ALTER TABLE recorridos
    ADD CONSTRAINT referencia_ibfk_1 FOREIGN KEY (referencia) REFERENCES cruceros (referencia) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE puertos
    ADD CONSTRAINT codigo_ibfk_1 FOREIGN KEY (codigo) REFERENCES recorridos (codigo) ON DELETE CASCADE ON UPDATE CASCADE;

SET FOREIGN_KEY_CHECKS=1;

-- Crear y ejecutar la vista 'mostrarcompannia'
CREATE OR REPLACE VIEW mostrarcompannia AS
SELECT compannia, precio
FROM cruceros AS cru
JOIN recorridos AS re ON cru.referencia = re.referencia
JOIN puertos AS pue ON pue.codigo = re.codigo
WHERE pue.pais = 'Grecia' AND re.numparadas > 5
ORDER BY precio DESC;

SELECT * FROM mostrarcompannia;

-- Crear y ejecutar la vista 'mostrarbaratos'
CREATE OR REPLACE VIEW mostrarbaratos AS
SELECT pue.pais, rec.numparadas
FROM puertos AS pue
JOIN recorridos AS rec ON pue.codigo = rec.codigo
JOIN cruceros AS cru ON cru.referencia = rec.referencia
WHERE pue.pais = 'Barcelona' AND rec.numparadas > 5
ORDER BY cru.precio ASC;

SELECT * FROM mostrarbaratos;

-- Incrementar el precio de los cruceros en un 25% y gestionar la transacción
SET autocommit = 0;
SELECT @@autocommit;

START TRANSACTION;
SELECT * FROM barcos.cruceros;

UPDATE cruceros
SET precio = precio * 1.25
WHERE referencia IN (
    SELECT referencia
    FROM recorridos
    WHERE numparadas >= 5
);

SELECT * FROM barcos.cruceros;

CREATE OR REPLACE VIEW incrementar25 AS
SELECT compannia, precio, precio * 1.25 AS nuevo_precio, numparadas
FROM cruceros AS cr
JOIN recorridos AS re ON cr.referencia = re.referencia
WHERE re.numparadas > 5;

SELECT * FROM incrementar25;

ROLLBACK;
COMMIT;

-- Reactivar los commits automáticos
SELECT @@autocommit;
-- → consultar SET FOREIGN_KEY_CHECKS=1;

---------------------------------------------------------------------------------
-- PLSQL

/*1.   Crea un procedimiento que reciba un número de empleado y una cadena correspondiente a su nuevo departamento (departament_id) como parámetros. El procedimiento deberá localizar el empleado, modificar su departamento por el nuevo y visualizar los cambios realizados. Pero antes se deberán hacer las siguientes comprobaciones (2,5 puntos): 
	1. Se deberá comprobar si el id del departamento existe. Esta tarea la realizará una función aparte a la que se le pasará el departamento por parámetro y retornará un booleano. Si el departamento no existe, el procedimiento informará con un mensaje por consola.
	2. Crea también un bloque anónimo donde ejecutar el procedimiento con los valores de los argumentos recogidos en variables de sustitución.*/
	
CREATE OR REPLACE FUNCTION department_exists(department_id NUMBER) RETURN BOOLEAN IS 
    dept_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO dept_count
    FROM departments
    WHERE department_id = department_id;

    IF dept_count > 0 THEN 
        RETURN TRUE;
    ELSE 
        RETURN FALSE;
    END IF;
END;
/

CREATE OR REPLACE PROCEDURE update_employee_department (
    emp_id IN NUMBER, 
    new_dept_id IN VARCHAR2
) IS 
    emp_exists EXCEPTION;
    dept_exists BOOLEAN;
BEGIN 
    -- Comprobar si el departamento existe
    dept_exists := department_exists(new_dept_id);

    IF dept_exists THEN 
        -- Actualizar el departamento del empleado
        UPDATE employees
        SET department_id = new_dept_id
        WHERE employee_id = emp_id;

        COMMIT;

        IF SQL%ROWCOUNT = 0 THEN 
            RAISE emp_exists;
        END IF;

        -- Mostrar los cambios realizados
        DBMS_OUTPUT.PUT_LINE('Empleado ' || emp_id || ' movido al departamento ' || new_dept_id);
    ELSE 
        DBMS_OUTPUT.PUT_LINE('El departamento ' || new_dept_id || ' no existe.');
    END IF;

EXCEPTION
    WHEN emp_exists THEN 
        DBMS_OUTPUT.PUT_LINE('El empleado ' || emp_id || ' no existe.');
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('Ocurrió un error: ' || SQLERRM);
END;
/

DECLARE 
    v_emp_id NUMBER;
    v_new_dept_id VARCHAR2(10);
BEGIN 
    -- Solicitar el número de empleado
    v_emp_id := &emp_id;

    -- Solicitar el nuevo departamento
    v_new_dept_id := '&new_dept_id';

    -- Ejecutar el procedimiento
    update_employee_department(v_emp_id, v_new_dept_id);
END;
/

/*2.   Crea un trigger que inserte un registro en una tabla nueva llamada SALMAX_AUDIT cada vez que modificamos el salario MÁXIMO de un trabajo. Sólo se realizará la operación si el salario máximo que se va a modificar difiere del nuevo. La tabla SALMAX_AUDIT tendrá los siguientes cinco campos (1,5 puntos):
	1. Fecha y hora en el que se hace la actualización.
	2. El JOB_ID y el JOB_TITLE sobre el que se cambia el salario máximo.
	3. El salario máximo anterior y el máximo salario nuevo.*/
	
CREATE TABLE SALMAX_AUDIT (
    audit_date TIMESTAMP,
    job_id VARCHAR2(10),
    job_title VARCHAR2(35),
    old_max_salary NUMBER,
    new_max_salary NUMBER
);

CREATE OR REPLACE TRIGGER trg_audit_max_salary 
AFTER UPDATE OF max_salary ON jobs 
FOR EACH ROW 
WHEN (OLD.max_salary != NEW.max_salary) 
BEGIN
    INSERT INTO SALMAX_AUDIT (
        audit_date,
        job_id,
        job_title,
        old_max_salary,
        new_max_salary
    )
    VALUES (
        SYSTIMESTAMP,
        :NEW.job_id,
        (SELECT job_title FROM jobs WHERE job_id = :NEW.job_id),
        :OLD.max_salary,
        :NEW.max_salary
    );
END;
/