386 Java Developer Interview Questions & Answers

At a high level, a REST API lets clients work with resources through HTTP. For example, products use URLs such as /products and /products/123. The client sends methods like GET, POST, PUT, PATCH, or DELETE, and the server returns a representation such as JSON with a meaningful status code. Each request should contain what the server needs. I would also validate input, authenticate requests, return consistent errors, paginate large lists, and use caching when useful. REST is an architectural style, not a Java framework, HTTP itself, or a data format.

A REST API gives programs a clear way to read or change information. In this example, the information is about products. A product gets an address such as /products/123. A client sends a request to that address, and the server sends a result back. The goal is to make these requests predictable and easy to understand. We also want invalid requests and failures to be handled clearly. I will follow the product example in the diagram and explain the URLs, actions, responses, security, errors, large lists, and caching.

• Which resources should this API expose?
• Which operations should clients perform on those resources?
• Which requests require authentication?
• How large can product lists become?

I would start with the resource. A resource is the thing the client wants to work with. Here, that resource is a product. The collection URL is /products. One specific product uses /products/123, where 123 identifies that product. The full example is https://api.example.com/products/123. The URL names the resource rather than an action. This makes the API easier for clients to understand.

The HTTP method tells the server what operation the client wants. GET /products reads the collection. GET /products/123 reads one product. POST /products creates a product. PUT /products/123 replaces a product. PATCH /products/123 partially updates it. DELETE /products/123 removes it. GET is safe because it should not change server state. GET, PUT, and DELETE are idempotent by HTTP semantics. Idempotent means repeating the same request has the same intended effect. The diagram also treats its PATCH operation as idempotent. PATCH is not automatically idempotent in HTTP, so that behavior must be designed carefully for this API. POST is shown as non-idempotent.

Each request should contain the information needed to process it. The server should not depend on stored client session state between requests. The example sends GET /products/123 HTTP/1.1 to api.example.com. It includes Accept: application/json and Authorization: Bearer <token>. HTTPS protects the request while it travels over the network. A bearer token or API key can identify or authenticate a caller. OAuth 2.0 can be used to obtain and authorize access tokens; it is not itself a Java framework.

The server returns a representation of the resource. The example uses JSON. It returns HTTP/1.1 200 OK, Content-Type: application/json, and product fields such as id, name, price, and inStock. Common codes shown are 200 OK, 201 Created, 400 Bad Request, 401 Unauthorized, 404 Not Found, 409 Conflict, and 500 Internal Server Error. These codes help clients understand what happened.

I would validate all input before using it. Invalid input should return 400 Bad Request with a clear message. Errors should use one consistent JSON shape so clients can handle them predictably. The diagram shows fields such as error, message, and code. Missing or invalid authentication can return 401 Unauthorized. A missing product returns 404 Not Found. A conflicting operation can return 409 Conflict. An unexpected server problem returns 500 Internal Server Error.

Large product collections should be returned in pages. The diagram uses GET /products?page=2&size=20. A response can include a total count or next and previous links. Caching can reduce server work and improve speed. The diagram shows Cache-Control, ETag, and Last-Modified. Its sample product response uses Cache-Control: max-age=60, allowing that response to be reused for a limited time.

Finally, REST is an architectural style. It is not a Java framework. Java frameworks such as Spring can help implement REST APIs, but they are not REST itself. REST commonly uses HTTP, but REST is not the HTTP transport protocol. REST is also not a data format. JSON is common, but XML, CSV, or other representations can also be used.

The main design goal is predictability. Resource URLs make it clear what data the client is using. HTTP methods give operations familiar meanings. Status codes and consistent error bodies make failures easier to handle. Stateless requests simplify server handling because every request carries what it needs. Authentication protects access, but credentials and tokens must be managed safely. Pagination keeps large responses smaller, but clients need more requests. Caching can improve speed and reduce server work, but cached data may be older until its cache lifetime ends. Idempotent operations are easier to repeat after an uncertain network result. The benefit is safer recovery. The downside is that the API must define repeated updates carefully, especially because PATCH is not inherently idempotent.

At a high level, Spring MVC is Spring Framework’s Servlet-based web framework for handling HTTP requests and responses. A request first reaches DispatcherServlet, which acts as the front controller. It uses request mapping to find the correct controller method. Spring resolves method arguments, validates input, runs the controller, and converts the result into HTML, JSON, XML, or text. Centralized exception handling keeps error responses consistent. The main trade-off is that Spring MVC uses a blocking, thread-based model, while Spring WebFlux uses a non-blocking reactive model for high-concurrency workloads.

