Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
mysql [2012/11/09 14:01] glaroc |
mysql [2014/04/01 16:56] (current) glaroc |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ======= Introduction to Database Management Systems with MySQL ======= | ======= Introduction to Database Management Systems with MySQL ======= | ||
| + | |||
| + | Workshop given by Guillaume Larocque, research professional at the QCBS, at McGill University on November 2 and 9, 2012. | ||
| [[http://prezi.com/vswzpe89tbwp/present/?auth_key=gv8krfa&follow=ktr6emnemif_|Link to Prezi presentation - Simultaneous presentation]] | [[http://prezi.com/vswzpe89tbwp/present/?auth_key=gv8krfa&follow=ktr6emnemif_|Link to Prezi presentation - Simultaneous presentation]] | ||
| Line 162: | Line 164: | ||
| </file> | </file> | ||
| ++++ | ++++ | ||
| - | However, we will use LibreOffice instead | + | However, we will use LibreOffice instead... |
| ===== Importing data with LibreOffice ===== | ===== Importing data with LibreOffice ===== | ||
| Line 190: | Line 192: | ||
| ** MATH AND GROUP BY (Aggregate) Functions** | ** MATH AND GROUP BY (Aggregate) Functions** | ||
| - | |AVG() |Return the average value of the argument| | + | |avg() |Return the average value of the argument| |
| - | |COUNT(DISTINCT) |Return the count of a number of different values| | + | |count(DISTINCT) |Return the count of a number of different values| |
| - | |COUNT() |Return a count of the number of rows returned| | + | |count() |Return a count of the number of rows returned| |
| - | |GROUP_CONCAT() |Return a concatenated string| | + | |group_concat() |Return a concatenated string| |
| - | |MAX() |Return the maximum value| | + | |max() |Return the maximum value| |
| - | |MIN() |Return the minimum value| | + | |min() |Return the minimum value| |
| - | |STD() |Return the population standard deviation| | + | |stddev() |Return the population standard deviation| |
| - | |STDDEV_POP() |Return the population standard deviation| | + | |stddev_pop() |Return the population standard deviation| |
| - | |STDDEV_SAMP() |Return the sample standard deviation| | + | |stddev_samp() |Return the sample standard deviation| |
| - | |STDDEV() |Return the population standard deviation| | + | |sum() |Return the sum| |
| - | |SUM() |Return the sum| | + | |var_pop() |Return the population standard variance| |
| - | |VAR_POP() |Return the population standard variance| | + | |var_samp() |Return the sample variance| |
| - | |VAR_SAMP() |Return the sample variance| | + | |variance() |Return the population standard variance| |
| - | |VARIANCE() |Return the population standard variance| | + | [[http://www.postgresql.org/docs/9.3/static/functions-aggregate.html|Full list]] |
| - | [[http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html|Full list]] | + | |
| \\ | \\ | ||
| \\ | \\ | ||
| Line 328: | Line 329: | ||
| </file> | </file> | ||
| - | ===== Exercise 4 - Working with multiple tables ===== | + | ===== Exercise 5 - Working with multiple tables ===== |
| Count the number of species in each lake. Display lake name and species number. | Count the number of species in each lake. Display lake name and species number. | ||
| Line 390: | Line 391: | ||
| [[http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html|Click here for a list of MySQL mathematical functions]] | [[http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html|Click here for a list of MySQL mathematical functions]] | ||
| - | ===== Exercise 5 - JOIN Operations ===== | + | ===== Exercise 6 - JOIN Operations ===== |
| |JOIN (INNNER JOIN, CROSS JOIN)| Keep all possible combinations of rows from A and B| | |JOIN (INNNER JOIN, CROSS JOIN)| Keep all possible combinations of rows from A and B| | ||
| |LEFT JOIN| Keep all records of table A, join with elements from B that are present in A| | |LEFT JOIN| Keep all records of table A, join with elements from B that are present in A| | ||
| Line 425: | Line 426: | ||
| </file> | </file> | ||
| - | ===== Exercise 6 - Subqueries ===== | + | ===== Exercise 7 - Subqueries ===== |
| Generate a table containing the count of the presence of each species in lakes North and South of the 50th parallel (latitude). | Generate a table containing the count of the presence of each species in lakes North and South of the 50th parallel (latitude). | ||
