I literally have a massive database of cat sounds.
- Learning Objectives
- Suggested Study
- Break-Down
- Class Recordings
- study.hackyourfuture.be
- home.hackyourfuture.be
What can you expect to learn in this module?
- 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
-
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).
Projects will continue from the project of (hyf-backend-introduction) of the previous module, but with an SQLite database instead of a .json file.
Helpful resources for this module
expand/collapse
- 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.
- ./node-query-scripts - practice querying an SQLite database from Node.js
- References
- 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
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.
Continue working on your project.
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- 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
Continue working on your project from the previous week. Focus on inserting, updating and deleting from the database.
Finish working on your project.
- 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
- Week 1
- Week 2 - server crash :(
Dimitri Roose, Randi