Data Definition Language (DDL): A Comprehensive Guide for Database Management

Question 1

What are the primary consequences of executing the DROP TABLE statement?

Accepted Answer

Permanent data loss and potential violation of referential integrity constraints.

Answer

Temporary data removal with the option to restore using an UNDROP statement.

Answer

Removal of table metadata only, leaving data intact.

Answer

No impact on data integrity or database structure.

Question 2

How do indexes primarily enhance database performance?

Accepted Answer

Provide a direct path to specific rows based on column values, reducing table scans.

Answer

Increase database size, potentially slowing down queries.

Answer

Enforce constraints and prevent data corruption.

Answer

Are only beneficial for small databases.

Question 3

Which DDL statement creates a new table in a database?

Accepted Answer

CREATE TABLE

Answer

ALTER TABLE

Answer

DROP TABLE

Answer

SELECT TABLE

Question 4

What data type is suitable for storing variable-length character strings in a database?

Accepted Answer

VARCHAR(n)

Answer

CHAR(n)

Answer

TEXT

Answer

BLOB

Question 5

What is the primary purpose of a PRIMARY KEY constraint in a database table?

Accepted Answer

To guarantee that each row has a unique identifier

Answer

To prevent duplicate rows from being inserted

Answer

To establish relationships between tables

Answer

To group rows together for more efficient retrieval

Question 6

Which of the following statements is used to modify the structure of an existing table?

Accepted Answer

ALTER TABLE

Answer

UPDATE TABLE

Answer

ADD TABLE

Answer

MODIFY TABLE

Question 7

In a database, what is the purpose of a VIEW?

Accepted Answer

It's a stored query that can be used like a regular table

Answer

It's a temporary table that is automatically deleted after use

Answer

It's a collection of indexes that enhance query execution speed

Answer

It's a mechanism for implementing data security measures

Question 8

What distinguishes a clustered index from a non-clustered index?

Accepted Answer

Clustered indexes physically arrange data on disk, unlike non-clustered indexes

Answer

Non-clustered indexes logically organize data in memory, unlike clustered indexes

Answer

Clustered indexes can only be created on primary keys, while non-clustered indexes can be created on any column

Answer

Non-clustered indexes always perform faster than clustered indexes

Question 9

What is the main purpose of using transactions in a database?

Accepted Answer

To guarantee that all database modifications are either fully applied or completely canceled

Answer

To improve query performance

Answer

To provide a way to restrict data from being modified by others

Answer

To create a transient copy of the database for testing purposes

Question 10

What is a key advantage of using a relational database management system?

Accepted Answer

All of the given options

Answer

Preserving data integrity and consistency

Answer

Storing and retrieving data efficiently

Answer

Supporting complex queries

Question 11

What is the key difference between a logical data model and a physical data model?

Accepted Answer

A logical data model conceptualizes data in terms of its meaning and relationships, while a physical data model focuses on its actual storage

Answer

A physical data model conceptualizes data in terms of its meaning and relationships, while a logical data model focuses on its actual storage

Answer

There is no distinction between a logical data model and a physical data model

Answer

A logical data model is more intricate than a physical data model

Question 12

What is the primary purpose of the PRIMARY KEY constraint?

Accepted Answer

To ensure that each row has a unique value for the specified column(s)

Answer

To define the columns used for joining the table with other tables

Answer

To prevent NULL values from being inserted into the specified column(s)

Answer

To create a foreign key relationship with another table

Question 13

Which of the provided data types is used to store dates and times?

Accepted Answer

DATE

Answer

CHAR

Answer

INT

Answer

VARCHAR

Question 14

Which syntax is used to add a new column to an existing table?

Accepted Answer

ALTER TABLE ADD COLUMN (column_name data_type)

Answer

ADD (column_name data_type)

Answer

MODIFY TABLE ADD (column_name data_type)

Answer

UPDATE TABLE ADD COLUMN (column_name data_type)

Question 15

What benefit does the CHECK constraint provide?

Accepted Answer

Ensures data integrity by limiting the range of values that can be inserted into a column

Answer

Improves performance by using an index on the restricted column

Answer

Enforces referential integrity between tables

Answer

Prevents duplicate rows from being inserted into a table

Question 16

What is the key difference between a FOREIGN KEY constraint and a UNIQUE constraint?

Accepted Answer

FOREIGN KEY constraints uphold referential integrity, while UNIQUE constraints prevent duplicate values

Answer

UNIQUE constraints uphold referential integrity, while FOREIGN KEY constraints prevent duplicate values

Answer

FOREIGN KEY constraints prevent duplicate values, while UNIQUE constraints enforce referential integrity

