1. Case study stored procedures
It's time to write the stored procedures to solve the business case objectives.
2. Taxi ride business problem
You completed EDA, created user defined functions, and learned how to format data to improve its readability. It's time to put that all together into two stored procedures: Borough Statistics and Pickup Location Shift Stats.
3. Evolution of an SP
As you develop stored procedures, you can break down the requirements into pieces. Start with a simple initial query and then test to verify it's providing the data as intended. Then add more complexity by incorporating additional requirements.
For example, to create the Borough Statistics stored procedure, start with some calculations on the detail records, including the user defined functions, as shown in this code section.
4. Next step
Once you have verified the detailed data results are correct, you can add the GROUP BY and ORDER BY clauses, as well the JOIN to the Zone table. Then you should compare the summarized data to your detailed result set and verify the GROUP BY and JOIN is behaving the way you intended.
5. "Last" step
Once you are comfortable with the query results, you can add CREATE OR ALTER PROCEDURE keywords to enclose the query. After the stored procedure exists, it should be tested in various execution scenarios to ensure it is robust. The CREATE OR ALTER statement is helpful when making changes to the SP during development since you will get an error if you attempt to CREATE a stored procedure that already exists.
You could also use the DROP PROCEDURE IF EXISTS statement before attempting to run the CREATE statement, as shown in the second code block. If the stored procedure doesn't exist, the DROP PROCEDURE IF EXISTS statement will execute without error.
6. Your turn!
It's time for you to solve the business case.