SQL

Introduction to SQL

SQL stands for Structured Query Language.It is used to create, store, retrieve, manipulate, and manage data in relational databases. It allows users to communicate with a database and perform various operations on the data.

A database stores information in the form of tables, which consist of rows and columns. SQL provides commands to work with these tables efficiently.

Why learn SQL

Almost every real application — social media, banking, e-commerce, even EDUhub if it had a backend — stores its data in a database somewhere. SQL is how you actually interact with that data. It's a completely different kind of thinking from writing a program step by step; instead, you describe what data you want, and the database figures out how to get it.

What makes SQL special

Used by nearly every major database system (MySQL, PostgreSQL, SQLite, SQL Server)
Declarative — you describe what you want, not the step-by-step process to get it
Handles massive amounts of data efficiently
A small, stable set of commands that haven't changed much in decades

Where it's used

Web and mobile applications, behind the scenes
Data analysis and reporting
Business systems — inventory, finance, customer records

What we will cover

This page covers the core fundamentals of SQL, step by step.

Databases & Tables — how data is organized
CREATE TABLE — building a table from scratch
Constraints — rules that keep data valid
SELECT — retrieving data
WHERE — filtering results
INSERT — adding new data
UPDATE & DELETE — changing and removing data
ORDER BY & LIMIT — sorting and limiting results
Aggregate Functions — COUNT, SUM, AVG, MAX, MIN
GROUP BY — grouping rows for summary data
JOIN — combining data from multiple tables
Quiz — test what you've learned

Topic 1

Databases & Tables

A database is just an organized collection of data. Inside a database, data is stored in tables — think of a table like a spreadsheet, with rows and columns.

Structure of a table

A table in SQL is used to store data in the form of rows and columns.

Row: A row is a horizontal arrangement of data in a table. It represents one complete record.
Column: A column is a vertical arrangement of data in a table. It represents a specific attribute or field.

Students table:

| id | name     | age | grade |
|----|----------|-----|-------|
| 1  | Adithyan | 21  | A     |
| 2  | Priya    | 20  | B     |
| 3  | Rahul    | 22  | A     |

Key terms

Table — a collection of related data, organized in rows and columns
Column — a single field, like "name" or "age," shared across every row
Row — one complete record, like one student's full information
Primary key — a column (often id) that uniquely identifies each row

Common data types

INT — whole numbers
VARCHAR — text, with a maximum length
DATE — calendar dates
BOOLEAN — true or false

Key points

A database holds one or more tables
Tables are organized into rows (records) and columns (fields)
A primary key uniquely identifies each row in a table

Topic 2

CREATE TABLE

Before you can store any data, you need a table to put it in. CREATE TABLE is how you build one from scratch, defining each column's name and data type upfront.

CREATE TABLE is an SQL command used to create a new table in a database by defining its columns, data types, and constraints.

Basic syntax

CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(2)
);

Breaking it down

CREATE TABLE students — names the new table "students"
Each line inside the parentheses defines one column: its name, then its data type
VARCHAR(50) — specifies the maximum length of the text that can be stored in that column.
The whole statement ends with a semicolon, like most SQL commands

A quick note on naming

Table and column names are usually written in lowercase with underscores (like student_name, not StudentName) — this isn't a strict rule, just a widely followed convention that keeps things consistent and readable.

Key points

CREATE TABLE defines a new table and its columns upfront
Every column needs a name and a data type
Statements end with a semicolon

Topic 3

Constraints

Constraints are rules you applied to a column when creating a table, making sure the data stored there stays valid and consistent. They're what prevent things like duplicate IDs or a required field being left empty.

Some important constraints are :

PRIMARY KEY

Uniquely identifies each row in a table. no two rows can share the same value in this column, and it can't be left empty.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

NOT NULL

Forces a column to always have a value ,trying to insert a row without one will fail.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

UNIQUE

