Multi-Table Queries and Database Helpers

Topics

• Joins
• Database Queries
• Knex Queries
• Modular Code

Assignment

For this lab you will

• write SQL statements against the northwind.db3 database. Once you have the correct SQL Statement for each query, write it inside the queries.sql file under the corresponding comment.
• write the db helper methods for the schemes resource in ./schemes/scheme-model.js

Multi Table Queries

Use a graphical tool like SQLite Studio to open ./data/northwind.db3 and execute the following queries:

• Display the ProductName and CategoryName for all products in the database. Returns 77 records.

select p.ProductName, c.CategoryName from "Product" as p join "Category" as c on c.id = p.CategoryId;

• Display the order Id and shipper CompanyName for all orders placed before August 9 2012. Returns 429 records.

select o.id, s.CompanyName from "Order" o join "Shipper" s on s.id = o.ShipVia where o.OrderDate < "2012-08-09";

• Display the name and quantity of the products ordered in order with Id 10251. Sort by ProductName. Returns 3 records.

select p.ProductName, d.Quantity from "OrderDetail" d join "Product" p on d.ProductId = p.id where d.OrderId = "10251";

• Display the OrderID, curstomer's Company Name and the employee's Last Name for every order. All columns should be labeled clearly. Returns 16,789 records.

select o.id as OrderId, c.CompanyName as CustomerName, e.LastName as EmployeeName from "Order" o join "Customer" c on o.CustomerId = c.id join "Employee" e on o.EmployeeId = e.id

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 parameter.
  • 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
  • Resolves 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.