Important topics: Databases and SELECT

Databases and SELECT16 min readLesson 1 of 3

1.1 Relational tables in plain terms A database is an organised store of related data that a program can read and change. In a relational database the data lives in tables. The formal model calls a table a relation, a row a tuple and a column an attribute, while everyday SQL writing says table, row and column or field. One table holds one kind of thing: students, courses, enrollments. A schema is the blueprint that names the tables and columns, gives each column a type and records rules such as uniqueness. Two counts describe a table. The degree is the number of columns and the cardinality is the number of rows. A table with six columns and nine rows has degree six and cardinality nine, and the two counts move independently. You can add rows all day without touching the degree, and you can change the degree with ALTER TABLE while every row stays put. Queries lean on that separation: they pick a subset of columns with the select list, then pick a subset of rows with a filter, and the two decisions do not interfere. SQLite is relaxed about types because it uses type affinity, so a TEXT column can still hold an integer. The declared type is worth keeping honest because it documents intent and the engine converts values when it can. PostgreSQL is stricter and rejects a value of the wrong type instead of converting it. This course runs every example on SQLite 3.53 and names the engine whenever a rule is engine-specific.

1.2 Keys keep rows identifiable A primary key is a column, or a small set of columns, that identifies each row uniquely, and it cannot be NULL. In the students table below the key is student_id. The enrollments table has no single unique column, so its key is the pair (student_id, course_code). That pair is a composite key. Each student and course combination appears once, and one student can still appear in several rows as long as the course differs. A candidate key is any minimal set of columns that is unique. A table can have several candidates, and you promote one to primary key while the rest stay as UNIQUE constraints. An email address is a natural key because the value exists outside the database. A student_id is a surrogate key because the database invented the number. Surrogate keys stay stable when a person changes their email, which is why most application schemas use them. A foreign key is a column that points at a key in another table. enrollments.student_id points at students.student_id, and enrollments.course_code points at courses.course_code. The engine can then enforce referential integrity: an enrollment cannot name a student who does not exist, and deleting a student who still has enrollments can be blocked or cascaded. SQLite enforces this only after PRAGMA foreign_keys = ON, which is off by default, so the script below turns it on.

1.3 The LearnHub practice database Every module in this course runs on one small database for a course platform called LearnHub. Six tables cover instructors, courses, students, enrollments, assignments and submissions. The assignments and submissions tables are filled too, and they become useful in the joins and subquery modules. Run the script once in a fresh SQLite database and the rest of this module works as written. This block prints no rows because the statements only define tables and load data.

sql

-- LearnHub practice database, SQLite 3.53
-- Re-running this script rebuilds the database from scratch.
PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS submissions;
DROP TABLE IF EXISTS assignments;
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS instructors;

CREATE TABLE instructors (
  instructor_id INTEGER PRIMARY KEY,
  name          TEXT    NOT NULL,
  city          TEXT,
  hired_on      TEXT    NOT NULL,
  salary        INTEGER
);

CREATE TABLE courses (
  course_code   TEXT    NOT NULL PRIMARY KEY,
  title         TEXT    NOT NULL,
  level         TEXT    NOT NULL CHECK (level IN ('beginner', 'intermediate', 'advanced')),
  fee           INTEGER NOT NULL CHECK (fee >= 0),
  capacity      INTEGER,
  instructor_id INTEGER REFERENCES instructors(instructor_id)
);

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  city       TEXT,
  email      TEXT    UNIQUE,
  joined_on  TEXT    NOT NULL,
  mentor_id  INTEGER REFERENCES students(student_id)
);

CREATE TABLE enrollments (
  student_id  INTEGER NOT NULL REFERENCES students(student_id),
  course_code TEXT    NOT NULL REFERENCES courses(course_code),
  enrolled_on TEXT    NOT NULL,
  status      TEXT    NOT NULL DEFAULT 'active'
              CHECK (status IN ('active', 'completed', 'dropped')),
  rating      INTEGER CHECK (rating BETWEEN 1 AND 5),
  PRIMARY KEY (student_id, course_code)
);

CREATE TABLE assignments (
  assignment_id INTEGER PRIMARY KEY,
  course_code   TEXT    NOT NULL REFERENCES courses(course_code),
  title         TEXT    NOT NULL,
  max_marks     INTEGER NOT NULL,
  due_on        TEXT    NOT NULL
);

CREATE TABLE submissions (
  submission_id INTEGER PRIMARY KEY,
  assignment_id INTEGER NOT NULL REFERENCES assignments(assignment_id),
  student_id    INTEGER NOT NULL REFERENCES students(student_id),
  submitted_on  TEXT,
  marks         INTEGER,
  UNIQUE (assignment_id, student_id)
);

