Fondamenti di SQL

Le query base

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";

Concetti chiave

• JOIN: INNER, LEFT, RIGHT.
• WHERE vs HAVING: filtri prima/dopo aggregazione.
• Indici: accelerano lookup, ma rallentano insert.
• Transazioni: BEGIN, COMMIT, ROLLBACK per coerenza.