Spring MVC helps a Java application receive a web request, choose the correct code, and send a response back. The goal is to make this flow clear and organized. The main challenge is understanding how the request reaches the correct controller, how request values become Java method arguments, how those values are checked, and how the returned Java value becomes an HTTP response. I would explain the same request-response path shown in the diagram, starting with the client and ending when DispatcherServlet sends the response back.

• Do you want me to explain both REST responses and HTML views?
• Should I also compare Spring MVC with Spring WebFlux?

I would start by saying that Spring MVC is Servlet-based. The client sends an HTTP request such as GET /users/123. DispatcherServlet receives the request first. It is the front controller, which means one central component coordinates web requests. It does not contain the application’s business logic. It receives the request, finds the correct handler, coordinates the processing steps, and sends the final HTTP response back to the client.

DispatcherServlet uses Handler Mapping to find the correct controller method. Request mapping connects a URL and HTTP method to Java code. Common annotations include @RequestMapping and @GetMapping. In the diagram, the example controller has @RequestMapping("/users") and @GetMapping("/{id}"). Together, they match a request such as GET /users/123. Handler Mapping identifies the controller method that should handle that request.

Spring MVC resolves request data into Java method arguments before normal controller processing continues. The diagram includes path values, query parameters, headers, request bodies, and cookies. Common annotations include @PathVariable, @RequestParam, and @RequestBody. The example uses @PathVariable Long id and an optional @RequestParam named includePosts. Spring can also run Bean Validation. Annotations such as @Valid and @NotNull help check incoming values before the application uses them.

The controller contains the Java method that handles the request. It may perform business logic or call another application service. In the example, getUser calls userService.findUser(id, includePosts). The controller then returns data or a view. A traditional Spring MVC controller can return a model and view for HTML. A REST controller normally returns data that becomes the HTTP response body.

For response bodies, Spring MVC uses HTTP message converters. These components convert Java objects into formats such as JSON, XML, or plain text. The diagram’s example produces JSON with fields such as id, name, and email. @ResponseBody tells Spring to write a returned value into the HTTP response body. @RestController provides response-body behavior for its controller methods. For an HTML response, the controller can instead return a model and view.

If processing fails, Spring MVC supports centralized exception handling. The diagram shows @ControllerAdvice handling exceptions and creating proper responses. This keeps common error handling outside individual controller methods. On the normal path, Spring converts the result when needed. DispatcherServlet then sends the HTTP response back to the client. The client can receive HTML or a response body such as JSON.

The main difference is the execution model. Spring MVC uses the Servlet API and a blocking, thread-based model. The diagram shows a Servlet-and-threads stack with servers such as Tomcat, Jetty, and Undertow. Spring WebFlux is non-blocking and reactive. It commonly uses Reactor types such as Mono<T> and Flux<T> with an event-loop model, and the diagram shows Netty as its default server. MVC is a strong fit for traditional applications and blocking database calls. WebFlux is useful when high concurrency, streaming, or real-time work is the main requirement.

The benefit of Spring MVC is that its request flow is easy to understand. DispatcherServlet gives the application one central entry point. Handler Mapping connects requests to controller methods. Automatic method argument binding and validation reduce repeated code. HTTP message converters make Java-to-JSON or Java-to-XML responses simple. Centralized exception handling also keeps error handling consistent. The downside is the blocking thread model. A request can keep a thread busy while waiting for database or network work. This works well for many traditional applications, but very high concurrency may need more threads and resources. Spring WebFlux uses non-blocking processing and can handle many waiting operations with fewer threads. However, reactive code is more complex. We accept Spring MVC when simplicity, familiar programming, and compatibility with blocking libraries matter more than reactive scalability.

Jakarta Persistence, commonly called JPA, is the Java specification for object-relational persistence. Hibernate ORM is a popular implementation of that specification. JPA defines standard APIs and behavior, while Hibernate performs the actual ORM work and also provides additional Hibernate-specific features.

JPA and Hibernate help a Java program save, find, change, and remove information in a database without making the developer write every database instruction by hand. One provides a common set of rules that Java programs can follow. The other is a tool that follows those rules and performs the real work. Developers describe how Java objects match stored information, and the tool handles many routine operations. This makes development easier, but developers still need to understand what work is happening behind the scenes so they can prevent slow, incorrect, or wasteful behavior.

1. Would you like only the basic difference between JPA and Hibernate, or should I also explain persistence context, transactions, lazy loading, and generated SQL?
2. Would you like me to mention Hibernate-specific features beyond the Jakarta Persistence standard?