INSERT INTO instructors (instructor_id, name, city, hired_on, salary) VALUES
  (1, 'Priya Menon',  'Bengaluru', '2023-06-12', 72000),
  (2, 'Arjun Das',    'Kolkata',   '2024-01-08', 65000),
  (3, 'Nadia Sheikh', 'Mumbai',    '2024-07-01', NULL),
  (4, 'Vikram Rao',   'Delhi',     '2025-02-17', 58000),
  (5, 'Tara Singh',   NULL,        '2025-08-25', 61000);

INSERT INTO courses (course_code, title, level, fee, capacity, instructor_id) VALUES
  ('SQL', 'SQL and Databases',       'beginner',     4999, 30, 1),
  ('PY',  'Python Basics',           'beginner',     3999, 25, NULL),
  ('DS',  'Data Structures',         'intermediate', 5999, 20, 2),
  ('ML',  'Machine Learning Basics', 'advanced',     8999, 15, 4),
  ('WEB', 'Web Development',         'beginner',     4499, 40, 4),
  ('CN',  'Computer Networks',       'intermediate', 5499, 20, 3);

INSERT INTO students (student_id, name, city, email, joined_on, mentor_id) VALUES
  (1, 'Aarav Sharma', 'Delhi',     'aarav.sharma@example.com', '2026-01-12', NULL),
  (2, 'Meera Iyer',   'Chennai',   'meera.iyer@example.com',   '2026-01-18', NULL),
  (3, 'Rohan Verma',  'Delhi',     'rohan.verma@example.com',  '2026-02-02', 1),
  (4, 'Sana Khan',    'Mumbai',    'sana.khan@example.com',    '2026-02-09', 1),
  (5, 'Kabir Nair',   'Kochi',     'kabir.nair@example.com',   '2026-02-15', 2),
  (6, 'Ishita Bose',  'Kolkata',   'ishita.bose@example.com',  '2026-03-01', 2),
  (7, 'Neel Joshi',   'Bengaluru', 'neel.joshi@example.com',   '2026-03-10', 2),
  (8, 'Zoya Ali',     NULL,        'zoya.ali@example.com',     '2026-03-18', 1),
  (9, 'Dev Patel',    'Ahmedabad', NULL,                       '2026-03-22', NULL);

INSERT INTO enrollments (student_id, course_code, enrolled_on, status, rating) VALUES
  (1, 'SQL', '2026-01-12', 'completed', 5),
  (2, 'SQL', '2026-01-18', 'active',    4),
  (2, 'PY',  '2026-01-25', 'completed', 5),
  (3, 'PY',  '2026-02-02', 'active',    NULL),
  (4, 'SQL', '2026-02-09', 'completed', 4),
  (4, 'DS',  '2026-02-10', 'active',    NULL),
  (5, 'PY',  '2026-02-15', 'dropped',   2),
  (6, 'SQL', '2026-03-01', 'active',    3),
  (6, 'ML',  '2026-03-02', 'active',    NULL),
  (7, 'DS',  '2026-03-10', 'active',    NULL),
  (7, 'WEB', '2026-03-11', 'active',    4),
  (8, 'SQL', '2026-03-18', 'active',    NULL),
  (1, 'DS',  '2026-02-20', 'active',    5),
  (4, 'PY',  '2026-03-05', 'active',    3);

INSERT INTO assignments (assignment_id, course_code, title, max_marks, due_on) VALUES
  (1, 'SQL', 'SELECT lab',          50, '2026-02-05'),
  (2, 'SQL', 'Joins lab',           50, '2026-02-26'),
  (3, 'PY',  'Variables lab',       40, '2026-02-12'),
  (4, 'DS',  'Stacks lab',          60, '2026-03-05'),
  (5, 'ML',  'Linear model report', 100, '2026-03-25'),
  (6, 'SQL', 'Final project',       100, '2026-03-30');

INSERT INTO submissions (submission_id, assignment_id, student_id, submitted_on, marks) VALUES
  (1,  1, 1, '2026-02-04', 46),
  (2,  1, 2, '2026-02-05', 38),
  (3,  1, 4, '2026-02-05', 44),
  (4,  1, 6, '2026-02-06', NULL),
  (5,  2, 1, '2026-02-24', 47),
  (6,  2, 4, '2026-02-25', 41),
  (7,  2, 6, '2026-03-01', 35),
  (8,  3, 3, '2026-02-10', 33),
  (9,  3, 5, '2026-02-12', 20),
  (10, 4, 7, '2026-03-04', 55),
  (11, 4, 4, '2026-03-07', 49),
  (12, 5, 6, '2026-03-24', 88);

