When interviewers say “SQL coding questions,” they expect you to write actual SQL statements to meet specific requirements. These questions test your ability to translate real-world tasks into correct SQL queries. Below, we present a series of SQL coding challenges similar to those asked in interviews (ranging from basic data manipulation to more complex analytics), each followed by a solution and explanation. Work through each to sharpen your skills – and don’t forget to practice writing and running the queries yourself!
Question: “List the first name, last name, and email of all users who have an email address ending in @example.com.”
This question tests basic SELECT syntax with a filtering condition (likely using the SQL LIKE operator for pattern matching).
Solution:
SELECT first_name, last_name, email
FROM users
WHERE email LIKE '%@example.com';
Explanation: This query selects the specified columns from the users table and uses a WHERE clause to filter emails. The pattern '%@example.com' means “any characters before @example.com”. In other words, it finds emails that end with @example.com. The % is a wildcard (matches any sequence of characters)codesignal.com. This will return only those users with an email domain of example.com.
Why it’s asked: It checks that you know how to write a basic SELECT and apply a WHERE filter with a wildcard. Variants might be filtering by other patterns or using ILIKE (case-insensitive) in Postgres, etc. A strong answer would also mention knowing the difference between % and _ wildcards (where _ matches a single character).
Question: “Write a SQL query to retrieve each order’s ID and date from an Orders table, along with the product name from a Products table. The Orders table has a product_id that references Products(product_id).”
This tests the ability to perform an INNER JOIN between two tables on a foreign key relationship.
Solution:
SELECT o.order_id, o.order_date, p.product_name
FROM orders o
JOIN products p
ON o.product_id = p.product_id;
Explanation: We use an INNER JOIN to combine orders (aliased as o) with products (p) on the matching product_id fieldcodesignal.com. The SELECT list picks columns from each table: the order’s ID and date, and the product’s name. Because we’re doing an inner join, the result will include only those orders that have a corresponding product. This query assumes referential integrity (every order’s product_id exists in Products).
In an interview, it’s good to clarify that this is an inner join (which is default when you just write JOIN). You might also mention that if an order could have a product that was deleted (or a null product_id), an inner join would exclude it – and if needed, a LEFT JOIN could be used to still show the order (with product_name as NULL). But with a proper foreign key setup, inner join is appropriate here.
Question: “Increase the salary by 10% for all employees in the Sales department.”
This question checks that you know how to write an UPDATE statement with a WHERE clause to restrict which rows get updated.
Solution:
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';
Explanation: The UPDATE statement targets the employees table. We use SET to modify the salary column by multiplying it by 1.10 (which is a 10% increase). The WHERE department = 'Sales' ensures that only rows where the department is "Sales" are affectedcodesignal.com. All other employees’ salaries remain unchanged.
It’s a simple arithmetic update, but in an interview, be sure to mention that without the WHERE clause, every employee’s salary would be increased – a potentially dangerous bug! So the WHERE is critical. Also, consider mentioning that you assume department is a column in the same table; if departments were normalized into a separate table, you might need a subquery or join in the update. Interviewers may also ask how to do a conditional update, e.g., “set some to +10%, others to +5%,” which could involve a CASE expression.
Question: “Find the top 3 customers who have spent the highest total amount on orders. Output the customer_id and their total spending.”
This requires grouping data by customer and summing order amounts, then selecting the top 3 totals. It tests GROUP BY, aggregate functions, and ORDER BY with LIMIT (or equivalent).
Solution:
SELECT customer_id, SUM(order_amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 3;
Explanation: We group the orders by customer_id and calculate SUM(order_amount) for each customercodesignal.com. The result gives each customer’s total spending. We alias it as total_spent for clarity. Then we sort the results in descending order of total_spent so the highest spenders come first. Finally, LIMIT 3 (this is MySQL/Postgres syntax; in SQL Server you’d use TOP 3 instead) returns only the top three customers.
This query highlights understanding of grouping and aggregation. In an interview, an important detail is to ensure that every column in the SELECT that is not aggregated is also in the GROUP BY (here customer_id is grouped, and we’re not selecting any non-aggregated, non-grouped columns aside from that). Also, mention that the LIMIT 3 is how we restrict to top 3 – different SQL dialects handle limiting differently (e.g., SQL Server’s TOP, Oracle’s rownum or FETCH FIRST).
Question: “List all employees whose salary is above the average salary of their department.”
This is a step up in complexity: for each employee, we need to compare their salary to the average salary of employees in the same department. This typically uses a correlated subquery or a join with aggregation.
Solution:
SELECT employee_id, department_id, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);
Explanation: The subquery inside the WHERE computes the average salary for the department that the outer row e belongs tocodesignal.comcodesignal.com. This is a correlated subquery because it references e.department_id from the outer query. The outer query then checks each employee’s salary against that department average. If the salary is greater, that employee is selected.
Alternatively, you could solve this with a join: join the employees table to a derived table of department_id and avg_salary, then filter where the employee’s salary > avg_salary. But the correlated subquery approach is straightforward and likely what the interviewer is looking for to test subquery understanding.
Important things to mention: correlated subqueries run for each row of the outer query, but SQL query optimizers often handle these efficiently (sometimes as a join under the hood). Also, ensure you understand that the inner WHERE clause uses the outer query’s department_id – that’s the correlation linking each employee to their group’s average.
Each of these challenges covers a fundamental aspect of SQL query writing that is commonly assessed in interviews:
SELECT + WHERE).GROUP BY and using ORDER BY to get top-N results.By practicing these, you demonstrate that you can not only recall SQL syntax but also apply it to solve problems. Interviewers often provide a scenario and expect you to come up with the correct SQL query, as we did above.
After writing your solution, be prepared to explain why it works and discuss alternative solutions. For example, you might explain how the AVG subquery relates to the outer query, or how using a HAVING clause (in challenge 4) could filter groups as well. Showing that you understand the result and edge cases (like ties in top-N, or ensuring the WHERE clause is present to avoid unintended updates) will make you stand out.
Finally, it’s a great idea to test similar queries on your own. Use our Galaxy SQL Playground or any SQL environment to practice these questions – it will help solidify your understanding and reveal any syntax errors before you’re in front of an interviewer!
.avif){kind=logo}