Answer

No distinction exists between FOREIGN KEY and UNIQUE constraints

Question 17

What is the intended purpose of an INDEX?

Accepted Answer

To increase query efficiency by utilizing a data structure for quick row identification based on a specified column

Answer

To preserve data integrity by preventing duplicate values in a column

Answer

To establish a connection between two tables

Answer

To store data in a compressed format

Question 18

What is the purpose of the DEFAULT constraint?

Accepted Answer

To assign a default value to a column when no value is provided

Answer

To prevent NULL values from being inserted into a column

Answer

To create referential integrity between tables

Answer

To create an index on the specified column

Question 19

To create a new table in a database, which DDL statement should you use?

Accepted Answer

CREATE TABLE

Answer

ALTER TABLE

Answer

DROP TABLE

Answer

UPDATE TABLE

Question 20

What is the primary purpose of utilizing a PRIMARY KEY constraint in a database table?

Accepted Answer

To uniquely identify each row within the table.

Answer

To ensure referential integrity between tables.

Answer

To prevent duplicate values in a specific column.

Answer

To establish a default value for a column.

Question 21

Which DDL statement is appropriate for adding a new column to an existing table in a database?

Accepted Answer

ADD COLUMN

Answer

ALTER COLUMN

Answer

MODIFY COLUMN

Answer

UPDATE COLUMN

Question 22

Describe the distinction between a PRIMARY KEY and a FOREIGN KEY in a database context.

Accepted Answer

A FOREIGN KEY establishes a relationship with another table, while a PRIMARY KEY ensures uniqueness within its own table.

Answer

A PRIMARY KEY can accept null values, unlike a FOREIGN KEY.

Answer

A FOREIGN KEY can reference multiple columns, whereas a PRIMARY KEY is limited to a single column.

Answer

There is no fundamental difference between a PRIMARY KEY and a FOREIGN KEY.

Question 23

What is the main advantage of using an INDEX in a database?

Accepted Answer

Enhancing query performance by optimizing data retrieval.

Answer

Enforcing data integrity by ensuring unique values in a specific column.

Answer

Storing additional information about the data in the table.

Answer

Establishing relationships between multiple tables.

Question 24

Define a "trigger" in the context of a database.

Accepted Answer

A stored procedure that executes automatically when a specified event occurs.

Answer

A constraint that enforces a business rule on the data.

Answer

A temporary table used for storing intermediate results.

Answer

A view that combines data from multiple tables.

Question 25

Which practice is generally considered poor when designing a database schema?

Accepted Answer

Duplicating data across multiple tables.

Answer

Using clear and consistent naming conventions.

Answer

Normalizing the data to minimize redundancy.

Answer

Enforcing referential integrity.

Question 26

What is the primary purpose of creating a "VIEW" in a database?

Accepted Answer

To provide a logical representation of data from one or more underlying tables.

Answer

To create a temporary table for data manipulation.

Answer

To store pre-computed results for faster query execution.

Answer

To control data access permissions for specific users.

Question 27

Which DDL statement is used to create a new table called 'customers' with columns 'id' (INT), 'name' (VARCHAR(255)), and 'email' (VARCHAR(255)) in MySQL?

Accepted Answer

CREATE TABLE customers (id INT, name VARCHAR(255), email VARCHAR(255))

Answer

ADD TABLE customers (id INT, name VARCHAR(255), email VARCHAR(255))

Answer

ALTER TABLE customers (id INT, name VARCHAR(255), email VARCHAR(255))

Answer

DEFINE TABLE customers (id INT, name VARCHAR(255), email VARCHAR(255))

Question 28

What is the purpose of a PRIMARY KEY constraint on a column named 'id' in a table?

Accepted Answer

To ensure that each value in the 'id' column is unique, preventing duplicate entries.

Answer

To prevent null values from being inserted into the 'id' column.

Answer

To create an index on the 'id' column, speeding up data retrieval based on this column.

Answer

To establish a relationship between the 'id' column in this table and another column in a different table.

Question 29

What is the main purpose of creating an INDEX on a column in a database?

Accepted Answer

To significantly speed up data retrieval based on the indexed column.

Answer

To enforce data integrity by preventing duplicate values in the indexed column.

Answer

To group related data together, making it easier to manage.

Answer

To delete duplicate records from the table.

Question 30

Which DDL statement is used to modify an existing table named 'departments' by adding a new column called 'location' (VARCHAR(255)) in SQL Server?

Accepted Answer

ALTER TABLE departments ADD COLUMN location VARCHAR(255)

Answer

CHANGE TABLE departments ADD COLUMN location VARCHAR(255)

