MySQL: Difference between revisions
Jump to navigation
Jump to search
m (→Exercises) |
m (→Exercises) |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 12: | Line 12: | ||
== Exercises == | == Exercises == | ||
'''1)''' | |||
<!-- Exercises are ready, but not shown - for reasons..... | <!-- Exercises are ready, but not shown - for reasons..... | ||
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. | 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. <span style="color: red;">You have the privilege to create a database with your DTU username, (the name of the | 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. <span style="color: red;">You have the privilege to create a database with your DTU username, (the name of the database is your username)</span>, nothing else. In that database, create tables that match the 3 files. Load the files into the tables. | ||
Table overview: | Table overview: | ||
| Line 25: | Line 24: | ||
disease.csv | disease.csv | ||
cpr, name of disease, date of discovery | cpr, name of disease, date of discovery | ||
--> | |||
'''2)''' | '''2)''' | ||
| Line 30: | Line 30: | ||
# Who do not have a father ? | # Who do not have a father ? | ||
# How many do not have a mother? | # How many do not have a mother? | ||
# How many cases of cancer - any type? | # How many cases of cancer - any type? You get to decide if a tumor also means cancer. | ||
# How many persons had cancer – any type? | # How many persons had cancer – any type? | ||
# Who are or have been married to someone named ’Finn’ (male name)? | # Who are or have been married to someone named ’Finn’ (male name)? | ||
Latest revision as of 12:51, 17 August 2026
| 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)
2) Use the SQL language to answer some questions about the data or manipulate it in some ways.
- Who do not have a father ?
- How many do not have a mother?
- How many cases of cancer - any type? You get to decide if a tumor also means cancer.
- How many persons had cancer – any type?
- Who are or have been married to someone named ’Finn’ (male name)?
- 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.
- 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.