May 14th: Database Relationships

For this challenge, you are hired are a consultant at TocBrite, You will be helping them fetch data for their application.

Their database schema consists of :

They have the following tables: Users, Events, Roles, and Membership. A User can create many events. A user can have many roles and a role can have many users. Also a User can only have one type of membership paid or premium, and their membership can be active or inactive.

For this coding challenge, you will need to install the following:

Postgres, this is the sql database: https://www.postgresql.org/download/

PG Admin, a gui client tool to connect to the database : https://www.pgadmin.org/download/

DATABASE CREDENTIALS:

Host: ec2522314156.compute1.amazonaws.com

Database:d21tjhl7aan1q8

User:chzowbzsqdveka

Port:5432

Password:bc4690eaa2dd242f6e987463636ecebbf09b88db2ff31839e1bc829cb4cc1fd8

Challenge:

1. Open PG Admin to connect to TocBrites Server
   1. Click Add New Server
   2. Enter the following Credentials provided during the meeting, following this site : https://medium.com/@vapurrmaid/getting-started-with-heroku-postgres-and-pgadmin-run-on-part-2-90d9499ed8fb
2. Select 2 users and create active memberships where one will be basic and one will be premium
   1. One to one Example :
      1. select * from users where id in (your_id_1, your_id_2);
      2. insert into membership (membership_type, user_id, is_active) values (‘paid’, your_id_1, true);
      3. Once completed verify your insert successfully happened by running the following:
         1. select users.id, users.email, membership.id, membership.user_id, membership.membership_type, membership.is_active from membership join users on membership.user_id = users.id;
      4. Extra Challenge: Create a few more memberships for users, and then run a sql query to list out all the users who got paid
   2. Basic Users are only allowed to create two event but premium users can create multiple events. For this exercise, we are going to create multiple events for our Premium users and basic users. Created by column represents the user id that created the event.
      1. One to Many Example
         1. select * from users where id in (your_id_1, your_id_2);
         2. insert into events (name, created_by, description) values (‘some event name’, your_user_id, “description”);
         3. Verify : select users.id, users.email, events.created_by, events.name from events join users on events.created_by = users.id;
         4. Extra challenge: Grab all users and events, group events by membership type
   3. User can have many roles and Role can have users. This only happens if the user has a premium membership. This user has a membership where it wants to give users under it’s specific roles. You will create the roles, “Admin” and “Event_Coordinator”. You will choose 3 Users. Give 1 of them of the admin role and the two others “Event Coordinator”
      1. Many to Many Example:
         1. insert into roles (name) values(“Event_Coordinator”);
         2. insert into user_roles (user_id, role_id) values (your_user_id, your_role_id);
         3. Challenge: Create the select query to verify your data
   4. Last Super Challenge:
      1. Create a select query to display users who have a premium membership type, a role of admin, and the events the user has created

SQL resource : https://www.w3schools.com/sql/default.asp