ironhack-edu/datav3_2.05_activities

API Documentation

We will start our project by first documenting all of the routes and data models for our API. Following best practices we will use verbs to specify the type of operation being done and nouns when naming endpoints.

Routes

Project routes

HTTP verb	URL	Request body	Action
GET	/api/projects	(empty)	Returns all the projects
POST	/api/projects	JSON	Adds a new project
GET	/api/projects/:projectId	(empty)	Returns the specified project
PUT	/api/projects/:projectId	JSON	Edits the specified project
DELETE	/api/projects/:projectId	(empty)	Deletes the specified project

Task routes

HTTP verb	URL	Request body	Action
POST	/api/tasks	JSON	Adds a new task
GET	/api/tasks/:taskId	(empty)	Returns the specified task
PUT	/api/tasks/:taskId	JSON	Edits the specified task
DELETE	/api/tasks/:taskId	(empty)	Deletes the specified task

Auth routes

HTTP verb	URL	Request Headers	Request Body
POST	/auth/signup	--	{ email, password, name }
POST	/auth/login	--	{ email, password }
GET	/auth/verify	Authorization: Bearer < JWT >	--

---

Models

Project Model

{
  title: String,
  description: String,
  tasks: [ { type: Schema.Types.ObjectId, ref: 'Task' } ]
}

Task Model

{
  title: String,
  description: String,
  project: { type: Schema.Types.ObjectId, ref: 'Project' }
}

User Model

{
  email: { type: String, unique: true, required: true },
  password: { type: String, required: true },
  name: { type: String, required: true },
}

datav3_2.05_activities's Issues

Typo

@sandrabosk On task 2, 'loan' instead of 'loads'

suggested solution doesnt work

select *, ('19' || substr(date,1,2)) as year from account;
returns 1 on all rows
in standard mysql mode will need to use CONCAT, or teach the students about sql compatibility modes, how to change the mode for concat || instead of OR
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
i feel like modes may be too complex for new sql users to follow ... could set as bonus

@sandrabosk

propose rephrasing to clarify

i worked through these instructions to review how to make them easier to follow

@sandrabosk

2.02 1

1. Select districts and salaries (from the district table) where salary is greater than 10000. I suggest using the case study extended here to work out which columns are required here. Return columns as district_name and average_salary.
2. Select those loans whose contract finished and were not paid back. Hint : you are looking at the loan column and you will need the extended case study information to tell you which value of status is required.
3. Select cards of type junior. Return just first 10 records in your query.
4. BONUS :

2.02 2

• Select those loans whose contract finished and not paid back. Return the debt value from the status you identified in the last activity, together with loan id and account id
• Calculate the urban population for all districts. Hint: you are looking for the no of inhabitants and the % of urban inhabitants in each district. Return columns as district_name and urban_population.
• On the previous query result - re run it but filtering out districts where the rural population is greater than 50%.
• BONUS :

2.02 3

1. Get all junior cards issued last year. Hint : use the numeric value 980000
2. Get the transactions for withdrawals that are not in cash. You will need the extended case study information to tell you which values are required here, and you will need to refer to conditions on two columns.
3. Refine your query from last activity on loans whose contract finished and not paid back - filtered to loans where they were left with a debt bigger than 1000. Return the debt value together with loan id and account id. Sort by the highest debt value to the lowest.
4. BONUS :

2.02 4

1. Get the biggest and the smallest transaction with non-zero values in the database (use the trans table)
2. Get account information with an extra column year showing the opening year as 'YY'. Eg 1995 will show as 95. Hint : look at the first two characters of the string date in the account table.
3. BONUS :