Database Administrator interview questions

A relational database stores structured data in tables and represents relationships between those tables through keys.

• Each table models one kind of entity or relationship using named columns.
• Rows contain individual records that follow the table's declared structure.
• SQL is used to define, query, and change the data.
• Constraints let the database enforce rules such as uniqueness and referential integrity.

A table represents a relation, a row represents one tuple or record, and a column represents one named attribute.

• Every column has a declared data type and may also have constraints.
• Each row supplies values for the table's columns, including NULL where allowed.
• Rows have no guaranteed display order unless a query uses ORDER BY.
• A key can identify one row uniquely or connect it to a row in another table.

A database schema is the defined structure and organization of database objects.

• It includes tables, columns, data types, keys, constraints, views, and indexes.
• In systems such as PostgreSQL, a schema is also a named namespace inside a database.
• Schema definitions describe allowed structure rather than the current rows stored in it.
• Changes to that structure are commonly managed through versioned migrations.

A key is one column or a set of columns used to identify rows or define relationships.

• A candidate key can uniquely identify a row.
• One candidate key is chosen as the primary key.
• A foreign key references a candidate or primary key in another or the same table.
• A composite key uses more than one column when uniqueness depends on their combination.

A primary key is the chosen column or column set that uniquely identifies every row in a table.

• Its values must be unique across all rows in the table.
• Primary-key columns cannot contain NULL.
• A table has one primary-key constraint, though that key may contain multiple columns.
• Database engines normally create an index to enforce or support the primary key.

A foreign key enforces that referenced values correspond to an existing candidate key in another or the same table.

• An orders.customer_id foreign key can reference customers.id.
• It prevents inserting a non-NULL reference with no matching parent row.
• It also controls what happens when a referenced row is updated or deleted.
• The constraint enforces referential integrity inside the database rather than relying only on application code.

Both enforce uniqueness, but a primary key is the table's main row identifier while UNIQUE defines additional candidate keys.

• A table can have only one primary-key constraint and multiple UNIQUE constraints.
• Primary-key columns are always NOT NULL.
• NULL handling in UNIQUE constraints varies by database engine and configuration.
• Foreign keys can reference a suitable UNIQUE key as well as a primary key.

These constraints define allowed or automatically supplied column values.

• NOT NULL rejects rows where the column has no value.
• CHECK requires a Boolean condition such as price >= 0 to be satisfied.
• DEFAULT supplies a value when an INSERT omits the column.
• A default does not override an explicitly provided value and does not validate existing business relationships.

These actions define how a child reference responds when its parent row is deleted.

• RESTRICT or NO ACTION prevents deletion while matching child rows remain.
• CASCADE automatically deletes the referencing child rows.
• SET NULL clears the foreign-key value and therefore requires that column to allow NULL.
• The choice must match ownership semantics because CASCADE can remove more data than one explicit DELETE names.

Data types define the values a column can store, how those values are represented, and which operations are valid.

• Integer, numeric, text, date, time, Boolean, and binary types express different domains.
• An appropriate type lets the database validate input and compare values correctly.
• Type choice affects range, precision, storage, sorting, and function behavior.
• PostgreSQL and MySQL share many type concepts but differ in some names and semantics.

These types store character data but differ in length rules and engine-specific behavior.

• CHAR(n) is fixed length and may pad shorter values to the declared size.
• VARCHAR(n) stores variable-length text up to a declared limit.
• TEXT stores variable-length text without the same application-level length declaration.
• PostgreSQL and MySQL differ in storage details, limits, and index rules, so their documentation remains authoritative.

DECIMAL or NUMERIC should be used when exact decimal arithmetic matters, while floating-point types represent approximate values.

• Monetary amounts commonly require exact precision and scale.
• FLOAT and DOUBLE can introduce binary rounding differences in decimal calculations.
• Approximate types suit scientific measurements where a range of values matters more than exact decimal representation.
• Precision and scale should be chosen to cover valid values without silently discarding required detail.

These types represent calendar dates, clock times, date-time values, and time-aware instants with different semantics.

• DATE stores a calendar date without a time of day.
• TIME stores a time of day without a date.
• TIMESTAMP stores a date and time whose time-zone interpretation depends on the database type and application contract.
• In PostgreSQL, timestamptz represents an instant normalized internally and displayed in the session time zone.

NULL represents a missing or unknown value and is not equal to any ordinary value, including another NULL.

• Comparisons with NULL usually produce UNKNOWN rather than TRUE or FALSE.
• IS NULL and IS NOT NULL test for its presence.
• WHERE keeps only rows whose predicate evaluates to TRUE, so UNKNOWN rows are filtered out.
• Functions and aggregates may handle NULL differently, so its semantics must be checked explicitly.

A basic SELECT query chooses columns, a source, optional row conditions, grouping, group conditions, ordering, and a result limit.

• SELECT defines the output expressions or columns.
• FROM identifies tables or derived sources, and JOIN connects related sources.
• WHERE filters rows before grouping, while GROUP BY and HAVING operate on groups.
• ORDER BY sorts the final result, and LIMIT or FETCH restricts the number of returned rows.

WHERE filters input rows by keeping only those for which its predicate evaluates to TRUE.

• Comparison operators test values, and AND, OR, and NOT combine conditions.
• NULL comparisons can produce UNKNOWN and therefore do not pass the filter.
• Parentheses make precedence explicit in compound predicates.
• WHERE is applied before aggregate grouping, so aggregate conditions belong in HAVING.

ORDER BY defines result order, LIMIT restricts row count, and OFFSET skips a number of rows.

• ORDER BY can sort by one or more expressions in ascending or descending order.
• Without ORDER BY, the database does not guarantee which rows appear first.
• LIMIT is useful for bounded result sets, while OFFSET is often combined with LIMIT for simple pagination.
• A stable pagination order requires enough sort columns to break ties consistently.

Aliases give temporary names to output expressions or data sources within one query.

• A column alias makes a calculated or renamed output easier to read.
• A table alias shortens qualified references such as o.customer_id.
• Aliases are especially useful when the same table appears more than once in a self-join.
• An alias does not rename the underlying database object.

An INNER JOIN returns combinations of rows that satisfy the join condition on both sides.

• A common condition matches a foreign key to a referenced key.
• Rows with no matching partner are excluded.
• One row can appear in multiple result rows when it matches multiple rows on the other side.
• The ON clause defines the relationship, while WHERE can further filter the joined result.

Outer joins retain unmatched rows from one or both sides and fill missing columns with NULL.

• LEFT JOIN keeps every left row and matching right rows.
• RIGHT JOIN keeps every right row and matching left rows.
• FULL OUTER JOIN keeps unmatched rows from both sides.
• A WHERE condition on the nullable side can accidentally remove unmatched rows and change the effective result.