Learn SQL

Every query runs on real PostgreSQL, in your browser.

For SQL beginners: each lesson explains one idea in short slides, lets you run every example, and ends with exercises that the page checks. There is nothing to install and no account. PostgreSQL downloads once, about 6 MB, then starts in a few seconds.

Lessons

  1. Part 1: one table
  2. 1 Tables, rows and columns
  3. 2 Choosing columns: SELECT
  4. 3 Choosing rows: WHERE
  5. 4 Missing values: NULL
  6. 5 Sorting and limiting: ORDER BY and LIMIT
  7. Part 2: summarizing
  8. 6 Counting and adding up: aggregates
  9. 7 Groups: GROUP BY and HAVING
  10. Part 3: several tables
  11. 8 Primary keys and foreign keys
  12. 9 Joining tables: JOIN
  13. 10 Keeping rows without a match: LEFT JOIN
  14. 11 Asking whether a row exists: EXISTS and NOT EXISTS
  15. 12 Stacking results: UNION
  16. Part 4: changing data
  17. 13 Changing data: INSERT, UPDATE and DELETE
  18. 14 Transactions: BEGIN, COMMIT and ROLLBACK
  19. Part 5: what happens underneath
  20. 15 Indexes, full table scans and EXPLAIN
  21. 16 How a join runs: nested loop, hash join, merge join

To try your own queries on the same data, outside a lesson, open the SQL playground.

The practice database

Every lesson uses the same small shop: customers place orders, each order has lines for products, and employees take the orders. The diagram shows its tables and the foreign keys between them. Hover a table or a column to read its comment: what it holds, and what it links to.

🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) 🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) customer_id 🔗 Foreign Key orders_employee_id_fkey orders ➔ employees ( employee_id ) 🔗 Foreign Key orders_employee_id_fkey orders ➔ employees ( employee_id ) employee_id 🔗 Foreign Key order_items_order_id_fkey order_items ➔ orders ( order_id ) 🔗 Foreign Key order_items_order_id_fkey order_items ➔ orders ( order_id ) order_id 🔗 Foreign Key order_items_product_id_fkey order_items ➔ products ( product_id ) 🔗 Foreign Key order_items_product_id_fkey order_items ➔ products ( product_id ) product_id 🔗 Foreign Key employees_department_id_fkey employees ➔ departments ( department_id ) 🔗 Foreign Key employees_department_id_fkey employees ➔ departments ( department_id ) department_id 🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) 🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) manager_id Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status Table public.order_items ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The lines of an order: one row for each product in it.order_items 🔑 Pk order_items_pkey ( order_id, line_no ) ⧉ order_id * int4 ↗ orders( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The order this line belongs to: an order_id of orders.order_id 🔗 References orders ( order_id ) 🔑 Pk order_items_pkey ( order_id, line_no ) ⧉ line_no * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number of the line within its order. With order_id, it identifies the line.line_no ⧉ product_id * int4 ↗ products( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The product on this line: a product_id of products.product_id 🔗 References products ( product_id ) ⧉ quantity * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces of the product were ordered.quantity ⧉ unit_price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The price of one piece on this line, kept with the order so that a later change of products.price leaves it as it was.unit_price Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock Table public.employees ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who work for the shop.employees 🔑 Pk employees_pkey ( employee_id ) ⧉ employee_id * int4 ↙ employees( manager_id ) ↙ orders( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the employee.employee_id 🔗 Referred by employees ( manager_id -> employee_id ) Referred by orders ( employee_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the employee.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the employee.last_name 🔍 Unq employees_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The work email address. No two employees share one.email ⧉ department_id * int4 ↗ departments( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The department the employee works in: a department_id of departments.department_id 🔗 References departments ( department_id ) ⧉ manager_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee this employee reports to: an employee_id of this same table. NULL for the head of a department.manager_id 🔗 References employees ( manager_id -> employee_id ) ⧉ hire_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the employee started.hire_date ⧉ salary * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The yearly salary, with two decimals.salary Table public.departments ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The departments of the shop. Every employee works in one of them.departments 🔑 Pk departments_pkey ( department_id ) ⧉ department_id * int4 ↙ employees( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the department.department_id 🔗 Referred by employees ( department_id ) 🔍 Unq departments_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the department, such as Sales. No two departments share a name.name
The practice database, drawn by DbSchema.

To run the lessons against your own PostgreSQL, create the same database from shop.sql, a SQL script for PostgreSQL. Connect DbSchema to it, and DbSchema draws this diagram. The Community Edition is free.