MySQL

Previous: Databases

Next: Coding against databases

Material for the lesson

Note: The powerpoint has been updated with transactions, but not the video.
Video: Creating databases, data types
Video: Using SQL language to create tables, manipulate data and retrieve data.
Video: Functions in SQL (aggregate, string, math, etc), loading files into tables, vice versa
Powerpoint: MySQL
Video: Exercises

Exercises

1) In /home/projects/pr_course there are 3 files: persons.csv, marriage.csv and disease.csv. That is data that corresponds to the database design exercise 2 last week. Your task is to investigate the data, so you know how it looks. Then you must create a database on pupil1 MySQL database system with the ’mysql’ command. You have the privilege to create a database with your DTU username, (the name of the database is your username), nothing else. In that database, create tables that match the 3 files. Load the files into the tables.

Table overview:
persons.csv
   cpr, first name, last name, height, weight, biological mother, biological father
marriage.csv
   male cpr, female cpr, start of marriage, end of marriage
disease.csv
   cpr, name of disease, date of discovery

2) Use the SQL language to answer some questions about the data or manipulate it in some ways.

1. Who do not have a father ?
2. How many do not have a mother?
3. How many cases of cancer - any type? You get to decide if a tumor also means cancer.
4. How many persons had cancer – any type?
5. Who are or have been married to someone named ’Finn’ (male name)?
6. Create 2 imaginary persons. Marry them to each other. Make them have a kid. Give the kid a broken nose. Insert all this info in the database.
7. Delete everyone with the last name ’Rapacki’. Make sure that database is consistent afterwards, like there is no marriages to non-existing persons or references to non-existing parents.

The solutions to be handed in are the SQL statements, that solves the problems.