SQL examples

Self-contained SQL examples that create their own sample data and run against SQLite in your browser. Learn table creation, filtering, joins, aggregation, CTEs, and window functions interactively.

create table and insert rows

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products (name, price) VALUES
  ('Keyboard', 79.00),
  ('Mouse', 39.00),
  ('Monitor', 249.00);

SELECT * FROM products ORDER BY price DESC;

Create a SQLite table, insert rows, and query the resulting data in one runnable script.

Run in SQL playground

where and order by

CREATE TABLE products (name TEXT, category TEXT, price REAL);
INSERT INTO products VALUES
  ('Keyboard', 'Accessories', 79),
  ('Mouse', 'Accessories', 39),
  ('Monitor', 'Displays', 249),
  ('Webcam', 'Cameras', 89);

SELECT name, price
FROM products
WHERE price >= 75
ORDER BY price DESC;

Filter rows with WHERE and sort the result with ORDER BY in SQLite.

Run in SQL playground

inner join

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Linus'), (3, 'Grace');
INSERT INTO orders VALUES (101, 1, 125.50), (102, 2, 89.00), (103, 1, 42.00);

SELECT customers.name, orders.id AS order_id, orders.total
FROM customers
INNER JOIN orders ON orders.customer_id = customers.id
ORDER BY orders.total DESC;

Join customers to their orders with a runnable SQL INNER JOIN.

Run in SQL playground

group by

CREATE TABLE sales (category TEXT, amount REAL);
INSERT INTO sales VALUES
  ('Books', 25), ('Books', 40),
  ('Games', 60), ('Games', 35), ('Games', 20),
  ('Music', 15);

SELECT category, COUNT(*) AS sales_count, SUM(amount) AS revenue
FROM sales
GROUP BY category
ORDER BY revenue DESC;

Aggregate sales by category with SQL GROUP BY, COUNT, and SUM.

Run in SQL playground

common table expression

CREATE TABLE employees (name TEXT, team TEXT, salary INTEGER);
INSERT INTO employees VALUES
  ('Ada', 'Platform', 130000), ('Linus', 'Platform', 110000),
  ('Grace', 'Data', 140000), ('Edsger', 'Data', 100000);

WITH team_stats AS (
  SELECT team, AVG(salary) AS average_salary
  FROM employees
  GROUP BY team
)
SELECT e.name, e.team, e.salary, ROUND(t.average_salary) AS team_average
FROM employees e
JOIN team_stats t ON t.team = e.team
WHERE e.salary > t.average_salary;

Use a SQL common table expression (WITH) to organize a multi-step query.

Run in SQL playground

window function

CREATE TABLE scores (player TEXT, division TEXT, points INTEGER);
INSERT INTO scores VALUES
  ('Ada', 'A', 95), ('Linus', 'A', 82), ('Grace', 'A', 95),
  ('Edsger', 'B', 91), ('Margaret', 'B', 88);

SELECT player, division, points,
  RANK() OVER (PARTITION BY division ORDER BY points DESC) AS division_rank
FROM scores
ORDER BY division, division_rank;

Rank rows within groups using the SQL RANK window function.

Run in SQL playground