Section A — 1 Mark Questions
1. What does DDL stand for in SQL? [1 Mark]
Answer: DDL stands for Data Definition Language.
2. Which command is used to create a new database table? [1 Mark]
Answer: The CREATE command is used.
3. Name a standard SQL numeric datatype. [1 Mark]
Answer: INTEGER or DECIMAL.
4. What is the purpose of the DROP command? [1 Mark]
Answer: It is used to delete an entire table or database structure from the schema.
5. Which SQL command is used to modify the structure of an existing table? [1 Mark]
Answer: The ALTER command.
6. Define a Schema. [1 Mark]
Answer: A schema is a structural container that defines the organization of data in a database.
7. What is the purpose of the TRUNCATE command? [1 Mark]
Answer: It is used to remove all records from a table while keeping the structure intact.
8. Which datatype is used to store variable-length strings? [1 Mark]
Answer: VARCHAR.
9. What does the RENAME command do? [1 Mark]
Answer: It changes the name of an existing database object, such as a table.
10. Identify the SQL command used to remove all data and reset identity counters. [1 Mark]
Answer: TRUNCATE.
11. Which SQL datatype represents true or false values? [1 Mark]
Answer: BOOLEAN.
12. Does DDL modify the data or the structure? [1 Mark]
Answer: DDL modifies the structure.
13. Which command is a shortcut to delete a table and recreate it? [1 Mark]
Answer: TRUNCATE.
14. Is DATE a valid SQL datatype? [1 Mark]
Answer: Yes.
15. What keyword follows ALTER TABLE to add a column? [1 Mark]
Answer: ADD.
16. What is the main characteristic of the DROP command? [1 Mark]
Answer: It removes the table definition entirely from the database.
17. Which SQL datatype is used for fixed-length character strings? [1 Mark]
Answer: CHAR.
18. What happens to indexes when a table is dropped? [1 Mark]
Answer: They are removed along with the table.
19. Can the RENAME command modify table columns? [1 Mark]
Answer: In most SQL implementations, RENAME targets the table name itself.
20. What is the primary role of Data Definition Language? [1 Mark]
Answer: To define and manage the database schema and structure.
Section B — 2/3 Mark Questions
1. Distinguish between DROP and TRUNCATE. [2 Marks]
Answer: DROP removes the entire table structure and data, whereas TRUNCATE removes only the data, leaving the table structure intact for future use.
2. Why is the CHAR datatype used? [2 Marks]
Answer: CHAR is used for fixed-length character strings. It is beneficial when the data length is consistent, as it allocates space regardless of the actual content length.
3. What is the function of the ALTER command in SQL? [2 Marks]
Answer: The ALTER command is used to modify the structure of an existing database object, such as adding a new column to a table, modifying a column's datatype, or dropping a column.
4. Explain the role of the CREATE command. [2 Marks]
Answer: The CREATE command is the foundational DDL command used to define new database objects such as tables, schemas, or indexes. It establishes the initial schema requirements and constraints.
5. List two common SQL datatypes and their usage. [2 Marks]
Answer: 1. INTEGER: Used for whole numbers. 2. VARCHAR: Used for variable-length strings where the length may change per entry.
6. Define the schema of a database. [3 Marks]
Answer: A database schema is the skeleton structure that represents the logical view of the entire database. It defines how data is organized, including table names, columns, datatypes, and the relationships between various tables.
7. How does the RENAME command function? [3 Marks]
Answer: The RENAME command is used to update the identifier of an existing object within the database. It is a critical utility for schema maintenance, ensuring that naming conventions remain consistent without needing to delete and recreate the object.
8. What are the limitations of the TRUNCATE command? [3 Marks]
Answer: TRUNCATE cannot be used on a table that is referenced by a FOREIGN KEY constraint. Additionally, it cannot be rolled back in many database systems and does not trigger individual row-level DELETE operations.
9. Why must one define datatypes while creating a table? [3 Marks]
Answer: Defining datatypes is essential for 1) Storage optimization (allocating appropriate space), 2) Data integrity (ensuring only valid data types are stored), and 3) Efficient query processing and mathematical operations.
10. When should you use VARCHAR instead of CHAR? [3 Marks]
Answer: You should use VARCHAR when storing text data that varies significantly in length, as it only consumes space based on the actual characters entered, thereby saving storage space compared to the fixed-size CHAR.
Section C — 5 Mark Questions
1. Explain the concept and importance of Data Definition Language (DDL) in a database system. [5 Marks]
Answer:
Introduction
Data Definition Language (DDL) is a subset of SQL used to define, modify, and delete the structure (schema) of database objects.
Importance
- Structure Management: It provides the tools to build the framework where data will live.
- Data Integrity: By defining column types and constraints during the creation phase, DDL helps ensure data consistency.
- Schema Maintenance: DDL allows for evolution; as business requirements change, tables can be altered or renamed without losing the core design.
- Access Control: Through schema definitions, administrators can control the layout and accessibility of database objects.
- Object Lifecycle: DDL manages the entire lifecycle of objects, from creation via CREATE to permanent removal via DROP.
2. Discuss the syntax and application of the CREATE and ALTER commands with examples. [5 Marks]
Answer:
CREATE Command
The CREATE command defines the table structure. Example: CREATE TABLE Students (ID INTEGER, Name VARCHAR(50)); This sets up a table named Students with two columns.
ALTER Command
The ALTER command modifies an existing table structure. It can add, change, or remove columns.
Application
- Adding a column: ALTER TABLE Students ADD Age INTEGER;
- Modifying a column type: ALTER TABLE Students MODIFY Name VARCHAR(100);
- Dropping a column: ALTER TABLE Students DROP COLUMN Age;
3. Compare the functional differences between DROP, TRUNCATE, and DELETE (in the context of structure). [5 Marks]
Answer:
| Feature | DROP | TRUNCATE |
|---|---|---|
| Structure | Removed | Retained |
| Data | Removed | Removed |
| Speed | Fast | Fast |
| Rollback | Not usually | Not usually |
Key Points
- DROP: Destroys the table completely. It is like deleting a file from a hard drive.
- TRUNCATE: Empties the table of all rows. The container (the table) remains empty and ready for new data.
- Use Case: Use DROP when the table is no longer needed. Use TRUNCATE when you need to reset a table completely but keep its columns and constraints for future use.
4. Describe the importance of choosing correct datatypes during the creation of a table. [5 Marks]
Answer:
Significance
Choosing the correct datatype is the first step in effective database design. It affects performance, storage, and reliability.
Key Considerations
- Storage Efficiency: Using an INTEGER for a small number instead of a large decimal saves memory.
- Data Accuracy: Using a DATE datatype ensures that invalid inputs like '30/13/2023' are rejected by the system.
- Query Performance: Databases can index and compare numeric datatypes much faster than unstructured text fields.
- Functional Constraints: Certain operations (like mathematical calculations) can only be performed on specific datatypes.
- Flexibility: Using types like VARCHAR instead of CHAR prevents unnecessary padding with empty spaces, which optimizes read operations.
5. Explain the process of modifying a database schema using DDL commands. [5 Marks]
Answer:
Process Overview
Modifying a schema is a structured process to ensure the database meets changing requirements.
Steps
- Requirement Analysis: Determine what structural change is needed (e.g., adding a new field for 'Email Address').
- Safety Assessment: Check if the change will affect existing applications or relationships.
- Execution (ALTER): Use the ALTER TABLE command to inject the change into the existing schema.
- Verification: Use database utilities to confirm the new column exists and is correctly typed.
- Backup: Always ensure a backup exists before running DDL commands like DROP or TRUNCATE to avoid irreversible data loss.
By following these steps, administrators can maintain high database performance and integrity.