SQL Interview Questions for 1–2 Years Experience

SELECT *
FROM Employees e
WHERE Salary > (
  SELECT AVG(Salary)
  FROM Employees
  WHERE DepartmentID = e.DepartmentID
);

SELECT DepartmentID
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) < 5;

DELETE FROM AuditLogs
WHERE LogID NOT IN (
  SELECT LogID
  FROM AuditLogs
  ORDER BY CreatedDate DESC
  LIMIT 100
);

SELECT CustomerID
FROM Orders
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY CustomerID
HAVING COUNT(DISTINCT MONTH(OrderDate)) = 12;

SELECT *
FROM Employees
WHERE Salary >= (
  SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY Salary) 
  FROM Employees
);

SELECT p.ProductID
FROM Products p
LEFT JOIN OrderDetails od ON p.ProductID = od.ProductID
WHERE od.OrderID IS NULL;

SELECT ManagerID
FROM Employees
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
HAVING COUNT(*) > 3;

SELECT FirstName
FROM Employees
WHERE FirstName = REVERSE(FirstName);

SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET n-1;

SELECT o.OrderID, o.Total
FROM Orders o
WHERE o.Total > 1.5 * (SELECT AVG(Total) FROM Orders);

SELECT e.*
FROM Employees e
JOIN Employees j ON e.DepartmentID = j.DepartmentID
WHERE j.FirstName = 'Ajit' AND j.LastName = 'Singh';

SELECT DISTINCT od1.ProductID, od2.ProductID
FROM OrderDetails od1
JOIN OrderDetails od2 
  ON od1.OrderID = od2.OrderID AND od1.ProductID < od2.ProductID;

SELECT * FROM A
EXCEPT
SELECT * FROM B
UNION ALL
SELECT * FROM B
EXCEPT
SELECT * FROM A;

SELECT o1.OrderID, o2.OrderID AS NextOrderID
FROM Orders o1
JOIN Orders o2 
  ON o2.OrderDate = (
    SELECT MIN(o3.OrderDate)
    FROM Orders o3
    WHERE o3.OrderDate > o1.OrderDate
  );

SELECT c.CustomerID
FROM Orders2023 c
INTERSECT
SELECT c.CustomerID
FROM Orders2024 c;

SELECT 
  COALESCE(a2.AddressLine1, a1.AddressLine1) AS AddressLine1,
  COALESCE(a2.City, a1.City)              AS City
FROM Address1 a1
FULL OUTER JOIN Address2 a2 ON a1.ID = a2.ID;

SELECT e.EmployeeID
FROM Employees e
LEFT JOIN Employees s ON s.ManagerID = e.EmployeeID
WHERE s.EmployeeID IS NULL;

WITH Dates AS (
  SELECT CAST('2025-01-01' AS DATE) + INTERVAL n DAY AS d
  FROM (SELECT ROW_NUMBER() OVER() - 1 AS n FROM dual CONNECT BY LEVEL <= 365)
)
SELECT d
FROM Dates
LEFT JOIN Sales s ON s.SaleDate = d
WHERE s.SaleDate IS NULL;

SELECT *
FROM Sales
PIVOT (
  SUM(Amount) 
  FOR Month IN ([Jan], [Feb], [Mar], …)
) AS p;

SELECT Company, Quarter, Revenue
FROM QuarterlyRev
UNPIVOT (
  Revenue FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS unp;

SELECT 
  SaleDate, 
  Amount,
  SUM(Amount) OVER (ORDER BY SaleDate) AS RunningTotal
FROM Sales;

SELECT 
  ProductID,
  CategoryID,
  Sales,
  RANK() OVER (PARTITION BY CategoryID ORDER BY Sales DESC) AS SalesRank
FROM ProductSales;

SELECT 
  Month,
  Sales2025,
  LAG(Sales2024) OVER (ORDER BY Month) AS PrevYearSales,
  (Sales2025 - LAG(Sales2024) OVER (ORDER BY Month)) / 
   LAG(Sales2024) OVER (ORDER BY Month) AS YoYGrowth
FROM YearlySales;

SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS rn
  FROM Employees
) t
WHERE rn <= 3;

