1) Basics
Select columns, filter rows, sort, and limit results.
SELECT *
FROM t
WHERE price > 100 AND category LIKE '%Kitchen%'
ORDER BY price DESC
LIMIT 50;
2) Counts, distinct, and quick sanity checks
Row counts, unique values, duplicates, and null checks.
SELECT COUNT(*) AS rows FROM t;
SELECT COUNT(DISTINCT brand) AS unique_brands
FROM t;
SELECT name, COUNT(*) AS n
FROM t
GROUP BY name
HAVING n > 1
ORDER BY n DESC;
3) Grouping & aggregation
Summaries by category, averages, min/max, and top-N groups.
SELECT category,
COUNT(*) AS rows,
AVG(price) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price
FROM t
GROUP BY category
ORDER BY rows DESC
LIMIT 10;
4) Joins (multiple files)
Load two+ files, then join by a key. Use aliases for clarity.
SELECT a.id, a.name, b.status
FROM table_a AS a
LEFT JOIN table_b AS b
ON a.id = b.id
WHERE b.status IS NOT NULL;
5) Subqueries & CTEs
Break complex logic into readable steps using WITH.
WITH ranked AS (
SELECT category, price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM t
)
SELECT * FROM ranked
WHERE rn <= 3
ORDER BY category, rn;
6) Window functions
Ranks, running totals, moving averages, percent-of-total.
SELECT category, price,
SUM(price) OVER (PARTITION BY category) AS category_total,
AVG(price) OVER (PARTITION BY category) AS category_avg
FROM t;
7) Text cleanup & matching
Case-insensitive search, trimming, replacing, extracting.
SELECT TRIM(name) AS name_clean
FROM t
WHERE LOWER(brand) LIKE '%garner%';
SELECT SUBSTR(ean, 1, 3) AS prefix,
COUNT(*) AS rows
FROM t
GROUP BY prefix
ORDER BY rows DESC;
8) Types, casts, and safe numeric ops
Convert text to numbers/dates and avoid surprises.
SELECT CAST(price AS REAL) AS price_num
FROM t
WHERE CAST(stock AS INTEGER) > 0;
SELECT *
FROM t
WHERE CAST(ean AS TEXT) IS NOT NULL;
9) Set operations
Combine result sets or compare lists.
SELECT id FROM table_a
UNION
SELECT id FROM table_b;
SELECT id FROM table_a
EXCEPT
SELECT id FROM table_b;
10) Views and performance helpers
Create views for reusable queries; add indexes for big joins.
CREATE VIEW v_top AS
SELECT * FROM t ORDER BY price DESC LIMIT 100;
CREATE INDEX idx_a_id ON table_a(id);
Notes: table names come from filenames. Column names are cleaned to be identifier-friendly; if you see a column name with spaces/symbols, wrap it in double quotes like "My Column". Everything runs locally and resets when you refresh or press Clear.