SQL for Everyone: The Ultimate Starter Guide

Welcome to your all-in-one SQL learning journey! 🚀

Whether you're just starting with databases or revising for a backend development project, this guide will help you master SQL from the ground up.

---

🚀 Topics Covered

• 📌 What is SQL? Why use databases?
• 🧱 Tables, Data Types, Keys & Constraints
• ✍️ INSERT, UPDATE, DELETE
• 🔍 SELECT, WHERE, ORDER BY, GROUP BY, HAVING
• 🎯 Aggregate Functions
• 🔧 ALTER, TRUNCATE, SQL_SAFE_UPDATES
• 🔗 JOINs (INNER, LEFT, RIGHT, FULL, SELF)
• 🌀 Subqueries
• 👓 Views
• ⚡ Indexes
• 🔐 Transactions & ACID principles
• 🎭 CASE Statements
• 📚 UNION vs UNION ALL
• 🧠 EXISTS vs IN
• 🔍 BETWEEN, LIKE, IS NULL
• 🏗️ Common Table Expressions (CTEs) with WITH

---

🧠 Who Is This For?

• Aspiring backend or full-stack developers
• Students working on SQL-based projects
• Engineers preparing for interviews
• Anyone who wants to write faster, cleaner, and more efficient SQL queries

---

💡 How to Use

• 💻 Copy code snippets and run them in your SQL environment (MySQL, PostgreSQL, etc.)
• 🔄 Practice with real datasets or mock tables
• 🧠 Use this guide as your personal SQL reference handbook

---

Let’s break it down into simple concepts, illustrated examples, and hands-on practice to build your confidence step by step. Ready? Let’s dive in! ⛏️

What is a Database?

A database is an organized collection of data that can be easily accessed, managed, and updated. It allows software applications to store, retrieve, and manipulate data efficiently.

---

Key Features of a Database:

• Persistent storage: Data is saved permanently (not lost after app closes).
• Structured format: Organized using tables, collections, schemas, etc.
• Efficient querying: Retrieve and filter data using query languages.
• Scalable & secure: Can handle large amounts of data with access control.

---

Common Types of Databases:

Type	Description	Examples
Relational (SQL)	Uses tables with rows & columns	MySQL, PostgreSQL, SQLite
NoSQL	Flexible schema, often document-based	MongoDB, Firebase, Redis
In-Memory	Data stored in RAM for fast access	Redis, Memcached
Graph	Stores data as nodes and edges (relationships)	Neo4j

---

Why Use a Database?

• Store user info (login/signup)
• Manage blog posts, products, orders, etc.
• Persist app data across sessions
• Enable data analysis, reports, and business logic

---

Example Use Case

An e-commerce app uses a database to:

• Save user accounts and passwords
• Store product information and inventory
• Track orders and payments
• Display customer reviews

---

Databases are the backbone of modern applications — allowing apps to be dynamic, data-driven, and scalable!

---

SQL vs NoSQL Databases

Choosing the right type of database is crucial for building scalable and efficient applications. Here's a breakdown of SQL (Relational) vs NoSQL (Non-Relational) databases:

---

Key Differences

Feature	SQL (Relational)	NoSQL (Non-Relational)
📐 Structure	Table-based (rows & columns)	Document, key-value, graph, or wide-column
📋 Schema	Fixed schema (predefined)	Dynamic schema (flexible)
💬 Query Language	SQL (Structured Query Language)	Varies: MongoDB uses JSON-like queries
📶 Relationships	Strong support for joins & relations	Weak or no join support
⚙️ Scalability	Vertical (scale-up: stronger machine)	Horizontal (scale-out: more machines)
🧩 Best For	Complex queries, ACID compliance	Large datasets, real-time apps, flexibility
📚 Examples	MySQL, PostgreSQL, SQLite, MS SQL Server	MongoDB, Firebase, CouchDB, Redis

---

When to Use SQL

• Banking, finance, or systems needing strong data integrity
• Apps requiring multi-table joins
• Structured data with clear relationships

