[IBM]Databases and SQL for Data Science with Python - Views, Stored Procedures

VIEWS

Show a selection of data for a given table 

Combine two or more tables in meaningful ways

It can be queried like a table, views are dynamic; only the definition of the view is stored, not the data 

 

Create view statement to create view 

CREATE VIEW <view name> (<column_alias_1>,<column_alias_2>, ... <Column_alias_n>) 
AS SELECT <column_1>, <column_2>,...<column_n>
FROM <table_name>
WHERE <predicate>;

 

REPLACE VIEW statement 

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition; 

 

 DROP VIEW statement 

DROP VIEW view_name;

 

EXERCISE

CREATE VIEW EMPSALARY AS 
SELECT EMP_ID, F_NAME, L_NAME, B_DATE, SEX, SALARY
FROM EMPLOYEES;

select * from EMPSALARY;

CREATE OR REPLACE VIEW EMPSALARY AS
SELECT EMP_ID, F_NAME, L_NAME, B_DATE, SEX, JOB_TITLE, MIN_SALARY, MAX_SALARY
FROM EMPLOYEES, JOBS
WHERE EMPLOYEES.JOB_ID = JOBS.JOB_IDENT;

select * from EMPSALARY;

DROP VIEW EMPSALARY;

CREATE VIEW JobSalaryRanges(Job, StartingSalary, MaximumSalary)

AS SELECT JOB_TITLE, MIN_SALARY, MAX_SALARY 

FROM JOBS

WHERE MIN_SALARY > 50000 AND MAX_SALARY < 100000;

STORED PROCEDURES

: a set of SQL statements storing and executing on the DB server\

: instead of sending multiple SQL statements from client to server, you encapsulate them in a stored procedure on the server and send one statement from the client to execute them 

 

: Stored procedures can use both input and output parameters.

 

: Reduction in network traffic 

: performance 

: reuse of code

: security

 

CREATE PROCEDURE statement 

CREATE PROCEDURE UPDATE_SAL ( IN empNum CHAR(6), IN rating SMALLINT)
	LANGUAGE SQL 
    BEGIN 
    	IF rating = 1 THEN
        	UPDATE employees 
            	SET salary = salary*1.10
                WHERE emp_ID = empNum; 
		ELSE 
        	UPDATE employee
            	SET salary = salary*1.05
                WHERE emp_id=empNum; 
	END IF;
END

: you can use the information passed to the procedure, the parameters, directly in your procedural logic

 

Call from external applications/Dynamic SQL statements 

-> CALL UPDATE_SAL ('E1001',1)

 

--#SET TERMINATOR @
CREATE PROCEDURE RETRIEVE_ALL       -- Name of this stored procedure routine

LANGUAGE SQL                        -- Language used in this routine 
READS SQL DATA                      -- This routine will only read data from the table

DYNAMIC RESULT SETS 1               -- Maximum possible number of result-sets to be returned to the caller query

BEGIN 

    DECLARE C1 CURSOR               -- CURSOR C1 will handle the result-set by retrieving records row by row from the table
    WITH RETURN FOR                 -- This routine will return retrieved records as a result-set to the caller query
    
    SELECT * FROM PETSALE;          -- Query to retrieve all the records from the table
    
    OPEN C1;                        -- Keeping the CURSOR C1 open so that result-set can be returned to the caller query

END
@                                   -- Routine termination character

CALL RETRIEVE_ALL;       -- caller query

DROP PROCEDURE RETRIEVE_ALL;

CALL RETRIEVE_ALL;

 

 

--#SET TERMINATOR @
CREATE PROCEDURE UPDATE_SALEPRICE ( 
    IN Animal_ID INTEGER, IN Animal_Health VARCHAR(5) )     -- ( { IN/OUT type } { parameter-name } { data-type }, ... )

LANGUAGE SQL                                                -- Language used in this routine
MODIFIES SQL DATA                                           -- This routine will only write/modify data in the table

BEGIN 

    IF Animal_Health = 'BAD' THEN                           -- Start of conditional statement
        UPDATE PETSALE
        SET SALEPRICE = SALEPRICE - (SALEPRICE * 0.25)
        WHERE ID = Animal_ID;
    
    ELSEIF Animal_Health = 'WORSE' THEN
        UPDATE PETSALE
        SET SALEPRICE = SALEPRICE - (SALEPRICE * 0.5)
        WHERE ID = Animal_ID;
        
    ELSE
        UPDATE PETSALE
        SET SALEPRICE = SALEPRICE
        WHERE ID = Animal_ID;

    END IF;                                                 -- End of conditional statement
    
END
@                                                           -- Routine termination character

CALL RETRIEVE_ALL;

CALL UPDATE_SALEPRICE(1, 'BAD');        -- Caller query

CALL RETRIEVE_ALL;

CALL RETRIEVE_ALL;

CALL UPDATE_SALEPRICE(3, 'WORSE');      -- Caller query

CALL RETRIEVE_ALL;