The Five Levels Of SQL

Introduction. Hello there data enthusiasts and welcome to yet another post on SQL. Today we are gonna dive into the five levels of SQL and I promise you are gonna love it and learn. So let's do some nerdy stuff. Shall we: 1. What is SQL: Before we can get too ahead of ourselves, let's develop a deeper more intuitive understanding of SQL. As you might have already heard, SQL (STRUCTURED QUERY LANGUAGE) is the language used to interact with structured relational database engines eg PostgreSQL or MySQL. The interaction with database using SQL may involve one or all of the following activities: Retrieving records from database.-> SELECT. Adding new records to the database. -> INSERT. Modifying existing records. -> UPDATE. Removing existing records. -> DELETE. The most best database to write SQL queries is PostgreSQL because it is object relational database, it is modern and most importantly it is open source (no license required). Most other databases however like oracle and MySQL may require a license for you to use them. It is best to learn SQL queries from terminal(using psql shell) because if you happen to SSH to a server, you can be able to handle the database effectively unlike when you are used to using GUI related databases. Now let's dive into the 5 levels of SQL query writing. 2. The five levels of SQL LEVEL ZERO This involves knowing to use the SELECT * FROM table_name; This is an equivalent of opening an excel sheet or a word document to get your hands into the data. If you can play around with this query intuitively, then you are on the right track to mastering SQL. LEVEL ONE Do you know the rest of the key words like SELECT, FROM, WHERE, GROUP BY, HAVING, LIMIT, ORDER BY etc. Do you also understand the order of execution of these key words when you combine them in one query which is FROM, WHERE, GROUP BY, HAVING, SELECT,ORDER BY,LIMIT. See if you understand what is going on in the following sample code. SELECT id,, first_name FROM person WHERE country_of_birth = 'Kenya' GROUP BY gender HAVING COUNT() > 5 ORDER BY id LIMIT 10; LEVEL TWO Can you master joins that you are going to use like the inner join and left join which are the most common and the less common include full outer join. Joins that you are not going to use very much are going to be right join and cross join.example sample query SELECT p.first_name, s.salary FROM person p INNER JOIN salary s ON p.id = s.person_id; Also make sure you have a good understanding of common table expressions(CTEs) and more often use those than sub queries eg by using with the WITH keyword. sample code below. Can you explain to someone what is going on? WITH recent_births AS ( SELECT FROM person WHERE date_of_birth > '2020-01-01' ) SELECT * FROM recent_births WHERE gender = 'Female'; LEVEL THREE You know window functions. IN this case you have a function eg SUM(), RANK() or AVG() and then you have the over clause and you have the window. A window is defined by partition, by order in rows. Also in this level you should be able to tell the difference between a RANK(skips ranks on ties), DENSE RANK(no rank gaps on ties) and ROW NUMBER( unique sequence). For example, do you relate with the following sample code. SELECT id, first_name, RANK() OVER (PARTITION BY country_of_birth ORDER BY date_of_birth) AS birth_rank FROM person; LEVEL FOUR Understand table scans to be able to get the right optimization techniques eg TABLE SCANS, INDICES, PARTITIONING etc. For example using EXPLAIN ANALYZE to inspect query plans. eg EXPLAIN ANALYZE SELECT * FROM person WHERE country_of_birth = 'Kenya' LEVEL FIVE If you have made it up to this far, you wouldnt be surprised to know that at level five, you should be able to write these queries effectively, debug them for errors without the help of an LLM. Only use LLMs to speed up your query writing process or automate repetitive tasks. So at what level of SQL mastery are you at. I'd love to know. Leave a comment below. Hope you enjoyed the read and see you in the next one.

May 2, 2025 - 22:45

Introduction.

Hello there data enthusiasts and welcome to yet another post on SQL. Today we are gonna dive into the five levels of SQL and I promise you are gonna love it and learn. So let's do some nerdy stuff. Shall we:

1. What is SQL:

Before we can get too ahead of ourselves, let's
develop a deeper more intuitive understanding of SQL. As you might have already heard, SQL (STRUCTURED QUERY LANGUAGE) is the language used to interact with structured relational database engines eg PostgreSQL or MySQL. The interaction with database using SQL may involve one or all of the following activities:

Retrieving records from database.-> SELECT.
Adding new records to the database. -> INSERT.
Modifying existing records. -> UPDATE.
Removing existing records. -> DELETE.

The most best database to write SQL queries is PostgreSQL because it is object relational database, it is modern and most importantly it is open source (no license required). Most other databases however like oracle and MySQL may require a license for you to use them. It is best to learn SQL queries from terminal(using psql shell) because if you happen to SSH to a server, you can be able to handle the database effectively unlike when you are used to using GUI related databases. Now let's dive into the 5 levels of SQL query writing.

2. The five levels of SQL

LEVEL ZERO
This involves knowing to use the SELECT * FROM table_name; This is an equivalent of opening an excel sheet or a word document to get your hands into the data. If you can play around with this query intuitively, then you are on the right track to mastering SQL.

LEVEL ONE
Do you know the rest of the key words like SELECT, FROM, WHERE, GROUP BY, HAVING, LIMIT, ORDER BY etc. Do you also understand the order of execution of these key words when you combine them in one query which is FROM, WHERE, GROUP BY, HAVING, SELECT,ORDER BY,LIMIT. See if you understand what is going on in the following sample code.

SELECT id,, first_name
FROM person
WHERE country_of_birth = 'Kenya'
GROUP BY gender
HAVING COUNT(*) > 5
ORDER BY id
LIMIT 10;

LEVEL TWO
Can you master joins that you are going to use like the inner join and left join which are the most common and the less common include full outer join. Joins that you are not going to use very much are going to be right join and cross join.example sample query

SELECT p.first_name, s.salary
FROM person p
INNER JOIN salary s ON p.id = s.person_id;

Also make sure you have a good understanding of common table expressions(CTEs) and more often use those than sub queries eg by using with the WITH keyword. sample code below. Can you explain to someone what is going on?

WITH recent_births AS (
  SELECT * FROM person WHERE date_of_birth > '2020-01-01'
)
SELECT * FROM recent_births WHERE gender = 'Female';

LEVEL THREE
You know window functions. IN this case you have a function eg SUM(), RANK() or AVG() and then you have the over clause and you have the window. A window is defined by partition, by order in rows. Also in this level you should be able to tell the difference between a RANK(skips ranks on ties), DENSE RANK(no rank gaps on ties) and ROW NUMBER( unique sequence). For example, do you relate with the following sample code.

SELECT id, first_name,
       RANK() OVER (PARTITION BY country_of_birth ORDER BY date_of_birth) AS birth_rank
FROM person;

LEVEL FOUR
Understand table scans to be able to get the right optimization techniques eg TABLE SCANS, INDICES, PARTITIONING etc. For example using EXPLAIN ANALYZE to inspect query plans. eg

EXPLAIN ANALYZE
SELECT * FROM person WHERE country_of_birth = 'Kenya'

LEVEL FIVE
If you have made it up to this far, you wouldnt be surprised to know that at level five, you should be able to write these queries effectively, debug them for errors without the help of an LLM. Only use LLMs to speed up your query writing process or automate repetitive tasks.

So at what level of SQL mastery are you at. I'd love to know. Leave a comment below. Hope you enjoyed the read and see you in the next one.