Advanced SQL

ER Diagram for classicmodels database

Stored Functions

A stored function is very similar to the functions we create in other languages. The function consists of the function name, parameters, and return value. Writing common queries can always do the work functions do. Functions simply make the procedure more clear and reproducible.

Stored Procedure

Stored procedures save you time by putting the queries into “scripts”. It works similarly to the “Automator” on your mac.

  1. They are pre-compiled and can be executed when called. Instead, stored functions will re-compile every time we call them.
  2. Stored procedures do not require a return value.
  3. Procedures can have inputs and outputs. Functions only have inputs.
  4. We can call functions in a procedure. The other way around does not work.
  5. Functions only work with SELECT. Procedures work with SELECT, INSERT, UPDATE, and DELETE.

Trigger

When we modify a table, the trigger will fire and keep records/create reminders for this modification. We can create a trigger before or after the modification, and the modifications can be INSERT, UPDATE, or DELETE. We use OLD and NEW to refer to the tables before /after the modification. The availability of OLD and NEW can be described as below:

Common Table Expressions (CTE)

A CTE (Common Table Expression) is a temporary result set that you can reference within other statements (SELECT, INSERT, UPDATE or DELETE). CTE helps promote readability, allows recursion, and it can be substituted for a View. CTE starts with a WITH operator.

Recursive CTE

Recursive CTE’s are CTE’s that reference themselves. If you have experience with recursion in other programming languages, you can get it pretty easily. Let’s recall how recursion works in Python with the Fibonacci example.

  1. We pass 1(n), 0(fib_n), 1(next_fib_n) as a base (we call it anchor) to the recursive CTE.
  2. We then use UNION ALL to achieve “n+1”. 2(n), 1(fib_n), 1 (next_fib_n) are passed to the recursive CTE again.
  3. The recursion continues until it hits the condition(where n < 10). The result looks like:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sean Zhang

Sean Zhang

Data Science | Machine Learning| Data Engineer