40 questions / 10 random questions
Random questions, instant feedback, and review for missed questions.
View recommended SQL & PostgreSQL resources →
Which SELECT retrieves only name and email from employees?
Answer: SELECT name, email FROM employees
Listing required columns reduces transfer and coupling.
Which clause filters rows where active is true?
Answer: WHERE active = true
WHERE filters rows before aggregation.
How do you sort by newest created_at first?
Answer: ORDER BY created_at DESC
DESC sorts descending, placing newer timestamps first.
Which PostgreSQL clause limits a result to at most 100 rows?
Answer: LIMIT 100
LIMIT sets the maximum returned row count.
Which condition correctly tests for NULL?
Answer: column IS NULL
NULL is tested with IS NULL rather than ordinary equality.
Which join keeps every order even when no matching customer exists?
Answer: A LEFT JOIN from orders to customers
LEFT JOIN preserves all left rows and fills unmatched right columns with NULL.
Which join normally returns only matching rows from both tables?
Answer: INNER JOIN
INNER JOIN returns only pairs matching the join condition.
What is the basic pattern for counting employees per department?
Answer: GROUP BY department_id with COUNT(*)
GROUP BY forms department groups and COUNT counts rows in each.
Which clause keeps only groups with at least ten employees after aggregation?
Answer: HAVING COUNT(*) >= 10
HAVING filters grouped aggregate results.
How do you list category values without duplicates?
Answer: SELECT DISTINCT category
DISTINCT removes duplicate selected rows.
Which function returns zero when a value is NULL?
Answer: COALESCE(value, 0)
COALESCE returns the first non-NULL argument.
Which standard SQL expression returns labels based on conditions?
Answer: CASE WHEN ... THEN ... END
CASE produces conditional values inside a query.
Which basic operator performs string pattern matching?
Answer: LIKE
LIKE matches patterns using % and _ wildcards.
Which operator combines two SELECT results and removes duplicates?
Answer: UNION
UNION combines compatible result sets and removes duplicates.
What is the safe way to pass user input into a WHERE condition?
Answer: Use the driver's parameterized query
Parameter binding separates values from SQL syntax and prevents injection.
Which mechanism commits several updates only if all succeed?
Answer: A transaction
Use COMMIT on success and ROLLBACK on failure within a transaction.
What lets you roll back to an intermediate point within a transaction?
Answer: SAVEPOINT
A SAVEPOINT allows partial rollback without ending the whole transaction.
What is a modern way to define an auto-generated primary key?
Answer: GENERATED ... AS IDENTITY
An identity column provides standard sequence-backed generation.
Which constraint prevents duplicate email values in the database?
Answer: UNIQUE
A UNIQUE constraint rejects duplicate values.
Which constraint prevents an order from referencing a nonexistent customer_id?
Answer: FOREIGN KEY
A FOREIGN KEY enforces existence of the referenced key.
What is a basic way to make many inserts efficient without committing every row separately?
Answer: Use batching or COPY within appropriately sized transactions
Batching or COPY reduces round trips and commit overhead.
How do you inspect a query's actual execution plan and timing?
Answer: EXPLAIN ANALYZE
EXPLAIN ANALYZE executes the query and reports actual rows and timing, so consider side effects.
A selective WHERE user_id = ? predicate is frequent. What should you consider?
Answer: An index on user_id
An appropriate index can reduce rows accessed.
What is a major drawback of too many indexes?
Answer: Writes and storage cost more due to index maintenance
Each write updates related indexes, so indexes should follow query needs.
Which query commonly benefits most directly from an index on (a, b)?
Answer: A query filtering on a and optionally b
A multicolumn B-tree index is most usable from its leftmost columns; order should match queries.
Why is VACUUM needed on a table with frequent updates and deletes?
Answer: Make dead tuples reusable and maintain MVCC housekeeping
PostgreSQL MVCC leaves old row versions, so VACUUM and autovacuum reclaim them.
Which command updates planner statistics?
Answer: ANALYZE
ANALYZE collects data-distribution statistics used by the planner.
Why can a long-running transaction be operationally harmful?
Answer: It can delay cleanup of old row versions and affect locks or bloat
A long snapshot can limit VACUUM cleanup, and held locks may cause contention.
Which construct helps workers claim queue rows without waiting on rows already locked by others?
Answer: FOR UPDATE SKIP LOCKED
SKIP LOCKED skips rows held by other transactions and can reduce queue-worker contention.
What is a common response when too many connections consume database resources?
Answer: Use a connection pool with limits and timeouts
A pool reuses connections and controls database concurrency.
Which backup practice actually supports recoverability?
Answer: Run regular backups plus restore tests and retention policies
A backup is useful only when restoration is verified in another environment.
What are typical requirements for point-in-time recovery?
Answer: A base backup and continuously archived WAL
WAL is replayed onto a base backup to reach a target time.
What is a sound policy for granting table permissions to an application user?
Answer: GRANT only required schema and table operations
Least privilege limits impact if credentials are compromised.
Why separate application and migration database roles?
Answer: Normal runtime avoids DDL privilege while migrations receive scoped change rights
The runtime role can be limited to data access while schema changes use a controlled path.
What should you consider when serving reads from a replica?
Answer: Replication lag may hide recent writes
Asynchronous replication may not guarantee read-after-write consistency.
What is appropriate preparation before risky production DDL?
Answer: Test lock impact, duration, and rollback or restore procedures
DDL may lock or rewrite tables, so pretesting and monitoring are necessary.
What helps correlate a slow query with application activity?
Answer: Log query duration, request ID, and sanitized context
Safe correlation IDs and durations connect traces with database statistics.
What is an appropriate application response to a database deadlock?
Answer: Roll back the transaction and retry a limited number of times if appropriate
The database aborts one transaction to break a deadlock; also review transaction length and lock ordering.
What is a problem with pagination using a very large OFFSET?
Answer: It scans many skipped rows and concurrent changes can shift results
Consider keyset pagination with a stable sort key.
What is a sound policy for using production data containing personal information in development?
Answer: Avoid it by default; if required, use approved anonymized and minimized data
Moving data to development increases exposure and requires anonymization and access control.