Data Definition and Control (DDL + DCL + TCL) Interview Questions - SQL

Data Definition and Control (DDL, DCL, TCL) in SQL deals with structuring, securing, and managing transactions in a database. DDL defines objects (CREATE, ALTER, DROP), DCL manages access (GRANT, REVOKE), and TCL ensures transaction integrity (COMMIT, ROLLBACK, SAVEPOINT). Together, they control how data is organized, secured, and consistently maintained.

1. What are the main types of SQL commands?

SQL commands are broadly classified into:

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE.
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE.
• DCL (Data Control Language): GRANT, REVOKE.
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT.

2. What is a primary key?

A primary key is a column (or set of columns) in a table that uniquely identifies each row. It cannot have duplicate or NULL values, ensuring every record is distinct. Example: student_id in a Students table.

3. What is a foreign key?

A foreign key is a column (or set of columns) in one table that references the primary key of another table. It is used to maintain relationships between tables and ensure referential integrity. Example: dept_id in the Employees table referencing dept_id in the Departments table.

4. What is the purpose of the DEFAULT constraint?

The DEFAULT constraint assigns a default value to a column when no value is provided during an INSERT operation. This helps maintain consistent data and simplifies data entry.

5. What is the purpose of the UNIQUE constraint?

The UNIQUE constraint ensures that all values in a column (or a group of columns) are distinct and no duplicates are allowed. Unlike a primary key, a table can have multiple UNIQUE constraints, and they can accept NULL values (but only once per column).

6. What are the types of constraints in SQL?

Common constraints include:

• NOT NULL: Ensures a column cannot have NULL values.
• UNIQUE: Ensures all values in a column are distinct.
• PRIMARY KEY: Uniquely identifies each row in a table.
• FOREIGN KEY: Ensures referential integrity by linking to a primary key in another table.
• CHECK: Ensures that all values in a column satisfy a specific condition.
• DEFAULT: Sets a default value for a column when no value is specified.

7. What is a trigger in SQL?

A trigger is a set of SQL statements that automatically execute in response to certain events on a table, such as INSERT, UPDATE, or DELETE. Triggers help maintain data consistency, enforce business rules, and implement complex integrity constraints.

8. What is a stored procedure?

A stored procedure is a precompiled set of SQL statements stored in the database. It can take input parameters, perform logic and queries, and return output values or result sets. Stored procedures improve performance and maintainability by centralizing business logic.

9. What is the difference between DDL and DML commands?

1. DDL (Data Definition Language):

These commands are used to define and modify the structure of database objects such as tables, indexes, and views. For example, the CREATE command creates a new table, the ALTER command modifies an existing table, and the DROP command removes a table entirely. DDL commands primarily focus on the schema or structure of the database.

Example:

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

2. DML (Data Manipulation Language):

These commands deal with the actual data stored within database objects. For instance, the INSERT command adds rows of data to a table, the UPDATE command modifies existing data, and the DELETE command removes rows from a table. In short, DML commands allow you to query and manipulate the data itself rather than the structure.

Example:

INSERT INTO Employees (ID, Name) VALUES (1, 'Alice');

10. What is the purpose of the ALTER command in SQL?

The ALTER command is used to modify the structure of an existing database object. This command is essential for adapting our database schema as requirements evolve.

• Add or drop a column in a table.
• Change a column’s data type.
• Add or remove constraints.
• Rename columns or tables.
• Adjust indexing or storage settings.

11. What is a composite primary key?

A composite primary key is a primary key made up of two or more columns. Together, these columns must form a unique combination for each row in the table. It’s used when a single column isn’t sufficient to uniquely identify a record.

Example:

Consider an Orders table where OrderID and ProductID together uniquely identify each record because multiple orders might include the same product, but not within the same order.

CREATE TABLE OrderDetails (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID)
);

12. What is the difference between clustered and non-clustered indexes?

1. Clustered Index:

• Organizes the physical data in the table itself in the order of the indexed column(s).
• A table can have only one clustered index.
• Improves range queries and queries that sort data.
• Example: If EmployeeID is the clustered index, the rows in the table are stored physically sorted by EmployeeID.

2. Non-Clustered Index:

• Maintains a separate structure that contains a reference (or pointer) to the physical data in the table.
• A table can have multiple non-clustered indexes.
• Useful for specific query conditions that aren’t related to the primary ordering of the data.
• Example: A non-clustered index on LastName allows fast lookups by last name even if the table is sorted by another column.

13. What are temporary tables, and how are they used?

Temporary tables are tables that exist only for the duration of a session or a transaction. They are useful for storing intermediate results, simplifying complex queries, or performing operations on subsets of data without modifying the main tables.

1. Local Temporary Tables:

• Prefixed with # (e.g., #TempTable).
• Only visible to the session that created them.
• Automatically dropped when the session ends.

2. Global Temporary Tables:

• Prefixed with ## (e.g., ##GlobalTempTable).
• Visible to all sessions.
• Dropped when all sessions that reference them are closed.

Example:

CREATE TABLE #TempResults (ID INT, Value VARCHAR(50));
INSERT INTO #TempResults VALUES (1, 'Test');
SELECT * FROM #TempResults;

14. What is a materialized view, and how does it differ from a standard view?

1. Standard View:

• A virtual table defined by a query.
• Does not store data; the underlying query is executed each time the view is referenced.
• A standard view shows real-time data.

2. Materialized View:

• A physical table that stores the result of the query.
• Data is precomputed and stored, making reads faster.
• Requires periodic refreshes to keep data up to date.
• Materialized view is used to store aggregated sales data, updated nightly, for fast reporting.

15. What is a sequence in SQL?

A sequence is a database object that generates a series of unique numeric values. It’s often used to produce unique identifiers for primary keys or other columns requiring sequential values.

Example:

CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1;
SELECT NEXT VALUE FOR seq_emp_id; -- Returns 1
SELECT NEXT VALUE FOR seq_emp_id; -- Returns 2

16. What are the advantages of using sequences over identity columns?

1. Greater Flexibility:

• Can specify start values, increments, and maximum values.
• Can be easily reused for multiple tables.

2. Dynamic Adjustment: Can alter the sequence without modifying the table structure.

3. Cross-Table Consistency: Use a single sequence for multiple related tables to ensure unique identifiers across them.
In short, sequences offer more control and reusability than identity columns.

17. How do constraints improve database integrity?

Constraints enforce rules that the data must follow, preventing invalid or inconsistent data from being entered:

• NOT NULL: Ensures that a column cannot contain NULL values.
• UNIQUE: Ensures that all values in a column are distinct.
• PRIMARY KEY: Combines NOT NULL and UNIQUE, guaranteeing that each row is uniquely identifiable.
• FOREIGN KEY: Ensures referential integrity by requiring values in one table to match primary key values in another.
• CHECK: Validates that values meet specific criteria (e.g., CHECK (Salary > 0)).
  By automatically enforcing these rules, constraints maintain data reliability and consistency.