HDip Data Analytics

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
~~CLOSETOC~~ 
|<100% 25% - >|
^  \\ DATA ANALYTICS REFERENCE DOCUMENT\\ \\   ^^
^  Document Title:|Applied Databases|
^  Document No.:|Topic 2 Exercise Sheet|
^  Student:|Gerhard van der Linde|



**REVISION HISTORY**
|< 100% 10% - - 10% 17% 10% >|
^  \\  Revision\\  \\  ^\\ Details of Modification(s)^\\ Reason for modification^  \\ Date  ^  \\ By  ^
|  [[:doku.php?id=notes&do=revisions|0]]  |Draft release|Document description here|  2019/01/29 16:25  |  Gerhard van der Linde  |


----

====== Applied Databases - Week 2 ======


===== 1. Get school.sql from Moodle and import it into MySQL. =====


  * Right Click and select “save as”
  * mysql -u root -p <school.sql

===== 2. What is the maximum length of data that can be inserted into the Name attribute of the subject table? =====


<code mysql>
mysql> describe subject;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| Name          | varchar(15) | NO   | PRI | NULL    |       |
| Teacher       | varchar(20) | YES  |     | NULL    |       |
| OnLeavingCert | tinyint(1)  | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
</code>

===== 3. What is the Primary Key of the teacher table? =====

<code mysql>
mysql> describe teacher;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| tid        | int(11)       | NO   | PRI | NULL    | auto_increment |
| Name       | varchar(20)   | YES  |     | NULL    |                |
| level      | enum('J','L') | YES  |     | NULL    |                |
| experience | int(11)       | YES  |     | NULL    |                |
| dob        | date          | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
</code>

===== 4. What is the Primary Key of the subject table? =====


<code mysql>
mysql> describe subject;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| Name          | varchar(15) | NO   | PRI | NULL    |       |
| Teacher       | varchar(20) | YES  |     | NULL    |       |
| OnLeavingCert | tinyint(1)  | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
</code>
 
===== 5. Show all data contained in the subject table. =====


<code mysql>
mysql> select * from subject;
+-----------+--------------+---------------+
| Name      | Teacher      | OnLeavingCert |
+-----------+--------------+---------------+
| Biology   | Mr. Pasteur  |             1 |
| Colouring | Mr. Picasso  |             0 |
| English   | Mr. Kavanagh |             1 |
| French    | Ms. Dubois   |             1 |
| Maths     | Mr. Hawking  |             1 |
| Religion  | Fr. Lynch    |             1 |
| Spelling  | Ms.
Smith   |             0 |
+-----------+--------------+---------------+
7 rows in set (0.00 sec) 
</code>

===== 6. Show all names of all subjects that are on the leaving cert. =====


<code mysql>
mysql> select * from subject
    -> where OnLeavingCert = '1';
+----------+--------------+---------------+
| Name     | Teacher      | OnLeavingCert |
+----------+--------------+---------------+
| Biology  | Mr. Pasteur  |             1 |
| English  | Mr. Kavanagh |             1 |
| French   | Ms. Dubois   |             1 |
| Maths    | Mr. Hawking  |             1 |
| Religion | Fr. Lynch    |             1 |
+----------+--------------+---------------+
5 rows in set (0.00 sec)
</code>

===== 7. Show all name and experience of all teachers who are qualified to teach to Leaving Cert. =====


<code mysql>
mysql> select * from teacher
    -> where level = 'L';
+-----+-------------+-------+------------+------------+
| tid | Name        | level | experience | dob        |
+-----+-------------+-------+------------+------------+
|   1 | Mr. Pasteur | L     |         15 | 1960-02-02 |
|   2 | Ms. Dubois  | L     |         22 | 1967-09-02 |
|   4 | Mr. Hawking | L     |         40 | 1951-02-19 |
|   7 | Fr. Lynch   | L     |         55 | 1939-03-31 |
+-----+-------------+-------+------------+------------+
4 rows in set (0.00 sec)
</code>

