By the end of this lesson, you will be able to:
- Connect to the database that you want to query, see the tables, and discover the attributes for each table
- Show the contents of the table, select which columns you want to retrieve, rename the columns, and order the results
- Filter the results of a query to include only rows that satisfy a given condition
- Express complex conditions using Boolean operators (AND, OR, NOT)
- Use the LIKE operator in SQL for writing approximate matching conditions
- Use the IN operator for writing containment queries
- Understand the NULL mark and how to write conditions that involve NULL
- Write complex queries that retrieve noisy data, and reformulate your queries iteratively
| Topic | Link |
|---|---|
| Intro to SQL | Watch |
| Navigating a Database | Watch |
| SELECT * Statement | Watch |
| SELECT * Statement EXTRA | Watch |
| SELECT Attributes & SELECT Attributes As | Watch |
| SELECT DISTINCT | Watch |
| ORDER BY / LIMIT | Watch |
| WHERE: Simple Equality Conditions | Watch |
| WHERE: Boolean Operators | Watch |
| WHERE: Inequality Operators | Watch |
| WHERE: IN Operator | Watch |
| Approximate Queries using LIKE | Watch |
| NULL Mark | Watch |
| Floating Point Numbers | Watch |
| Activity: Dealing with Dirty Data | Watch |
- SQL: Selection Queries — SELECT, AS, DISTINCT, ORDER BY, LIMIT
- SQL: Filtering Queries — WHERE, Boolean operators, IN, LIKE, NULL, CASE
These notebooks use Google BigQuery. To run them:
- Open the notebook in Google Colab
- Authenticate with your Google account
- Replace
'your-project-id'with your Google Cloud project ID
The queries use datasets in the nyu-datasets project:
nyu-datasets.imdb— Movies, actors, directors, roles, genresnyu-datasets.facebook— Student profiles, hobbies, relationships