sqlteaching-curriculum.txt

Intro
    In SQL, data is usually organized in various tables. For example, a sports team database might have tables <em>teams</em>, <em>players</em>, and <em>games</em>. A wedding database might have tables guests, vendors, and music_playlist.
    <br/><br/>
    Imagine we have a table that stores family members with each member's name, age, species, and gender.

SELECT *
    Select * all
        Let's start by grabbing all of the data.  We have a table called "family_members" that is shown below.  In order to grab all of that data, please run the following command: <br/><strong>SELECT * FROM family_members;</strong>

FILTERING
    Equality
        In order to select particular rows from this table, we use the "WHERE" keyword.  So for example, if we wanted to grab all of the rows that correspond to humans, we would type "SELECT * FROM family_members WHERE species = 'human';"  Note that the quotes have to be around the word 'human'.  Can you run a query that returns all of the rows that refer to dogs?
    Greater than
        -- maybe make a really old grandma?
        If we want to only select members based on a numerical field, we can also use the "WHERE" keyword.  In SQL, > means "greater than".  For example, if we wanted to select family members greater than 10, we would type "SELECT * FROM family_members WHERE age > 10;".
        <br/>
        Can you run a query that returns all rows of members with an age greater than 27?
    Greater than or equal
        SQL accepts various inequality symbols, including <br/>= "equal to"<br/>> "greater than"<br/>< "less than"<br/>>= "greater than or equal to"<br/><= "less than or equal to"<br/><br/> Can you return all rows of members with an age less than or equal to 27?

SELECT - limiting the results
    Selecting particular columns
        <code>SELECT *</code> grabs all fields (called columns) in a table. If WE only wanted to see the name and age columns, we would type <code>SELECT name, age FROM family_members</code>.<br/><br/>Can you return just the name and species columns?

    LIMIT 2
        Often, tables contain millions of rows, and it can take a while to grab everything. If we just want to get a few examples of the data in a table, we may want to select only a few rows. If we want to select 2 rows, we would add <code>LIMIT 2</code> at the end of the query.<br/><br/> Can you return the first row (and all columns) of a table called menu_items?<br/><br/> Note: A SQL interface like SequelPro automatically limits the rows returned to the first 1000.

AGGREGATION
    COUNT(*)
        Another way to explore a table is to check the number of rows in it. For example, if we are querying a table <em>states_of_us</em> we\'d expect 50 rows, or 500 rows in a table called <em>fortune_500_companies</em>.<br/><br/><code>SELECT COUNT(*) FROM family_members</code> returns the total number of rows in the table <em>family_members</em>. Try this for yourself.
    COUNT(*) ... WHERE
        We can combine <code>COUNT(*)</code> with <code>WHERE</code>. For example, <code>SELECT COUNT(*) FROM family_members WHERE species = 'human'</code> returns 2.<br/><br/>Can you return the number of rows in family_members where the species is a dog?
    SUM

    AVG, MAX, MIN,

    GROUP BY

SELECT
    ORDER BY
        When we use <code>LIMIT 1</code>, we just select the first row in the table. If we want to select the first row
        You can order by any type of field (numbers, strings, dates).

    DISTINCT

Advanced Filtering
    AND
    OR
    BETWEEN
    BETWEEN, < , >, <=, >= for Dates
    LIKE
    IN
    -- need a new table for this to be interesting

joining
    Instead of storing the species type as a string, we are now

Looking for what you need

STRING Functions:
    LIKE using %
    lcase, ucase
    LEFT, RIGHT

DATE functions

Data sets:
    menu_items:
        dish, price, calories

    sqlstr += "INSERT INTO family_members VALUES (1, 'Dave', 'male', 'human', 28);"
sqlstr += "INSERT INTO family_members VALUES (2, 'Mary', 'female', 'human', 27);"
sqlstr += "INSERT INTO family_members VALUES (3, 'Pickles', 'male', 'dog', 4);"