DROP TABLE and ALTER TABLE in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to delete the table named 'employees'.

SQL

DROP TABLE [1];

Drag options to blanks, or click blank then click option'

Astaff

Bemployee

Cemployee_list

Demployees

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong table name that does not exist.

Forgetting to include the table name after DROP TABLE.

The correct table name is 'employees'. The DROP TABLE command deletes the entire table.

2fill in blank

medium

Complete the code to add a new column 'age' of type INTEGER to the 'users' table.

SQL

ALTER TABLE users [1] age INTEGER;

Drag options to blanks, or click blank then click option'

AADD COLUMN

BCHANGE COLUMN

CMODIFY COLUMN

DDROP COLUMN

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'DROP COLUMN' which removes a column instead of adding.

Using 'MODIFY COLUMN' which changes an existing column.

To add a new column, use 'ADD COLUMN'. This adds 'age' as an INTEGER to 'users'.

3fill in blank

hard

Fix the error in the code to remove the column 'salary' from the 'staff' table.

SQL

ALTER TABLE staff [1] salary;

Drag options to blanks, or click blank then click option'

AREMOVE COLUMN

BDROP COLUMN

CDELETE COLUMN

DERASE COLUMN

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'REMOVE COLUMN' which is not valid SQL.

Using 'DELETE COLUMN' which is incorrect syntax.

The correct syntax to remove a column is 'DROP COLUMN'. Other options are not valid SQL commands.

4fill in blank

hard

Fill both blanks to rename the table 'orders' to 'customer_orders'.

SQL

ALTER TABLE [1] [2] customer_orders;

Drag options to blanks, or click blank then click option'

Aorders

BRENAME TO

CCHANGE TO

Dorder_list

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'CHANGE TO' which is not standard SQL for renaming tables.

Swapping the order of table names.

To rename a table, use 'ALTER TABLE old_name RENAME TO new_name;'. Here, 'orders' is the old name and 'RENAME TO' is the command.

5fill in blank

hard

Fill all three blanks to change the data type of the 'price' column in 'products' table to DECIMAL(10,2).

SQL

ALTER TABLE [1] [2] price [3] DECIMAL(10,2);

Drag options to blanks, or click blank then click option'

Aproducts

BMODIFY COLUMN

CSET DATA TYPE

DCHANGE COLUMN

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'MODIFY COLUMN' without 'SET DATA TYPE' which may not work in all SQL dialects.

Confusing 'DROP COLUMN' with changing data type.

The correct syntax to change a column's data type is 'ALTER TABLE table_name CHANGE COLUMN column_name new_column_definition;'. Here, 'products' is the table, 'CHANGE COLUMN' is the command, and 'MODIFY COLUMN' is not used in this syntax. The third blank should be the new column name or omitted depending on SQL dialect, but 'SET DATA TYPE' is not standard. The original answer had an incorrect third blank.

Practice

1. What does the SQL command DROP TABLE do?

easy

A. Adds a new column to the table.

B. Changes the data type of a column.

C. Deletes the entire table and all its data permanently.

D. Renames the table without deleting data.

Solution

1. Step 1: Understand the purpose of DROP TABLE

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

2. Step 2: Compare with other commands

   ALTER TABLE changes structure but keeps data; DROP TABLE deletes everything.

3. Final Answer:

   Deletes the entire table and all its data permanently. -> Option C

4. Quick Check:

   DROP TABLE = Delete table and data [OK]

Hint: DROP TABLE removes table and data completely [OK]

Common Mistakes:

• Thinking DROP TABLE only deletes data but keeps structure
• Confusing DROP TABLE with ALTER TABLE
• Assuming DROP TABLE can be undone easily

2. Which of the following is the correct syntax to add a new column named age of type INTEGER to a table called users?

easy

A. DROP TABLE users ADD COLUMN age INTEGER;

B. ALTER TABLE users ADD COLUMN age INTEGER;

C. ALTER TABLE users DROP COLUMN age INTEGER;

D. ALTER users ADD age INTEGER;