When to Use NoSQL

• Real-time analytics or big data
• Content management systems, IoT, or chat apps
• Rapid development with changing requirements

---

Example Comparison

SQL Table (User)

id	name	email
1	Alice	[e-mail]

NoSQL Document (MongoDB)

{
  "_id": "1",
  "name": "Alice",
  "email": "[e-mail]"
}

What is SQL?

SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases.

---

Key Features

• Create and modify database tables
• Insert, update, and delete data
• Retrieve specific data using queries
• Enforce constraints, relationships, and data integrity

---

SQL is Used For:

Action	SQL Command
Create Table	CREATE TABLE
Insert Data	INSERT INTO
Retrieve Data	SELECT
Update Data	UPDATE
Delete Data	DELETE
Filter Data	WHERE, LIKE, etc.
Join Tables	JOIN

---

Example

-- Create a table
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

-- Insert data
INSERT INTO users (id, name, email) VALUES (1, 'Alice', '[e-mail]');

-- Retrieve data
SELECT * FROM users WHERE name = 'Alice';

Table in SQL

A table in SQL is a structured format to store rows of data in columns, like a spreadsheet. Each table represents a specific entity — such as users, products, or orders.

---

Table Structure

• Rows: Each row (record) holds data for one item.
• Columns: Each column represents a specific attribute (field) of the item.
• Primary Key: Uniquely identifies each row.

---

Example Table: users

id	name	email
1	Alice	[e-mail]
2	Bob	[e-mail]

---

SQL Syntax: Create Table

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

Our First Database in SQL

Let’s create our first SQL database and table! This is your first real step into working with relational databases like MySQL, PostgreSQL, or SQLite.

---

Step 1: Create a Database

CREATE DATABASE myAppDB;

Step 2: Use the Database

USE myAppDB;

👉 Switches your current session to the newly created database.

Step 3: Create a Table

Let’s create a users table to store user information:

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  age INT
);

Step 4: Insert Data into the Table

INSERT INTO users (name, email, age)
VALUES ('Alice', '[e-mail]', 25),
       ('Bob', '[e-mail]', 30);

Step 5: View Data

SELECT * FROM users;

👉 This retrieves all records from the users table.

📋 Our First Table in SQL

Now that we’ve created our first database, let’s build our first table to store data!

---

What is a Table?

A table is a collection of related data organized in rows and columns.

Each column has a name and a data type (e.g., text, number, date).

---

Step-by-Step: Create a users Table

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  age INT,
  created_at DATE
);

Database Queries in SQL

A query is a request to the database to retrieve, insert, update, or delete data. SQL provides different commands to perform these actions efficiently.

Command	Purpose
SELECT	Retrieve data
INSERT	Add new data
UPDATE	Modify existing data
DELETE	Remove data
WHERE	Filter data
ORDER BY	Sort results
LIMIT	Limit number of rows

CREATE TABLE in SQL

The CREATE TABLE statement is used to define a new table and its structure in a relational database.

---

Basic Syntax

CREATE TABLE table_name (
  column1 datatype constraints,
  column2 datatype constraints,
  ...
);

Example: Creating a users Table

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  age INT,
  created_at DATE
);

Column Breakdown

Column Name	Data Type	Constraints	Description
id	INT	PRIMARY KEY, AUTO_INCREMENT	Unique ID for each user
name	VARCHAR(100)	NOT NULL	Required name field
email	VARCHAR(100)	UNIQUE, NOT NULL	Must be unique and not empty
age	INT	(optional)	User's age
created_at	DATE	(optional)	Date of account creation

Common Constraints

Constraint	Description
PRIMARY KEY	Uniquely identifies each row
NOT NULL	Field cannot be empty
UNIQUE	Prevents duplicate values in the column
AUTO_INCREMENT	Automatically increases the number
DEFAULT	Sets a default value

SQL Data Types

When creating tables in SQL, every column must be assigned a data type that defines what kind of data it can store — such as numbers, text, dates, or booleans.

---

Numeric Data Types

