Learn SQL

SQL (Structured Query Language) is the universal language for working with relational databases. It powers everything from small apps to massive enterprise systems. Whether you are a developer, data analyst, or backend engineer, SQL is one of the most valuable skills you can learn.

What Is SQL?

SQL stands for Structured Query Language. It is used to create, read, update, and delete data in relational databases — collectively known as CRUD operations. Popular database systems that use SQL include:

• MySQL — widely used in web apps (WordPress, e-commerce)
• PostgreSQL — powerful open-source database with advanced features
• SQLite — lightweight, file-based, great for learning and mobile apps
• Microsoft SQL Server — enterprise-grade, used in corporate systems
• Oracle Database — large-scale enterprise systems

SQL Basics — Your First Queries

Every SQL journey starts with understanding tables. A table is like a spreadsheet — it has columns (fields) and rows (records). Here is how you create one and insert data.

Creating a Table

CREATE TABLE students (
  id        INT PRIMARY KEY AUTO_INCREMENT,
  name      VARCHAR(100) NOT NULL,
  email     VARCHAR(150) UNIQUE,
  age       INT,
  course    VARCHAR(50),
  enrolled  DATE
);

Inserting Data

INSERT INTO students (name, email, age, course, enrolled)
VALUES
  ('Alice Johnson', 'alice@example.com', 20, 'Python', '2025-01-15'),
  ('Bob Smith',     'bob@example.com',   22, 'Web Dev', '2025-02-01'),
  ('Carla Reyes',   'carla@example.com', 19, 'Python', '2025-01-20'),
  ('David Lee',     'david@example.com', 21, 'SQL',    '2025-03-10');

Selecting Data

-- Select all columns
SELECT * FROM students;

-- Select specific columns
SELECT name, course FROM students;

-- Filter with WHERE
SELECT name, age FROM students WHERE age < 21;

-- Sort results
SELECT name, age FROM students ORDER BY age DESC;

-- Limit rows
SELECT name FROM students LIMIT 2;

Filtering with WHERE

The WHERE clause lets you filter rows using conditions. You can combine conditions with AND, OR, and NOT.

-- Single condition
SELECT * FROM students WHERE course = 'Python';

-- Multiple conditions
SELECT * FROM students WHERE course = 'Python' AND age <= 20;

-- OR condition
SELECT * FROM students WHERE course = 'SQL' OR course = 'Web Dev';

-- NOT condition
SELECT * FROM students WHERE NOT course = 'Python';

-- BETWEEN (inclusive range)
SELECT * FROM students WHERE age BETWEEN 19 AND 21;

-- IN (match a list)
SELECT * FROM students WHERE course IN ('Python', 'SQL');

-- LIKE (pattern matching)
SELECT * FROM students WHERE name LIKE 'A%';   -- starts with A
SELECT * FROM students WHERE email LIKE '%@example.com';

Updating and Deleting Data

-- Update a record
UPDATE students SET age = 23 WHERE name = 'Bob Smith';

-- Update multiple columns
UPDATE students SET course = 'Data Science', age = 21 WHERE id = 3;

-- Delete a specific record
DELETE FROM students WHERE id = 4;

-- Delete all rows (keep table structure)
DELETE FROM students;

Aggregate Functions

Aggregate functions perform calculations on a set of rows and return a single value. They are essential for data analysis.

-- Count total students
SELECT COUNT(*) AS total_students FROM students;

-- Average age
SELECT AVG(age) AS average_age FROM students;

-- Youngest and oldest
SELECT MIN(age) AS youngest, MAX(age) AS oldest FROM students;

-- Count students per course
SELECT course, COUNT(*) AS total
FROM students
GROUP BY course;

-- Filter groups with HAVING
SELECT course, COUNT(*) AS total
FROM students
GROUP BY course
HAVING COUNT(*) > 1;

SQL JOINs

JOINs combine rows from two or more tables based on a related column. This is the backbone of relational database design.

Let's say we have a second table:

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
  student_id    INT,
  course_name   VARCHAR(50),
  grade         CHAR(2),
  FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO enrollments (student_id, course_name, grade)
VALUES (1, 'Python', 'A'), (2, 'Web Dev', 'B+'), (1, 'SQL', 'A-');

INNER JOIN — only matching rows

SELECT s.name, e.course_name, e.grade
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id;

LEFT JOIN — all students, even with no enrollment

SELECT s.name, e.course_name, e.grade
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id;

RIGHT JOIN — all enrollments, even unmatched

SELECT s.name, e.course_name, e.grade
FROM students s
RIGHT JOIN enrollments e ON s.id = e.student_id;

FULL OUTER JOIN — all rows from both tables

-- Not supported in MySQL — use UNION of LEFT and RIGHT JOIN
SELECT s.name, e.course_name
FROM students s LEFT JOIN enrollments e ON s.id = e.student_id
UNION
SELECT s.name, e.course_name
FROM students s RIGHT JOIN enrollments e ON s.id = e.student_id;

Subqueries

A subquery is a query nested inside another query. It runs first and passes its result to the outer query.

-- Find students older than the average age
SELECT name, age FROM students
WHERE age > (SELECT AVG(age) FROM students);

-- Find students enrolled in at least one course
SELECT name FROM students
WHERE id IN (SELECT DISTINCT student_id FROM enrollments);

-- Subquery in SELECT (scalar subquery)
SELECT name,
       (SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.id) AS total_courses
FROM students s;

Indexes

Indexes speed up data retrieval on large tables. Think of them like a book index — instead of scanning every page, you jump straight to what you need.

-- Create an index on a column
CREATE INDEX idx_course ON students(course);

-- Create a unique index
CREATE UNIQUE INDEX idx_email ON students(email);

-- Drop an index
DROP INDEX idx_course ON students;

-- View indexes (MySQL)
SHOW INDEX FROM students;

Views

A view is a saved SQL query you can treat like a virtual table. It simplifies complex queries and adds a layer of security by hiding raw table structure.

-- Create a view
CREATE VIEW python_students AS
SELECT name, age, email
FROM students
WHERE course = 'Python';

-- Query the view like a table
SELECT * FROM python_students;

-- Drop the view
DROP VIEW python_students;

Stored Procedures

Stored procedures are reusable blocks of SQL code stored in the database. They accept parameters and can contain logic like loops and conditionals.

-- Create a stored procedure (MySQL syntax)
DELIMITER $$

CREATE PROCEDURE GetStudentsByCourse(IN course_name VARCHAR(50))
BEGIN
  SELECT name, age, email
  FROM students
  WHERE course = course_name;
END $$

DELIMITER ;

-- Call the procedure
CALL GetStudentsByCourse('Python');

Transactions

Transactions group multiple SQL statements into a single unit. Either all statements succeed, or none do — this keeps your data consistent.

START TRANSACTION;

UPDATE students SET course = 'Data Science' WHERE id = 1;
INSERT INTO enrollments (student_id, course_name, grade)
VALUES (1, 'Data Science', 'A');

-- If everything is fine, save it
COMMIT;

-- If something went wrong, undo all changes
ROLLBACK;

Database Design Basics

Good database design prevents data duplication and keeps your data clean. The key principles are called normalization.

• 1NF — Each column holds a single value. No repeating groups.
• 2NF — Every non-key column depends on the entire primary key (no partial dependencies).
• 3NF — No transitive dependencies. Non-key columns depend only on the primary key.
• Foreign Keys — Link tables together and enforce referential integrity.
• Primary Key — Uniquely identifies each row in a table.

SQL Learning Roadmap

Featured SQL Tutorials