SQL for Beginners: The Complete Guide to Databases

What is SQL and Why Does It Matter?

SQL (Structured Query Language) is the universal language for communicating with relational databases. Created in the 1970s at IBM and standardised by ANSI, SQL remains one of the most consistently in-demand technical skills in the job market more than fifty years later. Every major tech company — Google, Amazon, Meta, Microsoft — stores and queries billions of records every day using SQL at its core.

Virtually every meaningful piece of software you use stores its data in a relational database: your bank account, your social media feed, your e-commerce order history, your streaming playlist. Understanding SQL means understanding how the data powering the modern world is actually stored, retrieved, and managed. For developers, data analysts, data scientists, and product managers alike, SQL is a non-negotiable foundational skill.

A relational database organises data into tables (like spreadsheet tabs), where each table has named columns (attributes) and rows (records). Tables can be linked to each other through foreign keys, creating relationships — hence "relational." Popular relational database systems include PostgreSQL, MySQL, SQLite, Microsoft SQL Server, and Oracle.

Creating Your First Database and Tables

Before you can store data, you need to define the structure that will hold it. The CREATE TABLE statement defines a table's name, its columns, and the data type of each column:

-- Create a students table
CREATE TABLE students (
    student_id   INTEGER      PRIMARY KEY AUTOINCREMENT,
    full_name    VARCHAR(100) NOT NULL,
    email        VARCHAR(150) NOT NULL UNIQUE,
    age          INTEGER      CHECK (age >= 16 AND age <= 100),
    course       VARCHAR(50)  DEFAULT 'General',
    enrolled_at  DATE         DEFAULT CURRENT_DATE
);

-- Create a courses table
CREATE TABLE courses (
    course_id    INTEGER      PRIMARY KEY AUTOINCREMENT,
    course_name  VARCHAR(100) NOT NULL,
    instructor   VARCHAR(100),
    duration_hrs INTEGER,
    is_active    BOOLEAN      DEFAULT TRUE
);

Key constraints: PRIMARY KEY uniquely identifies each row. NOT NULL prevents empty values. UNIQUE ensures no two rows share the same value in that column. CHECK enforces a business rule. DEFAULT provides a fallback value when none is given.

Inserting Data

-- Insert a single row
INSERT INTO students (full_name, email, age, course)
VALUES ('Alice Johnson', 'alice@example.com', 22, 'Python');

-- Insert multiple rows at once (more efficient)
INSERT INTO students (full_name, email, age, course) VALUES
    ('Bob Smith',   'bob@example.com',   25, 'Web Dev'),
    ('Charlie Roy', 'charlie@example.com', 19, 'Python'),
    ('Diana Patel', 'diana@example.com',  21, 'Data Science'),
    ('Eshan Kumar', 'eshan@example.com',  23, 'Cybersecurity');

Querying Data with SELECT

The SELECT statement is the workhorse of SQL — you will use it constantly to retrieve data:

-- Retrieve every column from every row
SELECT * FROM students;

-- Retrieve specific columns only
SELECT full_name, email, course FROM students;

-- Filter rows with WHERE
SELECT full_name, age FROM students WHERE age < 22;
SELECT full_name, course FROM students WHERE course = 'Python';

-- Multiple conditions: AND, OR, NOT
SELECT * FROM students
WHERE course = 'Python' AND age >= 20;

-- Pattern matching with LIKE
SELECT * FROM students WHERE email LIKE '%@example.com';
SELECT * FROM students WHERE full_name LIKE 'A%';  -- starts with A

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

-- Limit the number of results
SELECT full_name FROM students ORDER BY enrolled_at DESC LIMIT 5;

Aggregate Functions

Aggregate functions compute a single summary value from multiple rows — essential for analysis and reporting:

SELECT COUNT(*)                 AS total_students   FROM students;
SELECT COUNT(DISTINCT course)   AS unique_courses   FROM students;
SELECT AVG(age)                 AS average_age      FROM students;
SELECT MIN(age)                 AS youngest         FROM students;
SELECT MAX(age)                 AS oldest           FROM students;
SELECT SUM(age)                 AS sum_of_ages      FROM students;

-- GROUP BY: aggregate per group
SELECT course, COUNT(*) AS student_count, AVG(age) AS avg_age
FROM students
GROUP BY course
ORDER BY student_count DESC;

-- HAVING: filter groups (like WHERE but for groups)
SELECT course, COUNT(*) AS student_count
FROM students
GROUP BY course
HAVING COUNT(*) >= 2;

Joining Tables

JOINs combine rows from two or more tables based on a related column — this is the most powerful feature of relational databases:

-- Add a foreign key relationship
CREATE TABLE enrollments (
    enrollment_id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id    INTEGER REFERENCES students(student_id),
    course_id     INTEGER REFERENCES courses(course_id),
    grade         CHAR(1),
    completed_at  DATE
);

-- INNER JOIN: only rows that match in BOTH tables
SELECT s.full_name, c.course_name, e.grade
FROM enrollments e
INNER JOIN students s ON e.student_id = s.student_id
INNER JOIN courses  c ON e.course_id  = c.course_id
ORDER BY s.full_name;

-- LEFT JOIN: all rows from left table, matched rows from right
SELECT s.full_name, e.grade
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id;

Updating and Deleting Data

-- Update specific rows
UPDATE students
SET course = 'Advanced Python', age = 23
WHERE email = 'alice@example.com';

-- Update all rows in a column
UPDATE courses SET is_active = FALSE WHERE duration_hrs < 10;

-- Delete specific rows (ALWAYS use WHERE!)
DELETE FROM students WHERE student_id = 5;

-- WARNING: Delete ALL rows (very dangerous without WHERE)
-- DELETE FROM students;  ← Never do this without a backup!

Useful Functions and Expressions

-- String functions
SELECT UPPER(full_name), LOWER(email) FROM students;
SELECT CONCAT(full_name, ' — ', course) AS label FROM students;
SELECT LENGTH(full_name) AS name_length FROM students;

-- Date functions
SELECT full_name, enrolled_at,
       strftime('%Y', enrolled_at) AS enrollment_year  -- SQLite
FROM students;

-- CASE expression (like IF/ELSE in SQL)
SELECT full_name, age,
  CASE
    WHEN age < 20 THEN 'Teen'
    WHEN age < 25 THEN 'Young Adult'
    ELSE 'Adult'
  END AS age_group
FROM students;

Subqueries

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

-- Find courses that have at least one enrolled student
SELECT course_name FROM courses
WHERE course_id IN (SELECT DISTINCT course_id FROM enrollments);

← Back to Blog 📚 Browse Courses

SQL for Beginners: The Complete Guide to Relational Databases