Ultimate SQL Playground - Learn & Explore SQL

Ultimate SQL Playground 🚀

Explore our bookstore database with basic SQL queries! Try the pre-loaded query, check out the Examples tab for more, and export results as CSV or charts as PNG. Learn SQL hands-on with no setup needed!

Write Your SQL Query

Query Results

Query History

SQL Tutorials 📚

Master SQL with our tutorials. From beginner basics to advanced techniques, each lesson includes explanations, examples, and challenges to test your skills.

Beginner Tutorials

1. Selecting Data with SELECT

The SELECT statement retrieves data from a table. Use * to select all columns or specify columns for targeted data.

SELECT title, price FROM books;

Challenge: Select all columns from the books table where the price is less than 15.

SELECT * FROM books WHERE price < 15;

2. Filtering with WHERE

The WHERE clause filters rows based on conditions. Combine with AND, OR, or LIKE for complex filters.

SELECT title, publication_year FROM books WHERE publication_year > 2000 AND price < 20;

Challenge: Find books with titles containing "Harry".

SELECT title FROM books WHERE title LIKE '%Harry%';

Intermediate Tutorials

3. Joining Multiple Tables

Use JOIN to combine data from multiple tables. INNER JOIN returns matching records, while LEFT JOIN includes all records from the left table.

SELECT books.title, authors.name, publishers.name AS publisher
FROM books
INNER JOIN authors ON books.author_id = authors.author_id
INNER JOIN publishers ON books.publisher_id = publishers.publisher_id;

Challenge: List all books with their authors and genres.

SELECT books.title, authors.name, genres.name AS genre
FROM books
INNER JOIN authors ON books.author_id = authors.author_id
INNER JOIN genres ON books.genre_id = genres.genre_id;

4. Aggregating Data with GROUP BY

GROUP BY groups rows for aggregate functions like COUNT, SUM, AVG. Use HAVING to filter groups.

SELECT genres.name, COUNT(*) as book_count
FROM books
INNER JOIN genres ON books.genre_id = genres.genre_id
GROUP BY genres.name
HAVING COUNT(*) > 2;

Challenge: Find the average book price per publisher.

SELECT publishers.name, AVG(books.price) as avg_price
FROM books
INNER JOIN publishers ON books.publisher_id = publishers.publisher_id
GROUP BY publishers.name;

Advanced Tutorials

5. Subqueries and CTEs

Subqueries and Common Table Expressions (CTEs) allow complex queries. CTEs improve readability for nested queries.

WITH top_rated AS (
    SELECT book_id, AVG(rating) as avg_rating
    FROM reviews
    GROUP BY book_id
    HAVING AVG(rating) > 4
)
SELECT books.title, top_rated.avg_rating
FROM books
INNER JOIN top_rated ON books.book_id = top_rated.book_id;

Challenge: Find customers who placed orders worth more than the average order total.

WITH avg_order AS (
    SELECT AVG(total_amount) as avg_total
    FROM orders
)
SELECT customers.name, orders.total_amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.total_amount > (SELECT avg_total FROM avg_order);

6. Window Functions

Window functions perform calculations across rows related to the current row, like ranking or running totals.

SELECT title, price,
       RANK() OVER (PARTITION BY genre_id ORDER BY price DESC) as price_rank
FROM books;

Challenge: Rank authors by the number of books they’ve written.

SELECT authors.name, 
       COUNT(books.book_id) as book_count,
       RANK() OVER (ORDER BY COUNT(books.book_id) DESC) as author_rank
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id
GROUP BY authors.name;

7. Analyzing Stock Changes

Use CASE statements and JOINs to analyze stock movements from the inventory_logs table.

SELECT 
    g.name AS genre,
    SUM(CASE WHEN il.change_type = 'ADD' THEN il.quantity ELSE -il.quantity END) as net_stock_change
FROM genres g
INNER JOIN books b ON b.genre_id = g.genre_id
INNER JOIN inventory_logs il ON b.book_id = il.book_id
GROUP BY g.name
HAVING net_stock_change > 10
ORDER BY net_stock_change DESC;

Challenge: Find the net stock change per book title.

SELECT 
    b.title,
    SUM(CASE WHEN il.change_type = 'ADD' THEN il.quantity ELSE -il.quantity END) as net_stock_change
FROM books b
INNER JOIN inventory_logs il ON b.book_id = il.book_id
GROUP BY b.title
ORDER BY net_stock_change DESC;

Database Schema 🗄️

Explore the bookstore database schema, including stock change logs. The diagram and table details below help you understand relationships and craft powerful queries.

Schema Diagram

Tables

books

Stores book details.

Columns:

book_id (INTEGER, Primary Key)
title (TEXT)
author_id (INTEGER, Foreign Key to authors)
publisher_id (INTEGER, Foreign Key to publishers)
genre_id (INTEGER, Foreign Key to genres)
price (REAL)
publication_year (INTEGER)
stock (INTEGER)

Sample Data:

book_id | title                   | author_id | publisher_id | genre_id | price | publication_year | stock
1       | The Great Gatsby        | 1         | 1           | 1        | 12.99 | 1925             | 50
2       | 1984                    | 2         | 2           | 2        | 15.50 | 1949             | 30
3       | Harry Potter and the Philosopher's Stone | 4 | 3 | 3 | 20.99 | 1997 | 100

