- What is the schema of
members after the rollback? Answer: Neither phone nor tier exists because the failed transaction rolls back the earlier schema change.
When working with DDL (Data Definition Language) statements in PostgreSQL, the critical concept to understand is transactional DDL — unlike many other databases, PostgreSQL allows schema changes like ALTER TABLE to participate fully in transactions, meaning they can be committed or rolled back as a unit.
Here's the key insight: a transaction is all-or-nothing. When BEGIN starts a transaction block, every statement inside it — including schema changes — is provisional until COMMIT. If anything causes a rollback (whether explicit or triggered by a failure), every change made within that transaction is undone, regardless of which individual statement succeeded. In this scenario, the second ALTER TABLE fails because existing rows cannot satisfy the NOT NULL constraint on tier without a default value. That failure causes the entire transaction to roll back, wiping out both ALTER TABLE statements. So members returns to its original schema — no phone, no tier. That makes D correct.
A is wrong because it assumes partial rollback — as if PostgreSQL undoes only the failed statement while keeping earlier successful ones. Transactions don't work that way; failure rolls back everything. B is wrong for the same reason and adds an additional misconception that PostgreSQL "preserves successful portions," which misrepresents how atomicity works. C is wrong on two counts: the transaction fails before committing, so neither column persists, and PostgreSQL would never silently convert nulls to empty strings to satisfy a constraint.
Remember: in PostgreSQL, DDL is transactional. If a transaction fails, all schema changes inside it vanish — treat the entire BEGIN...COMMIT block as a single atomic operation.
- Which approach most reliably completes the change while making the truncation explicit?
Answer: Update oversized codes with left(code, 8), then alter the column type to varchar(8).
When shrinking a varchar column in PostgreSQL, you need to understand two distinct problems: making existing data conform to the new constraint, and then changing the schema itself. PostgreSQL will not silently truncate stored values when you narrow a column — it will raise an error if any existing row exceeds the new length. That means the order and explicitness of your steps matter enormously.
Option D is the correct approach because it separates the two concerns cleanly. First, you explicitly truncate oversized values in place with UPDATE inventory SET code = left(code, 8) WHERE length(code) > 8, making the data transformation visible and auditable. Once every value fits within eight characters, the ALTER TABLE ... ALTER COLUMN code TYPE varchar(8) succeeds without conflict. This is explicit, safe, and reversible before the ALTER runs.
Option A fails because PostgreSQL does not auto-truncate existing data when you change a column's type — it throws an error on the conflicting row 'CLEARANCE-2026', aborting the statement entirely. The declaration only limits future inserts, not stored values.
Option B introduces a red herring. Setting a column default affects only future rows that omit a value; it has zero effect on existing stored data. The oversized row still breaks the ALTER.
Option C is completely unrelated to the problem. SET NOT NULL enforces null constraints, not length limits, and does nothing to prepare oversized values for a type change.
Your study tip: whenever you alter a column to a more restrictive type, always clean the data first. Think of it as "prepare, then declare."
- Which outcome should the administrator expect?
Answer: The data, constraints, and indexes remain, but the application query fails because dept is no longer the column name.
When working with DDL operations like RENAME COLUMN, the key question to ask is: what exactly changes, and what stays the same? PostgreSQL's RENAME COLUMN is a metadata-only operation — it updates the column's name in the system catalog without touching the underlying data, constraints, or indexes. Those structures are automatically updated to reference the new column name, so nothing is lost.
This makes A correct. After renaming dept to department, the table's data is fully intact, all constraints (like NOT NULL or CHECK) and indexes on that column continue to work under the new name — but any external query still using the old name dept will receive an error like column "dept" does not exist. The rename is a hard cut; PostgreSQL does not maintain the old name in any way.
B is wrong because it describes the opposite of what happens to constraints and indexes. They are preserved and updated, not dropped. This is a common misconception — students sometimes confuse renaming a column with dropping and recreating it.
C is wrong because PostgreSQL does not create permanent aliases from rename operations. Once renamed, dept is simply gone as a recognized identifier. There is no aliasing mechanism that persists at the table level.
D is wrong because RENAME COLUMN never rebuilds or truncates the table. The rows remain entirely untouched; this answer conflates a rename with a TRUNCATE or DROP/RECREATE pattern.
A useful rule of thumb: DDL renames in PostgreSQL change the name, preserve the structure and data, and immediately break any client code still using the old name — so always audit your application queries before renaming columns in production.
- Which sequence permits a normal validated foreign key to be added?
Answer: Insert customer 3 into customers, then add the foreign key to invoices.customer_id.
When adding a foreign key constraint in PostgreSQL, the database validates every existing row in the child table against the parent table. This means before the constraint can be created, all non-NULL values in invoices.customer_id must already exist in customers.customer_id. NULL values are exempt — a NULL foreign key simply means "no reference," which is always valid.
The correct path is C: insert customer 3 into the customers table first, then add the foreign key. At that point, every non-NULL value in invoices.customer_id (1 and 3) has a matching row in customers, so PostgreSQL's validation passes cleanly.
A gets the sequence backwards. PostgreSQL performs validation at the moment the constraint is added, so it will reject the foreign key immediately if orphaned rows exist. You can't add the constraint first and fix the data second — the error prevents the constraint from being created at all.
B removes the NULL invoice row, but NULL was never the problem. The real violation is customer_id = 3, which has no matching customer. Deleting the NULL row still leaves an orphaned reference to customer 3, so the foreign key addition would still fail.
D confuses indexing with constraint enforcement. Adding an index on invoices.customer_id improves query performance but does nothing about referential integrity. The orphaned value 3 still exists, and PostgreSQL will still reject the foreign key.
Study tip: When a question asks about adding a foreign key to existing data, always ask yourself: "Does every non-NULL child value already exist in the parent table?" If not, fix the data first, then add the constraint.
- Immediately after these operations, which description of the four rows is correct?
Answer: There is one null status, one 'closed' status, and two 'open' statuses, because only the newly inserted row uses the default.
When working with SQL schema changes, it's crucial to distinguish between what affects existing rows versus future rows. ALTER TABLE ... SET DEFAULT is a metadata-only change — it tells the database what value to use when a new row omits that column, but it never touches data already stored in the table.
Here's what actually happens in this scenario: the three original rows keep their exact values — 'open', NULL, and 'closed' — completely unchanged. When the administrator then inserts a new row without specifying status, PostgreSQL applies the newly set default of 'open', adding a fourth row with that value. The final state is therefore one NULL, one 'closed', and two 'open' values — exactly what answer B describes.
Answer A is tempting but reflects a fundamental misunderstanding: SET DEFAULT is not an UPDATE statement. It cannot retroactively overwrite existing NULL values. If you wanted to replace existing nulls, you'd need an explicit UPDATE tickets SET status = 'open' WHERE status IS NULL. Answer C incorrectly suggests the new row receives NULL, which would only happen if no default were defined and no value were supplied — but a default was just set. Answer D invents a fictional rule that defaults require an additional schema change to activate; defaults take effect immediately upon being set.
A useful mental model: think of SET DEFAULT as updating the column's instruction manual, not the column's existing data. On SQL exams, whenever you see a default being set, ask yourself — does this operation touch rows already in the table, or only rows inserted afterward?
- Which action will allow the constraint to be added while retaining all four rows?
Answer: Change one 'north' value to a distinct non-null alias, then add the unique constraint.
When working with UNIQUE constraints in PostgreSQL, your goal is to ensure no duplicate non-null values exist in the target column before the constraint can be applied. PostgreSQL allows multiple NULL values under a unique constraint because NULL is not considered equal to anything — not even another NULL — so nulls never conflict with each other.
The problem here is the two duplicate 'north' values. A unique constraint will be rejected at creation time if existing data violates it, regardless of nulls. The correct path is A: change one 'north' to a different, distinct alias. This eliminates the only actual uniqueness violation, leaving you with one 'north', one distinct alias, and two NULLs — all of which coexist peacefully under the constraint.
B makes things worse, not better. Replacing the NULLs with 'north' would give you four identical 'north' values, creating three additional conflicts instead of resolving any. C is a misconception about how SET NOT NULL works — it enforces that no future nulls can be inserted, but it does nothing to resolve the duplicate 'north' problem, so the constraint still fails. Additionally, SET NOT NULL would reject the existing NULL rows entirely, violating the requirement to retain all four rows. D sounds technical but misunderstands constraint creation: adding a regular (non-unique) index doesn't resolve uniqueness violations, and PostgreSQL would still refuse the UNIQUE constraint with duplicate data present.
The key study tip: always audit for duplicate non-null values before adding a UNIQUE constraint — NULLs are never the blocker in PostgreSQL.
- What is the resulting effect on the data and schema?
Answer: Existing emails remain unchanged, and the new row receives a null email because the column is now nullable.
When you alter a column's constraints in PostgreSQL, it's critical to understand what changes and what doesn't. Constraints govern what future data can look like — they don't retroactively modify existing rows.
Running ALTER TABLE users ALTER COLUMN email DROP NOT NULL does exactly one thing: it removes the NOT NULL constraint from the schema definition. Every existing row keeps its current email value untouched, because the alteration has no effect on stored data. Now that the column is nullable, any subsequent INSERT that omits email will store NULL in that column — not a copy from another row, not an error, just NULL. That reasoning confirms C as the correct answer.
A is wrong because ALTER TABLE never erases or modifies existing row data. It only changes the column's metadata/constraint rules. Thinking that dropping a constraint "clears" existing values is a common misconception.
B is wrong because it reverses the actual behavior. Once NOT NULL is dropped, the column does permit null values — that's the entire purpose of the statement. Omitting a nullable column with no default results in NULL, not an error.
D is wrong because PostgreSQL has no mechanism to auto-copy values from other rows during an insert. Missing column values resolve to either a declared DEFAULT or NULL if nullable; they never pull data from existing records.
A useful rule of thumb: ALTER COLUMN changes the rules, not the data. Always ask yourself separately, "What happens to existing rows?" (nothing) and "What happens to future inserts?" (they follow the new rules).
- Which sequence of statements satisfies all requirements?
Answer: ADD COLUMN region text; update existing rows to 'unknown'; then ALTER COLUMN region SET NOT NULL.
When modifying an existing table's structure in PostgreSQL, you need to think carefully about the order of operations — constraints are enforced immediately, so adding a NOT NULL constraint before populating data will cause errors on existing rows.
The correct approach, option A, follows the only logical sequence that satisfies every requirement. First, you add the nullable column (ADD COLUMN region text), which safely creates it without breaking existing rows. Next, you update all existing rows to 'unknown', so no row has a NULL in that column. Finally, you apply ALTER COLUMN region SET NOT NULL, which PostgreSQL will accept because no NULLs remain. Crucially, no permanent DEFAULT is stored on the column, exactly as the passage requires.
Option B fails immediately: adding a column with NOT NULL but no default causes PostgreSQL to reject the statement outright, because existing rows would instantly violate the constraint — there's nothing to fill them with. Option C reverses the update and constraint steps, meaning you'd be attempting to set NOT NULL while thousands of existing rows still contain NULL, which PostgreSQL will refuse. Option D correctly adds the column and populates existing rows, but then does nothing to enforce the constraint going forward — future rows could still insert NULL values, violating the requirement to reject missing regions.
A useful pattern to remember: populate, then constrain. Whenever you retrofit a NOT NULL column onto an existing table without a permanent default, always fill existing rows first, then lock down the constraint. This sequence appears frequently in schema migration questions and real-world database work alike.
- Which preparation and alteration will successfully perform the conversion?
Answer: Replace 'N/A' with null, then alter the type using trim(code)::integer in a USING clause.
When altering a column's data type in PostgreSQL, you need to think about two things: does every existing value convert cleanly, and does PostgreSQL know how to perform the conversion? The ALTER COLUMN TYPE command accepts a USING clause that tells PostgreSQL exactly how to transform old values into the new type — this is your key tool here.
Option C is correct because it handles both problems systematically. First, updating 'N/A' to null removes the uncastable value before the type change. Then, ALTER COLUMN type integer USING trim(code)::integer strips whitespace from ' 202 ' and casts remaining values to integers. Null values pass through safely, leaving you with a clean integer column.
Option A fails because PostgreSQL does not silently convert uncastable strings to null — attempting to cast 'N/A' to integer raises a runtime error. PostgreSQL is strict; it won't guess your intent.
Option B misunderstands how ALTER COLUMN TYPE works. Setting a column default has no effect on existing rows, and the USING clause must be written inside the ALTER statement itself — you can't apply it row-by-row as a separate step.
Option D tries to enforce a check constraint on data that already violates it. PostgreSQL will reject adding the constraint because existing rows contain 'N/A', and even if it worked, no check constraint makes code::integer safe to run against invalid data.
A good study habit: whenever you see a type-change migration problem, ask yourself "are all existing values castable?" If not, you must clean first, then cast using USING.
- Which migration order meets these requirements?
Answer: Drop the view, drop products.description, and recreate the view using only the remaining columns.
When managing dependent database objects in PostgreSQL, you need to think about dependency order: you cannot modify or drop an object that another object depends on without first handling that dependency. A view that references a column creates a hard dependency — PostgreSQL will refuse to drop that column while the view exists, unless you use CASCADE (which is explicitly forbidden here).
The only safe path is B: drop the view first, then drop products.description, then recreate the view selecting only the remaining columns. This respects dependency order, keeps the view alive after migration, and avoids CASCADE. PostgreSQL allows this clean sequence without any errors.
Answer A fails because it tries to drop products.description before replacing the view. At the moment you issue DROP COLUMN description, the view still exists and still references that column — PostgreSQL will throw a dependency error and block the operation entirely.
Answer C is a creative-sounding trap, but renaming the column doesn't help. The original view definition references description by name; after the rename, the view is now broken because the column it references no longer exists under that name. The view "exists" in name only — it would throw an error on execution.
Answer D is a red herring. Setting column values to NULL is a data operation, not a structural one. PostgreSQL's dependency system is based on schema structure, not data values, so nullifying rows does nothing to remove the column's dependency relationship with the view.
A good rule of thumb: when dropping a column, always ask "what references this column?" and handle those dependents before issuing the DROP COLUMN statement.
