SQL Interview Preparation Q&A | Part-2

11. What is a primary key in SQL? Can a table have multiple primary keys?

Answer: A primary key is a unique identifier for each row in a table. It ensures:

• Uniqueness: No two rows have the same value in the primary key column(s).
• Not Null: Primary key columns cannot contain null values.

Can a table have multiple primary keys? No, a table can have only one primary key. However, the primary key can consist of multiple columns (composite key).

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

-- Composite key example
CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID)
); (code-box)

Tips: Emphasize the role of primary keys in maintaining data integrity. Be ready to explain composite keys during interviews.

12. What is a foreign key, and how is it used?

Answer: A foreign key establishes a relationship between two tables. It ensures that the value in a column corresponds to a value in the primary key of another table.

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50)
);

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseName VARCHAR(50),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
); (code-box)

Tips: Highlight the importance of foreign keys in maintaining referential integrity. Explain real-world scenarios like linking customers to their orders.

13. What is a subquery, and what are its types?

Answer: A subquery is a query nested within another query. Types of subqueries:

• Single-row subquery: Returns one row.
• Multi-row subquery: Returns multiple rows.
• Correlated subquery: Depends on the outer query.

Example:

SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees); (code-box)

Tips: Be ready to differentiate between correlated and non-correlated subqueries.

14. Explain the difference between `UNION` and `UNION ALL`.

Answer:

• `UNION`: Combines results from two queries and removes duplicates.
• `UNION ALL`: Combines results and includes duplicates.

Example:

-- Using UNION
SELECT Name FROM DepartmentA
UNION
SELECT Name FROM DepartmentB;

-- Using UNION ALL
SELECT Name FROM DepartmentA
UNION ALL
SELECT Name FROM DepartmentB; (code-box)

Tips: Discuss use cases for each. Mention that `UNION` involves more processing due to duplicate removal.

15. What are aggregate functions in SQL? Provide examples.

Answer: Aggregate functions perform calculations on multiple rows and return a single value. Common functions:

• `COUNT()`: Counts rows.
• `SUM()`: Calculates total.
• `AVG()`: Calculates average.
• `MIN()` and `MAX()`: Return smallest and largest values, respectively.

Example:

SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department; (code-box)

Tips: Highlight the importance of `GROUP BY` with aggregate functions.

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

Answer:

• Clustered Index: Physically sorts data in the table. A table can have only one clustered index.
• Non-clustered Index: Creates a logical order for data but does not affect physical storage. Multiple non-clustered indexes can exist.

Example:

-- Clustered index
CREATE CLUSTERED INDEX idx_salary ON Employees(Salary);

-- Non-clustered index
CREATE NONCLUSTERED INDEX idx_name ON Employees(Name); (code-box)

Tips: Explain performance trade-offs and when to use each type.

17. What are stored procedures? How do they differ from functions?

Answer: A stored procedure is a precompiled SQL code block executed on the server.

Differences from functions:

• Procedures: Can perform both DML and DDL operations, do not necessarily return a value.
• Functions: Must return a value and cannot modify database state.

Example:

-- Stored procedure example
CREATE PROCEDURE GetEmployeeDetails
AS
BEGIN
    SELECT * FROM Employees;
END;

-- Function example
CREATE FUNCTION GetTotalEmployees()
RETURNS INT
AS
BEGIN
    RETURN (SELECT COUNT(*) FROM Employees);
END; (code-box)

Tips: Discuss scenarios where stored procedures enhance modularity.

18. What is a view in SQL, and how is it different from a table?

Answer: A view is a virtual table based on the result of an SQL query. Unlike tables, views do not store data; they retrieve it dynamically.

Example:

CREATE VIEW HighSalaryEmployees AS
SELECT Name, Salary
FROM Employees
WHERE Salary > 70000;

-- Using the view
SELECT * FROM HighSalaryEmployees; (code-box)

Tips: Mention that views simplify complex queries and can enhance security by restricting data access.

19. What is the difference between `DELETE` and `DROP`?

Answer:

• `DELETE`: Removes rows from a table but retains the table structure.
• `DROP`: Deletes the entire table, including its structure.

Example:

-- DELETE example
DELETE FROM Employees WHERE DepartmentID = 2;

-- DROP example
DROP TABLE Employees; (code-box)

Tips: Highlight the irreversibility of `DROP`.

20. Explain transactions in SQL and how they are managed.

Answer: A transaction is a sequence of operations treated as a single unit. It is managed using:

• `BEGIN TRANSACTION`: Starts a transaction.
• `COMMIT`: Saves changes.
• `ROLLBACK`: Reverts changes.

Example:

BEGIN TRANSACTION;

UPDATE Employees SET Salary = Salary + 1000 WHERE DepartmentID = 2;

COMMIT; -- Or ROLLBACK if an error occurs (code-box)

Tips: Discuss the importance of transactions in ensuring data consistency.