Skip to content

Run specific queries using Advanced SQL techniques that generate the required result sets.

Notifications You must be signed in to change notification settings

Mohamed-fawzyy/Advanced-SQL-Techniques

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

4 Commits
 
 

Repository files navigation

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 🚀

linkedIn    googleEmail    facebook


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.

Exercise_1_Q1

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.

Exercise_1_Q2

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

About

Run specific queries using Advanced SQL techniques that generate the required result sets.

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published