Answer

MODIFY TABLE departments ADD COLUMN location VARCHAR(255)

Answer

UPDATE TABLE departments ADD COLUMN location VARCHAR(255)

Question 31

What is the key difference between a view and a table in a database?

Accepted Answer

Views are virtual tables derived from one or more tables, while tables are physical storage structures that hold actual data.

Answer

Tables are virtual tables derived from one or more views, while views are physical storage structures that hold actual data.

Answer

Views and tables serve the same purpose and are interchangeable.

Answer

There is no difference between views and tables.

Question 32

What is the purpose of a CHECK constraint?

Accepted Answer

To restrict the range of values that can be inserted into a column based on a specified condition.

Answer

To prevent null values from being inserted into a column.

Answer

To enforce a relationship between two or more columns in different tables.

Answer

To create an index on a column.

Question 33

Which of the following is NOT a potential benefit of using DDL?

Accepted Answer

Degraded data processing performance.

Answer

Improved organization and management of data.

Answer

Reduced data redundancy.

Answer

Enhanced data security.

Question 34

What is the purpose of a DEFAULT constraint?

Accepted Answer

To specify a default value for a column when no value is explicitly provided during data insertion.

Answer

To prevent null values from being inserted into a column.

Answer

To limit the range of values that can be inserted into a column.

Answer

To create an index on a column.

Question 35

Which syntax is correct to create a table named 'Employees' with columns 'ID', 'Name', and 'Salary' ?

Accepted Answer

CREATE TABLE Employees (ID INT, Name VARCHAR(255), Salary DECIMAL(10, 2))

Answer

CREATE TABLE 'Employees' (ID INT, 'Name' VARCHAR(255), 'Salary' DECIMAL(10, 2))

Answer

CREATE TABLE Employees ('ID' INT, 'Name' VARCHAR(255), 'Salary' DECIMAL(10.2))

Answer

CREATE TABLE Employees (ID STRING, Name VARCHAR(255), Salary NUMERIC(10, 2))

Question 36

What is the primary purpose of the 'ALTER' statement in DDL ?

Accepted Answer

Modify existing database objects

Answer

Create new database objects

Answer

Delete database objects

Answer

Retrieve data from database objects

Question 37

Which data type is appropriate for an ID column ?

Accepted Answer

INT

Answer

VARCHAR

Answer

DATE

Answer

DOUBLE

Question 38

Explain the distinction between a primary key and a foreign key.

Accepted Answer

A primary key uniquely identifies each row in a table, while a foreign key references a primary key in another table

Answer

A foreign key uniquely identifies each row in a table, while a primary key references a foreign key in another table

Answer

Both primary keys and foreign keys uniquely identify rows in tables

Answer

There is no difference between a primary key and a foreign key

Question 39

Describe the difference between a clustered index and a non-clustered index.

Accepted Answer

A clustered index physically sorts the data in the table based on the indexed column, unlike a non-clustered index

Answer

A non-clustered index physically sorts the data in the table based on the indexed column, unlike a clustered index

Answer

There is no difference between a clustered index and a non-clustered index

Answer

A clustered index is created on the primary key of a table, while a non-clustered index is created on any other column

Question 40

What is the purpose of a commit operation within a transaction ?

Accepted Answer

Make database changes permanent

Answer

Undo changes made in the transaction

Answer

Save changes without making them permanent

Answer

Create a new transaction

Question 41

Describe the purpose of a foreign key constraint.

Accepted Answer

Ensuring values in one table correspond to existing values in another table

Answer

Preventing duplicate values from being inserted into a table

Answer

Creating a relationship between two tables

Answer

Improving query performance

Question 42

What is a potential disadvantage of using database views instead of base tables?

Accepted Answer

Increased maintenance

Answer

Data abstraction

Answer

Reduced maintenance

Answer

Improved performance

Question 43

Which of the following DDL statements is used to create a new table in a database?

Accepted Answer

CREATE TABLE

Answer

ALTER TABLE

Answer

DROP TABLE

Question 44

When defining a column in a relational database table, what is the purpose of applying a NOT NULL constraint?

Accepted Answer

To ensure that the column cannot contain missing values (NULL values)

Answer

To allow the column to contain missing values (NULL values)

Answer

To ensure that the column always contains a unique value

Answer

To prevent the column from being modified

Question 45

Which of the following is a key benefit of using a CHECK constraint in a database?

Accepted Answer

Ensures data integrity by preventing invalid data entry.

Answer

Enhances database performance by optimizing data retrieval.

Answer

Simplifies data manipulation by reducing the need for complex queries.

