Skip to content

Latest commit

 

History

History
92 lines (60 loc) · 3.78 KB

MAKEME.md

File metadata and controls

92 lines (60 loc) · 3.78 KB
Raw

This Assignment assumes you have installed the software MySQL on your computer. If not, please do that first!

Assignment Databases Week 2

Todo list

  1. Practice the concepts
  2. Prep exercises
  3. MySQL exercises
  4. Keep practicing (Optional)

1. Practice the concepts

Let's do some interactive exercises first. In the following online course, do sections 3 (Aggregate Functions) and sections 4 (Multiple Tables).

Also make time to do lesson 6-12 from the following:

2. Prep exercises

Prep exercises are exercises that you should work on before the session on Sunday. These are a little more difficult or show an important concept and as such are a great exercise to talk about with your mentor. Have a solution ready by Sunday as you may be asked to show what you did.

The QA_PREP_EXERCISE.md file explains what needs to be done for the prep exercise this week. There will also be some questions at the bottom to think about. Go through them before the session on Sunday as it will be covered then.

3. MySQL exercises

You will need to do some research to solve these exercises. All the concepts necessary to solve these exercises are NOT covered in the reading material. This is on purpose.

This week we'll practice some more with writing SQL queries using JavaScript. For each exercise make a separate .js file; make sure to give it an appropriate name!

3.1. Exercise 1: Keys

  1. Create a table, called authors. Give it the following fields: (author_id(Primary Key), author_name, university, date_of_birth, h_index, gender)
  2. Write a query that adds a column called mentor to authors table that references the column author_id. For integrity add a foreign key on this column.

3.2. Exercise 2: Relationships

  1. Create another table, called research_Papers with the following fields: (paper_id, paper_title, conference, publish_date, ...)
  2. What is the relationship between Authors and Research papers ? Make necessary changes to authors and research_Papers tables and add more tables if necessary.
  3. Read exercises 3 and 4 and then add information (insert rows) of 15 authors and 30 research papers such that all queries in the exercises 3 and 4 will return some answers

3.3. Exercise 3: Joins

  1. Write a query that prints names of all authors and their corresponding mentors.
  2. Write a query that prints all columns of authors and their published paper_title. If there is an author without any research_Papers, print the information of that author too.

3.4 Exercise 4: Aggregate Functions

Write some queries to retrieve the following rows:

  1. All research papers and the number of authors that wrote that paper.
  2. Sum of the research papers published by all female authors.
  3. Average of the h-index of all authors per university.
  4. Sum of the research papers of the authors per university.
  5. Minimum and maximum of the h-index of all authors per university.

4. Keep practicing (Optional)

It is always good to keep practicing, so if you have time then have a go at the following:

SUBMIT YOUR ASSIGNMENT!

After you've finished your todo list it's time to show us what you got! The assignment that needs to be submitted is the following:

  1. MySQL exercises

Upload both to your forked Databases repository in GitHub. Make a pull request to HackYourFuture's forked repository.

Forgotten how to upload your Assignment? Go through the guide to learn how to do this again.

Deadline Tuesday 23.59 CET