~~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 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)