Reading List
Notes
Intro
- Structured Query Language (pronounce S-Q-L or "sequel")
- communicate with database (SQLite, PostgreSQL, MySQL) → manage relational database systems
- can mix SQL & JavaScript
- stores data in tables: columns (categories) & rows (1 user)
- ? errors here are typically Runtime errors?
- arr of obj
- different to other languages: uses many keywords (760) instead of functions & libraries
- exec(query, bindParameters) is query() in PostgreSQL, execute() in Python SQLite
Best Practice:
- Carefully setting up TABLES, COLUMNS with constraints → very powerful, prevents data invalid, accidents
- Typical Constraints for Merkmal:
- foreign keys: tie tables together by certain Merkmal (user & discount_id)
- nullability: is it ok for some values to be null?
- uniqueness: where necessary, where not? 1-1, 1-many, many-1, many-to-many
- Example Constraints:
- email TEXT NOT NULL UNIQUE
- is_admin BOOLEAN NOT NULL DEFAULT FALSE
- foreign_key → is it ok to be null, for 2 rows to ref same?
- Example Tests:
- User can't REFERENCES non-existent discount
- User can register without discount (null)
- User can REFERENCES with existing discount
- 2 Users can't REFERENCES same discount
- Naming columns: login_count
Use