Jakarta Persistence, historically known as JPA, is a specification. A specification defines standard interfaces, annotations, and expected behavior for persisting Java objects in relational databases. It is not itself the complete persistence engine.

Hibernate ORM is a widely used ORM implementation that supports Jakarta Persistence. ORM means object-relational mapping. It maps Java objects and fields to relational database tables and columns. Hibernate also provides features beyond the standard Jakarta Persistence API.

An entity is a Java class whose state can be persisted in a database. It is normally marked with @Entity. Mapping annotations such as @Table, @Id, @Column, @OneToMany, and @ManyToOne describe how Java classes, fields, identifiers, and relationships correspond to database structures.

A persistence context is a set of entity instances managed as one unit by the persistence provider. Within one persistence context, one database identity corresponds to one managed entity instance. The provider tracks changes to managed entities and can synchronize those changes with the database.

EntityManager is the main Jakarta Persistence interface used to interact with a persistence context. It can make new entities persistent, find entities by identifier, remove entities, and execute queries. In framework-managed applications, such as many Spring applications, the framework commonly creates and manages the EntityManager and its lifecycle. In a standalone Jakarta Persistence application, an EntityManager can be created from an EntityManagerFactory.

Database modifications normally happen inside a transaction. A transaction defines a unit of work that either commits successfully or is rolled back. Calling persist() does not necessarily execute an INSERT immediately. The provider may delay SQL until the persistence context is flushed. A flush synchronizes pending in-memory entity changes with the database, but flush is not the same as commit. The transaction can still be rolled back after a flush if the transaction has not committed.

Hibernate generates SQL for many entity operations and ORM queries. This reduces repetitive persistence code, but generated SQL still matters. A poor mapping or query can create unnecessary joins, retrieve too much data, or execute too many statements.

Lazy loading means related data can be deferred until the application first accesses it rather than always loading it with the original entity. This can avoid unnecessary database work, but it has tradeoffs. Accessing an unloaded lazy association after the required persistence context is unavailable can fail. Repeatedly accessing lazy associations can also create the N+1 query problem.

The N+1 problem commonly occurs when one query loads a collection of parent entities and then additional queries load related data separately for many or all of those parents. The exact number of extra queries depends on the mapping, fetch strategy, access pattern, and provider optimizations, so developers should inspect the actual SQL instead of assuming a fixed query count.

The main difference is therefore simple: Jakarta Persistence defines the portable persistence contract, while Hibernate ORM is software that implements that contract and adds its own capabilities. Using standard Jakarta Persistence interfaces can reduce direct dependence on Hibernate-specific APIs, although an application can deliberately use Hibernate-specific features when they provide a useful benefit.

Developers still need SQL and database knowledge when using Hibernate. They should understand joins, indexes, constraints, transactions, locking, query plans, and the SQL generated by the ORM. An ORM makes database access more convenient, but it does not automatically make database access efficient or correct.

1. State the core distinction: Jakarta Persistence/JPA is a specification, while Hibernate ORM is an implementation.
2. Explain entities and mappings.
3. Explain EntityManager and the persistence context.
4. Explain transaction boundaries, flushing, and commit.
5. Explain generated SQL and lazy loading.
6. Mention the N+1 query risk.
7. Finish by explaining why SQL, indexes, constraints, transactions, and query-plan knowledge are still necessary.

There is no single time or memory complexity for JPA or Hibernate because the real cost depends on the SQL generated, the amount of data loaded, the mappings, and the database execution plan. ORM also uses application memory to keep managed entities and track changes. Lazy loading can avoid loading unused data, but careless access can cause many extra queries. A large persistence context can use significant memory and make change tracking more expensive. Maintenance is often easier because standard mappings and APIs reduce repetitive persistence code, but developers still need to monitor generated SQL, transaction boundaries, indexes, and query plans.

A common mistake is saying JPA and Hibernate are the same thing. Jakarta Persistence/JPA is the standard contract, while Hibernate ORM is an implementation with additional features. Another mistake is assuming persist() always executes SQL immediately. Candidates may also confuse flush with commit, assume lazy loading is always faster, overlook the N+1 query problem, or believe an ORM removes the need to understand SQL, indexes, constraints, transactions, locking, and query plans.

Start with one clear sentence: JPA is the specification, and Hibernate is an implementation. Then briefly explain entities, EntityManager, persistence context, transactions, generated SQL, and lazy loading. Finish by saying that strong Hibernate developers still inspect SQL and understand database fundamentals.

