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 release | Document description here | 2019/01/29 16:25 | Gerhard van der Linde |
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)
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)
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)
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)
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)
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)
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)
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)
mysql> SELECT distinct month(dob) mon FROM school.teacher; +------+ | mon | +------+ | 2 | | 9 | | 3 | | 11 | +------+ 4 rows in set (0.00 sec)
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)
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)
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)