Skip to main content

Simple Queries

With the view that we just created, we can for instance calculate the number of records for each group of people:

_________________________________________________________________________________________

sql>SELECT type, COUNT(*) AS total 
FROM onboard_people GROUP BY type ORDER BY type;
+------------+-------+
| type       | total |
+============+=======+
| craftsmen  |  2349 |
| impotenten |   938 |
| passengers |  2813 |
| seafarers  |  4468 |
| soldiers   |  4177 |
| total      |  2467 |
+------------+-------+

_________________________________________________________________________________________

Let's see how many impotenten we have in the dataset:

_________________________________________________________________________________________

sql>SELECT count(*) FROM impotenten;
+------+
| L1   |
+======+
|  938 |
+------+

_________________________________________________________________________________________

It is possible to play with the set in many ways, to find out several things that took place during the voyages of the ships, or the money that was earned.

A few examples are shown below:

_________________________________________________________________________________________

sql>SELECT COUNT(*) FROM voyages
WHERE particulars LIKE '%_recked%';
+------+
| L1   |
+======+
|  358 |
+------+
sql>SELECT chamber, CAST(AVG(invoice) AS integer) AS average
FROM invoices
WHERE invoice IS NOT NULL
GROUP BY chamber
ORDER BY average DESC;
+---------+---------+
| chamber | average |
+=========+=========+
| A       |  282996 |
| Z       |  259300 |
| H       |  150182 |
| R       |  149628 |
| D       |  149522 |
| E       |  149518 |
| null    |   83309 |
+---------+---------+
sql>CREATE VIEW extended_onboard AS
SELECT number, number_sup, trip, trip_sup,
    onboard_at_departure, death_at_cape,
    left_at_cape, onboard_at_cape,
    death_during_voyage, onboard_at_arrival,
    death_during_voyage - left_at_cape AS death_at_arrival
FROM onboard_people;
sql>\d
TABLE  voc.craftsmen
VIEW   voc.extended_onboard
TABLE  voc.impotenten
TABLE  voc.invoices
VIEW   voc.onboard_people
TABLE  voc.passengers
TABLE  voc.seafarers
TABLE  voc.soldiers
TABLE  voc.total
TABLE  voc.voyages

_________________________________________________________________________________________