GROUP BY + HAVING
GROUP BY with an aggregate and HAVING to filter the groups — the difference between WHERE and HAVING made concrete.
-- The sample database is already loaded. Press Run, or Ctrl + Enter.
-- Ctrl + Enter with text selected runs only the selection.
SELECT
d.name AS department,
COUNT(*) AS headcount,
ROUND(AVG(e.salary)) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE e.active = 1
GROUP BY d.name
HAVING COUNT(*) > 1
ORDER BY avg_salary DESC;
Multi-table JOIN
A four-table join summing line items per order, which is the shape of most reporting queries.
-- Revenue per order, joining four tables.
SELECT
o.id AS order_id,
c.name AS customer,
e.name AS sold_by,
o.ordered_on,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN employees e ON e.id = o.employee_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY o.id
ORDER BY order_total DESC
LIMIT 10;
Self-join (who reports to whom)
A table joined to itself through manager_id, so every employee is listed beside their manager.
-- employees.manager_id points back at employees.id.
SELECT
e.name AS employee,
e.role,
COALESCE(m.name, '— none —') AS manager,
d.name AS department
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
LEFT JOIN departments d ON d.id = e.department_id
ORDER BY d.name, m.name NULLS FIRST, e.name;
Window functions
RANK and AVG as window functions with PARTITION BY — ranking inside each group without collapsing the rows.
-- Rank salaries inside each department, and compare to the dept average.
SELECT
d.name AS department,
e.name AS employee,
e.salary,
RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rank_in_dept,
ROUND(AVG(e.salary) OVER (PARTITION BY e.department_id)) AS dept_avg,
ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id)) AS vs_avg
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE e.active = 1
ORDER BY department, rank_in_dept;
Subqueries & EXISTS
NOT EXISTS to find rows with no match, and a correlated subquery comparing each row to its own group's average.
-- Customers who have never placed a paid order.
SELECT c.name, c.city, c.joined_on
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id AND o.status = 'paid'
);
-- Employees earning more than their department's average.
SELECT e.name, e.role, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary) FROM employees e2
WHERE e2.department_id = e.department_id
)
ORDER BY e.salary DESC;
CTEs (WITH ...)
WITH to build the answer in named steps instead of nesting subqueries.
-- Build up an answer in readable steps instead of one nested query.
WITH order_totals AS (
SELECT o.id, o.customer_id, o.ordered_on,
SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY o.id
),
customer_spend AS (
SELECT c.name AS customer, c.state,
COUNT(*) AS orders,
SUM(t.total) AS lifetime_value
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
GROUP BY c.id
)
SELECT * FROM customer_spend
ORDER BY lifetime_value DESC;
Dates & CASE
strftime to group by month, and CASE to bucket the result.
-- SQLite stores dates as text; strftime does the work.
SELECT
strftime('%Y-%m', o.ordered_on) AS month,
COUNT(DISTINCT o.id) AS orders,
SUM(oi.quantity * oi.unit_price) AS revenue,
CASE
WHEN SUM(oi.quantity * oi.unit_price) > 150000 THEN 'strong'
WHEN SUM(oi.quantity * oi.unit_price) > 60000 THEN 'steady'
ELSE 'slow'
END AS verdict
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY month
ORDER BY month;
INSERT / UPDATE / DELETE
INSERT, UPDATE and DELETE against the live database, then reading back what changed.
-- Changes are real, and last until you reset the database.
INSERT INTO products (name, category, price, stock)
VALUES ('Analytics Add-on', 'Add-on', 7500, 999);
UPDATE employees
SET salary = ROUND(salary * 1.10)
WHERE department_id = 3 AND active = 1;
DELETE FROM orders WHERE status = 'cancelled';
-- Then check what changed:
SELECT name, category, price FROM products WHERE category = 'Add-on';
SELECT name, salary FROM employees WHERE department_id = 3;
SELECT COUNT(*) AS remaining_orders FROM orders;
Create your own table
CREATE TABLE with a primary key and a CHECK constraint, then querying your own table.
DROP TABLE IF EXISTS students;
CREATE TABLE students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
branch TEXT NOT NULL,
marks INTEGER CHECK (marks BETWEEN 0 AND 100)
);
INSERT INTO students (name, branch, marks) VALUES
('Aisha', 'CSE', 91),
('Rohan', 'ECE', 78),
('Meera', 'CSE', 84),
('Kabir', 'ME', 66),
('Tanvi', 'ECE', 89);
SELECT branch,
COUNT(*) AS students,
MAX(marks) AS top,
ROUND(AVG(marks), 1) AS average
FROM students
GROUP BY branch
ORDER BY average DESC;