In this class, students will be introduced to relational data terminology (row, column), the function of a primary key, and retrieving data from a MySQL database using SELECT queries.
Objective: Students should be able to retrieve data from a database table using SELECT statements that include WHERE, GROUP BY, and ORDER BY.
- What are databases?
- Exploring databases and database management systems
- The features of a relational database
- Introduction to database modeling
- SQL: Using the basic SELECT statement
- Creating SQL queries
- Creating the WHERE clause
- Sorting query results
- Using aggregate function
- Joining tables
- The relational model of data
- A 'database' vs. a 'DBMS' (database management system)
- The concept of a schema
- The properties of an 'entity' (or 'row')
- Basic entity relationship diagrams
- A basic SELECT statement
- Constructing more complex SELECT statements
- JOIN
- Selecting composite data from multiple tables
- Compare JOIN WHERE with cartesian product
- Naming Conventions: UpperCamelCase/PascalCase, lowerCamelCase, snake_case, hnHungarianNotation/HNHungarianNotation
- Character Sets in Databases (hint: always use UTF-8 encoding, called 'utf8mb4' in MySQL)
- TutorialsPoint MySQL Introduction
- Official MySQL Documentation
- Official MySQL Tutorial (pretty dense)
- Node MySQL Documentation on Github
- Types of SQL JOINs
- Joel Spolsky - Character Sets and Unicode
Design queries that retrieve the following data sets. Compare with classmates to see if your answers were correct.
- Find out how many todo items are on the list
- Find all the todo items that are marked as done
- Find all the todo items that are not marked as done
- Get all the todo items, sorted with the most recent first
- Get the single most recently added todo item
- Get all todo items about 'databases'
Build a simple Node application to connect to the class database (using require('mysql')). Run the above SQL statements through your Node app (either command-line input or hard-coded) and print the results on the command line.
Watch the previous session recorded on video here:
Part 1 : https://www.youtube.com/watch?v=NbB-EXAdKks&t
Part 2 : https://www.youtube.com/watch?v=yN5XyVSUj8k&t=4055s