User Tools

Site Tools


submissions:worksheet:databases:topic2

DATA ANALYTICS REFERENCE DOCUMENT

Document Title:Applied Databases
Document No.:Topic 2 Exercise Sheet
Student:Gerhard van der Linde

REVISION HISTORY


Revision


Details of Modification(s)

Reason for modification

Date

By
0 Draft releaseDocument 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?

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)

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

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)

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

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)

5. Show all data contained in the subject table.

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) 

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

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)

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

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)

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

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)

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.

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)

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

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

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

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)

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

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)

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.

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)

submissions/worksheet/databases/topic2.txt · Last modified: 2020/06/20 14:39 by 127.0.0.1