Joins & Stored Procedures

Dump to MySQL

---

• We’ll use MySQL for this project
• We’ll create and load the tables using dump files downloaded to the local drive
  • chicago_public_schools - Too long to post image of column names
  • chicago_crime

• chicago_socioeconomic_data

• Create db: Mysql_learners via phpMyAdmin

• Import the dump files for each table to create them all one by one

Joins

---

Task 1

• List the school names, community names and average attendance for communities with a hardship index of 98.

SELECT S.NAME_OF_SCHOOL, S.AVERAGE_STUDENT_ATTENDANCE, D.COMMUNITY_AREA_NAME 
FROM   chicago_public_schools S
LEFT JOIN 
       chicago_socioeconomic_data D  
ON     S.COMMUNITY_AREA_NUMBER = D.COMMUNITY_AREA_NUMBER 
WHERE  D.HARDSHIP_INDEX = 98;

Task 2

• List all crimes that took place at a school.
  • Include case number
  • crime type
  • community name.
• Community name and community area number is found in chicago_socioeconomic_data table
• Community area number and case number are found in crime table
• crimes at schools can be found in crime table using WHERE LOCATION_DESCRIPTION LIKE 'SCHOOLS%'
• So we need to extract CASE_NUMBER , PRIMARY_TYPE based on WHERE above
• Then match with the socioeconomic data table ON COMMUNITY_AREA_NUMBER and extract the COMMUNITY_AREA_NAME from the intersection

SELECT  C.CASE_NUMBER, C.PRIMARY_TYPE, D.COMMUNITY_AREA_NAME 
FROM    chicago_crime C
LEFT JOIN chicago_socioeconomic_data D
ON      C.COMMUNITY_AREA_NUMBER = D.COMMUNITY_AREA_NUMBER
WHERE   C.LOCATION_DESCRIPTION LIKE 'SCHOOL%'

Views

---

For privacy reasons, you have been asked to create a view that enables users to select just

• the school name
• the icon fields from the CHICAGO_PUBLIC_SCHOOLS table
• By providing a view, you can ensure that users cannot see the actual scores given to a school, just the icon associated with their score.
• You should define new names for the view columns to obscure the use of scores and icons in the original table.

Task 1

• Write and execute a SQL statement to create a view showing the columns listed in the following table
• New column names as shown in the second column.

Column name in CHICAGO_PUBLIC_SCHOOLS	Column name in view
NAME_OF_SCHOOL	School_Name
Safety_Icon	Safety_Rating
Family_Involvement_Icon	Family_Rating
Environment_Icon	Environment_Rating
Instruction_Icon	Instruction_Rating
Leaders_Icon	Leaders_Rating
Teachers_Icon	Teachers_Rating

CREATE VIEW Task_1 AS
SELECT      NAME_OF_SCHOOL School_Name, Safety_Icon Safety_Rating, Family_Involvement_Icon Family_Rating, Environment_Icon Environment_Rating, Instruction_Icon Instruction_Rating, Leaders_Icon Leaders_Rating, Teachers_Icon Teachers_Rating
FROM    chicago_public_schools; 

Task 2

• Write and execute a SQL statement that returns all of the columns from the view.

SELECT   *
FROM     Task_1;

Task 3

• Write and execute a SQL statement that returns just the school name and leaders rating from the view.

SELECT School_Name, Leaders_Rating
FROM   Task_1;

Stored Procedure

---

The icon fields are calculated based on the value in the corresponding score field. You need to make sure that when a score field is updated, the icon field is updated too. To do this, you will write a stored procedure that receives the school id and a leaders score as input parameters, calculates the icon setting and updates the fields appropriately.

Task 1

• Write the structure of a query to create or replace a stored procedure called UPDATE_LEADERS_SCORE that takes a in_School_ID parameter as an integer and a in_Leader_Score parameter as an integer.

DELIMITER $$
CREATE PROCEDURE   UPDATE_LEADERS_SCORE ( IN School_ID INTEGER, IN Leaders_Score INTEGER)
BEGIN

END
$$
DELIMITER ;

Task 2

• Inside your stored procedure, write a SQL statement to update the Leaders_Score field in the CHICAGO_PUBLIC_SCHOOLS table for the school identified by in_School_ID to the value in the in_Leader_Score parameter.

DELIMITER $$
CREATE PROCEDURE   UPDATE_LEADERS_SCORE ( IN in_School_ID INTEGER, IN in_Leader_Score INTEGER)
BEGIN
        UPDATE     chicago_public_schools
        SET        Leaders_Score = in_Leader_Score
        WHERE      School_ID = in_School_ID;
END
$$
DELIMITER ;

Task 3

• Inside your stored procedure, write a SQL IF statement to

  • update the Leaders_Icon field in the CHICAGO_PUBLIC_SCHOOLS table for the school identified by in_School_ID using the table below

  • Remember that once a clause of the IF statement executes, no further checking occurs and processing moves to the code below the IF statement.

Score lower limit	Score upper limit	Icon
80	99	Very strong
60	79	Strong
40	59	Average
20	39	Weak
0	19	Very weak