Data Type	Description
INT	Integer number (whole numbers)
SMALLINT	Smaller range of integer
BIGINT	Very large integers
DECIMAL(p,s)	Fixed-point numbers (e.g., money)
FLOAT	Approximate floating-point number
DOUBLE	Double precision float

---

String/Text Data Types

Data Type	Description
CHAR(n)	Fixed-length string (e.g., CHAR(10))
VARCHAR(n)	Variable-length string (e.g., VARCHAR(100))
TEXT	Large text field

---

Date and Time Data Types

Data Type	Description
DATE	Date only (YYYY-MM-DD)
TIME	Time only (HH:MM:SS)
DATETIME	Date and time
TIMESTAMP	Auto-records current time

---

Boolean Data Type

Data Type	Description
BOOLEAN	TRUE or FALSE

Some databases (like MySQL) use TINYINT(1) for BOOLEAN.

---

Other Data Types (Advanced Use)

Data Type	Description
ENUM	Set of predefined values
BLOB	Binary Large Object (images/files)
JSON	Stores JSON-formatted data
UUID	Universally Unique Identifier

---

Example: Using Data Types

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2),
  in_stock BOOLEAN DEFAULT true,
  created_at DATETIME
);

🔐 What are SQL Constraints?

Constraints in SQL are rules applied to columns in a table to enforce data integrity, restrict invalid input, and maintain accuracy and reliability in the database.

---

Common SQL Constraints

Constraint	Description
PRIMARY KEY	Uniquely identifies each record in a table
FOREIGN KEY	Links a column to the PRIMARY KEY of another table (maintains relationships)
NOT NULL	Ensures a column cannot have a NULL (empty) value
UNIQUE	Ensures all values in a column are different
DEFAULT	Sets a default value if none is provided
CHECK	Limits values based on a condition
AUTO_INCREMENT	Automatically increases value for new records (commonly used for IDs)

---

Example: Applying Constraints

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT CHECK (age >= 18),
  country VARCHAR(50) DEFAULT 'Canada'
);

FOREIGN KEY Example

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Why Use Constraints?

• Prevent invalid data (e.g., negative age, duplicate emails)
• Ensure relationships between tables stay consistent
• Provide fallback values with DEFAULT
• Avoid NULL errors and enforce required fields

Key Constraints in SQL

Key constraints are special rules that help uniquely identify rows and maintain relationships between tables in a database.

---

1. PRIMARY KEY

• Uniquely identifies each record in a table
• Cannot have NULL or duplicate values
• Usually used on the id column

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

You can only have one PRIMARY KEY per table (can be a single column or a combination of columns).

2. FOREIGN KEY

Creates a relationship between two tables

The value must match a PRIMARY KEY in another table

Ensures referential integrity

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

If a user_id in orders doesn’t exist in the users table, the database will throw an error.

3. COMPOSITE KEY

A PRIMARY KEY that consists of two or more columns

Ensures uniqueness across the combination of values

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);

Useful when no single column is unique, but a combination is.

Primary Key vs Foreign Key in SQL

Both Primary Keys and Foreign Keys are types of constraints used to manage relationships and ensure data integrity in relational databases.

---

Primary Key

• Uniquely identifies each row in a table.
• Must be unique and NOT NULL.
• A table can have only one primary key.
• Often used on the id column.

Example:

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

id is the primary key — each user will have a unique ID.

Foreign Key

• Establishes a link between two tables.
• Points to a primary key in another table.
• Ensures referential integrity (no invalid references).

Example:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

user_id in orders references id in users.
You can’t insert an order with a user_id that doesn’t exist in users.

INSERT INTO in SQL

The INSERT INTO statement is used to add new rows of data into an existing table.

---

Basic Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example: Inserting into users Table

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', '[e-mail]');

Notes

• Always match column names with their respective values.
• You can omit columns that allow NULL or have default values.
• If you’re using AUTO_INCREMENT for id, you can skip it:

SELECT Command in SQL

