Start by clarifying the table schema and confirming that a single row with both max and min marks is required. Then write a simple SELECT statement using the MAX() and MIN() aggregate functions on the marks column, optionally aliasing the results for clarity. If the table might be empty, consider how to handle NULLs or use a UNION approach to always return a row.
Pro tip: Mention that using MAX() and MIN() in a single SELECT is efficient because it scans the table only once, and if the table is large, an index on the marks column can speed up the query. Also, be prepared to discuss how to handle ties or multiple students with the same max/min marks if the interviewer asks for more detail.
Confirm the table name, column names, and that the output should be a single row with two columns: maximum and minimum marks. Ask if there are any constraints like handling empty tables or ties.
Use SELECT MAX(marks) AS max_mark, MIN(marks) AS min_mark FROM students; to get the desired result in one row.
If the table could be empty, the query returns NULLs. If you need to return a row even when empty, you might use a UNION with a dummy row or COALESCE to replace NULLs with 0 or another value.
Mention that an index on the marks column can improve performance for large tables, as the database can quickly find the max and min values.
Walk through the query logic, explaining that MAX and MIN are aggregate functions that ignore NULLs and return a single value each, combined into one row.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.