SQL Interview Questions for Freshers - Interview Questions and Answers

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.

CommandDeletes DataCan RollbackDeletes Table Structure
DELETEYesYesNo
TRUNCATEYes (All)NoNo
DROPYes (All)NoYes

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.

Clustered IndexNon-Clustered Index
Alters table's data orderSeparate from table data
One per tableMany allowed

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

EmployeeIDFirstNameLastNameSalaryDepartmentIDManagerID
1JohnSmith50000101NULL
2JaneDoe700001011
3TomLee900001021
4MaryKim600001032