Learnixo
Back to blog
Backend Systemsbeginner

SQL Interview Questions: Junior Level (Q1–Q60)

60 SQL interview questions for junior developers with detailed answers — SELECT basics, WHERE, JOINs, GROUP BY, subqueries, NULL handling, and data types. Perfect for 0–2 years experience.

LearnixoJune 3, 202616 min read
SQLDatabaseInterviewJuniorJoinsQueries
Share:𝕏

About This Guide

Junior SQL interviews test whether you can read and write real queries. They don't expect you to know execution plans or index internals — they want to know you can retrieve, filter, join, and aggregate data correctly.

For each answer, focus on understanding why the query works, not just what to type.

SELECT Basics

Q1: What does SELECT * do and why should you avoid it in production?

SELECT * retrieves all columns from a table. Avoid it in production because:

  • It fetches columns you don't need, wasting network and memory
  • It breaks if a column is added, removed, or reordered
  • Query optimisers can't use covering indexes when all columns are selected
SQL
-- Bad
SELECT * FROM Customers;

-- Good — only fetch what you need
SELECT Id, Name, Email FROM Customers;

Q2: What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters after grouping (on aggregated values).

SQL
-- WHERE — filter before aggregate
SELECT CustomerId, SUM(Amount) AS Total
FROM Orders
WHERE Status = 'Completed'      -- filter rows first
GROUP BY CustomerId;

-- HAVING — filter after aggregate
SELECT CustomerId, SUM(Amount) AS Total
FROM Orders
GROUP BY CustomerId
HAVING SUM(Amount) > 1000;      -- filter groups by aggregate

-- Both together
SELECT CustomerId, SUM(Amount) AS Total
FROM Orders
WHERE Status = 'Completed'
GROUP BY CustomerId
HAVING SUM(Amount) > 1000;

Q3: What is DISTINCT and when would you use it?

Removes duplicate rows from the result.

SQL
-- All unique countries in the customer table
SELECT DISTINCT Country FROM Customers;

-- Without DISTINCT — duplicates appear
SELECT Country FROM Customers; -- "UK" may appear 50 times

Use it to find unique values. Avoid it as a crutch to hide JOIN problems that produce duplicates — understand why duplicates appear first.

Q4: What is the order SQL clauses are written vs evaluated?

Written order: SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

Evaluation order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

This matters because you can't use a SELECT alias in WHERE — WHERE runs before SELECT.

SQL
-- WRONG — 'Total' alias not yet defined when WHERE runs
SELECT SUM(Amount) AS Total FROM Orders WHERE Total > 1000;

-- RIGHT — use HAVING for aggregates, or repeat the expression
SELECT SUM(Amount) AS Total FROM Orders GROUP BY CustomerId HAVING SUM(Amount) > 1000;

Q5: What does ORDER BY do and what is the default sort direction?

Sorts results. Default is ASC (ascending). Use DESC for descending.

SQL
SELECT Name, Price FROM Products ORDER BY Price DESC;

-- Multiple columns — sort by Category, then by Price within category
SELECT Name, Category, Price
FROM Products
ORDER BY Category ASC, Price DESC;

Q6: What does LIMIT / TOP do?

Restricts how many rows are returned. Syntax varies by database:

SQL
-- SQL Server
SELECT TOP 10 * FROM Orders ORDER BY CreatedAt DESC;

-- PostgreSQL / MySQL / SQLite
SELECT * FROM Orders ORDER BY CreatedAt DESC LIMIT 10;

-- Pagination
SELECT * FROM Orders ORDER BY Id LIMIT 10 OFFSET 20; -- rows 21-30

NULL Handling

Q7: What is NULL and how do you test for it?

NULL means the value is unknown or absent. It is not zero, not empty string, not false. You cannot use = NULL — use IS NULL or IS NOT NULL.

SQL
-- WRONG — always returns no rows
SELECT * FROM Customers WHERE Phone = NULL;

-- RIGHT
SELECT * FROM Customers WHERE Phone IS NULL;
SELECT * FROM Customers WHERE Phone IS NOT NULL;

Q8: What is COALESCE and ISNULL?

Both return the first non-NULL value. COALESCE is standard SQL (works everywhere). ISNULL is SQL Server specific.

SQL
-- Return Phone, or Mobile if Phone is NULL, or 'No contact' if both NULL
SELECT Name, COALESCE(Phone, Mobile, 'No contact') AS ContactNumber
FROM Customers;

