API tools faq
paste
DekkerBass

SQL Simulacro

DekkerBass
Jan 26th, 2021 (edited)
1,055
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 18.30 KB | None | 0 0
raw download clone embed print report
  1. -- Definicion del procedimiento de creacion de tablas
  2. DELIMITER //
  3. CREATE OR REPLACE PROCEDURE
  4.     createTables()
  5. BEGIN
  6.     SET FOREIGN_KEY_CHECKS=0;
  7.     DROP TABLE IF EXISTS Degrees;
  8.     DROP TABLE IF EXISTS Subjects;
  9.     DROP TABLE IF EXISTS Groups;
  10.     DROP TABLE IF EXISTS InternalStudents;
  11.     DROP TABLE IF EXISTS Students;
  12.     DROP TABLE IF EXISTS GroupsStudents;
  13.     DROP TABLE IF EXISTS Grades;
  14.     DROP TABLE IF EXISTS Offices;
  15.     DROP TABLE IF EXISTS Classrooms;
  16.     DROP TABLE IF EXISTS Departments;
  17.     DROP TABLE IF EXISTS TutoringHours;
  18.     DROP TABLE IF EXISTS Publicaciones;
  19.     DROP TABLE IF EXISTS TeachingLoads;
  20.     DROP TABLE IF EXISTS Appointments;
  21.     DROP TABLE IF EXISTS Professors;
  22.     SET FOREIGN_KEY_CHECKS=1;
  23.  
  24.     CREATE TABLE Offices (
  25.         officeId INT NOT NULL AUTO_INCREMENT,
  26.         name VARCHAR(60) NOT NULL,
  27.         FLOOR INT NOT NULL,
  28.         capacity INT NOT NULL,
  29.         PRIMARY KEY (officeId),
  30.         CONSTRAINT invalidFloor CHECK (FLOOR >= 0),
  31.         CONSTRAINT invalidCapacity CHECK (capacity > 0),
  32.         UNIQUE (name)
  33.     );
  34.  
  35.    
  36.     CREATE TABLE Classrooms (
  37.         classroomId INT NOT NULL AUTO_INCREMENT,
  38.         name VARCHAR(60) NOT NULL UNIQUE,
  39.         FLOOR INT NOT NULL,
  40.         capacity INT NOT NULL,
  41.         hasProjector BOOLEAN NOT NULL,
  42.         hasLoudSpeakers BOOLEAN NOT NULL,
  43.         PRIMARY KEY (classroomId),
  44.         CONSTRAINT invalidFloor CHECK (FLOOR >= 0),
  45.         CONSTRAINT invalidCapacity CHECK (capacity > 0)
  46.     );
  47.  
  48.     CREATE TABLE Departments (
  49.         departmentId INT NOT NULL AUTO_INCREMENT,
  50.         name VARCHAR(60) NOT NULL UNIQUE,
  51.         PRIMARY KEY (departmentId)
  52.     );
  53.  
  54.     CREATE TABLE Degrees(
  55.         degreeId INT NOT NULL AUTO_INCREMENT,
  56.         name VARCHAR(60) NOT NULL UNIQUE,
  57.         years INT DEFAULT 4 NOT NULL,
  58.         PRIMARY KEY (degreeId),
  59.         CONSTRAINT invalidDegreeYear CHECK (years >=3 AND years <=5)
  60.     );
  61.  
  62.     CREATE TABLE Subjects(
  63.         subjectId INT NOT NULL AUTO_INCREMENT,
  64.         name VARCHAR(100) NOT NULL UNIQUE,
  65.         acronym VARCHAR(8) NOT NULL UNIQUE,
  66.         credits INT NOT NULL,
  67.         course INT NOT NULL,
  68.         TYPE VARCHAR(20) NOT NULL,
  69.         degreeId INT NOT NULL,
  70.         departmentId INT NOT NULL,
  71.         PRIMARY KEY (subjectId),
  72.         FOREIGN KEY (degreeId) REFERENCES Degrees (degreeId),
  73.         FOREIGN KEY (departmentId) REFERENCES Departments (departmentId),
  74.         CONSTRAINT negativeSubjectCredits CHECK (credits > 0),
  75.         CONSTRAINT invalidSubjectCourse CHECK (course > 0 AND course < 6),
  76.         CONSTRAINT invalidSubjectType CHECK (TYPE IN ('Formacion Basica',
  77.                                                                     'Optativa',
  78.                                                                     'Obligatoria'))
  79.     );
  80.  
  81.     CREATE TABLE Groups(
  82.         groupId INT NOT NULL AUTO_INCREMENT,
  83.         name VARCHAR(30) NOT NULL,
  84.         activity VARCHAR(20) NOT NULL,
  85.         YEAR INT NOT NULL,
  86.         subjectId INT NOT NULL,
  87.         classroomId INT NOT NULL,
  88.         PRIMARY KEY (groupId),
  89.         FOREIGN KEY (subjectId) REFERENCES Subjects (subjectId),
  90.         FOREIGN KEY (classroomId) REFERENCES Classrooms (classroomId),
  91.         UNIQUE (name, YEAR, subjectId),
  92.         CONSTRAINT negativeGroupYear CHECK (YEAR > 0),
  93.         CONSTRAINT invalidGroupActivity CHECK (activity IN ('Teoria',
  94.                                                                             'Laboratorio'))
  95.     );
  96.  
  97.     CREATE TABLE Students(
  98.         studentId INT NOT NULL AUTO_INCREMENT,
  99.         accessMethod VARCHAR(30) NOT NULL,
  100.         dni CHAR(9) NOT NULL UNIQUE,
  101.         firstName VARCHAR(100) NOT NULL,
  102.         surname VARCHAR(100) NOT NULL,
  103.         birthDate DATE NOT NULL,
  104.         email VARCHAR(250) NOT NULL UNIQUE,
  105.         PRIMARY KEY (studentId),
  106.         CONSTRAINT invalidStudentAccessMethod CHECK (accessMethod IN ('Selectividad',
  107.                                                                                         'Ciclo',
  108.                                                                                         'Mayor',
  109.                                                                                         'Titulado Extranjero'))
  110.     );
  111.    
  112.     CREATE TABLE InternalStudents (
  113.         internalStudentId INT NOT NULL AUTO_INCREMENT,
  114.         departmentId INT NOT NULL,
  115.         studentId INT NOT NULL,
  116.         academicYear INT NOT NULL,
  117.         duration INT,
  118.         PRIMARY KEY (internalStudentId),
  119.         FOREIGN KEY (studentId) REFERENCES students(studentId),
  120.         FOREIGN KEY (departmentId) REFERENCES departments(departmentId),
  121.         UNIQUE (studentId, academicYear),
  122.         CONSTRAINT invalidDuration CHECK (duration >= 3 && duration <= 9)
  123.     );
  124.  
  125.     CREATE TABLE GroupsStudents(
  126.         groupStudentId INT NOT NULL AUTO_INCREMENT,
  127.         groupId INT NOT NULL,
  128.         studentId INT NOT NULL,
  129.         PRIMARY KEY (groupStudentId),
  130.         FOREIGN KEY (groupId) REFERENCES Groups (groupId) ON DELETE CASCADE,
  131.         FOREIGN KEY (studentId) REFERENCES Students (studentId),
  132.         UNIQUE (groupId, studentId)
  133.     );
  134.  
  135.     CREATE TABLE Grades(
  136.         gradeId INT NOT NULL AUTO_INCREMENT,
  137.         VALUE DECIMAL(4,2) NOT NULL,
  138.         gradeCall INT NOT NULL,
  139.         withHonours BOOLEAN NOT NULL,
  140.         studentId INT NOT NULL,
  141.         groupId INT NOT NULL,
  142.         PRIMARY KEY (gradeId),
  143.         FOREIGN KEY (studentId) REFERENCES Students (studentId),
  144.         FOREIGN KEY (groupId) REFERENCES Groups (groupId) ON DELETE CASCADE,
  145.         CONSTRAINT invalidGradeValue CHECK (VALUE >= 0 AND VALUE <= 10),
  146.         CONSTRAINT invalidGradeCall CHECK (gradeCall >= 1 AND gradeCall <= 3),
  147.         CONSTRAINT duplicatedCallGrade UNIQUE (gradeCall, studentId, groupId)
  148.     );
  149.  
  150.     CREATE TABLE Professors (
  151.         professorId INT NOT NULL AUTO_INCREMENT,
  152.         officeId INT NOT NULL,
  153.         departmentId INT NOT NULL,
  154.         category VARCHAR(5) NOT NULL,
  155.         dni CHAR(9) NOT NULL UNIQUE,
  156.         firstName VARCHAR(100) NOT NULL,
  157.         surname VARCHAR(100) NOT NULL,
  158.         birthDate DATE NOT NULL,
  159.         email VARCHAR(250) NOT NULL UNIQUE,
  160.         PRIMARY KEY (professorId),
  161.         FOREIGN KEY (officeId) REFERENCES Offices (officeId),
  162.         FOREIGN KEY (departmentId) REFERENCES Departments (departmentId),
  163.         CONSTRAINT invalidCategory CHECK (category IN ('CU',
  164.                                                         'TU',
  165.                                                         'PCD',
  166.                                                         'PAD'))
  167.     );
  168.  
  169.     CREATE TABLE Publicaciones(
  170.         publicacionId INT NOT NULL AUTO_INCREMENT,
  171.         title VARCHAR(100) NOT NULL,
  172.         professorId INT NOT NULL,
  173.         totalAuthors INT NOT NULL,
  174.         publishDay DATE,
  175.         magazine VARCHAR (100) NOT NULL,
  176.         PRIMARY KEY (publicacionId),
  177.         UNIQUE (professorId, publishDay, magazine),
  178.         CONSTRAINT invalidAuthors CHECK (totalAuthors <= 10 && totalAuthors >= 1),
  179.         FOREIGN KEY (professorId) REFERENCES professors (professorId)
  180.         );
  181.        
  182.     CREATE TABLE TutoringHours(
  183.         tutoringHoursId INT NOT NULL AUTO_INCREMENT,
  184.         professorId INT NOT NULL,
  185.         dayOfWeek INT NOT NULL,
  186.         startHour TIME,
  187.         endHour TIME,
  188.         PRIMARY KEY (tutoringHoursId),
  189.         FOREIGN KEY (professorId) REFERENCES Professors (professorId),
  190.         CONSTRAINT invalidDayOfWeek CHECK (dayOfWeek >= 0 AND dayOfWeek <= 6)
  191.     );
  192.  
  193.     CREATE TABLE Appointments(
  194.         appointmentId INT NOT NULL AUTO_INCREMENT,
  195.         tutoringHoursId INT NOT NULL,
  196.         studentId INT NOT NULL,
  197.         HOUR TIME NOT NULL,
  198.         DATE DATE NOT NULL,
  199.         PRIMARY KEY (appointmentId),
  200.         FOREIGN KEY (tutoringHoursId) REFERENCES TutoringHours (tutoringHoursId),
  201.         FOREIGN KEY (studentId) REFERENCES Students (studentId)
  202.     );
  203.  
  204.     CREATE TABLE TeachingLoads(
  205.         teachingLoadId INT NOT NULL AUTO_INCREMENT,
  206.         professorId INT NOT NULL,
  207.         groupId INT NOT NULL,
  208.         credits INT NOT NULL,
  209.         PRIMARY KEY (teachingLoadId),
  210.         FOREIGN KEY (professorId) REFERENCES Professors (professorId),
  211.         FOREIGN KEY (groupId) REFERENCES Groups (groupId),
  212.         CONSTRAINT invalidCredits CHECK (credits > 0)
  213.     );
  214. END //
  215. DELIMITER ;
  216.  
  217. -- Llamada al procedimiento de creacion de tablas
  218. CALL createTables();
  219.  
  220. -- Definicion del procedimiento de insercion de datos
  221. DELIMITER //
  222. CREATE OR REPLACE PROCEDURE
  223.     populate()
  224. BEGIN
  225.     SET FOREIGN_KEY_CHECKS=0;
  226.     DELETE FROM Degrees;
  227.     DELETE FROM Subjects;
  228.     DELETE FROM Groups;
  229.     DELETE FROM Students;
  230.     DELETE FROM GroupsStudents;
  231.     DELETE FROM Grades;
  232.     DELETE FROM Offices;
  233.     DELETE FROM Classrooms;
  234.     DELETE FROM Departments;
  235.     DELETE FROM TutoringHours;
  236.     DELETE FROM TeachingLoads;
  237.     DELETE FROM Appointments;
  238.     DELETE FROM Professors;
  239.     SET FOREIGN_KEY_CHECKS=1;
  240.     ALTER TABLE Degrees AUTO_INCREMENT=1;
  241.     ALTER TABLE Subjects AUTO_INCREMENT=1;
  242.     ALTER TABLE Groups AUTO_INCREMENT=1;
  243.     ALTER TABLE Students AUTO_INCREMENT=1;
  244.     ALTER TABLE GroupsStudents AUTO_INCREMENT=1;
  245.     ALTER TABLE Grades AUTO_INCREMENT=1;
  246.     ALTER TABLE Offices AUTO_INCREMENT=1;
  247.     ALTER TABLE Classrooms AUTO_INCREMENT=1;
  248.     ALTER TABLE Departments AUTO_INCREMENT=1;
  249.     ALTER TABLE TutoringHours AUTO_INCREMENT=1;
  250.     ALTER TABLE TeachingLoads AUTO_INCREMENT=1;
  251.     ALTER TABLE Appointments AUTO_INCREMENT=1;
  252.     ALTER TABLE Professors AUTO_INCREMENT=1;
  253.    
  254.     INSERT INTO Offices (name, FLOOR, capacity) VALUES
  255.         ('F1.85', 1, 5),
  256.         ('F0.45', 0, 3);
  257.    
  258.     INSERT INTO Classrooms (name, FLOOR, capacity, hasProjector, hasLoudSpeakers) VALUES
  259.         ('F1.31', 1, 30, TRUE, FALSE),
  260.         ('F1.33', 1, 35, TRUE, FALSE),
  261.         ('A0.31', 1, 80, TRUE, TRUE);
  262.    
  263.     INSERT INTO Departments (name) VALUES
  264.         ('Lenguajes y Sistemas Informáticos'),
  265.         ('Matemáticas');
  266.    
  267.     INSERT INTO Degrees (name, years) VALUES
  268.         ('Ingeniería del Software', 4),
  269.         ('Ingeniería de Computadores', 4),
  270.         ('Tecnologías Informáticas', 4);
  271.    
  272.     INSERT INTO Subjects (name, acronym, credits, course, TYPE, degreeId, departmentId) VALUES
  273.         ('Diseño y Pruebas', 'DP', 12, 3, 'Obligatoria', 1, 1),
  274.         ('Acceso Inteligente a la Informacion', 'AII', 6, 4, 'Optativa', 1, 1),
  275.         ('Optimizacion de Sistemas', 'OS', 6, 4, 'Optativa', 1, 1),
  276.         ('Ingeniería de Requisitos', 'IR', 6, 2, 'Obligatoria', 1, 1),
  277.         ('Análisis y Diseño de Datos y Algoritmos', 'ADDA', 12, 2, 'Obligatoria', 1, 1),
  278.     -- 5/6
  279.         ('Introducción a la Matematica Discreta', 'IMD', 6, 1, 'Formacion Basica', 2, 2),
  280.         ('Redes de Computadores', 'RC', 6, 2, 'Obligatoria', 2, 1),
  281.         ('Teoría de Grafos', 'TG', 6, 3, 'Obligatoria', 2, 2),
  282.         ('Aplicaciones de Soft Computing', 'ASC', 6, 4, 'Optativa', 2, 1),
  283.     -- 9/10
  284.         ('Fundamentos de Programación', 'FP', 12, 1, 'Formacion Basica', 3, 1),
  285.         ('Lógica Informatica', 'LI', 6, 2, 'Optativa', 3, 2),
  286.         ('Gestión y Estrategia Empresarial', 'GEE', 12, 3, 'Optativa', 3, 1),
  287.         ('Trabajo de Fin de Grado', 'TFG', 12, 4, 'Obligatoria', 3, 1);
  288.        
  289.     INSERT INTO Groups (name, activity, YEAR, subjectId, classroomId) VALUES
  290.         ('T1', 'Teoria', 2018, 1, 1),
  291.         ('T2', 'Teoria', 2018, 1, 2),
  292.         ('L1', 'Laboratorio', 2018, 1, 3),
  293.         ('L2', 'Laboratorio', 2018, 1, 1),
  294.         ('L3', 'Laboratorio', 2018, 1, 2),
  295.         ('T1', 'Teoria', 2019, 1, 3),
  296.         ('T2', 'Laboratorio', 2019, 1, 1),
  297.         ('L1', 'Laboratorio', 2019, 1, 2),
  298.         ('L2', 'Laboratorio', 2019, 1, 3),
  299.     -- 9/10
  300.         ('Teor1', 'Teoria', 2018, 2, 1),
  301.         ('Teor2', 'Teoria', 2018, 2, 2),
  302.         ('Lab1', 'Laboratorio', 2018, 2, 3),
  303.         ('Lab2', 'Laboratorio', 2018, 2, 1),
  304.         ('Teor1', 'Teoria', 2019, 2, 2),
  305.         ('Lab1', 'Laboratorio', 2019, 2, 3),
  306.         ('Lab2', 'Laboratorio', 2019, 2, 1),
  307.     -- 16/17
  308.         ('T1', 'Teoria', 2019, 10, 2),
  309.         ('T2', 'Teoria', 2019, 10, 3),
  310.         ('T3', 'Teoria', 2019, 10, 1),
  311.         ('L1', 'Laboratorio', 2019, 10, 2),
  312.         ('L2', 'Laboratorio', 2019, 10, 3),
  313.         ('L3', 'Laboratorio', 2019, 10, 1),
  314.         ('L4', 'Laboratorio', 2019, 10, 2),
  315.     -- 23/24
  316.         ('Clase', 'Teoria', 2019, 12, 3),
  317.         ('T1', 'Teoria', 2019, 6, 1);
  318.        
  319.     INSERT INTO Students (accessMethod, dni, firstname, surname, birthdate, email) VALUES
  320.         ('Selectividad', '12345678A', 'Daniel', 'Pérez', '1991-01-01', 'daniel@alum.us.es'),
  321.         ('Selectividad', '22345678A', 'Rafael', 'Ramírez', '1992-01-01', 'rafael@alum.us.es'),
  322.         ('Selectividad', '32345678A', 'Gabriel', 'Hernández', '1993-01-01', 'gabriel@alum.us.es'),
  323.         ('Selectividad', '42345678A', 'Manuel', 'Fernández', '1994-01-01', 'manuel@alum.us.es'),
  324.         ('Selectividad', '52345678A', 'Joel', 'Gómez', '1995-01-01', 'joel@alum.us.es'),
  325.         ('Selectividad', '62345678A', 'Abel', 'López', '1996-01-01', 'abel@alum.us.es'),
  326.         ('Selectividad', '72345678A', 'Azael', 'González', '1997-01-01', 'azael@alum.us.es'),
  327.         ('Selectividad', '8345678A', 'Uriel', 'Martínez', '1998-01-01', 'uriel@alum.us.es'),
  328.         ('Selectividad', '92345678A', 'Gael', 'Sánchez', '1999-01-01', 'gael@alum.us.es'),
  329.         ('Titulado Extranjero', '12345678B', 'Noel', 'Álvarez', '1991-02-02', 'noel@alum.us.es'),
  330.         ('Titulado Extranjero', '22345678B', 'Ismael', 'Antúnez', '1992-02-02', 'ismael@alum.us.es'),
  331.         ('Titulado Extranjero', '32345678B', 'Nathanael', 'Antolinez', '1993-02-02', 'nathanael@alum.us.es'),
  332.         ('Titulado Extranjero', '42345678B', 'Ezequiel', 'Aznárez', '1994-02-02', 'ezequiel@alum.us.es'),
  333.         ('Titulado Extranjero', '52345678B', 'Ángel', 'Chávez', '1995-02-02', 'angel@alum.us.es'),
  334.         ('Titulado Extranjero', '62345678B', 'Matusael', 'Gutiérrez', '1996-02-02', 'matusael@alum.us.es'),
  335.         ('Titulado Extranjero', '72345678B', 'Samael', 'Gálvez', '1997-02-02', 'samael@alum.us.es'),
  336.         ('Titulado Extranjero', '82345678B', 'Baraquiel', 'Ibáñez', '1998-02-02', 'baraquiel@alum.us.es'),
  337.         ('Titulado Extranjero', '92345678B', 'Otoniel', 'Idiáquez', '1999-02-02', 'otoniel@alum.us.es'),
  338.         ('Titulado Extranjero', '12345678C', 'Niriel', 'Benítez', '1991-03-03', 'niriel@alum.us.es'),
  339.         ('Titulado Extranjero', '22345678C', 'Múriel', 'Bermúdez', '1992-03-03', 'muriel@alum.us.es'),
  340.         ('Titulado Extranjero', '32345678C', 'John', 'AII', '2000-01-01', 'john@alum.us.es');
  341.        
  342.     INSERT INTO GroupsStudents (groupId, studentId) VALUES
  343.         (1, 1),
  344.         (3, 1),
  345.         (7, 1),
  346.         (8, 1),
  347.         (10, 1),
  348.         (12, 1),
  349.     -- 6/7
  350.         (2, 2),
  351.         (3, 2),
  352.         (10, 2),
  353.         (12, 2),
  354.     -- 10/11
  355.         (18, 21),
  356.         (21, 21),
  357.     -- 12/13
  358.         (1, 9);
  359.        
  360.     INSERT INTO Grades (VALUE, gradeCall, withHonours, studentId, groupId) VALUES
  361.         (4.50, 1, 0, 1, 1),
  362.         (3.25, 2, 0, 1, 1),
  363.         (9.95, 1, 0, 1, 7),
  364.         (7.5, 1, 0, 1, 10),
  365.     -- 4/5
  366.         (2.50, 1, 0, 2, 2),
  367.         (5.00, 2, 0, 2, 2),
  368.         (10.00, 1, 1, 2, 10),
  369.     -- 7/8
  370.         (0.00, 1, 0, 21, 18),
  371.         (1.25, 2, 0, 21, 18),
  372.         (0.5, 3, 0, 21, 18);
  373.    
  374.     INSERT INTO Professors (officeId, departmentId, category, dni, firstname, surname, birthdate, email) VALUES
  375.         (1, 1, 'PAD', '42345678C', 'Fernando', 'Ramírez', '1960-05-02', 'fernando@us.es'),
  376.         (1, 1, 'TU', '52345678C', 'David', 'Zuir', '1902-01-01', 'dzuir@us.es'),
  377.         (1, 1, 'TU', '62345678C', 'Antonio', 'Zuir', '1902-01-01', 'azuir@us.es'),
  378.         (1, 2, 'CU', '72345678C', 'Rafael', 'Gómez', '1959-12-12', 'rdgomez@us.es'),
  379.         (2, 1, 'TU', '82345678C', 'Inma', 'Hernández', '1234-5-6', 'inmahrdz@us.es');
  380.    
  381.     INSERT INTO TutoringHours (professorId, dayOfWeek, startHour, endHour) VALUES
  382.         (1, 0, '12:00:00', '14:00:00'),
  383.         (1, 1, '18:00:00', '19:00:00'),
  384.         (1, 1, '11:30:00', '12:30:00'),
  385.         (2, 2, '10:00:00', '20:00:00');
  386.    
  387.     INSERT INTO Appointments (tutoringHoursId, studentId, HOUR, DATE) VALUES
  388.         (1, 1, '13:00:00', '2019-11-18'),
  389.         (2, 2, '18:20:00', '2019-11-19'),
  390.         (4, 1, '15:00:00', '2019-11-20');
  391.    
  392.     INSERT INTO TeachingLoads (professorId, groupId, credits) VALUES
  393.         (1, 1, 6),
  394.         (2, 1, 12),
  395.         (1, 2, 6),
  396.         (1, 3, 12);
  397. END //
  398. DELIMITER ;
  399.  
  400. -- Llamada al proceso de insercion de datos
  401. CALL populate();
  402.  
  403. -- Procedimientos varios
  404.  
  405. -- RF-001
  406. DELIMITER //
  407. CREATE OR REPLACE PROCEDURE
  408.     createGrade(groupId INT, studentId INT, gradeCall INT, withHonours BOOLEAN, VALUE DECIMAL(4,2))
  409. BEGIN
  410.     INSERT INTO Grades (groupId, studentId, gradeCall, withHonours, VALUE) VALUES (groupId, studentId, gradeCall, withHonours, VALUE);
  411. END //
  412. DELIMITER ;
  413.  
  414. -- RF-006
  415. DELIMITER //
  416. CREATE OR REPLACE PROCEDURE
  417.     procedureDeleteGrades(studentDni CHAR(9))
  418. BEGIN
  419.     DECLARE id INT;
  420.     SET id = (SELECT studentId FROM Students WHERE dni=studentDni);
  421.     DELETE FROM Grades WHERE studentId=id;
  422. END //
  423. DELIMITER ;
  424.  
  425. -- Triggers
  426.  
  427. -- RN-006
  428. DELIMITER //
  429. CREATE OR REPLACE TRIGGER triggerWithHonours
  430.     BEFORE INSERT ON Grades
  431.     FOR EACH ROW
  432.     BEGIN
  433.         IF (NEW.withHonours = 1 AND NEW.VALUE < 9.0) THEN
  434.             SIGNAL SQLSTATE '45000' SET message_text =
  435.             'Para obtener matrícula hay que sacar al menos un 9';
  436.         END IF;
  437.     END//
  438. DELIMITER ;
  439.  
  440. -- ExamenSimulacro
  441.  
  442. DELIMITER //
  443. CREATE OR REPLACE PROCEDURE
  444.     pInsertInterns()
  445. BEGIN
  446.     INSERT INTO InternalStudents (departmentId, studentId, academicYear, duration) VALUES   (1, 1, 2019, 3);
  447.     INSERT INTO InternalStudents (departmentId, studentId, academicYear, duration) VALUES   (1, 1, 2020, 6);
  448.     INSERT INTO InternalStudents (departmentId, studentId, academicYear, duration) VALUES   (1, 2, 2019, NULL);
  449. END //
  450. DELIMITER ;
  451.  
  452.  
  453. -- Disparador 1
  454. DELIMITER //
  455. CREATE OR REPLACE TRIGGER tCorrectDuration
  456.     BEFORE INSERT ON InternalStudents
  457.     FOR EACH ROW
  458.     BEGIN
  459.         IF (NEW.duration >9) THEN
  460.             SET NEW.duration = 8;
  461.         END IF;
  462.     END//
  463. DELIMITER ;
  464.  
  465. -- Procedure 2
  466.  
  467. -- s --> old
  468. -- d --> new
  469. DELIMITER //
  470. CREATE OR REPLACE PROCEDURE
  471.     pUpdateInterns(s INT,d INT)
  472. BEGIN
  473.     UPDATE InternalStudents SET studentId = d WHERE studentId = s;
  474.  
  475. END //
  476. DELIMITER ;
  477.  
  478. -- Procedure 3
  479. DELIMITER //
  480. CREATE OR REPLACE PROCEDURE
  481.     pDeleteInterns(s INT)
  482. BEGIN
  483.     DELETE FROM InternalStudents WHERE studentId=s;
  484. END //
  485. DELIMITER ;
  486.  
  487. -- LLamadas
  488. CALL pInsertInterns();
  489. CALL pUpdateInterns(1, 13);
  490. CALL pDeleteInterns(2);
  491.  
  492. -- Consultas
  493.  
  494. -- SELECT firstName, NAME, credits  FROM professors NATURAL JOIN teachingloads NATURAL JOIN groups;
  495.  
  496. -- SELECT AVG(VALUE) FROM grades WHERE groupId = 2;
  497.  
  498. -- SELECT studentId, MAX(VALUE) FROM grades GROUP BY studentId ORDER BY value DESC;
  499.  
  500. -- SELECT firstName, surname, COUNT(professorId) numberOfGroups FROM professors NATURAL JOIN teachingloads GROUP BY professorId ORDER BY numberOfGroups DESC;
  501.  
  502.  
  503. -- ------------------------
  504.        
  505. DELIMITER //
  506. CREATE OR REPLACE PROCEDURE
  507.     pInsertPublications()
  508. BEGIN
  509.     INSERT INTO publicaciones (title, professorId, totalAuthors, publishDay, magazine) VALUES   ("Publicación 1", 1, 3, NULL, "Revista 1");
  510.     INSERT INTO publicaciones (title, professorId, totalAuthors, publishDay, magazine) VALUES   ("Publicación 2", 1, 5, "2018-01-01", "Revista 2");
  511.     INSERT INTO publicaciones (title, professorId, totalAuthors, publishDay, magazine) VALUES   ("Publicación 3", 2, 2, NULL, "Revista 3");
  512. END //
  513. DELIMITER ;
  514.  
  515.  
  516. DELIMITER //
  517. CREATE OR REPLACE TRIGGER tCorrectAuthors
  518.     BEFORE INSERT ON publicaciones
  519.     FOR EACH ROW
  520.     BEGIN
  521.         IF (NEW.totalAuthors >10) THEN
  522.             SET NEW.totalAuthors = 10;
  523.         END IF;
  524.     END//
  525. DELIMITER ;
  526.  
  527. DELIMITER //
  528. CREATE OR REPLACE PROCEDURE
  529.     pUpdatePublications(p INT,n INT)
  530. BEGIN
  531.     UPDATE publicaciones    SET totalAuthors = n WHERE professorId = p;
  532.  
  533. END //
  534. DELIMITER ;
  535.  
  536.  
  537. DELIMITER //
  538. CREATE OR REPLACE PROCEDURE
  539.     pDeletePublications(p INT)
  540. BEGIN
  541.     DELETE FROM publicaciones WHERE professorId=p;
  542. END //
  543. DELIMITER ;
  544.  
  545.  
  546. -- LLamadas
  547. CALL pInsertPublications();
  548. CALL pUpdatePublications(1, 10);
  549. CALL pDeletePublications(2);
  550.  
  551. -- Consultas
  552.  
  553. -- SELECT  degrees.name, subjects.name, credits, type FROM subjects JOIN degrees ORDER BY degrees.name;
  554.  
  555. -- SELECT tutoringHoursId FROM appointments NATURAL JOIN tutoringhours GROUP BY tutoringHoursId;
  556.  
  557. -- SELECT AVG(credits) FROM teachingLoads WHERE professorId = 1;
  558.  
  559. -- SELECT firstName, surname, AVG(grades.value), MIN(grades.value), MAX(grades.value) FROM grades NATURAL JOIN students GROUP BY studentId LIMIT 2;
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!