SQL Notes for Beginners

01

What SQL is

You describe the result you want, not the steps to get it

SQL stands for Structured Query Language and has been the standard way to work with databases since 1974. Unlike other languages on this site, you do not write loops and if-statements — you write a question, and the database works out how to answer it.

Databasestudy_hubthe whole container

Tablestudentslike one spreadsheet

Rowone studenta single record

Columnname, marksone field of every row

Where you will meet it

1
Any website with logins or a saved list — a blog, a shop, your Study Hub dashboard.
2
Data analysis and reporting: "average marks per class, last 30 days".
3
Job interviews. SQL is asked about more than almost any other technical skill.
4
Practise with SQLite (sqlite3 study.db) — it is a single file, needs no setup, and speaks standard SQL.

02

Tables, rows & columns

Creating a table and choosing column types

SQL create.sql

CREATE TABLE students (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    name      TEXT    NOT NULL,
    email     TEXT    UNIQUE,
    class     INTEGER NOT NULL,
    marks     REAL    DEFAULT 0,
    joined_on TEXT    DEFAULT CURRENT_DATE
);

CREATE TABLE subjects (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL UNIQUE
);

-- the table that links students to subjects: a "many-to-many" bridge
CREATE TABLE scores (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL REFERENCES students(id),
    subject_id INTEGER NOT NULL REFERENCES subjects(id),
    marks      INTEGER NOT NULL CHECK (marks BETWEEN 0 AND 100)
);

Type	Holds	Notes
`INTEGER`	Whole numbers	Ids, counts, marks
`REAL`	Decimals	Percentages, prices
`TEXT`	Text of any length	Names, emails, dates as text
`BOOLEAN`	true / false	Stored as 0 or 1 in most databases
`DATE` / `TIMESTAMP`	Dates and times	MySQL, PostgreSQL, SQL Server

The constraints that keep data clean

1
PRIMARY KEY — the unique id of each row. Every table should have one.
2
NOT NULL — this column must always have a value. Stops half-empty records.
3
UNIQUE — no two rows may repeat this value. Perfect for emails and usernames.
4
DEFAULT — the value used when you do not supply one.
5
CHECK — a rule every value must pass, like marks BETWEEN 0 AND 100.
6
REFERENCES (a foreign key) points at another table's id, so the database blocks impossible data.

03

SELECT — reading data

The one command you will run a hundred times a day

SQL select.sql

-- everything, every column
SELECT * FROM students;

-- just the columns you need (better - less data moved)
SELECT name, marks FROM students;

-- give a column a nicer name in the output
SELECT name AS student_name, marks AS score FROM students;

-- calculate new columns
SELECT
    name,
    marks,
    marks / 100.0 * 100 AS percentage,
    CASE
        WHEN marks >= 75 THEN 'A'
        WHEN marks >= 33 THEN 'Pass'
        ELSE 'Fail'
    END AS result
FROM students;

-- remove duplicate values
SELECT DISTINCT class FROM students;

-- only the first 10 rows
SELECT * FROM students LIMIT 10;

-- rows 11 to 20 (skip 10, take 10)
SELECT * FROM students LIMIT 10 OFFSET 10;

1
Avoid SELECT * in real code. Name your columns so the query keeps working after someone adds a column.
2
SQL keywords are not case-sensitive — select and SELECT work. Writing keywords in capitals and column names in lower case is the common convention.
3
Every statement ends with a semicolon ;.

04

WHERE & ORDER BY

Filtering rows, then sorting what is left

Condition	Meaning	Example
`=` `!=`	Equal, not equal	`class = 10`
`> < >= <=`	Comparisons	`marks >= 75`
`BETWEEN a AND b`	Inside a range (inclusive)	`marks BETWEEN 60 AND 80`
`IN (…)`	Matches any value in the list	`class IN (9, 10, 12)`
`LIKE`	Text pattern — `%` any characters, `_` one character	`name LIKE 'A%'`
`IS NULL`	The value is empty (never use `= NULL`)	`email IS NULL`
`AND OR NOT`	Combine conditions	`class = 10 AND marks > 80`

SQL filter.sql

-- students who passed
SELECT name, marks FROM students WHERE marks >= 33;

-- class 10 students with more than 80
SELECT name FROM students
WHERE class = 10 AND marks > 80;

-- anyone failing in class 9 or 10
SELECT name, class, marks FROM students
WHERE marks < 33 AND class IN (9, 10);

-- names that start with A
SELECT name FROM students WHERE name LIKE 'A%';

-- names with exactly five letters
SELECT name FROM students WHERE name LIKE '_____';

-- any part of the text (case-insensitive on most databases)
SELECT name FROM students WHERE name LIKE '%danth%';

-- missing emails
SELECT name FROM students WHERE email IS NULL;