-- SQL Server shorthand
SELECT ISNULL(Phone, 'No contact') FROM Customers;

Q9: How does NULL affect aggregate functions?

Most aggregates (SUM, AVG, MAX, MIN, COUNT(column)) ignore NULL. COUNT(*) counts all rows including NULLs.

SQL
-- If Discount is NULL for some rows, AVG ignores them
SELECT AVG(Discount) FROM Products; -- average of non-NULL discounts only

-- COUNT(*) vs COUNT(column)
SELECT COUNT(*)        FROM Customers; -- all rows
SELECT COUNT(Phone)    FROM Customers; -- only rows where Phone is NOT NULL

Q10: What does NULL in a comparison return?

NULL in any comparison returns UNKNOWN, not true or false.

SQL
NULL = NULL    -- UNKNOWN (not TRUE)
NULL <> 1      -- UNKNOWN
NULL + 5       -- NULL

This is why WHERE col = NULL never returns rows — you must use IS NULL.

JOINs

Q11: What is an INNER JOIN?

Returns only rows where there is a match in both tables.

SQL
SELECT o.Id, o.Amount, c.Name
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id;
-- Orders without a matching customer are excluded
-- Customers with no orders are excluded

Q12: What is a LEFT JOIN?

Returns all rows from the left table, and matching rows from the right. NULLs fill the right side where there is no match.

SQL
-- All customers, including those with no orders
SELECT c.Name, o.Id AS OrderId, o.Amount
FROM Customers c
LEFT JOIN Orders o ON c.Id = o.CustomerId;
-- Customers with no orders have NULL for OrderId and Amount

Q13: What is the difference between LEFT JOIN and RIGHT JOIN?

They're mirrors of each other. LEFT JOIN keeps all from the left table. RIGHT JOIN keeps all from the right. Most developers use LEFT JOIN consistently and swap table order rather than using RIGHT JOIN.

SQL
-- These two are equivalent
SELECT * FROM A LEFT  JOIN B ON A.Id = B.AId;
SELECT * FROM B RIGHT JOIN A ON B.AId = A.Id;

Q14: What is a FULL OUTER JOIN?

Returns all rows from both tables. NULLs fill the missing side.

SQL
SELECT c.Name, o.Amount
FROM Customers c
FULL OUTER JOIN Orders o ON c.Id = o.CustomerId;
-- All customers (even no orders) + all orders (even no customer match)

Q15: What is a CROSS JOIN?

Cartesian product — every row from the left joined with every row from the right. N × M rows.

SQL
-- 4 sizes × 5 colours = 20 combinations
SELECT s.Size, c.Colour
FROM Sizes s
CROSS JOIN Colours c;

Q16: What is a self JOIN?

Joining a table to itself. Common for hierarchical data.

SQL
-- Find each employee and their manager (manager is also in Employees)
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerId = m.Id;

Q17: How do you find rows in table A that have no match in table B?

Use a LEFT JOIN and filter where the right side is NULL.

SQL
-- Customers who have never placed an order
SELECT c.Id, c.Name
FROM Customers c
LEFT JOIN Orders o ON c.Id = o.CustomerId
WHERE o.Id IS NULL;

GROUP BY and Aggregates

Q18: What is GROUP BY?

Collapses rows with the same value(s) into groups. You can then apply aggregate functions to each group.

SQL
-- Count orders per customer
SELECT CustomerId, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerId;

Every column in SELECT must either be in GROUP BY or wrapped in an aggregate.

Q19: What are the main aggregate functions?

| Function | Returns | |---|---| | COUNT(*) | Number of rows | | COUNT(col) | Rows where col is not NULL | | SUM(col) | Sum of values | | AVG(col) | Average of values | | MAX(col) | Largest value | | MIN(col) | Smallest value |

SQL
SELECT
    CustomerId,
    COUNT(*)          AS Orders,
    SUM(Amount)       AS TotalSpend,
    AVG(Amount)       AS AvgOrder,
    MAX(Amount)       AS LargestOrder,
    MIN(CreatedAt)    AS FirstOrder
FROM Orders
GROUP BY CustomerId;

Q20: What is GROUP BY with ROLLUP?

Adds subtotal and grand total rows.

