Write Basic Data Queries

Pull Your Own Data: An Introduction to SQL Part I

Topics
In the first lecture we'll cover the following topics:
- Introduction to databases, tables, columns, and rows: the spreadsheet analogy
- An overview of web technology: servers, databases, and clients
- Getting acquainted with the interactive SQL terminal, and writing your first query
- Understanding data types
- Getting started; SQL syntax
- SELECT
- WHERE
- AND and OR
- LIMIT
- ORDER BY
- Selecting data from multiple tables
- Using AS to make queries more readable
- Aggregate functions and GROUP BY
- Next steps
Class Resources:
1. The primary tool for our class: http://dfkoz.com/SQL_101.php?pass=topsecretpass
2. The challenges for the class: https://docs.google.com/document/d/18Wg_6FLLC9td8avkdu1Sk5bD5zVLzFCbqItDZPOFXVc/edit?usp=sharing
Additional Resources:
1. For a handy summary of SQL syntax: http://www.w3schools.com/sql/default.asp
2. The official MySQL site: http://www.mysql.com/
3. A more detailed tutorial for MySQL that includes some more advanced topics, like creating databases: http://dev.mysql.com/doc/refman/5.0/en/tutorial.html
4. Syntax differences across various SQL products: http://troels.arvin.dk/db/rdbms/
Here is a previous recording of Lecture #1.

Pull Your Own Data: An Introduction to SQL Part II

Topics
Topics I'll be covering in the second lecture:
- Questions from Lecture #1
- Aggregate functions and HAVING
- INNER JOIN and OUTER JOIN
- Subqueries; the IN keyword
- A preview of REGEXP
- Learning more
The more detailed notes for this lecture can be found here: https://docs.google.com/document/d/17iY_tKlhMXUZytmPXoZYbwj6wnK_0SD_ESWPgPKdVi0/edit?usp=sharing

Project: Data Queries

We'll be using a sample set of e-commerce data to write the data queries that you can later apply to
We'll be using a sample set of e-commerce data to write the data queries that you can later apply to any set of data, once you get your hands on it! If you can answer these questions, you'll have shown you've got what it takes to be a data-pulling expert.
1. Work on this list of queries - if you can run all of these, you'll have all of the raw ingredients necessary to tackle the final challenge.
2. Think of an important, tough, or fun question to answer. Then, share your solution with the rest of the class in the online discussions so everyone can learn from each other.
3. Answer these challenge questions to prove you've learned how to pull your own queries!
Share your answered query challenges!
Share your answered query challenges!

Stephen Fox

--