authors

Stores author details.

Columns:

author_id (INTEGER, Primary Key)
name (TEXT)
birth_year (INTEGER)
country (TEXT)

Sample Data:

author_id | name                | birth_year | country
1         | F. Scott Fitzgerald | 1896       | USA
2         | George Orwell       | 1903       | UK
3         | Jane Austen         | 1775       | UK

publishers

Stores publisher details.

Columns:

publisher_id (INTEGER, Primary Key)
name (TEXT)
city (TEXT)

Sample Data:

publisher_id | name                | city
1            | Scribner            | New York
2            | Secker & Warburg    | London
3            | Bloomsbury          | London

genres

Stores book genres.

Columns:

genre_id (INTEGER, Primary Key)
name (TEXT)

Sample Data:

genre_id | name
1        | Fiction
2        | Dystopian
3        | Fantasy

customers

Stores customer details.

Columns:

customer_id (INTEGER, Primary Key)
name (TEXT)
email (TEXT)
join_date (TEXT)

Sample Data:

customer_id | name           | email                | join_date
1           | Alice Smith    | alice@example.com    | 2023-01-15
2           | Bob Johnson    | bob@example.com      | 2023-02-10

orders

Stores order details.

Columns:

order_id (INTEGER, Primary Key)
customer_id (INTEGER, Foreign Key to customers)
book_id (INTEGER, Foreign Key to books)
order_date (TEXT)
quantity (INTEGER)
total_amount (REAL)

Sample Data:

order_id | customer_id | book_id | order_date  | quantity | total_amount
1        | 1          | 1       | 2023-03-01  | 2        | 25.98
2        | 2          | 2       | 2023-03-02  | 1        | 15.50

reviews

Stores book reviews.

Columns:

review_id (INTEGER, Primary Key)
book_id (INTEGER, Foreign Key to books)
customer_id (INTEGER, Foreign Key to customers)
rating (INTEGER)
comment (TEXT)
review_date (TEXT)

Sample Data:

review_id | book_id | customer_id | rating | comment                | review_date
1         | 1       | 1          | 5      | Amazing read!          | 2023-03-05
2         | 2       | 2          | 4      | Thought-provoking      | 2023-03-06

inventory_logs

Stores stock change logs for books.

Columns:

log_id (INTEGER, Primary Key)
book_id (INTEGER, Foreign Key to books)
change_type (TEXT, 'ADD' or 'REMOVE')
quantity (INTEGER)
log_date (TEXT)

Sample Data:

log_id | book_id | change_type | quantity | log_date
1      | 1       | ADD         | 20       | 2023-03-01
2      | 2       | REMOVE      | 5        | 2023-03-02
3      | 4       | ADD         | 50       | 2023-03-03

Example Queries 📋

Try these basic SQL queries to explore the bookstore database. Click "Run Query" to load each query into the editor, then execute it to see results in a table or chart. Export results as CSV or charts as PNG!

Basic Queries

1. List All Books

Retrieve all columns from the books table to see titles, prices, and more.

SELECT * FROM books;

2. Books Under $15

Find books with a price less than $15, showing title and price.

SELECT title, price FROM books WHERE price < 15;

3. Books by Genre

List books in the Fantasy genre, joining with the genres table.

SELECT b.title, g.name AS genre
FROM books b
INNER JOIN genres g ON b.genre_id = g.genre_id
WHERE g.name = 'Fantasy';

4. Books with Authors

Join books and authors to show book titles and author names.

SELECT b.title, a.name AS author
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id;

5. Sort Books by Price

List books sorted by price in descending order.

SELECT title, price FROM books ORDER BY price DESC;

6. Count Books per Genre

Count the number of books in each genre (produces a chart).

SELECT g.name AS genre, COUNT(*) AS book_count
FROM genres g
INNER JOIN books b ON b.genre_id = g.genre_id
GROUP BY g.name;

7. Average Book Price by Publisher

Calculate the average book price for each publisher (produces a chart).

SELECT p.name AS publisher, AVG(b.price) AS avg_price
FROM publishers p
INNER JOIN books b ON b.publisher_id = p.publisher_id
GROUP BY p.name;

8. Net Stock Change by Genre

Analyze net stock changes (ADD minus REMOVE) per genre (produces a chart).

SELECT 
    g.name AS genre,
    SUM(CASE WHEN il.change_type = 'ADD' THEN il.quantity ELSE -il.quantity END) AS net_stock_change
FROM genres g
INNER JOIN books b ON b.genre_id = g.genre_id
INNER JOIN inventory_logs il ON b.book_id = il.book_id
GROUP BY g.name
HAVING net_stock_change > 0;

9. Customer Orders

List all orders with customer names and book titles.

SELECT c.name AS customer, b.title, o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN books b ON o.book_id = b.book_id;

10. High-Rated Reviews

Find reviews with a rating of 5, showing book titles and comments.

SELECT b.title, r.comment, r.rating
FROM reviews r
INNER JOIN books b ON r.book_id = b.book_id
WHERE r.rating = 5;