WHERE filters individual rows before GROUP BY and aggregation. HAVING filters groups after aggregation. Use WHERE for source-row conditions, such as status = 'PAID', and HAVING for group conditions, such as SUM(amount) > 1000.

This question asks when SQL should remove individual records and when it should remove completed summaries. The first choice checks each original row before rows are combined or totals are calculated. The second choice checks the finished groups, such as departments with more than five employees. This difference matters because filtering earlier changes which rows are included in each total, while filtering later decides which completed totals are returned. Choosing the correct clause keeps the result accurate, makes the query easier to understand, and can avoid processing rows that are not needed.

Useful clarifying questions:

• Are we filtering individual rows, grouped results, or both?
• Does the condition depend on an aggregate value such as COUNT, SUM, or AVG?

WHERE filters source rows before GROUP BY and aggregate calculations are logically performed. It is used for conditions that can be evaluated for each individual row, such as status = 'PAID', country = 'US', or order_date >= DATE '2026-01-01'.

HAVING filters the groups produced by grouping and aggregation. It is commonly used when the condition depends on an aggregate result, such as COUNT(*) >= 5, SUM(amount) > 1000, or AVG(score) >= 80.

For example, suppose the requirement is to find customers whose paid orders during 2026 total more than 1,000. WHERE first keeps only paid orders from 2026. SQL then groups those remaining rows by customer and calculates each customer's total. HAVING removes customer groups whose calculated total is not greater than 1,000.

The practical rule is:

• Use WHERE for conditions on individual input rows.
• Use HAVING for conditions on grouped or aggregated results.
• Use both when the input rows and the completed groups require separate filtering.

A HAVING condition does not always need to contain an aggregate function. It may also reference a grouping column, depending on the database and query. However, when an equivalent condition can safely be applied to each source row, placing it in WHERE is usually clearer and may reduce the rows that must be grouped.

Some database systems also allow HAVING without an explicit GROUP BY. In that case, the qualifying input is generally treated as one group. This behavior is supported differently across database products, so it should not be relied on when portability is important.

SQL is declarative, so the database optimizer may rewrite predicates or push safe conditions earlier. The logical distinction still remains: WHERE determines which rows participate in grouping and aggregation, while HAVING determines which resulting groups are returned.

1. Identify each filtering condition in the requirement.
2. Decide whether the condition applies to an individual source row or to a completed group.
3. Put row-level conditions in WHERE.
4. Apply GROUP BY when grouped aggregation is required.
5. Calculate aggregate values such as COUNT, SUM, or AVG.
6. Put aggregate or group-result conditions in HAVING.
7. Verify that moving a condition would not change which rows contribute to the aggregates.

The exact execution cost depends on the database, indexes, statistics, data distribution, grouping columns, and query plan. A selective WHERE condition can reduce the number of rows read, grouped, sorted, hashed, or aggregated. This may lower CPU use, memory pressure, temporary-disk use, and execution time. HAVING normally filters after group results are formed logically, although an optimizer may push safe predicates earlier. No fixed time or memory complexity can be stated without the schema and execution plan.

Common mistakes include writing an aggregate condition at the same query level in WHERE, such as WHERE COUNT(*) > 5; treating WHERE and HAVING as interchangeable; putting every condition in HAVING even when it applies to source rows; forgetting that WHERE changes which rows contribute to an aggregate; and selecting nonaggregated columns that are not included in GROUP BY. Another mistake is assuming that an optimizer will always move a row-level HAVING condition into WHERE. Such rewrites are database-specific and are not guaranteed.

Start with one sentence: WHERE filters rows before grouping, while HAVING filters groups after aggregation. Then show a query that uses both clauses and explain how WHERE changes the aggregate input while HAVING filters the completed aggregate results.

INNER JOIN returns only matching rows. LEFT JOIN keeps all left-side rows, RIGHT JOIN keeps all right-side rows, and FULL OUTER JOIN keeps all rows from both sides. In an outer join, columns belonging to a missing match contain NULL.

This question asks how to combine information from two lists when some entries have a partner and others do not. The important decision is which unpaired entries must remain in the final result. You may want only paired entries, every entry from the first list, every entry from the second list, or every entry from both lists. When an entry has no partner but must still be shown, the missing information appears as empty. Choosing the wrong method can silently remove important records or produce unexpected extra rows.

Useful clarifying questions:

• Which table must keep all of its rows?
• Should unmatched rows from either table appear?
• Are the join columns unique, nullable, or duplicated?
• Which database product must run the query?

A SQL join combines rows from two table expressions by evaluating a join condition, commonly an equality such as customers.customer_id = orders.customer_id.