===== 8. Show all details of all subjects who are taught by teachers whose title is not “Mr.” =====


<code mysql>
mysql> select * from teacher
    -> where name NOT LIKE 'Mr.%'
    -> ;
+-----+------------+-------+------------+------------+
| tid | Name       | level | experience | dob        |
+-----+------------+-------+------------+------------+
|   2 | Ms. Dubois | L     |         22 | 1967-09-02 |
|   3 | Ms. Smith  | J     |          4 | 1980-03-23 |
|   7 | Fr. Lynch  | L     |         55 | 1939-03-31 |
+-----+------------+-------+------------+------------+
3 rows in set (0.00 sec)
</code>

===== 9. Show all details of all teachers who were born in January, February or March, and who can teach as far as Junior Cert only. =====


<code mysql>
mysql> select * from teacher
    -> where (month(dob) != 1
    -> or month(dob) != 2
    -> or month(dob) != 3)
    -> and level = 'j';
+-----+---------------+-------+------------+------------+
| tid | Name          | level | experience | dob        |
+-----+---------------+-------+------------+------------+
|   3 | Ms. Smith     | J     |          4 | 1980-03-23 |
|   5 | Mr. Kavanagh  | J     |         50 | 1949-11-01 |
|   6 | Mr. Picasso   | J     |         42 | 1939-03-30 |
+-----+---------------+-------+------------+------------+
3 rows in set (0.00 sec)
</code>

===== 10. Show all unique month names that teachers were born in. =====


<code mysql>
mysql> SELECT distinct month(dob) mon FROM school.teacher;
+------+
| mon  |
+------+
|    2 |
|    9 |
|    3 |
|   11 |
+------+
4 rows in set (0.00 sec)
</code>

===== 11. Show all details of all teachers, sorted by first by experience, then level. =====


<code mysql>
mysql> SELECT * FROM school.teacher
    -> order by experience, level;
+-----+---------------+-------+------------+------------+
| tid | Name          | level | experience | dob        |
+-----+---------------+-------+------------+------------+
|   3 | Ms. Smith     | J     |          4 | 1980-03-23 |
|   1 | Mr. Pasteur   | L     |         15 | 1960-02-02 |
|   2 | Ms. Dubois    | L     |         22 | 1967-09-02 |
|   4 | Mr. Hawking   | L     |         40 | 1951-02-19 |
|   6 | Mr. Picasso   | J     |         42 | 1939-03-30 |
|   5 | Mr. Kavanagh  | J     |         50 | 1949-11-01 |
|   7 | Fr. Lynch     | L     |         55 | 1939-03-31 |
+-----+---------------+-------+------------+------------+
7 rows in set (0.00 sec)
</code>

===== 12. Show all details of all subjects whose 3rd or 4th letter is “l”. Sort them by name. =====


<code mysql>
mysql> SELECT * FROM school.subject
    -> where name like '___i%'
    -> or name like '____i%'
    -> order by name;
+----------+--------------+---------------+
| Name     | Teacher      | OnLeavingCert |
+----------+--------------+---------------+
| English  | Mr. Kavanagh |             1 |
| Religion | Fr. Lynch    |             1 |
+----------+--------------+---------------+
2 rows in set (0.00 sec)
</code> 

===== 13. Show the name of all teachers who have 10, 15, 20, 25, 30, 35, 40, 45, 50, 55 or 60 years experience. Sort from youngest to oldest. =====

<code mysql>
mysql> SELECT * FROM school.teacher
    -> where experience in (10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60);
+-----+---------------+-------+------------+------------+
| tid | Name          | level | experience | dob        |
+-----+---------------+-------+------------+------------+
|   1 | Mr. Pasteur   | L     |         15 | 1960-02-02 |
|   4 | Mr. Hawking   | L     |         40 | 1951-02-19 |
|   5 | Mr. Kavanagh  | J     |         50 | 1949-11-01 |
|   7 | Fr. Lynch     | L     |         55 | 1939-03-31 |
+-----+---------------+-------+------------+------------+
4 rows in set (0.00 sec)
</code>