Quick summary
Summarize this blog with AI
Use this sheet for short spoken SQL interview answers. Each answer states the idea, then the qualification that prevents a common mistake. The executable examples use PostgreSQL 16. For hands-on syntax practice, use the SQL basics reference.
1. What do primary and foreign keys guarantee?
A primary key uniquely identifies a row and makes its columns non-NULL. It can contain several columns. A foreign key constrains referencing values to an eligible key in a referenced table; that need not be its primary key. In PostgreSQL, a suitable unique key can be referenced too. NULL handling and the selected delete/update action are separate parts of the relationship.
This complete temporary fixture references a unique department code while retaining a different numeric primary key. The rejected insert proves that an unknown department code cannot enter:
BEGIN;
CREATE TEMP TABLE concept_department (
id integer PRIMARY KEY,code text NOT NULL UNIQUE
) ON COMMIT DROP;
CREATE TEMP TABLE concept_employee (
id integer PRIMARY KEY,department_code text NOT NULL
REFERENCES concept_department(code)
) ON COMMIT DROP;
INSERT INTO concept_department VALUES (1,'ENG');
INSERT INTO concept_employee VALUES (10,'ENG');
DO $$ BEGIN
BEGIN
INSERT INTO pg_temp.concept_employee VALUES (11,'UNKNOWN');
RAISE EXCEPTION 'Expected foreign-key rejection';
EXCEPTION WHEN foreign_key_violation THEN
RAISE NOTICE 'Unknown department rejected';
END;
END $$;
SELECT id,department_code FROM pg_temp.concept_employee;
ROLLBACK;NOTICE: Unknown department rejected
id | department_code
10 | ENG2. Can WHERE, GROUP BY, and HAVING work together?
Yes. WHERE filters input rows; GROUP BY forms groups; HAVING filters groups. Saying WHERE cannot accompany GROUP BY is incorrect. Here the cancelled row is removed before grouping, and the small remaining group is removed afterward:
WITH orders(team,status,amount) AS (
VALUES ('A','paid',10),('A','paid',20),('A','cancelled',100),('B','paid',5)
)
SELECT team,SUM(amount) AS paid_amount
FROM orders WHERE status='paid'
GROUP BY team HAVING SUM(amount)>=20 ORDER BY team;team | paid_amount
A | 303. What does an outer join preserve?
A LEFT JOIN preserves every left-side row, using NULLs for unmatched right-side columns. RIGHT JOIN preserves the right side; FULL JOIN preserves unmatched rows from both sides. Preservation can be undone by a later WHERE condition on the right-side value. Multiple matches also multiply rows; “left join” does not mean “one output row per left row.”
4. How do JOIN and UNION differ?
JOIN combines matching rows using a relationship and can add columns. UNION stacks compatible query results with the same number of columns and compatible corresponding types. UNION removes duplicate complete output rows; UNION ALL preserves them. Neither operation establishes a meaningful display order without a final ORDER BY. UNION ALL is appropriate when repeated events are valid, not merely because it avoids deduplication work.
5. What changes when a value is NULL?
NULL represents missing or unknown information under the column’s contract. Use IS NULL to test it. COUNT(*) counts rows, while COUNT(column) counts non-NULL values. Most ordinary comparisons with NULL produce unknown, which WHERE does not keep. For example, NOT IN with a NULL in its comparison set can unexpectedly retain no rows:
WITH x(value) AS (VALUES (1),(2),(NULL::integer))
SELECT COUNT(*) AS rows,COUNT(value) AS observed,
COUNT(*) FILTER (WHERE value NOT IN (1,NULL)) AS not_in_rows
FROM x;rows | observed | not_in_rows
3 | 2 | 06. What are subqueries and CTEs?
A subquery is a query nested inside another statement. A CTE names an auxiliary result within a statement using WITH. They help express intermediate grains and dependencies. A CTE is not automatically a permanently stored table or a performance improvement; PostgreSQL can fold eligible CTEs into their parent query. A recursive CTE additionally needs an anchor, a recursive step, and a termination policy.
7. What is a self join?
It is an ordinary join in which a table appears under two aliases. Employee and manager roles are a typical example. A LEFT self join retains employees with no matching manager. The join condition and uniqueness constraints determine whether the result contains one or many matches; sharing the same underlying table does not change those rules.
8. When is an index useful?
An index can help selective lookups, joins, or ordered access. PostgreSQL B-tree indexes support common equality and range conditions; other index types target other access patterns. Indexes consume space and add write maintenance. Their existence does not force the planner to use them, and a tiny or broadly scanned table may be cheaper to scan. Explain the workload and check a plan instead of proposing an index on every column.
9. Why normalize or denormalize?
Normalization organizes facts around their dependencies to reduce update anomalies: a department name should not need inconsistent edits across employee rows. Denormalization deliberately stores redundant or precomputed information for a measured access need. It adds a consistency and refresh obligation, and does not guarantee faster queries. Preserve authoritative sources and define how derived copies are repaired.
10. What does a transaction add?
It groups changes under commit/rollback and an isolation model. Rollback can undo uncommitted work; it cannot reverse an earlier successful commit. Isolation does not automatically mean a separate preview query and later update see unchanged data. State the database’s isolation level and the concurrency guarantee the operation actually requires.
Practice concise answers with counterexamples
A strong answer includes one boundary case: a NULL key, a one-to-many join, tied ordering values, or an incomplete observation window. Avoid promising universal optimizer behavior. Primary PostgreSQL references: constraints, table expressions, CTEs, and indexes.
