Pekka Alaluukas /Databases

Database Exercises

AGGREGATE FUNCTIONS

The aggregate functions are: sum, avg, min, max and count (and group_concat in MySQL).

GROUP BY

Remember that if, you put regular fields and aggregate functions to SELECT statement, you should allways add GROUP BY statement to your code.

WHERE and HAVING

If you have to make some selections from your data, you have to options: WHERE and HAVING. They have a different meaning: WHERE is used before grouping and HAVING is used after grouping.

SERIE 1: Project database

AGGREGATE FUNCTIONS / Project database

Shows maximum salary and minimum salary
Shows the number of rows in project table
Shows average of birthyears
Shows the amount of persons which lives in Turku
Shows sum of salary of persons which lives in Turku
Write Queries which will retrieve below views. Notice the column headers

+----------+-----------------+
| city     | Sum of Salaries |
+----------+-----------------+
| TURKU    |             127 |
| HELSINKI |              75 |
| TAMPERE  |              34 |
+----------+-----------------+

+----------+------------------+
| city     | Average Salaries |
+----------+------------------+
| TURKU    |            42.33 |
| HELSINKI |            37.50 |
| TAMPERE  |            34.00 |
+----------+------------------+

+------------+----------------+
| id_project | SUM(work_hour) |
+------------+----------------+
|        101 |           1300 |
|        102 |            700 |
|        103 |            200 |
|        104 |            900 |
+------------+----------------+

The same as above, but if Sum of hour is <500, project is not included in view

+------------+----------------+
| id_project | SUM(work_hour) |
+------------+----------------+
|        101 |           1300 |
|        102 |            700 |
|        104 |            900 |
+------------+----------------+

+----------+----------------------+
| city     | person               |
+----------+----------------------+
| HELSINKI | River,Simpson        |
| TAMPERE  | Daniels              |
| TURKU    | Morrison,Smith,Jones |
+----------+----------------------+

+----------+---------------------+
| place    | project             |
+----------+---------------------+
| NULL     | Statistics          |
| HELSINKI | Billing,Store       |
| KUOPIO   | Customers           |
| TURKU    | Bookkeeping,Selling |
+----------+---------------------+

SERIE 2: Library database

AGGREGATE FUNCTIONS / Library database

Show author and the amount of books of that author

Show author and the names of the books made by that author the result should be like this

  +----------+----------------------------------------------------+
| author   | books                                              |
+----------+----------------------------------------------------+
| Albrecht | Accounting Concepts                                |
| Manz     | Leadership Wisdom of Jesus                         |
| Marakas  | Management Info Systems,Management Info Systems    |
| Miller   | Business Law Today                                 |
| Speight  | Shaping Space,Art Since 1940                       |
| Stice    | Intermediate Accounting                            |
| Stokstad | Make it in Clay,Art History Vol II & ala carte lab |
| Upton    | Everything You Ever Wanted to Know                 |
| Vilppu   | Photography                                        |
| Williams | Management                                         |
| Zelanshi | Drawing Manual Vilppu,TBA                          |
+----------+----------------------------------------------------+

Which will show the postalcodes and amount of borrowers on that postalcode area (borrower table). Results should be like this

+------------+---------------------+
| postalcode | amount of borrowers |
+------------+---------------------+
| 90100      |                   4 |
| 90101      |                   4 |
| 90102      |                   4 |
| 90103      |                   4 |
| 90104      |                   4 |
+------------+---------------------+

Toggle Menu