Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsStrong SQL interview answers explain both the query and why it works. The questions below cover SELECT structure, filtering, joins, aggregation, set operations, CTEs, ordering, and practical query problems. Examples use standard SQL-style syntax where possible; row-limiting and some details differ between PostgreSQL and Microsoft SQL Server, so confirm executable syntax for your target database.
What is the general shape of a SELECT query?
A SELECT statement returns chosen expressions from rows supplied by its table expressions. A typical query is written in this order:
As an Amazon Associate I earn from qualifying purchases.
SELECT department_id, COUNT(*) AS employee_count
FROM employees
WHERE active = TRUE
GROUP BY department_id
HAVING COUNT(*) > 1
ORDER BY employee_count DESC;FROMidentifies the table inputs and joins.WHEREfilters input rows.GROUP BYforms groups for aggregate calculations.HAVINGfilters those groups.SELECTchooses the output expressions.ORDER BYrequests result ordering; a row-limiting clause can restrict how many rows are returned.
PostgreSQL 17 describes a logical processing sequence in which WITH and FROM are considered, WHERE filters rows, grouping and HAVING are applied, output expressions are computed, and ordering and limits follow. This is an explanatory model, not a promise about the database’s physical execution plan. See the PostgreSQL 17 SELECT documentation.
Free tools Windows power users keep installed
One-click scans. No signup required.
What is the difference between WHERE and HAVING?
WHERE tests individual rows before grouping. HAVING tests groups after aggregate values are available. Put a condition on a raw order row in WHERE, and a condition on the total spend per customer in HAVING.
#1 Best Overall
SELECT customer_id, SUM(amount) AS total_spend
FROM orders
WHERE order_date >= DATE '2025-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 1000;Here the date condition reduces the rows considered; the spending threshold is checked against each customer’s aggregate. The date literal’s exact syntax may vary by SQL dialect. Microsoft also demonstrates combining WHERE, GROUP BY, and HAVING in its SELECT examples.
How do INNER JOIN and LEFT JOIN differ?
An INNER JOIN returns combinations for which the join condition matches. A LEFT JOIN retains every row from its left input; if no right-side row matches, right-side columns are returned as NULL.
SELECT d.department_id, e.employee_id
FROM departments AS d
LEFT JOIN employees AS e
ON e.department_id = d.department_id;This can include departments with no employees. Predicate placement matters: a condition on the right table in WHERE rejects NULL-extended rows and can remove unmatched departments, whereas putting a matching restriction in ON still allows those departments to remain. Join behavior belongs to the table-expression part of SELECT; consult the relevant engine manual for exact dialect semantics. See PostgreSQL 17 SELECT and Microsoft SELECT.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →What does GROUP BY do?
GROUP BY partitions input rows by one or more expressions so aggregates such as COUNT, SUM, and AVG can return a value per group.
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;Selected expressions that are not aggregate calculations must comply with the database’s grouping rules. Microsoft’s SELECT examples show grouped sums and averages.
How do you find duplicate values?
First define what counts as a duplicate: one repeated email, a repeated combination of key fields, or identical full rows. Group by that business key and use HAVING to retain groups occurring more than once.
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;This finds repeated email values, not necessarily duplicate user records. To find duplicate combinations, include all columns that define the key in both SELECT and GROUP BY.
What is the difference between UNION and UNION ALL?
Both set operators combine compatible result sets: corresponding queries need compatible output columns. UNION removes duplicate result rows; UNION ALL keeps them. Use the latter when duplicates are meaningful or should remain in the output.
SELECT email FROM current_users
UNION ALL
SELECT email FROM archived_users;PostgreSQL documents duplicate elimination by default for set operations; Microsoft’s examples illustrate the distinction. See PostgreSQL 17 SELECT and Microsoft SELECT examples.
What is a common table expression?
A common table expression (CTE) is a named query introduced with WITH and referenced by the statement that follows. It can make a multi-stage query easier to read without changing the result logic.
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total_spend
FROM customer_totals
WHERE total_spend > 1000;Do not assume a CTE is always materialized or always faster than an equivalent subquery. PostgreSQL 17 documents circumstances where a multiply referenced WITH query is computed once unless NOT MATERIALIZED is specified. See its SELECT documentation.
Why should you use ORDER BY?
Use ORDER BY whenever the requested output has an order requirement. Without it, the query does not promise a stable sort order; the order observed in one run should not be treated as guaranteed. PostgreSQL explains this in its SELECT documentation.
Rank #4
For a top-N result, sort explicitly and include a tie-breaker if the order among equal values matters. Row-limiting syntax differs: PostgreSQL documents LIMIT and FETCH forms, while SQL Server documents TOP. Check the target engine’s PostgreSQL or SQL Server syntax.
How do you find the highest-paid employee in each department?
Use a window function to rank employees within each department, then select the first row from each ranking. This example chooses exactly one employee per department and uses employee_id as the tie-breaker when salaries match.
WITH ranked_employees AS (
SELECT employee_id, department_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC, employee_id
) AS rn
FROM employees
)
SELECT employee_id, department_id, salary
FROM ranked_employees
WHERE rn = 1;If every employee tied for the top salary should be returned, use a ranking approach that preserves ties rather than filtering to one row number. Verify window-function syntax and behavior against the target database before using the query in production.
Recommended Free Tools
How do you find the most recent order per customer?
Rank orders per customer by date and then by a unique order identifier, so orders sharing a timestamp still have a defined winner.
WITH ranked_orders AS (
SELECT order_id, customer_id, order_date, amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS rn
FROM orders
)
SELECT order_id, customer_id, order_date, amount
FROM ranked_orders
WHERE rn = 1;This returns one row per customer with at least one order. The order_id tie-breaker makes the selection deterministic; choose ascending or descending according to the intended rule.
What is the difference between a join and a subquery?
A join relates table inputs in a query’s FROM clause. A subquery is a query nested inside another query and may provide a scalar value, a set of rows, or an existence test. Some tasks can be expressed either way; select the form that most clearly matches the desired result and check the target engine’s behavior.
For example, a correlated existence test can find customers who have placed an order:
SELECT c.customer_id
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);Microsoft’s SELECT examples illustrate joins and subqueries, including correlated subqueries.
How should you prepare for SQL interview questions?
Practice explaining the stage and purpose of each clause, then work through queries with small sample tables. For every answer, make the data assumptions visible: what defines a duplicate, whether ties should all be returned, and whether unmatched rows must be retained.
- Given
orders(order_id, customer_id, order_date, amount), find customers whose total spend exceeds a threshold and explain why that condition belongs in HAVING. - Given
users(user_id, email), identify repeated emails and state whether distinct users sharing an email count as duplicates. - Combine two compatible query results with and without duplicate removal, then explain which operator preserves repeats.
- Return a stable top-N list by adding an explicit sort and a tie-breaker.
- Name the SQL dialect when syntax is not portable, especially for row limiting and window functions.
Microsoft’s documented SELECT examples are a useful source for practicing joins, subqueries, grouping, and aggregate filters: SELECT examples for SQL Server.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