The SELECT command is used to retrieve data from a database table. It's one of the most commonly used SQL commands.

---

Basic Syntax

SELECT column1, column2, ...
FROM table_name;

Example: Selecting from a users Table

SELECT * FROM users;

• Returns all columns and rows from the users table.

Select Specific Columns

SELECT name, email FROM users;

• Fetches only the name and email columns.

SQL WHERE Clause

The WHERE clause in SQL is used to filter records based on specific conditions.

It helps retrieve only the rows that meet a defined criteria.

---

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example: Filter Users by Age

SELECT * FROM users
WHERE age > 25;

• Returns users older than 25.

Operators in SQL WHERE Clause

Operators in the WHERE clause allow you to filter rows based on conditions. These can include comparisons, pattern matching, null checks, and logical combinations.

---

1. Comparison Operators

Operator	Description	Example
=	Equal to	age = 30
!=	Not equal to	name != 'Alice'
<>	Not equal (alternative)	email <> '[e-mail]'
>	Greater than	salary > 50000
<	Less than	age < 18
>=	Greater than or equal to	age >= 21
<=	Less than or equal to	score <= 100

---

2. Logical Operators

Operator	Description	Example
AND	Both conditions must be true	age > 25 AND country = 'Canada'
OR	At least one condition must be true	city = 'Delhi' OR city = 'Mumbai'
NOT	Reverses the result of a condition	NOT (age < 18)

---

3. Special Operators

Operator	Description	Example
BETWEEN	Value within a range (inclusive)	age BETWEEN 20 AND 30
IN	Match any value in a list	country IN ('India', 'Canada', 'USA')
LIKE	Pattern match using wildcards	name LIKE 'A%' (starts with 'A')
IS NULL	Checks for NULL	email IS NULL
IS NOT NULL	Checks for non-NULL	email IS NOT NULL

---

Pattern Matching with LIKE

Pattern	Meaning
'A%'	Starts with A
'%z'	Ends with z
'%test%'	Contains 'test' anywhere
'____'	Exactly 4 characters
'A_C'	Starts with A, any one char, ends with C

---

Examples

-- Users older than 30
SELECT * FROM users WHERE age > 30;

-- Users named Alice or Bob
SELECT * FROM users WHERE name IN ('Alice', 'Bob');

-- Users with no email
SELECT * FROM users WHERE email IS NULL;

-- Users whose name starts with J
SELECT * FROM users WHERE name LIKE 'J%';

Frequently Used SQL Operators

These are the most commonly used operators when writing SQL queries, especially in the WHERE clause for filtering data.

---

1. Comparison Operators

Operator	Description	Example
=	Equal to	age = 25
!=	Not equal to	city != 'Delhi'
<>	Not equal to (same as !=)	status <> 'Active'
>	Greater than	price > 100
<	Less than	age < 18
>=	Greater than or equal to	score >= 75
<=	Less than or equal to	stock <= 10

---

2. Logical Operators

Operator	Description	Example
AND	Both conditions must be true	age > 18 AND country = 'India'
OR	At least one condition must be true	state = 'NY' OR state = 'CA'
NOT	Reverses the condition	NOT (category = 'Electronics')

---

3. Special Operators

Operator	Description	Example
BETWEEN	Within a range	salary BETWEEN 50000 AND 80000
IN	Match against a list	department IN ('HR', 'IT', 'Sales')
LIKE	Pattern matching	name LIKE 'A%'
IS NULL	Checks for empty (null) values	email IS NULL
IS NOT NULL	Checks for non-null values	email IS NOT NULL

---

Pattern Matching with LIKE

Pattern	Description
'A%'	Starts with A
'%z'	Ends with z
'%tech%'	Contains 'tech' anywhere
'____'	Exactly 4 characters

---

Example Usage

SELECT * FROM products
WHERE category = 'Electronics' AND price > 1000;

SELECT * FROM users
WHERE name LIKE 'J%' AND city IN ('Toronto', 'Vancouver');

SELECT * FROM orders
WHERE delivery_date IS NOT NULL;