SQL
SELECT Category, Status, COUNT(*) AS Total
FROM Orders
GROUP BY ROLLUP(Category, Status);
-- Returns: per-category-per-status, per-category subtotals, grand total

Subqueries

Q21: What is a subquery?

A query nested inside another query.

SQL
-- Find customers who spent more than the average order
SELECT CustomerId
FROM Orders
GROUP BY CustomerId
HAVING SUM(Amount) > (
    SELECT AVG(Amount) FROM Orders  -- subquery
);

Q22: What is a correlated subquery?

A subquery that references the outer query. Executed once per row of the outer query.

SQL
-- For each customer, find their most recent order date
SELECT c.Name,
    (SELECT MAX(o.CreatedAt)
     FROM Orders o
     WHERE o.CustomerId = c.Id) AS LastOrderDate  -- references outer c.Id
FROM Customers c;

Correlated subqueries can be slow on large tables — often replaceable with JOINs.

Q23: What is IN vs EXISTS?

IN checks if a value is in a list or subquery result. EXISTS checks if a subquery returns any rows.

SQL
-- IN
SELECT * FROM Customers
WHERE Id IN (SELECT CustomerId FROM Orders WHERE Amount > 500);

-- EXISTS — often faster; stops at first match
SELECT * FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id AND o.Amount > 500);

Q24: What is a CTE (Common Table Expression)?

A named temporary result set defined with WITH. Makes complex queries readable.

SQL
WITH HighValueOrders AS (
    SELECT CustomerId, SUM(Amount) AS Total
    FROM Orders
    GROUP BY CustomerId
    HAVING SUM(Amount) > 1000
)
SELECT c.Name, hvo.Total
FROM Customers c
INNER JOIN HighValueOrders hvo ON c.Id = hvo.CustomerId;

Q25: What is a recursive CTE?

A CTE that references itself. Used for hierarchical data like org charts or category trees.

SQL
WITH RECURSIVE OrgChart AS (
    -- Anchor: top-level employees (no manager)
    SELECT Id, Name, ManagerId, 0 AS Level
    FROM Employees WHERE ManagerId IS NULL

    UNION ALL

    -- Recursive: employees whose manager is already in the CTE
    SELECT e.Id, e.Name, e.ManagerId, oc.Level + 1
    FROM Employees e
    INNER JOIN OrgChart oc ON e.ManagerId = oc.Id
)
SELECT * FROM OrgChart ORDER BY Level, Name;

Data Modification

Q26: What is the syntax for INSERT?

SQL
-- Single row
INSERT INTO Products (Name, Price, CategoryId)
VALUES ('Widget', 9.99, 3);

-- Multiple rows
INSERT INTO Products (Name, Price, CategoryId) VALUES
    ('Gadget',    14.99, 3),
    ('Doohickey', 4.99,  1);

-- Insert from SELECT
INSERT INTO ArchivedOrders (Id, CustomerId, Amount)
SELECT Id, CustomerId, Amount FROM Orders WHERE CreatedAt < '2025-01-01';

Q27: What is the syntax for UPDATE?

SQL
-- Update one column
UPDATE Products SET Price = 12.99 WHERE Id = 5;

-- Update multiple columns
UPDATE Customers
SET Email = 'new@email.com', UpdatedAt = GETDATE()
WHERE Id = 42;

-- ALWAYS use WHERE — without it, every row is updated
UPDATE Products SET Price = 0; -- DANGER: updates all products!

Q28: What is the syntax for DELETE vs TRUNCATE?

SQL
-- DELETE — removes specific rows, logs each deletion, can be rolled back
DELETE FROM Orders WHERE Status = 'Cancelled' AND CreatedAt < '2024-01-01';

-- TRUNCATE — removes ALL rows, minimal logging, much faster
TRUNCATE TABLE TempImportData;
-- Cannot be used with WHERE
-- Cannot be rolled back in some databases

Data Types and Schema

Q29: What is the difference between CHAR and VARCHAR?

CHAR(n) is fixed-length — always stores n characters, padded with spaces. VARCHAR(n) is variable-length — stores only the actual characters, up to n.

Use CHAR for fixed-length data (country codes, ISO codes). Use VARCHAR for everything else.

Q30: What is the difference between INT, BIGINT, DECIMAL, and FLOAT?

