Base queries
SELECT id, title FROM skills WHERE roadmap_id = 1 ORDER BY sort_order LIMIT 20;
SELECT s.title, COUNT(r.id) AS resources_count
FROM skills s
LEFT JOIN resources r ON r.skill_id = s.id
GROUP BY s.id, s.title;
INSERT INTO skills (slug, roadmap_id) VALUES ("foo", 1);
UPDATE skills SET sort_order = 5 WHERE slug = "foo";
DELETE FROM skills WHERE slug = "foo";
Key concepts
- JOIN: INNER, LEFT, RIGHT.
- WHERE vs HAVING: filters before/after aggregation.
- Indexes: speed up lookups but slow inserts.
- Transactions: BEGIN, COMMIT, ROLLBACK for consistency.
