As you have experienced, relationships between different database tables are a very common way of organizing data in a relational database system.

In this last set of challenges, youll be working with a blog database. This database will have a posts table, a users table and a comments table.

The posts table will have 1 reference column (the user_id column) to know what user created the content in a specific post.

On the other hand, the comments table will have the post_id column to know what post the comment belongs to and a user_id to know what user created the comment.

11. 1. Challenge

A simple blog database

A sql-5-7.sql file has been opened for you.

In the sql-5-7.sql file:

1. Use the blog database
2. Write a SQL RIGHT OUTER JOIN statement that joins the user_id column from the blog.posts table, the name column of the blog.users table and the body column of the blog.posts table together.

11. 2. Challenge

Defining the comments table

A mysql prompt has been opened for you.

Using the blog database, complete the following tasks:

1. Create a comments table with this definition (use either a single-line or a multi-line SQL statement):

+---------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-----------------+------+-----+---------+----------------+ | id | int(3) unsigned | NO | PRI | NULL | auto_increment | | body | text | YES | | NULL | | | user_id | int(7) unsigned | NO | MUL | NULL | | +---------+-----------------+------+-----+---------+----------------+

11. 3. Challenge

Altering the comments table

A mysql prompt has been opened for you.

Using the blog database (use either a single-line or a multi-line SQL statement):

1. Write a SQL ALTER TABLE statement that adds a post_idcolumn to the blog.comments table. This new column should be an INTEGER data type with a max. size of 3 digits, UNSIGNED, it should be NOT NULL and it should work as a FOREIGN KEY that uses as a reference the id column of the blog.posts table.