Step by Step guide on how to learn SQL
If you are someone who wants to learn SQL from scratch or you are someone who has learnt the SQL basic but still does not feel confident then this blog is for you.
SQL is an relatively easy programming language to learn but it’s very important that you learn it the right way. That is why in this blog, you will find the step by step guide on how to learn SQL. I shall provide all the difference SQL concepts you need to learn from scratch and the order in which you should be learning them.
I will also provide what are the different concepts you must cover when learning to write basic, intermediate and advance level of SQL Queries.
Step by Step guide to learn SQL
Understand the fundamentals:
Understanding what is SQL, why is it used and where is it used can help you to better understand your purpose of learning SQL. Get familiar with the following terms:
What is Data, Database and RDBMS
What is SQL and what is the purpose of using it?
How data is stored in RDBMS?
EXERCISE: Install a relational database and an SQL IDE on your computer. For example, install PostgreSQL database and PgAdmin tool (IDE) to start working with SQL in your computer. You can install any other RDBMS as well such as Microsoft SQL Server, MySQL, Oracle etc.
Learn the SQL commands:
DDL commands
CREATE, ALTER, DROP, TRUNCATE
Learn about Data type
Some popular data types are VARCHAR, INT, DATE, FLOAT, BOOLEAN
There are several other data types as well which can be optional for beginners.
Learn about Constraints
PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and NOT NULL
There are few other constraints as well which can be optional for beginners.
EXERCISE: Create a sample database and then create some sample tables in it. Experiment by creating tables with different data type and constraints. Also try out altering the table structure and dropping the table.
DML commands
INSERT, UPDATE, DELETE
MERGE command can be optional for beginners.
EXERCISE: Load some data into the above created tables. Try updating and deleting some data from these tables.
TCL commands
COMMIT and ROLLBACK
SAVEPOINT is optional for beginners.
EXERCISE: Check out how table data reacts when using commit and rollback after performing DML operation on your sample tables.
DCL commands
GRANT and REVOKE are the DCL commands but learning these can be optional for beginners.
DQL commands
Understand what SELECT, FROM and WHERE clause is used for.
EXERCISE: Write couple of queries using SELECT, FROM and WHERE clause on your sample tables.
Practice writing basic SQL queries using below concepts:
Once you have learnt the different SQL commands and have practiced creating some sample tables and loaded some data into them, it is time to write SQL Queries.
Start with some basic SQL Queries using the below concepts:
Practice writing some SQL Queries using some of the below operators:
Comparison Operators like =, <>, != , >, <, >=, ≤
Arithmetic Operators like +, -, *, /, %
Logical Operators like AND, OR, NOT, IN, BETWEEN, LIKE etc.
UNION and UNION ALL operators
Use CASE statement
Solve some SQL Queries using CASE statement.
Use DISTINCT and LIMIT (or TOP) clause
Learn how to use DISTINCT and LIMIT (or TOP) clause. See how the result changes when you use these clauses.
Also learn about ORDER BY clause to understand how to sort your result in different order.
Use INNER Join
Finally, try to fetch data from multiple tables using the INNER Join.
Practice writing intermediate SQL queries using below concepts:
Subqueries
Try writing SQL Queries where you use the 3 different types of subqueries such as Scalar subquery Multi row subquery and Correlated subquery.
GROUP BY and HAVING clause
GROUP BY is probably something we use all the time when solving problems or analysing data hence definitely write few queries using GROUP BY.
Aggregate functions
Try to use different aggregate functions like MAX, MIN, SUM, AVG and COUNT.
Use aggregate functions with and without group by.
It is not mandatory to use aggregate functions with GROUP BY always so check out the different queries where you may need to use group by and where you may not need it.
Different JOINS.
Write queries using LEFT Join, RIGHT Join, FULL Outer Join and Cross Join.
Inbuilt Date conversion functions
Write queries using inbuilt date conversion functions like to_date or date_format or cast etc.
Inbuilt String conversion functions
Write queries using inbuilt string functions to fetch part of a text from data such as substring, position, instr etc.
Practice writing advance SQL queries using below concepts:
SELF JOIN
SELF Join can help you in many ways to solve problems which may seems impossible to solve using SQL hence being comfortable in using SELF join can help you in many different scenarios.
WITH Clause or CTE Table
Writing SQL Queries using WITH clause has several benefits so explore this feature.
Window functions
Probably one of the best features to solve complex queries are window functions so definitely spend some time to explore the most widely used window functions such as row_number, rank, dense_rank, first_value, last_value, lead, lag, nth_value.
There are other window functions as well such ntile, percent_rank, cume_dist which can be useful in certain scenarios but this can be kept for the last since its not something you would use all the time.
Frame clause is a concept in window function which can help you to solve some unexpected issues. Keep this for the last when you have covered all other concepts.
Learning Resources
W3Schools - for SQL syntax
LearnSQL.com - for SQL course
StrataScratch - for practicing basic, intermediate and complex SQL queries
LeetCode - for practicing basic SQL queries
techTFQ - YouTube Tutorial
Sample SQL scripts for practice
To practice all the concepts covered in this blog, You will need a sample database which contains some sample tables and also contains some realistic data in these tables.
I have already prepared some sample database with tables and data and some sample SQL queries which you can download for free and get started with your SQL learning. Download these scripts below.
