SQL Quiz: Alter Table
10 questions · exam conditions
0:00
Alter TableQuestion 1 of 10

In PostgreSQL, users.email is currently declared NOT NULL, has no default, and every existing row contains a non-null email address. The following statement succeeds: ALTER TABLE users ALTER COLUMN email DROP NOT NULL;. A new row is then inserted without specifying email.

What is the resulting effect on the data and schema?

Existing emails are erased, and the new row receives a null email under the relaxed constraint.
Existing emails remain unchanged, but the insert fails because dropping NOT NULL does not permit omitted values.
Existing emails remain unchanged, and the new row receives a null email because the column is now nullable.
Existing emails remain unchanged, and the new row copies an email from an existing row automatically.
← Back to quizzes

SQL Quiz

SQL Quiz: Alter Table

Practice Alter Table in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Alter Table, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

In PostgreSQL, users.email is currently declared NOT NULL, has no default, and every existing row contains a non-null email address. The following statement succeeds: ALTER TABLE users ALTER COLUMN email DROP NOT NULL;. A new row is then inserted without specifying email.

What is the resulting effect on the data and schema?

  1. Existing emails are erased, and the new row receives a null email under the relaxed constraint.
  2. Existing emails remain unchanged, but the insert fails because dropping NOT NULL does not permit omitted values.
  3. Existing emails remain unchanged, and the new row receives a null email because the column is now nullable. (correct answer)
  4. Existing emails remain unchanged, and the new row copies an email from an existing row automatically.
Explanation: 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).

Question 2

PostgreSQL is being used with transactional DDL. A populated table members is altered inside one transaction: BEGIN; ALTER TABLE members ADD COLUMN phone text; ALTER TABLE members ADD COLUMN tier text NOT NULL;. The second ALTER TABLE fails because existing rows would have null tiers. The transaction is then rolled back.

What is the schema of members after the rollback?

  1. The phone column remains, but the failed tier column is absent because only the second statement is undone.
  2. Both phone and nullable tier remain because PostgreSQL preserves the successful portions of each alteration.
  3. Both columns remain, and existing rows receive empty strings so the NOT NULL condition is satisfied.
  4. Neither phone nor tier exists because the failed transaction rolls back the earlier schema change. (correct answer)
Explanation: 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.

Question 3

A PostgreSQL table inventory has a code column declared as varchar(20). Most codes are at most eight characters long, but one existing row contains 'CLEARANCE-2026'. The column must be changed to varchar(8), and truncating long codes is acceptable.

Which approach most reliably completes the change while making the truncation explicit?

  1. Change the type directly to varchar(8) and rely on the declaration to truncate every oversized stored value.
  2. Set a default of eight characters, then change the type directly to varchar(8) for all stored values.
  3. Apply SET NOT NULL to the column, then change its type directly to varchar(8) without updating data.
  4. Update oversized codes with left(code, 8), then alter the column type to varchar(8). (correct answer)
Explanation: 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."

Question 4

A PostgreSQL table named customers already contains several thousand rows. A new column named region must be added. Every existing row should receive the value 'unknown', and the database must ultimately reject future rows that have no region. No permanent default should be defined.

Which sequence of statements satisfies all requirements?

  1. ADD COLUMN region text; update existing rows to 'unknown'; then ALTER COLUMN region SET NOT NULL. (correct answer)
  2. ADD COLUMN region text NOT NULL; update existing rows to 'unknown'; then verify the new constraint.
  3. ADD COLUMN region text; apply SET NOT NULL; then update existing rows to 'unknown'.
  4. ADD COLUMN region text; update existing rows to 'unknown'; then leave the column nullable.
Explanation: 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.

Question 5

In PostgreSQL, view product_summary selects product_id, name, and description from table products. The description column must be removed, the view must still exist after the migration, and the administrator is not permitted to use CASCADE.

Which migration order meets these requirements?

  1. Drop products.description first, then replace the dependent view so it selects only the remaining columns.
  2. Drop the view, drop products.description, and recreate the view using only the remaining columns. (correct answer)
  3. Rename description, drop the renamed column, and leave the original view definition unchanged.
  4. Set description to null in every row, then drop the column while retaining the dependent view.
Explanation: 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.

Question 6

In PostgreSQL, accounts.alias currently contains 'north', 'north', NULL, and NULL. The database permits multiple nulls in a UNIQUE constraint. An administrator wants to add CONSTRAINT uq_alias UNIQUE (alias).

Which action will allow the constraint to be added while retaining all four rows?

  1. Change one 'north' value to a distinct non-null alias, then add the unique constraint. (correct answer)
  2. Replace both null values with 'north', then add the unique constraint to the column.
  3. Apply SET NOT NULL to the alias column, then add the unique constraint without updating rows.
  4. Add a regular index on alias, then add the unique constraint without changing any values.
Explanation: 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.

Question 7

A PostgreSQL table tickets contains three rows whose status values are 'open', NULL, and 'closed'. An administrator executes ALTER TABLE tickets ALTER COLUMN status SET DEFAULT 'open'; and then inserts one row without specifying status.

Immediately after these operations, which description of the four rows is correct?

  1. There are no null statuses, because setting the default replaces existing nulls with 'open'.
  2. There is one null status, one 'closed' status, and two 'open' statuses, because only the newly inserted row uses the default. (correct answer)
  3. There is one null status and one 'open' status, because the new row receives a null value.
  4. There are two null statuses and one 'open' status, because defaults apply only after another schema change.
Explanation: 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?

Question 8

A PostgreSQL table employees contains data, constraints, and indexes. The statement ALTER TABLE employees RENAME COLUMN dept TO department; succeeds. An application still sends SELECT dept FROM employees;.

Which outcome should the administrator expect?

  1. The data, constraints, and indexes remain, but the application query fails because dept is no longer the column name. (correct answer)
  2. The data remains, but every constraint and index involving the column is automatically removed by the rename.
  3. The query continues to work because PostgreSQL permanently treats dept as an alias for department.
  4. The table is recreated without its rows, and the application query returns an empty result using dept.
Explanation: 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.

Question 9

A PostgreSQL database has customers(customer_id) values 1 and 2. The nullable column invoices.customer_id contains 1, 3, and NULL. The administrator wants to add a foreign key from invoices.customer_id to customers.customer_id.

Which sequence permits a normal validated foreign key to be added?

  1. Add the foreign key first, then insert customer 3 after PostgreSQL reports the orphaned invoice.
  2. Delete the invoice whose customer ID is null, then add the foreign key without changing customer 3.
  3. Insert customer 3 into customers, then add the foreign key to invoices.customer_id. (correct answer)
  4. Create a regular index on invoices.customer_id, then add the foreign key without changing either table.
Explanation: 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.

Question 10

A nullable PostgreSQL column imports.code has type text and contains '101', ' 202 ', and 'N/A'. It must become an integer column. Invalid codes may be converted to null.

Which preparation and alteration will successfully perform the conversion?

  1. Alter the type using code::integer; PostgreSQL will automatically convert 'N/A' to null.
  2. Set the column default to null, then alter the type using trim(code)::integer for every existing row.
  3. Replace 'N/A' with null, then alter the type using trim(code)::integer in a USING clause. (correct answer)
  4. Add a check requiring numeric text, then alter the type using code::integer without changing existing rows.
Explanation: 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.