This is our sequel to the databases workshops :)
- Fork this repository and create a new Cloud9 project by cloning your fork
- All exercises to be done on master, in a file called
exercise-#.js
- Some exercises may require NPM packages. You will need to use
npm install --save
to get these packages. Thepackage.json
should be part of the same commit where you start using the required package(s). For example, if one of the exercises requires the use of therequest
library, then thepackage.json
for that submission should contain the dependency, and it should be committed at the same time as theexercise-#.js
thatrequire
s it. - A
boilerplate.js
file is made available to you. It contains the base code to load the MySQL library, make a first query, extract the rows as well as close the connection. You may copy this file for each exercise so that you have a base to get started from.
Before starting the exercises, import the file called data.sql
in MySQL. This will create a database called addressbook
. The database will contain a few tables. Explore all the tables using describe
to get a feel for this address book schema. Don't come back here until you know the table structure well! It'll make it easier to do the exercises.
- Write a program that fetches all the databases in MySQL and prints them nicely on the screen.
- Don't hesitate to use
colors
orcli-table
or any other module of your choice to make the output nicer.
- Write a program that fetches the first 5 accounts in the addressbook database
- For each account,
console.log
a line with the account's ID and email, like this:#1:
email@domain.com
- Use the
colors
NPM module with the.bold
option to achieve this effect
- Write a program that fetches all the accounts and their addressbooks.
- Output one line for each account as in Exercise 4, followed by a listing of all the address book names for that account, one per line
- Make the output look nice in any way you like
- Here is an example:
#1: john@smith.com
business contacts
friends
#2: jane@smith.com
...
- Notice that for the previous exercise, Account #5 did not appear in the listing. Don't come back here until you have re-checked the previous exercise and noticed for yourself that Account #5 is missing.
- The reason for this is because Account #5 does not have any AddressBook, so doing the JOIN left it out.
- Read and understand this article on SQL JOINs, more specifically about the
LEFT JOIN
. - Based on your new understanding, create a similar program to Exercise #4.
- The only difference, if an account does not have any address book, print it like this:
#3: xxx@yyy.com
--no address books--
- Write a program that fetches all accounts, their addressbooks, and entries.
- Hint #1: you will have two
JOIN
s in your query. - Hint #2: you will need to use
AS
to give each column a unique name - Once you receive your results in JavaScript land, transform them into an array of accounts with nested relations.
- Here is an example of the end result we are looking for. Note you will probably need to use some of these array methods we saw together in week 1. This is a good time to remind yourself of them, specifically
map
andreduce
.
[
{
id: 1,
email: 'john@smith.com',
addressBooks: [
{
id: 1,
name: 'business contacts',
entries: [
{
id: 1,
firstName: 'John',
lastName: 'Connor'
},
{
id: 10,
firstName: 'Sarah',
lastName: 'Connor'
}
]
},
{
// another address book for account 1...
}
]
},
{
// another account...
}
]