40 questions with answers for candidates with Freshers — 26 easy, 14 medium.
What interviewers expect: freshers
Fresher interviews and online tests focus on SELECT, WHERE, joins, GROUP BY, HAVING, subqueries, keys and normalisation basics, with classic problems such as second highest salary, duplicates and customers without orders. Some companies add simple window function questions. Interviewers also check whether you can explain your query logic clearly and handle NULLs correctly.
This set is weighted towards SQL Basics (8), Window Functions (6), Database Design (6), Aggregation and Grouping (5).
Joins and grouping Classic interview problems Keys and normalisation NULL handling
Solved at this levelStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
PARTITION BY divides rows into groups and the window function restarts for each group, like per department. ORDER BY defines the order of rows within each partition, needed for ranking, running totals and LAG or LEAD. Without PARTITION BY the whole result is one partition. Without ORDER BY, aggregate windows compute over the entire partition.
02
What are referential actions such as ON DELETE CASCADE?
Referential actions define what happens to child rows when a parent row is deleted or updated. CASCADE deletes or updates children automatically, SET NULL sets the foreign key to NULL, SET DEFAULT uses the default value, and RESTRICT or NO ACTION blocks the change. CASCADE is convenient for dependent data like order items but risky on important data, because one delete can remove many rows.
03
What is the difference between MIN/MAX on numbers and on strings or dates?
MIN and MAX work on any comparable type. On numbers they return smallest and largest values, on dates the earliest and latest, and on strings the first and last in alphabetical order based on collation. MAX(order_date) gives the latest order, and MIN(name) gives the alphabetically first name, which is useful but not a meaningful business metric by itself.
04
What is the difference between GROUP BY and window functions?
GROUP BY collapses rows into one row per group, so detail columns are lost. Window functions keep every row and add the computed value alongside, such as each employee with their department average. You can combine both: group first, then apply a window over the grouped result, for example percent of total using SUM(SUM(sales)) OVER ().
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
With a window function:
SELECT DISTINCT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t WHERE rnk = 2;
Both handle duplicate top salaries correctly.
SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 100
ORDER BY customers DESC;
HAVING is needed because the condition uses an aggregate. A WHERE clause could still filter rows first, for example only active customers.
07
Find all customers whose email is missing or empty.
SELECT customer_id, name
FROM customers
WHERE email IS NULL
OR TRIM(email) = '';
NULL needs IS NULL, because email = NULL never matches. Empty strings and spaces are separate values, so TRIM handles both. Consider fixing data at entry with NOT NULL and CHECK constraints.
08
A table stores phone numbers as 'ph1, ph2, ph3' in one column. What is wrong and how do you fix it?
The column violates first normal form because it stores multiple values in one field, making search, validation and updates difficult. Create a separate customer_phones table with customer_id, phone and type columns, one row per number, with a foreign key to customers. Migrate data by splitting the strings, then drop the old column after applications are updated.
09
Write a query to show each employee with their manager's name.
SELECT e.emp_id,
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;
The same table is used twice with different aliases. LEFT JOIN ensures the CEO, who has no manager, still appears with a NULL manager name.
10
Design tables for students, courses and enrolments.
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
title VARCHAR(100) NOT NULL
);
CREATE TABLE enrolments (
student_id INT REFERENCES students(student_id),
course_id INT REFERENCES courses(course_id),
enrolled_on DATE,
PRIMARY KEY (student_id, course_id)
);
The junction table resolves the many-to-many relationship.
A subquery is a query nested inside another statement. It can appear in WHERE, such as salary > (SELECT AVG(salary) FROM employees), in FROM as a derived table, in SELECT as a scalar value, and in HAVING. It can return a single value, a list for IN, or a table. Subqueries break complex logic into smaller steps.
12
Write SQL to create a students table with a primary key and constraints.
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE,
age INT CHECK (age >= 17),
city VARCHAR(50) DEFAULT 'Delhi',
joined_on DATE
);
PRIMARY KEY ensures unique non-null IDs, UNIQUE blocks duplicate emails, and CHECK validates age.
13
A COUNT(DISTINCT) query on a huge events table is very slow. What options do you have?
Exact distinct counts need sorting or hashing all values, which is expensive. Options are filtering the date range tightly, pre-aggregating daily distinct users into a summary table, indexing the columns involved, or using approximate functions such as APPROX_COUNT_DISTINCT or HyperLogLog where small error is acceptable. Explain the accuracy trade-off to stakeholders before switching to approximations.
14
A business user needs a dashboard on a complex 6-table join that runs slowly. Would you use a view or a materialised view?
A normal view only simplifies the query; it still runs the full join every time, so performance does not improve. If the data can be a few minutes or hours old, a materialised view or summary table refreshed on schedule gives fast reads. If real-time data is required, tune the underlying query with indexes or build an incrementally maintained table.
15
What is the CASE expression and where can it be used?
CASE adds conditional logic in SQL, returning a value based on conditions: CASE WHEN marks >= 60 THEN 'First' WHEN marks >= 45 THEN 'Second' ELSE 'Fail' END. It can be used in SELECT, WHERE, ORDER BY, GROUP BY and inside aggregates, for example SUM(CASE WHEN status = 'Paid' THEN amount ELSE 0 END) to pivot data. Conditions are checked in order and the first true one wins.
An index is a separate data structure, usually a B-tree, that stores column values in sorted order with pointers to rows, so the database can find matching rows without scanning the whole table, like a book index. Indexes speed up WHERE, JOIN, ORDER BY and GROUP BY, but use storage and slow down inserts, updates and deletes because the index must be maintained.
SQL, or Structured Query Language, is the standard language for working with relational databases. It is used to create tables, insert, update and delete data, query data with filters, joins and aggregations, and control access. Almost every application, analytics team and reporting tool relies on SQL, which is why it is tested in interviews for developers, analysts, testers and data engineers.
18
What is the logical order of execution of a SELECT query?
The logical order is FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT or OFFSET (or TOP/FETCH). This explains why a column alias defined in SELECT cannot be used in WHERE but can be used in ORDER BY in most databases. The optimiser may execute differently physically, but results follow this logical order.
AVG ignores NULLs, dividing the sum of non-null values by the count of non-null values. If three employees have bonuses 1000, 2000 and NULL, AVG(bonus) is 1500, not 1000. If NULL should mean zero, use AVG(COALESCE(bonus, 0)). Interviewers ask this to check whether you understand how missing data changes business metrics.
20
How do you make sure your SQL changes do not break anything?
Situation: in my internship I had to change a report query used by three teams. Task: modify it safely. Action: I tested it on a copy of data, compared row counts and totals with the old version, asked a senior for review, and documented the change. Result: the update went live without issues, and the comparison approach became part of my routine for every change.
WITH m AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders GROUP BY 1
)
SELECT month, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS mom_pct
FROM m ORDER BY month;
NULLIF avoids division by zero.
A stored procedure is a named set of SQL statements saved in the database and executed with CALL or EXEC, optionally with input and output parameters. It encapsulates business logic, reduces network round trips, can be reused by many applications and allows permission to execute without direct table access. Overusing procedures can make logic harder to version and test compared with application code.
23
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only rows where the join condition matches in both tables. LEFT JOIN keeps every row from the left table and fills columns from the right with NULL when there is no match. For customers and orders, INNER JOIN shows only customers who ordered, while LEFT JOIN shows all customers, including those with no orders, which is useful for finding inactive customers.
NULL means a missing or unknown value, not zero or an empty string. Any comparison with NULL using = or <> returns unknown, so rows are not returned. Use IS NULL or IS NOT NULL to test it, and COALESCE or IFNULL to replace it. Aggregates like SUM and AVG ignore NULLs, while COUNT(*) counts all rows and COUNT(column) skips NULLs.
A natural key is a real-world identifier like PAN number, email or product code. A surrogate key is a system-generated value, usually an integer or UUID, with no business meaning. Surrogate keys are stable, compact and good for joins, while natural keys can change or be long. A common design uses a surrogate primary key plus a unique constraint on the natural key.
26
Describe your SQL project from college or internship.
Situation: during my internship the team tracked student attendance in spreadsheets. Task: build a database and reports. Action: I designed tables for students, courses and attendance, loaded data, and wrote queries with joins, group by and window functions to show monthly attendance and low-attendance alerts. Result: faculty got a weekly report automatically, and I learned how real data quality issues affect queries.
A star schema has a central fact table with measures such as sales amount and foreign keys to denormalised dimension tables like date, product, store and customer. A snowflake schema normalises dimensions further, such as product to category to department. Star schemas are simpler and faster for BI queries, while snowflake schemas save space and reduce redundancy.
28
Find employees who earn more than their department average.
SELECT e.emp_id, e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
This is a correlated subquery. A window version uses AVG(salary) OVER (PARTITION BY department_id) in a CTE and filters in the outer query.
29
Why should we hire you for a SQL or data role as a fresher?
Situation: in college I built a library management project with a MySQL backend. Task: make queries fast and correct. Action: I designed normalised tables, wrote queries with joins and group by, and practised over 150 interview problems online. Result: I can write clean, tested SQL today. I learn quickly, ask good questions and want to grow into a strong data professional with your team.
30
An interviewer asks to show each employee's salary next to their department's average. How do you do it?
Use a window aggregate: SELECT name, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg, salary - AVG(salary) OVER (PARTITION BY department_id) AS diff FROM employees. Unlike GROUP BY, every employee row is kept. Mention that a join with a grouped subquery produces the same result but is longer.
Situation: after college I knew only basic SELECT queries. Task: become interview-ready for data roles. Action: I solved two practice problems daily, read execution plans for my own queries, revised window functions weekly and built a small project analysing public sales data in PostgreSQL. Result: within three months I could solve medium-level problems confidently, and I continue practising regularly.
32
Find the number of employees and average salary in each department.
SELECT department_id,
COUNT(*) AS emp_count,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY emp_count DESC;
Join with the departments table if department names are needed, and use LEFT JOIN from departments to include empty departments.
33
A query uses SELECT * on a table with 80 columns. Why is that a problem?
SELECT * fetches every column, increasing disk reads, memory, network transfer and application processing. It prevents covering indexes from being used and can break applications when columns are added or reordered. Select only required columns, which lets the optimiser use narrower indexes and makes the intent clear. SELECT * is acceptable only for quick exploration, not production code.
CHAR(n) stores fixed-length strings and pads shorter values with spaces, so every value uses n characters. VARCHAR(n) stores variable-length strings up to n and uses only the needed space plus a small length overhead. CHAR suits fixed codes such as country code or PIN format, while VARCHAR suits names, emails and addresses whose length varies.
35
What is a composite index and why does column order matter?
A composite index covers multiple columns, such as (customer_id, order_date). It is sorted by the first column, then the second within it. It helps queries filtering on customer_id alone or on both columns, but not queries filtering only on order_date, because of the leftmost prefix rule. Put the most commonly filtered equality column first, followed by range columns.
A primary key uniquely identifies each row in a table and cannot be NULL or duplicate, such as student_id. A foreign key is a column in one table that references the primary key of another, such as orders.customer_id referencing customers.customer_id. Foreign keys enforce referential integrity, preventing orders for customers that do not exist.
37
What is the difference between a correlated and a non-correlated subquery?
A non-correlated subquery runs independently and returns a result once, such as the overall average salary. A correlated subquery references columns from the outer query and logically runs for each outer row, such as comparing each employee's salary with the average of their own department. Correlated subqueries are powerful but may be slow without good indexes, although optimisers often rewrite them as joins.
Window functions perform calculations across a set of rows related to the current row without collapsing them into one row like GROUP BY. They use the OVER clause with optional PARTITION BY, ORDER BY and a frame. Examples are ROW_NUMBER, RANK, running SUM, LAG and LEAD. You can show each order alongside the customer's total, which GROUP BY alone cannot do.
An execution plan shows how the database will run a query: which tables are scanned or seeked, which indexes are used, the join algorithms, sort and aggregate steps, and estimated row counts and costs. Use EXPLAIN in MySQL and PostgreSQL, EXPLAIN ANALYZE for actual timing, and the estimated or actual plan in SQL Server. Reading plans is the first step in tuning slow queries.
40
Your NOT IN query returns zero rows even though you expect results. What do you check?
Check whether the subquery returns any NULL values, because NOT IN with a NULL makes every comparison unknown. Run the subquery alone and look for NULLs. Fix it with NOT EXISTS, which handles NULLs correctly, or add WHERE column IS NOT NULL inside the subquery. Also confirm data types match so values compare as expected.
Frequently asked questions
What is asked in SQL interviews for Freshers?
Fresher interviews and online tests focus on SELECT, WHERE, joins, GROUP BY, HAVING, subqueries, keys and normalisation basics, with classic problems such as second highest salary, duplicates and customers without orders. Some companies add simple window function questions. Interviewers also check whether you can explain your query logic clearly and handle NULLs correctly.
How many questions should I practise before a SQL interview?
Work through all 40 questions here, then the company-wise sets for the employers you are targeting. Quality matters more than count: be able to explain why each answer is right.
Which topics matter most for Freshers?
Focus first on Joins and grouping, Classic interview problems, Keys and normalisation, NULL handling.