INNER JOIN returns only row combinations for which the join condition evaluates to true. Rows without a match are excluded from the result. Use it when the result should include only records that have related data on both sides, such as orders that reference an existing customer.

LEFT JOIN, also called LEFT OUTER JOIN, returns every row from the left table expression and any matching rows from the right table expression. When no right-side match exists, the selected right-side columns are returned as NULL. Use it when the left side is the complete population that must be preserved, such as all customers including customers with no orders.

RIGHT JOIN, also called RIGHT OUTER JOIN, returns every row from the right table expression and any matching rows from the left table expression. When no left-side match exists, the selected left-side columns are returned as NULL. It is logically equivalent to swapping the table positions and using a LEFT JOIN. Many teams prefer LEFT JOIN for consistency and readability, but RIGHT JOIN is valid where the database supports it.

FULL OUTER JOIN returns matching row combinations plus unmatched rows from both sides. For an unmatched row, columns from the missing side are returned as NULL. Use it for reconciliation, migration validation, dataset comparison, and finding records present in one source but absent from the other. PostgreSQL, SQL Server, and Oracle support FULL OUTER JOIN. MySQL does not provide a native FULL OUTER JOIN operator, so a carefully designed alternative such as combining complementary outer joins with UNION ALL may be required.

The words left and right refer only to the table-expression positions in the SQL statement. They do not describe business priority.

A join does not necessarily return one result row for each source row. It returns every row combination that satisfies the join condition. If a key occurs three times on one side and twice on the other, that key can produce six matching result rows.

NULL also requires care. A comparison such as column = NULL is not true; use IS NULL to test for missing values. In addition, placing a condition on the non-preserved side in the WHERE clause can remove NULL-extended rows and make an outer join behave like an inner join. Put that condition in the ON clause when it should restrict matches without removing preserved rows.

1. Identify the two table expressions and the columns that define a valid relationship.
2. Decide whether unmatched rows should be excluded or preserved.
3. Use INNER JOIN when only matches are required.
4. Use LEFT JOIN when all left-side rows must remain.
5. Use RIGHT JOIN when all right-side rows must remain, or swap the table order and use LEFT JOIN.
6. Use FULL OUTER JOIN when unmatched rows from both sides must remain.
7. Check whether duplicate join values can multiply result rows.
8. Place predicates in ON or WHERE according to whether they should restrict matching or remove final rows.
9. Verify database support and inspect the execution plan when performance matters.

A join has no single fixed time or memory cost because the database chooses an execution strategy. A nested-loop join may repeatedly search one input for rows from the other input. A hash join may build an in-memory hash table and can spill temporary data to disk when memory is insufficient. A merge join usually benefits from inputs that are already ordered or can be sorted. Table sizes, matching-row counts, indexes, data distribution, statistics, available memory, and the selected columns all affect cost. An index on a useful join column can reduce lookup work, but indexes consume storage and add work to inserts, updates, and deletes. Duplicate join values can greatly increase result size, network transfer, application memory use, and processing time.

Common mistakes include reversing which side a LEFT or RIGHT JOIN preserves, forgetting that table order defines left and right, and assuming that each source row produces at most one output row. Duplicate join values can multiply matches. Another frequent mistake is putting a condition on the non-preserved table in the WHERE clause after an outer join, which removes NULL-extended rows and may effectively turn it into an INNER JOIN. Other mistakes include using = NULL instead of IS NULL, joining on incomplete or incorrect keys, using DISTINCT to hide an incorrect join, and assuming every database supports RIGHT JOIN or FULL OUTER JOIN.

Start with one decision: which unmatched rows must remain? Explain all four joins with the same two-table example, mention NULL for missing matches, and then warn about duplicate-key row multiplication and WHERE-clause filters on outer joins.

A correlated subquery is an inner query that uses a value from the current row of the outer query. Unlike an ordinary subquery, it cannot normally run independently. It is commonly used with EXISTS, NOT EXISTS, or row-specific comparisons.

This question asks about a smaller search placed inside a larger search. The key idea is that the smaller search uses information from the current item being checked by the larger search. Its result can therefore be different for each item. A normal smaller search does not depend on the larger one and can usually be checked separately. The interviewer wants to know whether you can explain this difference, recognize useful cases, identify possible errors, and understand that the database may reorganize the work internally to make it more efficient.

Useful clarifying questions:

• Would you like an EXISTS example or a comparison example?
• Should I compare it with a join or a window function?
• Are we discussing logical SQL behavior or a particular database execution plan?

