Project Scenario 🎩

You have to analyze the following datasets for the city of Chicago, as available on the Chicago City data portal.

• Socioeconomic indicators in Chicago
• Chicago public schools
• Chicago crime data

Based on the information available in the different tables, you have to run specific queries using Advanced SQL techniques that generate the required result sets.

Objectives📝

• After completing this project, you will be able to:
  • Use joins to query data from multiple tables
  • Create and query views
  • Write and run stored procedures
  • Use transactions

Reach/Follow me on 🚀

     

Prepare the lab environment 📦

• In this lab, you will use MySQL.
• Database Used in this Lab

Note: Here you will be creating and inserting data into the below mentioned 3 tables

• Here you will be using 3 dump files for this purpose.
  

chicago_public_schools

chicago_socioeconomic_data

chicago_crime

1. create a new empty database Mysql_Learners
2. Load the dump files one by one into the database Mysql_learners by clicking the Import tab and choose the file.

Task 1 - Write and execute a SQL query to list the school names, community names and average attendance for communities with a hardship index of 98.

Task 2 - Write and execute a SQL query to list all crimes that took place at a school. Include case number, crime type and community name.

Task 3 - Creating a View

1. Write and execute a SQL statement to create a view showing the columns listed in the following table, with 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

2. Write and execute a SQL statement that returns all of the columns from the view. 3.Write and execute a SQL statement that returns just the school name and leaders rating from the view.

CREATE VIEW PRIVATE_VIEW (School_Name, Safety_Rating, Family_Rating, Environment _Rating, Instruction_Rating, Leaders_Rating, Teachers _Rating)
AS SELECT NAME_OF_SCHOOL, Safety_Icon, Family_Involvement_Icon, Environment_Icon, Instruction_Icon, Leaders_Icon, Teachers_Icon
FROM CHICAGO_PUBLIC_SCHOOLS;

SELECT * FROM PRIVATE_ VIEW;
SELECT SCHOOL_NAME, Leaders_Rating FROM PRIVATE_VIEW

Task 4 - 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.

--#SET TERMINATOR 0
CREATE OR REPLACE PROCEDURE
UPDATE LEADERS SCORE (IN in School ID INTEGER, IN in Leader Score INTEGER)
LANGUAGE SOL
BEGIN
END@

Task 5 - 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.

CREATE OR REPLACE PROCEDURE
UPDATE_LEADERS_SCORE (IN in_School_ID INTEGER, IN in_Leader_Score INTEGER)
LANGUAGE SOL
BEGIN
UPDATE CHICAGO PUBLIC SCHOOLS
SET "Leaders Score" = in Leader Score
WHERE "School_ID" = in_School_ID;
END@

Task 6 - 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 following information.



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

IF in Leader Score >
© AND in_Leader_Score < 20 THEN
UPDATE chicago_public_schools
SET Safety_Icon
= 'VERY WEEK
WHERE in School ID = in School ID:
ELSEIF in Leader Score
< 40 THEN
UPDATE chicago_public_schools
SET Safety_Icon
'WEEK
WHERE in School ID = in School ID;
ELSEIF in Leader Score
< 60 THEN
UPDATE chicago_public_schools
SET Safety_Icon = 'AVERAGE'
WHERE in School ID = in School ID;
ELSEIF in Leader Score
< 80 THEN
UPDATE chicago_public_schools
SET Safety_Icon
'STRONG'
WHERE in School ID = in School ID:
ELSEIF in Leader Score
< 100 THEN
UPDATE chicago_public_schools
SET Safety_Icon =
'VERY STRONG'
WHERE in School ID = in School ID;

Task 7 - 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.

• Run your code to create the stored procedure.

CREATE OR REPLACE PROCEDURE UPDATE_LEADERS_SCORE (IN in_School_ID INTEGER, IN in_Leader_Score INTEGER)
LANGUAGE SQL
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';

ELSEIF in Leader Score < 40 THEN
    UPDATE CHICAGO_PUBLIC_SCHOOLS
    SET "Leaders icon" = 'Weak';

ELSEIF in Leader Score < 60 THEN
    UPDATE CHICAGO_PUBLIC SCHOOLS
    SET "Leaders_icon" = 'Average';

ELSEIF in Leader Score < 80 THEN
    UPDATE CHICAGO_PUBLIC_SCHOOLS
    SET "Leaders_icon" = 'Strong';

ELSEIF in Leader Score < 100 THEN
    UPDATE CHICAGO_PUBLIC_SCHOOLS
    SET "Leaders_icon" = 'Very Strong';
END IF;
END@

Task 8 - 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.

ELSE
   ROLLBACK;
-- UPDATE chicago_public_schools
-- SET Safety_Icon = Safety_Icon
-- WHERE in_School_ID = School_ID:
END IF:
END@

Task 9 - Update your stored procedure definition again. Add a statement to commit the current unit of work at the end of the procedure.

END IF:
COMMIT:
END
@

Contributing 📝

Contributions are welcome! Please open an issue or pull request for any changes or improvements.