SQL Interview Questions for Freshers

SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It allows users to create, read, update, and delete data within a database.

SQL statements are categorized into:

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

A primary key is a column (or a set of columns) that uniquely identifies each row in a table. It must contain unique values and cannot contain NULLs.

A foreign key is a column in one table that references the primary key in another table, establishing a relationship between the two tables.

• WHERE is used to filter rows before grouping.
• HAVING is used to filter groups after the GROUP BY clause.

-- Using WHERE
SELECT * FROM Employees WHERE Salary > 50000;

-- Using HAVING
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 60000;

The SELECT statement is used to retrieve data from one or more tables.

Example:

SELECT FirstName, LastName FROM Employees;

Use the DISTINCT keyword to fetch unique records.

Example:

SELECT DISTINCT DepartmentID FROM Employees;

The ORDER BY clause is used to sort the result set in ascending (ASC) or descending (DESC) order.

Example:

SELECT * FROM Employees ORDER BY Salary DESC;

• DELETE removes specified rows and can be rolled back.
• TRUNCATE removes all rows from a table and cannot be rolled back.

A JOIN is used to combine rows from two or more tables based on a related column.

Types of JOINs:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN

Example:

SELECT Employees.FirstName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Used for pattern matching in WHERE clause.

SELECT * FROM Employees WHERE FirstName LIKE 'J%';

• % matches any number of characters.
• _ matches exactly one character.

A marker for missing or unknown data. Not the same as 0 or empty string.

SELECT * FROM Employees WHERE DepartmentID IS NULL;

Ascending (ASC).

SELECT FirstName AS Name FROM Employees;

Used to filter values within a range.

SELECT * FROM Employees WHERE Salary BETWEEN 50000 AND 80000;

A temporary name given to a table or column.

SELECT COUNT(*) FROM Employees;

SELECT MAX(Salary) FROM Employees;

Groups rows with the same values.

SELECT DepartmentID, AVG(Salary) FROM Employees GROUP BY DepartmentID;

Functions that perform calculations on multiple rows (e.g., SUM, COUNT, AVG, MAX, MIN).

UPDATE Employees SET Salary = 70000 WHERE EmployeeID = 1;

DELETE FROM Employees WHERE EmployeeID = 1;

A query nested inside another query.

Rules applied to table columns (NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY).

Assigns a default value when none is provided.

Combines results from two queries and removes duplicates.

• UNION: Removes duplicates.
• UNION ALL: Keeps all rows including duplicates.

Organizing data to reduce redundancy and improve integrity.

Combining tables for read performance at the cost of redundancy.

• 1NF: Atomic values
• 2NF: No partial dependency
• 3NF: No transitive dependency

A virtual table based on a SQL query.

CREATE VIEW HighSalaries AS
SELECT * FROM Employees WHERE Salary > 80000;

Yes, if it's based on a single table and doesn't include aggregates.

Improves the speed of data retrieval.

• Unique Index
• Composite Index
• Clustered & Non-Clustered Index

A unit of work that is performed as a single logical operation.

• Atomicity
• Consistency
• Isolation
• Durability

Saves the current transaction to the database.

Undoes the changes in a transaction.

Marks a point in a transaction to rollback to.

Subquery that uses values from the outer query.

SELECT E1.FirstName, E2.FirstName
FROM Employees E1, Employees E2
WHERE E1.ManagerID = E2.EmployeeID;

Cartesian product of two tables.

A subquery used in the FROM clause.

SELECT FirstName,
  CASE 
    WHEN Salary > 80000 THEN 'High'
    ELSE 'Normal'
  END AS SalaryLevel
FROM Employees;

Precompiled SQL code that can be executed.

Returns a single value and can be used in SQL expressions.

• Procedures can return multiple values.
• Functions must return one value and are used in expressions.

Special procedures that automatically execute when a specific event occurs.

Replaces NULL with a specified value.

Returns the first non-null value in a list.

SELECT GETDATE();

Functionally equivalent; both return current system date and time.

SELECT FirstName + ' ' + LastName AS FullName FROM Employees;

SELECT COUNT(DISTINCT DepartmentID) FROM Employees;

SELECT TOP 5 * FROM Employees;

SELECT FirstName, COUNT(*)
FROM Employees
GROUP BY FirstName
HAVING COUNT(*) > 1;

A table created for the current session.

CREATE TABLE #TempEmployees (ID INT);

Conditional logic inside SQL.

SELECT Name, Salary,
  CASE 
    WHEN Salary > 70000 THEN 'High'
    WHEN Salary BETWEEN 40000 AND 70000 THEN 'Medium'
    ELSE 'Low'
  END AS SalaryRange
FROM Employees;

Used to filter aggregated/grouped data.

SELECT DepartmentID, AVG(Salary)
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 50000;

Checks for the existence of rows returned by a subquery.

SELECT Name
FROM Employees E
WHERE EXISTS (
  SELECT 1 FROM Departments D WHERE D.ManagerID = E.EmployeeID
);

• EXISTS: Stops searching once it finds a match (better for correlated subqueries).
• IN: Retrieves all matching rows (can be less efficient for large data sets).

A primary key made up of two or more columns.

ALTER TABLE Employees ADD DateOfJoining DATE;

ALTER TABLE Employees DROP COLUMN DateOfJoining;

Deletes a table, view, or database permanently.

INSERT INTO EmployeesBackup SELECT * FROM Employees;

SELECT MAX(Salary) 
FROM Employees 
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

SELECT Salary
FROM (
  SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
  FROM Employees
) AS RankedSalaries
WHERE Rank = 3;

Assigns ranks to rows, skipping duplicates.

Like RANK() but doesn’t skip ranks for duplicates.

Assigns a unique sequential integer to each row.

WITH Ranked AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID) AS rn
  FROM Employees
)
DELETE FROM Ranked WHERE rn > 1;

LEN(), UPPER(), LOWER(), LTRIM(), RTRIM(), SUBSTRING(), REPLACE()

Converts a value from one data type to another.

SELECT CAST(Salary AS VARCHAR(10)) FROM Employees;

Also converts data types. More flexible with formatting.

Operate on a single value and return a single result.
Examples: LEN(), GETDATE(), ISNULL()

Return a table instead of a single value.

SELECT DepartmentID, SUM(Salary)
FROM Employees
GROUP BY DepartmentID;

System views that let you query metadata (tables, columns, etc.).

SELECT * FROM INFORMATION_SCHEMA.TABLES;

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Employees';

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Employees')
PRINT 'Table Exists';

A system-generated unique identifier (e.g., auto-increment ID).

A real-world identifier like email or SSN.

A column that could be a primary key.

Validates data before insertion.

ALTER TABLE Employees ADD CONSTRAINT chk_Salary CHECK (Salary > 0);

• CHAR: Fixed-length
• VARCHAR: Variable-length

Tables maintained by the DBMS to store metadata.

Always use IS NULL because = NULL will not return any result.

Temporary result sets used in complex queries.

WITH DeptCTE AS (
  SELECT DepartmentID, COUNT(*) AS EmpCount
  FROM Employees
  GROUP BY DepartmentID
)
SELECT * FROM DeptCTE WHERE EmpCount > 5;

A CTE that refers to itself to handle hierarchical data (like org charts).

SELECT * INTO EmployeesBackup FROM Employees;

ALTER TABLE Employees ADD CONSTRAINT UQ_Email UNIQUE (Email);

Faster query performance, especially for SELECT operations.

• Extra storage space
• Slower INSERT, UPDATE, DELETE operations