Advanced SQL cheatsheet!

For this practice we are going to use WORLD database from mysql.com. you can download from here: https://dev.mysql.com/doc/index-other.html

Subqueries and Correlated Subqueries:
SQL subqueries are nested queries placed inside another query, allowing you to perform complex filtering, calculation, or joining operations. Subqueries can return single values, multiple values, or entire tables, depending on how they’re used. Below are different types of SQL subqueries and examples of how to use them.

I want to display name of the cities, country code, district, population having more than average population:

show entries having more languages more than India: When i ran query for this, I found out India is having highest number of languages so there’s no country available having more languages than India, here’s the query I ran to find out Number of languages in each counrties in Descending order then Ran another query to find out countries having lesser number of languages in descing order

Including Country name as well from Country table using Join:

Stored Procedure:

A stored procedure is a prepared SQL code that you can save, so that the code can be reused over and over again.

Triggers in SQL:

An automated stored procedure that runs automatically when something is triggered in SQL server.

Triggers are SQL codes that are automatically executed in responsw to certain events on a particular table. Triggers help to maintain the integrity of the data.

Syntax:

CREATE TRIGGER TRIGGER_NAME

(before | after) –> a

[INSERT | UPDATE | DELETE] –> b

on [TABLE_NAME]

[FOR EACH ROW | FOR EACH COLUMN] –> c

[TRIGGER_BODY] –> d

See and delete triggers:

SHOW TRIGGERS

IN DATABASE_NAME;

To Delete: DROP TRIGGER Trigger_NAme

Let us understand the syntax!

`(before | after)` –> `a`

This part specifies when the trigger should be fired:

BEFORE: The trigger executes before the operation (INSERT, UPDATE, or DELETE) takes place.
AFTER: The trigger executes after the operation has been completed.

`[INSERT | UPDATE | DELETE]` –> `b`

This specifies what type of event will fire the trigger:

INSERT: The trigger is fired when a new row is inserted into the table.
UPDATE: The trigger is fired when a row is updated.
DELETE: The trigger is fired when a row is deleted from the table.

`[FOR EACH ROW | FOR EACH COLUMN]` –> `c`

This specifies how the trigger should operate:

FOR EACH ROW: The trigger will be executed for every row that is affected by the operation (INSERT, UPDATE, DELETE).
FOR EACH COLUMN: Not often used in all SQL systems, but in some cases, this option could apply to column-level triggers (when the trigger operates based on changes in specific columns).

Now here are examples of triggers for each of the three tables.

Before Insert Trigger (Checking Population > 0):

Trying to insert an entry:

Here’s the result:

CTE: Common table expression:

A CTE is a temporary named result set created by a simple select statement that can be used in a subsequesnt select statement.
We define CTEs by adding a with clause directly before select, insert, update, delete, merge statement.
The with clause can include one or more CTEs separated by commas

Syntax:
CTE query
WITH MY_CTE as(
SELECT a,b,c
FROM TABLE 1)

main query

SELECT A,C

FROM MY_CTE

CTE query and MAIN query have to be executed together.
JOINs can also be implemented with CTEs
CTEs are used to optimise SQL queries

Recursive CTE:

A recursive CTE references itself, it returns the result subset, then it repeatedly references itself and stops when it returns all the results.

It has three elements:

Non recursive term: It’s a CTE query definition that forms the base result set of the CTE structure.
Recursive term: One or more CTE query definition joined with non-recursive term using UNION or UNION all operator.
Termination check: The recursion stops when no rows are returned from the previous iteration.

Recursive CTE syntax:

WITH RECURSIVE CTE_NAME AS(
CTE_QUERY_DEFINITION
UNION ALL
RECURSIVE_QUERY_DEFINITION
)
SELECT * FROM CTE_NAME

explanation:

Anchor member:

The base case starts with n=1 and factorial=1.

Recursive member:

The recursive part takes the previous n, increments it by 1 (n+1), and multiplies the previous factorial by the new n.

WHERE clause:

This ensures that the recursion stops when n reaches 5 (you can change this to calculate higher factorials).

output:

Another example:

output:

Views:

Views are kind of a virtual table, a view also has rows and columns like tables, but it doen’t store data on a disk. VIew defines a customised query that retrieves data from one or more tables.

Syntax: Create view as {

condition

}

Views allow you to simplify complex SQL queries by encapsulating them in a single query. Instead of rewriting a long, complex query with multiple joins and conditions each time, you can define a view and query it like a table.
Once a view is defined, it can be reused in multiple queries across the database. This promotes consistency and avoids duplication of logic.
Views can encapsulate complex JOIN operations or calculations, making it easier for users to work with data without needing to know the relationships between the tables.

Display all views:
SHOW FULL TABLES
WHERE TABLE_TYPE=’VIEW’

Delete Views:

drop view view_name.

here created a VIEW by leveraging two Tables in our database countrylanguage and country. This would give us CountryName, CountryCode and LanguageCount in a country, We would be using this view later on, here’s the view:

Window Functions:

Window in SQL: Set of rows or observations in a table or result set.
Window functions are basically functions that enable us to perform operations on a window or set of rows.
Window functions apply Aggregate, ranking, analytical functions over a particular window

Aggregate functions: sum, avg, count, min, max
Ranking functions: Row_number, Rank, dense_Rank, Percent_Rank

Value/ Analytical: Lead, Lag, First_Value, Last_Value, NTILE

SQL understands a Window function seeing the OVER() word.

here’s the output:

Explataion of the query:

This is a window function that counts the number of languages per CountryCode.
PARTITION BY CountryCode tells the query to count the number of languages within each country (i.e., partitioning the data by CountryCode).
The OVER() clause makes sure the count is calculated for each row without collapsing the data.
LanguageCount is an alias for the result of the window function, representing how many languages exist for that particular country.

Let’s see another query to calculate row_number, rank, dense_rank based on the languageCount from the langDetails2 view:

let’s check the output now:

Here you can see the difference between rank() and dense_rank(). Rank() skipped 2-5 when languageCount decreased from 12 to 11, on another hand dense_rank() didn’t skip 2,3,4,5.

Lead(), Lag(): Lead returns the value from the next row, Lag() returns the value from the previous row.

We can also use Lead(count,2) to take the value from the next to next row.

Difference betwen Aggregate functions and Window functions:

Normal (Aggregate) Functions:
Aggregate functions, such as SUM(), COUNT(), AVG(), etc., perform calculations on a group of rows and return a single value for the group. They are used with the GROUP BY clause to aggregate data across multiple rows.

Window Functions:
Window functions also operate over a set of rows, but unlike aggregate functions, they do not collapse the rows into a single result. Instead, window functions return a value for each row, while calculating values over a defined window (a subset of rows, which could be defined by PARTITION BY or ORDER BY).

let’s see an example,

Let’s calculate the number of languages spoken per country using a normal aggregate function.

here’s the output,

now,

Let’s return the total number of languages spoken per country without collapsing the rows, using the window function COUNT().

here’s the outcome now:

Explanation:

This query calculates the total number of languages spoken in each country (LanguageCount), but the result set does not collapse into one row per country.
Instead, each row represents a single language spoken in a country, while showing the total number of languages spoken in that country.

Another Window Function Example (Average Population in the country table):

This query calculates the average population of countries within each continent. It returns one row per continent.

Window Function:

The window function calculates the average population per continent, but each row still represents a country. It doesn’t collapse the rows like an aggregate function.

That’s it for now! I hope you found the article helpful. If you haven’t had a chance to explore my other articles, feel free to check them out. I’m always open to questions, so don’t hesitate to reach out if you have any doubts or need clarification.