DDL (CREATE, ALTER, DROP) in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding CREATE TABLE syntax

What will be the result of executing the following SQL statement?

CREATE TABLE Employees (ID INT PRIMARY KEY, Name VARCHAR(50), Age INT);

Choose the correct description of what this statement does.

A Adds three new columns to an existing Employees table: ID, Name, and Age.

B Deletes the Employees table if it exists and creates a new one with the specified columns.

C Creates a new table named Employees with three columns: ID, Name, and Age, where ID is the primary key.

D Renames an existing table to Employees and sets ID as the primary key.

💡 Hint

Think about what the CREATE TABLE command does in SQL.

The CREATE TABLE statement creates a new table with the specified columns and constraints. It does not delete or rename tables.

📋 Factual

intermediate

Effect of ALTER TABLE ADD COLUMN

What happens when you execute the following SQL command?

ALTER TABLE Employees ADD COLUMN Salary DECIMAL(10,2);

A Deletes the Salary column from the Employees table.

B Adds a new column named Salary to the Employees table with decimal values up to 10 digits and 2 decimal places.

C Changes the data type of the Salary column to DECIMAL(10,2).

D Creates a new table named Salary with the specified decimal format.

💡 Hint

Consider what ALTER TABLE with ADD COLUMN does.

ALTER TABLE with ADD COLUMN adds a new column to an existing table without affecting existing data.

🔍 Analysis

advanced

DROP TABLE consequences

What is the immediate effect of running this SQL command?

DROP TABLE Employees;

Choose the correct outcome.

A Removes the Employees table and all its data permanently from the database.

B Deletes all data inside Employees but keeps the table structure intact.

C Renames the Employees table to a temporary name.

D Locks the Employees table for editing but does not delete it.

💡 Hint

Think about what DROP TABLE does compared to DELETE or TRUNCATE.

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

❓ Comparison

advanced

Difference between ALTER TABLE MODIFY and ALTER TABLE RENAME

Which statement correctly describes the difference between these two commands?

ALTER TABLE Employees MODIFY Age SMALLINT;
ALTER TABLE Employees RENAME TO Staff;

A MODIFY deletes a column; RENAME TO deletes the table.

B MODIFY renames the table; RENAME TO changes a column's data type.

C Both commands rename the table but in different ways.

D MODIFY changes the data type of a column; RENAME TO changes the table's name.

💡 Hint

Consider what MODIFY and RENAME TO do in ALTER TABLE statements.

MODIFY changes a column's data type or definition; RENAME TO changes the table's name.

❓ Reasoning

expert

Predicting error from DROP COLUMN on non-existent column

What error will this SQL command produce if the column 'Salary' does not exist in the Employees table?

ALTER TABLE Employees DROP COLUMN Salary;

A An error indicating that the column 'Salary' does not exist.

B The command will succeed and do nothing.

C The Employees table will be deleted.

D The command will rename the 'Salary' column to NULL.

💡 Hint

Think about what happens when you try to drop a column that isn't there.

Most SQL databases raise an error if you try to drop a column that does not exist.