/*Actividad 1: */


--1.Instala MySql. Pon como contraseña de root la palabra ‘austria’.

apt-get install mysql-server mysql-client

--2. Accede como root

mysql -u root -p

--3. Implementa la siguiente base de datos que llamaremos “Clase_primera” (pista: se requieren tres tablas):

CREATE DATABASE Clase_primera;
use Clase_primera;

CREATE TABLE PROFESOR(
    DNI_Profesor CHAR(9) PRIMARY KEY,
    Nombre_Profesor VARCHAR(20) NOT NULL,
    Edad_Profesor INT NOT NULL
    );
CREATE TABLE ALUMNO(
    DNI_Alumno CHAR(9) PRIMARY KEY,
    Nombre_Alumno VARCHAR(20) NOT NULL,
    Direccion_Alumno Varchar(30) NOT NULL
    );
CREATE TABLE P_A(
    DNI_Profesor CHAR(9),
    DNI_Alumno CHAR(9),
    PRIMARY KEY (DNI_Profesor, DNI_Alumno),
    FOREIGN KEY (DNI_Profesor) REFERENCES PROFESOR (DNI_Profesor),
    FOREIGN KEY (DNI_Alumno) REFERENCES ALUMNO (DNI_Alumno)
    );

--4. Inserta en PROFESOR tres filas: (22334455H, Basilio Vicente, 56), (15162324U, Jacinto Velarte, 45), (34324455Y, Jaime Villanueva, 29)

INSERT INTO PROFESOR VALUES ("22334455H", "Basilio Vicente", 56);
INSERT INTO PROFESOR VALUES ("15162324U", "Jacinto Velarte", 45);
INSERT INTO PROFESOR VALUES ("34324455Y", "Jaime Villanueva", 29);

--5. Inserta en ALUMNO tres filas: (12223321F, Francisco Espejo, c/Bilbao 89 2º 1ª), (45674543R, Imma Fernández, c/San Patricio 17 11º 3ª), (56778987P, Almudena Ochoa, c/Eustaquio Ovidio 54º 1ª)

INSERT INTO ALUMNO VALUES ("12223321F", "Francisco Espejo", "c/Bilbao 89 2º 1ª");
INSERT INTO ALUMNO VALUES ("45674543R", "Imma Fernández", "c/San Patricio 17 11º 3ª");
INSERT INTO ALUMNO VALUES ("56778987P", "Almudena Ochoa", "c/Eustaquio Ovidio 54º 1ª");

--6. Observa mediante consultas sencillas que todos los campos se han insertado correctamente.

SELECT * FROM PROFESOR;
SELECT * FROM ALUMNO;

--7. Asocia los alumnos Francisco Espejo y Imma Fernández con el profesor Basilio Vicente.

INSERT INTO P_A VALUES ("22334455H","12223321F");
INSERT INTO P_A VALUES ("22334455H","45674543R");

--8. Modifica la edad del profesor Jaime Villanueva a 31.

UPDATE PROFESOR set Edad_Profesor=31 where dni_Profesor="34324455Y";

--9. Elimina la alumna Almudena Ochoa.

DELETE FROM ALUMNO where DNI_Alumno="56778987P";

----------------------------------------------------------------------------------------------------------------------


/*Actividad 2*/
--Realiza las siguientes consultas:

--Muestra profesores con una edad superior a 50.

select * from PROFESOR where Edad_Profesor > 50;

--Muestra el número total de alumnos.

select count(*) FROM ALUMNO;

--Muestra cuántos alumnos tiene a su cargo el profesor Basilio Vicente.
--> Si sabem el seu DNI:
    select count(*) from P_A where DNI_Profesor='22334455H';
--> Si ho volem buscar explicitament amb el nom:
    select count(*) FROM P_A NATURAL JOIN PROFESOR where upper(Nombre_Profesor)='BASILIO VICENTE';