A correlated subquery is a subquery that references one or more columns from an outer query. The outer query provides values from its current candidate row, and the inner query uses those values when evaluating related data.

For example, suppose each employee belongs to a department. To find employees whose salary is above their own department's average, the inner query can calculate the average salary for the department of the current outer employee. The reference e.department_id connects the inner query to the outer row.

An ordinary, or uncorrelated, subquery does not reference the outer query. It can normally be evaluated independently. For example, a subquery that calculates the average salary for the entire company has the same result regardless of which outer employee is being considered.

The logical model is that the correlated condition is evaluated in the context of each candidate outer row. However, this does not mean that the database must physically rerun the inner query once for every row. The optimizer may transform the query into a join, semi-join, anti-join, grouped operation, or another equivalent plan.

Correlated subqueries are especially natural with EXISTS and NOT EXISTS. EXISTS returns true when the inner query can produce at least one matching row. The values in its select list are not used, so SELECT 1 is commonly written to show that only existence matters. NOT EXISTS returns true when no matching row exists and is often safer than NOT IN when the compared inner column may contain NULL.

A correlated scalar subquery used with operators such as =, >, or < must return at most one value for each outer row. If it returns multiple rows, most databases raise an error. An aggregate such as AVG normally produces one row, but it can return NULL when no qualifying non-NULL values exist. A comparison with that NULL result is unknown and does not pass a WHERE condition unless the query handles the case explicitly.

Use a correlated subquery when it expresses a row-dependent rule clearly. Consider a join, grouped derived table, common table expression, or window function when it is easier to understand, avoids repeated logical work, or produces a better plan. Performance depends on the database engine, indexes, statistics, data distribution, and optimizer decisions. Important production queries should be tested with representative data and inspected using the actual execution plan.

1. Identify the outer query and the candidate rows it produces.
2. Inspect the inner query for references to an alias defined by the outer query.
3. Explain that this outer reference makes the inner result depend on the current outer row.
4. Contrast it with an ordinary subquery that has no outer reference and can normally run independently.
5. Determine whether the subquery is used for existence checking, a scalar comparison, or another row-dependent rule.
6. Check scalar-subquery cardinality and NULL behavior where applicable.
7. Explain that the optimizer may rewrite the physical execution while preserving the same logical result.
8. For production use, compare suitable alternatives and inspect the actual execution plan with representative data.

A correlated subquery has no single fixed cost. In a simple mental model, the database may need to check related inner data for every outer row. If there are N outer rows and an unindexed inner scan examines M rows each time, the work can approach N multiplied by M. A suitable index, early EXISTS termination, cached work, or an optimizer rewrite can reduce that cost substantially. Memory use depends on the chosen physical plan. A nested-loop plan may use little extra memory, while a rewritten hash join, sort, or aggregation may require memory proportional to an intermediate result and may spill to temporary storage. Deeply nested correlated logic can also increase maintenance and testing cost.

Common mistakes include calling every nested query correlated; failing to identify the outer-column reference; claiming that the database always executes the inner query once per outer row; using a scalar correlated subquery that can return multiple rows; ignoring the effect of NULL from an empty aggregate result; using NOT IN without considering NULL values; selecting unnecessary columns inside EXISTS; writing ambiguous unqualified column names; assuming that correlated syntax is always slower than a join; and making performance claims without checking indexes, statistics, representative data, and the actual execution plan.

Begin with the dependency: the inner query references the current outer row. Contrast it with an independent subquery, show one concise SQL example, mention scalar and NULL edge cases, and explain that the optimizer may choose a different but equivalent physical plan.

GROUP BY places rows with matching grouping values into the same group. Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX then calculate one value for each group. WHERE filters rows before grouping, while HAVING filters groups after aggregation.

This question asks how many records can be arranged into matching sets and then summarized. For example, a company may want one result for each customer instead of one result for every order. The answer should explain what decides which records belong together, what summary is calculated for each set, and why the result usually contains fewer lines. It should also explain the difference between removing individual records before the sets are created and removing completed sets after their summary values have been calculated.

Useful clarifying questions:

• Should I cover grouping by multiple columns?
• Should I explain WHERE versus HAVING?
• Should I include NULL behavior and performance considerations?

GROUP BY organizes the rows that remain after FROM, joins, and WHERE filtering into groups. Rows belong to the same group when their grouping expressions have equal grouping values. Aggregate functions then calculate one result for each group.

For example, grouping orders by customer_id creates one group for each customer. COUNT(*) counts the rows in each customer's group, and SUM(amount) adds the non-NULL amounts in that group. The result normally contains one row per customer instead of one row per order.

