Databases
I literally have a massive database of cat sounds.
Contents
- Learning Objectives
- Suggested Study
- Break-Down
- Class Recordings
- study.hackyourfuture.be
- home.hackyourfuture.be
Learning Objectives
What can you expect to learn in this module?
Programming Skills
- Learn what relational database management systems (RDBMS) are
- Learn about relational models, tables, columns, rows, primary keys, foreign keys and relationships
- Learn how to write structured query language (SQL)
- Familiarize some theoretical concepts: information systems, databases, RDMBs, relational models, tables, records, attributes, fields and keys
- Learn how to navigate a (SQLite) database
- Learn how to write basic SQL queries to read data using following statements:
SELECT
,ORDER BY
,SELECT DISTINCT
,WHERE
,LIMIT
,BETWEEN
,LIKE
,GROUP BY
,JOIN
- Learn how to write basic SQL queries to create, update and delete records and tables using the following statements:
INSERT
,UPDATE
,DELETE
,CREATE TABLE
,ALTER TABLE
,DROP TABLE
- Learn about SQL injection
Tooling
-
SQLite - a SQL database engine
Using the Chinook database of which you can find the file here (
chinook-database/chinook.sqlite
) and the ERD here (chinook-database/chinook-schema.png
).
Module Projects
Projects will continue from the project of (hyf-backend-introduction
) of the previous module, but with an SQLite database instead of a .json file.
Suggested Study
Helpful resources for this module
expand/collapse
SQL
Tooling
- TablePlus is a modern, native, and friendly GUI tool for several relational databases: SQLite, MySQL, PostgreSQL & more.
- DB Browser for SQLite is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.
- SQLite Viewer is an online SQLite editor.
- SQLite Editor by SQLite Tutorial is also an online SQLite editor, specifically for the Chinook sample database.
- vscode-sqlite is a VSCode extension to explore and query SQLite databases.
SQLite & Node.js
- ./node-query-scripts - practice querying an SQLite database from Node.js
- References
Week 1
- Familiarize some theoretical concepts: information systems, databases, RDMBs, relational models, tables, records, attributes, fields and keys
- Learn how to navigate a (SQLite) database
- Learn how to write basic SQL queries using the following statements:
SELECT
,ORDER BY
,SELECT DISTINCT
,WHERE
,LIMIT
,BETWEEN
,LIKE
,GROUP BY
- Learn how to communicate with SQLite databases in JavaScript
- Plan and scope projects around your data
expand/collapse
Before Class
During Class
Before Break
After Break
Continue working on your project from the previous module (hyf-backend-introduction
). Instead of fetching data from JSON file a SQLite database can now be leveraged. Start from the slack-clone.sqlite file. Focus on reading from the database.
After Class
Continue working on your project.
Impress Yourselves
Here's a starter repository you can use, but you don't have to.
- [ ] [repo](https://github.com/_/_) (with a complete README)
- Project Planning
- [ ] [Backlog](https://github.com/_/_/tree/master/project-planning/backlog.md)
- [ ] [Development Strategy](https://github.com/_/_/tree/master/project-planning/development-strategy.md)
- [ ] [Project board](https://github.com/_/_/projects/_)
- Implementation
- [ ] Deployed - [deployment link]()
- [ ] A working frontend in the `/client` directory
- [ ] Uses the SQLite Chinook database
- [ ] A working `/api` to access the the database
Week 2
- Learn about primary keys, foreign keys, relationships and SQL injection
- Learn how to write SQL queries to read data using following statements:
JOIN
- Learn how to write basic SQL queries to create, update and delete records and tables using the following statements:
INSERT
,UPDATE
,DELETE
,CREATE TABLE
,ALTER TABLE
,DROP TABLE
expand/collapse
Before Class
During Class
Before Break
After Break
Continue working on your project from the previous week. Focus on inserting, updating and deleting from the database.
After Class
Finish working on your project.
Class Recordings
- Students: Here you can find recordings of this module from past classes. Enjoy!
- Coaches: When sending your PR's with links please ...
- Indicate which class you were teaching
- Which week it was (if the module is more than 1 week)
- Give your name
- and a helpful description
Class 7-8
Class 9-10
- Week 1
- Week 2 - server crash :(
Class 11-12
Dimitri Roose, Randi