Assignment: Advanced SQL Techniques

!pip install sqlalchemy==1.3.9
!pip install ibm_db_sa

%load_ext sql

# Remember the connection string is of the format:
# %sql ibm_db_sa://my-username:my-password@my-hostname:my-port/my-db-name
# Enter the connection string for your Db2 on Cloud database instance below
%sql ibm_db_sa://kfm42587:6zkhf3chpx0-m9cl@dashdb-txn-sbox-yp-lon02-13.services.eu-gb.bluemix.net:50000/BLUDB

%%sql
SELECT S.NAME_OF_SCHOOL, S.COMMUNITY_AREA_NAME, S.AVERAGE_STUDENT_ATTENDANCE FROM CHICAGO_PUBLIC_SCHOOLS S 
LEFT OUTER JOIN CENSUS_DATA C 
ON S.COMMUNITY_AREA_NUMBER = C.COMMUNITY_AREA_NUMBER 
WHERE C.HARDSHIP_INDEX = 98.0

%%sql
SELECT CR.CASE_NUMBER, CR.PRIMARY_TYPE, CE.COMMUNITY_AREA_NAME FROM CHICAGO_CRIME_DATA CR
LEFT OUTER JOIN CENSUS_DATA CE 
ON CR.COMMUNITY_AREA_NUMBER = CE.COMMUNITY_AREA_NUMBER 
WHERE CR.LOCATION_DESCRIPTION LIKE '%SCHOOL%'

%%sql
CREATE VIEW 

AS SELECT
FROM CHICAGO_PUBLIC_SCHOOLS

%%sql
--#SET TERMINATOR@
CREATE PROCEDURE UPDATE_LEADERS_SCORE(IN in_School_ID INTEGER, IN in_Leader_Score INTEGER)
BEGIN
    UPDATE 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@