SQL Practice Questions: 50 Free Quiz Questions with Answers
Sharpen your database skills with our comprehensive list of 50 free SQL practice questions. This quiz, complete with detailed answers, covers everything from basic SELECT statements and WHERE clauses to advanced JOINs, GROUP BY, SQL aggregate functions, and subqueries. Whether you're preparing for a technical interview or just want to master data manipulation, these SQL interview questions are the perfect tool. Test your knowledge on DDL, DML, window functions, and more with our interactive SQL quiz with answers.
Why Practice SQL Questions?
SQL (Structured Query Language) is the backbone of data management for countless applications. It's the standard language for relational database management systems, including popular platforms like PostgreSQL, MySQL, and Microsoft SQL Server. Mastering SQL interview questions is a non-negotiable skill for data analysts, backend developers, data scientists, and database administrators.
Answering SQL query questions can be daunting. The best way to build confidence is through hands-on practice with real SQL practice questions. This quiz is designed to test your knowledge comprehensively from basic to advanced levels. If you need a refresher on the basics, check out this Complete Guide to SQL.
At Mind Hustle, we believe in the power of active learning. You can read more about us and how it works to see how we're changing professional development. While SQL is a relational database language, it's also helpful to understand its counterparts, like NoSQL databases.
The SQL Quiz: 50 Questions (Basic to Advanced)
The SQL questions and answers are grouped by topic. Click "Show Answer" to reveal the solution and explanation for each question. We'll assume we are working with two tables:
Employees Table:
EmployeeID (INT, Primary Key)FirstName (VARCHAR)LastName (VARCHAR)DepartmentID (INT, Foreign Key)Salary (DECIMAL)HireDate (DATE)
Departments Table:
DepartmentID (INT, Primary Key)DepartmentName (VARCHAR)Location (VARCHAR)
Category 1: Basic SELECT and Filtering
1. What is the basic SQL query to retrieve all columns and all rows from the Employees table?
Show Answer
SELECT * FROM Employees;
Explanation: SELECT * means "select all columns". FROM Employees specifies the table to retrieve data from. This is one of the most fundamental SQL practice questions for beginners.
2. How do you select only the FirstName and LastName of all employees?
Show Answer
SELECT FirstName, LastName FROM Employees;
Explanation: Instead of *, you list the specific column names you want to retrieve, separated by commas. This SQL WHERE clause examples technique focuses your query results.
3. How do you find all employees who have a Salary greater than 50000?
Show Answer
SELECT * FROM Employees WHERE Salary > 50000;
Explanation: The WHERE clause is used to filter rows based on a specified condition in SQL query questions. This is a fundamental concept, much like programming syntax itself.
4. How do you retrieve a list of unique Locations from the Departments table?
Show Answer
SELECT DISTINCT Location FROM Departments;
Explanation: The DISTINCT keyword removes duplicate rows from the result set. This is commonly tested in SQL interview questions.
5. How do you select all employees from the 'Sales' department? (You will need to use DepartmentID). Let's assume 'Sales' is DepartmentID 1.
Show Answer
SELECT * FROM Employees WHERE DepartmentID = 1;
Explanation: The = operator is used for equality checks in the WHERE clause. We will cover how to find the ID from the name 'Sales' in the SQL JOIN examples section.
6. How do you find employees whose Salary is between 60000 and 80000?
Show Answer
SELECT * FROM Employees WHERE Salary BETWEEN 60000 AND 80000;
Explanation: The BETWEEN operator is inclusive, meaning it selects values that are greater than or equal to the first value AND less than or equal to the second value. This is a popular SQL practice questions topic.
7. How do you find all employees hired in the year 2023?
Show Answer
-- For MySQL/PostgreSQL:
SELECT * FROM Employees WHERE YEAR(HireDate) = 2023;
-- For SQL Server:
SELECT * FROM Employees WHERE DATEPART(year, HireDate) = 2023;
-- For SQLite:
SELECT * FROM Employees WHERE STRFTIME('%Y', HireDate) = '2023';
Explanation: Date/time functions are specific to the SQL dialect (e.g., MySQL, PostgreSQL, SQL Server). YEAR() or DATEPART() are common functions to extract parts of a date in SQL query questions.
Category 2: Sorting and Advanced Filtering
8. How do you retrieve all employees, sorted by their Salary in descending order (highest to lowest)?
Show Answer
SELECT * FROM Employees ORDER BY Salary DESC;
Explanation: The ORDER BY clause sorts the result set. DESC specifies descending order. The default is ASC (ascending). This is a foundational concept in SQL interview questions, similar to sorting algorithms in programming.
9. How do you find employees whose FirstName starts with the letter 'J'?
Show Answer
SELECT * FROM Employees WHERE FirstName LIKE 'J%';
Explanation: The LIKE operator is used for pattern matching in SQL WHERE clause examples. The wildcard % matches any sequence of zero or more characters.
10. How do you find employees in DepartmentID 1 (Sales) OR 2 (Marketing)?
Show Answer
-- Using OR:
SELECT * FROM Employees WHERE DepartmentID = 1 OR DepartmentID = 2;
-- Using IN (more efficient):
SELECT * FROM Employees WHERE DepartmentID IN (1, 2);
Explanation: The IN operator is a shorthand for multiple OR conditions and is often more readable and performant in SQL practice questions.
11. How do you find all employees who are NOT in the 'Engineering' department (let's say DepartmentID 3)?
Show Answer
-- Using NOT EQUAL:
SELECT * FROM Employees WHERE DepartmentID != 3;
-- Or
SELECT * FROM Employees WHERE DepartmentID <> 3;
-- Using NOT IN:
SELECT * FROM Employees WHERE DepartmentID NOT IN (3);
Explanation: Both != and <> are standard operators for "not equal" in SQL query questions. NOT IN is useful for excluding a list of values.
12. How do you select an employee's full name as a single column named FullName?
Show Answer
-- For PostgreSQL/Oracle (standard):
SELECT FirstName || ' ' || LastName AS FullName FROM Employees;
-- For MySQL:
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;
-- For SQL Server:
SELECT FirstName + ' ' + LastName AS FullName FROM Employees;
Explanation: String concatenation syntax varies by SQL dialect in SQL interview questions. AS is the keyword used to create a column alias (rename a column in the output).
13. How do you find employees who do not have a DepartmentID assigned? (i.e., the value is NULL)
Show Answer
SELECT * FROM Employees WHERE DepartmentID IS NULL;
Explanation: You cannot use = NULL to check for NULL values in SQL practice questions. You must use the IS NULL operator. Conversely, you would use IS NOT NULL to find rows with a value.
14. How do you select only the top 5 highest-paid employees?
Show Answer
-- For MySQL/PostgreSQL:
SELECT * FROM Employees
ORDER BY Salary DESC
LIMIT 5;
-- For SQL Server:
SELECT TOP 5 * FROM Employees
ORDER BY Salary DESC;
-- For Oracle:
SELECT * FROM Employees
ORDER BY Salary DESC
FETCH FIRST 5 ROWS ONLY;
Explanation: The syntax for limiting results differs across database systems. This SQL query questions example first sorts all employees by salary (highest to lowest) and then takes only the top 5 rows from that sorted set.
15. How do you find employees in 'Sales' (ID 1) who also earn more than 70000?
Show Answer
SELECT * FROM Employees
WHERE DepartmentID = 1 AND Salary > 70000;
Explanation: The AND operator is used to combine multiple conditions in a WHERE clause for SQL interview questions. Both conditions must be true for a row to be returned.
Category 3: Aggregate Functions and Grouping
16. How do you count the total number of employees in the Employees table?
Show Answer
SELECT COUNT(*) FROM Employees;
Explanation: COUNT(*) is an SQL aggregate functions example that returns the total number of rows in a table. COUNT(ColumnName) would count non-NULL values in that specific column.
17. How do you find the average salary of all employees?
Show Answer
SELECT AVG(Salary) FROM Employees;
Explanation: AVG() is an aggregate function that calculates the average value of a numeric column. This is frequently tested in SQL practice questions.
18. How do you find the highest and lowest salaries in the company?
Show Answer
SELECT MAX(Salary), MIN(Salary) FROM Employees;
Explanation: MAX() and MIN() are SQL aggregate functions that return the maximum and minimum values of a column, respectively.
19. How do you find the total number of employees in each department?
Show Answer
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;
Explanation: The GROUP BY clause groups rows that have the same values in specified columns (here, DepartmentID). The COUNT(*) aggregate function then counts the rows within each group. This is a core concept in SQL GROUP BY questions.
20. How do you find the average salary for each department?
Show Answer
SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID;
Explanation: Similar to the previous SQL GROUP BY questions example, GROUP BY creates groups, and AVG(Salary) calculates the average salary for each of those groups.
21. What is the difference between WHERE and HAVING?
Show Answer
Explanation:
WHERE filters rows before any grouping or aggregation happens (it works on individual rows)HAVING filters groups after grouping and aggregation happen (it works on the results of SQL aggregate functions like COUNT() or AVG())
This distinction is crucial in SQL interview questions.
22. How do you find departments that have more than 10 employees?
Show Answer
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 10;
Explanation: You must use HAVING here, not WHERE, in these SQL GROUP BY questions. WHERE COUNT(*) > 10 would fail because WHERE is processed before the COUNT() is calculated.
Category 4: JOINS
23. What is an INNER JOIN?
Show Answer
Explanation: An INNER JOIN (or just JOIN) returns only the rows that have matching values in both tables. If an employee has a DepartmentID that doesn't exist in the Departments table, they will not be included in the result. This is fundamental to understanding SQL JOIN examples.
24. How do you retrieve each employee's FirstName, LastName, and DepartmentName?
Show Answer
SELECT
e.FirstName,
e.LastName,
d.DepartmentName
FROM
Employees e
INNER JOIN
Departments d ON e.DepartmentID = d.DepartmentID;
Explanation: This SQL JOIN examples query joins the two tables ON the column they have in common (DepartmentID). We use aliases (e for Employees, d for Departments) to make the query shorter and clearer. This explores relationships, a key concept in graph algorithms.
25. What is a LEFT JOIN (or LEFT OUTER JOIN)?
Show Answer
Explanation: A LEFT JOIN returns all rows from the left table (the first table listed, e.g., Employees), and the matched rows from the right table. If there is no match, the columns from the right table will be filled with NULL. This is commonly tested in SQL interview questions.
26. How do you find all employees, including those without a department, and show their department name if they have one?
Show Answer
SELECT
e.FirstName,
e.LastName,
d.DepartmentName
FROM
Employees e
LEFT JOIN
Departments d ON e.DepartmentID = d.DepartmentID;
Explanation: If an employee has a DepartmentID of NULL (or one that doesn't exist in Departments), they will still be listed in these SQL JOIN examples, but their DepartmentName will be NULL.
27. How do you find all departments, including those with no employees?
Show Answer
SELECT
d.DepartmentName,
e.FirstName,
e.LastName
FROM
Departments d
LEFT JOIN
Employees e ON d.DepartmentID = e.DepartmentID;
Explanation: This is the reverse of the previous SQL practice questions example. By putting Departments on the left side of the LEFT JOIN, we guarantee all departments will be listed, even if no employees match (in which case FirstName and LastName would be NULL).
28. What is a RIGHT JOIN?
Show Answer
Explanation: A RIGHT JOIN is the mirror image of a LEFT JOIN in SQL JOIN examples. It returns all rows from the right table and matched rows from the left. The previous query (Q27) could be rewritten with a RIGHT JOIN as:
... FROM Employees e RIGHT JOIN Departments d ON ...
Most developers prefer to stick to LEFT JOINs for consistency in SQL query questions.
29. What is a FULL OUTER JOIN?
Show Answer
Explanation: A FULL OUTER JOIN returns all rows when there is a match in either the left or the right table in SQL interview questions. It's a combination of a LEFT JOIN and a RIGHT JOIN. It will show all employees (even those without departments) AND all departments (even those without employees).
30. How do you find departments that have no employees? (Use a JOIN)
Show Answer
SELECT
d.DepartmentName
FROM
Departments d
LEFT JOIN
Employees e ON d.DepartmentID = e.DepartmentID
WHERE
e.EmployeeID IS NULL;
Explanation: We perform a LEFT JOIN from Departments (to keep all departments) in this SQL JOIN examples question. For departments with no employees, the e.EmployeeID column (and all other Employees columns) will be NULL. We then use the WHERE clause to filter for only those NULL rows.
Category 5: Subqueries
31. What is a subquery?
Show Answer
Explanation: A subquery is a SQL query nested inside another query. Subqueries can be used in SELECT, FROM, WHERE, or HAVING clauses. They are powerful tools in SQL subquery examples and SQL interview questions.
32. How do you find employees who earn more than the average salary?
Show Answer
SELECT * FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
Explanation: The subquery (SELECT AVG(Salary) FROM Employees) calculates the average salary first, then the outer query uses that value to filter employees. This is a classic SQL subquery examples question.
33. How do you find the department with the highest number of employees?
Show Answer
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
ORDER BY EmployeeCount DESC
LIMIT 1;
-- Or using a subquery:
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) = (
SELECT MAX(EmployeeCount)
FROM (SELECT COUNT(*) AS EmployeeCount FROM Employees GROUP BY DepartmentID) AS DeptCounts
);
Explanation: Both approaches work in SQL practice questions. The first sorts and limits results; the second uses a subquery to find the maximum count.
34. How do you find employees who work in departments located in 'New York'?
Show Answer
-- Using a subquery:
SELECT * FROM Employees
WHERE DepartmentID IN (
SELECT DepartmentID FROM Departments WHERE Location = 'New York'
);
-- Using a JOIN (often more efficient):
SELECT e.* FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.Location = 'New York';
Explanation: This SQL subquery examples question demonstrates two approaches: subqueries and joins. Both are valid, though JOINs are often more performant.
35. How do you find employees whose salary is higher than anyone in the 'Sales' department?
Show Answer
SELECT * FROM Employees
WHERE Salary > (
SELECT MAX(Salary) FROM Employees WHERE DepartmentID = 1
);
Explanation: The subquery finds the maximum salary in the Sales department (DepartmentID 1), and the outer query finds all employees earning more than that amount. This is an advanced SQL interview questions example.
36. What is a correlated subquery?
Show Answer
Explanation: A correlated subquery is a subquery that references columns from the outer query. It is executed once for each row processed by the outer query, making it potentially slower than non-correlated subqueries. This concept appears frequently in advanced SQL subquery examples.
37. How do you find employees who earn more than the average salary in their own department?
Show Answer
SELECT e1.FirstName, e1.LastName, e1.Salary, e1.DepartmentID
FROM Employees e1
WHERE e1.Salary > (
SELECT AVG(e2.Salary)
FROM Employees e2
WHERE e2.DepartmentID = e1.DepartmentID
);
Explanation: This is a correlated subquery in SQL practice questions. For each employee (e1), the subquery calculates the average salary of their department by referencing e1.DepartmentID.
Category 6: Data Manipulation (INSERT, UPDATE, DELETE)
38. How do you insert a new employee into the Employees table?
Show Answer
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary, HireDate)
VALUES (101, 'John', 'Doe', 1, 60000, '2023-05-15');
Explanation: The INSERT INTO statement adds new rows to a table in SQL query questions. You specify the columns and their corresponding values.
39. How do you update an employee's salary?
Show Answer
UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 101;
Explanation: The UPDATE statement modifies existing data. Always use a WHERE clause to avoid updating all rows accidentally. This is critical in SQL practice questions.
40. How do you delete an employee from the Employees table?
Show Answer
DELETE FROM Employees
WHERE EmployeeID = 101;
Explanation: The DELETE statement removes rows from a table. Always use a WHERE clause to specify which rows to delete in SQL interview questions, or you'll delete all rows!
41. How do you increase all employees' salaries by 10%?
Show Answer
UPDATE Employees
SET Salary = Salary * 1.10;
Explanation: You can use expressions in the SET clause of an UPDATE statement. This SQL query questions example increases each salary by multiplying it by 1.10 (adding 10%).
Category 7: Advanced Topics
42. What is a window function?
Show Answer
Explanation: Window functions perform calculations across a set of rows related to the current row, without collapsing the result set like SQL aggregate functions do with GROUP BY. They use the OVER() clause. Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), and LAG(). These are advanced SQL interview questions topics.
43. How do you assign a row number to each employee ordered by salary?
Show Answer
SELECT
FirstName,
LastName,
Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;
Explanation: ROW_NUMBER() assigns a unique sequential number to each row based on the specified order in SQL practice questions. This is useful for pagination and ranking.
44. How do you find the second-highest salary?
Show Answer
-- Using LIMIT/OFFSET (MySQL/PostgreSQL):
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;
-- Using a subquery:
SELECT MAX(Salary) FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
-- Using window functions:
SELECT Salary
FROM (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees
) AS RankedSalaries
WHERE SalaryRank = 2;
Explanation: Multiple approaches exist for this common SQL interview questions challenge. Window functions with DENSE_RANK() handle ties gracefully.
45. What is a UNION and how does it differ from UNION ALL?
Show Answer
Explanation:
UNION combines the result sets of two or more SELECT statements and removes duplicate rowsUNION ALL combines result sets but keeps all duplicates
Both require the same number of columns with compatible data types. This is frequently tested in SQL query questions.
46. How do you combine results from two queries and remove duplicates?
Show Answer
SELECT FirstName, LastName FROM Employees WHERE DepartmentID = 1
UNION
SELECT FirstName, LastName FROM Employees WHERE DepartmentID = 2;
Explanation: UNION automatically removes duplicate rows if an employee appears in both departments in these SQL practice questions.
47. What is an index and why is it important?
Show Answer
Explanation: An index is a database object that improves the speed of data retrieval operations on a table. It works like a book's index, allowing the database to find rows quickly without scanning the entire table. However, indexes slow down INSERT, UPDATE, and DELETE operations because the index must be updated. This concept is crucial for performance optimization in SQL interview questions.
48. How do you create an index on the Salary column?
Show Answer
CREATE INDEX idx_salary ON Employees(Salary);
Explanation: This creates an index named idx_salary on the Salary column of the Employees table, speeding up queries that filter or sort by salary in SQL query questions.
49. What is a CASE statement and how is it used?
Show Answer
Explanation: A CASE statement is SQL's version of if-then-else logic. It allows you to add conditional logic to queries, returning different values based on specified conditions. This is useful in SQL practice questions for categorizing data.
SELECT
FirstName,
LastName,
Salary,
CASE
WHEN Salary > 80000 THEN 'High'
WHEN Salary > 50000 THEN 'Medium'
ELSE 'Low'
END AS SalaryBracket
FROM Employees;
50. How do you find duplicate records in a table?
Show Answer
SELECT FirstName, LastName, COUNT(*) AS DuplicateCount
FROM Employees
GROUP BY FirstName, LastName
HAVING COUNT(*) > 1;
Explanation: This SQL GROUP BY questions example groups by the columns you want to check for duplicates (FirstName, LastName) and uses HAVING to filter for groups with more than one row. This is a practical SQL interview questions scenario for data quality checks.
Next Steps: Keep Practicing
Congratulations on completing these 50 SQL practice questions! Regular practice with SQL quiz with answers resources like this one is the key to mastering database queries. Whether you're preparing for SQL interview questions or enhancing your data manipulation skills, consistent exposure to SQL JOIN examples, SQL GROUP BY questions, SQL aggregate functions, and SQL subquery examples will build your confidence.
For more structured learning experiences, explore our quiz creation platform where you can test yourself on SQL WHERE clause examples and other advanced topics. Consider diving deeper into related concepts like data structures or algorithms to complement your SQL knowledge.
Ready to take your skills further? Create your own custom SQL query questions quiz on Mind Hustle Playground and track your progress as you master database management!