Practice questions: databases and SELECT
Databases and SELECT20 min readLesson 3 of 3
These ten questions cover the vocabulary, the written queries and the traps that exams and interviews reuse. The tables are the LearnHub ones from the other two notes. Attempt each question before reading the answer, and run any query you write on a real SQLite database instead of trusting the output you remember.
3.1.1 Q1 What is a database, and what does a database management system add to it?
Answer (English): A database is an organised collection of related data stored so that programs can read and change it. A database management system is the software that manages that store. It handles concurrent readers and writers, keeps data after a crash, enforces declared constraints and exposes a query language. SQLite and PostgreSQL are DBMSs. The data itself is the set of tables, rows and index pages the DBMS controls.
Hinglish: Database related data ka organised store hai. DBMS woh software hai jo us store ko sambhalta hai: ek saath kai log padh lein, crash ke baad data bacha rahe, constraints enforce hon aur SQL chale. SQLite aur PostgreSQL DBMS ke examples hain; database data rakhta hai, DBMS usko manage karta hai.
3.1.2 Q2 Define relation, tuple, attribute, degree and cardinality.
Answer (English): A relation is a table with a fixed set of named columns. A tuple is one row of the relation. An attribute is one named column with a declared type. The degree is the number of attributes, and the cardinality is the number of tuples. A relation with zero tuples is still a valid table, which is why SELECT on an empty table returns no rows instead of an error.
Hinglish: Relation matlab table, tuple matlab ek row, attribute matlab ek column. Degree columns ki ginti aur cardinality rows ki ginti hai. Khaali table bhi valid hoti hai, isliye khaali table par SELECT error nahi deta, sirf zero rows deta hai.
3.1.3 Q3 What is the difference between a primary key and a foreign key?
Answer (English): A primary key identifies each row of its own table and cannot be NULL. A foreign key stores a reference to a key in another table and may repeat, because many rows can point at the same parent. The foreign key also gives the engine a rule to enforce: with foreign keys enabled, an enrollment cannot name a student who does not exist. The two roles can coexist in one column, as when a self-referencing table uses parent ids.
Hinglish: Primary key apni table ki har row ko unique pehchanta hai aur NULL nahi ho sakta. Foreign key doosri table ki key ka reference rakhta hai aur repeat ho sakta hai, kyunki kai rows ek hi parent ko point kar sakte hain. Foreign keys on hone par engine rishta enforce karta hai, jaise enrollment kisi missing student ka naam nahi le sakta.
3.1.4 Q4 Why does WHERE city = NULL return no rows, and what should you write instead?
Answer (English): NULL represents an unknown value, so city = NULL evaluates to UNKNOWN for every row, and WHERE keeps only rows where the condition is TRUE. The same happens with <>, < and >. Use IS NULL to find missing values and IS NOT NULL to find rows that have one. This is a property of three-valued logic, not a quirk of one engine.
Hinglish: NULL ka matlab unknown hai, isliye city = NULL har row ke liye UNKNOWN deta hai aur WHERE sirf TRUE wali rows rakhta hai. Missing value dhoondhne ke liye IS NULL use karo, aur value wali rows ke liye IS NOT NULL. Yeh kisi engine ka bug nahi, SQL ki three-valued logic hai.
SELECT name FROM students WHERE city = NULL;
SELECT name, city FROM students WHERE city IS NULL;Output
(0 rows) name | city ---------+----- Zoya Ali | NULL
3.1.5 Q5 Write a query for the names and join dates of students who joined on or after 1 February 2026 and have a known city, newest first.
Answer (English): The filter goes in WHERE, the sort in ORDER BY, and the columns in the select list. Comparing ISO dates as text works because year, month and day are in that order. The query below returns Dev Patel, Neel Joshi and Ishita Bose. Adding a second sort key such as name ASC keeps the order stable when two students share a date.
Hinglish: Filter WHERE mein, sort ORDER BY mein aur columns select list mein. ISO date ko text ki tarah compare karna chalta hai kyunki year, month, day usi order mein hain. Neeche wali query Dev Patel, Neel Joshi aur Ishita Bose deti hai. Agar do students ki date same ho to name ASC jaisa doosra sort key stable order deta hai.
SELECT name, joined_on FROM students
WHERE city IS NOT NULL AND joined_on >= '2026-02-01'
ORDER BY joined_on DESC
LIMIT 3;Output
name | joined_on ------------+----------- Dev Patel | 2026-03-22 Neel Joshi | 2026-03-10 Ishita Bose | 2026-03-01
3.1.6 Q6 What is the logical order of a SELECT query, and why can a select alias be used in ORDER BY but not in WHERE?
Answer (English): The logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. An alias is created in the SELECT stage, so WHERE has already run and cannot see it in standard SQL. ORDER BY runs after SELECT, so the alias exists by then. SQLite accepts an alias in WHERE as an extension, and PostgreSQL does not.
Hinglish: Logical order hai FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. Alias SELECT stage mein banta hai, isliye standard SQL mein WHERE usko nahi dekh sakta; ORDER BY SELECT ke baad chalta hai, isliye wahan alias mil jata hai. SQLite WHERE mein alias allow karta hai par PostgreSQL nahi, so code portable rakhna ho to base expression likho.
3.1.7 Q7 Write a query that lists each distinct status and rating pair in the enrollments table, sorted by both columns.
Answer (English): DISTINCT compares the whole select list, so the pair (city, status) is deduplicated as a unit. Sorting by both columns makes the output readable and repeatable. The result is a category list, not a count. If you wanted one row per city with a count, that is aggregation and module three covers it.
Hinglish: DISTINCT poori select list compare karta hai, isliye (city, status) pair ek unit ki tarah deduplicate hota hai. Dono columns par sort karne se output readable aur repeatable rehta hai. Yeh category list hai, count nahi. Ek city mein kitne enrollments hain, woh aggregation hai jo module three mein aati hai.
SELECT DISTINCT status, rating FROM enrollments ORDER BY status, rating;
SELECT DISTINCT status FROM enrollments ORDER BY status;Output
status | rating ----------+------- active | NULL active | 3 active | 4 active | 5 completed | 4 completed | 5 dropped | 2 status --------- active completed dropped
3.1.8 Q8 What do selection and projection mean, and which clause does each?
Answer (English): Projection chooses columns and is performed by the select list. Selection chooses rows and is performed by WHERE. The two are independent operations: a projection of three columns from a selection of two rows is a normal result, and neither clause changes what the other one can see. The terms come from relational algebra, where they are called projection and selection as well.
Hinglish: Projection columns chunta hai aur select list se hoti hai. Selection rows chunti hai aur WHERE se hoti hai. Dono alag operations hain: do rows ka three-column result normal hai, aur ek clause doosre ko beech mein nahi rokta. Yeh terms relational algebra se aaye hain.
3.1.9 Q9 How do you fetch page three of a result set when each page holds three students ordered by name?
Answer (English): Sort first, then skip two pages and take three rows: LIMIT 3 OFFSET 6. The formula is LIMIT n OFFSET (p - 1) * n. The sort must be unique or pagination can repeat and skip rows, so include a tie-breaking key such as student_id. SQLite requires the LIMIT before OFFSET; a bare OFFSET is a syntax error, and LIMIT -1 OFFSET n is the way to skip without a cap.
Hinglish: Pehle sort, phir do page skip karke teen rows lo: LIMIT 3 OFFSET 6. Formula hai LIMIT n OFFSET (p - 1) * n. Sort unique hona chahiye warna pagination rows repeat ya skip kar sakta hai, isliye student_id jaisa tie-breaker rakho. SQLite mein OFFSET se pehle LIMIT chahiye; akela OFFSET syntax error deta hai.
SELECT student_id, name FROM students ORDER BY name ASC, student_id ASC LIMIT 3 OFFSET 6;Output
student_id | name -----------+------------ 3 | Rohan Verma 4 | Sana Khan 8 | Zoya Ali
3.1.10 Q10 As an interviewer, what wrong conclusions can a report draw from these two queries: SELECT name FROM students LIMIT 3; and SELECT name FROM students ORDER BY score DESC LIMIT 3; when score can be NULL?
Answer (English): The first query promises no order, so the three names are whatever the engine returned, and a different plan or a new index can change them. The second query is ordered, but the score column contains NULLs, and NULL placement differs between SQLite and PostgreSQL, so the top three can differ between engines too. A careful report states the engine, adds NULLS FIRST or NULLS LAST when allowed, and names the tie-breaker. Without those, the ranking is not reproducible.
Hinglish: Pehli query koi order promise nahi karti, isliye teen names jo engine ne diye wahi hain; plan ya index badalne par result badal sakta hai. Doosri query sorted hai par score mein NULL hai, aur NULL ki jagah SQLite aur PostgreSQL mein alag hai, isliye top three engine ke hisaab se badal sakte hain. Report mein engine, NULL placement aur tie-breaker likho, tab ranking reproducible hoti hai.
3.2 Quiz pointer
The five-question quiz for this module is separate from these answers. Take it without scrolling back, then rerun any query you missed on the practice database. If two options look close, check which one names the engine and the rule instead of only the keyword.