SQL for Everyone [Part 2]

If you’re a complete beginner, start here.

There’s a ton of blog posts out there that explain the “important SQL functions” with examples. But important is subjective, isn’t it? So here’s the SQL functions I’ve used the most - all of these have very extensive set of usecases and I’m sure every almost-beginner would benefit by understanding these & keeping them handy.

Let’s dive right in!

JOINS

Joins are used to literally join two or more tables on a key column which is same in the tables being joined. This way, row 1 from table 1 is joined with row 1 of table 2 & in the output, it becomes a common continuous row. Below. roll_no is the key column.

SELECT * from students_table INNER JOIN marks_table ON students_table.roll_no = marks_table.roll_no

I’ll let the illustrative picture better explain the different types of joins that exist.

Subqueries

A subquery is a SELECT statement that is nested within another statement. You can use a subquery to return data that will be used in the outer query. Here is an example of a subquery in SQL:

SELECT product_id, product_name,
    (SELECT AVG(price) FROM products) AS avg_price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

In this example, the subquery SELECT AVG(price) FROM products is used to calculate the average price of all products. The outer query then selects all products with a price greater than the average price, and returns the product ID, product name, and average price for each matching product.

CTE

My most beloved CTE. Whenever I’m down with a difficult, complex problem statement & I’m unsure how to go about it, I start writing a CTE.

A CTE (Common Table Expression) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. You can use a CTE to simplify complex queries by breaking them up into smaller, more manageable pieces.

WITH top_selling_products AS (
    SELECT product_id, product_name,
        SUM(quantity) AS total_sold
    FROM sales
    GROUP BY product_id, product_name
)
SELECT * FROM top_selling_products
WHERE total_sold >=10;

How did the CTE help? I wanted retrieve entries withith total_sols>=10 and it’s not possible to specify this in the WHERE clause because it’s an aggregated column. CTE helps me do this.

So, basically, CTE is an abstract table you create within the query!

Aggregate Functions

These functions help you aggregate rows and perform calculations like MIN(), MAX(), AVG(), SUM() & COUNT() on them - and to do perform these calculations, you need to GROUP BY a column.

Window Functions

SQL window functions allow you to perform calculations across a set of rows and return the results in a new set of rows or columns. So how’s window function different from aggregate function, you might ask.

a. You don’t use GROUP BY with window functions because they’re aggregated “over” a “window frame”. So you use OVER() here. What does OVER() do?

• It Partitions rows into form set of rows. (PARTITION BY clause is used)
• It orders rows within those partitions into a particular order. (ORDER BY clause is used)

b. Unlike aggregate functions, they don’t “aggregate” to produce one value for an aggregate. Window functions produce one calculated value per row.

Types of Window Functions: RANK() , LEAD() , LAG() , NTILE()

--Ranking
SELECT product_id, product_name, price,
    RANK() OVER (ORDER BY price DESC) as price_rank
FROM products_table;

Wildcards

Wildcards are special characters that can be used in SQL to match patterns in data. It’s crazy how I’ve used wildcards in almost every query where I worked with STRING datatype. I personally love the freedom these give me.

w3school has beautifully yet simply explained wildcards, take a look here

Functions used for extracting DateTime / Timestamp

CURRENT_DATE(), CURRENT_TIME() fetches current date & current time respectively. Personally I’ve found working with timestamp functions a little tricky. So here’s the functions that came to my rescue:

DATE_ADD: The DATE_ADD function adds a specified interval to a date or time value

Extract: The EXTRACT function retrieves a specific part of a date or time value (such as the year, month, or day).

--Date_Add()
SELECT DATE_ADD(timestamp, INTERVAL 1 DAY) as delivery_date
FROM orders;

-- Extract
SELECT EXTRACT(MONTH FROM timestamp) as order_year
FROM orders;

And, my favourite of all…

CASE Statments

It’s incredible what case statements can help you achieve! Basically a CASE statement is a control flow statement that allows you to specify multiple conditions and return different results based on those conditions. Let me show 2 examples.

--Example 1
SELECT product_id, product_name,
    CASE
        WHEN price > 100 THEN 'expensive'
        WHEN price > 50 THEN 'moderate'
        ELSE 'cheap'
    END AS price_category
FROM products;

--Example 2
SELECT product_name,
    CASE
        WHEN category LIKE "%item%" THEN 'Item'
        WHEN category LIKE "%tool%" THEN 'Tool'
        WHEN category LIKE "%gear%" THEN 'Gear'
        ELSE 'miscellaneous'
    END AS product_category
FROM products;

And that’s almost everything! Easy peasy lemon squeezy, right!?

When starting with SQL, I took some time to absorp these concepts and commands, but it’s crazy how they’ve become a part of my daily & I use it as my second language :)