--Ordena de mayor a menor los alumnos en función de la longitud (número de caracteres) de su dirección. Muestra el nombre del alumno y dicha longitud.

SELECT Nombre_Alumno, LENGTH(Direccion_Alumno) AS Longitud FROM ALUMNO ORDER BY LENGTH(Direccion_Alumno) DESC;


--Muestra los alumnos cuyo nombre empiece por la letra F y tengan un 2 entre los dígitos del DNI

SELECT * from ALUMNO where substr(Nombre_Alumno,1,1)='F' and INSTR(DNI_Alumno,'2')>0;

--Ordena los profesores en función del número de alumnos que tienen a su cargo

SELECT Nombre_Profesor from P_A RIGHT OUTER JOIN PROFESOR USING(DNI_Profesor) group by DNI_Profesor order by COUNT(DNI_Alumno) DESC;

--¿Puedes cambiar el DNI del profesor Basilio Vicente?¿Por qué?

/*No, ja que la taula P_A al ser N-N, necessita com a claus principals els DNI_Profesor i els DNI_alumnes, i aquestes a la vegada seran claus foraneas de les taules de Alumnes i profesor. Com que el profesor Basilio ja te un insert a la taula de P-A amb algun alumne, no podem modificar el seu DNI ja que no quadraria amb l'entrada que te a la taula P-A*/

--Añade la columna ‘Edad_Alumno’ a la tabla Alumno. Defínela como entero muy pequeño y positivo

ALTER TABLE ALUMNO ADD Edad_Alumno SMALLINT unsigned;

--Muestra cuántos alumnos tienen a su cargo cada profesor. Ordena según número de alumnos

SELECT Nombre_Profesor, count(DNI_ALUMNO) from P_A RIGHT OUTER JOIN PROFESOR USING(DNI_Profesor) group by DNI_Profesor order by COUNT(DNI_Alumno) DESC;


--Muestra solo aquellos profesores que tengan más de un alumno a su cargo

SELECT Nombre_Profesor, count(DNI_ALUMNO) from P_A RIGHT OUTER JOIN PROFESOR USING(DNI_Profesor) group by DNI_Profesor HAVING Count(DNI_ALUMNO) > 1 order by COUNT(DNI_Alumno) DESC ;



/*Actividad 3 */

--Crea un usuario que pueda ejecutar procedimientos. Llámalo manolo.

CREATE USER Manolo IDENTIFIED BY 'austria';
GRANT Execute ON Clase_Primera.* TO 'Manolo'@'localHost';
FLUSH PRIVILEGES;

--Crea un procedimiento que devuelva el número de alumnos que hay en la tabla ALUMNO. Lo llamaremos proc1

DELIMITER $$
CREATE PROCEDURE proc1 ()
BEGIN
    select count(*) AS num_alums from ALUMNO;
END
$$

call proc1;

--¿Puede manolo ejecutar proc1?

Sí, ya que le hemos dado los privilegios anteriormente.

--Crea una función de devuelva el apellido del nombre del alumno que le pases como argumento a la fución. Así cuando el argumento de entrada sea Imma, devolverá Fernández.

DELIMITER $$
CREATE FUNCTION select_apellido (nombre VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
    DECLARE apellido VARCHAR(20);
    SET apellido = (SELECT REPLACE(Nombre_Alumno, concat(nombre, ' '), '') AS APE_ALUM FROM ALUMNO WHERE substr(Nombre_Alumno, 1, length(nombre)) = nombre);
    RETURN apellido;
END;
$$

--Crea un dispardor que impida cambiar los DNIs de los profesores.

CREATE TRIGGER dni_trigger
BEFORE UPDATE ON PROFESOR
FOR EACH ROW
begin
    DECLARE error varchar(100);
    SET error = 'Error. No pots canviar el DNI d!un professor';
    IF NEW.DNI_Profesor != OLD.DNI_Profesor THEN
        SIGNAL SQLSTATE '45000' set message_text = error;
    END IF;
END;