https://teaching.healthtech.dtu.dk:443/22112/index.php?action=history&feed=atom&title=MySQL 2026-04-25T08:13:10Z Revision history for this page on the wiki MediaWiki 1.41.0 https://teaching.healthtech.dtu.dk:443/22112/index.php?title=MySQL&diff=89&oldid=prev 2024-09-13T06:00:26Z

<p><span dir="auto"><span class="autocomment">Exercises</span></span></p> <table style="background-color: #fff; color: #202122;" data-mw="interface"> <col class="diff-marker" /> <col class="diff-content" /> <col class="diff-marker" /> <col class="diff-content" /> <tr class="diff-title" lang="en"> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 08:00, 13 September 2024</td> </tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l16">Line 16:</td> <td colspan="2" class="diff-lineno">Line 16:</td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>&#039;&#039;&#039;1)&#039;&#039;&#039;</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>&#039;&#039;&#039;1)&#039;&#039;&#039;</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>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.</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>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.</div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>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. &lt;span style="color: red;"&gt;You have the privilege to create a database with your DTU username, (the name of the <del style="font-weight: bold; text-decoration: none;">databse </del>is your username)&lt;/span&gt;, nothing else. In that database, create tables that match the 3 files. Load the files into the tables.</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>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. &lt;span style="color: red;"&gt;You have the privilege to create a database with your DTU username, (the name of the <ins style="font-weight: bold; text-decoration: none;">database </ins>is your username)&lt;/span&gt;, nothing else. In that database, create tables that match the 3 files. Load the files into the tables.</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>  Table overview:</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>  Table overview:</div></td></tr> <tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l30">Line 30:</td> <td colspan="2" class="diff-lineno">Line 30:</td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># Who do not have a father ?</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># Who do not have a father ?</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># How many do not have a mother?</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># How many do not have a mother?</div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div># How many cases of cancer - any type?</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div># How many cases of cancer - any type? <ins style="font-weight: bold; text-decoration: none;">You get to decide if a tumor also means cancer.</ins></div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># How many persons had cancer – any type?</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># How many persons had cancer – any type?</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># Who are or have been married to someone named ’Finn’ (male name)?</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># Who are or have been married to someone named ’Finn’ (male name)?</div></td></tr> </table>

WikiSysop https://teaching.healthtech.dtu.dk:443/22112/index.php?title=MySQL&diff=88&oldid=prev 2024-09-13T05:57:09Z

<p><span dir="auto"><span class="autocomment">Exercises</span></span></p> <table style="background-color: #fff; color: #202122;" data-mw="interface"> <col class="diff-marker" /> <col class="diff-content" /> <col class="diff-marker" /> <col class="diff-content" /> <tr class="diff-title" lang="en"> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 07:57, 13 September 2024</td> </tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l32">Line 32:</td> <td colspan="2" class="diff-lineno">Line 32:</td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># How many cases of cancer - any type?</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># How many cases of cancer - any type?</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># How many persons had cancer – any type?</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># How many persons had cancer – any type?</div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div># Who are or have been married to someone named ’Finn’?</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div># Who are or have been married to someone named ’Finn’ <ins style="font-weight: bold; text-decoration: none;">(male name)</ins>?</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># 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.</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># 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.</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># 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.</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># 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.</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>The solutions to be handed in are the SQL statements, that solves the problems.</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>The solutions to be handed in are the SQL statements, that solves the problems.</div></td></tr> </table>

WikiSysop https://teaching.healthtech.dtu.dk:443/22112/index.php?title=MySQL&diff=43&oldid=prev 2024-06-17T09:22:20Z

<p><span dir="auto"><span class="autocomment">Material for the lesson</span></span></p> <table style="background-color: #fff; color: #202122;" data-mw="interface"> <col class="diff-marker" /> <col class="diff-content" /> <col class="diff-marker" /> <col class="diff-content" /> <tr class="diff-title" lang="en"> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 11:22, 17 June 2024</td> </tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l8">Line 8:</td> <td colspan="2" class="diff-lineno">Line 8:</td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=9162c944-634a-4c14-b784-af270125d681 Using SQL language to create tables, manipulate data and retrieve data.]&lt;br&gt;</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=9162c944-634a-4c14-b784-af270125d681 Using SQL language to create tables, manipulate data and retrieve data.]&lt;br&gt;</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=3158d3b6-b07a-4f3e-9211-af270125af81 Functions in SQL (aggregate, string, math, etc), loading files into tables, vice versa]&lt;br&gt;</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=3158d3b6-b07a-4f3e-9211-af270125af81 Functions in SQL (aggregate, string, math, etc), loading files into tables, vice versa]&lt;br&gt;</div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>Powerpoint: [https://teaching.healthtech.dtu.dk/material/22112/<del style="font-weight: bold; text-decoration: none;">HPCLife12</del>-MySQL.ppt MySQL]&lt;br&gt;</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>Powerpoint: [https://teaching.healthtech.dtu.dk/material/22112/<ins style="font-weight: bold; text-decoration: none;">22112_02</ins>-MySQL.ppt MySQL]&lt;br&gt;</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=a3d3779d-2d3b-4fa8-83f6-af170071d48b Exercises]</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=a3d3779d-2d3b-4fa8-83f6-af170071d48b Exercises]</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br></td></tr> </table>

