Visit Buggerspot - Platform for Developers

Learn SQL from Basics to Advanced

What is SQL? Complete SQL Tutorial for Beginners

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!