Pekka Alaluukas /Databases

Database Exercises

SERIE 1: Project database

Union / Project database

Note! The rows doesn't have to be exactly in the same order as they are below.

Print this table

+----------+------+
| city     |Amount|
+----------+------+
| HELSINKI |   2  |
| TAMPERE  |   1  |
| TURKU    |   3  |
| TOTAL    |   6  |
+----------+------+

Print this table

+------------+-------------+-------------+
| id_project | pname       | sum of hour |
+------------+-------------+-------------+
| 101        | Bookkeeping |        1300 |
| 102        | Billing     |         700 |
| 103        | Store       |         200 |
| 104        | Selling     |         900 |
| PROJ       | TOTAL       |        3100 |
+------------+-------------+-------------+

Print this table (you can do this also using SELECT IF )

+-------------+----------+
| pname       | place    |
+-------------+----------+
| Bookkeeping | TURKU    |
| Billing     | HELSINKI |
| Store       | HELSINKI |
| Selling     | TURKU    |
| Customers   | KUOPIO   |
| Statistics  | NO PLACE |
+-------------+----------+

Print this table

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

Print this table

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

SERIE 2: Library database

Union / Library database

Print this table

+----------+-----------------+
| author   | amount of books |
+----------+-----------------+
| Upton    |               1 |
| Vilppu   |               1 |
| Zelanshi |               2 |
| Speight  |               2 |
| Stokstad |               2 |
| Albrecht |               1 |
| Stice    |               1 |
| Marakas  |               2 |
| Williams |               1 |
| Manz     |               1 |
| Miller   |               1 |
| TOTAL    |              15 |
+----------+-----------------+

Toggle Menu