Today we will be working with multi-table queries on an existing database schema.
You can connect to a local sqlite database on the command line by running the
command sqlite3 dbName.sqlite3. Navigate into the /data directory and open
the dev.sqlite3 database this way. You can now run SQL queries against it.
(Don’t forget to end your commands with a ;).
You can exit by pressing CTRL-d. View the tables and their schema with the
commands .tables and .schema --indent table_name. For a more legible list of
a tables columns use PRAGMA table_info('table_name'). To have column names
appear in query output run .headers ON and .mode column.
See https://sqlite.org/cli.html for more configuration options.
We will be using the books.db3 in the root of the repository.
SELECT title, publisher FROM books
JOIN publishers ON publishers.id = books.publisher_id;SELECT reviews.rating, users.username, books.title FROM reviews
JOIN users ON users.id = reviews.user_id
JOIN books ON books.id = reviews.book_id
WHERE reviews.rating > 4;SELECT books.title, authors.name FROM books_authors
JOIN books ON books.id = books_authors.author_id
JOIN authors ON authors.id = books_authors.book_id;Write helpers and crud routes for owners and pets, with the ability to get all pets belonging to a specific owner.
| name | type | notnull | pk |
| id | integer | yes | yes |
| name | varchar(255) | yes | |
| varchar(255) | yes |
| name | type | notnull | pk |
| id | integer | yes | yes |
| specie | varchar(255) | yes |
| name | type | not null | pk | fk |
| id | integer | yes | yes | |
| name | varchar(255) | yes | ||
| age | integer | yes | ||
| owner_id | integer | yes | owners.id | |
| care_instructions | text | |||
| species_id | integer | yes | species.id |