{
  "version": 1,
  "name": "SQL basics",
  "iconName": "list.bullet.rectangle.fill",
  "colorID": "teal",
  "detail": "Query, filter, join and summarize",
  "cards": [
    {
      "title": "Read every column from a table",
      "concept": "The star means all columns. Handy for a look, rarely right for production.",
      "code": "SELECT * FROM users;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Pick specific columns",
      "concept": "Name only what you need.",
      "code": "SELECT name, email FROM users;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Filter rows",
      "concept": "WHERE keeps the rows where the condition is true.",
      "code": "SELECT * FROM users WHERE age >= 18;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Sort the result",
      "concept": "ORDER BY sorts ascending; add DESC for the reverse.",
      "code": "SELECT * FROM users ORDER BY created_at DESC;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Limit the rows",
      "concept": "Returns only the first few rows.",
      "code": "SELECT * FROM users LIMIT 10;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Remove duplicates",
      "concept": "DISTINCT returns each different value once.",
      "code": "SELECT DISTINCT country FROM users;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Match several values",
      "concept": "IN checks against a list.",
      "code": "SELECT * FROM users WHERE country IN ('MX', 'US');",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Match a pattern",
      "concept": "% stands for any run of characters.",
      "code": "SELECT * FROM users WHERE name LIKE 'Ma%';",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Check for missing values",
      "concept": "Use IS NULL; = NULL never matches.",
      "code": "SELECT * FROM users WHERE phone IS NULL;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Count rows",
      "concept": "COUNT(*) counts rows, including ones with NULLs.",
      "code": "SELECT COUNT(*) FROM orders;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Total and average",
      "concept": "Aggregate functions collapse many rows into one value.",
      "code": "SELECT SUM(total), AVG(total) FROM orders;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Group rows",
      "concept": "GROUP BY makes one result row per group.",
      "code": "SELECT country, COUNT(*) FROM users GROUP BY country;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Filter after grouping",
      "concept": "HAVING filters groups; WHERE filters rows before grouping.",
      "code": "SELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*) > 5;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "INNER JOIN",
      "concept": "Keeps only rows that have a match in both tables.",
      "code": "SELECT u.name, o.total FROM users u INNER JOIN orders o ON o.user_id = u.id;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "LEFT JOIN",
      "concept": "Keeps every row from the left table, with NULLs where there is no match.",
      "code": "SELECT u.name, o.total FROM users u LEFT JOIN orders o ON o.user_id = u.id;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Rename a column or table",
      "concept": "AS gives a name that is easier to read.",
      "code": "SELECT COUNT(*) AS total_users FROM users;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Insert a row",
      "concept": "Adds one row with the values you list.",
      "code": "INSERT INTO users (name, email) VALUES ('Ana', 'ana@example.com');",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Update rows",
      "concept": "Always add a WHERE, or every row changes.",
      "code": "UPDATE users SET age = 30 WHERE id = 7;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Delete rows",
      "concept": "Always add a WHERE, or the table empties.",
      "code": "DELETE FROM users WHERE id = 7;",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Create a table",
      "concept": "Defines columns, types and constraints.",
      "code": "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);",
      "languageID": "sql",
      "kindRaw": "code",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "What is a primary key?",
      "concept": "A column, or set of columns, that uniquely identifies each row and can't be NULL.",
      "code": "",
      "languageID": "none",
      "kindRaw": "basic",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "What is a foreign key?",
      "concept": "A column that points to the primary key of another table and keeps the link consistent.",
      "code": "",
      "languageID": "none",
      "kindRaw": "basic",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "Why add an index?",
      "concept": "It makes lookups and sorts on a column much faster, at the cost of slower writes and more space.",
      "code": "",
      "languageID": "none",
      "kindRaw": "basic",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "WHERE vs HAVING",
      "concept": "WHERE filters rows before grouping. HAVING filters the groups after aggregation.",
      "code": "",
      "languageID": "none",
      "kindRaw": "basic",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    },
    {
      "title": "What does a transaction guarantee?",
      "concept": "A group of statements either all happen or none do, so the data never ends up half changed.",
      "code": "",
      "languageID": "none",
      "kindRaw": "basic",
      "styleID": "",
      "notes": "",
      "colorID": "teal",
      "tags": []
    }
  ],
  "subfolders": []
}