codejump

Interview questionsSQL

SQL interview questions and answers

30 SQL questions picked from Codejump's quizzes, from the basics a junior is expected to know to the details that trip up senior candidates. Each one comes with its answer and an explanation — try to answer before you open it.

Junior level SQL questions

  1. 1.How does ILIKE differ from LIKE in PostgreSQL?

    • A.ILIKE is case-insensitive pattern matching; LIKE is case-sensitive
    • B.ILIKE uses regular expressions; LIKE uses simple patterns
    • C.ILIKE matches against integers; LIKE matches against strings
    • D.ILIKE is faster because it uses an index by default
    Show answer

    Answer: A. ILIKE is case-insensitive pattern matching; LIKE is case-sensitive

    LIKE performs case-sensitive pattern matching using % (any sequence) and _ (single character) wildcards. ILIKE is a PostgreSQL extension that performs the same pattern matching but ignores case differences, so 'Alice' ILIKE 'alice' is true. Standard SQL does not include ILIKE; the SQL-standard equivalent would be LOWER(column) LIKE LOWER(pattern). For indexing ILIKE, you can use a pg_trgm GIN/GIST index or an expression index on LOWER(column).

    From the quiz PostgreSQL Basics

  2. 2.Which Prisma query finds all active users whose email contains "gmail"?

    • A.prisma.user.findMany({ where: { active: true, email: { contains: 'gmail' } } })
    • B.prisma.user.findMany({ filter: { active: true, email: { includes: 'gmail' } } })
    • C.prisma.user.findMany({ where: { active: true, email: { like: '%gmail%' } } })
    • D.prisma.user.query({ active: true, emailLike: 'gmail' })
    Show answer

    Answer: A. prisma.user.findMany({ where: { active: true, email: { contains: 'gmail' } } })

    Prisma uses a where object with filter operators. String filters include: contains (case-sensitive by default), startsWith, endsWith, equals, not. For case-insensitive search, add mode: 'insensitive': { contains: 'gmail', mode: 'insensitive' }. Number/date filters include: gt, gte, lt, lte, in, notIn. Boolean fields can be filtered directly: { active: true }. Logical operators AND, OR, NOT wrap condition arrays. findMany returns all matching records; combine with take, skip, and orderBy for pagination.

    From the quiz Schema & Basic CRUD

  3. 3.What is INCR used for and why is it preferred over a read-modify-write pattern for counters?

    • A.INCR increments a string value alphabetically; use it for sorting names
    • B.INCR is only safe in single-threaded environments
    • C.INCR is shorthand for INSERT in Redis
    • D.INCR atomically increments an integer string value by 1 in a single server-side operation, making it safe for concurrent counter increments without race conditions
    Show answer

    Answer: D. INCR atomically increments an integer string value by 1 in a single server-side operation, making it safe for concurrent counter increments without race conditions

    INCR key atomically increments the integer value stored at key by 1 (creating the key with value 1 if it doesn't exist). INCRBY key n increments by n. DECR / DECRBY decrement. The atomicity is crucial: in a read-modify-write pattern (GET → add 1 → SET), two concurrent clients could both read 5, both write 6, losing one increment. INCR is processed in a single atomic step, guaranteeing correctness. This makes it ideal for page view counters, rate limiters (counting requests per minute), and inventory management. The value is stored as a string but must be parseable as a 64-bit integer.

    From the quiz Keys, TTL & Data Types

  4. 4.Which approach correctly models a many-to-many relationship between students and courses?

    javascript
    CREATE TABLE student_courses (
  student_id  INT NOT NULL REFERENCES students(id),
  course_id   INT NOT NULL REFERENCES courses(id),
  enrolled_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (student_id, course_id)
);
    • A.Add a courses array column to the students table
    • B.Add a student_id column directly to the courses table
    • C.Create a junction table student_courses with foreign keys to both students and courses
    • D.Merge both tables into a single wide table
    Show answer

    Answer: C. Create a junction table student_courses with foreign keys to both students and courses

    A many-to-many relationship requires a junction (or associative) table that holds foreign key references to both parent tables. The junction table's primary key is typically a composite of both foreign keys. This allows one student to be enrolled in many courses and one course to have many students, without any data duplication.

    From the quiz Keys, Types & Constraints

  5. 5.How do you retrieve only the first 5 rows from a products table?

    javascript
    SELECT * FROM products LIMIT 5;
    • A.SELECT * FROM products ROWNUM = 5
    • B.SELECT TOP 5 * FROM products
    • C.SELECT * FROM products FETCH 5
    • D.SELECT * FROM products LIMIT 5
    Show answer

    Answer: D. SELECT * FROM products LIMIT 5

    LIMIT is standard SQL (PostgreSQL, MySQL, SQLite): Note: TOP is SQL Server syntax: For PostgreSQL, you can also use: In interviews, always clarify which database you're targeting when discussing pagination. ---

    From the quiz SQL Basics

  6. 6.Which is the correct syntax to create an index named idx_orders_user_id on the user_id column of orders?

    • A.ADD INDEX idx_orders_user_id ON orders.user_id;
    • B.CREATE INDEX idx_orders_user_id ON orders (user_id);
    • C.CREATE INDEX ON orders.user_id AS idx_orders_user_id;
    • D.INDEX idx_orders_user_id USING orders (user_id);
    Show answer

    Answer: B. CREATE INDEX idx_orders_user_id ON orders (user_id);

    The standard PostgreSQL syntax is CREATE [UNIQUE] INDEX [name] ON table_name (column1, column2, ...). The index name is optional — omitting it causes PostgreSQL to auto-generate a name. The USING clause specifies the index type (defaulting to btree): CREATE INDEX idx_orders_user_id ON orders USING btree (user_id). Options A, C, and D are syntactically invalid.

    From the quiz Index Basics

  7. 7.Which statement about column aliases is correct?

    javascript
    SELECT
  first_name || ' ' || last_name AS full_name,
  salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 60000;
    • A.The query works correctly and filters employees with annual salary above 60,000
    • B.The WHERE clause cannot reference a column alias defined in the same SELECT
    • C.AS is required for aliases; omitting it causes an error
    • D.|| is not valid for string concatenation in SQL
    Show answer

    Answer: B. The WHERE clause cannot reference a column alias defined in the same SELECT

    In SQL's logical processing order, the WHERE clause is evaluated before the SELECT clause, so column aliases defined in SELECT are not yet available in WHERE. This query would raise an error like column "annual_salary" does not exist. To filter on a computed expression, repeat the expression in WHERE or wrap the query in a subquery/CTE. Note that AS is optional for aliases — salary * 12 annual_salary is also valid.

    From the quiz CASE, COALESCE & Functions

  8. 8.Which statement best describes the "Consistency" property in ACID?

    • A.All read operations see the most recently committed data
    • B.A transaction brings the database from one valid state to another valid state, respecting all defined integrity constraints, rules, and triggers
    • C.A committed transaction's changes are permanent even after a power failure
    • D.Multiple concurrent transactions do not interfere with each other
    Show answer

    Answer: B. A transaction brings the database from one valid state to another valid state, respecting all defined integrity constraints, rules, and triggers

    Consistency (in ACID) guarantees that a transaction can only move the database between valid states — states that satisfy all defined integrity constraints (primary keys, foreign keys, CHECK constraints, triggers). If a transaction would violate any constraint, it is rejected entirely. Note: ACID Consistency is different from CAP theorem Consistency, which refers to distributed replication; the terms overlap but refer to fundamentally different properties.

    From the quiz ACID & Basic Transactions

  9. 9.In PostgreSQL, what is the relationship between schemas and databases?

    • A.Schemas and databases are the same concept
    • B.A schema can span multiple databases for cross-database queries
    • C.A database can contain multiple schemas; schemas provide namespace isolation within a database but cannot span databases
    • D.Each schema is a separate physical file on disk
    Show answer

    Answer: C. A database can contain multiple schemas; schemas provide namespace isolation within a database but cannot span databases

    In PostgreSQL, a database is an isolated environment with its own set of users/roles, schemas, and objects. You cannot reference objects across databases in a standard query. A schema is a namespace within a database — a database can have many schemas (e.g., public, billing, auth). Objects within different schemas in the same database can be joined freely. Schemas are commonly used to organize objects, implement multi-tenancy, or separate concerns within a single database.

    From the quiz PostgreSQL Basics

  10. 10.How do you create a Post with a nested Author creation in a single Prisma call?

    • A.prisma.post.create({ data: { title: 'Hello', author: { new: { name: 'Alice' } } } })
    • B.prisma.post.create({ data: { title: 'Hello', authorId: await prisma.user.create({ data: { name: 'Alice' } }) } })
    • C.prisma.post.create({ data: { title: 'Hello' }, include: { author: { name: 'Alice' } } })
    • D.prisma.post.create({ data: { title: 'Hello', author: { create: { name: 'Alice' } } } })
    Show answer

    Answer: D. prisma.post.create({ data: { title: 'Hello', author: { create: { name: 'Alice' } } } })

    Prismas nested writes use `create`, `connect`, `connectOrCreate`, `createMany`, `update`, and `upsert` as sub-operations within a relation field. `author: { create: { name: Alice' } } creates a new User record and links it to the post in a single atomic database operation (wrapped in a transaction). This avoids two separate queries and a race condition. The equivalent connect operation links to an existing record: author: { connect: { id: userId } }. connectOrCreate` combines both: find by a unique field or create if not found.

    From the quiz Schema & Basic CRUD

Mid level SQL questions

  1. 11.What is the advantage of using CREATE TYPE status AS ENUM ('active', 'inactive', 'pending') over a plain VARCHAR column?

    • A.ENUM values can be changed without a migration
    • B.ENUM types support full-text search automatically
    • C.ENUM columns are stored as integers internally, making them faster to sort and compare, while also enforcing that only defined values can be inserted
    • D.ENUM columns are automatically indexed
    Show answer

    Answer: C. ENUM columns are stored as integers internally, making them faster to sort and compare, while also enforcing that only defined values can be inserted

    PostgreSQL stores ENUM values as integers internally (4 bytes per value) rather than the full string, making storage more efficient and comparisons faster since they reduce to integer comparisons. The database enforces that only declared ENUM values can be inserted, providing a database-level constraint without a CHECK constraint. ENUMs also make the schema more self-documenting. Adding a new value is done with ALTER TYPE status ADD VALUE 'archived' (safe, non-blocking). However, removing or reordering values requires recreating the type.

    From the quiz JSONB, Arrays & FTS

  2. 12.What does connectOrCreate do in a Prisma nested write?

    • A.It attempts to find an existing record by a unique field; if found, it connects to it; if not found, it creates a new record and connects it — all in one atomic operation
    • B.It creates a new record and connects it, then throws if a record with the same unique field already exists
    • C.It connects to an existing record if found, or silently skips the operation if not found
    • D.It is equivalent to upsert but only works inside nested writes
    Show answer

    Answer: A. It attempts to find an existing record by a unique field; if found, it connects to it; if not found, it creates a new record and connects it — all in one atomic operation

    connectOrCreate combines connect and create into a single atomic operation. You provide a where clause (unique identifier to look up) and a create object (data to use if no record is found). Example: author: { connectOrCreate: { where: { email: 'alice@example.com' }, create: { name: 'Alice', email: 'alice@example.com' } } }. This is especially useful in data seeding, upsert-like operations, or importing data where the related record may or may not exist. The operation is wrapped in a transaction to prevent race conditions.

    From the quiz Relations & Migrations

  3. 13.How do you retrieve the top 10 users by score (descending) from a Sorted Set?

    • A.ZRANGE leaderboard 0 9 (returns lowest to highest scores)
    • B.ZRANGEBYSCORE leaderboard +inf -inf LIMIT 0 10
    • C.ZSORTBY leaderboard DESC LIMIT 10
    • D.ZRANGE leaderboard 0 9 REV or ZREVRANGE leaderboard 0 9 (returns highest to lowest)
    Show answer

    Answer: D. ZRANGE leaderboard 0 9 REV or ZREVRANGE leaderboard 0 9 (returns highest to lowest)

    Sorted Sets store members with associated float scores, maintaining members in sorted order. ZRANGE key start stop returns members with lowest scores first (ascending). ZRANGE key 0 9 REV (Redis 6.2+) or the legacy ZREVRANGE key 0 9 returns the top members in descending score order. ZADD leaderboard 1000 alice adds/updates a member. ZSCORE leaderboard alice returns alices score. `ZRANK leaderboard alice` returns alices rank (0-indexed, ascending). ZREVRANK returns rank in descending order. ZINCRBY leaderboard 50 alice atomically adds 50 to alice's score.

    From the quiz Hashes, Lists & Sorted Sets

  4. 14.A table is in 3NF but NOT in BCNF. What condition distinguishes BCNF from 3NF?

    • A.BCNF requires that for every non-trivial functional dependency X → Y, X must be a superkey
    • B.BCNF additionally requires all columns to be indexed
    • C.BCNF eliminates all NULL values from the schema
    • D.BCNF requires all foreign keys to have ON DELETE CASCADE
    Show answer

    Answer: A. BCNF requires that for every non-trivial functional dependency X → Y, X must be a superkey

    BCNF (also called 3.5NF) is a stricter version of 3NF. A relation is in BCNF if and only if for every non-trivial functional dependency X → Y, X is a superkey (a set of attributes that uniquely identifies a row). A table can be in 3NF but not BCNF when it has overlapping candidate keys. BCNF violations are rare in practice but can cause update anomalies similar to lower normal forms when multiple candidate keys overlap.

    From the quiz Normalization & Naming

  5. 15.Which column has the best index selectivity?

    • A.An email column with unique values for every row
    • B.A country_code column with 250 distinct values out of 1 million rows
    • C.A gender column with values 'M' and 'F'
    • D.A status column with values 'active', 'inactive', 'deleted'
    Show answer

    Answer: A. An email column with unique values for every row

    Index selectivity measures how well an index can narrow down rows. High selectivity means few rows match a given value — ideally one row per unique value. An email column with all unique values has the highest cardinality and thus the highest selectivity. A B-tree index on it can pinpoint exactly one row per lookup. Low-cardinality columns like gender (2 values) or status (3 values) have very low selectivity — filtering on them still returns a large fraction of the table, making an index often inefficient.

    From the quiz Composite & Partial Indexes

  6. 16.What is the difference between WHERE and HAVING?

    javascript
    SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE total > 50
GROUP BY user_id
HAVING COUNT(*) > 2;
    • A.WHERE and HAVING are interchangeable — both filter rows
    • B.HAVING filters rows before grouping; WHERE filters groups after aggregation
    • C.WHERE filters rows before grouping; HAVING filters groups after aggregation
    • D.WHERE works on columns; HAVING works on tables
    Show answer

    Answer: C. WHERE filters rows before grouping; HAVING filters groups after aggregation

    Execution order in SQL: You cannot use aggregate functions in WHERE: ---

    From the quiz Joins & Aggregations

  7. 17.What is the correct syntax for chaining two CTEs?

    javascript
    WITH
  active_users AS (
    SELECT id, name FROM users WHERE active = true
  ),
  user_orders AS (
    SELECT u.name, COUNT(o.id) AS order_count
    FROM active_users u
    JOIN orders o ON o.user_id = u.id
    GROUP BY u.name
  )
SELECT * FROM user_orders WHERE order_count > 5;
    • A.WITH cte1 AS (...); WITH cte2 AS (...) SELECT ...
    • B.WITH cte1 AS (...), cte2 AS (...) SELECT ...
    • C.WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...
    • D.CTE cte1 AS (...), cte2 AS (...) SELECT ...
    Show answer

    Answer: B. WITH cte1 AS (...), cte2 AS (...) SELECT ...

    Multiple CTEs in a single query are defined with a single WITH keyword followed by comma-separated CTE definitions. Each subsequent CTE can reference previously defined CTEs in the same WITH clause. This allows you to build up complex logic step by step, making queries easier to read and debug. Option A would be parsed as two separate statements. Options C and D are syntactically invalid.

    From the quiz Subqueries & Set Operations

  8. 18.What does SERIALIZABLE guarantee that REPEATABLE READ does not?

    • A.It additionally prevents dirty reads that REPEATABLE READ allows
    • B.It ensures the outcome of concurrent transactions is equivalent to some serial execution, preventing all anomalies including write skew
    • C.It additionally prevents phantom reads in MySQL-style databases
    • D.It automatically retries aborted transactions without any application-level retry logic
    Show answer

    Answer: B. It ensures the outcome of concurrent transactions is equivalent to some serial execution, preventing all anomalies including write skew

    SERIALIZABLE is the strongest isolation level. It guarantees that even if transactions run concurrently, the final result is equivalent to some serial (one-at-a-time) execution order. This prevents all anomalies including write skew (where two transactions each read overlapping data and make individually valid writes that together violate a business invariant). PostgreSQL implements this using Serializable Snapshot Isolation (SSI), which detects dangerous read-write conflicts and aborts one transaction rather than using heavy predicate locks.

    From the quiz Isolation Levels

  9. 19.Why is a GIN index preferred over a B-tree index for JSONB columns?

    • A.GIN (Generalized Inverted Index) indexes individual keys and values within the JSONB document, enabling fast containment (@>) and key-existence (?) queries that B-tree indexes cannot support
    • B.B-tree indexes cannot be created on JSONB columns at all
    • C.GIN indexes are smaller than B-tree indexes for JSONB data
    • D.GIN indexes automatically normalize JSONB keys to lowercase
    Show answer

    Answer: A. GIN (Generalized Inverted Index) indexes individual keys and values within the JSONB document, enabling fast containment (@>) and key-existence (?) queries that B-tree indexes cannot support

    A B-tree index treats the entire JSONB value as a single opaque value and can only compare for equality or ordering of the whole document. GIN (Generalized Inverted Index) decomposes the JSONB document and creates index entries for each key-value path, enabling fast lookups for @> (containment), ? (key exists), ?| (any key exists), and ?& (all keys exist). You create it with CREATE INDEX idx ON table USING GIN (jsonb_column). For querying specific paths, you can also use jsonb_path_ops class for a smaller index focused on @>.

    From the quiz JSONB, Arrays & FTS

  10. 20.When should you use prisma migrate deploy instead of prisma migrate dev?

    • A.migrate deploy is for local development; migrate dev is for CI pipelines
    • B.migrate deploy only works with cloud databases; migrate dev works locally
    • C.They are identical; the naming is just a convention
    • D.migrate deploy is designed for production/staging environments — it applies pending migration files without creating new ones, generating files, or resetting the database; migrate dev is for local development and can create new migrations and reset the database
    Show answer

    Answer: D. migrate deploy is designed for production/staging environments — it applies pending migration files without creating new ones, generating files, or resetting the database; migrate dev is for local development and can create new migrations and reset the database

    prisma migrate dev is interactive and development-focused: it detects schema drift, can reset the database, creates new migration files, and runs prisma generate. It is NOT safe for production because it can modify your migration history or reset data. prisma migrate deploy is non-interactive and production-safe: it only applies migration files that exist in prisma/migrations/ and haven't been applied yet (based on _prisma_migrations table). It never creates new files or resets the database. Use migrate deploy in your CI/CD pipeline and on production deployments: npx prisma migrate deploy.

    From the quiz Relations & Migrations

Senior level SQL questions

  1. 21.What is the difference between PgBouncer's session, transaction, and statement pooling modes?

    • A.session assigns a server connection for the full client session; transaction assigns a connection only for each transaction (released after COMMIT/ROLLBACK); statement assigns a connection per individual statement (no multi-statement transactions)
    • B.session = one connection per query; transaction = one connection per statement; statement = persistent connection per session
    • C.All three modes perform the same function but differ in TLS handling
    • D.statement mode is the recommended default for all workloads
    Show answer

    Answer: A. session assigns a server connection for the full client session; transaction assigns a connection only for each transaction (released after COMMIT/ROLLBACK); statement assigns a connection per individual statement (no multi-statement transactions)

    PgBouncer is a connection pooler that sits between your application and PostgreSQL. In session mode, each client gets a dedicated server connection for its entire session lifecycle — similar to no pooling but with connection reuse. In transaction mode (most common for web apps), a server connection is borrowed only for the duration of a transaction and returned to the pool immediately after COMMIT/ROLLBACK, allowing high multiplexing. In statement mode, each statement gets its own server connection — this prohibits multi-statement transactions. Transaction mode is recommended for stateless apps (e.g., Next.js), but requires avoiding session-level features like prepared statements, advisory locks, and SET configurations.

    From the quiz RLS, Partitioning & FDW

  2. 22.What does @@unique([userId, postId]) do in a Prisma model?

    • A.It creates separate unique indexes on userId and postId individually
    • B.It is equivalent to defining @unique on both userId and postId separately
    • C.It creates a composite unique constraint ensuring the combination of userId and postId is unique across all rows (while individual values can repeat)
    • D.It requires both fields to be non-null foreign keys
    Show answer

    Answer: C. It creates a composite unique constraint ensuring the combination of userId and postId is unique across all rows (while individual values can repeat)

    @@unique([field1, field2]) at the model level creates a composite unique constraint. This means no two rows can have the same combination of values for both fields simultaneously. A user can appear many times (with different postId values), and a post can appear many times (with different userId values), but the same user-post pair can only exist once. This is commonly used for junction/pivot tables (e.g., a user can only like a post once) or to ensure idempotency. Prisma also exposes this as a findUnique filter: prisma.like.findUnique({ where: { userId_postId: { userId, postId } } }).

    From the quiz Transactions & Raw SQL

  3. 23.How does WATCH key implement optimistic locking in Redis?

    • A.WATCH acquires a pessimistic lock on the key, blocking other clients from modifying it until EXEC is called
    • B.WATCH prevents the key from expiring during the MULTI/EXEC block
    • C.WATCH is only used inside Lua scripts for atomic read-modify-write operations
    • D.WATCH key monitors the key; if the key is modified by any other client between WATCH and EXEC, the EXEC returns nil (transaction aborted), and the client must retry — implementing check-and-set (CAS) without blocking
    Show answer

    Answer: D. WATCH key monitors the key; if the key is modified by any other client between WATCH and EXEC, the EXEC returns nil (transaction aborted), and the client must retry — implementing check-and-set (CAS) without blocking

    WATCH implements optimistic concurrency control. Pattern: (1) WATCH balance_key — start monitoring. (2) Read current value: GET balance_key. (3) MULTI — queue the update. (4) SET balance_key newValue — queue the write. (5) EXEC — if balance_key was NOT modified by another client since WATCH, the transaction executes; if it WAS modified, EXEC returns nil. (6) On nil, retry from step 1. This is the Redis equivalent of Compare-And-Swap (CAS). UNWATCH cancels all watches. EXEC and DISCARD also automatically unwatch all keys.

    From the quiz Locking, Streams & Scripting

  4. 24.What is the purpose of EXPLAIN ANALYZE in PostgreSQL?

    javascript
    EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;
    • A.It validates the SQL syntax without executing the query
    • B.It creates an optimized version of the query automatically
    • C.It returns the query execution plan and actual runtime statistics (rows scanned, time spent)
    • D.It shows the table schema and indexes
    Show answer

    Answer: C. It returns the query execution plan and actual runtime statistics (rows scanned, time spent)

    EXPLAIN shows the planned execution path. EXPLAIN ANALYZE actually runs the query and shows real vs. estimated statistics: Key terms:
    • Seq Scan — full table scan (bad for large tables — index needed)
    • Index Scan — uses an index (good)
    • cost — planner estimate (startup..total)
    • actual time — real milliseconds
    • rows — actual rows returned If you see Seq Scan on a large table in a hot path, consider adding an index. ---

    From the quiz Query Optimization

  5. 25.What is the main advantage of class-table inheritance over single-table inheritance?

    javascript
    CREATE TABLE persons   (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE employees (person_id INT PRIMARY KEY REFERENCES persons(id), department TEXT NOT NULL);
CREATE TABLE managers  (person_id INT PRIMARY KEY REFERENCES persons(id), budget NUMERIC NOT NULL);
    • A.It requires fewer JOINs to query all subtypes simultaneously
    • B.It stores each subtype's unique attributes in its own table, avoiding NULLs and keeping each table lean
    • C.It is the only approach that supports foreign keys between subtypes
    • D.It automatically enforces that each entity can only belong to one type at a time
    Show answer

    Answer: B. It stores each subtype's unique attributes in its own table, avoiding NULLs and keeping each table lean

    Class-table inheritance uses a base table for shared columns and separate extension tables for each subtypes unique columns. Each extension tables primary key is also a foreign key back to the base table. This avoids the NULL-heavy sparse table problem of STI, results in narrower and more storage-efficient tables, and allows NOT NULL constraints to be enforced per subtype. The cost is additional JOINs when retrieving a full entity.

    From the quiz Soft Deletes & Multitenancy

  6. 26.For which query type is a GiST index most appropriate?

    • A.WHERE user_id = 42
    • B.WHERE title LIKE 'prefix%'
    • C.WHERE geom && ST_MakeEnvelope(...) (geometric bounding box overlap)
    • D.WHERE created_at > '2024-01-01'
    Show answer

    Answer: C. WHERE geom && ST_MakeEnvelope(...) (geometric bounding box overlap)

    GiST (Generalized Search Tree) is an extensible index framework designed for complex data types and operators that dont fit the B-trees total ordering assumption. It excels at geometric queries (PostGIS), full-text search (tsvector), range types, and nearest-neighbor searches. The && operator for geometry bounding box overlap is a classic GiST use case. Options A, B, and D all work efficiently with B-tree indexes. Full-text search with @@ operator uses either GiST or GIN (GIN is generally faster for @@).

    From the quiz GIN, GiST & BRIN

  7. 27.What is the difference between ROWS BETWEEN and RANGE BETWEEN in a window frame?

    javascript
    -- 7-day moving average using ROWS
SELECT date, revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;
    • A.They produce identical results in all cases
    • B.ROWS is deprecated in favor of RANGE
    • C.RANGE uses physical row offsets; ROWS uses value-based offsets
    • D.ROWS uses physical row offsets; RANGE uses logical value-based offsets
    Show answer

    Answer: D. ROWS uses physical row offsets; RANGE uses logical value-based offsets

    ROWS BETWEEN n PRECEDING AND CURRENT ROW counts exactly n physical rows before the current row, regardless of values. RANGE BETWEEN n PRECEDING AND CURRENT ROW includes all rows whose ORDER BY value falls within n of the current rows value — with `RANGE`, ties receive the same frame. For example, if three rows have the same date, `ROWS BETWEEN 1 PRECEDING AND CURRENT ROW` takes exactly 2 rows, while `RANGE BETWEEN INTERVAL 1 day' PRECEDING AND CURRENT ROW` includes all rows within the value range.

    From the quiz Window Functions & GROUPING

  8. 28.What does SELECT ... FOR UPDATE NOWAIT do when the target rows are already locked?

    javascript
    BEGIN;
SELECT * FROM documents WHERE id = 1 FOR UPDATE NOWAIT;
-- If the row is locked by another transaction: immediate ERROR (no waiting)
COMMIT;
    • A.It waits for up to 1 second and then returns an empty result set
    • B.It silently skips the locked rows and returns all unlocked rows matching the query
    • C.It acquires a weaker shared lock and proceeds without waiting for the exclusive lock
    • D.It immediately raises an error rather than blocking, allowing the application to handle the conflict without any wait time
    Show answer

    Answer: D. It immediately raises an error rather than blocking, allowing the application to handle the conflict without any wait time

    NOWAIT instructs PostgreSQL not to wait if a lock cannot be acquired immediately. Instead of blocking the calling process, it immediately raises ERROR: could not obtain lock on row in relation "...". This is useful when the application prefers to fail fast and either report the conflict to the user or retry immediately, rather than waiting an unknown amount of time for the lock to be released by another transaction.

    From the quiz Locking & Deadlocks

  9. 29.What is the autovacuum trigger formula for a table?

    • A.Autovacuum triggers when the table has more than 100 dead tuples
    • B.Autovacuum triggers when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * table_size
    • C.Autovacuum runs on a fixed schedule every autovacuum_naptime seconds regardless of dead tuple count
    • D.Autovacuum is triggered manually only via VACUUM ANALYZE
    Show answer

    Answer: B. Autovacuum triggers when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * table_size

    PostgreSQL's autovacuum daemon checks each table and triggers VACUUM when the estimated dead tuple count exceeds: autovacuum_vacuum_threshold (default 50) plus autovacuum_vacuum_scale_factor (default 0.2) multiplied by the live row count. So a table with 10,000 rows triggers autovacuum when it has approximately 2,050 dead tuples. Dead tuples accumulate from UPDATE and DELETE operations (MVCC keeps old row versions). VACUUM reclaims space from dead tuples. Without regular vacuuming, tables bloat and eventually hit transaction ID wraparound. You can override thresholds per-table with ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.01).

    From the quiz RLS, Partitioning & FDW

  10. 30.When should you add @@index to a Prisma model?

    • A.Add @@index([field]) when a non-unique field is frequently used in where, orderBy, or join conditions to speed up queries; Prisma will generate a corresponding database index in the migration
    • B.@@index is required on all fields used in where clauses
    • C.@@index creates a full-text search index on the specified fields
    • D.@@index is only valid on numeric fields
    Show answer

    Answer: A. Add @@index([field]) when a non-unique field is frequently used in where, orderBy, or join conditions to speed up queries; Prisma will generate a corresponding database index in the migration

    Database indexes speed up lookups on non-primary-key columns. In Prisma, @@index([email]) generates CREATE INDEX in the migration for the email column. Multi-column indexes are also supported: @@index([status, createdAt]) creates a composite index useful for queries filtering by status and sorting by createdAt. Index selection should be based on your query patterns — over-indexing slows down writes. Foreign key fields (relation scalar fields like authorId) should almost always be indexed. @@index supports naming (@@index([field], name: "idx_name")) and type for GIN/GiST indexes in PostgreSQL.

    From the quiz Transactions & Raw SQL

Practise 900+ more SQL questions

Timed quizzes with instant feedback, the questions you miss coming back until they stick, coding challenges and AI mock interviews — free to start.

Try a mock interviewSQL jobs