A useful conceptual processing order is:

1. FROM and joins produce the input rows.
2. WHERE removes individual rows.
3. GROUP BY forms groups from the remaining rows.
4. Aggregate functions calculate values for each group.
5. HAVING removes groups that do not satisfy its condition.
6. SELECT produces the requested result expressions.
7. ORDER BY sorts the final rows.

The exact physical execution chosen by the database may differ from this conceptual order as long as the result remains equivalent.

In a grouped query, each selected expression must be valid for one result row per group. Portable SQL should select only:

• Expressions included in the GROUP BY clause.
• Aggregate expressions such as COUNT(*) or SUM(amount).
• Expressions derived only from grouped values or aggregate results.

Some databases permit a non-grouped column when they can prove that it is functionally dependent on the grouped columns. Other databases or SQL modes apply different rules. For portable and predictable SQL, explicitly group every selected non-aggregate value required to identify the group.

Common aggregate functions are:

• COUNT(*): counts every row in the group.
• COUNT(column): counts rows where that expression is not NULL.
• SUM(column): adds the non-NULL numeric values.
• AVG(column): averages the non-NULL numeric values.
• MIN(column) and MAX(column): return the smallest and largest non-NULL values.

Without GROUP BY, an aggregate query treats all qualifying rows as one implicit group. It usually returns one summary row. If no rows qualify, COUNT(*) returns zero, while aggregates such as SUM, AVG, MIN, and MAX normally return NULL. With an explicit GROUP BY, no qualifying input rows normally produce no groups and therefore no result rows.

When several columns appear in GROUP BY, SQL creates one group for each distinct combination of their grouping values. For example, grouping by customer_id and status produces a separate group for each customer-and-status combination. Adding an unnecessary grouping column changes the result granularity and may create many more rows.

WHERE and HAVING perform different jobs. WHERE filters individual rows before groups and aggregate results are created. HAVING filters groups after aggregation. For example, WHERE status = 'COMPLETED' removes individual orders, while HAVING SUM(amount) > 1000 keeps only customer groups whose total exceeds 1000. A non-aggregate condition should normally be placed in WHERE when it is logically equivalent, because early filtering can reduce the amount of data processed.

For grouping, all NULL values of the same grouping expression are placed into one group. Most aggregate functions ignore NULL inputs, but COUNT(*) counts the row itself. Therefore, COUNT(*) and COUNT(column) can return different values. AVG(column) divides the sum of non-NULL values by the count of non-NULL values, not by the total number of rows.

A condition in HAVING should be written using valid grouped or aggregate expressions according to the database's rules. Column aliases in GROUP BY or HAVING are database-specific, so repeating the expression or using a subquery or common table expression is safer when portability matters.

For large queries, performance depends on the number of qualifying rows, the number and width of distinct grouping keys, joins, available memory, indexes, and the database's execution plan. A database commonly uses hash aggregation, sort-based aggregation, or an index-supported ordered strategy. Hash aggregation generally needs memory related to the number and size of groups. Sort-based aggregation must order the relevant rows and may spill temporary data to disk when memory is insufficient. An index may help filtering or provide a useful row order, but it does not guarantee that grouping will avoid scanning, sorting, hashing, or temporary storage. Production performance should be verified with the database's actual execution plan and representative data.

1. Identify the input table or joined rows.
2. Put row-level filters in WHERE.
3. Choose the expression or expressions that define one group.
4. Select only grouped values, aggregate results, or valid expressions derived from them.
5. Apply COUNT, SUM, AVG, MIN, MAX, or another required aggregate.
6. Put aggregate-based filters in HAVING.
7. Use ORDER BY only when a defined output order is required.
8. Test NULL and empty-input behavior.
9. For large data sets, inspect the actual execution plan and measure the query with representative data.

The database must examine the qualifying rows and organize them into groups. A hash-based plan is commonly close to one grouping operation per input row, but it needs memory for the distinct groups and their running aggregate values. A sort-based plan must order rows by the grouping keys, which is generally more work as the input grows and may require temporary disk space. Wide grouping keys, many distinct groups, large joins, and insufficient memory increase cost. Early WHERE filtering can reduce the input. Indexes can help filtering or ordering, but they consume storage and add maintenance work to inserts, updates, and deletes.

Common mistakes include selecting a non-aggregate value that does not validly identify the group, placing an aggregate condition in WHERE, using HAVING for a row-level condition that belongs in WHERE, assuming COUNT(column) counts NULL values, forgetting that AVG ignores NULL values, adding unnecessary grouping columns and changing the result granularity, expecting an explicit GROUP BY query with no matching rows to return one row, relying on database-specific alias or functional-dependency behavior, assuming result order without ORDER BY, and claiming that an index automatically removes aggregation cost.

