Basically I would go with the tools to be able to do stuff yourself first (how to see what the contents of each field in a table are, how to figure out stuff like 'PostTypes' that are numbers), then doing simple WHERE statements (find all questions in the past month), then a simple JOIN (users and posts, for instance)
- How to determine the contents of a table
- How to determine the meaning of numerical fields
- Simple WHERE statements on a single table
- JOIN statement between two tables
- Data.SE specific quirks (
- Possibly more complex stuff (sub-queries,
Start with the basics
Explain the general layout -- title field, description field, query field, and the table list to the right. Then teach how to run a simple query to look at what's in one of the tables:
-- SELECT * grabs everything FROM the specified table
-- Since that's a lot of info, we limit it to 100 entries
SELECT TOP 100 *
-- This tells you what fields are in the table with sample data
Teach about numerical fields
-- Run this query, what does PostTypeId mean? It's a number!
SELECT Top 100 Title, Id, PostTypeId
ORDER BY Id DESC
-- Now comment the above lines with --
-- And uncomment the lines below and run again
-- SELECT DISTINCT p.PostTypeId, pt.Name
-- FROM Posts p
-- JOIN PostTypes pt ON p.PostTypeId = pt.Id
-- ORDER BY p.PostTypeId ASC
-- This can be seen much easier if you click on the ? icon
-- next to the PostTypes table in the right hand table list
-- What if we only want to see certain posts?
-- For instance, what about all posts in July 2014?
SELECT Title, Id, PostTypeId
WHERE CreationDate BETWEEN '2014-07-01' AND '2014-07-31'
-- But some posts have no titles? Why is that? Check PostTypeId
-- Not all items have all data types -- answers don't have titles
-- Uncomment the next line to filter the data to only show questions
-- AND PostTypeId = 1
-- But what if we want to look only at questions in july by users with <500 rep?
-- We can use a JOIN clause to look at data from two tables
-- But we have to tell the query how to link the tables
SELECT p.Title, p.Id, u.DisplayName, u.Reputation
-- We are using prefixes to explain which table to take info from
FROM Posts p
-- We are naming Posts 'p' so if two fields have the same name we can differentiate
JOIN Users u ON p.OwnerUserId = u.Id
-- We want to give the info for the user who owns the post on the same line
WHERE p.CreationDate BETWEEN '2014-07-01' AND '2014-07-31'
AND p.PostTypeId = 1
-- If we changed this to 'u.CreationDate' what do you think happens?
-- Try it and find out!
Data.SE Specific Quirks
Not writing examples for these, but teach about
##UserId## at the very least.
More complex stuff?
Conditional clauses (
IIF), aggregate clauses (
COUNT), sub-queries (
WITH tableA AS), etc. can also be covered for more complex queries, but the above probably handles 70% of most basic queries.