mysql_02.sql

/* Statement :
•Import the SQL file described in this quest's Content tab.
•Retrieve all of the fields containing wizards born between 1975 and 1985
•Retrieve the wizards’ names whose first names start with the letter “H”
•Retrieve the first names and last names of the whole “Potter” family, ordered by first name
•Retrieve the oldest wizard’s first name, last name and birth date (this must work regardless of the table content) */


mysql> SELECT * FROM wizard WHERE birthday BETWEEN '1975.01.01' AND '1985.01.01';
+----+-----------+----------+------------+-------------+---------------------------------------+-----------+
| id | firstname | lastname | birthday   | birth_place | biography                             | is_muggle |
+----+-----------+----------+------------+-------------+---------------------------------------+-----------+
| 29 | harry     | potter   | 1980-07-31 | london      |                                       |         0 |
| 30 | hermione  | granger  | 1979-09-19 |             | Friend of Harry Potter                |         0 |
| 32 | ron       | weasley  | 1980-03-01 |             | Best friend of Harry                  |         0 |
| 33 | ginny     | weasley  | 1981-08-11 |             | Sister of Ron and girlfriend of Harry |         0 |
| 34 | fred      | weasley  | 1978-04-01 |             |                                       |         0 |
| 35 | george    | weasley  | 1978-04-01 |             |                                       |         0 |
| 38 | drago     | malefoy  | 1980-06-05 |             |                                       |         0 |
| 42 | dudley    | dursley  | 1980-06-23 |             | Cousin d'Harry                        |         1 |
+----+-----------+----------+------------+-------------+---------------------------------------+-----------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM wizard WHERE firstname LIKE 'H%';
+----+-----------+----------+------------+-------------+------------------------+-----------+
| id | firstname | lastname | birthday   | birth_place | biography              | is_muggle |
+----+-----------+----------+------------+-------------+------------------------+-----------+
| 29 | harry     | potter   | 1980-07-31 | london      |                        |         0 |
| 30 | hermione  | granger  | 1979-09-19 |             | Friend of Harry Potter |         0 |
+----+-----------+----------+------------+-------------+------------------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM wizard WHERE lastname LIKE 'Potter' ORDER BY firstname ASC;
+----+-----------+----------+------------+-------------+------------------------+-----------+
| id | firstname | lastname | birthday   | birth_place | biography              | is_muggle |
+----+-----------+----------+------------+-------------+------------------------+-----------+
| 29 | harry     | potter   | 1980-07-31 | london      |                        |         0 |
| 31 | lily      | potter   | 1960-01-30 |             | mother of Harry Potter |         0 |
+----+-----------+----------+------------+-------------+------------------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM wizard WHERE birthday = (SELECT max(birthday) FROM wizard);
+----+-----------+----------+------------+-------------+---------------------------------------+-----------+
| id | firstname | lastname | birthday   | birth_place | biography                             | is_muggle |
+----+-----------+----------+------------+-------------+---------------------------------------+-----------+
| 33 | ginny     | weasley  | 1981-08-11 |             | Sister of Ron and girlfriend of Harry |         0 |
+----+-----------+----------+------------+-------------+---------------------------------------+-----------+
1 row in set (0.00 sec)