SQL - Structured Query Language Data Definition Language (DDL) Questions and Answers 1 — Questions and Answers
Question 1: Which of the following SQL statements is NOT considered a Data Definition Language (DDL) command?
- UPDATE (Correct answer)
- CREATE
- ALTER
- DROP
Correct answer: UPDATE
DDL commands are used to define and manage the structure of database objects. CREATE, ALTER, and DROP are all DDL commands used to create, modify, and delete tables respectively. UPDATE is a Data Manipulation Language (DML) command used to modify existing records within a table.
Question 2: A database administrator needs to remove all rows from a large table named `LogData` but wants to keep the table structure for future use. Which DDL command is the most efficient for this task?
- DELETE FROM LogData;
- DROP TABLE LogData;
- TRUNCATE TABLE LogData; (Correct answer)
- ALTER TABLE LogData EMPTY;
Correct answer: TRUNCATE TABLE LogData;
The TRUNCATE TABLE command is designed to quickly delete all rows from a table. It is more efficient than DELETE because it deallocates the data pages without logging each individual row deletion. DROP TABLE would remove the entire table structure, which is not the desired outcome.
Question 3: You are tasked with adding a new column named `Email` with a data type of `VARCHAR(255)` to an existing table called `Employees`. Which is the correct DDL syntax to accomplish this?
- ADD COLUMN Email VARCHAR(255) TO Employees;
- CREATE COLUMN Email VARCHAR(255) IN Employees;
- UPDATE TABLE Employees ADD Email VARCHAR(255);
- ALTER TABLE Employees ADD Email VARCHAR(255); (Correct answer)
Correct answer: ALTER TABLE Employees ADD Email VARCHAR(255);
The `ALTER TABLE` statement is used to modify the structure of an existing table. The `ADD` clause is used specifically to add a new column, followed by the column name and its data type.
Question 4: Which DDL statement is used to define a new table, including its columns, data types, and constraints?
- INSERT TABLE
- CREATE TABLE (Correct answer)
- DEFINE TABLE
- NEW TABLE
Correct answer: CREATE TABLE
The `CREATE TABLE` statement is the fundamental DDL command used to create a new table in a database. Its syntax allows for the definition of the table's name, the names of its columns, the data type for each column, and any applicable constraints like PRIMARY KEY or NOT NULL.
Question 5: A developer wants to change the name of an existing table from `tbl_Users` to `Users`. Which of the following commands should be used?
- UPDATE TABLE tbl_Users SET NAME = Users;
- MODIFY TABLE tbl_Users RENAME TO Users;
- RENAME TABLE tbl_Users TO Users; (Correct answer)
- CREATE ALIAS Users FOR tbl_Users;
Correct answer: RENAME TABLE tbl_Users TO Users;
The `RENAME TABLE` command is the standard DDL statement used to change the name of an existing table. While some database systems might use a variation like `ALTER TABLE ... RENAME TO`, `RENAME TABLE` is also a common and direct syntax.
Question 6: What is the primary difference between the `DROP TABLE` and `TRUNCATE TABLE` commands?
- DROP TABLE can be rolled back, while TRUNCATE TABLE cannot.
- DROP TABLE removes only the data, while TRUNCATE TABLE removes the data and structure.
- DROP TABLE removes the entire table structure and its data, while TRUNCATE TABLE only removes the data. (Correct answer)
- TRUNCATE TABLE is a DML command, while DROP TABLE is a DDL command.
Correct answer: DROP TABLE removes the entire table structure and its data, while TRUNCATE TABLE only removes the data.
`DROP TABLE` is a DDL command that permanently removes the entire table, including its structure, data, indexes, and constraints. `TRUNCATE TABLE` is also a DDL command, but it only removes all the data rows from the table, leaving the table structure intact for future use.
