# Procedures & Triggers Review {:toc} - Upload all .sql in the __/highschool/__ folder in your Git repository. - Presentation will be taken into account. Using <a href="/DAM-BD/UD1/exercicis/01_ddl_highschool.html" target="_blank">the highschool database</a> __Files:__ - <a href="/itb/DAM-BD/files/en/highschool/schema/create_schema_highschool.sql" download="create_schema_highschool.sql">create_schema_highschool.sql</a> - <a href="/itb/DAM-BD/files/en/highschool/schema/create_schema_highschool_material.sql" download="create_schema_highschool_material.sql">create_schema_highschool_material.sql</a> - <a href="/itb/DAM-BD/files/en/highschool/data/insert_data_highschool.sql" download="insert_data_highschool.sql">insert_data_highschool.sql</a> - <a href="/itb/DAM-BD/files/en/highschool/data/insert_data_highschool_material.sql" download="insert_data_highschool_material.sql">insert_data_highschool_material.sql</a> __Tests__: - <a href="/itb/DAM-BD/files/en/highschool/tests/tests_highschool_procedures_review.yml" download="tests_highschool_procedures_review.yml">tests_highschool_procedures_review.yml</a> <div class="mermaid"> erDiagram MATERIAL { int material_id PK varchar100 name int amount } MATERIAL_HISTORY }o--|| TEACHER : "" MATERIAL_HISTORY }o--|| MATERIAL : "" MATERIAL_HISTORY { int material_id FK varchar9 teacherDNI FK int amount timestamp timestamp } PERSON { char9 DNI PK varchar50 name varchar50 surname varchar30 phone date birth_date varchar100 personal_email varchar100 corporate_email } TEACHER |o--|| PERSON: "" TEACHER { char9 DNI FK } STUDENT |o--|| PERSON: "" STUDENT { char9 DNI FK char9 NIA int course_id FK char group_letter FK } GROUP |o--|| TEACHER: "is tutor of" GROUP ||--|{ STUDENT: "belogns" GROUP }|--|| COURSE: "" GROUP { int course_id FK char letter PK char9 tutorDNI FK } ENROLLED }|--|| STUDENT : "" ENROLLED }|--|| SUBJECT: "" ENROLLED }|--|| YEAR: "" ENROLLED { char9 studentDNI FK char4 subject_id FK int year FK decimal4_2 grade } YEAR { int start_year PK int end_year } TEACHES }|--|| TEACHER: "" TEACHES }|--|| SUBJECT: "" TEACHES }|--|| YEAR: "" TEACHES { char9 teacherDNI FK char4 subject_id FK int year FK } FAMILY { int family_id PK varchar100 name } COURSE }o--|| FAMILY : "" COURSE { int course_id PK int family_id FK varchar100 name varchar6 acronym enum level } SUBJECT }o--|| COURSE : "" SUBJECT { char4 subject_id PK int course_id FK varchar100 name varchar6 acronym int etcs int hours } </div> ## Functions ### Percentage course completed - __File:__ functions/percentage_course_completed.sql Create a function named __percentage_course_completed(studentDNI)__ that returns the percentatge (up to two decimals) of the ETCS completed of the course he is enrolled to. #### Input ```sql use highschool; select s.DNI, c.acronym course, percentage_course_completed(s.DNI) percentage_complete from student s inner join course c on s.course_id = c.course_id; ``` #### Output ```text +-----------+--------+---------------------+ | DNI | course | percentage_complete | +-----------+--------+---------------------+ | 32409818N | ASIX | 27 | | 40189039N | ASIX | 28 | | 40625464N | ASIX | 7 | | 54289537S | ASIX | 28 | | 60839601R | ASIX | 15 | | 68743667W | ASIX | 17 | | 72404553R | ASIX | 13 | | 87497335E | ASIX | 28 | | 44152608Z | DAM | 0 | | 44791256K | DAM | 0 | | 55508376S | DAM | 0 | | 63665445L | DAM | 100 | | 71299956W | DAM | 100 | | 75697315Z | DAM | 0 | | 78762933P | DAM | 0 | | 88629474D | DAM | 0 | | 38620245W | DAM | 0 | | 39765288J | DAM | 0 | | 41259845F | DAM | 0 | | 56294859J | DAM | 0 | | 67647948A | DAM | 0 | | 67884293T | DAM | 0 | | 79891955Y | DAM | 0 | | 92743788Z | DAM | 0 | | 18485557C | DAW | 41 | | 24623862Q | DAW | 24 | | 25962840A | DAW | 18 | | 53104350H | DAW | 14 | | 58224076J | DAW | 22 | | 68498722F | DAW | 41 | | 70632246M | DAW | 16 | | 88279831N | DAW | 26 | | 18589096J | SMX | 0 | | 21078983N | SMX | 0 | | 28968804M | SMX | 0 | | 33847291P | SMX | 100 | | 34033673K | SMX | 100 | | 40838808P | SMX | 100 | | 75935093H | SMX | 0 | | 81010248Q | SMX | 0 | +-----------+--------+---------------------+ ``` ### Attempts left - __File:__ functions/attempts_left.sql Create a function named __attempts_left(studentDNI, subject_id)__ that returns the number of remaining attempts that the student has. The total number of attempts is 4. #### Input ```sql use highschool; select s.DNI, c.acronym course, "0485" subject, attempts_left(s.DNI, "0485") attempts_left from student s inner join course c on s.course_id = c.course_id where c.acronym = "DAW"; ``` #### Output ```text +-----------+--------+---------+---------------+ | DNI | course | subject | attempts_left | +-----------+--------+---------+---------------+ | 18485557C | DAW | 0485 | 3 | | 24623862Q | DAW | 0485 | 3 | | 25962840A | DAW | 0485 | 4 | | 53104350H | DAW | 0485 | 4 | | 58224076J | DAW | 0485 | 4 | | 68498722F | DAW | 0485 | 3 | | 70632246M | DAW | 0485 | 3 | | 88279831N | DAW | 0485 | 4 | +-----------+--------+---------+---------------+ ``` ## Triggers ### Course total ETCS - __File:__ triggers/course_total_etcs.sql Create the necessary triggers that prevent that a course has more than 120 ETCS. ``` The course "XXX" can't have more than 120 ETCS. ```