WikiSysop https://teaching.healthtech.dtu.dk:443/22112/index.php?title=MySQL&diff=12&oldid=prev 2024-03-06T10:49:24Z

<p><span dir="auto"><span class="autocomment">Material for the lesson</span></span></p> <table style="background-color: #fff; color: #202122;" data-mw="interface"> <col class="diff-marker" /> <col class="diff-content" /> <col class="diff-marker" /> <col class="diff-content" /> <tr class="diff-title" lang="en"> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td> <td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 12:49, 6 March 2024</td> </tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l8">Line 8:</td> <td colspan="2" class="diff-lineno">Line 8:</td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=9162c944-634a-4c14-b784-af270125d681 Using SQL language to create tables, manipulate data and retrieve data.]&lt;br&gt;</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=9162c944-634a-4c14-b784-af270125d681 Using SQL language to create tables, manipulate data and retrieve data.]&lt;br&gt;</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=3158d3b6-b07a-4f3e-9211-af270125af81 Functions in SQL (aggregate, string, math, etc), loading files into tables, vice versa]&lt;br&gt;</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=3158d3b6-b07a-4f3e-9211-af270125af81 Functions in SQL (aggregate, string, math, etc), loading files into tables, vice versa]&lt;br&gt;</div></td></tr> <tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>Powerpoint: [https://teaching.healthtech.dtu.dk/material/<del style="font-weight: bold; text-decoration: none;">36612</del>/HPCLife12-MySQL.ppt MySQL]&lt;br&gt;</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>Powerpoint: [https://teaching.healthtech.dtu.dk/material/<ins style="font-weight: bold; text-decoration: none;">22112</ins>/HPCLife12-MySQL.ppt MySQL]&lt;br&gt;</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=a3d3779d-2d3b-4fa8-83f6-af170071d48b Exercises]</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=a3d3779d-2d3b-4fa8-83f6-af170071d48b Exercises]</div></td></tr> <tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br></td></tr> </table>

WikiSysop https://teaching.healthtech.dtu.dk:443/22112/index.php?title=MySQL&diff=11&oldid=prev 2024-03-06T10:49:02Z

<p>Created page with &quot;{| width=500 style=&quot;float:right; margin-left: 10px; margin-top: -56px;&quot; |Previous: <a href="/22112/index.php/Databases" title="Databases">Databases</a> |Next: <a href="/22112/index.php/Coding_against_databases" title="Coding against databases">Coding against databases</a> |} == Material for the lesson == Note: The powerpoint has been updated with transactions, but not the video.&lt;br&gt; Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=7b280a2a-256e-46c8-8f79-af2701260171 Creating databases, data types]&lt;br&gt; Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=9162c944-634a-4c14-b784-af270125d68...&quot;</p> <p><b>New page</b></p><div>{| width=500 style=&quot;float:right; margin-left: 10px; margin-top: -56px;&quot;<br /> |Previous: [[Databases]]<br /> |Next: [[Coding against databases]]<br /> |}<br /> == Material for the lesson ==<br /> Note: The powerpoint has been updated with transactions, but not the video.&lt;br&gt;<br /> Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=7b280a2a-256e-46c8-8f79-af2701260171 Creating databases, data types]&lt;br&gt;<br /> Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=9162c944-634a-4c14-b784-af270125d681 Using SQL language to create tables, manipulate data and retrieve data.]&lt;br&gt;<br /> Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=3158d3b6-b07a-4f3e-9211-af270125af81 Functions in SQL (aggregate, string, math, etc), loading files into tables, vice versa]&lt;br&gt;<br /> Powerpoint: [https://teaching.healthtech.dtu.dk/material/36612/HPCLife12-MySQL.ppt MySQL]&lt;br&gt;<br /> Video: [https://panopto.dtu.dk/Panopto/Pages/Viewer.aspx?id=a3d3779d-2d3b-4fa8-83f6-af170071d48b Exercises]<br /> <br /> == Exercises ==<br /> &lt;!-- Exercises are ready, but not shown - for reasons.....<br /> --&gt;<br /> &#039;&#039;&#039;1)&#039;&#039;&#039;<br /> 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.<br /> Your task is to investigate the data, so you know how it looks. Then you must create a database on &#039;&#039;&#039;pupil1&#039;&#039;&#039; MySQL database system with the ’mysql’ command. &lt;span style=&quot;color: red;&quot;&gt;You have the privilege to create a database with your DTU username, (the name of the databse is your username)&lt;/span&gt;, nothing else. In that database, create tables that match the 3 files. Load the files into the tables.<br /> <br /> Table overview:<br /> persons.csv<br /> cpr, first name, last name, height, weight, biological mother, biological father<br /> marriage.csv<br /> male cpr, female cpr, start of marriage, end of marriage<br /> disease.csv<br /> cpr, name of disease, date of discovery<br /> <br /> &#039;&#039;&#039;2)&#039;&#039;&#039;<br /> Use the SQL language to answer some questions about the data or manipulate it in some ways.<br /> # Who do not have a father ?<br /> # How many do not have a mother?<br /> # How many cases of cancer - any type?<br /> # How many persons had cancer – any type?<br /> # Who are or have been married to someone named ’Finn’?<br /> # 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.<br /> # 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.<br /> <br /> The solutions to be handed in are the SQL statements, that solves the problems.</div>

WikiSysop