Start with the phrase "one result row per group." Give a small customer-orders example, distinguish WHERE from HAVING, explain COUNT(*) versus COUNT(column), and mention that multiple grouping columns change the result granularity. Finish by saying that large grouped queries should be checked with the actual execution plan.

I would first confirm that “second highest” means the second distinct salary. For only the value, I would find the maximum salary below the overall maximum. If employee rows are also needed, I would use DENSE_RANK so tied salaries receive the same rank.

This question asks you to examine employee pay amounts and return the amount immediately below the largest different amount. The important detail is how repeated amounts should be treated. If several employees receive the highest pay, they normally count as one pay level, and the next lower amount is the answer. You should also consider missing pay values, a table with no rows, and a table with only one different pay amount. A strong answer explains these cases and chooses a method that matches whether only the amount or complete employee details are required.

Useful clarifying questions:

• Does “second highest” mean the second distinct salary?
• Should I return only the salary or all employees earning it?
• Which database system should the SQL target?

I would normally interpret the requirement as the second highest distinct, non-NULL salary.

For only the salary value, a portable and clear approach is:

SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < ( SELECT MAX(salary) FROM employees );

The inner query finds the highest non-NULL salary. The outer query considers only salaries below that value and returns the largest one. Duplicate highest salaries do not change the result because every value equal to the maximum is excluded. Standard aggregate functions such as MAX ignore NULL values. If the table is empty, all salaries are NULL, or fewer than two distinct non-NULL salaries exist, the result is NULL.

A sorting approach is also possible:

SELECT DISTINCT salary FROM employees WHERE salary IS NOT NULL ORDER BY salary DESC OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

This sorts the distinct salary values from highest to lowest, skips the first value, and returns the next one. Pagination syntax differs by database. PostgreSQL and MySQL commonly use LIMIT 1 OFFSET 1, while SQL Server can use OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY with ORDER BY.

If the requirement is to return all employees who earn the second highest distinct salary, use DENSE_RANK:

WITH ranked_salaries AS ( SELECT employee_id, employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees WHERE salary IS NOT NULL ) SELECT employee_id, employee_name, salary FROM ranked_salaries WHERE salary_rank = 2;

DENSE_RANK gives equal salaries the same rank and assigns consecutive ranks to different salary values. Therefore, every employee tied at the second highest distinct salary receives rank 2.

ROW_NUMBER is not appropriate when ties must share a rank because it numbers individual rows. RANK also differs from DENSE_RANK because it leaves gaps after ties. For example, if two employees share the highest salary, RANK assigns the next lower salary rank 3 rather than rank 2.

For a single salary value, the nested MAX query is concise and broadly portable. For employee details, ties, or additional salary levels, DENSE_RANK is usually easier to extend and explain.

1. Confirm whether duplicate salaries count once; the usual requirement is the second distinct salary. 2. Decide whether the output should contain only the salary value or complete employee rows. 3. Exclude NULL salaries from ranking or comparisons. 4. For only the value, find the overall maximum and then the maximum salary below it. 5. For employee rows, assign DENSE_RANK values in descending salary order and select rank 2. 6. Define the expected result when fewer than two distinct non-NULL salaries exist.

Without a useful index, the database may scan the salary column for the inner MAX and again for the outer MAX, so the work is proportional to the number of rows, while the query itself needs little additional working memory. A database optimizer may choose a different physical plan. An index whose leading column is salary can help find the largest values with fewer reads, although the exact benefit depends on the database, index structure, table statistics, and execution plan. DISTINCT with ORDER BY and DENSE_RANK may require sorting or an index-ordered scan. A sort can use memory proportional to the rows or distinct values being processed and may spill to temporary disk space when the available memory is insufficient.

Common mistakes include returning the second physical row instead of the second distinct salary, forgetting DISTINCT in an OFFSET-based query, using ROW_NUMBER when tied salaries should share a rank, and using RANK while expecting consecutive ranks after ties. Other mistakes include ignoring NULL values, failing to define the result when fewer than two distinct salaries exist, assuming pagination syntax is identical across databases, and returning one arbitrary employee when several employees share the second highest salary.

State the duplicate-salary assumption first. Present the nested MAX query for a single value, then explain that DENSE_RANK is better when employee rows or ties must be returned. Briefly mention NULL handling, insufficient distinct values, and database-specific pagination syntax.