Skip to content
master
Switch branches/tags
Code

Latest commit

 

Git stats

Files

Permalink
Failed to load latest commit information.
Type
Name
Latest commit message
Commit time
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Open In Colab

Relational Databases and SQL

This is a brief introductory module to relational databases and SQL. It mainly targets people that are interested in learning SQL, and does not cover topics such as indexing, transactions, stored procedures, etc.

Videos for the class

Videos for the class

Indicative Schedule

Session 1: Entity-Relationship Model and Relational Databases

  • Entities, Primary Keys, and Attributes
  • Relations
  • Cardinality: One-to-One, One-to-Many, Many-to-Many
  • From ER Diagram to a Relational Database
  • (Optional) SQL Statements for Creating Tables
  • (Optional) Populating a Database with Data
In class: Artist-Gallery-Painting example
In class: From Spreadsheet to a Normalized Database
In class: Water Utility Company example

Session 2: Selection Queries

  • Understand the design of our example databases
  • Navigating a Database: USE, SHOW TABLES, DESCRIBE
  • Selection queries: SELECT *, SELECT column, column AS,
  • Selection queries: DISTINCT, ORDER BY, LIMIT

Session 3: Filtering Queries

  • WHERE clause
  • Boolean conditions: AND, OR, NOT, BETWEEN
  • Containment condition: IN,
  • Approximate matches: LIKE
  • NULL values
In class: Find People that Live in "New York" (exploration for data cleaning)

Session 4: JOIN queries

  • Inner Joins
  • Self Joins
  • Outer Joins

Session 5: Aggregate queries

  • Aggregation functions (COUNT, COUNT DISTINC, SUM, AVG, MAX, MIN, STDEV, CONCAT)
  • GROUP BY on a single attribute
  • GROUP BY on multiple attributes
  • HAVING clause
  • Integrated JOIN and GROUP BY queries

Session 6: Subqueries

  • Views and temporary tables
  • The WITH clause
  • Subqueries
  • Variables
In-class Exercise: Compare Tastes Across Demographic Segments
In-class Exercise: Music Recommendation Service

Note: The in-class activities in this section usually take longer time than planned. I often go faster in the prior sessions, so that I can start describing the concept of veiw

Misceallaneous

  • Functions
  • UNION
  • CASE
  • ANY/ALL
  • ROLLUP

Additional Resources for Learning SQL

Useful Pointers

About

No description, website, or topics provided.

Resources

Releases

No releases published

Packages

No packages published