Top 50 SQL Interview Questions.

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

• It is used to establish and enforce a relationship between two tables.
• It can contain duplicates and NULLs, depending on the relationship.
• It ensures referential integrity.

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);

CREATE TABLE CourseRegistrations (
    StudentID INT,
    CourseID INT,
    RegistrationDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

-- Employee Table
EmployeeID | Name | DepartmentID

-- Department Table
DepartmentID | DepartmentName

EmployeeID | Name | DepartmentName

Employee Table:
ID | Name   | Department
1  | Alice  | HR
2  | Bob    | HR
3  | Carol  | H.R.

1. First Normal Form (1NF)

StudentID | Name  | Subjects
1         | Alice | Math, Science

StudentID | Name  | Subject
1         | Alice | Math
1         | Alice | Science

• It is already in 1NF
• And every non-key column is fully functionally dependent on the entire primary key (i.e., no partial dependency)

(StudentID, CourseID) → InstructorName  
InstructorName depends only on CourseID, not the full key.

3. Third Normal Form (3NF)

EmployeeID | Name  | DepartmentID | DepartmentName

Boyce-Codd Normal Form (BCNF)

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age >= 18),
    JoinDate DATE DEFAULT GETDATE()
);

• Enforce business rules at the database level
• Prevent invalid data entry
• Maintain relationships between tables
• Improve data reliability and quality

SELECT A.Name, B.DepartmentName
FROM Employees A
INNER JOIN Departments B
ON A.DepartmentID = B.DepartmentID;

• Returns all rows from the left table and matched rows from the right.
• If no match, NULLs are returned for rthe ight table columns.

SELECT A.Name, B.DepartmentName
FROM Employees A
LEFT JOIN Departments B
ON A.DepartmentID = B.DepartmentID;

• Returns all rows from the right table and matched rows from the left.
• If no match, NULLs are returned for left table columns.

SELECT A.Name, B.DepartmentName
FROM Employees A
RIGHT JOIN Departments B
ON A.DepartmentID = B.DepartmentID;

• Returns all rows when there is a match in either the left or right table.
• Non-matching rows are filled with NULLs.

SELECT A.Name, B.DepartmentName
FROM Employees A
FULL OUTER JOIN Departments B
ON A.DepartmentID = B.DepartmentID;

SELECT A.Name, B.DepartmentName
FROM Employees A
CROSS JOIN Departments B;

• A table is joined with itself to compare rows within the same table.

SELECT A.EmployeeID, A.Name, B.Name AS ManagerName
FROM Employees A
JOIN Employees B
ON A.ManagerID = B.EmployeeID;

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

SELECT e1.Name
FROM Employees e1
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees e2
    WHERE e1.Department = e2.Department
);

CREATE VIEW ActiveEmployees AS
SELECT Name, Department
FROM Employees
WHERE Status = 'Active';

SELECT * FROM ActiveEmployees;

• Faster SELECT queries.
• Improve JOIN and WHERE performance.
• Support for ORDER BY and GROUP BY.
• Used automatically by the query optimizer.

CREATE CLUSTERED INDEX idx_emp_id ON Employees(EmployeeID);

CREATE NONCLUSTERED INDEX idx_emp_name ON Employees(Name);

CREATE UNIQUE INDEX idx_email ON Employees(Email);

CREATE INDEX idx_name_dept ON Employees(Name, DepartmentID);

CREATE FULLTEXT INDEX ON Articles(Content) 
KEY INDEX PK_Articles;

• Optimized for geographic or spatial data types (e.g., geometry, geography).
• Used in location-based applications.

CREATE SPATIAL INDEX idx_location ON Locations(GeoData);

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department;

19. Explain the HAVING clause and how it differs from WHERE.

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
WHERE Status = 'Active'
GROUP BY Department
HAVING COUNT(*) > 5;

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE PROCEDURE GetEmployeeByDept
    @DeptID INT
AS
BEGIN
    SELECT * FROM Employees WHERE DepartmentID = @DeptID;
END

EXEC GetEmployeeByDept @DeptID = 2;

CREATE FUNCTION GetFullName(
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50)
)
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END

SELECT dbo.GetFullName('John', 'Doe');

Ways to Handle NULL Values in SQL:

