Learn SQL from Basics to Advanced
Complete SQL Tutorial for Beginners with Examples
If you are learning web development, backend development, or software development, you will eventually come across SQL. SQL is one of the most important technologies used to work with databases.
Whether you are building a website, mobile application, e-commerce platform, or any other software application, you often need to store, retrieve, update, and delete data. This is where databases and SQL become important.
In this guide, we will learn what SQL is, how databases work, SQL commands, tables, rows and columns, SELECT, INSERT, UPDATE, DELETE, WHERE, ORDER BY, GROUP BY, JOINs, subqueries, aggregate functions, constraints, primary keys, foreign keys, and many other important SQL concepts with practical examples.
Let's start with the basics.
What is SQL?
SQL stands for Structured Query Language. It is a language used to communicate with and manage relational databases.
SQL allows us to perform common database operations such as:
- Creating databases and tables
- Adding data to tables
- Reading data from tables
- Updating existing data
- Deleting data
- Filtering data
- Sorting data
- Grouping data
- Combining data from multiple tables
- Managing relationships between tables
In simple words:
SQL is a language used to work with data stored in databases.
What is a Database?
Before understanding SQL, you should understand what a database is.
A database is an organized collection of data that allows applications to store and retrieve information efficiently.
For example, imagine that you are building a website where users can create accounts. You might need to store information such as:
name email password age phone number created date
Instead of storing this information randomly in files, we can store it in a database in an organized way.
A relational database stores data in structures called tables.
What is a Table?
A table is used to store related data in rows and columns.
For example, imagine we have a users table:
users +----+----------+---------------------+-----+ | id | name | email | age | +----+----------+---------------------+-----+ | 1 | James | james@example.com | 22 | | 2 | Rahul | rahul@example.com | 25 | | 3 | Arun | arun@example.com | 28 | +----+----------+---------------------+-----+
Here:
- Table = users
- Columns = id, name, email, age
- Rows = individual users
- id = unique identifier for each user
What is a Row?
A row represents one complete record in a table.
For example:
1 | james | james@example.com | 22
This entire line represents one user, so it is one row.
What is a Column?
A column represents a specific type of information stored in a table.
For example:
id name email age
Each column has a specific purpose and usually has a defined data type.
What is a Relational Database?
A relational database stores data in tables and allows relationships to be created between those tables.
For example, an application might have:
users orders products payments
These tables can be connected using relationships.
For example, an order can belong to a particular user.
users | | user_id ↓ orders
This relationship is one of the most important concepts in SQL databases.
Popular SQL Databases
SQL is a language, not a database itself. Different database management systems use SQL.
Some popular SQL database systems are:
- MySQL
- PostgreSQL
- Microsoft SQL Server
- Oracle Database
- SQLite
- MariaDB
Although there are differences between them, the fundamental SQL concepts are largely similar.
SQL vs MySQL
Beginners often confuse SQL and MySQL.
SQL is a language used to communicate with databases.
MySQL is a database management system that uses SQL.
Think of it like this:
SQL = Language MySQL = Database Management System
PostgreSQL, SQL Server, Oracle, and other relational database systems also support SQL.
How Does SQL Work?
When you write an SQL query, you are asking the database to perform an operation.
For example:
SELECT * FROM users;
This query asks the database to return all columns and all rows from the users table.
The basic idea is:
Application
↓
SQL Query
↓
Database
↓
Result
For example, a web application might send:
SELECT name, email FROM users;
The database processes the query and returns the requested data to the application.
What is an SQL Query?
An SQL query is a statement written using SQL that tells the database what operation you want to perform.
Example:
SELECT name FROM users;
This query retrieves the name column from the users table.
SQL Commands
SQL commands are commonly divided into different categories.
The major SQL command categories are:
- DDL - Data Definition Language
- DML - Data Manipulation Language
- DQL - Data Query Language
- DCL - Data Control Language
- TCL - Transaction Control Language
What is DDL?
DDL stands for Data Definition Language. DDL commands are used to define and modify the structure of database objects such as tables.
Common DDL commands include:
CREATE ALTER DROP TRUNCATE
CREATE TABLE
The CREATE TABLE command is used to create a new table.
CREATE TABLE users ( id INT, name VARCHAR(100), email VARCHAR(255), age INT );
This creates a table named users with four columns.
INSERT INTO
The INSERT INTO statement is used to add new records to a table.
INSERT INTO users (id, name, email, age) VALUES (1, 'james', 'james@example.com', 22);
We can insert multiple records at once:
INSERT INTO users (id, name, email, age) VALUES (1, 'james', 'james@example.com', 22), (2, 'Rahul', 'rahul@example.com', 25), (3, 'Arun', 'arun@example.com', 28);
SELECT Statement
The SELECT statement is one of the most commonly used SQL commands. It is used to retrieve data from a database.
To retrieve all columns:
SELECT * FROM users;
The * means all columns.
You can also select specific columns:
SELECT name, email FROM users;
This returns only the name and email columns.
Using WHERE
The WHERE clause is used to filter records.
For example, to find a user whose age is 22:
SELECT * FROM users WHERE age = 22;
You can also use other comparison operators:
= != <> > < >= <=
Example:
SELECT * FROM users WHERE age > 25;
This returns users whose age is greater than 25.
Using AND
The AND operator allows you to apply multiple conditions.
SELECT * FROM users WHERE age > 20 AND age < 30;
Both conditions must be true.
Using OR
The OR operator returns records when at least one condition is true.
SELECT * FROM users WHERE age = 22 OR age = 25;
Using NOT
The NOT operator reverses a condition.
SELECT * FROM users WHERE NOT age = 22;
This returns users whose age is not 22.
Using ORDER BY
The ORDER BY clause is used to sort query results.
Sort from smallest to largest:
SELECT * FROM users ORDER BY age ASC;
Sort from largest to smallest:
SELECT * FROM users ORDER BY age DESC;
ASC means ascending order and DESC means descending order.
Using LIMIT
The LIMIT clause can be used to restrict the number of rows returned.
SELECT * FROM users LIMIT 5;
This returns up to 5 records.
Using DISTINCT
The DISTINCT keyword removes duplicate values from the result.
SELECT DISTINCT age FROM users;
This returns each age only once.
Using UPDATE
The UPDATE statement is used to modify existing records.
UPDATE users SET age = 23 WHERE id = 1;
This changes the age of the user whose id is 1.
Be careful when using UPDATE. If you forget the WHERE condition, you may update every row in the table.
UPDATE users SET age = 23;
The above query updates the age for all users.
Using DELETE
The DELETE statement is used to remove records.
DELETE FROM users WHERE id = 1;
This deletes the user whose id is 1.
Just like UPDATE, be careful with DELETE.
DELETE FROM users;
This deletes all records from the table.
What is a Primary Key?
A primary key is a column, or combination of columns, that uniquely identifies each row in a table.
For example:
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255) );
Here, id is the primary key.
Two users cannot have the same primary key value.
What is a Foreign Key?
A foreign key is used to create a relationship between tables.
For example, suppose we have a users table:
users id name
And an orders table:
orders id user_id product
The user_id column in the orders table can reference the id column in the users table.
CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, product VARCHAR(100), FOREIGN KEY (user_id) REFERENCES users(id) );
This creates a relationship between users and orders.
What are SQL Constraints?
SQL constraints are rules applied to columns to control the type of data that can be stored.
Common SQL constraints include:
- PRIMARY KEY
- FOREIGN KEY
- NOT NULL
- UNIQUE
- CHECK
- DEFAULT
NOT NULL
NOT NULL prevents a column from storing NULL values.
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL );
Every user must have a name in this example.
UNIQUE
The UNIQUE constraint prevents duplicate values.
CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(255) UNIQUE );
This can be useful when every user must have a different email address.
DEFAULT
The DEFAULT constraint provides a default value when a value is not supplied.
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), country VARCHAR(100) DEFAULT 'India' );
What are SQL Data Types?
Every column in a database usually has a data type. The data type defines what kind of value the column can store.
Common SQL data types include:
- INT - Integer numbers
- DECIMAL - Decimal numbers
- VARCHAR - Variable-length text
- CHAR - Fixed-length text
- DATE - Date values
- DATETIME - Date and time values
- BOOLEAN - True or false values
- TEXT - Large amounts of text
Example:
CREATE TABLE products ( id INT, name VARCHAR(100), price DECIMAL(10, 2), description TEXT, created_at DATETIME );
What are SQL Aggregate Functions?
Aggregate functions perform calculations on multiple rows and return a single result.
Common aggregate functions include:
COUNT() SUM() AVG() MIN() MAX()
COUNT()
COUNT() counts rows.
SELECT COUNT(*) FROM users;
This returns the total number of users.
SUM()
SUM() calculates the total of numeric values.
SELECT SUM(price) FROM products;
AVG()
AVG() calculates the average value.
SELECT AVG(price) FROM products;
MIN() and MAX()
MIN() returns the smallest value and MAX() returns the largest value.
SELECT MIN(price) FROM products; SELECT MAX(price) FROM products;
What is GROUP BY?
The GROUP BY clause groups rows that have the same value. It is commonly used with aggregate functions.
For example, imagine an orders table containing:
id | customer | amount 1 | james | 500 2 | Rahul | 300 3 | james | 700 4 | Rahul | 200
We can calculate the total amount for each customer:
SELECT customer, SUM(amount) FROM orders GROUP BY customer;
What is HAVING?
The HAVING clause is used to filter grouped results.
SELECT customer, SUM(amount) AS total_amount FROM orders GROUP BY customer HAVING SUM(amount) > 500;
WHERE filters individual rows before grouping, while HAVING filters grouped results.
What is a SQL JOIN?
A JOIN is used to combine data from multiple tables based on a related column.
Suppose we have two tables:
users id | name 1 | james 2 | Rahul
orders id | user_id | product 1 | 1 | Laptop 2 | 2 | Mouse
We can combine these tables using a JOIN.
INNER JOIN
An INNER JOIN returns rows where there is a matching value in both tables.
SELECT users.name, orders.product FROM users INNER JOIN orders ON users.id = orders.user_id;
The result could look like:
name | product james | Laptop Rahul | Mouse
LEFT JOIN
A LEFT JOIN returns all rows from the left table and matching rows from the right table.
SELECT users.name, orders.product FROM users LEFT JOIN orders ON users.id = orders.user_id;
This is useful when you want to include users even if they do not have an order.
RIGHT JOIN
A RIGHT JOIN returns all rows from the right table and matching rows from the left table.
SELECT users.name, orders.product FROM users RIGHT JOIN orders ON users.id = orders.user_id;
FULL OUTER JOIN
A FULL OUTER JOIN returns matching rows as well as unmatched rows from both tables.
SELECT users.name, orders.product FROM users FULL OUTER JOIN orders ON users.id = orders.user_id;
Support for FULL OUTER JOIN depends on the database system you are using.
What is LIKE in SQL?
The LIKE operator is used to search for a pattern in text.
For example:
SELECT * FROM users WHERE name LIKE 'S%';
The % wildcard represents zero or more characters.
This query finds names that start with S.
Another example:
SELECT * FROM users WHERE name LIKE '%a%';
This finds names containing the letter "a".
SQL Wildcards
Common SQL wildcard characters include:
- % - Represents zero or more characters
- _ - Represents exactly one character
Example:
SELECT * FROM users WHERE name LIKE 'S_v_';
Using IN
The IN operator allows you to match a value against multiple possible values.
SELECT * FROM users WHERE age IN (20, 22, 25);
This is often cleaner than writing multiple OR conditions.
Using BETWEEN
The BETWEEN operator is used to filter values within a range.
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
What is NULL?
NULL represents a missing or unknown value.
NULL is different from:
0 '' false
To find NULL values, use IS NULL.
SELECT * FROM users WHERE email IS NULL;
To find values that are not NULL:
SELECT * FROM users WHERE email IS NOT NULL;
What is a Subquery?
A subquery is a query written inside another SQL query.
For example:
SELECT name FROM users WHERE age > ( SELECT AVG(age) FROM users );
The inner query calculates the average age, and the outer query finds users older than that average.
What is an Alias?
An alias gives a temporary name to a column or table.
Example:
SELECT name AS user_name FROM users;
Here, user_name is an alias for the name column.
Table aliases are also commonly used with JOINs:
SELECT u.name, o.product FROM users AS u INNER JOIN orders AS o ON u.id = o.user_id;
What is an Index?
A database index is a data structure that can help the database find rows more efficiently.
For example:
CREATE INDEX idx_users_email ON users(email);
Indexes can improve the performance of searches on frequently queried columns. However, indexes also use storage and can add overhead when data is inserted or updated.
What is a Database Transaction?
A transaction is a group of database operations treated as a unit.
Transactions are important when multiple operations must succeed together.
Common transaction commands include:
BEGIN COMMIT ROLLBACK
For example:
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
If something goes wrong before committing, a transaction can potentially be rolled back depending on the database system and transaction configuration.
What is ACID in SQL?
Relational databases commonly use the concept of ACID transactions.
ACID stands for:
- Atomicity - A transaction is treated as a unit of work.
- Consistency - Data must follow the database's defined rules.
- Isolation - Concurrent transactions are isolated according to the database's isolation rules.
- Durability - Committed changes are designed to persist.
What is Database Normalization?
Database normalization is a technique used to organize relational database tables and reduce unnecessary duplication of data.
For example, instead of storing the same customer information repeatedly inside every order record, we can separate customers and orders into different tables.
customers id | name | email
orders id | customer_id | product
The customer_id connects the order to the customer.
Normalization can make data easier to maintain and help reduce inconsistent duplicate data.
What is CRUD?
CRUD represents the four basic operations commonly performed on stored data.
- Create - Add new data
- Read - Retrieve data
- Update - Modify existing data
- Delete - Remove data
In SQL, CRUD can be represented as:
Create → INSERT Read → SELECT Update → UPDATE Delete → DELETE
CRUD is an important concept because almost every application needs to perform these operations.
A Complete SQL Example
Let's put several concepts together and create a simple users table.
Step 1: Create the Table
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE, age INT );
Step 2: Insert Data
INSERT INTO users (id, name, email, age) VALUES (1, 'james', 'james@example.com', 22), (2, 'Rahul', 'rahul@example.com', 25), (3, 'Arun', 'arun@example.com', 28);
Step 3: Read the Data
SELECT * FROM users;
Step 4: Filter the Data
SELECT * FROM users WHERE age > 23;
Step 5: Update the Data
UPDATE users SET age = 23 WHERE id = 1;
Step 6: Delete the Data
DELETE FROM users WHERE id = 3;
These simple operations form the foundation of working with SQL databases.
SQL Command Cheat Sheet
CREATE TABLE → Creates a new table INSERT INTO → Adds records SELECT → Retrieves records WHERE → Filters records UPDATE → Modifies records DELETE → Removes records ORDER BY → Sorts records GROUP BY → Groups records HAVING → Filters grouped results JOIN → Combines data from tables DISTINCT → Removes duplicate results LIMIT → Limits returned rows
SQL Best Practices for Beginners
When learning SQL, it is important to develop good habits from the beginning.
- Use meaningful table and column names.
- Always be careful when using UPDATE and DELETE.
- Use WHERE when you only want to modify specific rows.
- Use primary keys to uniquely identify records.
- Use foreign keys when relationships between tables need to be enforced.
- Use indexes for appropriate frequently queried columns.
- Avoid selecting unnecessary columns when you only need specific data.
- Learn how your specific database handles transactions and constraints.
SQL vs NoSQL
SQL databases are generally relational databases that organize data into tables and relationships. NoSQL databases use other data models, such as document, key-value, wide-column, or graph models.
Examples of relational SQL databases include:
MySQL PostgreSQL SQL Server Oracle SQLite
Examples of popular NoSQL databases include:
MongoDB Redis Cassandra
SQL and NoSQL databases solve different types of data-storage problems. The right choice depends on the application's requirements, data model, query patterns, consistency needs, and other factors.
Why Should You Learn SQL?
SQL is useful for many types of software development.
If you are learning web development, SQL can help you understand how applications store users, products, orders, payments, posts, comments, and other data.
SQL is also useful for:
- Frontend developers who need to understand backend data
- Backend developers
- Full-stack developers
- Data analysts
- Software engineers
- Database administrators
- Data engineers
SQL and Web Development
A typical web application may contain several parts:
Frontend ↓ Backend ↓ SQL Database
For example, when a user logs into a website, the backend may query the database to find the user's account.
SELECT id, name, email FROM users WHERE email = 'james@example.com';
The backend receives the result and uses it to continue processing the login request.
In a modern application, the frontend normally does not directly connect to the production database. Instead, application code on the server or through an appropriate backend/data-access layer communicates with the database.
SQL in Full-Stack Development
If you are learning full-stack development, understanding SQL will make it easier to understand how data flows through an application.
A typical flow might look like:
User ↓ Frontend ↓ API / Backend ↓ SQL Query ↓ Database ↓ SQL Result ↓ Backend ↓ Frontend ↓ User
Once you understand this flow, concepts such as authentication, user accounts, e-commerce orders, dashboards, comments, blog systems, and admin panels become easier to understand.
Frequently Asked Questions About SQL
Is SQL a programming language?
SQL is a specialized language designed primarily for working with relational databases. It is different from general-purpose programming languages such as JavaScript, Python, Java, or C++.
Is SQL difficult to learn?
The basic SQL syntax is relatively straightforward. Beginners can start with SELECT, INSERT, UPDATE, DELETE, WHERE, ORDER BY, and JOIN, then gradually learn more advanced database concepts.
Is SQL still used today?
Yes. SQL remains widely used for applications that work with relational data. Popular relational database systems such as PostgreSQL, MySQL, Microsoft SQL Server, Oracle Database, and SQLite continue to be used in many software projects.
Can I use SQL with JavaScript?
Yes. JavaScript applications can work with SQL databases through a backend or server-side environment and database drivers, libraries, or ORMs.
Which SQL database should a beginner learn?
MySQL and PostgreSQL are both useful choices for learning relational database concepts. The fundamental SQL concepts you learn can be transferred to many other relational database systems, although syntax and features can differ between database products.
What should I learn after SQL basics?
After learning basic SQL, you can move on to database relationships, JOINs, indexes, transactions, normalization, query optimization, database design, and using SQL from a programming language.
Key Takeaways
- SQL stands for Structured Query Language.
- SQL is used to communicate with relational databases.
- Relational databases organize data into tables.
- Tables contain rows and columns.
- SELECT is used to retrieve data.
- INSERT is used to add data.
- UPDATE is used to modify data.
- DELETE is used to remove data.
- WHERE is used to filter records.
- ORDER BY is used to sort results.
- GROUP BY is used to group rows.
- JOINs are used to combine related data from multiple tables.
- Primary keys uniquely identify records.
- Foreign keys help establish relationships between tables.
- Indexes can improve query performance when used appropriately.
- Transactions help manage groups of related database operations.
SQL is one of the most useful skills to learn if you want to become a web developer, backend developer, full-stack developer, data analyst, or software engineer. Once you understand how tables, queries, relationships, and CRUD operations work, you have a strong foundation for working with relational databases.
The best way to learn SQL is by practicing queries yourself. Start with simple SELECT statements, then gradually move to filtering, sorting, aggregation, JOINs, subqueries, indexes, transactions, and database design.
Thank You!