1.4 SELECT chooses columns A query starts with SELECT, then the columns you want, then FROM and the table name. The select list can reorder columns, repeat one, or leave some out. That projection is the reason the same table can feed a narrow report and a wide export without storing anything twice. SELECT * asks for every column and belongs in exploration, not in application code, because a later ALTER TABLE silently changes what the application receives. The engine reads the table, produces one output row per input row that survives filtering, and labels the output columns with the source names unless you rename them. Names are case-insensitive in SQL keywords and table names, though keeping a single style makes queries easier to read. The final semicolon tells a client where one statement ends and the next begins.

sql

SELECT * FROM courses;

Output

course_code | title                   | level        | fee  | capacity | instructor_id
------------+-------------------------+--------------+------+----------+--------------
SQL         | SQL and Databases       | beginner     | 4999 | 30       | 1
PY          | Python Basics           | beginner     | 3999 | 25       | NULL
DS          | Data Structures         | intermediate | 5999 | 20       | 2
ML          | Machine Learning Basics | advanced     | 8999 | 15       | 4
WEB         | Web Development         | beginner     | 4499 | 40       | 4
CN          | Computer Networks       | intermediate | 5499 | 20       | 3

1.5 WHERE chooses rows WHERE is the row filter. It takes a condition and keeps only rows for which that condition evaluates to TRUE. Comparison operators are =, <> (also written!=), <, <=, >, >= . Text values sit in single quotes, numbers do not. Combine conditions with AND, OR and NOT, and use parentheses when the mix would otherwise need the reader to remember precedence. Two shortcuts save typing. IN tests one value against a list, and BETWEEN a AND b tests an inclusive range. Both are exact equivalents of the long form with OR and with two comparisons, so prefer the clear version when the list is short. The order of clauses matters to the parser: SELECT names columns, FROM names tables, WHERE filters rows, and only later clauses sort or cut the result.

sql

SELECT name, city, joined_on FROM students WHERE city = 'Delhi';

Output

name         | city  | joined_on
-------------+-------+-----------
Aarav Sharma | Delhi | 2026-01-12
Rohan Verma  | Delhi | 2026-02-02

sql

SELECT title, fee FROM courses
WHERE level IN ('beginner', 'advanced') AND fee > 4500;

Output

title                   | fee
------------------------+-----
SQL and Databases       | 4999
Machine Learning Basics | 8999

1.6 ORDER BY sorts the result Without ORDER BY the engine may return rows in whatever order the storage gives, and that order can change after an insert or an index. ORDER BY fixes the sequence: ASC is the default, DESC reverses it, and listing several columns sorts by the first, then breaks ties with the second. Sorting is a real operation, so it costs time on large results, and only the final result is ordered rather than the table. NULL is the awkward value here. Standard SQL leaves NULL ordering to the engine. SQLite treats NULL as smaller than every other value, so ascending order puts NULLs first and descending puts them last. PostgreSQL does the opposite by default. Write NULLS FIRST or NULLS LAST when the answer matters and the engine allows it, or filter the NULLs out before sorting.

sql

SELECT name, city, joined_on FROM students ORDER BY city ASC, joined_on DESC;

Output

name         | city      | joined_on
-------------+-----------+-----------
Zoya Ali     | NULL      | 2026-03-18
Dev Patel    | Ahmedabad | 2026-03-22
Neel Joshi   | Bengaluru | 2026-03-10
Meera Iyer   | Chennai   | 2026-01-18
Rohan Verma  | Delhi     | 2026-02-02
Aarav Sharma | Delhi     | 2026-01-12
Kabir Nair   | Kochi     | 2026-02-15
Ishita Bose  | Kolkata   | 2026-03-01
Sana Khan    | Mumbai    | 2026-02-09

sql

SELECT name, mentor_id FROM students ORDER BY mentor_id DESC LIMIT 3;

Output

name        | mentor_id
------------+----------
Kabir Nair  | 2
Ishita Bose | 2
Neel Joshi  | 2

1.7 LIMIT and OFFSET cut the list LIMIT caps how many rows come back and OFFSET skips rows before counting. Together they turn a sorted result into a page: page p of size n uses LIMIT n OFFSET (p - 1) * n . The pair applies after ORDER BY, which is why an unsorted LIMIT returns an arbitrary subset rather than the "first" rows of anything. SQLite requires LIMIT before OFFSET and rejects a bare OFFSET, so a pure skip is written LIMIT -1 OFFSET n . PostgreSQL accepts OFFSET on its own.

