README.md

How to learn SQL for free | Roadmap to learning SQL

In this video, a complete roadmap on how to learn SQL for Beginners, Intermediate level, Advanced level and for database developers will be found.

You will know how to choose a database and what SQL concepts you need to learn to have basic, intermediate, advanced and developer level knowledge of SQL.

Watch the video here

Pre-Requisites

Choose your relational database (RDBMS)

• Most popular RDBMS are:

  • Oracle
  • MySQL
  • Microsoft SQL Server
  • PostgreSQL

• Checkout DB-Engines to get list of popular databases

Install the database

• If using cloud based database/datawarehouse then make sure to setup on your system.

Install the IDE

Mentioned here are the IDE for 4 most popular RDBMS:

Basic SQL

Concepts

What is a Relational Database / RDBMS?

• How data is stored in a relational database?
• What is a schema wrt to a relational database?

SQL commands

• DDL, DML, DCL, TCL, DQL
• What are commands under each of these category and what each of these commands actually do?

Data Types

• String data type like VARCHAR, TEXT etc
• Integer data type like INT, NUMBER etc.
• DATE
• FLOAT / DECIMAL
• BOOLEAN
• Also check out IDENTITY column (Auto Increment column)

Constraints

• Primary key
• Foreign key
• Check constraint, Not null constraint, Unique constraint, Default etc.

Normalization in SQL

• Different normal forms like 1NF, 2NF, 3NF, BCNF

Operators

• Arithmetic operator
• Logical operator
• Comparison operator
• UNION, UNION ALL operator

CASE statement

• Simple case statement as well nested case statement.

Important SQL clause

• DISTINCT clause
• Order by clause
• Limit / Top clause

INNER join

• How to fetch data from multiple tables.

Learning Resources

• Install PostgreSQL database and PgAdmin tool
• Database tutorial
• Complete Basic SQL tutorial
• W3Schools

Practice Platforms

• StrataScratch (Easy SQL problems)
• LeetCode (Easy SQL problems)
• DataLemur (Easy problems)

Jobs

• Business Analyst
• Junior Analyst roles
• Software Engineer

Intermediate SQL

Concepts

Group By and Having clause

Aggregate functions

Order of Execution

Sub-Queries

CTE table / WITH clause

All type of Joins

• LEFT Join, RIGHT Join, FULL OUTER Join
• CROSS Join, SELF Join

In-built functions

• String functions like Substring, Position, Coalesce etc.
• Date functions like Extract, To_Date etc.

Window functions

• Most important are RANK, DENSE_RANK, ROW_NUMBER, LEAD, LAG
• Also good to learn FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE

Views

Learning Resources

• Group By, Having clause
• Order of Execution
• Sub-Queries Tutorial
• CTE / WITH clause Tutorial
• JOINS Tutorial
  • Part 1
  • Part 2
• Window functions Tutorial
  • Part 1
  • Part 2
• Views Tutorial
• In-built functions
  • PostgreSQL LEFT: Get First N Characters in a String
  • Oracle String Functions By Examples
  • SQL Server String Functions
  • MySQL String Functions Overview
• Date functions:

  • PostgreSQL AGE Function

  • Oracle Date Functions

  • SQL Server Date Functions Overview

  • MySQL Date Functions: Handling Date & Time Data More Effectively

Practice Platforms

• StrataScratch (Medium level problems)
• LeetCode (Medium level problems)
• DataLemur (Medium level problems)
• SQL case study from Data with Danny

Jobs

• Data Analyst
• Data Scientist

Advance SQL

Concepts

Recursive SQL Queries

PIVOT table / CROSSTAB function

Materialized Views

Stored Procedure

User Defined Functions

Learning Resources

• Recursive SQL Queries
• PIVOT table / CROSSTAB function
• Materialized Views
• Stored Procedure
• JOINS Tutorial
  • Part 1
  • Part 2
• Window functions Tutorial
  • Part 1
  • Part 2
• Views Tutorial
• User Defined Functions

Practice Platforms

• StrataScratch (Hard level problems)
• LeetCode (Hard level problems)
• DataLemur (Hard level problems)
• SQL case study from Data with Danny

Jobs

• Data Engineer
• ETL Developers

SQL for Database Developers / SQL Developers

Concepts

Indexes

Triggers

Temporary tables

Dynamic Execution of SQL statements

PL/SQL concepts

• Variables
• Cursors
• Collection types
• Loop statements
• IF Else statement
• Exception Handling
• Packages

Performance tuning

• Explain plan
• Table Statistics
• Table Partitioning
• DBMS_Profiler
• SQL Trace and TKProf

Learning Resources

• Indexes
• Triggers
• Temporary tables
• Dynamic Execution of SQL statements
• PL/SQL concepts

Practice Platforms

• Explain Plan:

  • Understanding Oracle Explain Plan
  • EXPLAIN PLAN FOR and DBMS_XPLAN.DISPLAY

• Table Statistics:

  • Oracle Tables and Statistics

• Table Partitioning:

  • 5.11. Table Partitioning

• DBMS_Profiler:

  • DBMS_PROFILER

• SQL Trace and TKProf:

  • SQL trace, 10046, trcsess and tkprof in Oracle

Jobs

• Database Developers
• SQL Developers