-- highest marks first, then alphabetically for ties
SELECT name, marks FROM students
WHERE marks >= 33
ORDER BY marks DESC, name ASC
LIMIT 10;

-- ORDER BY works on calculated columns too
SELECT name, marks, marks / 100.0 AS ratio
FROM students
ORDER BY ratio DESC;

05

GROUP BY & aggregate functions

Turning many rows into one summary row

Function	Gives you	Example
`COUNT()`	How many rows	`COUNT(*)`, `COUNT(email)` ignores NULLs
`SUM()`	Total	`SUM(marks)`
`AVG()`	Average	`AVG(marks)`
`MIN()` / `MAX()`	Smallest / largest	`MAX(marks)`
`ROUND(x, 2)`	Rounds to 2 decimals	`ROUND(AVG(marks), 2)`

SQL group.sql

-- one row for the whole table
SELECT COUNT(*) AS total_students,
       ROUND(AVG(marks), 2) AS average_marks,
       MAX(marks) AS highest
FROM students;

-- one row PER CLASS
SELECT
    class,
    COUNT(*)              AS students,
    ROUND(AVG(marks), 2)  AS avg_marks,
    MAX(marks)            AS top_marks
FROM students
GROUP BY class
ORDER BY avg_marks DESC;

-- filter the groups (not the rows) - HAVING, not WHERE
SELECT class, AVG(marks) AS avg_marks
FROM students
GROUP BY class
HAVING AVG(marks) > 70;

-- how many students per class, counting each name only once
SELECT class, COUNT(DISTINCT name) AS unique_names
FROM students
GROUP BY class;

-- WHERE filters rows BEFORE grouping, HAVING filters groups AFTER
SELECT class, AVG(marks) AS avg_marks
FROM students
WHERE marks >= 0            -- drops bad rows first
GROUP BY class
HAVING COUNT(*) >= 3       -- keeps only well-populated classes
ORDER BY avg_marks DESC;

06

JOIN — combining tables

The one skill that separates beginners from everyone else

Real data is split across tables so nothing is stored twice. JOIN stitches them back together using matching values — usually an id.

Join type	Keeps rows from	Use it when
`INNER JOIN`	Both tables (only matches)	You only want complete pairs
`LEFT JOIN`	All of the first table	Students who may have no scores yet
`RIGHT JOIN`	All of the second table	Rarely — swap the tables and use LEFT
`FULL OUTER JOIN`	Both, matched or not	Finding orphans on either side

SQL join.sql

-- INNER JOIN: only pairs that exist in both tables
SELECT s.name, sub.title, sc.marks
FROM scores sc
JOIN students s  ON s.id = sc.student_id
JOIN subjects sub ON sub.id = sc.subject_id
ORDER BY s.name, sub.title;

-- LEFT JOIN: every student, even those with no scores at all
SELECT s.name, COALESCE(sub.title, 'no scores yet') AS subject, sc.marks
FROM students s
LEFT JOIN scores sc   ON sc.student_id = s.id
LEFT JOIN subjects sub ON sub.id = sc.subject_id
ORDER BY s.name;

-- average per subject, using a join + group by together
SELECT
    sub.title,
    COUNT(*)             AS attempts,
    ROUND(AVG(sc.marks), 1) AS avg_marks
FROM scores sc
JOIN subjects sub ON sub.id = sc.subject_id
GROUP BY sub.title
ORDER BY avg_marks DESC;

-- find students with NO scores (a very common interview question)
SELECT s.name
FROM students s
LEFT JOIN scores sc ON sc.student_id = s.id
WHERE sc.id IS NULL;

-- joining a table to itself
-- (pairs of students in the same class)
SELECT a.name AS student_a, b.name AS student_b
FROM students a
JOIN students b ON a.class = b.class AND a.id < b.id;

1
Always give tables a short alias (students s) and qualify every column with it. It stops the "ambiguous column" error and makes the query readable.
2
Forgetting the JOIN condition creates a cross join — every row paired with every row. A 1,000-row table becomes a million rows and the query seems to hang.
3
COALESCE(value, 'fallback') replaces NULL with something readable. Perfect with LEFT JOIN, where missing matches produce NULLs.

07

INSERT, UPDATE, DELETE

Changing data — carefully

SQL write.sql

-- INSERT one row
INSERT INTO students (name, email, class, marks)
VALUES ('Ayush', 'ayush@study.com', 10, 87);

-- INSERT several rows at once (much faster than one at a time)
INSERT INTO students (name, class, marks) VALUES
    ('Riya',  10, 92),
    ('Karan', 10, 45),
    ('Meera', 9,  78);

-- UPDATE: ALWAYS write a WHERE, or you change every row
UPDATE students
SET marks = 91, email = 'riya@study.com'
WHERE name = 'Riya';

