SQL Questions and Answers (with T-SQL and P-SQL examples)
Basic SQL Questions
1. Question: What is SQL?
Answer: Structured Query Language (SQL) is a standard programming language used to manage
and manipulate relational databases. It is used to create, read, update, and delete (CRUD)
database records.
2. Question: How do you retrieve all records from a table?
Answer:
SELECT * FROM table_name;
Example:
SELECT * FROM Employees;
3. Question: How can you filter data in SQL?
Answer: Use the WHERE clause to filter rows based on conditions.
SELECT * FROM Employees
WHERE Department = 'HR';
4. Question: How do you sort data in SQL?
Answer: Use the ORDER BY clause to sort the results in ascending (ASC) or descending (DESC)
order.
SELECT * FROM Employees
ORDER BY Salary DESC;
Intermediate SQL Questions
5. Question: How do you perform an inner join between two tables?
Answer:
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
Example: Retrieves the names of employees along with their department names.
6. Question: How do you calculate the total sales for each department?
Answer:
SELECT Department, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY Department;
Example: Groups sales records by department and calculates the total sales per department.
T-SQL (Transact-SQL) Questions
7. Question: How do you create a stored procedure in T-SQL?
Answer:
CREATE PROCEDURE GetEmployeeDetails
AS
BEGIN
SELECT * FROM Employees;
END;
Example: A stored procedure that retrieves all employee records.
8. Question: How do you use a TRY-CATCH block in T-SQL for error handling?
Answer:
BEGIN TRY
-- SQL statements
INSERT INTO Employees (Name, DepartmentID)
VALUES ('John Doe', 2);
END TRY
BEGIN CATCH
PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;
Example: Inserts a new employee record and handles potential errors.
9. Question: How do you use a Common Table Expression (CTE) in T-SQL?
Answer:
WITH DepartmentSales AS (
SELECT Department, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY Department
SELECT * FROM DepartmentSales;
Example: Creates a temporary result set for department sales and retrieves all records from it.
P-SQL (PL/pgSQL) Questions
10. Question: How do you write a function in P-SQL?
Answer:
CREATE OR REPLACE FUNCTION GetEmployeeCount()
RETURNS INTEGER AS $$
DECLARE
count INTEGER;
BEGIN
SELECT COUNT(*) INTO count FROM Employees;
RETURN count;
END;
$$ LANGUAGE plpgsql;
Example: A function that returns the total number of employees.
11. Question: How do you use a loop in P-SQL?
Answer:
DO $$
DECLARE
emp RECORD;
BEGIN
FOR emp IN SELECT * FROM Employees LOOP
RAISE NOTICE 'Employee: %', emp.Name;
END LOOP;
END;
$$;
Example: Loops through all employees and prints their names.
12. Question: How do you handle exceptions in P-SQL?
Answer:
BEGIN
INSERT INTO Employees (Name, DepartmentID)
VALUES ('Jane Doe', 5);
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'An error occurred: %', SQLERRM;
END;
Example: Handles any error that occurs during the insertion of a new employee record.
Advanced SQL Questions
13. Question: How do you create an index on a table?
Answer:
CREATE INDEX idx_department ON Employees(DepartmentID);
Example: Creates an index on the DepartmentID column of the Employees table to improve query
performance.
14. Question: What is a window function in SQL, and how is it used?
Answer:
SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;
Example: Assigns a rank to employees based on their salary in descending order.
15. Question: How do you perform a recursive query in SQL?
Answer:
WITH RECURSIVE EmployeeHierarchy AS (
SELECT EmployeeID, ManagerID, Name
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.Name
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
SELECT * FROM EmployeeHierarchy;
Example: Retrieves a hierarchical list of employees and their managers.
Additional Questions to Explore
16. Question: How do you use a trigger in SQL?
Answer:
CREATE TRIGGER UpdateTimestamp
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
UPDATE Employees SET LastModified = CURRENT_TIMESTAMP WHERE EmployeeID =
NEW.EmployeeID;
END;
Example: Updates the LastModified column whenever an employee record is updated.
17. Question: How do you implement transaction management in SQL?
Answer:
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
IF @@ERROR <> 0
BEGIN
ROLLBACK;
END
ELSE
BEGIN
COMMIT;
END;
Example: Transfers money between accounts while ensuring transactional consistency.
