80 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.
How can you retrieve the top three salaries within each department instead of applying one LIMIT to the whole result?
Answer: Rank with ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) and filter ranks up to 3
A window function ranks rows within each department partition, and an outer query filters by rank. Use RANK or DENSE_RANK when ties should share a rank.
Which expression shows a running transaction total on each row within each account ordered by time?
Answer: SUM(amount) OVER (PARTITION BY account_id ORDER BY occurred_at, id)
A window aggregate computes a running value while preserving each row. Include a unique tie-breaker in ORDER BY for deterministic ordering at equal timestamps.
Which construct names an intermediate result for readable reuse within the same complex statement?
Answer: A CTE in a WITH clause
A CTE names a query within a statement and can express staged or recursive work. Check the plan and choose materialization behavior when performance matters.
Which condition clearly returns only customers with at least one order when no order columns are needed?
Answer: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
EXISTS tests whether a matching correlated row exists. It directly expresses existence and avoids duplicating customer rows through a join.
What is a readable PostgreSQL approach to return separate counts for paid and failed statuses in one aggregation?
Answer: Use COUNT(*) FILTER (WHERE status = 'paid') and corresponding filters for each status
The aggregate FILTER clause clearly expresses conditional aggregates within one group. CASE-based conditional aggregation is an option when portability is needed.
Which predicate retrieves July 2026 timestamp rows without gaps or overlap and remains index-friendly?
Answer: created_at >= TIMESTAMP '2026-07-01' AND created_at < TIMESTAMP '2026-08-01'
A half-open range includes the start and excludes the next period's start, independent of timestamp precision. Avoiding a function on the column is friendly to a normal B-tree index.
For keyset pagination ordered by created_at DESC, id DESC, which predicate correctly fetches the next page?
Answer: WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT :n
Using the same composite key as the cursor advances deterministically through equal timestamps using id. Match comparison direction to ORDER BY and consider a corresponding index.
Which PostgreSQL comparison safely detects different values even when either side may be NULL?
Answer: old_value IS DISTINCT FROM new_value
IS DISTINCT FROM treats NULL as a comparable value and always returns true or false. Two NULLs are equal for this test, while NULL and non-NULL are distinct.
What should define intentional deletion of child rows when a parent row is deleted, making the impact explicit in the schema?
Answer: Configure ON DELETE CASCADE on the foreign key after confirming the requirement
ON DELETE CASCADE defines child deletion as a database referential-integrity rule. Assess large cascades, audit requirements, and safeguards against unintended parent deletion.
Which PostgreSQL clause returns the updated row's id and new status without a separate SELECT?
Answer: UPDATE ... RETURNING id, status
RETURNING emits columns from rows affected by INSERT, UPDATE, or DELETE in the same statement. It is useful for generated values and confirming actual targets.
How can one statement insert a new email or update only last_seen when the email already exists, while handling concurrency safely?
Answer: INSERT ... ON CONFLICT (email) DO UPDATE SET last_seen = EXCLUDED.last_seen
ON CONFLICT atomically handles conflicts against a unique index or constraint. EXCLUDED exposes proposed values so update columns and conditions can be explicit.
Most jobs are processed, but queries frequently search only WHERE processed_at IS NULL. Which index is suitable?
Answer: A partial index with the predicate WHERE processed_at IS NULL
A partial index stores only rows matching its predicate, keeping it small for pending-queue lookups. Query predicates must align with the index predicate.
Which index should be considered for case-insensitive lookups using WHERE lower(email) = lower(:email)?
Answer: An expression index on lower(email)
An expression index matching the query expression supports indexed lookup of normalized values. Use a unique expression index when uniqueness must also be case-insensitive.
Which index is commonly considered for frequent jsonb containment queries such as attributes @> :criteria?
Answer: A GIN index on attributes
GIN supports searches such as containment over jsonb keys and values. Choose the operator class while considering query patterns, index size, and write cost.
Which PostgreSQL command should be considered to add an index to a large production table without blocking normal reads and writes for the full build?
Answer: CREATE INDEX CONCURRENTLY
CREATE INDEX CONCURRENTLY allows normal writes during most of the build, but takes longer, uses I/O, cannot run inside a transaction block, and may leave an invalid index after failure.
Which PostgreSQL extension and view help identify costly SQL by total time, mean time, and call count in production?
Answer: pg_stat_statements
pg_stat_statements aggregates execution statistics by normalized statement. Account for setup, statistic resets, time-window deltas, and parameter privacy.
A logical replication slot consumer has been stopped for a long time and WAL usage on the primary keeps growing. What is the main reason?
Answer: The slot retains WAL not yet confirmed by the consumer, so lag and slot state require monitoring and recovery decisions
A replication slot prevents recycling WAL newer than the consumer's required position. An abandoned slot can exhaust disk, so monitor lag and capacity and define safe drop or recreation procedures.
Which settings help bound damage from an unexpectedly long web query and DDL waiting too long for a lock?
Answer: Set appropriate statement_timeout and lock_timeout values at the role or session level
statement_timeout bounds total statement execution, while lock_timeout bounds lock acquisition waits. Use workload-specific values and handle transaction rollback after cancellation.
Which tool combination provides a logical backup that supports selective and parallel restore?
Answer: pg_dump in custom or directory format with pg_restore
Custom and directory pg_dump formats support object selection and parallel restoration with pg_restore. Large environments also need physical backup or PITR strategy and restore testing.
Which PostgreSQL feature can prevent ordinary queries from seeing rows belonging to other tenants in a shared table?
Answer: Enable Row-Level Security and define a tenant predicate policy
RLS policies enforce row visibility and modification based on roles or session context. Carefully handle owners and BYPASSRLS, initialize pooled-session context, and test policies.
Which SQL feature can return sales subtotals by region, by product, and for the grand total in one aggregation?
Answer: Use GROUPING SETS
GROUPING SETS expresses multiple grouping levels in one GROUP BY. ROLLUP or CUBE may also fit hierarchical or all-combination totals.
Which approach retrieves each customer's latest three orders while retaining customers with no orders?
Answer: LEFT JOIN LATERAL from customers to a correlated orders subquery with ORDER BY and LIMIT 3
A LATERAL subquery can reference the current customer row. LEFT JOIN preserves the customer when the subquery returns no orders.
How should a recursive CTE guard against nontermination caused by cycles in hierarchical data?
Answer: Track the visited path or use the CYCLE clause on supported versions
Potentially cyclic graphs require visited-path detection or the CYCLE clause. A reasonable depth bound can provide additional defense.
Source data for MERGE may contain duplicate keys. What is the core measure for deterministic target updates?
Answer: Validate and deduplicate the source so multiple candidate rows cannot match one target row
MERGE executes the first true WHEN action per candidate change row, but duplicate source matches can cause errors or ambiguous requirements. Enforce source uniqueness first.
In PostgreSQL 18, how can CSV import skip conversion-error rows but fail after a defined error threshold?
Answer: Use COPY FROM with ON_ERROR ignore and REJECT_LIMIT, and monitor rejected-row counts
PostgreSQL 18 COPY FROM can combine ON_ERROR ignore with REJECT_LIMIT for conversion failures. It does not automatically handle every business-validation error.
Which database mechanism safely prevents overlapping reservation periods for the same room, including concurrent inserts?
Answer: Define an EXCLUDE constraint using a range type and GiST
An EXCLUDE constraint can reject rows where the room is equal and time ranges overlap. It is concurrency-safe compared with a check-then-insert query.
In PostgreSQL 18, which syntax can express non-overlapping validity periods per contract as a key constraint?
Answer: Specify WITHOUT OVERLAPS on the period column of a UNIQUE or PRIMARY KEY constraint
PostgreSQL 18 allows WITHOUT OVERLAPS on the final range column of UNIQUE and PRIMARY KEY constraints. Verify the server major version before adoption.
What prerequisite is required for REFRESH MATERIALIZED VIEW CONCURRENTLY to refresh an aggregate without blocking readers?
Answer: A suitable UNIQUE index exists, covers all rows, and uses only column names
CONCURRENTLY requires a UNIQUE index over plain columns that identifies all rows, and the view must already be populated.
In PostgreSQL 18, which function generates time-ordered UUIDs that can improve B-tree locality versus random UUIDs?
Answer: uuidv7()
PostgreSQL 18 uuidv7() generates temporally sortable UUIDs. It is not a sequence, and designs should consider timestamp exposure and version compatibility.
Which property should be understood before adopting PostgreSQL 18 virtual generated columns?
Answer: Values are computed when read, giving different CPU and storage tradeoffs from STORED columns
Virtual generated columns evaluate their expression at read time. Evaluate read frequency, computation cost, indexing, and replication needs, choosing STORED when appropriate.
For a PostgreSQL 18 B-tree index on (a, b), what is the correct judgment when querying only b and hoping for skip scan?
Answer: The planner may choose it when profitable, such as when a has few distinct values, so verify with EXPLAIN
Skip scan performs repeated internal searches across leading-column values. Many distinct leading values can make it unattractive, so choose indexes from statistics and plans.
In PostgreSQL 18, how can an UPDATE return both old and new values for an audit event without another SELECT?
Answer: Explicitly reference OLD and NEW values in RETURNING
PostgreSQL 18 RETURNING can explicitly expose OLD and NEW for INSERT, UPDATE, DELETE, and MERGE. Check server version and naming conflicts.
Filters on strongly correlated columns such as country and state produce poor row-count estimates. What should be considered first?
Answer: Create extended statistics on the columns with CREATE STATISTICS, then run ANALYZE
Per-column statistics may assume independence. Extended statistics such as dependencies or MCV lists inform the planner about cross-column relationships.
What is the appropriate way to introduce PostgreSQL 18 asynchronous I/O into production?
Answer: Verify build and OS support, tune io_method and related settings gradually, and compare wait time, latency, and resource use on real workloads
AIO benefits vary with OS, storage, and scan or vacuum workload. Check supported methods and restart requirements, then use load tests and staged rollout.
A pooled client becomes idle with an open transaction, holding locks and blocking dead-row cleanup. Which setting directly bounds this state?
Answer: Set idle_in_transaction_session_timeout for the relevant role or connection workload
idle_in_transaction_session_timeout terminates sessions idle inside an open transaction. Configure it with pool reconnection behavior and legitimate transaction duration in mind.
To continue logical subscriptions after failing a publisher over to its standby, what is a critical pre-failover check?
Answer: Verify failover-enabled slots are synchronized to the standby, synced, valid, and ready for failover
Logical-slot synchronization is asynchronous. Identify required slots and verify on the standby that they are synced, non-temporary, and have no invalidation reason.
Which view should be examined first for cluster-wide I/O patterns by backend type, object, and context, including queries, vacuum, and WAL activity?
Answer: pg_stat_io
pg_stat_io provides cumulative statistics by backend type, I/O object, and context. Analyze time-window deltas alongside OS, storage, and query-level metrics.
How can a foreign key be added to a large table while separating the long validation scan from the initial schema change?
Answer: Add the constraint NOT VALID, inspect and fix existing violations, then VALIDATE CONSTRAINT
A NOT VALID foreign key still checks new and changed rows. Existing-row validation can be scheduled separately with VALIDATE CONSTRAINT.
What must be considered before using EXPLAIN ANALYZE on SQL containing UPDATE or DELETE in production?
Answer: The SQL actually runs, so use a safe environment or plan a BEGIN followed by ROLLBACK to contain side effects
EXPLAIN ANALYZE executes the statement to collect actual measurements. Even with rollback, consider locks, triggers, external side effects, and sequences.
Why is merely accepting an access token insufficient when introducing PostgreSQL 18 OAuth authentication?
Answer: The trust boundary must include validator libraries, issuer, audience, expiry, privilege mapping, TLS, and revocation operations
OAuth requires validation and authorization design for which tokens map to which database roles. Staged rollout and emergency revocation procedures are also required.