LIMIT Clause in SQL

The LIMIT clause is used to restrict the number of rows returned by a query.

It's especially useful for pagination or when previewing large datasets.

---

Basic Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT number;

Example: Get First 5 Rows

SELECT * FROM users
LIMIT 5;

• Returns only the first 5 rows from the users table.

LIMIT with OFFSET

Use OFFSET to skip a specific number of rows before starting to return rows.

SELECT * FROM users
LIMIT 5 OFFSET 10;

Skips the first 10 rows and returns the next 5.

Pagination Example

-- Page 1: First 10 results
SELECT * FROM users LIMIT 10 OFFSET 0;

-- Page 2: Next 10 results
SELECT * FROM users LIMIT 10 OFFSET 10;

ORDER BY Clause in SQL

The ORDER BY clause is used to sort the result-set of a query by one or more columns.

---

Basic Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC];

• ASC → Ascending order (default)
• DESC → Descending order

Example: Sorting Users by Age (Ascending)

SELECT * FROM users
ORDER BY age;

Sorts users by age in ascending order (youngest first).

Example: Sorting by Age (Descending)

SELECT * FROM users
ORDER BY age DESC;

Returns users sorted from oldest to youngest.

Aggregate Functions in SQL

Aggregate functions perform a calculation on a set of values and return a single value.

They are commonly used with the GROUP BY clause and help summarize data.

---

Common Aggregate Functions

Function	Description	Example Usage
COUNT()	Counts the number of rows	COUNT(*), COUNT(column_name)
SUM()	Returns the total sum	SUM(price)
AVG()	Returns the average value	AVG(score)
MIN()	Returns the minimum value	MIN(age)
MAX()	Returns the maximum value	MAX(salary)

---

Example Table: orders

id	customer	amount
1	Alice	100
2	Bob	150
3	Alice	200
4	Charlie	50

---

Examples

Count Total Orders

SELECT COUNT(*) FROM orders;

Returns: 4

Total Sales

SELECT SUM(amount) FROM orders;

Returns: 500

GROUP BY Clause in SQL

The GROUP BY clause is used to group rows that have the same values in specified columns, often combined with aggregate functions like COUNT(), SUM(), AVG(), etc.

---

Basic Syntax

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;

id	customer	amount
1	Alice	100
2	Bob	150
3	Alice	200
4	Charlie	50

Example: Total Spent by Each Customer

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer;

customer	total_spent
Alice	300
Bob	150
Charlie	50

HAVING Clause in SQL

The HAVING clause is used to filter groups after aggregation — similar to WHERE, but works with GROUP BY.

---

Syntax

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

HAVING vs WHERE

Clause	Filters	Used With Aggregates?
WHERE	Before grouping	❌ No
HAVING	After grouping	✅ Yes

id	customer	amount
1	Alice	100
2	Bob	150
3	Alice	200
4	Charlie	50

Example: Customers who Spent More Than 100

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
HAVING SUM(amount) > 100;

customer	total_spent
Alice	300
Bob	150

General Order of SQL Clauses

When writing SQL queries, there’s a logical order that SQL follows to execute the clauses — which may be different from the order we write them.

---

Logical Execution Order in SQL

Order	Clause	Purpose
1️⃣	FROM	Specifies the table(s) to retrieve data from
2️⃣	JOIN	Combines rows from multiple tables
3️⃣	WHERE	Filters rows based on a condition
4️⃣	GROUP BY	Groups rows for aggregation
5️⃣	HAVING	Filters aggregated data
6️⃣	SELECT	Specifies which columns to return
7️⃣	DISTINCT	Removes duplicate rows
8️⃣	ORDER BY	Sorts the result set
9️⃣	LIMIT / OFFSET	Limits number of rows returned

---

Example Query

SELECT customer, SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer
HAVING SUM(amount) > 100
ORDER BY total_spent DESC
LIMIT 5;

Real-Life Analogy