Question 46

What is the immediate consequence of executing the DROP TABLE statement in DDL?

Accepted Answer

Permanent deletion of the specified table and its data, potentially affecting referential integrity.

Answer

Temporary hiding of the specified table, preserving its data and referential integrity.

Answer

Creation of a new table with the same name and structure, copying its data.

Answer

Renaming of the specified table to a new name, maintaining its data and referential integrity.

Question 47

Which DDL statement is used explicitly to create a new table?

Accepted Answer

CREATE TABLE

Answer

ALTER TABLE

Answer

DROP TABLE

Answer

UPDATE TABLE

Question 48

What is the primary purpose of a PRIMARY KEY constraint?

Accepted Answer

To ensure that each row in a table has a unique identifier

Answer

To enforce data integrity by preventing duplicate values solely

Answer

To create a relationship between two tables exclusively

Answer

To optimize query performance as the sole purpose

Question 49

Which data type is most appropriate for storing dates and times in a database?

Accepted Answer

DATETIME

Answer

INT

Answer

VARCHAR

Answer

BOOLEAN

Question 50

Which DDL statement is used to remove an existing index from a table?

Accepted Answer

DROP INDEX

Answer

CREATE INDEX

Answer

ALTER INDEX

Answer

UPDATE INDEX

Question 51

What is the purpose of a FOREIGN KEY constraint?

Accepted Answer

To enforce referential integrity by ensuring that child records exist in the parent table

Answer

To create a one-to-many relationship between two tables solely

Answer

To prevent duplicate values in a table exclusively

Answer

To optimize query performance as the sole purpose

Question 52

Which data type allows for the storage of large amounts of unstructured text data?

Accepted Answer

TEXT

Answer

CHAR

Answer

VARCHAR

Answer

BLOB

Question 53

What is the primary purpose of a CHECK constraint?

Accepted Answer

To ensure that data in a column meets a specific condition or set of conditions

Answer

To create a relationship between two tables exclusively

Answer

To prevent duplicate values in a table solely

Answer

To optimize query performance as the sole purpose

Question 54

Which DDL statement is used to modify the structure of an existing table?

Accepted Answer

ALTER

Answer

CREATE

Answer

DROP

Answer

UPDATE

Question 55

Which DDL statement is used to create a new table?

Accepted Answer

CREATE TABLE

Answer

ALTER TABLE

Answer

DROP TABLE

Answer

INDEX TABLE

Question 56

What is the purpose of a PRIMARY KEY constraint?

Accepted Answer

Uniquely identifies each row in a table

Answer

Enforces a not null constraint on a column

Answer

Establishes a foreign key relationship with another table

Answer

Indexes a column for faster data retrieval

Question 57

What is the correct syntax to add a new column to an existing table using DDL?

Accepted Answer

ALTER TABLE ADD COLUMN

Answer

ADD COLUMN <column_name> <data_type>

Answer

MODIFY COLUMN

Answer

UPDATE TABLE SET =

Question 58

What is a benefit of using a FOREIGN KEY constraint?

Accepted Answer

Ensures data integrity by preserving relationships between tables

Answer

Improves performance by creating an index on the foreign key column

Answer

Simplifies data retrieval by generating virtual columns

Answer

Prevents duplicate data in multiple tables

Question 59

What is the key difference between a view and a materialized view?

Accepted Answer

Data in a materialized view is automatically updated, while a view is manually updated

Answer

A view is stored in the database, while a materialized view is stored in memory

Answer

A materialized view is a subset of a table, while a view combines data from multiple tables

Answer

There is no difference between a view and a materialized view

Question 60

What is the purpose of a CHECK constraint?

Accepted Answer

Ensures that data in a column meets specified conditions

Answer

Creates a unique index on a column

Answer

Prevents null values from being inserted into a column

Answer

Establishes a relationship between two tables

Question 61

Which of the following is not a key aspect of a well-designed database schema?

Accepted Answer

Redundancy

Answer

Normalization

Answer

Consistency

Answer

Efficiency

Question 62

Which DDL statement is used to create a database?

Accepted Answer

CREATE DATABASE

Answer

ALTER DATABASE

Answer

DROP DATABASE

Answer

UPDATE DATABASE

Question 63

Which DDL statement is used to modify an existing table?

Accepted Answer

ALTER TABLE

Answer

CREATE TABLE

Answer

DROP TABLE

Answer

UPDATE TABLE

Question 64

What is the purpose of a foreign key constraint?

Accepted Answer

To ensure data consistency between related tables

Answer

To create a unique index on a column

Answer

To define the default value for a column

Answer

To specify the data type of a column