If you are preparing for a Data Analyst, MIS Analyst, Reporting Analyst, Business Analyst, or SQL-related job, knowing the fundamentals of SQL is extremely important.
In this article, we will cover 50 basic SQL interview questions with simple explanations and examples. These questions are especially useful for beginners who are learning SQL for the first time or preparing for their first SQL interview.
The examples use common tables such as Employees, Departments, and Companies.
1. What is SQL?
Answer:
SQL stands for Structured Query Language. It is used to communicate with relational databases.
SQL can be used to:
Retrieve data
Insert data
Update data
Delete data
Create tables
Modify database structures
Analyze business data
Example:
SELECT * FROM Employees;
This query retrieves all records from the Employees table.
2. What is a Database?
A database is an organized collection of data that allows information to be stored, managed, and retrieved efficiently.
For example, a company database might contain:
Employees
Departments
Salaries
Customers
Orders
Products
3. What is a Table?
A table stores data in rows and columns.
Example:
| EmployeeID | EmployeeName | Department | Salary |
|---|---|---|---|
| 101 | Rahul | IT | 75000 |
| 102 | Amit | HR | 65000 |
| 103 | Neha | Finance | 85000 |
Here:
EmployeeID,EmployeeName,Department, andSalaryare columns.Each employee record is a row.
4. What is a Primary Key?
A Primary Key uniquely identifies each record in a table.
Example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
Salary INT
);Here, EmployeeID is the primary key.
A primary key:
Must contain unique values.
Cannot contain NULL values.
Identifies each row uniquely.
5. What is a Foreign Key?
A foreign key is a column that creates a relationship between two tables.
Example:
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, EmployeeName VARCHAR(100), DepartmentID INT, FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID));
DepartmentID connects the Employees table with the Departments table.
6. What is SELECT in SQL?
SELECT is used to retrieve data from a table.
SELECT EmployeeName, Salary
FROM Employees;This returns the employee name and salary.
7. How do you retrieve all columns from a table?
Use the * symbol.
SELECT *
FROM Employees;This returns all columns.
8. What is the WHERE clause?
WHERE filters records based on a condition.
SELECT *
FROM Employees
WHERE Salary > 70000;This returns employees whose salary is greater than ₹70,000.
9. What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping.
HAVING filters groups after GROUP BY.
Example:
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 70000;Here, HAVING filters departments whose average salary is greater than ₹70,000.
10. What is ORDER BY?
ORDER BY sorts the result.
Ascending:
SELECT *
FROM Employees
ORDER BY Salary ASC;Descending:
SELECT *
FROM Employees
ORDER BY Salary DESC;11. What is DISTINCT?
DISTINCT removes duplicate values.
SELECT DISTINCT Department
FROM Employees;This returns each department only once.
12. What is GROUP BY?
GROUP BY groups rows having the same values.
Example:
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;This calculates the number of employees in each department.
13. What is COUNT()?
COUNT() counts records.
SELECT COUNT(*) AS TotalEmployees
FROM Employees;It returns the total number of employees.
14. What is SUM()?
SUM() calculates the total of a numeric column.
SELECT SUM(Salary) AS TotalSalary
FROM Employees;15. What is AVG()?
AVG() calculates the average value.
SELECT AVG(Salary) AS AverageSalaryFROM Employees;
16. What is MAX()?
MAX() returns the highest value.
SELECT MAX(Salary) AS HighestSalary
FROM Employees;17. What is MIN()?
MIN() returns the lowest value.
SELECT MIN(Salary) AS LowestSalaryFROM Employees;
18. What is an INNER JOIN?
INNER JOIN returns records that have matching values in both tables.
SELECT e.EmployeeName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;19. What is a LEFT JOIN?
A LEFT JOIN returns all records from the left table and matching records from the right table.
SELECT e.EmployeeName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;If an employee does not have a matching department, the department columns will contain NULL.
20. What is a RIGHT JOIN?
A RIGHT JOIN returns all records from the right table and matching records from the left table.
SELECT e.EmployeeName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;21. What is a FULL OUTER JOIN?
A FULL OUTER JOIN returns:
Matching records
Unmatched records from the left table
Unmatched records from the right table
Example:
SELECT *
FROM Employees e
FULL OUTER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;22. What is a JOIN?
A JOIN is used to combine data from two or more tables using a related column.
For example:
Employees → Departments → Companies
can be joined to find:
Employee Name + Department Name + Company Name
23. What is LIKE?
LIKE is used for pattern matching.
Example:
SELECT *
FROM Employees
WHERE EmployeeName LIKE 'A%';This finds employees whose names start with A.
24. What does % mean in LIKE?
% represents zero or more characters.
WHERE EmployeeName LIKE 'A%'Means the name starts with A.
WHERE EmployeeName LIKE '%a'Means the name ends with a.
25. What does _ mean in LIKE?
_ represents exactly one character.
Example:
WHERE EmployeeName LIKE 'A_i%'
The underscore represents one character between A and i.
26. What is BETWEEN?
BETWEEN checks whether a value falls within a range.
SELECT *
FROM Employees
WHERE Salary BETWEEN 50000 AND 80000;27. What is IN?
IN checks whether a value matches any value in a list.
SELECT *
FROM Employees
WHERE Department IN ('IT', 'HR', 'Finance');28. What is NOT IN?
NOT IN excludes specified values.
SELECT *
FROM Employees
WHERE Department NOT IN ('HR', 'Finance');29. What is NULL?
NULL means that a value is missing or unknown.
NULL is not the same as:
0
Blank text
Empty string
To find NULL values:
SELECT *
FROM Employees
WHERE DepartmentID IS NULL;30. How do you find NOT NULL values?
Use IS NOT NULL.
SELECT *
FROM Employees
WHERE DepartmentID IS NOT NULL;31. What is CASE in SQL?
CASE is used to create conditional logic.
SELECT EmployeeName, Salary, CASE WHEN Salary >= 80000 THEN 'High' WHEN Salary >= 60000 THEN 'Medium' ELSE 'Low' END AS SalaryCategoryFROM Employees;
32. What is an Alias?
An alias gives a temporary name to a column or table.
Example:
SELECT EmployeeName AS Name, Salary AS MonthlySalaryFROM Employees;
33. What is a Subquery?
A subquery is a query inside another query.
Example:
SELECT *FROM EmployeesWHERE Salary > (SELECT AVG(Salary) FROM Employees);
This finds employees whose salary is greater than the average salary.
34. How do you find the highest salary?
SELECT MAX(Salary) AS HighestSalary
FROM Employees;35. How do you find the second-highest salary?
One common method is:
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (
SELECT MAX(Salary)
FROM Employees
);36. How do you find employees earning more than the average salary?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
);This is a very common SQL interview question.
37. What is a CTE?
CTE stands for Common Table Expression.
It creates a temporary named result set that can be used by the main query.
Syntax:
WITH EmployeeData AS( SELECT EmployeeName, Salary FROM Employees)SELECT *FROM EmployeeData;
CTEs can make complex queries easier to read.
38. What is a Window Function?
A window function performs calculations across related rows without combining them into a single row.
Example:
SELECT EmployeeName, Salary, RANK() OVER (ORDER BY Salary DESC) AS SalaryRankFROM Employees;
This assigns a salary rank to each employee.
39. What is RANK()?
RANK() assigns a ranking to rows.
SELECT EmployeeName, Salary, RANK() OVER (ORDER BY Salary DESC) AS RankNoFROM Employees;
If two employees have the same salary, they receive the same rank, and the next rank is skipped.
40. What is DENSE_RANK()?
DENSE_RANK() also assigns rankings but does not skip numbers after a tie.
Example:
Salary RANK DENSE_RANK90000 1 190000 1 180000 3 270000 4 3
41. What is ROW_NUMBER()?
ROW_NUMBER() assigns a unique sequential number to every row.
SELECT EmployeeName, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNoFROM Employees;
Even if two employees have the same salary, each gets a different row number.
42. What is the difference between RANK(), DENSE_RANK() and ROW_NUMBER()?
The main difference is how they handle duplicate values.
| Function | Duplicate Rank | Skips Rank? |
|---|---|---|
| RANK() | Same | Yes |
| DENSE_RANK() | Same | No |
| ROW_NUMBER() | Different | No |
This is an important SQL interview topic.
43. What is a DELETE statement?
DELETE removes records from a table.
DELETE FROM EmployeesWHERE EmployeeID = 101;
Always use the WHERE condition carefully.
44. What is UPDATE?
UPDATE modifies existing records.
UPDATE EmployeesSET Salary = 75000WHERE EmployeeID = 101;
45. What is INSERT?
INSERT adds new records.
INSERT INTO Employees(EmployeeID, EmployeeName, Salary)VALUES(105, 'Ravi', 70000);
46. What is the difference between DELETE and TRUNCATE?
Both remove data, but they work differently.
DELETEDELETE FROM Employees;
TRUNCATE
Can delete selected rows using WHERE.
TRUNCATE TABLE Employees;
Removes all rows from the table.
TRUNCATE generally operates differently from DELETE in terms of logging and transaction behavior, depending on the database system.
47. What is a View?
A View is a virtual table based on a SQL query.
Example:
CREATE VIEW EmployeeSalaryView ASSELECT EmployeeName, SalaryFROM Employees;SELECT *FROM EmployeeSalaryView;
You can then query:
Views are commonly used to simplify reporting queries and control access to data.
48. What is a Constraint?
A constraint is a rule applied to data in a table.
Common constraints include:
PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK
DEFAULT
Example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100) NOT NULL,
Salary INT CHECK (Salary > 0)
);49. How do you find duplicate values?
Suppose we want to find duplicate employee names:
SELECT EmployeeName, COUNT(*) AS DuplicateCountFROM EmployeesGROUP BY EmployeeNameHAVING COUNT(*) > 1;
The important combination here is:
GROUP BY + COUNT() + HAVING
50. How do you find the highest-paid employee in each department?
One common approach is using RANK():
WITH RankedEmployees AS( SELECT EmployeeName, Department, Salary, RANK() OVER ( PARTITION BY Department ORDER BY Salary DESC ) AS SalaryRank FROM Employees)SELECT EmployeeName, Department, SalaryFROM RankedEmployeesWHERE SalaryRank = 1;
This is a very useful real-world SQL problem because it combines:
CTE
Window functions
PARTITION BY
ORDER BY
Filtering
Bonus: 10 SQL Topics You Should Learn After These 50 Questions
If you have completed these basic SQL interview questions, your next step should be to practice:
Joins
Subqueries
CTEs
Window Functions
RANK()
DENSE_RANK()
ROW_NUMBER()
CASE statements
Date functions
String functions
For Data Analyst interviews, don't just memorize SQL syntax. Practice writing queries from business requirements.
For example:
"Find departments where the average salary is greater than ₹70,000."
You should be able to convert that requirement into:
SELECT Department, AVG(Salary) AS AverageSalaryFROM EmployeesGROUP BY DepartmentHAVING AVG(Salary) > 70000;
That is the type of practical SQL thinking recruiters look for.
Final Tips for SQL Interviews
Before attending an SQL interview, make sure you can confidently explain and practice:
SELECT → WHERE → ORDER BY → GROUP BY → HAVING → JOIN → Subquery → CTE → CASE → Window Functions
Also practice writing queries without looking at the answer.
The best way to learn SQL is:
Learn → Write → Execute → Make mistakes → Fix → Repeat
If you are preparing for a Data Analyst or MIS Analyst interview, SQL combined with Excel and Power BI can be a particularly useful skill combination.
Conclusion
These 50 SQL interview questions provide a strong foundation for beginners.
Don't try to memorize all 50 answers. Instead, create a small practice database and execute every query yourself.
Once you can understand why a query works rather than simply memorizing the syntax, your SQL interview confidence will improve significantly.
Keep practicing SQL every day!
