376 - Web App Dev: Databases & SQL

For this unit we will learn how data is organized in a relational databases and then how we can access and manipulate it using Structured Query Language (SQL). After the first lesson we will spend some time completing the Introduction to SQL course in CodeHS and then return to the remaining lessons here.

Lessons

1. Database introduction (with a spreadsheet!)
   1. Tables
   2. Rows/Records
   3. Columns/Fields
   4. Keys (primary, foreign, and natural)
   5. Relationships
   6. Data Sanitation
2. Installing and using MySQL
   1. MySQL Community Server
   2. MySQL Workbench
   3. Chapter 12: Functions and operators
   4. Chapter 13: SQL statements
3. Student Information System (SIS) data schema; click here to download a backup (or dump) of the database
4. Basic SELECT statements
   1. SELECT
   2. SELECT DISTINCT
   3. WHERE clause
   4. AND, OR, and NOT operators
   5. ORDER BY clause
5. Combining tables with JOIN clauses
   1. INNER JOIN
   2. LEFT JOIN
6. Aggregation
   1. Standard functions
   2. GROUP BY clause
   3. HAVING clause
7. Sub-select statements
   1. IN operator
   2. EXISTS operator
8. Modifying data
   1. CRUD operations (Create, Read, Update, Delete)
   2. UPDATE statements
   3. INSERT statements
   4. DELETE statements
9. CREATE statements
   1. CREATE DATABASE statement
   2. CREATE TABLE statement