Solution

1. Step 1: Identify correct ALTER TABLE syntax

   To add a column, use: ALTER TABLE table_name ADD COLUMN column_name data_type;

2. Step 2: Check each option

   ALTER TABLE users ADD COLUMN age INTEGER; matches correct syntax; others misuse DROP or omit TABLE keyword.

3. Final Answer:

   ALTER TABLE users ADD COLUMN age INTEGER; -> Option B

4. Quick Check:

   ALTER TABLE ADD COLUMN = ALTER TABLE users ADD COLUMN age INTEGER; [OK]

Hint: Use ALTER TABLE ... ADD COLUMN to add columns [OK]

Common Mistakes:

• Using DROP TABLE instead of ALTER TABLE
• Omitting TABLE keyword after ALTER
• Using ADD without COLUMN keyword

3. Given the table employees with columns id, name, and salary, what will be the result of this SQL command?

ALTER TABLE employees DROP COLUMN salary;

medium

A. The table employees will be deleted.

B. All data in the employees table will be deleted.

C. A new column named salary will be added.

D. The salary column will be removed from the employees table.

Solution

1. Step 1: Understand ALTER TABLE DROP COLUMN

   This command removes the specified column from the table structure but keeps the table and other data intact.

2. Step 2: Analyze options

   Only The salary column will be removed from the employees table. correctly describes removing the salary column; others describe deleting data or table, or adding columns.

3. Final Answer:

   The salary column will be removed from the employees table. -> Option D

4. Quick Check:

   ALTER TABLE DROP COLUMN removes column [OK]

Hint: ALTER TABLE DROP COLUMN removes only that column [OK]

Common Mistakes:

• Thinking DROP COLUMN deletes all data
• Confusing DROP COLUMN with DROP TABLE
• Assuming DROP COLUMN adds a column

4. What is wrong with this SQL command?

ALTER TABLE customers DROP age;

medium

A. The command is missing the keyword COLUMN after DROP.

B. DROP cannot be used with ALTER TABLE.

C. The table name is incorrect.

D. The command should be DROP TABLE customers;

Solution

1. Step 1: Check ALTER TABLE DROP syntax

   To remove a column, the syntax requires DROP COLUMN column_name, not just DROP column_name.

2. Step 2: Validate other options

   DROP is valid with ALTER TABLE if used correctly; table name is correct; DROP TABLE deletes whole table, not column.

3. Final Answer:

   The command is missing the keyword COLUMN after DROP. -> Option A

4. Quick Check:

   ALTER TABLE DROP COLUMN required [OK]

Hint: Use DROP COLUMN, not just DROP, to remove columns [OK]

Common Mistakes:

• Omitting COLUMN keyword after DROP
• Confusing DROP TABLE with DROP COLUMN
• Assuming DROP alone removes columns

5. You want to rename the column fullname to name in the table students. Which SQL command correctly does this in a standard way?

hard

A. ALTER TABLE students RENAME COLUMN fullname TO name;

B. ALTER TABLE students CHANGE fullname name VARCHAR(100);

C. DROP TABLE students RENAME COLUMN fullname TO name;

D. ALTER TABLE students DROP COLUMN fullname ADD COLUMN name VARCHAR(100);

Solution

1. Step 1: Identify standard rename column syntax

   The standard SQL command to rename a column is ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

2. Step 2: Evaluate options

   ALTER TABLE students RENAME COLUMN fullname TO name; matches standard syntax; A wrongly uses DROP TABLE; B is MySQL-specific CHANGE syntax; D drops and adds column losing data.

3. Final Answer:

   ALTER TABLE students RENAME COLUMN fullname TO name; -> Option A

4. Quick Check:

   Rename column = ALTER TABLE RENAME COLUMN [OK]

Hint: Use ALTER TABLE RENAME COLUMN to rename columns safely [OK]

Common Mistakes:

• Using DROP TABLE instead of ALTER TABLE
• Dropping and adding columns loses data
• Using non-standard syntax without checking compatibility