SQL Interview Programs

Return every employee ID and a salary band: Unknown for NULL, Low below 150, Medium from 150 through 249, and High from 250 upward.

Easy· Filtering ·10 min

Return user ID and preferred contact: email first, then phone, then Unknown. Empty strings are present values and must not be replaced.

Easy· Filtering ·10 min

Return customer ID and name for customers with no order. Orders with NULL customer_id must not suppress the result.

Easy· Subqueries ·10 min

For each non-NULL department ID represented by employees, return employee count, maximum salary, and average salary rounded to two decimals. COUNT includes employees with NULL salary; aggregates ignore NULL salaries.

Easy· Aggregation ·10 min

Return each non-NULL department ID containing at least three employees and its employee count.

Easy· Aggregation ·10 min

Return employee ID, employee name, and manager name when the employee salary strictly exceeds their manager salary. Exclude missing managers and NULL salary comparisons.

Easy· Joins ·10 min

Return order ID, amount, and date for orders in calendar year 2024. Dates are stored as fixed-width YYYY-MM-DD text.

Easy· Dates ·10 min

Return department ID and name for departments having no matching employee.

Easy· Joins ·10 min

Return each non-NULL email used by at least two users, with its occurrence count.

Easy· Data Cleanup ·10 min

Return every customer with their number of orders, including zero. Ignore orders whose customer_id does not match a customer.

Easy· Joins ·10 min

Return each customer who has ordered exactly once in the result, regardless of how many orders they placed.

Easy· Joins ·10 min

Return user ID, a trimmed lowercase name, and its first three characters. Preserve NULL names as NULL.

Easy· Data Cleanup ·10 min

Return each region, its total sales, and its percentage of all sales rounded to two decimals. Amounts and regions are non-NULL; return NULL percentages when the grand total is zero.

Medium· Aggregation ·20 min

For every employee with a non-NULL salary, return both RANK and DENSE_RANK by descending salary across the company. Tied salaries share ranks.

Medium· Ranking ·20 min

Return each order and the cumulative amount across all orders by date then ID. Amounts are non-NULL and dates may tie.

Medium· Window Functions ·20 min

Return employee ID, name, and salary for employees earning strictly more than the average non-NULL salary in their non-NULL department.

Medium· Subqueries ·20 min

Return the latest order for every non-NULL customer_id represented in orders. Break same-date ties by greatest order ID; a customer table match is not required.

Medium· Window Functions ·20 min

For N = 3, return one row with the third distinct highest non-NULL salary. Return NULL if fewer than three distinct salaries exist.

Medium· Ranking ·20 min

Return one row containing the second distinct highest non-NULL salary. Return NULL when fewer than two distinct salaries exist.

Medium· Subqueries ·20 min

Return all pairs of employees sharing a non-NULL department. Output the smaller employee ID first and never include a self-pair or reversed duplicate.

Medium· Joins ·20 min

Return each region with sales totals q1 through q4. Quarter is 1–4, region and amount are non-NULL, and absent quarters have total zero.

Medium· Aggregation ·20 min

Return department ID, employee ID, and salary for all employees tied for the maximum non-NULL salary in each non-NULL department. Exclude departments with only NULL salaries.

Medium· Joins ·20 min

Users may share the same email, including NULL. Return the complete row with the smallest ID per email; treat all NULL emails as a single group. Do not execute DELETE.

Medium· Data Cleanup ·20 min

Return every employee ID, employee name, manager name, and grandmanager name. Preserve employees without a manager or grandmanager and show missing names as NULL.

Medium· Joins ·20 min

Return employees at the top three distinct non-NULL salary levels within each non-NULL department, including all ties.

Medium· Window Functions ·20 min

Return the median non-NULL salary of each non-NULL department, averaging the two middle values for even-sized groups and rounding to two decimals. Omit departments without a non-NULL salary.

Hard· Window Functions ·35 min

Return customers who purchased every product in the catalog at least once. Ignore purchases of unknown products. If the catalog is empty, every customer qualifies.

Hard· Subqueries ·35 min

Return start_value, end_value, and length for each maximal consecutive integer island. Ignore NULLs and deduplicate values before forming islands.

Hard· Window Functions ·35 min

Return every non-NULL suite with its longest adjacent FAIL streak in ascending ID order within that suite. Status is PASS or FAIL; suites with no failures return zero.

Hard· Window Functions ·35 min

Return each non-NULL value occurring in at least three consecutive rows when logs are ordered by unique ID. Consecutive means adjacent rows, not necessarily consecutive numeric IDs.

Hard· Window Functions ·35 min