-- guard against a mistake: see what would be affected
SELECT * FROM students WHERE class = 9;   -- check first
UPDATE students SET marks = marks + 2 WHERE class = 9;   -- then change

-- DELETE: same rule - WHERE or everything goes
DELETE FROM students WHERE id = 4;

-- remove duplicates in one statement (keep the lowest id)
DELETE FROM students
WHERE id NOT IN (
    SELECT MIN(id) FROM students GROUP BY name
);

-- UPSERT: insert, or update if the row already exists (SQLite/PostgreSQL)
INSERT INTO students (id, name, class, marks)
VALUES (1, 'Ayush', 10, 95)
ON CONFLICT (id) DO UPDATE SET marks = excluded.marks;

-- transactions: all of it, or none of it
BEGIN;
    UPDATE students SET marks = marks - 5 WHERE class = 10;
    INSERT INTO scores (student_id, subject_id, marks) VALUES (1, 2, 88);
COMMIT;                     -- ROLLBACK; to undo everything instead

-- DROP deletes the whole table - and the data is gone
-- DROP TABLE students;

1
A BEGIN … COMMIT block is all-or-nothing. If anything fails halfway, ROLLBACK leaves the data exactly as it was.
2
A foreign key can block a delete — you cannot remove a student who still has scores pointing at them. Delete the scores first.
3
AUTOINCREMENT ids are never reused. Gaps after a delete are normal and not a problem.

08

Keys, indexes & mistakes

Making queries fast and keeping data honest

Keys and indexes in one page

Term	What it does	Why it matters
Primary key	Unique id of a row	How you refer to one exact record
Foreign key	Points at another table's id	Stops impossible data and orphan rows
Index	A lookup structure on a column	Turns a slow full scan into an instant search
Unique index	An index that forbids duplicates	Emails, usernames, roll numbers
Composite index	An index over two or more columns	Used for `WHERE class = 10 AND marks > 80`

SQL index.sql

-- speed up the searches you actually run
CREATE INDEX idx_students_class ON students (class);
CREATE INDEX idx_students_class_marks ON students (class, marks);
CREATE UNIQUE INDEX idx_students_email ON students (email);

-- see how the database plans to answer your query
EXPLAIN QUERY PLAN
SELECT * FROM students WHERE class = 10 AND marks > 80;

-- useful housekeeping
ALTER TABLE students ADD COLUMN phone TEXT;   -- add a column
ALTER TABLE students RENAME TO learners;      -- rename a table
VACUUM;                                       -- reclaim unused space

The mistakes beginners make

1
No WHERE on an UPDATE or DELETE. This is the classic disaster — and there is no undo button.
2
Writing = NULL instead of IS NULL. It silently returns nothing.
3
Using WHERE when you meant HAVING, or the other way round. WHERE filters rows before grouping; HAVING filters the groups after.
4
Forgetting the ON condition in a JOIN, which pairs every row with every row.
5
Pasting user input straight into a query. That is SQL injection — the number one website vulnerability. Use placeholders (?) or prepared statements, always.
6
Storing everything in one giant table. Split it and JOIN — that is the whole point of a relational database.
7
Adding an index to every column. It slows down writes and wastes space. Index what you actually search and sort by.

SQL reading order of a query

Written order:        SQL runs roughly in this order:

SELECT  columns        1. FROM      which tables
FROM    table          2. JOIN      combine them
JOIN    other          3. WHERE     filter the rows
WHERE   conditions     4. GROUP BY  make groups
GROUP BY column        5. HAVING    filter the groups
HAVING  group filter   6. SELECT    pick the columns
ORDER BY column        7. ORDER BY  sort the result
LIMIT   n              8. LIMIT     cut it down

09

SQL cheat sheet

Keep this open while you practise

