SQL basics flash cards

The cards, front and back

1. 1
   Front

   SQL

   Back

   Structured Query Language, the standard language for creating, reading, updating and deleting data in relational databases.

2. 2
   Front

   Table, row and column

   Back

   A table stores data about one kind of thing. Each row (record) is one item, and each column (field) is one attribute of it.

3. 3
   Front

   SELECT

   Back

   Chooses which columns to return.

   SELECT name, age FROM students;

   SELECT * returns every column.

4. 4
   Front

   WHERE

   Back

   Filters rows by a condition.

   SELECT * FROM students WHERE age >= 16;

5. 5
   Front

   AND, OR, NOT

   Back

   Combine conditions in WHERE.

   WHERE year = 10 AND score > 80

6. 6
   Front

   ORDER BY

   Back

   Sorts the results. ASC is ascending (the default); DESC is descending.

   SELECT name FROM students ORDER BY score DESC;

7. 7
   Front

   LIMIT

   Back

   Returns only a set number of rows. LIMIT works in MySQL, PostgreSQL and SQLite; SQL Server uses TOP instead.

   SELECT * FROM products LIMIT 5;

8. 8
   Front

   LIKE and wildcards

   Back

   Pattern matching on text. % matches any number of characters; _ matches exactly one.

   WHERE name LIKE 'A%' finds names starting with A.

9. 9
   Front

   IN and BETWEEN

   Back

   IN matches any value in a list: WHERE country IN ('Fiji', 'Chile').

   BETWEEN matches a range, including both ends: WHERE price BETWEEN 5 AND 10.

10. 10
    Front

    NULL

    Back

    Means a value is missing or unknown. Test it with IS NULL or IS NOT NULL, not with =.

11. 11
    Front

    Aggregate functions

    Back

    • COUNT(): number of rows
    • SUM(): total
    • AVG(): average
    • MIN() and MAX(): smallest and largest
12. 12
    Front

    GROUP BY

    Back

    Groups rows that share a value so aggregates are calculated per group.

    SELECT class, AVG(score) FROM results GROUP BY class;

13. 13
    Front

    HAVING

    Back

    Filters groups after GROUP BY, the way WHERE filters rows.

    ... GROUP BY class HAVING AVG(score) > 70;

14. 14
    Front

    Primary key

    Back

    A column, or set of columns, whose value uniquely identifies each row. It cannot be NULL or repeated.

15. 15
    Front

    Foreign key

    Back

    A column that refers to the primary key of another table, linking the two tables together.

16. 16
    Front

    INNER JOIN

    Back

    Returns only rows that have a match in both tables.

    SELECT s.name, c.title FROM students s JOIN classes c ON s.class_id = c.id;

17. 17
    Front

    LEFT JOIN

    Back

    Returns every row from the left table plus matching rows from the right table. Where there is no match, the right-hand columns are NULL.

18. 18
    Front

    INSERT

    Back

    Adds a new row.

    INSERT INTO students (name, age) VALUES ('Ana', 15);

19. 19
    Front

    UPDATE

    Back

    Changes existing rows.

    UPDATE students SET age = 16 WHERE name = 'Ana';

    Without WHERE, every row is changed.

20. 20
    Front

    DELETE

    Back

    Removes rows.

    DELETE FROM students WHERE id = 7;

    Without WHERE, every row in the table is deleted.

21. 21
    Front

    CREATE TABLE

    Back

    Defines a new table and its columns.

    CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);