• Think of SQL as a pipeline:
• Start by picking your data source (FROM)
• Filter raw rows (WHERE)
• Group them if needed (GROUP BY)
• Filter the groups (HAVING)
• Choose what to show (SELECT)
• Sort it (ORDER BY)
• Limit it (LIMIT)

UPDATE Statement in SQL

The UPDATE statement is used to modify existing records in a table.

---

Basic Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example: Update a User's Email

UPDATE users
SET email = '[e-mail]'
WHERE id = 5;

Updates the email of the user whose ID is 5.

SET SQL_SAFE_UPDATES = 0; in MySQL

What is SQL_SAFE_UPDATES?

In MySQL, the SQL_SAFE_UPDATES mode prevents accidental updates or deletions by requiring a WHERE clause or a LIMIT clause in UPDATE and DELETE statements.

---

Safe Updates Mode Enabled (= 1)

SET SQL_SAFE_UPDATES = 1;

DELETE Statement in SQL

The DELETE statement is used to remove rows from a table.

---

Basic Syntax

DELETE FROM table_name
WHERE condition;

Example: Delete a Specific User

DELETE FROM users
WHERE id = 3;

Removes the user with id = 3 from the users table.

ALTER TABLE in SQL

The ALTER TABLE statement is used to modify the structure of an existing table — including adding, deleting, or modifying columns.

---

Basic Syntax

ALTER TABLE table_name
action;

Add a Column

ALTER TABLE users
ADD phone_number VARCHAR(15);

Drop (Remove) a Column

ALTER TABLE users
DROP COLUMN phone_number;

Rename a Column (MySQL)

ALTER TABLE users
CHANGE COLUMN old_name new_name datatype;

-- Example:
ALTER TABLE users
CHANGE COLUMN fullname full_name VARCHAR(100);

Modify Column Data Type

ALTER TABLE users
MODIFY COLUMN age SMALLINT;

Changes the age column's data type.

Rename Table

RENAME TABLE old_table_name TO new_table_name;

-- Example:
RENAME TABLE customers TO clients;

Add Constraints (e.g., NOT NULL)

ALTER TABLE users
MODIFY COLUMN email VARCHAR(255) NOT NULL;

TRUNCATE TABLE in SQL

The TRUNCATE TABLE statement is used to remove all rows from a table very quickly — while keeping the table structure intact.

---

Basic Syntax

TRUNCATE TABLE table_name;

Example

TRUNCATE TABLE users;

Deletes all rows from the users table, but does not remove the table itself.

SQL Practice Section

Boost your SQL skills by solving the following practice exercises using a sample users and orders table.

---

Sample Tables

users

id	name	age	country
1	Alice	28	Canada
2	Bob	35	USA
3	Charlie	22	Canada
4	Diana	30	UK

orders

id	user_id	product	amount
1	1	Laptop	1200
2	1	Headphones	200
3	2	Monitor	300
4	3	Mouse	50

---

Beginner Practice

1. Select all columns from the users table.

SELECT * FROM users;

1. Select names of users who are over 25.

SELECT name FROM users
WHERE age > 25;

1. Find all orders with amount > 100.

SELECT * FROM orders
WHERE amount > 100;

1. Use ORDER BY to sort users by age in descending order.

SELECT * FROM users
ORDER BY age DESC;

---

Intermediate Practice

1. Count how many users are from Canada.

SELECT COUNT(*) AS canada_users
FROM users
WHERE country = 'Canada';

1. Get the total amount spent by each user.

SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id;

1. Find the average age of all users.

SELECT AVG(age) AS avg_age
FROM users;

1. List users who haven't placed an order. (Hint: LEFT JOIN)

SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

---

Advanced Practice

1. Write a query to display the highest order amount for each user.

SELECT user_id, MAX(amount) AS max_order
FROM orders
GROUP BY user_id;

1. Update Bob’s age to 36.

UPDATE users
SET age = 36
WHERE name = 'Bob';

1. Delete users from the UK.

DELETE FROM users
WHERE country = 'UK';

1. Truncate the orders table.

TRUNCATE TABLE orders;

---

