80 questions / 10 random questions
Random questions, instant feedback, and review for missed questions.
View recommended Database Specialist resources →
In a relational database, which is the horizontal unit representing one record (entity)?
Answer: A row (record / tuple)
A relational table consists of rows (records) and columns (attributes); one row represents one record.
Which column (or set) is defined to uniquely identify each row in a relational database?
Answer: Primary key
A primary key enforces uniqueness and non-NULL (entity integrity) to identify each row uniquely.
Which column references another table's primary key to maintain relationships and integrity?
Answer: Foreign key
A foreign key references another table's primary key and enforces referential integrity.
Which normal form removes repeating groups so each cell holds a single value?
Answer: First normal form
First normal form removes repeating groups so each attribute holds a single value; it is the starting point.
To reach second normal form, which kind of functional dependency is removed?
Answer: Partial dependencies on only part of the primary key
Second normal form, building on 1NF, removes partial dependencies on part of a composite primary key.
To reach third normal form, which kind of functional dependency is removed?
Answer: Transitive dependencies where a non-key attribute depends on another non-key attribute
Third normal form, building on 2NF, removes transitive dependencies among non-key attributes.
Which basic SQL statement retrieves rows matching a condition from a table?
Answer: SELECT ... WHERE
SELECT chooses columns and WHERE filters rows; CREATE defines structures and GRANT assigns privileges.
Which SQL operation combines multiple tables into one result using related columns?
Answer: JOIN
JOIN combines tables on related columns; an inner join returns only rows matching in both tables.
Which SQL clause groups rows by a column to aggregate sums or counts?
Answer: GROUP BY
GROUP BY groups rows by columns and works with aggregate functions like SUM and COUNT.
What is the main purpose of creating an index?
Answer: To speed up searches (reads)
Indexes speed up reads but add maintenance cost on writes and consume extra storage.
Which pair of operations enforces the atomicity property of a transaction?
Answer: Commit and rollback
Atomicity guarantees all-or-nothing: commit finalizes on success, rollback undoes on failure.
Which describes the isolation property among the ACID properties?
Answer: Concurrent transactions do not interfere with each other
Isolation ensures concurrently executing transactions do not interfere with one another.
Which state occurs when transactions wait on each other's locks and none can proceed?
Answer: Deadlock
A deadlock is mutual lock-waiting that halts progress; it is resolved by detection and aborting one transaction.
Which mechanism prevents inconsistencies when multiple users update the same data concurrently?
Answer: Concurrency control (locking)
Concurrency control coordinates simultaneous updates with locks to keep data consistent; shared and exclusive locks exist.
Which virtual table is defined from base tables and appears as a table to users?
Answer: A view
A view provides query results as a virtual table, simplifying complex queries and restricting access.
Which database object runs automatically in response to events like table updates?
Answer: A trigger
A trigger runs automatically on events like INSERT or UPDATE, used for integrity maintenance and logging.
Which technique diagrams entities and their relationships in database design?
Answer: An E-R diagram (entity-relationship diagram)
An E-R diagram depicts entities, attributes, and relationships, used in conceptual data modeling.
Which recovery process uses logs to reapply committed updates after a failure?
Answer: Roll-forward (forward recovery)
Roll-forward restores by reapplying committed log updates to a backup; rollback undoes incomplete work.
In a distributed database, which procedure ensures all sites either all commit or all roll back?
Answer: Two-phase commit
Two-phase commit gathers agreement in a prepare phase, then commits or aborts all sites together, preserving atomicity.
Which system integrates and stores data from multiple systems for large-scale analysis?
Answer: A data warehouse
A data warehouse integrates and stores data for analysis to support decision making.
Which is the umbrella term for databases that avoid fixed schemas and suit large-scale distribution?
Answer: NoSQL
NoSQL covers flexible models like key-value and document stores, suiting large-scale, distributed, unstructured data.
Which describes a candidate key?
Answer: A column (or set) that can uniquely identify rows and could serve as a primary key
A candidate key uniquely identifies rows and could be the primary key; unchosen ones are alternate keys.
Which condition is used in SQL to test whether a column contains NULL?
Answer: IS NULL
NULL represents unknown/absent and cannot be tested with comparison operators; use IS NULL / IS NOT NULL.
What is a main benefit of applying normalization?
Answer: Reducing redundancy and preventing update anomalies
Normalization removes redundancy to prevent anomalies, though joins increase and denormalization is sometimes used for performance.
Which is an example of a one-to-many relationship between entities?
Answer: One customer has many orders
One customer with many orders is a classic one-to-many; many-to-many is modeled via a junction table as two one-to-many.
Which mechanism keeps and synchronizes database copies on other servers for availability and load distribution?
Answer: Replication
Replication synchronizes database copies, distributing read load and improving availability during failures.
Which predefined routine stored in the database lets a set of operations be invoked together?
Answer: A stored procedure
A stored procedure defines logic in the database for reuse, reducing round-trips and standardizing processing.
Which SQL clause sorts retrieved results in ascending or descending order by a column?
Answer: ORDER BY
ORDER BY sorts results by a column; GROUP BY groups for aggregation and HAVING filters after aggregation.
A large orders table is frequently slow when filtering by order date and customer ID. Which measure should be considered first?
Answer: Design a composite index matching the predicates and verify the effect with the execution plan
Query performance depends on indexes that match predicates, joins, and ordering, plus execution-plan verification. Indexes can speed reads but increase update cost.
In transaction isolation, what is the phenomenon where a transaction reads another transaction's uncommitted update?
Answer: Dirty read
A dirty read occurs when uncommitted data is read. If that data is later rolled back, dependent processing may become inconsistent.
When rerunning the same aggregate condition, another transaction's inserted rows change the result set. What is this phenomenon?
Answer: Phantom read
A phantom read occurs when the set of rows matching the same predicate changes due to another transaction's insert or delete.
Two transactions keep waiting for each other's locks to be released, and neither can proceed. What is this state?
Answer: Deadlock
A deadlock occurs when processes hold resources needed by each other and cannot leave the wait state. A DBMS may detect it and abort one transaction.
Which design splits a table by month or range so searches and maintenance can target only relevant parts?
Answer: Partitioning
Partitioning divides a large table physically or logically by range, list, hash, and similar methods, helping prune searches and maintain old data.
In production database backup design, you want a small RPO. Which design is most closely related?
Answer: Shorten backup frequency and transaction-log capture intervals
RPO indicates the point in time to which data must be recoverable. Smaller RPO requires shorter log capture and differential or incremental backup intervals.
You want to show columns containing personal data partially masked to unauthorized users. Which measure is appropriate?
Answer: Control displayed values using column masking or views
Sensitive data should be protected through least privilege, views, column masking, audit logs, and encryption where appropriate.
Which common modeling approach uses fact tables and dimension tables for easier data warehouse analysis?
Answer: Star schema
A star schema places a fact table such as sales at the center and surrounds it with dimensions such as date, product, and customer.
Which description best characterizes an OLTP system?
Answer: Processes many short transactions with high consistency
OLTP suits many short transactions such as orders, payments, and inventory updates, requiring consistency and responsiveness.
As a SQL injection countermeasure, what is the most basic implementation when an application queries a database?
Answer: Bind values using prepared statements with placeholders
Prepared statements separate SQL syntax from values and are a fundamental SQL injection defense, alongside input validation and least privilege.
What is the most appropriate main purpose of defining a foreign-key constraint?
Answer: Prevent child rows from referencing nonexistent parent rows and maintain referential integrity
A foreign-key constraint ensures values in a child table exist in the parent table's key, preventing orphan records.
When improving a slow query in an operational database, what investigation should be done first?
Answer: Check the execution plan, statistics, rows read, and wait events
Slow-query tuning starts by checking execution plans and measured data to isolate missing indexes, stale statistics, join order, I/O waits, and similar causes.
A contract is either personal or corporate, never both, and only corporate contracts have a billing contact. What is the best conceptual data model?
Answer: Model Contract as a supertype with mutually exclusive Personal and Corporate subtypes
Common attributes belong in the supertype and type-specific attributes in subtypes. An exclusivity constraint captures the rule that a contract cannot be both types.
Product prices must reproduce historical orders at the price valid then, while future price changes can be registered in advance. Which design is appropriate?
Answer: Store effective start and end dates in price history and prevent overlapping periods
An effective-dated history model represents prices at any point and future schedules. Constraints and update procedures define how overlaps and gaps are handled.
Two screens update the same customer, and the later save silently overwrites the first change. Which measure can notify the user of the conflict?
Answer: Optimistic locking with a version number in the update condition
Include the version read by the user in the UPDATE predicate. If zero rows update, another transaction changed the row and the user can be asked to reload or reconcile.
Under timestamp ordering, newer transaction T2 has already updated a row when older T1 attempts to write it. What is the basic handling?
Answer: Reject T1's write for violating timestamp order and retry if needed
Timestamp ordering preserves a logical execution order. If an older write would invalidate a newer update, it is aborted and may be retried in a valid order.
In an MVCC database, a long-running read transaction prevents cleanup of obsolete row versions and the table is bloating. What is the best response?
Answer: Identify and shorten long transactions and monitor garbage-collection progress
MVCC cannot remove old row versions while an old snapshot may need them. Fix the long transaction and restore normal product-specific cleanup such as vacuuming.
An orders table frequently runs WHERE customer_id = ? AND ordered_at >= ? ORDER BY ordered_at. Which composite index should be considered first?
Answer: (customer_id, ordered_at)
Placing equality column customer_id first and range/order column ordered_at second commonly supports the customer's time-range scan and ordering. Verify with real data and plans.
A join estimated at 100 rows actually returns one million, causing an inefficient join algorithm. What should be checked first?
Answer: Statistics freshness, data skew, column correlation, and actual row counts
The optimizer estimates cardinality from statistics. Stale statistics, skew, or correlated columns can cause large errors and poor join order or algorithm choices.
A frequently updated table must be paged without duplicates or gaps in (created_at, id) order. Which condition is preferable to OFFSET pagination?
Answer: Fetch rows after the previous page's last (created_at, id) using the same ordering
Keyset pagination uses a unique ordering and the prior last key. It reduces effects of concurrent changes and the scan cost of deep offsets.
A nightly batch updates 100 million rows. On failure it restarts from the beginning and misses its window. What is the best improvement?
Answer: Commit in restartable units and resume from checkpoints
Split work into business-consistent units and persist progress. Design idempotency to avoid double updates and define business rules for partial commits.
A required column must be added with near-zero downtime while old application versions still run. Which migration sequence is safest?
Answer: Add it nullable, deploy dual-compatible code, backfill data, then tighten the constraint
The expand-and-contract pattern first extends the schema compatibly, then migrates data and code, and only later removes temporary compatibility.
A multi-terabyte production database must migrate to another product with only minutes of downtime. Which method is most appropriate?
Answer: Take an initial full copy, continuously apply changes with CDC, validate, then cut over
CDC follows changes during the bulk load. Once lag is small, a short freeze allows final sync and cutover, with count, hash, and business validation.
During two-phase commit, a participant is prepared but loses contact with the coordinator. Why must it not commit independently?
Answer: It could disagree with other participants and violate distributed atomicity
A prepared participant must learn the final decision from the coordinator's durable record or recovery procedure. Independent action can split commit and rollback outcomes.
A read replica may return stale data immediately after a write. How can a user be guaranteed to see their own update right away?
Answer: Read from the primary until a time or replication-position condition is met
Asynchronous replicas lag. For read-your-writes consistency, pin reads to the primary or wait until the replica reaches the required log position.
During a network partition, two primary candidates accepted writes and data conflicted after recovery. What is the core prevention?
Answer: Use quorum and fencing so only one primary can accept writes
To prevent split brain, quorum selects one legitimate primary and fencing isolates the old primary from storage or network access.
Critical data was deleted accidentally at 10:15. The team needs the 10:14 state for comparison with other databases. What is the best method?
Answer: Perform point-in-time recovery to an isolated environment using backups and transaction logs
PITR replays to just before deletion. Validate affected and dependent data in isolation, then choose selective repair or full cutover based on impact.
Database encryption keys are being rotated. Why is storing plaintext keys beside database backups dangerous?
Answer: A storage breach would expose both encrypted data and its decryption key
Keys should be protected in a separate KMS or HSM, with access control, audit, rotation, and retention for decrypting older backups.
In a multitenant database, the database itself must prevent cross-tenant rows even if an application predicate is omitted. Which measure is appropriate?
Answer: Enforce row-level security from authenticated identity and separate administrative privileges
Row-level security makes the DBMS enforce tenant boundaries on queries. Trusted connection identity, bypass privileges, and administrative auditing must also be designed.
A customer requests personal-data deletion. The live database is cleared, but backups retain data for a period. Which operation is appropriate?
Answer: Define retention and re-deletion after restore, isolate backups, and expire them on schedule
When backups cannot be edited safely, define justified retention, access restrictions, and expiration. Reapply deletion records after restore so deleted data is not revived.
A materialized view accelerates complex aggregation, but users require data no more than five minutes stale. What is most important in the design?
Answer: Define refresh method, interval, failure monitoring, and freshness measurement
Because materialized views persist results, full versus incremental refresh, duration, retries, and last-success monitoring directly determine the freshness SLA.
A hash join spills heavily to temporary storage and slows a nightly aggregation. What is the best investigation before changing it?
Answer: Check actual plan rows, estimation error, memory limit, temporary I/O, and pre-join filtering
Spills can result from cardinality errors, insufficient filtering, or memory limits. Measurements guide whether to fix statistics, SQL, indexes, batching, or memory.
With standard SQL NULL semantics, apply WHERE id NOT IN (2, NULL) to rows whose IDs are 1, 2, and 3. Which IDs are returned?
Answer: No matching rows are returned.
The predicate is false for 2 and unknown for 1 and 3. WHERE keeps only true predicates. Decide how nulls should behave before using an alternative such as NOT EXISTS.
A LEFT JOIN must list every customer and only their paid orders, retaining customers with no paid orders. Where should the order predicate status='paid' go?
Answer: Put it in ON with the customer-ID match.
Filtering orders in ON preserves unmatched customers as null-extended rows. Filtering the right-side status only in WHERE removes those rows.
Customers are LEFT JOINed to orders and grouped by customer ID. orders.id is a non-null primary key. Which aggregate yields zero for a customer with no orders?
Answer: Count using COUNT(orders.id).
The outer join retains a row even without an order. COUNT(*) and COUNT(1) count that row; COUNT(orders.id) ignores its null order ID and yields zero.
One order has two lines and three payments. Joining both child tables by order ID inflates their SUM values. How should both totals be calculated?
Answer: Aggregate each child by order before joining the totals.
The join forms six combinations and repeats each amount. Pre-aggregating each child preserves independent totals. Distinct amounts can incorrectly collapse separate transactions with equal values.
Sales detail queries for 2025 and 2026 return compatible columns. Separate transactions may have identical displayed values. Which operation preserves every row, including duplicates?
Answer: Combine them using UNION ALL.
UNION ALL retains duplicate rows. UNION deduplicates the selected values and may collapse separate transactions. Use a final ORDER BY if a defined presentation order is required.
Select customers whose total sales from 2026 orders reach one million yen. Where do the date and aggregate-total predicates belong?
Answer: Put the date in WHERE and SUM(amount) in HAVING.
WHERE restricts orders before grouping; HAVING filters the resulting groups by total. Omitting the date filter changes the sales period being measured.
Return each customer-table row once if at least one related order exists. Which approach avoids multiplying customers by their number of orders?
Answer: Use an EXISTS predicate correlated by customer ID.
EXISTS tests whether a matching order is present without multiplying the outer row. It expresses the requirement directly; performance still depends on indexes and the execution plan.
A PostgreSQL table has only CHECK (quantity > 0) on quantity. Which additional constraint rejects NULL quantities?
Answer: Add NOT NULL to quantity.
PostgreSQL CHECK rejects false results but permits unknown results caused by NULL. Combine the range check with NOT NULL to require a positive value.
Membership numbers are non-null. The app checks for absence with SELECT before INSERT, yet concurrent requests create duplicates. What is the core prevention?
Answer: Enforce UNIQUE on the number and handle conflicts.
A check-then-insert race requires database-enforced uniqueness. Handle constraint conflicts in the application and resolve existing duplicates before adding the constraint.
Use ROW_NUMBER to select one latest order per customer. ordered_at is non-null and IDs are unique. Which ordering deterministically resolves equal timestamps?
Answer: Partition by customer; order by ordered_at DESC, id DESC.
Timestamp ties require a unique tiebreaker. Adding id DESC defines which tied row wins, while PARTITION BY customer keeps ranking separate for each customer.
A learner may enroll in multiple courses and each course may have many learners. A learner may enroll in a particular course only once. Which uniqueness rule fits the enrollment table?
Answer: Enforce one unique constraint on the learner ID and course ID together.
Uniqueness applies to the learner-course pair. Make both IDs required foreign keys and enforce a primary or unique key on the pair. A surrogate enrollment ID does not replace that business constraint.
Account numbers are unique only within a tenant. An invoice requires both a tenant ID and an account number. Which database rule ensures the account belongs to that tenant?
Answer: Reference the matching pair of columns in accounts with one composite foreign key.
Give the account table a candidate key on tenant ID and account number. Reference that pair with a single foreign key. Separate existence checks do not prove that the two values occur together in one account row.
A department's parent ID references the same table's department ID; roots can have a null parent. What can this self-referencing foreign key alone fail to prevent?
Answer: Two departments that both exist name one another as their parent.
A foreign key checks that a referenced row exists. If A and B both exist, each can reference the other without breaking that check. A hierarchy that must be acyclic needs a separate cycle check when reparenting.
Order lines have a foreign key to products. Which deletion rule rejects physical deletion of a referenced product without modifying the historical lines?
Answer: Use ON DELETE RESTRICT to block deletion while lines refer to the product.
ON DELETE RESTRICT blocks deletion while a line references the product. Discontinuing sales need not erase historical identity: keep the product row and change its availability status.
One transaction inserts an order and then adds optional gift wrapping. Which sequence lets it undo only the wrapping and still commit the order?
Answer: Set a savepoint after the order, add wrapping, roll back to that point and commit.
Create a savepoint after inserting the order and before adding the wrapping. Rolling back to it discards the later work, not the order. The retained order is still uncommitted until the final commit.
A reservation must subtract one unit only if at least one is available. Quantities are integers with one row per product; concurrency errors are handled separately. Which update distinguishes success from insufficient stock?
Answer: Decrement only if stock is positive and check the number of rows updated.
Use both product ID and sufficient stock in the UPDATE predicate and decrement the stored value. For an existing product, one affected row means reserved; zero means unavailable. An earlier unprotected read can become stale before an unconditional write.
Three sites report 10, 20 and NULL. NULL means unmeasured rather than zero. Which calculation gives the mean across measured sites only?
Answer: Leave the missing reading null and use AVG to obtain 15 from the two measured sites.
AVG skips null inputs, giving (10 + 20) / 2 = 15. Replacing missing readings with zero or dividing by all rows computes a different metric that counts the unmeasured site.
A daily report must return one total of zero even when the date filter matches no sales. Amount is non-null. Which ungrouped aggregate expression meets the requirement?
Answer: Use COALESCE(SUM(amount), 0) to fill the missing aggregate result.
An ungrouped aggregate returns one result even for no input rows, but SUM is null on empty input. Coalesce the aggregate result to zero. Transforming individual amounts cannot create a missing input row.
Four stores have sales of 100, 100, 80 and 60. Return all stores in the two highest distinct sales levels. Ranking only by descending sales, which method works?
Answer: Use DENSE_RANK and select stores with a rank of two or better.
DENSE_RANK assigns 1, 1, 2, 3, so the first two ranks include three stores. RANK leaves a gap after the top tie. The requirement counts distinct sales levels rather than a fixed number of rows.
Payments in unique transaction-sequence order are 100, 20, 30 and 40. Which window frame sums the current row and its two predecessors, yielding 90 on row four?
Answer: Use ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
ROWS counts positions in the ordered input. On row four the frame spans 20, 30 and 40, totaling 90. Near the start it includes only available rows. Elapsed time between payments does not affect this frame.