Top 25+ SQL Interview Q&A with Practical Test | Try Personalized AI Help

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The purpose of the foreign key is to ensure referential integrity of the data, meaning that the foreign key values in the referencing table must match primary key values in the referenced table.

Uses:

• Data Integrity: Ensures that relationships between tables remain consistent. If a record in the parent table (referenced table) is deleted, the corresponding records in the child table (referencing table) can either be deleted or set to NULL, depending on the referential action specified.
• Joining Tables: Facilitates joining tables in queries to retrieve related data across multiple tables.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

These commands are used to remove data or database objects but differ in their operations and consequences:

• DELETE:
• Usage: Deletes rows from a table based on a specified condition.
• Transaction: DML command that can be rolled back if used within a transaction.
• Performance: Slower than TRUNCATE because it logs individual row deletions.

DELETE FROM table_name WHERE condition;

• TRUNCATE:
• Usage: Removes all rows from a table but retains the table structure for future use.
• Transaction: DDL command that cannot be rolled back in most systems.
• Performance: Faster than DELETE as it deallocates data pages instead of logging individual row deletions.

TRUNCATE TABLE table_name;

• DROP:
• Usage: Deletes the entire table, including its structure, from the database.
• Transaction: DDL command that cannot be rolled back in most systems.
• Performance: Irreversible and completely removes the table and its data.

DROP TABLE table_name;

Stored procedures are precompiled collections of one or more SQL statements stored under a name and processed as a unit. They can accept parameters, perform operations, and return results.

Benefits:

• Performance: Precompiled and cached, which reduces the parsing time for queries.
• Security: Can encapsulate business logic, providing controlled access to data.
• Reusability: Can be reused across different applications and users, promoting code reuse.
• Maintainability: Easier to maintain since changes can be made in one place without altering multiple queries.

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

ACID properties ensure reliable processing of database transactions and maintain data integrity even in the event of failures. The properties are:

• Atomicity: Ensures that all operations within a transaction are completed successfully. If any operation fails, the entire transaction is rolled back.
• Consistency: Ensures that a transaction takes the database from one valid state to another valid state, maintaining database integrity.
• Isolation: Ensures that the operations of a transaction are isolated from those of other transactions. This prevents transactions from interfering with each other.
• Durability: Ensures that the results of a committed transaction are permanent and survive system failures.

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
IF @@ERROR <> 0
    ROLLBACK TRANSACTION;
ELSE
    COMMIT TRANSACTION;

A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve readability and simplify complex queries by breaking them into simpler parts.

Usage:

• Recursive Queries: Performing recursive operations, such as traversing hierarchical data.
• Simplifying Queries: Breaking down complex queries into more manageable parts.

WITH EmployeeCTE AS (
    SELECT EmployeeID, FirstName, LastName, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID
    FROM Employees e
    INNER JOIN EmployeeCTE ecte ON e.ManagerID = ecte.EmployeeID
)
SELECT * FROM EmployeeCTE;

A view is a virtual table that is based on the result set of an SQL query. It contains rows and columns, just like a real table, and the fields in a view are fields from one or more real tables in the database.

Uses of Views:

• Simplify Complex Queries: Encapsulates complex queries in a single view, making it easier to query.
• Security: Restricts access to specific rows and columns of data by allowing users to query the view instead of the underlying table.
• Data Abstraction: Provides a layer of abstraction, presenting a consistent, unchanging interface even if the underlying database schema changes.
• Reusability: Once created, views can be reused across multiple queries.

CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
WHERE Department = 'Sales';

A trigger is a special kind of stored procedure that automatically executes in response to certain events on a particular table or view. Triggers can be used to enforce business rules, maintain audit trails, and synchronize tables.

Types of Triggers:

• Before Triggers: Execute before an INSERT, UPDATE, or DELETE operation.
• After Triggers: Execute after an INSERT, UPDATE, or DELETE operation.
• Instead of Triggers: Execute in place of the triggering operation, commonly used with views.

CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO EmployeeAudit(EmployeeID, ChangeDate)
    SELECT EmployeeID, GETDATE()
    FROM inserted;
END;

Uses:

• Enforcing Business Rules: Automatically enforce complex business rules that cannot be enforced by constraints.
• Audit Trails: Automatically log changes to data for auditing purposes.
• Data Synchronization: Ensure data consistency across related tables.