Do this	Write this	Notes
Make a table	`CREATE TABLE t (id INTEGER PRIMARY KEY, …)`	Always have a primary key
Delete a table	`DROP TABLE t;`	Data is gone for good
Add a column	`ALTER TABLE t ADD COLUMN x TEXT;`	Existing rows get NULL
Get all rows	`SELECT * FROM t;`	Fine for exploring
Pick columns	`SELECT name, marks FROM t;`	Use in real code
Rename in output	`SELECT name AS n`	Aliases only affect the result
Filter rows	`WHERE marks >= 33`	Before grouping
Text search	`WHERE name LIKE 'A%'`	`%` = any characters
Range	`BETWEEN 60 AND 80`	Both ends included
Either of	`IN (9, 10, 12)`	Cleaner than many ORs
Empty values	`IS NULL`	Never `= NULL`
Fallback value	`COALESCE(email, 'none')`	Great with LEFT JOIN
Sort	`ORDER BY marks DESC`	ASC is the default
Second sort key	`ORDER BY marks DESC, name ASC`	Breaks ties
First N rows	`LIMIT 10 OFFSET 20`	Paging through results
Count rows	`SELECT COUNT(*) FROM t;`	Counts rows, not values
Count non-empty	`COUNT(email)`	Ignores NULLs
Totals	`SUM(marks)`, `AVG(marks)`	Also `MIN`, `MAX`
Summary per group	`GROUP BY class`	One row per class
Filter groups	`HAVING COUNT(*) > 2`	After grouping
Combine tables	`JOIN other o ON o.id = t.other_id`	Always write the ON
Keep all of one side	`LEFT JOIN`	Unmatched rows get NULL
Add a row	`INSERT INTO t (a, b) VALUES (1, 'x');`	Columns and values must match
Change rows	`UPDATE t SET a = 5 WHERE id = 1;`	WHERE or everything changes
Remove rows	`DELETE FROM t WHERE id = 1;`	Same rule
Speed up a search	`CREATE INDEX idx ON t (col);`	Index what you filter on
All or nothing	`BEGIN; … COMMIT;`	`ROLLBACK;` to undo

Practice project: a marks report

SQL report.sql

-- 1. set up a small practice database
CREATE TABLE students (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    name  TEXT NOT NULL,
    class INTEGER NOT NULL
);

CREATE TABLE subjects (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL UNIQUE
);

CREATE TABLE scores (
    student_id INTEGER REFERENCES students(id),
    subject_id INTEGER REFERENCES subjects(id),
    marks      INTEGER CHECK (marks BETWEEN 0 AND 100)
);

INSERT INTO students (name, class) VALUES
    ('Ayush', 10), ('Riya', 10), ('Karan', 10), ('Meera', 9);

INSERT INTO subjects (title) VALUES ('Maths'), ('Science'), ('English');

INSERT INTO scores (student_id, subject_id, marks) VALUES
    (1, 1, 78), (1, 2, 91), (1, 3, 66),
    (2, 1, 92), (2, 2, 88), (2, 3, 95),
    (3, 1, 45), (3, 2, 52), (3, 3, 60);

-- 2. the report card: one row per student, subjects across the columns
SELECT
    s.name,
    s.class,
    ROUND(AVG(sc.marks), 1)          AS average,
    MAX(sc.marks)                    AS best,
    CASE
        WHEN AVG(sc.marks) >= 85 THEN 'A'
        WHEN AVG(sc.marks) >= 70 THEN 'B'
        WHEN AVG(sc.marks) >= 50 THEN 'C'
        ELSE 'D'
    END                              AS grade
FROM students s
JOIN scores sc ON sc.student_id = s.id
GROUP BY s.id, s.name, s.class
ORDER BY average DESC;

-- 3. class-wise summary
SELECT
    class,
    COUNT(*)             AS students,
    ROUND(AVG(sc.marks), 1) AS class_average
FROM students s
JOIN scores sc ON sc.student_id = s.id
GROUP BY class
ORDER BY class_average DESC;

-- 4. subject-wise difficulty
SELECT
    sub.title,
    ROUND(AVG(sc.marks), 1) AS avg_marks,
    MIN(sc.marks)           AS lowest
FROM scores sc
JOIN subjects sub ON sub.id = sc.subject_id
GROUP BY sub.title
ORDER BY avg_marks ASC;

-- 5. who needs help: anyone below 50 in any subject
SELECT s.name, sub.title, sc.marks
FROM scores sc
JOIN students s   ON s.id = sc.student_id
JOIN subjects sub ON sub.id = sc.subject_id
WHERE sc.marks < 50
ORDER BY sc.marks ASC;

What SQL is

Where you will meet it

Tables, rows & columns

The constraints that keep data clean

SELECT — reading data

WHERE & ORDER BY

GROUP BY & aggregate functions

JOIN — combining tables

INSERT, UPDATE, DELETE

Keys, indexes & mistakes

Keys and indexes in one page

The mistakes beginners make

SQL cheat sheet

Practice project: a marks report

Test yourself

Q1 Which clause filters rows before grouping?

Q2 How do you find rows where `email` is empty?

Q3 Which JOIN keeps every row of the first table, even with no match?

Q4 What is the most dangerous mistake in SQL?

Continue with another language

SQL notes for beginners

Where you will meet it

The constraints that keep data clean

Keys and indexes in one page

The mistakes beginners make

Practice project: a marks report

Test yourself

Q1 Which clause filters rows before grouping?

Q2 How do you find rows where email is empty?

Q3 Which JOIN keeps every row of the first table, even with no match?

Q4 What is the most dangerous mistake in SQL?

Continue with another language

Q2 How do you find rows where `email` is empty?