SELECT * FROM Employees WHERE ManagerID IS NULL;

SELECT Name, COALESCE(Email, 'No Email') AS ContactEmail
FROM Employees;

SELECT ISNULL(PhoneNumber, 'Not Provided') AS Phone
FROM Customers;

SELECT NULLIF(Salary, 0) AS ValidSalary FROM Employees;

SELECT 
    Name,
    CASE 
        WHEN Email IS NULL THEN 'Missing Email'
        ELSE Email
    END AS EmailStatus
FROM Employees;

WITH CTE_Name AS (
    SELECT column1, column2
    FROM SomeTable
    WHERE condition
)
SELECT * FROM CTE_Name;

WITH ActiveEmployees AS (
    SELECT EmployeeID, Name
    FROM Employees
    WHERE Status = 'Active'
)
SELECT * FROM ActiveEmployees;

SELECT 
    Name, 
    Department,
    Salary,
    RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;

BEGIN TRY
    -- SQL statements that might cause an error
END TRY
BEGIN CATCH
    -- Error handling logic
END CATCH

BEGIN TRY
    -- Attempt to divide by zero (will cause error)
    DECLARE @x INT = 1, @y INT = 0, @result INT;
    SET @result = @x / @y;
END TRY
BEGIN CATCH
    PRINT 'An error occurred:';
    PRINT ERROR_MESSAGE();  -- Returns the actual error message
END CATCH;

• ERROR_NUMBER() Returns the error number
• ERROR_MESSAGE() Returns the error message text
• ERROR_LINE() Returns the line number where error occurred
• ERROR_SEVERITY() Returns severity of the error
• ERROR_PROCEDURE() Returns the name of the stored proc

29. How can you optimize a slow-running query?

How to Prevent Deadlocks?

32. Explain the concept of data warehousing.

33. What is the difference between CHAR and VARCHAR2 in SQL?

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Department VARCHAR(50),
  Salary DECIMAL(10,2),
  JoinDate DATE
);

INSERT INTO Employees (ID, Name, Department, Salary, JoinDate)
VALUES (1, 'Alice', 'HR', 60000, '2022-01-15');

INSERT INTO Employees (ID, Name, Department, Salary, JoinDate)
VALUES 
(2, 'Bob', 'IT', 70000, '2022-03-01'),
(3, 'Carol', 'Finance', 65000, '2022-02-20');

CREATE TABLE Departments (
  DeptID INT PRIMARY KEY,
  DeptName VARCHAR(50)
);

ALTER TABLE Employees
ADD CONSTRAINT FK_Department
FOREIGN KEY (Department) REFERENCES Departments(DeptName);

SELECT * FROM employees;

SELECT first_name, last_name FROM employees;

SELECT COUNT(*) FROM employees;

SELECT DISTINCT department FROM employees;

UPDATE employees SET salary = salary * 1.1 WHERE performance_rating = 'Excellent';

DELETE FROM employees WHERE termination_date IS NOT NULL;

ALTER TABLE employees ADD COLUMN date_of_birth DATE;

SELECT *
FROM Employees
ORDER BY ID
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

• OFFSET 20: Skip the first 20 rows.
• FETCH NEXT 10 ROWS ONLY: Return the next 10 rows.
• This gives you Page 3 (if Page 1 = 0–9, Page 2 = 10–19, Page 3 = 20–29).

SELECT *
FROM Employees
ORDER BY ID
LIMIT 10 OFFSET 20;

• LIMIT 10: Return 10 rows.
• OFFSET 20: Skip the first 20 rows.

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

SELECT department_id, SUM(salary) 
FROM employees 
GROUP BY department_id;

SELECT employee_id, COUNT(project_id) 
FROM project_assignments 
GROUP BY employee_id;

SQL Injection is a security vulnerability that occurs when untrusted input is inserted into an SQL query without proper validation or sanitization, allowing attackers to manipulate the query and potentially access, modify, or delete database data.

1. Use Parameterized Queries / Prepared Statements

• This ensures input values are treated strictly as data, not executable code.

2. Use ORM Frameworks

• Tools like Entity Framework, Hibernate, or Sequelize automatically parameterize queries.

50. How would you implement user access control in a database?

Conclusion.