CharityBunyon commented 4 years ago

Developer Story

As a developer, I am going to create a database that will show the relationships between multiple tables.

AC

WHEN THEN AND

Dev Notes

[ ] create database activateMe
[ ] create tables
[ ] insert data into tables

CharityBunyon commented 4 years ago

create table [User]( ID int primary key identity (1,1) not null, firstname varchar(255) not null, lastname varchar(255) not null, dateJoined DateTime not null, userName varchar(255)null, Email varchar(255) null, city varchar(255) null, [state] varchar(255) null, imageUrl varchar(255) null, points int null, badgeId varchar(255) null )

create table Badges ( badgeId int primary key identity (1,1) not null, [name] varchar(255) null, pointValue int null, imageUrl varchar(255) null )

create table SampleExercises ( id int primary key identity (1,1) not null, [name] varchar(255) not null, imageUrl varchar(255) null, link varchar(255) null )

create table SampleRecipes ( id int primary key identity (1,1) not null, [name] varchar(255) not null, imageUrl varchar(255) not null, link varchar(255) not null )

create table Food ( id int primary key identity (1,1) not null, [name] varchar(255) not null, foodType varchar(255) not null, quantity int null, calories int null, points int null )

create table FoodCategory ( id int primary key identity (1,1) not null, [type] varchar(255) not null, )

create table Exercise ( id int primary key identity (1,1) not null, [name] varchar(255) not null, categoryType varchar(255) not null, [time] int null, calories int null, points int null )

create table ExerciseCategory ( id int primary key identity (1,1) not null, [type] varchar(255) not null, )

create table Dashboard ( id int primary key identity (1,1) not null, userPoints int null, badgeId int null, sampleExerciseId int null, sampleRecipesId int null, )

create table [Log] ( id int primary key identity (1,1) not null, exerciseId int not null, foodId int not null, caloriesFromFood int null, )

CharityBunyon commented 4 years ago

Table Data

insert into [User] (firstname, lastname, dateJoined, username, Email, city, state, imageUrl, points) values('James', 'Baldwin', getdate(), 'jbadwin', 'jBadwin@test.com', 'Harlem', 'NYC','https://www.biography.com/.image/t_share/MTE4MDAzNDEwNDQ0NzE1NTM0/james-baldwin-9196635-2-402.jpg' ,0,), ('Maya', 'Angelou', getdate(), 'mAngelou', 'mAngelou@test.com','St.Louis', 'MO', 'https://static01.nyt.com/images/2014/05/29/obituaries/ANGELOU/ANGELOU-superJumbo.jpg', 0), ('Jean-Michel', 'Basquiat', getdate(), 'jBasquiat', 'jBasquiat@test.com', 'Brooklyn', 'NYC', 'https://uploads3.wikiart.org/images/jean-michel-basquiat.jpg!Portrait.jpg', 0)

insert into Food(name, food_type, quantity, calories, points, userId) values('broccoli', 'vegetable', 50, 150, 1 ), ('Almonds', 'Nuts & Seeds', 100, 200, 1), ('bell pepper','vegetable', 50, 150, 2),('Black Beans', 'Legumes', 50, 150, 3),

insert into Food_Category(type, point_multiplier) values('Vegetable', 3),('Fruit', 2), ('Whole Grains', 1), ('Legumes', 3), ('Nuts & Seeds', 2)

insert into Exercise_Category(type, point_multiplier) values('Endurance', 3),('Strength', 3), ('Flexibility', 2), ('Balance', 1)

insert into Exercise([name], categoryType, [time], calories, points, userId) values('weight training', 'strength', 60, 300, 180, 1), ('yoga', 'flexibility', 30, 60, 20, 1), ('running', 'endurance', 30, 90, 45, 2), ('cycling', 'endurance', 30, 90, 45, 2), ('squats', 'strength', 10, 3, 20, 3)

insert into Log(exerciseId, foodId, caloriesFromFood, userId) (1, 1, 50, 1), (2, 2, 200, 1), (2, 2, 200, 2), (3, 3, 50, 3)

CharityBunyon commented 4 years ago

Points query

select 
*
from Badges b
where (select sum(points) as points
from food 
where userId = 9) >= b.pointValue

CharityBunyon / activateMe

Create Database #3

Developer Story

AC

Dev Notes

Table Data