sql

SELECT name, joined_on FROM students ORDER BY joined_on DESC LIMIT 3;
SELECT name, joined_on FROM students ORDER BY joined_on DESC LIMIT 2 OFFSET 2;

Output

name       | joined_on
-----------+-----------
Dev Patel  | 2026-03-22
Zoya Ali   | 2026-03-18
Neel Joshi | 2026-03-10
name        | joined_on
------------+-----------
Neel Joshi  | 2026-03-10
Ishita Bose | 2026-03-01

1.8 DISTINCT removes duplicate rows DISTINCT removes duplicate result rows after the select list is evaluated. It compares the whole list, not one column. SELECT DISTINCT city, status FROM enrollments keeps one row for each city and status pair, which is not the same as the list of cities. SQLite and PostgreSQL both treat NULLs as equal for this comparison, so a single NULL survives, and sorting afterwards makes the output stable. DISTINCT acts on the final projection, so a column you do not select cannot break a duplicate. That is also its main trap. Adding one more column to the list usually turns one row into several. Later modules compare DISTINCT with GROUP BY, which removes duplicates and can also summarize the rows it collapsed.

sql

SELECT DISTINCT city FROM students ORDER BY city;
SELECT DISTINCT level FROM courses ORDER BY level;

Output

city
---------
NULL
Ahmedabad
Bengaluru
Chennai
Delhi
Kochi
Kolkata
Mumbai
level
------------
advanced
beginner
intermediate

1.9 NULL means unknown NULL marks a missing or unknown value. It is not zero, not an empty string and not FALSE. Because it is unknown, every comparison with NULL produces UNKNOWN rather than TRUE or FALSE: NULL = NULL, NULL <> 5 and NULL >= 80 all evaluate to UNKNOWN. A WHERE clause keeps only rows where the condition is TRUE, so rows with NULL silently drop out instead of raising an error. Use IS NULL and IS NOT NULL to test for it, and remember the same rule inside NOT IN . city NOT IN ('Delhi', NULL) can never be TRUE for any row: for a matching city the predicate is FALSE, and for a non-matching city it is UNKNOWN. The result is an empty list that looks like a bug. NOT EXISTS with a correlated subquery avoids the trap, which module five covers. Aggregates handle NULL in their own way: COUNT(column) skips NULLs while COUNT(*) counts rows, as module three shows.

sql

SELECT name, city FROM students WHERE city IS NULL;

Output

name     | city
---------+-----
Zoya Ali | NULL

sql

SELECT name FROM students WHERE city = NULL;
SELECT name FROM students WHERE city NOT IN ('Delhi', NULL);

Output

(0 rows)
(0 rows)

1.10 Common mistakes Writing = NULL instead of IS NULL, then wondering why the result is empty. Forgetting that WHERE runs before SELECT, so a column alias defined in the select list is not portable inside WHERE . Expecting LIMIT 3 alone to return three "top" rows when there is no ORDER BY . Adding a column to a DISTINCT select list and expecting the same row count. Treating a NULL rating or score as zero in a comparison, which silently drops those rows.

Exam

Logical order wins over written order: FROM, WHERE, SELECT, DISTINCT, ORDER BY, LIMIT. In an interview, name the engine for NULL ordering and mention that LIMIT without ORDER BY is not a promise.

Run the setup script once and confirm the six tables exist.
Write one query each for projection, row filtering, sorting, paging and duplicate removal.
Prove the NULL rule with a query that returns zero rows for city = NULL.
Explain the difference between degree and cardinality in one sentence.
Say the logical clause order out loud and give one query where it matters.

in hinglish

Database ka matlab related data ka organised store hai, aur relational database us data ko tables mein rakhta hai. Relation matlab table, tuple matlab row, attribute matlab column. Degree columns ki ginti hai, cardinality rows ki ginti. Primary key har row ko unique pehchanta hai aur NULL nahi ho sakta; composite key do ya zyada columns ka pair hota hai; foreign key do tables ke beech rishta banata hai aur referential integrity us rishte ko tootne se rokta hai. SQL mein SELECT columns chunta hai, FROM table, WHERE rows filter karta hai, ORDER BY sort karta hai, LIMIT aur OFFSET paging banate hain, aur DISTINCT duplicate result rows hata deta hai. Yaad rakho ke NULL ka matlab unknown hai, isliye = NULL kabhi TRUE nahi hota; wahan IS NULL use karo. NOT IN ki list mein NULL ho to answer khaali aa sakta hai. Bina ORDER BY ke LIMIT ka koi bharosa nahi, aur NULL ka sort order engine par depend karta hai.