Guest Blog – SQL Framework

😁 Following my last post, I want to continue to have a more detailed framework for the query language branch.

Imagine that I have several tables to extract data, what data do I want?

💪 The most frequent steps are:

Step 1. Choose tables with FROM         

Step 2. Add conditions with WHERE (=, non-equals, BETWEEN, IN, LIKE)

Step 3. Choose columns with SELECT and simple aggregation {COUNT, MAX, MIN, AVG, SUM, DISTINCT}

Step 4. ORDER BY to sort out the rows and LIMIT to choose the specific amount from the beginning

👉 The possible scenarios are:

Option 1. Merge columns into one table with JOIN {LEFT, SELF, FULL, CROSS} and ON (=), USING

          Merge rows into one table with UNION {INTERSECT, EXCEPT}

Option 2. Classify all rows into groups for calculation and comparison {GROUP BY, HAVING}

Option 3. Add new aggregated or transformed columns in temporary WINDOWs and add new values to each row, WINDOW can be one if ignores the PARTITION BY

               Aggregation functions MIN / MAX/ AVG/ SUM (…) OVER(PARTITION BY…)

               Ranking in groups RANK()/ DENSE_RANK() OVER(PARTITION BY… ORDER BY…)

               Move the value up or down LEAD/LAG (…, offset) OVER(ORDER BY…)

               Create a WINDOW related to the current row OVER(ORDER BY… ROWS/RANGE/GROUPS BETWEEN {n /UNBOUNDED PRECEDING, CURRENT ROW} AND { n /UNBOUNDED FOLLOWING, CURRENT ROW})

💁‍♀️ The special techniques are:

Technique 1: Use () to combine queries, called subquery, the latter can use the output of the former one

Technique 2: Create Common Table Expression (CTE) to create a temporary table for simplification WITH table_name AS (……)

Technique 3: Define a Window then OVER it in SELECT clause {WINDOW window_name AS (PARTITION BY…)}

✌ With this, I can solve problems by combining them together. The more questions I solve, the more functions I will know, and the framework will be enriched. #sql #learningandgrowing            

 

Follow up Guest Blog by Ivy Li who has been reskilling and is Actively looking for a 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐬𝐭 role – check out her website here.

If any of our members or women looking to get into or are already in the tech industry and wish to share their knowledge then please feel free to do a guest blog with us – we’re here to support the community.