40 questions with answers for candidates with 1 year of experience — 21 easy, 17 medium, 2 hard.
What interviewers expect: 1 year experience
With one year of experience, expect practical questions on queries you wrote at work, joins across several tables, CTEs, window functions like ROW_NUMBER and LAG, and basic indexing. Interviewers may give a live problem on real-looking tables and ask how you verified results. Be ready to explain one report or feature end to end.
This set is weighted towards Database Design (7), Joins (7), Indexes and Performance (7), SQL Basics (6).
Window functions CTEs Basic indexing Work project walkthrough
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.
✓ 3 questions free • Unlock the rest in seconds
01
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 trigger is code that runs automatically before or after INSERT, UPDATE or DELETE on a table. It is used for auditing changes, enforcing complex rules and maintaining derived data. Triggers are hidden from application developers, can slow writes and make debugging hard, especially when they cascade. Use them sparingly, document them well, and prefer constraints or application logic when possible.
Common types are INT, BIGINT and SMALLINT for whole numbers, DECIMAL or NUMERIC for exact values like money, FLOAT for approximate values, CHAR for fixed-length and VARCHAR for variable-length text, DATE, TIME, DATETIME or TIMESTAMP for dates, and BOOLEAN where supported. Choosing correct types saves space, improves performance and prevents errors such as storing amounts in FLOAT and getting rounding issues.
04
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.
A CROSS JOIN returns the Cartesian product, every row of the first table combined with every row of the second, so 10 rows by 5 rows gives 50 rows. It is useful for generating combinations, such as all stores by all dates to build a calendar grid and then left join actual sales to show zero-sales days. Accidental cross joins from missing conditions produce huge results.
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.
07
How do you read an EXPLAIN ANALYZE output in PostgreSQL?
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 101;
Read the tree from the innermost node outward. Check node types such as Seq Scan, Index Scan or Hash Join, compare estimated rows with actual rows, and look at actual time per node. Large estimate errors suggest stale statistics, and Seq Scan on a selective filter suggests a missing index.
08
Write an upsert that inserts a row or updates it if it exists.
PostgreSQL:
INSERT INTO inventory (sku, qty)
VALUES ('A100', 10)
ON CONFLICT (sku)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;
MySQL:
INSERT INTO inventory (sku, qty) VALUES ('A100', 10)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);
SQL Server and others use MERGE. A unique key on sku is required.
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
To see full duplicate rows:
SELECT * FROM users
WHERE email IN (
SELECT email FROM users
GROUP BY email HAVING COUNT(*) > 1
)
ORDER BY email;
Normalise case with LOWER(email) if needed.
10
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.
Avoid indexes on small tables where scans are cheap, on columns with very low selectivity such as a boolean flag used alone, on tables with heavy writes and few reads, and duplicates of existing indexes. Every index slows inserts and updates and uses memory and storage. Review unused indexes periodically with database statistics and drop them after checking dependencies.
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.
13
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.
14
Write a query to get employees from Delhi with salary above 50000, highest first.
SELECT emp_id, name, salary
FROM employees
WHERE city = 'Delhi'
AND salary > 50000
ORDER BY salary DESC;
WHERE filters rows, and ORDER BY DESC sorts from highest to lowest. Add LIMIT 10 in MySQL or PostgreSQL, or TOP 10 in SQL Server, to return only the first ten.
A view is a saved SELECT query that behaves like a virtual table. It simplifies complex joins for users, hides sensitive columns for security, and provides a stable interface when underlying tables change. A normal view does not store data, so it runs the query each time. Simple views can be updatable, while views with aggregates or joins usually are not.
16
Tell me about a time you had to deliver a report under a tight deadline.
Situation: the sales head needed region-wise quarterly numbers for a client meeting the same afternoon. Task: deliver accurate figures within three hours. Action: I confirmed metric definitions, reused tested queries, wrote one grouped query with ROLLUP for subtotals and cross-checked totals with the finance summary. Result: the report was delivered in two hours, and it later became a scheduled weekly report.
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.
18
You need to update 50 million rows in production. How do you do it safely?
Avoid one huge transaction that locks the table and fills the log. Test on a copy first, take a backup, and update in batches of a few thousand rows using a key range, committing each batch and pausing briefly to reduce load. Run during low traffic, monitor replication lag and locks, keep a rollback script, and verify counts after completion.
A composite key is a primary key made of two or more columns that together uniquely identify a row. For example, in an enrolments table, student_id and course_id together form the key because a student can join many courses and a course has many students, but each pair appears once. Column order in a composite key matters for index usage.
A self join joins a table to itself using aliases, used for hierarchical or comparative data in one table. Example: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id. Each employee row is matched with the row of its manager. LEFT JOIN keeps the top manager whose manager_id is NULL.
21
What are database statistics and why do they matter?
Statistics describe data distribution in columns, such as row counts, distinct values and histograms. The optimiser uses them to estimate how many rows each step returns and pick join types and indexes. Stale statistics after large data loads lead to bad plans, such as nested loops over millions of rows. Update them with ANALYZE in PostgreSQL or UPDATE STATISTICS in SQL Server.
22
What is the difference between UNION and UNION ALL?
UNION combines the results of two queries and removes duplicate rows, which requires sorting or hashing. UNION ALL combines results and keeps all rows, so it is faster. Both need the same number of columns with compatible types in the same order. Use UNION ALL when duplicates are impossible or acceptable, such as stacking monthly tables, and UNION only when deduplication is required.
23
An interviewer asks for the Nth highest salary in a reusable way. How do you answer?
Use DENSE_RANK so ties share a rank: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t WHERE r = :n. Alternatively use ORDER BY salary DESC with OFFSET n-1 ROWS FETCH NEXT 1 ROW ONLY on distinct salaries. Mention how ties are handled and that per-department versions add PARTITION BY department_id.
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.
Constraints enforce rules on data: PRIMARY KEY for unique row identity, FOREIGN KEY for valid references, UNIQUE for no duplicates, NOT NULL for required values, CHECK for conditions like age >= 18, and DEFAULT for automatic values. They protect data quality at the database level, so bad data is rejected even if an application has a bug.
26
A query with many OR conditions across different columns is slow. How can you optimise it?
OR across different columns often prevents a single index seek. Rewrite it as UNION ALL of separate queries, each using its own index, removing duplicates carefully if needed, or use IN for multiple values of the same column. Some optimisers can combine indexes with bitmap or index merge plans. Compare plans and timings to choose the best approach.
27
How do you approach a SQL problem you have never seen before in an interview?
Situation: in a campus interview I got a problem about consecutive login days that I had not practised. Task: solve it while explaining my thinking. Action: I clarified the table and expected output, wrote a small example, broke it into steps with CTEs, and talked through each step before writing the final query. Result: I solved it with a small hint and the interviewer appreciated the structured approach.
INSERT INTO students (student_id, name, city)
VALUES (101, 'Anita', 'Noida');
UPDATE students
SET city = 'Gurugram'
WHERE student_id = 101;
DELETE FROM students
WHERE student_id = 101;
Always use WHERE with UPDATE and DELETE; without it every row changes.
29
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.
Normalisation organises data into tables to reduce redundancy and avoid insert, update and delete anomalies. For example, storing customer address in every order row means updating it in many places. By separating customers and orders and linking with keys, each fact is stored once. Common forms are 1NF, 2NF, 3NF and BCNF, and most transactional systems target 3NF.
31
Match each employee's salary to a grade using a range table.
SELECT e.name, e.salary, g.grade
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary AND g.max_salary;
This is a non-equi join. Ensure grade ranges do not overlap, otherwise an employee matches more than one grade and appears twice.
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.
33
An e-commerce app needs products with very different attributes, such as phones and shirts. How do you model them?
Options are a common products table plus category-specific tables (table per type), an entity-attribute-value table, or a JSON or JSONB column for flexible attributes. A practical design keeps shared fields like name, price and category as columns, stores variable attributes in JSONB with indexes on frequently filtered keys, and validates required attributes in the application or with check constraints.
34
What is GROUP BY and what is the rule for selected columns?
GROUP BY groups rows that share values in specified columns so aggregates can be computed for each group. Every column in SELECT must either be in GROUP BY or inside an aggregate function. SELECT city, COUNT(*) FROM customers GROUP BY city is valid, but adding name without aggregating it is an error in standard SQL, though older MySQL modes allowed it.
35
What is the difference between a primary key and a unique key?
Both enforce uniqueness. A table can have only one primary key, and its columns cannot contain NULL. A table can have many unique constraints, and in most databases a unique column can hold NULL, often more than one, though SQL Server allows only one NULL by default. Primary keys often create a clustered index in SQL Server; unique keys create non-clustered indexes.
LAG returns a value from a previous row in the partition and LEAD from a following row, based on the ORDER BY. LAG(revenue, 1, 0) OVER (ORDER BY month) gives last month's revenue with 0 as default for the first row. They are used for month-over-month change, time between events, and detecting status changes without self joins.
37
What is the difference between putting a condition in ON versus WHERE in a LEFT JOIN?
In a LEFT JOIN, a condition in ON filters the right table before matching, so all left rows are still kept. A condition on the right table in WHERE runs after the join and removes rows where the right side is NULL, effectively turning it into an inner join. To get all customers with only their 2025 orders, put the date condition in ON.
SELECT o.order_id, o.order_date, c.name, o.amount
FROM orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;
Aliases o and c keep the query short and clear. INNER JOIN drops orders whose customer is missing, so use LEFT JOIN if such orders must still appear.
39
A query that ran in 1 second now takes 40 seconds after a data load. What do you check?
Compare the current execution plan with the old one. A big data load often makes statistics stale, causing poor row estimates and a different join or scan choice, so update statistics first. Check for index fragmentation, missing indexes after the load, blocking from other sessions, and whether the data volume crossed a threshold. Test again after each change.
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.
Frequently asked questions
What is asked in SQL interviews for 1 year of experience?
With one year of experience, expect practical questions on queries you wrote at work, joins across several tables, CTEs, window functions like ROW_NUMBER and LAG, and basic indexing. Interviewers may give a live problem on real-looking tables and ask how you verified results. Be ready to explain one report or feature end to end.
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 1 year of experience?
Focus first on Window functions, CTEs, Basic indexing, Work project walkthrough.