Practice makes perfect. Try solving these using a SQL playground or your local database setup.

---

SQL JOINS

In relational databases, JOINS are used to combine rows from two or more tables based on a related column between them.

---

Sample Tables

users Table

id	name
1	Alice
2	Bob
3	Carol

orders Table

id	user_id	product
1	1	Laptop
2	2	Monitor
3	4	Mouse

---

INNER JOIN

Returns only matching rows from both tables.

SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN

Returns all rows from the left table and matching rows from the right table. Fills in NULL where there's no match.

SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

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

• Returns all rows from both tables, filling in NULL where there's no match.
• Not supported in MySQL directly — use UNION.

SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id
UNION
SELECT users.name, orders.product
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

SELF JOIN

Joins a table to itself. Useful for hierarchical data (e.g., employees & managers).

Example: Employees with Managers

SELECT a.name AS employee, b.name AS manager
FROM employees a
JOIN employees b ON a.manager_id = b.id;

When to use each join

Type	Use Case Example
INNER JOIN	Get orders made by users
LEFT JOIN	Show all users, even if they didn’t order
RIGHT JOIN	Show all orders, even without known users
FULL JOIN	Get complete list of users and orders
SELF JOIN	Compare rows in the same table (e.g., hierarchy)

SQL Subqueries

A subquery (also known as an inner query or nested query) is a query inside another query.

You can use subqueries in:

• SELECT clause
• FROM clause
• WHERE clause

---

Subquery in SELECT

SELECT name,
  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS total_orders
FROM users;

Subquery in WHERE

SELECT name FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE product = 'Laptop'
);

Returns users who bought a Laptop.

Subquery in FROM

SELECT avg_orders.user_id, avg_orders.avg_amount
FROM (
  SELECT user_id, AVG(amount) AS avg_amount
  FROM orders
  GROUP BY user_id
) AS avg_orders
WHERE avg_orders.avg_amount > 100;

Gets users whose average order amount is greater than 100.

SubQuery vs Join

Feature	JOIN	Subquery
Speed	Often faster with large data	Slower for deeply nested logic
Readability	More intuitive for relations	Better for calculated/aggregated fields
Flexibility	Less flexible in nested logic	Powerful filtering, calculations

Views in SQL

A View is a virtual table based on the result of a SELECT query. It doesn't store data itself — it displays data from other tables.

---

Why Use Views?

• Simplify complex queries
• Enhance security (restrict access to certain columns)
• Present data in a customized format

---

Create a View

CREATE VIEW user_orders AS
SELECT users.name, orders.product
FROM users
JOIN orders ON users.id = orders.user_id;

Query a View

SELECT * FROM user_orders;

Indexes in SQL

An Index is like a book’s table of contents — it helps the database find rows faster without scanning the entire table.

---

Why Use Indexes?

• 🔍 Speeds up SELECT queries using WHERE, JOIN, ORDER BY, etc.
• ⚠️ Slightly slows down INSERT, UPDATE, and DELETE (due to index maintenance)

---

Create an Index

CREATE INDEX idx_user_name
ON users(name);

Use it just like a regular table.

Using Indexes Automatically

Once created, the database engine decides when to use the index. You don’t have to change your SQL queries.

SELECT * FROM users WHERE name = 'Alice';

If name is indexed, this runs faster!

Best Practices

Tip	Reason
Index columns used in WHERE	Faster lookups
Avoid over-indexing	Wastes memory + slows writes
Use composite indexes wisely	Order matters (leftmost prefix)
Don’t index frequently updated columns	Poor performance

Transactions in SQL

A Transaction is a sequence of operations performed as a single logical unit of work. Either all operations succeed, or none take effect.

---

Why Use Transactions?

• Maintain data integrity
• Useful for banking, inventory, batch updates
• Prevents partial updates if something goes wrong

---

ACID Properties

Property	Meaning
A — Atomicity	All or nothing
C — Consistency	DB remains valid before/after
I — Isolation	No interference from others
D — Durability	Changes survive failures

---

