SQL Week 2 Exercise

sql-week2-exercise.md

Node with Postgres Exercise

This exercise requires a working Postgres DB server with the homework database from https://github.com/CodeYourFuture/SQL-Coursework-Week1/tree/main/2-classes-db.

1. Initialise a new Node/Express web server.
2. Set up a connection pool to the homework database with node-posgtres.
3. Create a GET /spends endpoint that returns the list of all spends as a JSON array of objects. Each object should contain the date, description and amount properties.
4. Create a GET /suppliers endpoint that returns the list of the names of all suppliers as a JSON array of strings. Make sure the response is an array of strings and not an array of objects!
5. Extend the GET /spends endpont to return a more compete list of spends. Each object should contain the expense_area, expense_type, supplier, date, description and amount properties. The objects should not include any IDs, that is, the tables should be joined together.

Example response for exercise 3

[
  {
    "date": "2021-03-31T22:00:00.000Z",
    "description": "Medical Equipment - Imaging System",
    "amount": "306375"
  },
  {
    "date": "2021-03-31T22:00:00.000Z",
    "description": "Annual Fee",
    "amount": "319646"
  },
  {
    "date": "2021-03-31T22:00:00.000Z",
    "description": "Staff Nurse",
    "amount": "351630"
  }
]

Example response for exercise 4

[
  "INTERCLASS PLC",
  "FRESENIUS MEDICAL CARE UK LTD",
  "SIEMENS HEALTHCARE LTD",
  "JMG ROOFING LTD"
]

Example response for exercise 5

[
  {
    "expense_area": "Balance Sheet",
    "expense_type": "AUC Additions",
    "supplier": "E MANTON LTD",
    "date": "2021-03-31T22:00:00.000Z",
    "description": "Refurbishment of Utilities at the Alexandra Hospital",
    "amount": "48270"
  },
  {
    "expense_area": "COO Operational Team",
    "expense_type": "External Contractors",
    "supplier": "TRANSFORM HOSPITAL GROUP LTD",
    "date": "2021-03-31T22:00:00.000Z",
    "description": "OMF Services",
    "amount": "48750"
  },
  {
    "expense_area": "Balance Sheet",
    "expense_type": "AUC Additions",
    "supplier": "SPELLER METCALFE MALVERN LTD",
    "date": "2021-03-31T22:00:00.000Z",
    "description": "Breast Screening unit extension",
    "amount": "49205"
  }
]