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.
1- 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?
2- 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.
DML commands
- INSERT, UPDATE, DELETE
- MERGE command can be optional for beginners.
TCL commands
- COMMIT and ROLLBACK
- SAVEPOINT is optional for beginners
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
3- 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.
4- 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 analyzing 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.
5-Practice writing advanced 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
- SQL Basics Book
- SQL Complete Tutorial Playlist
- techTFQ Very Good YouTube Channel Tutorial
- Datalemur Practice SQL Interview and Data Science Interview questions
- W3Schools for SQL syntax
- LearnSQL.com for SQL course
- StrataScratch for practicing basic, intermediate and complex SQL queries
- codewars for practicing basic SQL queries
All the best to you.
Top comments (0)