Scribd
Upload
87 views

Nutritics MySQL Test

The document describes a MySQL database containing food consumption data from clients. It includes four tables: [1] trackerdata logs all foods eaten by each user at each meal, [2] trackercollectors archives available meals, [3] clients archives users, and [4] allfoods archives available foods. It poses four assessment questions: [1] find most common foods per meal/age range, [2] predict likely future foods based on weighted parameters, [3] update question 2 to include personal eating histories, [4] recommend optimizations to the database schema. The assessment will evaluate query competency, problem solving, performance, and code quality.

Uploaded by

Rishabh Jain
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
87 views

Nutritics MySQL Test

The document describes a MySQL database containing food consumption data from clients. It includes four tables: [1] trackerdata logs all foods eaten by each user at each meal, [2] trackercollectors archives available meals, [3] clients archives users, and [4] allfoods archives available foods. It poses four assessment questions: [1] find most common foods per meal/age range, [2] predict likely future foods based on weighted parameters, [3] update question 2 to include personal eating histories, [4] recommend optimizations to the database schema. The assessment will evaluate query competency, problem solving, performance, and code quality.

Uploaded by

Rishabh Jain
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

MySQL Test

The attached tables represent foods eaten by clients (users) over a given period. Each food is
tagged to a collector which indicates the meal in which the food was consumed.

Tables Included:

● trackerdata: a log of all foods & quantities eaten for each user at each meal
● trackercollectors: an archive of meals available
● clients: an archive of users available
● allfoods: an archive of foods available

Assessment Questions

Write queries which find the following data. Please write 1 query for each question 1-3. You may
use PHP if necessary:

1. Using the trackerdata table, find the most common 3 foods eaten for each meal, in each
age range: 18-55, 56-65, 66-75, >75 ?
2. Predict which foods will be most likely eaten tomorrow based on the following
parameters. The points below should be used to weight each parameter’s contribution
towards the resultset. Use variables at the top of the query for each parameter. You may
add additional parameters if you wish (for example allfoods.userCount).
a. Meal - 4 points
b. Client’s Gender - 2 points
c. Client’s Age - 2 points
d. Client’s Country - 3 points
e. Day of the week today - 1 points

Sample Input:
SET @meal = 2, @gender = ‘m’, @age = 26, @country = ‘Ireland’, @dayOfWeek = 1;
Sample Output:

foodID name points

123 Food 1 1051

456 Food 2 912

...etc

3. Update the query from question #2 to include each client’s personal eating history as an
added parameter. If there is no personal data available, fall back to the generic
parameters. The input variables from #2 should be replaced with a single variable:
clientID.
4. Please give some simple recommendations on how you might optimise this database
schema. Be sure to explain your reasoning (query speed, data robustness, security, etc)

Assessment criteria:

You will be assessed based on the following criteria:

● Competency & completion of all tasks


● Comprehension and problem solving
● Query performance
● Code formatting & comments

Submission Guidelines

Please submit all responses in a single text or word document with clear labelling, font
formatting and code commenting. If you have points of feedback or recommendations, please
include these inside the document.

You might also like