Final Project: Advanced SQL Techniques

 Answering the questions of project

Introduction

This project is the ‘Optional Honors’ part of the course SQL:A Practical Introduction for Querying Databases. Here is the source. I will be answering the questions asked with the help of MySQL.

 

Task A: Create a database

I downloaded the datasets available in ‘.sql ‘ format and imported all the tables one by one in MySQL database ‘practicedb’ by running the SQL script. We can see tables available in the database by querying ‘show tables’.

Meta Data

I wrote a query ‘desc <table name>’ to find the data type and dimension of each table.

1. Chicago Public School

Fig. 3Column names and data types have 78 columns

FIg. 4 Table has 566 rows

2. Chicago Crime data

---

3. Socioeconomic table named Census Data

Fig. 6 Census data has only 9 columns and 77 rows

---

Exercise 1: Using Joins

You have been asked to produce some reports about the communities and crimes in the Chicago area.

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

To find those columns in the data base for which I am not sure which table it belongs I use this query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%HARD%'
ORDER BY TABLE_NAME;

Fig. 7 Result of above query

/*Census data and school data has common column community area number*/
SELECT cps.NAME_OF_SCHOOL, cps.COMMUNITY_AREA_NAME, cps.AVERAGE_STUDENT_ATTENDANCE
FROM chicago_public_schools cps
JOIN census_data cs
USING(COMMUNITY_AREA_NUMBER)
WHERE cs.HARDSHIP_INDEX =98;

Fig.8 Result of above query

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

SELECT ccd.CASE_NUMBER, ccd.PRIMARY_TYPE, cd.COMMUNITY_AREA_NAME
FROM chicago_crime_data ccd
JOIN census_data cd
USING(COMMUNITY_AREA_NUMBER)
WHERE ccd.LOCATION_DESCRIPTION LIKE '%School%';

Fig.9 Total 10 records

Exercise 2: Creating a View

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

Fig. 10 Desired column in the view table

/*Query to create View*/
CREATE VIEW chicago_school_info(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;

Fig. 11 View created successfully

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

Fig. 12 All columns from the view

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

Fig.13

Exercise 3: Creating a Stored Procedure

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

/*Structure of Stored procedure in MySQL*/
DELIMITER //
CREATE   PROCEDURE `UPDATE_LEADERS_SCORE`(in_School_ID int, in_Leader_Score int)
BEGIN

END //
DELIMITER;

Question 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_School_ID  int, in_Leader_Score int)
BEGIN
/********Update statement begins********/
 UPDATE CHICAGO_PUBLIC_SCHOOLS
 SET Leaders_Score = in_Leader_Score
 WHERE School_ID = in_School_ID ;
/******Update statement ends******/
END //

Question 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 following information.

/*Complete query of stored procedure*/
DELIMITER //
CREATE   PROCEDURE `UPDATE_LEADERS_SCORE` (IN in_School_ID  int,IN in_Leader_Score  int)
BEGIN
 UPDATE CHICAGO_PUBLIC_SCHOOLS
 SET Leaders_Score = in_Leader_Score
 WHERE School_ID = in_School_ID ;
/****If statement begins****/
  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;
  /****If statement ends****/
END //

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

Data type of leaders icon is varchar(4) so an error was given if character longer than 4 was entered. We can see below:

Fig. 14

Hence I first modified the data type.

/*Query to modify data type of column*/
ALTER TABLE CHICAGO_PUBLIC_SCHOOLS MODIFY COLUMN leaders_icon varchar(15);

I selected school id 610084, below is screenshot before calling the stored procedure.

Fig. 15

call UPDATE_LEADERS_SCORE(610084,50);
/*To check the result*/
select School_ID, leaders_icon, Leaders_Score from CHICAGO_PUBLIC_SCHOOLS where School_ID=610084;

Fig. 16 Columns updated

Exercise 4: Using Transactions

You realise 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.

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

MySQL provides a feature to modify an existing stored procedure so if I apply after the changes it will replace the existing procedure.

Fig. 17 On the left pane is stored procedure and right it opened the procedure to modify

I wrote the query in this section. I am only showing the query below asked for this question

CREATE DEFINER=`root`@`localhost` PROCEDURE `UPDATE_LEADERS_SCORE`(in_School_ID  int,in_Leader_Score  int)
BEGIN
/*TRANSACTION STATEMENT BEGINS*/
START TRANSACTION;
/*Here is the update code*/
   /*ELSE STATEMENT IF ABOVE 100 THEN ROLL BACK*/
   ELSE 
   ROLLBACK;
END IF;
END

Question 2

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

/*Here is complete query, commit added after if statement ends*/
CREATE DEFINER=`root`@`localhost` PROCEDURE `UPDATE_LEADERS_SCORE`(in_School_ID  int,in_Leader_Score  int)
BEGIN
 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;
END IF;
  COMMIT;
 
END

Run your code to replace the stored procedure.

After modifying the query I applied the changes, below is a snapshot.

Fig. 18 Left before apply with preview, right after applying.

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

Fig. 19 Changes applied

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

When I provided a score of 101 to the function it ran successfully but no changes were made.

Fig. 20 No changes in the table

That was the last question asked in the project.

 Thank you for reading!

If you found this article helpful, consider following me for more insights. Any thoughts or suggestions are welcome in the comment or you can directly message and connect with me on Linkedin.