DROP TABLE and ALTER TABLE in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the result of dropping a table?

Consider a database with a table named Employees. What happens if you run the following SQL command?

DROP TABLE Employees;

What will be the state of the Employees table after this command?

A The table <code>Employees</code> is permanently deleted along with all its data.

B The table <code>Employees</code> is renamed to <code>Employees_backup</code>.

C The table <code>Employees</code> remains but all data inside it is deleted.

D The table <code>Employees</code> is locked and cannot be accessed.

💡 Hint

Think about what DROP means in SQL.

The DROP TABLE command removes the entire table and all its data permanently from the database.

❓ query_result

intermediate

What does this ALTER TABLE command do?

Given a table Products with columns id and name, what is the effect of this command?

ALTER TABLE Products ADD COLUMN price DECIMAL(10,2);

A Adds a new column named <code>price</code> to the <code>Products</code> table to store decimal numbers.

B Deletes the <code>price</code> column from the <code>Products</code> table.

C Changes the data type of the <code>name</code> column to decimal.

D Renames the <code>Products</code> table to <code>price</code>.

💡 Hint

Look at the keywords ADD COLUMN in the command.

The command adds a new column called price with decimal type to the existing Products table.

📝 Syntax

advanced

Which ALTER TABLE command correctly renames a column?

You want to rename the column old_name to new_name in the table Customers. Which of the following commands is correct in standard SQL?

A ALTER TABLE Customers MODIFY COLUMN old_name new_name;

B ALTER TABLE Customers RENAME COLUMN old_name TO new_name;

C ALTER TABLE Customers CHANGE old_name new_name VARCHAR(255);

D ALTER TABLE Customers RENAME old_name new_name;

💡 Hint

Standard SQL uses a specific syntax for renaming columns.

The standard SQL syntax to rename a column is ALTER TABLE table_name RENAME COLUMN old_name TO new_name;. Other options are dialect-specific or invalid.

🔧 Debug

advanced

Why does this DROP TABLE command fail?

Consider the command:

DROP TABLE Orders;

It fails with an error saying the table does not exist. What is the best way to avoid this error?

A Rename the table before dropping it.

B Use <code>DELETE TABLE Orders;</code> instead of DROP.

C Use <code>DROP TABLE IF EXISTS Orders;</code> to drop only if the table exists.

D Restart the database server before running DROP.

💡 Hint

Check if the table exists before dropping it.

The IF EXISTS clause prevents errors if the table does not exist by only dropping it when present.

🧠 Conceptual

expert

What happens when you drop a table with foreign key dependencies?

Table Orders has a foreign key referencing Customers. What happens if you run:

DROP TABLE Customers;

Assuming no special options are used, what is the expected outcome?

A The <code>Customers</code> table is dropped but the <code>Orders</code> table remains with broken references.

B The <code>Customers</code> table is dropped and all related <code>Orders</code> rows are deleted automatically.

C Both tables are dropped automatically.

D The command fails due to foreign key constraints preventing the drop.

💡 Hint

Think about referential integrity rules in relational databases.

Most databases prevent dropping a table if other tables have foreign keys referencing it, to avoid broken references.