Similar to a primary key, but a table can have multiple UNIQUE columns, while it can only have one primary key. Useful for things like email addresses, where duplicates shouldn't be allowed.

CREATE TABLE students (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

DEFAULT

Automatically fills in a value if none is provided when a row is inserted.

CREATE TABLE students (
    id INT PRIMARY KEY,
    grade VARCHAR(2) DEFAULT 'N/A'
);

FOREIGN KEY

A foreign key connects one table to another — it stores a value that matches a primary key in a different table, linking related data together without repeating it.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

Here, dept_id in the students table must match an id that already exists in the departments table — this is what keeps related data connected and consistent.

Key points

PRIMARY KEY — uniquely identifies each row, can't be empty or duplicated
NOT NULL — the column must always have a value
UNIQUE — no duplicate values allowed, but multiple columns can have this
DEFAULT — automatically fills in a value when none is given
FOREIGN KEY — A foreign key is a field that links two tables by referring to the primary key of another table.

Topic 4

SELECT

SELECT is the command you'll use constantly in SQL — It is used to retrieve or display data from one or more tables in a database.

Selecting specific columns

SELECT name, age FROM students;

This returns only the "name" and "age" columns from the students table, for every row.

Selecting everything

The * symbol means "all columns" — a quick way to see an entire table without listing every column by name.

SELECT * FROM students;

Using the Students table from earlier

| id | name    | age | grade |
|----|---------|-----|-------|
| 1  | Aravind | 21  | A     |
| 2  | Priya   | 20  | B     |
| 3  | Rahul   | 22  | A     |

SELECT name FROM students;

-- Output:
-- Aravind 
-- Priya
-- Rahul

Renaming columns in the output — AS

AS gives a column a different name in the result, without changing anything in the actual table.

SELECT name AS student_name FROM students;

Key points

SELECT retrieves data — it doesn't change anything in the table
* selects every column
AS renames a column just in the result, not in the actual table

Topic 5

WHERE

The WHERE clause is used with SQL statements to filter records based on a specific condition. It displays or affects only the rows that satisfy the condition.

Basic filtering

| id | name    | age | grade |
|----|---------|-----|-------|
| 1  | Aravind | 21  | A     |
| 2  | Priya   | 20  | B     |
| 3  | Rahul   | 22  | A     |

SELECT name FROM students;

SELECT * FROM students WHERE age > 20;

This returns only Aravind and Rahul, since Priya is 20, not older than 20.

Comparison operators

= — equal to
!= or <> — not equal to
> / < — greater than / less than
>= / <= — greater than or equal to / less than or equal to

SELECT * FROM students WHERE grade = 'A';

Combining conditions — AND / OR

SELECT * FROM students WHERE age > 20 AND grade = 'A';

AND requires both conditions to be true. OR only requires one condition to be true.

BETWEEN

The BETWEEN operator is used to select records where a value falls within a specified range.

It is usually used with the WHERE clause.

SELECT * FROM students WHERE age BETWEEN 20 AND 21;

LIKE — matching patterns in text

Used for partial text matching. % is a wildcard, standing in for any number of characters.

SELECT * FROM students WHERE name LIKE 'A%';

This finds any name starting with "A" — in our table, that's Aravind.

Key points

WHERE filters rows based on a condition
AND/OR logical operators used to combine multiple conditions
BETWEEN used to select values within a specific range.
LIKE matches text patterns

Topic 6

INSERT

INSERT is how you add new rows of data into a table. While SELECT reads data, INSERT writes it.

Basic syntax

INSERT INTO students (id, name, age, grade)
VALUES (4, 'Alex', 19, 'A');

The column names come first, then their matching values — notice the order has to line up exactly between the two lists.

Inserting into every column

If you're providing a value for every single column, in the exact order the table was created, you can skip listing the column names.

INSERT INTO students
VALUES (4, 'Alex', 19, 'A');

This works, but it's riskier — if the table's structure ever changes (a column gets added or reordered), this query could silently insert data into the wrong columns. Listing column names explicitly is generally the safer habit.

Inserting multiple rows at once

INSERT INTO students (id, name, age, grade)
VALUES 
    (5, 'Arjun', 20, 'B'),
    (6, 'Divya', 21, 'A');

What the table looks like after these inserts

| id | name    | age | grade |
|----|---------|-----|-------|
| 1  | Akhil   | 21  | A     |
| 2  | Arjun   | 20  | B     |
| 3  | Rahul   | 22  | A     |
| 4  | Meera   | 19  | A     |
| 5  | Pranav  | 20  | B     |
| 6  | Divya   | 21  | A     |

Key points

INSERT INTO adds new rows to a table
Listing column names explicitly is safer than relying on table order
Multiple rows can be inserted in a single statement, separated by commas

Important Points:

INSERT is used to add new data to a table.
INSERT INTO specifies the table where data is added.
Column names specify where the data should be stored.
VALUES specifies the actual data to be inserted.
The values must match the order and data types of the columns.
Multiple records can also be inserted using a single INSERT statement.

Topic 7

UPDATE & DELETE

UPDATE changes existing data in a table, and DELETE removes rows entirely. Both are powerful — and both can cause real damage if used carelessly, since there's no "undo" once a query runs.

UPDATE — basic syntax

UPDATE students
SET grade = 'A'
WHERE name = 'Priya';

This changes Priya's grade to 'A', using our same students table.

Updating multiple columns at once

UPDATE students
SET age = 21, grade = 'A'
WHERE id = 2;

The danger of forgetting WHERE

This is one of the most common — and costly — mistakes in SQL. Without a WHERE clause, UPDATE applies to every single row in the table.

UPDATE students
SET grade = 'A';

This sets everyone's grade to 'A' — probably not what was intended. Always double-check a WHERE clause is in place before running an UPDATE.

DELETE — basic syntax

DELETE FROM students
WHERE name = 'Rahul';

This removes Rahul's row entirely from the table.

The same danger applies to DELETE

DELETE FROM students;

Without a WHERE clause, this deletes every row in the table — the table itself still exists, but it's now completely empty.

Key points

UPDATE changes existing data, DELETE removes rows
Both use WHERE to target specific rows
Forgetting WHERE applies the change to the entire table — always double-check before running

Topic 8

ORDER BY & LIMIT

By default, a query returns rows in whatever order the database happens to store them — not necessarily sorted in any meaningful way. ORDER BY and LIMIT give you control over that.

ORDER BY — sorting results

SELECT * FROM students ORDER BY age;

The ORDER BY clause is used to sort the records in ascending (smallest to largest) or descending (largest to smallest) order.

Sorting in descending order

SELECT * FROM students ORDER BY age DESC;

DESC flips it to largest-to-smallest. Without it, SQL defaults to ascending order (ASC), smallest to largest.

Sorting by multiple columns

SELECT * FROM students ORDER BY grade, age DESC;

This sorts primarily by grade, and for students with the same grade, sorts by age (descending) as a tiebreaker.

LIMIT

The LIMIT clause is used to restrict the number of records displayed.

SELECT * FROM students ORDER BY age DESC LIMIT 2;

This returns only the 2 oldest students, instead of the entire table ,it useful for things like "show the top 10 results."

Key points

ORDER BY sorts results — ascending by default, use DESC for descending, ASC for ascending
You can sort by multiple columns, in order of priority
LIMIT restricts how many rows are returned

Topic 9

Aggregate Functions

Aggregate functions are functions used to perform calculations on multiple rows and return a single result.

COUNT

Counts the number of rows or values.

SELECT COUNT(*) FROM students;

Using our students table (6 rows after the inserts), this returns 6.

SUM

Adds up all the values in a numeric column.

SELECT SUM(age) FROM students;

AVG

Calculates the average of a numeric column.

SELECT AVG(age) FROM students;

MAX and MIN

Find the highest or lowest value in a column.

SELECT MAX(age) FROM students;
SELECT MIN(age) FROM students;

Combining with WHERE

Aggregate functions work fine alongside a WHERE clause, letting you summarize a filtered subset of data instead of the whole table.

SELECT COUNT(*) FROM students WHERE grade = 'A';

Key points

COUNT() — counts rows
SUM() — adds up values
AVG() — calculates an average
MAX()/MIN() — finds the highest/lowest value
Aggregate functions can be combined with WHERE to summarize filtered data

Topic 10

GROUP BY

Aggregate functions are powerful on their own, but they get really useful once combined with GROUP BY — which splits your data into groups first, then runs the aggregate calculation separately for each group, instead of over the whole table at once.

The GROUP BY clause is used to group rows with the same values in one or more columns. It is commonly used with aggregate functions like COUNT(), SUM(), and AVG().

Basic example

SELECT grade, COUNT(*) 
FROM students 
GROUP BY grade;

Instead of one total count for the whole table, this returns a separate count for each grade — how many students got an A, how many got a B, and so on.

Using our students table

| id | name    | age | grade |
|----|---------|-----|-------|
| 1  | Cecil   | 21  | A     |
| 2  | Priya   | 20  | B     |
| 3  | Rahul   | 22  | A     |
| 4  | Meera   | 19  | A     |
| 5  | Arjun   | 20  | B     |
| 6  | Divya   | 21  | A     |

SELECT grade, COUNT(*) 
FROM students 
GROUP BY grade;

-- Output:
-- grade | COUNT(*)
-- A     | 4
-- B     | 2

Combining with other aggregate functions

SELECT grade, AVG(age) 
FROM students 
GROUP BY grade;

This gives the average age of students within each grade group, rather than one average for everyone.

Filtering groups with HAVING

WHERE filters rows before grouping happens. If you want to filter the groups themselves after they're calculated — like "only show grades with more than 2 students" — you need HAVING instead.

SELECT grade, COUNT(*) 
FROM students 
GROUP BY grade 
HAVING COUNT(*) > 2;

Key points

GROUP BY splits data into groups before applying an aggregate function
Each group gets its own calculated result, instead of one total
HAVING filters groups after aggregation — WHERE can't do this

Topic 11

JOIN

Real databases rarely keep everything in one giant table , data is usually split across multiple related tables to avoid repetition.Therefore, A JOIN is used to combine data from two or more tables based on a related column between them

Why split data into multiple tables

Imagine every student's enrolled courses were stored directly in the students table — you'd end up repeating each student's name and age for every course they're taking. Instead, we keep a separate table just for enrollments, and connect the two using a shared ID.

Our second table — enrollments

| student_id | course     |
|------------|------------|
| 1          | Math       |
| 1          | Physics    |
| 2          | Chemistry  |
| 3          | Math       |

Notice student_id matches up with the id column in our students table — that's the shared link between them.

INNER JOIN — basic syntax

SELECT students.name, enrollments.course
FROM students
JOIN enrollments ON students.id = enrollments.student_id;

-- Output:
-- name   | course
-- Akhil  | Math
-- Akhil  | Physics
-- Saran  | Chemistry
-- Rahul  | Math

This combines matching rows from both tables based on the condition after ON — wherever a student's id matches an enrollment's student_id.

Why it's called INNER JOIN

By default, JOIN means INNER JOIN — it only returns rows that have a match in both tables. A student with no enrollments at all simply wouldn't appear in these results.

Key points

JOIN combines rows from two tables based on a shared column
The ON clause defines exactly how the tables are connected
Splitting data across related tables avoids repeating the same information over and over
By default, JOIN only returns rows that have matches in both tables

↑ Back to top

Think you've got SQL down? Test yourself!

Start Quiz →