A database (SQLite) running inside your browser comes loaded with practice tables of employees, departments and sales. Write SQL, run it and see the result as a table immediately. Practice SELECT and WHERE, ORDER BY, GROUP BY and HAVING, inner and outer joins and subqueries with ten exercises that check your answers.
Favorites and recently used calculators are stored only in this browser (localStorage).
Exercises
Result
Practice tables
The database runs only inside this page, and the SQL and data you enter are never sent anywhere. If you change tables with INSERT or UPDATE, Reset tables or reloading the page restores them. When checking answers, row order is ignored unless the exercise uses ORDER BY.
See it as a graph
Other inputs stay at their current values while the chosen input varies from 1/4 to 4 times its current value. The red dot is the current value.
The practice tables
The employees table holds emp_id, name, dept_id, salary and hired; departments holds dept_id, dept_name and location; and sales holds sale_id, emp_id, month and amount. employees.dept_id is a foreign key referencing departments.dept_id. The data deliberately includes an employee with no department (dept_id is NULL), an employee whose salary is NULL and a department with no employees, so you can see how NULL behaves in joins and aggregates. Open Practice tables at the bottom of the page to see every table's contents at any time.
SELECT basics: columns, filtering and sorting
List the columns after SELECT, the table after FROM and the row condition after WHERE. SELECT name, salary FROM employees WHERE salary >= 300000 ORDER BY salary DESC; lists employees earning 300000 or more, highest first. NULL means "no value"; test for it with IS NULL or IS NOT NULL, not = NULL. Note that an employee whose salary is NULL matches neither salary >= 300000 nor salary < 300000.
Aggregation: GROUP BY and HAVING
Aggregate functions such as COUNT, SUM, AVG, MAX and MIN return one value per group formed by GROUP BY. WHERE filters rows before grouping, and HAVING filters the aggregated results after grouping. COUNT(*) counts rows, COUNT(column) counts non-NULL values, and AVG(salary) averages only the rows whose salary is not NULL. Check that the answer to exercise 5 is the average of 7 employees, not 8.
Joins: inner and outer
An INNER JOIN returns only pairs that satisfy the ON condition, so an employee with no department and a department with no employees do not appear. A LEFT OUTER JOIN keeps every row of the left table and fills the right table's columns with NULL when nothing matches. With departments on the left joined to employees, counting COUNT(employees.emp_id) shows departments with no employees as 0. COUNT(*) would count the NULL-filled row as one, so it would not give 0.
Subqueries: IN and EXISTS
A SELECT written inside WHERE (a subquery) lets you use its result as a condition. IN checks whether a value is in the list a subquery returns, and EXISTS checks whether the subquery returns at least one row. NOT EXISTS finds rows with no matching rows, such as employees with no sales at all. A subquery that uses a value from the outer row is called a correlated subquery; think of it as being evaluated once for each outer row.
WHERE price >= 150 selects only rows whose price is 150 or more. The ruler, at exactly 150, is included, so three rows are selected: notebook (250), file (300) and ruler (150). ORDER BY price DESC then sorts them from the highest price (descending order), giving file (300), notebook (250), ruler (150). Specifying ASC, or nothing, with ORDER BY sorts from the lowest (ascending order). Without ORDER BY, the order of the result rows is not guaranteed. After SELECT you write the columns to retrieve (here name), after FROM the table, and after WHERE the condition that selects rows.
DISTINCT removes duplicate rows from the result, collapsing identical values into one row. There are three distinct dept values, Sales, Dev and Admin, so the result has three rows. Without DISTINCT, SELECT dept FROM employee returns all six rows, duplicates included. Two rows is the count of only the departments that appear two or more times (Sales and Dev), which corresponds to the result of a query such as SELECT dept FROM employee GROUP BY dept HAVING COUNT(*) >= 2.
COUNT(*) counts rows regardless of whether they contain NULL, so it is 6. With a column specified, as in COUNT(score), only rows where that column is not NULL are counted, so it is 4. AVG(score) divides the sum of the four non-NULL values, 50 + 70 + 90 + 30 = 240, by the number of non-NULL values, 4, giving 240 ÷ 4 = 60. Note that this differs from dividing the sum by the total number of rows, SUM(score) / COUNT(*) = 240 ÷ 6 = 40. To treat NULL as 0 points, replace it with COALESCE(score, 0) before aggregating.
GROUP BY store gathers the rows by store into four groups with totals A = 500 + 700 = 1200, B = 300 + 400 = 700, C = 1200 and D = 200 + 900 = 1100. HAVING SUM(amount) >= 1000 keeps only groups whose total is 1000 or more, so the result has three rows: A, C and D. One row is the result of selecting by the amount of each individual row before grouping, as with WHERE amount >= 1000 (only C). Four rows is the number of groups without HAVING.
An INNER JOIN returns only the combinations of rows that satisfy the ON condition. The two employees with dept_id 10 combine with department 10, and the one employee with dept_id 20 combines with department 20, so the result has three rows. The employee whose dept_id is NULL is not joined to any department, because a comparison with NULL is never true, and disappears from the result. Department 30, which has no employees, does not appear either. Four rows is the count for a left outer join that keeps every row of the employee table (employee LEFT OUTER JOIN department). Twelve rows is the CROSS JOIN (Cartesian product), which forms every combination without a condition: 4 × 3 = 12 rows.
A LEFT OUTER JOIN keeps every row of the left table (here the department table); when the right table has no matching row, it produces one row with the right table's columns filled with NULL. Department 10 has two employees, giving two rows; department 20 gives one row; and department 30, which has no employees, gives one row with a NULL name, for a total of four rows. The two employees whose dept_id is NULL match no department and, being rows of the right table, which is not the one kept in full, do not appear in the result. An inner join would also drop the department 30 row, giving three rows. Swapping the tables to employee LEFT OUTER JOIN department keeps all five employees, giving five rows. With outer joins, it is important to check which table keeps all its rows.
ASELECT name FROM customer WHERE EXISTS (SELECT * FROM orders WHERE orders.cust_id = customer.cust_id)
BSELECT name FROM customer WHERE NOT EXISTS (SELECT * FROM orders)
CSELECT name FROM customer WHERE NOT EXISTS (SELECT * FROM orders WHERE orders.cust_id = customer.cust_id)
Show answer and explanation
Answer: C. SELECT name FROM customer WHERE NOT EXISTS (SELECT * FROM orders WHERE orders.cust_id = customer.cust_id)
EXISTS is a predicate that is true if the subquery in parentheses returns at least one row and false if it returns none; NOT EXISTS is the opposite. Using a column of the outer table inside the subquery, as in orders.cust_id = customer.cust_id, makes it a correlated subquery that checks, for each row of the outer customer table, whether that customer has any orders. Combined with NOT EXISTS, only customers with no orders at all remain. With EXISTS, you get the opposite: customers who have ordered. Without a condition linking the subquery to the customer, the subquery always returns rows as long as the orders table has at least one row, so NOT EXISTS is always false and the result is zero rows. The same result can also be obtained with a LEFT OUTER JOIN, selecting the rows where the orders-side columns are NULL.
FAQ
Do I need to install anything or sign up?
No. The tool uses sql.js, a library that runs SQLite in the browser, so it works as soon as the page opens. The SQL and data you enter are never sent anywhere.
What if I break the tables?
Press Reset tables or reload the page to return to the initial state. Answers are always checked against a fresh database, so grading works even after you change the tables.
How are answers checked?
Your SQL and the model answer each run on a fresh copy of the database, and the number of rows and columns and the values are compared. Row order is ignored unless the exercise uses ORDER BY. Column names and aliases do not matter, so a different query that gives the same result counts as correct.
Which SQL dialect can I use?
SQLite's. SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, joins, subqueries, aggregate functions and CASE expressions all work. Function names and details differ between database products, so check the dialect of the product or exam you are preparing for.
Is this useful for exam preparation?
It helps you try out the ideas exams ask about most: SELECT, joins, GROUP BY with HAVING, subqueries and NULL handling. The tables and question formats differ from real exams, so use it alongside past papers.