DELIMITER $$
CREATE PROCEDURE  UPDATE_LEADERS_SCORE ( IN in_School_ID INTEGER, IN in_Leader_Score INTEGER)
BEGIN
        UPDATE    chicago_public_schools
        SET       Leaders_Score = in_Leader_Score
        WHERE     School_ID = in_School_ID;
        
        IF        in_Leader_Score > 0 AND in_Leader_Score < 20  THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Very weak"
                  WHERE    School_ID = in_School_ID;
        ELSEIF    in_Leader_Score < 40 THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Weak"
                  WHERE    School_ID = in_School_ID;
        ELSEIF    in_Leader_Score < 60 THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Average"
                  WHERE    School_ID = in_School_ID;
        ELSEIF    in_Leader_Score < 80 THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Strong"
                  WHERE    School_ID = in_School_ID;
        ELSEIF    in_Leader_Score < 100 THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Very Strong"
                  WHERE    School_ID = in_School_ID;
        END IF;
END
$$
DELIMITER ;

Task 4

• Run your code to create the stored procedure.
• Write a query to call the stored procedure, passing a valid school ID and a leader score of 50, to check that the procedure works as expected.

Before I do that I will create a view of the columns we need without changing their names (like we did in (Views above), shown below the code here is just the first few rows of the VIEW for comparison

CREATE VIEW Procedure_View AS
SELECT  School_ID, NAME_OF_SCHOOL, Leaders_Score, Leaders_Icon
FROM    chicago_public_schools; 

SELECT *
FROM   Procedure_View

• Now let’s run the Stored Procedure with School_ID = 610038 and Leaders_Score = 50
• The result should be a change from Weak to Average
• As you see in the result image below the Leaders_Score is 50, Leaders_Icon = Average for School_ID 610038

CALL  UPDATE_LEADERS_SCORE ( 610038, 50)

Transactions

---

You realize that if someone calls your code with a score outside of the allowed range (0-99), then the score will be updated with the invalid data and the icon will remain at its previous value. There are various ways to avoid this problem, one of which is using a transaction.

Task 1

• Update your stored procedure definition. Add a generic ELSE clause to the IF statement that rolls back the current work if the score did not fit any of the preceding categories.

• You can add an ELSE clause to the IF statement which will only run if none of the previous conditions have been met.

DELIMITER $$
CREATE PROCEDURE  UPDATE_LEADERS_SCORE ( IN in_School_ID INTEGER, IN in_Leader_Score INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
                ROLLBACK;
                RESIGNAL;
        END;

        START TRANSACTION;
        UPDATE    chicago_public_schools
        SET       Leaders_Score = in_Leader_Score
        WHERE     School_ID = in_School_ID;
        
        IF        in_Leader_Score > 0 AND in_Leader_Score < 20  THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Very weak"
                  WHERE    School_ID = in_School_ID;
        ELSEIF    in_Leader_Score < 40 THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Weak"
                  WHERE    School_ID = in_School_ID;
        ELSEIF    in_Leader_Score < 60 THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Average"
                  WHERE    School_ID = in_School_ID;
        ELSEIF    in_Leader_Score < 80 THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Strong"
                  WHERE    School_ID = in_School_ID;
        ELSEIF    in_Leader_Score < 100 THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Very Strong"
                  WHERE    School_ID = in_School_ID;
        ELSE      ROLLBACK WORK;
        END IF;
END
$$
DELIMITER ;

Task 2

• Update your stored procedure definition again and rename it this time TRANS_UPDATE_LEADERS_SCORE

• Add a statement to commit the current unit of work at the end of the procedure.

• Remember that as soon as any code inside the IF/ELSE IF/ELSE statements completes, processing resumes after the END IF, so you can add your commit code there.

DELIMITER $$
CREATE PROCEDURE  TRANS_UPDATE_LEADERS_SCORE ( IN in_School_ID INTEGER, IN in_Leader_Score INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
                ROLLBACK;
                RESIGNAL;
        END;

        START TRANSACTION;
        UPDATE    chicago_public_schools
        SET       Leaders_Score = in_Leader_Score
        WHERE     School_ID = in_School_ID;
        
        IF        in_Leader_Score > 0 AND in_Leader_Score < 20  THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Very weak"
                  WHERE    School_ID = in_School_ID;
        ELSEIF    in_Leader_Score < 40 THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Weak"
                  WHERE    School_ID = in_School_ID;
        ELSEIF    in_Leader_Score < 60 THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Average"
                  WHERE    School_ID = in_School_ID;
        ELSEIF    in_Leader_Score < 80 THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Strong"
                  WHERE    School_ID = in_School_ID;
        ELSEIF    in_Leader_Score < 100 THEN
                  UPDATE   chicago_public_schools
                  SET      Leaders_Icon = "Very Strong"
                  WHERE    School_ID = in_School_ID;
        ELSE      ROLLBACK WORK;
        END IF;
        COMMIT WORK;
END
$$
DELIMITER ;

Take a screenshot showing the SQL query.

• Run your code to replace the stored procedure.

• Write and run one query to check that the updated stored procedure works as expected when you use a valid score of 38.

• Write and run another query to check that the updated stored procedure works as expected when you use an invalid score of 101.

Let’s run the Transaction with:

• Leaders_Score is 38, Leaders_Icon = Average for School_ID 610038 on the same school we ran earlier
  • This should change the score to 38 and Icon to “WEAK”

CALL  TRANS_UPDATE_LEADERS_SCORE ( 610038, 38)

• Then run it with same School_ID but use a score of 101 to see if it ROLLSBACK
  • So the result should be the same

CALL  TRANS_UPDATE_LEADERS_SCORE ( 610038, 101)