Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Appearance settings

bloominstituteoftechnology/webdb-iv-challenge

Open more actions menu

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

43 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

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.

About

Web DB IV Module Challenge

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Contributors 3

  •  
  •  
  •  
Morty Proxy This is a proxified and sanitized view of the page, visit original site.