SELECT 
  Date,
  AVG(Views) OVER (
    ORDER BY Date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS MovingAvg7
FROM PageViews;

WITH Seq(n) AS (
  SELECT MIN(ID) FROM Items
  UNION ALL
  SELECT n+1 FROM Seq WHERE n < (SELECT MAX(ID) FROM Items)
)
SELECT n AS MissingID
FROM Seq
LEFT JOIN Items i ON i.ID = Seq.n
WHERE i.ID IS NULL;

SELECT 
  StudentID, Score,
  DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM Scores;

SELECT
  SaleID,
  Amount,
  PERCENT_RANK() OVER (ORDER BY Amount) AS PctRank
FROM Sales;

• Cumulative: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• Sliding: Specify fixed frame, e.g. ROWS BETWEEN 4 PRECEDING AND CURRENT ROW

SELECT DISTINCT
  CustomerID,
  FIRST_VALUE(SaleDate) OVER (PARTITION BY CustomerID ORDER BY SaleDate) AS FirstSale,
  LAST_VALUE(SaleDate) OVER (
    PARTITION BY CustomerID 
    ORDER BY SaleDate 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS LastSale
FROM Sales;

WITH RECURSIVE OrgChart AS (
  SELECT EmployeeID, ManagerID, FirstName
  FROM Employees
  WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.EmployeeID, e.ManagerID, e.FirstName
  FROM Employees e
  JOIN OrgChart o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgChart;

WITH RECURSIVE Fact(n, f) AS (
  SELECT 1, 1
  UNION ALL
  SELECT n+1, (n+1)*f FROM Fact WHERE n < @N
)
SELECT f FROM Fact ORDER BY n DESC LIMIT 1;

WITH RECURSIVE Calendar AS (
  SELECT CAST('2025-01-01' AS DATE) AS CalDate
  UNION ALL
  SELECT CalDate + INTERVAL 1 DAY FROM Calendar WHERE CalDate < '2025-12-31'
)
SELECT CalDate FROM Calendar;

WITH RECURSIVE TeamSales AS (
  SELECT EmployeeID, ManagerID, Sales
  FROM Employees
  UNION ALL
  SELECT e.EmployeeID, e.ManagerID, t.Sales
  FROM Employees e
  JOIN TeamSales t ON e.ManagerID = t.EmployeeID
)
SELECT ManagerID, SUM(Sales) AS TotalTeamSales
FROM TeamSales
GROUP BY ManagerID;

WITH Split AS (
  SELECT 
    TRIM(value) AS Item
  FROM STRING_SPLIT('A,B,C', ',')
)
SELECT * FROM Split;

WITH RECURSIVE Paths AS (
  SELECT FromNode, ToNode, CAST(FromNode AS VARCHAR(100)) AS Path
  FROM Graph
  UNION ALL
  SELECT g.FromNode, g.ToNode, p.Path || '->' || g.ToNode
  FROM Graph g
  JOIN Paths p ON g.FromNode = p.ToNode
  WHERE p.Path NOT LIKE '%' || g.ToNode || '%'
)
SELECT * FROM Paths WHERE FromNode = ToNode;

WITH Latest AS (
  SELECT CustomerID, MAX(OrderDate) AS LastOrder
  FROM Orders GROUP BY CustomerID
)
UPDATE Customers c
SET c.LastOrderDate = l.LastOrder
FROM Latest l WHERE c.CustomerID = l.CustomerID;

WITH RECURSIVE ToDelete AS (
  SELECT EmployeeID FROM Employees WHERE EmployeeID = @Target
  UNION ALL
  SELECT e.EmployeeID
  FROM Employees e
  JOIN ToDelete td ON e.ManagerID = td.EmployeeID
)
DELETE FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM ToDelete);

WITH Numbered AS (
  SELECT 
    InvoiceID,
    ROW_NUMBER() OVER (ORDER BY InvoiceDate) AS InvNum
  FROM Invoices
)
SELECT InvNum, *
FROM Numbered;

WITH ActiveUsers AS (
  SELECT UserID, LastLogin
  FROM Users
  WHERE LastLogin > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
)
CREATE TEMPORARY TABLE RecentActive AS SELECT * FROM ActiveUsers;

Use EXPLAIN (MySQL/PostgreSQL) or SET STATISTICS PROFILE ON; (SQL Server) to view indexes used, join order, and cost estimates.

When no usable index exists for the query predicates, forcing the engine to read every row.

They include all columns needed by the query, so the engine can satisfy the query entirely from the index without fetching table rows.

Over time, inserts/updates/deletes cause index pages to split and contain empty space. Regularly rebuild or reorganize indexes to maintain performance.

Use keyset pagination (seek method) instead of LIMIT OFFSET:

SELECT * FROM Orders
WHERE OrderID > @LastSeen
ORDER BY OrderID
LIMIT 50;

Applying functions (e.g. UPPER(Name) = 'JOHN') prevents index usage. Instead, transform constants or use function-based indexes if supported.

When the optimizer uses the first supplied parameter’s distribution statistics to generate a plan that may not be optimal for other parameter values.
Mitigation: Use local variables, OPTION (RECOMPILE), or query hints.

• Implicit: Database automatically wraps each statement in a transaction.
• Explicit: Developer uses BEGIN TRANSACTION/COMMIT/ROLLBACK.

Use dynamic management views (sys.dm_exec_requests, sys.dm_tran_locks) in SQL Server or SHOW ENGINE INNODB STATUS; in MySQL to inspect lock waits and deadlock chains.

Percentage of space filled on each index page when rebuilt. Lower fill factor leaves more free space for future inserts, reducing page splits.

• Read Uncommitted: Dirty reads allowed.
• Read Committed: No dirty reads; non-repeatable reads possible.
• Repeatable Read: Prevents dirty & non-repeatable reads; phantom reads possible.
• Serializable: Full isolation; prevents phantoms but most restrictive.

Phantom reads occur when new rows are added by another transaction between two reads. Only Serializable prevents them.

Engines like PostgreSQL/MariaDB (with MVCC) keep multiple versions of a row to allow readers to see a consistent snapshot without blocking writers.

• Pessimistic: Locks rows early, preventing others from modifying.
• Optimistic: Checks for conflicts at commit time, allowing concurrent access until then.

Catch deadlock error in application code or stored proc, wait briefly, then retry the transaction up to a fixed number of attempts.

A transaction that sees a consistent snapshot of the database as of its start time, isolating it from concurrent changes.

A protocol for distributed transactions:

• Prepare Phase: All participants vote to commit or abort.
• Commit Phase: Coordinator tells all to commit if all voted yes; otherwise abort.

Use optimistic concurrency with a version/timestamp column and check before updating, or use proper locking hints.

In certain isolation levels, two concurrent transactions read overlapping data, make decisions, and write, leading to inconsistent state. Prevented by stricter isolation (e.g., Serializable).

Track start/end timestamps in application or use database profiling/tracing tools to log transaction durations.

When table/column names aren’t known until runtime, or to build flexible search queries. Caution: Guard against SQL injection (use parameterization).

Use parameterized queries or bind variables; avoid concatenating raw user input.

• Local (#): Visible only in the session that created them.
• Global (##): Visible to all sessions until the creating session disconnects.

Simply SELECT inside the proc; most clients will receive it as a result set.

Obfuscates the procedure’s definition, preventing users from viewing its source.

Use TRY...CATCH blocks (SQL Server) or DECLARE EXIT HANDLER in MySQL to catch exceptions and roll back if needed.

Define a table type in the database and use it as a parameter in the proc:

CREATE TYPE IdList AS TABLE (ID INT);
CREATE PROCEDURE ProcessIDs (@IDs IdList READONLY) AS …

• Inline: Single SELECT returning a result; often optimized like a view.
• Multi-statement: Multiple statements populate a table variable; can be less efficient.

Use SQL Server Agent Job (SQL Server) or Event Scheduler (MySQL) to call the proc on a schedule.

• EXEC: Executes a string command; parameters aren’t bound—SQL injection risk.
• sp_executesql: Allows parameterization, safer and can reuse execution plans.

Use JSON_VALUE(), JSON_QUERY(), and OPENJSON():

SELECT JSON_VALUE(Data, '$.user.name') AS UserName
FROM Logs;

UPDATE Logs
SET Data = JSON_MODIFY(Data, '$.status', 'completed');

Use .nodes() and .value():

SELECT
  x.value('(Order/OrderID)[1]', 'INT') AS OrderID
FROM XmlTable t
CROSS APPLY t.XmlCol.nodes('/Orders') AS T(x);

SELECT *
FROM Employees
FOR JSON AUTO;

SELECT TRIM(val) AS item
FROM JSON_TABLE(
  CONCAT('["', REPLACE('A,B,C', ',', '","'), '"]'),
  '$[*]' COLUMNS(val VARCHAR(100) PATH '$')
) AS jt;

Create a computed column extracting a JSON path, then index that column.

UPDATE Config
SET JsonCol = JSON_MERGE_PATCH(JsonCol, '{"newKey":"value"}');

Create primary XML index on the column, then selective secondary or path indexes for frequent queries:

CREATE PRIMARY XML INDEX PIdx ON XmlTable(XmlCol);
CREATE XML INDEX PathIdx ON XmlTable(XmlCol)
  USING XML INDEX PIdx FOR PATH('Orders/Order');

• JSON: Flexible schema, easy for semi-structured data.
• Relational: Strong typing, integrity constraints, easier to index and query with SQL.

When leveraging existing XML-based apps, needing XML schema validation, or using XQuery features unique to your DBMS.

In SQL Server, query the dynamic management view sys.dm_db_missing_index_details to see recommendations.

Occurs when the engine must fetch additional columns not covered by the index by looking up the clustered index or heap.

They index only rows that satisfy a WHERE clause, reducing index size and improving performance on selective queries.

When your query selects only the columns in the index, so the engine never has to read the base table.

The engine can split a query into multiple threads if the cost exceeds the threshold, allowing concurrent I/O and CPU usage.

Query hints forcing the optimizer to use (or skip) a specific index:

SELECT * FROM Employees WITH (INDEX(IX_Emp_Salary))
WHERE Salary > 50000;

Use hints like INNER LOOP JOIN, HASH JOIN, or MERGE JOIN in SQL Server:

SELECT * FROM A 
INNER HASH JOIN B ON A.ID = B.ID;

Identify the top-cost operators, look at row estimates vs. actuals, check missing index warnings, and large sorts or hash spills.

It materializes intermediate results into temp storage for reuse, often caused by nested loops or repeated scans.

Save it via SQL Server Management Studio to a .sqlplan file or use SET STATISTICS XML ON;.

CREATE USER readonly FOR LOGIN user;
GRANT SELECT ON SCHEMA::dbo TO readonly;

Injection occurs when user input alters SQL structure. Prevent by using parameterized queries, stored procedures, and ORM frameworks.

SQL Server: Create a security policy with predicates:

CREATE SECURITY POLICY FilterOrders
ADD FILTER PREDICATE dbo.fn_securitypredicate(UserID) 
ON Orders;

• GRANT: Gives a permission to a user.
• WITH GRANT OPTION: Allows that user to grant the same permission to others.

Use Always Encrypted with client-side encryption keys, or Transparent Data Encryption for entire database.

Used to encrypt connections, sign modules (procs, triggers), and protect symmetric keys.

• Use CHANGE DATA CAPTURE or CHANGE TRACKING in SQL Server.
• Or create triggers that log changes to an audit table.

Give users only the permissions they absolutely need to reduce security risks.

REVOKE INSERT ON Employees TO readonly;

Encrypts data at rest at the file level. Decryption happens in memory for authorized users, protecting backups and data files.