| Type | Size | Range | Use for | |---|---|---|---| | INT | 4 bytes | ±2.1 billion | IDs, counts | | BIGINT | 8 bytes | ±9.2 quintillion | Large IDs, timestamps as int | | DECIMAL(p,s) | Exact | Specified by p,s | Money — exact arithmetic | | FLOAT / REAL | 4-8 bytes | Approximate | Scientific, where small errors OK |

Never use FLOAT for money — floating point arithmetic is imprecise.

Q31: What is a primary key?

A column (or combination) that uniquely identifies each row. Cannot be NULL. Usually auto-incremented.

SQL
CREATE TABLE Customers (
    Id    INT         PRIMARY KEY IDENTITY(1,1),
    Email VARCHAR(255) NOT NULL UNIQUE,
    Name  VARCHAR(100) NOT NULL
);

Q32: What is a foreign key?

A column that references a primary key in another table, enforcing referential integrity.

SQL
CREATE TABLE Orders (
    Id         INT PRIMARY KEY IDENTITY(1,1),
    CustomerId INT NOT NULL,
    Amount     DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (CustomerId) REFERENCES Customers(Id)
    -- Prevents inserting an order for a non-existent customer
);

Q33: What is a UNIQUE constraint?

Ensures all values in a column (or combination) are distinct.

SQL
-- Single column
ALTER TABLE Customers ADD CONSTRAINT UQ_Email UNIQUE (Email);

-- Composite unique (combination must be unique)
ALTER TABLE ProductVariants ADD CONSTRAINT UQ_ProductColour UNIQUE (ProductId, ColourId);

Q34: What is NOT NULL constraint?

Prevents NULL values in a column.

SQL
CREATE TABLE Products (
    Id    INT          NOT NULL PRIMARY KEY,
    Name  VARCHAR(200) NOT NULL,   -- cannot be NULL
    Description VARCHAR(MAX)       -- nullable
);

Q35: What is DEFAULT constraint?

Sets a value when none is provided on INSERT.

SQL
CREATE TABLE Orders (
    Id        INT PRIMARY KEY,
    Status    VARCHAR(20) DEFAULT 'Pending',
    CreatedAt DATETIME    DEFAULT GETDATE()
);

INSERT INTO Orders (Id) VALUES (1);
-- Status = 'Pending', CreatedAt = current time

String Functions

Q36: What are common string functions in SQL?

SQL
SELECT
    LEN('Hello')              AS Length,        -- 5
    UPPER('hello')            AS Uppercase,     -- HELLO
    LOWER('HELLO')            AS Lowercase,     -- hello
    TRIM('  hello  ')         AS Trimmed,       -- 'hello'
    LTRIM(RTRIM(' hello '))   AS TrimmedOld,    -- SQL Server older syntax
    LEFT('Hello', 3)          AS LeftPart,      -- Hel
    RIGHT('Hello', 3)         AS RightPart,     -- llo
    SUBSTRING('Hello', 2, 3)  AS Sub,           -- ell
    REPLACE('Hello', 'l', 'r') AS Replaced,     -- Herro
    CHARINDEX('ll', 'Hello')  AS Position,      -- 3
    CONCAT('Hello', ' ', 'World') AS Joined;    -- Hello World

Q37: What does LIKE do?

Pattern matching on strings. % matches any sequence of characters. _ matches exactly one character.

SQL
-- Names starting with 'A'
SELECT * FROM Customers WHERE Name LIKE 'A%';

-- Emails containing 'gmail'
SELECT * FROM Customers WHERE Email LIKE '%gmail%';

-- Exactly 5-character codes
SELECT * FROM Products WHERE Code LIKE '_____';

-- Escape the % character
SELECT * FROM Products WHERE Description LIKE '%10\% off%' ESCAPE '\';

Date and Time

Q38: How do you get the current date/time?

SQL
-- SQL Server
SELECT GETDATE()         -- datetime with time
SELECT GETUTCDATE()      -- UTC datetime
SELECT SYSDATETIMEOFFSET() -- datetime with timezone offset

-- PostgreSQL / MySQL
SELECT NOW()
SELECT CURRENT_TIMESTAMP

Q39: How do you extract parts of a date?

SQL
-- SQL Server
SELECT
    YEAR(CreatedAt)    AS Year,
    MONTH(CreatedAt)   AS Month,
    DAY(CreatedAt)     AS Day,
    DATEPART(HOUR, CreatedAt)   AS Hour,
    DATEPART(WEEKDAY, CreatedAt) AS Weekday;

