Multi-Table Queries and Database Helpers

Topics

• Joins
• Database Queries
• Knex Queries
• Modular Code

Assignment

For this lab you will

• write SQL statements against a pre-populated database using an online tool. Once you have the correct SQL Statement for each query, write it inside the queries.md file under the appropriate heading.
• write the db helper methods for the schemes resource in ./schemes/scheme-model.js

Multi Table Queries

Visit SQL Try Editor at W3Schools.com using the Google Chrome (or Chromium if you use Linux) browser and write SQL queries for the following requirements:

• Display the ProductName and CategoryName for all products in the database. Shows 76 records.
• Display the OrderID and ShipperName for all orders placed before January 9, 1997. Shows 161 records.
• Display all ProductNames and Quantities placed on order 10251. Sort by ProductName. Shows 3 records.
• Display the OrderID, CustomerName and the employee's LastName for every order. All columns should be labeled clearly. Displays 196 records.

Database Methods

Write helpers methods in ./schemes/scheme-model.js that match the following specifications:

• find():
  • Calling find returns a promise that resolves to an array of all schemes in the database.
  • No steps are included.
• findById(id):
  • Expects a scheme id as its only paramater.
  • Resolve to a single scheme object.
  • On an invalid id, resolves to null.
• findSteps(id):
  • Expects a scheme id.
  • Resolves to an array of all correctly ordered step for the given scheme: [ { id: 17, scheme_name: 'Find the Holy Grail', step_number: 1, instructions: 'quest'}, { id: 18, scheme_name: 'Find the Holy Grail', step_number: 2, instructions: '...and quest'}, etc. ].
  • This array should include the scheme_name not the scheme_id.
• add(scheme):
  • Expects a scheme object.
  • Inserts scheme into the database.
  • Resolves to the newly inserted scheme, including id.
• update(changes, id):
  • Expects a changes object and an id.
  • Updates the scheme with the given id.
  • Resolves to the newly updated scheme object.
• remove(id):
  • Removes the scheme object with the provided id.
  • Resolves to the removed scheme
  • Reolves to null on an invalid id.
  • (Hint: Only worry about removing the scheme. The database is configured to automatically remove all associated steps.)

Schemes Schema

field	data type	metadata
id	unsigned integer	primary key, auto-increments, generated by database
scheme_name	string	required, unique

Steps Schema

field	data type	metadata
id	unsigned integer	primary key, auto-increments, generated by database
scheme_id	unsigned integer	foreign key referencing scheme.id, required
step_number	unsigned integer	required
instructions	string	required

API

The following endpoints are available to test the functionality of the model methods.

• GET /api/schemes/ - gets master list of schemes (without steps)
• GET /api/schemes/:id - gets a single scheme
• GET /api/schemes/:id/steps - gets all steps for a given scheme, ordered correctly
• POST /api/schemes - adds a new scheme
• PUT /api/schemes:id - updates a given scheme
• DELETE /api/schemes/:id - removes a given scheme and all associated steps

Stretch Problems

• In SQL Try Editor at W3Schools.com:
  • Displays CategoryName and a new column called Count that shows how many products are in each category. Shows 9 records.
  • Display OrderID and a column called ItemCount that shows the total number of products placed on the order. Shows 196 records.
• Add the following method to your API
  • addStep(step, scheme_id): This method expects a step object and a scheme id. It inserts the new step into the database, correctly linking it to the intended scheme.
  • You may use POST /api/schemes/:id/addStep to test this method.