Transaction Syntax (MySQL)

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

COMMIT saves changes to the database.

Rollback a Transaction

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Simulate error
ROLLBACK;

ROLLBACK undoes everything in the transaction block.

Autocommit Mode

In MySQL, autocommit is ON by default, meaning every query is treated as its own transaction.

• Disable it manually if needed:

SET autocommit = 0;

Use Cases

• Banking systems
• E-commerce orders
• Stock updates
• Any multi-step update where failure in one step should undo all

CASE Statement in SQL

The CASE statement allows you to add conditional logic inside SQL queries — similar to if...else.

---

Basic Syntax

SELECT name,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 60 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group
FROM users;

Example with SELECT

SELECT name, country,
  CASE country
    WHEN 'Canada' THEN 'CA'
    WHEN 'United States' THEN 'US'
    ELSE 'Other'
  END AS country_code
FROM users;

Transforms country names into short codes.

UNION vs UNION ALL in SQL

UNION and UNION ALL are used to combine the results of two or more SELECT queries into a single result set.

---

UNION

• Removes duplicate rows
• Sorts results by default
• Slower than UNION ALL (because of deduplication)

SELECT name FROM customers
UNION
SELECT name FROM vendors;

Returns a combined list of unique names from both tables.

UNION ALL

• Keeps all rows (including duplicates)
• Faster (no deduplication)

SELECT name FROM customers
UNION ALL
SELECT name FROM vendors;

Returns all names, including duplicates.

EXISTS vs IN in SQL

Both EXISTS and IN are used in WHERE clauses to filter data based on subquery results, but they work differently under the hood.

---

IN — Check if a value is in a list

SELECT name FROM users
WHERE id IN (
SELECT user_id FROM orders
);

EXISTS — Checks if a row exists (faster with large datasets)

SELECT name FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
);

Returns users where at least one matching order exists.

Difference

Feature	IN	EXISTS
Works with	List of values	Subquery with correlated condition
Performance	Slower with large result sets	Faster with large, indexed tables
Subquery evaluated	First (gets full list)	Row-by-row (stops on first match)
Can return NULLs	May fail if subquery has NULLs	Works fine

Caution: NULL behavior

SELECT name FROM users
WHERE id IN (NULL); -- ❌ No results (not even NULL match)

SELECT name FROM users
WHERE EXISTS (SELECT 1); -- ✅ Always true if subquery returns a row

Common Table Expressions (CTEs) in SQL

A CTE (Common Table Expression) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

Defined using the WITH keyword.

---

Basic Syntax

WITH temp_table AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
)
SELECT users.name, temp_table.order_count
FROM users
JOIN temp_table ON users.id = temp_table.user_id;

The CTE temp_table holds aggregated order data for each user.

Why Use CTEs?

• Improves readability
• Reuses logic without repeating subqueries
• Helps break complex queries into parts

Recursive CTE Example (Advanced)

Use recursion to query hierarchical data (e.g., employees & managers):

WITH RECURSIVE employee_hierarchy AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

Builds a tree-like structure of employees reporting to managers.

CTE vs SubQuery

Feature	CTE	Subquery
Readability	✅ High	❌ Can get messy
Reuse	✅ Yes (name it once)	❌ No reuse
Recursion	✅ Supports recursive queries	❌ Not supported

🎉 Guide Complete!

You've made it to the end of this comprehensive SQL guide — great job! 🚀

Whether you're just starting out or brushing up your skills, remember:

🧠 Practice is the key to mastery.

Apply what you've learned by solving real-world problems, building projects, or exploring sample databases.

---

✅ Next Steps:

• Revisit tricky topics
• Solve all practice exercises

---

💬 Have questions or want more content like this?

Leave a comment or follow

Stay consistent, keep practicing, and happy querying! 🧑‍💻

Comments

[Sawyer Wolfe]

This guide covers so much ground that even my SELECT * FROM brain WHERE focus = 1; can't keep up! Great work—just don’t let anyone catch you TRUNCATE-ing a production table on your first day.