-- PostgreSQL
SELECT
    EXTRACT(YEAR FROM created_at),
    DATE_TRUNC('month', created_at);

Q40: How do you calculate the difference between two dates?

SQL
-- SQL Server
SELECT DATEDIFF(DAY, OrderDate, DeliveryDate) AS DaysToDeliver
FROM Orders;

SELECT DATEDIFF(YEAR, BirthDate, GETDATE()) AS Age FROM Customers;

-- Add to a date
SELECT DATEADD(DAY, 30, GETDATE()) AS DueDate;

Additional Junior Questions (Q41–Q60)

Q41: What is an alias? AS renames a column or table in the result. SELECT SUM(Amount) AS Total or FROM Orders o (table alias for shorter references in JOINs).

Q42: What is the difference between UNION and UNION ALL? UNION combines results and removes duplicates. UNION ALL combines results keeping duplicates — faster because no deduplication step.

Q43: When would you use UNION? When combining similar result sets from different tables or conditions. Both queries must have the same number of columns with compatible types.

Q44: What is BETWEEN? Inclusive range filter. WHERE Price BETWEEN 10 AND 50 is equivalent to WHERE Price >= 10 AND Price <= 50.

Q45: What is IN with a list? WHERE Country IN ('UK', 'US', 'DE') — shorthand for WHERE Country = 'UK' OR Country = 'US' OR Country = 'DE'.

Q46: What is NOT IN and its NULL trap? WHERE Id NOT IN (1, 2, NULL) returns no rows — because x NOT IN (...NULL...) is UNKNOWN for any x. Always check for NULLs in subqueries used with NOT IN, or use NOT EXISTS instead.

Q47: What is a CHECK constraint? Validates column values against an expression. CHECK (Price > 0) prevents negative prices.

Q48: What is CAST and CONVERT? Type conversion. CAST(Price AS VARCHAR(10)) is standard SQL. CONVERT(VARCHAR(10), Price, 120) is SQL Server specific and supports format styles.

Q49: What is CASE expression? Conditional logic in SQL. CASE WHEN Score >= 90 THEN 'A' WHEN Score >= 80 THEN 'B' ELSE 'C' END.

Q50: What is IIF in SQL Server? Inline if. IIF(Score >= 60, 'Pass', 'Fail') — shorthand for simple CASE expressions.

Q51: What is the difference between a table and a view? A table stores data. A view is a saved SELECT query — it looks like a table but reads from underlying tables. Views don't store data (unless materialised/indexed).

Q52: What is an index? A data structure that speeds up lookups on a column. Like a book index — instead of scanning every page, you jump directly to the relevant rows. Slows writes; speeds reads.

Q53: What is a clustered vs non-clustered index? Clustered: the table rows are physically sorted by the index key. One per table. Non-clustered: a separate structure pointing to rows. Multiple per table.

Q54: What does SELECT INTO do? Creates a new table and inserts query results. SELECT * INTO NewTable FROM OldTable WHERE .... Good for quick backups or temp tables.

Q55: What is a temp table? #TempTable — a table that exists only for the current session. Dropped when session ends. Useful for intermediate results in complex queries.

Q56: What is a table variable? DECLARE @t TABLE (Id INT, Name VARCHAR(100)) — scoped to the current batch, lighter than temp tables for small result sets.

Q57: What is ROWCOUNT? SET ROWCOUNT n limits the number of rows affected by a statement. @@ROWCOUNT returns the number of rows affected by the last statement.

Q58: What is TOP WITH TIES? Includes rows that tie on the last value. SELECT TOP 3 WITH TIES * FROM Products ORDER BY Price returns 3+ rows if multiple products share the 3rd price.

Q59: What does NOLOCK hint do? Allows dirty reads — reads uncommitted data. Avoids locking contention but may return inconsistent results. Use only for reporting queries where occasional stale data is acceptable.

Q60: What is a stored procedure? A named, precompiled SQL program stored in the database. Called with EXEC ProcName @param1. Benefits: reuse, security (grant execute without table access), reduced network traffic.

Enjoyed this article?

Explore the Backend Systems learning path for more.

Subscribe for weekly tutorials

Found this helpful?

Share:𝕏

Leave a comment

Have a question, correction, or just found this helpful? Leave a note below.