A subquery, also known as an inner query or nested query, is a query within another SQL query. The result of the subquery is used by the outer query. Subqueries can be used in various SQL clauses like SELECT, INSERT, UPDATE, DELETE, and WHERE.

Types of Subqueries:

• Single-Row Subquery: Returns a single row of results.

SELECT * FROM Employees
WHERE EmployeeID = (SELECT ManagerID FROM Departments WHERE DepartmentID = 1);

• Multi-Row Subquery: Returns multiple rows of results.

SELECT * FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');

• Correlated Subquery: A subquery that references columns from the outer query.

SELECT e1.EmployeeID, e1.FirstName
FROM Employees e1
WHERE e1.Salary > (SELECT AVG(e2.Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID);

UNION: Combines the result sets of two or more SELECT queries into a single result set, excluding duplicate rows. The columns in the SELECT statements must have the same number and compatible data types.

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

UNION ALL: Combines the result sets of two or more SELECT queries into a single result set, including all duplicate rows.

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;

Key Differences:

• Duplicates: UNION removes duplicate rows, while UNION ALL includes all duplicates.
• Performance: UNION is slower due to the additional step of removing duplicates, while UNION ALL is faster as it simply combines the results.

Aggregate functions perform a calculation on a set of values and return a single value. They are commonly used with the GROUP BY clause in SQL queries to group rows that have the same values in specified columns.

Common Aggregate Functions:

• COUNT(): Returns the number of rows.

SELECT COUNT(*) FROM Employees;

• SUM(): Returns the total sum of a numeric column.

SELECT SUM(Salary) FROM Employees;

• AVG(): Returns the average value of a numeric column.

SELECT AVG(Salary) FROM Employees;

• MIN(): Returns the minimum value.

SELECT MIN(Salary) FROM Employees;

• MAX(): Returns the maximum value.

SELECT MAX(Salary) FROM Employees;

A materialized view is a database object that contains the results of a query and stores them physically. Unlike a regular view, which is a virtual table created dynamically upon each access, a materialized view stores data and can be refreshed periodically.

Differences:

• Storage: Regular views do not store data physically; materialized views store data physically.
• Performance: Materialized views can improve query performance as the data is precomputed and stored. Regular views are slower as they execute the underlying query each time they are accessed.
• Update Frequency: Materialized views need to be refreshed to reflect the latest data changes, whereas regular views always show the current data from the underlying tables.

User-defined functions (UDFs) are routines that accept parameters, perform an action, and return the result of that action as a value. UDFs can be scalar (return a single value) or table-valued (return a table).

Differences from Stored Procedures:

• Return Type: UDFs must return a value, whereas stored procedures may or may not return values.
• Usage in Queries: UDFs can be used in SQL statements like SELECT, WHERE, and JOIN clauses, while stored procedures cannot.
• Transaction Control: UDFs do not allow explicit transaction control (BEGIN TRANSACTION, COMMIT, ROLLBACK), whereas stored procedures do.

Database sharding is a method of distributing data across multiple databases or servers to improve performance and scalability. Each shard is a separate database that contains a subset of the data.

Uses:

• Scalability: Allows the database to handle more data and transactions by distributing the load across multiple servers.
• Performance: Reduces the amount of data each server has to handle, improving query response times.
• Availability: Enhances availability by isolating failures to individual shards.

Example: If a customer database is sharded by region, one shard might contain data for North America, while another shard contains data for Europe.

The HAVING and WHERE clauses are both used to filter records in SQL, but they operate in different contexts and stages of query execution.

WHERE Clause:

• Purpose: Filters rows before any groupings are made.
• Usage: Applied to individual rows in a table.
• Context: Used in SELECT, UPDATE, DELETE statements.

SELECT * FROM Employees WHERE Salary > 50000;

HAVING Clause:

• Purpose: Filters groups after the GROUP BY clause has been applied.
• Usage: Applied to aggregated data.
• Context: Used with GROUP BY in SELECT statements.

SELECT Department, COUNT(*) as EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;

Key Differences:

• Filtering Stage: WHERE filters before grouping, HAVING filters after grouping.
• Applicability: WHERE cannot be used with aggregate functions; HAVING can.

A surrogate key is an artificial key that is used as a unique identifier for each row in a table. It is not derived from the data in the table and has no business meaning. Surrogate keys are typically auto-incremented integers.

Uses:

• Uniqueness: Ensures each row has a unique identifier.
• Simplicity: Simplifies the identification of rows, especially in cases where there are no natural unique keys.

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);