3 hours
Go through the “Joining Table Data with SQL” section of https://teamtreehouse.com/library/querying-relational-databases
Work through the Codecademy SQL Tutorial Section 4 (Multiple Tables)
In SQLite on your own machine, you are going to create the tables for a microblogging platform (an app similar to Twitter). Your database should be able to store user information and posts by specific users. One post must belong to exactly one user. One user can have many posts. Later, we’ll add the ability for users to follow each other, but not now.
Here’s a sample schema:
- a table named: `users`
- which has a text field named: `name`
- and a text field named: `email`
- a table named: `posts`
- which has an integer field named: `user_id`
- and a text field named: `content`Enter the SQLite command line program using the terminal commad sqlite3 twitter.sqlite3. (This will save your database to a file called twitter.sqlite3). Once there, use SQL statements to create two tables according to the spec for the two tables in step 1.
Once you have your two tables set up, compare your table set-up with another apprentice’s.
Add sample data to the tables yourself (make up some users and posts).
Try writing queries that get data such as:
Now let’s perform a database migration. We will add a new column to an existing table in our database. Add a text field named: bio to your existing users table!
Add some sample data to your new bio fields, but not for every user (leave some of their bios blank).
If you complete the above, we’ll move on to creating a join table.
Add a new table named follows that has an int field follower_id and an int field followed_id. Draw a picture for this table.
follows, look for follower_id 2, and get all of the user IDs that are in those rows’ followed_id fields. Then get the users by that set of user IDs.followed_id)
follows, look for followed_id 2, and get all of the user IDs that are in those rows’ follower_id fields. Then get those users.