REVISION HISTORY
Revision | Details of Modification(s) | Reason for modification | Date | By |
---|---|---|---|---|
0 | Draft release | Document description here | 2019/03/04 11:53 | 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)
MySQL - u root -p <employees.sql
mysql> SELECT emp_no, first_name, ucase(last_name) 'last_name' -> FROM employees.employees -> limit 10; +--------+------------+-----------+ | emp_no | first_name | last_name | +--------+------------+-----------+ | 10001 | Georgi | FACELLO | | 10002 | Bezalel | SIMMEL | | 10003 | Parto | BAMFORD | | 10004 | Chirstian | KOBLICK | | 10005 | Kyoichi | MALINIAK | | 10006 | Anneke | PREUSIG | | 10007 | Tzvetan | ZIELINSKI | | 10008 | Saniya | KALLOUFI | | 10009 | Sumant | PEAC | | 10010 | Duangkaew | PIVETEAU | +--------+------------+-----------+ 10 rows in set (0.00 sec)
SELECT * FROM employees.employees # order by length(last_name) # order by last_name # order by first_name order by length(first_name); SELECT * FROM employees.employees order by length(last_name), last_name, length(first_name), first_name;
mysql> SELECT emp_no,birth_date,first_name,last_name,gender,hire_date, concat(left(first_name,1), left(last_name,1)) 'Initials' -> FROM employees.employees -> limit 10; +--------+------------+------------+-----------+--------+------------+----------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | Initials | +--------+------------+------------+-----------+--------+------------+----------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | GF | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | BS | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | PB | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | CK | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | KM | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | AP | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | TZ | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | SK | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | SP | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | DP | +--------+------------+------------+-----------+--------+------------+----------+ 10 rows in set (0.00 sec)
mysql> SELECT * FROM employees.employees -> where gender = 'F' -> and (year(birth_date)>='1950' and year(birth_date)<='1959') -> and (hire_date>='1988-09-01' and hire_date<='1991-02-28') -> ; +--------+------------+------------+------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+------------+--------+------------+ | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 | | 10023 | 1953-09-29 | Bojan | Montemayor | F | 1989-12-17 | | 10041 | 1959-08-27 | Uri | Lenart | F | 1989-11-12 | | 10063 | 1952-08-06 | Gino | Leonhardt | F | 1989-04-08 | | 10074 | 1955-08-28 | Mokhtar | Bernatsky | F | 1990-08-13 | | 10088 | 1954-02-25 | Jungsoon | Syrzycki | F | 1988-09-02 | | 10099 | 1956-05-25 | Valter | Sullins | F | 1988-10-18 | +--------+------------+------------+------------+--------+------------+ 9 rows in set (0.03 sec)
mysql> SELECT round(avg(salary),2) 'avg_salary' FROM employees.salaries; +------------+ | avg_salary | +------------+ | 64417.59 | +------------+ 1 row in set (0.00 sec)
mysql> SELECT emp_no, round(avg(salary),2) avg_sal -> FROM employees.salaries -> group by emp_no -> limit 10; +--------+----------+ | emp_no | avg_sal | +--------+----------+ | 10001 | 75388.94 | | 10002 | 68854.50 | | 10003 | 43030.29 | | 10004 | 56512.25 | | 10005 | 87275.77 | | 10006 | 50514.92 | | 10007 | 70826.71 | | 10008 | 49307.67 | | 10009 | 78284.56 | | 10010 | 76723.00 | +--------+----------+ 10 rows in set (0.01 sec)
mysql> SELECT emp_no, cast(max(salary) as decimal(10,2)) max_sal -> FROM employees.salaries -> group by emp_no -> limit 10; +--------+----------+ | emp_no | max_sal | +--------+----------+ | 10001 | 88958.00 | | 10002 | 72527.00 | | 10003 | 43699.00 | | 10004 | 74057.00 | | 10005 | 94692.00 | | 10006 | 60098.00 | | 10007 | 88070.00 | | 10008 | 52668.00 | | 10009 | 94443.00 | | 10010 | 80324.00 | +--------+----------+ 10 rows in set (0.00 sec)
mysql> SELECT emp_no, -> avg(salary) -> FROM employees.salaries -> where emp_no in (10001, 10021, 10033, 10087) -> and salary > 80000 -> group by emp_no; +--------+-------------+ | emp_no | avg(salary) | +--------+-------------+ | 10001 | 83745.5714 | | 10021 | 83232.0000 | | 10087 | 99015.2500 | +--------+-------------+ 3 rows in set (0.01 sec)
note: forgot to round to 2 decimal points
mysql> SELECT emp_no, round(avg(salary)) as avg_sal -> FROM employees.salaries -> group by emp_no -> having avg_sal > 90000; +--------+---------+ | emp_no | avg_sal | +--------+---------+ | 10024 | 90572 | | 10068 | 101224 | | 10087 | 99015 | +--------+---------+ 3 rows in set (0.00 sec)
mysql> SELECT emp_no as ID, -> if(gender = 'M', 'Mr.','Ms.') as Gender, -> first_name as Name, -> last_name as Surname, -> gender as Gender -> FROM employees.employees -> order by emp_no -> limit 15; +-------+--------+-----------+-----------+--------+ | ID | Gender | Name | Surname | Gender | +-------+--------+-----------+-----------+--------+ | 10001 | Mr. | Georgi | Facello | M | | 10002 | Ms. | Bezalel | Simmel | F | | 10003 | Mr. | Parto | Bamford | M | | 10004 | Mr. | Chirstian | Koblick | M | | 10005 | Mr. | Kyoichi | Maliniak | M | | 10006 | Ms. | Anneke | Preusig | F | | 10007 | Ms. | Tzvetan | Zielinski | F | | 10008 | Mr. | Saniya | Kalloufi | M | | 10009 | Ms. | Sumant | Peac | F | | 10010 | Ms. | Duangkaew | Piveteau | F | | 10011 | Ms. | Mary | Sluis | F | | 10012 | Mr. | Patricio | Bridgland | M | | 10013 | Mr. | Eberhardt | Terkki | M | | 10014 | Mr. | Berni | Genin | M | | 10015 | Mr. | Guoxiang | Nooteboom | M | +-------+--------+-----------+-----------+--------+ 15 rows in set (0.00 sec)
Tax brackets are defined as follows:
Max Salary | Tax Bracket |
---|---|
Under 40,000 | 30% |
Under 60,000 | 40% |
Under 80,000 | 50% |
Over 80,000 | 60% |
mysql> SELECT emp_no as 'Employee Number', -> max(salary) as 'Max Salary', -> CASE -> when max(salary) < 40000 then '30%' -> when max(salary) < 60000 then '40%' -> when max(salary) < 80000 then '50%' -> else '60%' -> END as 'Tax Bracket' -> FROM employees.salaries -> group by emp_no -> limit 15; +-----------------+------------+-------------+ | Employee Number | Max Salary | Tax Bracket | +-----------------+------------+-------------+ | 10001 | 88958 | 60% | | 10002 | 72527 | 50% | | 10003 | 43699 | 40% | | 10004 | 74057 | 50% | | 10005 | 94692 | 60% | | 10006 | 60098 | 50% | | 10007 | 88070 | 60% | | 10008 | 52668 | 40% | | 10009 | 94443 | 60% | | 10010 | 80324 | 60% | | 10011 | 56753 | 40% | | 10012 | 54794 | 40% | | 10013 | 68901 | 50% | | 10014 | 60598 | 50% | | 10015 | 40000 | 40% | +-----------------+------------+-------------+ 15 rows in set (0.00 sec)
SELECT *, if(datediff(to_date,from_date)<356, "under 1 year", "over 1 year") as Time FROM employees.salaries # having Time like '%under%' ;
+--------+--------+------------+------------+-------------+ | emp_no | salary | from_date | to_date | Time | +--------+--------+------------+------------+-------------+ | 10001 | 60117 | 1986-06-26 | 1987-06-26 | over 1 year | | 10001 | 62102 | 1987-06-26 | 1988-06-25 | over 1 year | | 10001 | 66074 | 1988-06-25 | 1989-06-25 | over 1 year | | 10001 | 66596 | 1989-06-25 | 1990-06-25 | over 1 year | | 10001 | 66961 | 1990-06-25 | 1991-06-25 | over 1 year | | 10001 | 71046 | 1991-06-25 | 1992-06-24 | over 1 year | | 10001 | 74333 | 1992-06-24 | 1993-06-24 | over 1 year | | 10001 | 75286 | 1993-06-24 | 1994-06-24 | over 1 year | | 10001 | 75994 | 1994-06-24 | 1995-06-24 | over 1 year | | 10001 | 76884 | 1995-06-24 | 1996-06-23 | over 1 year | +--------+--------+------------+------------+-------------+ 10 rows in set (0.00 sec)
HINT: Don’t for get to change the delimiter when writing the function and change it back to a semi-colon when the function is written.
CREATE FUNCTION `getage`(d1 date, d2 date) RETURNS float(5,1) DETERMINISTIC BEGIN RETURN round(datediff(d2,d1)/365,1); END
+--------+------------+------------+-----------+--------+------------+------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | Age | +--------+------------+------------+-----------+--------+------------+------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 32.8 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | 21.5 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | 26.8 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | 32.6 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | 34.7 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | 36.1 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | 31.7 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | 36.6 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | 32.9 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | 26.2 | +--------+------------+------------+-----------+--------+------------+------+ 10 rows in set (0.00 sec)
CREATE DEFINER=`jattie`@`%` PROCEDURE `hires`(y integer, m integer) DETERMINISTIC BEGIN SELECT * FROM employees.employees where year(hire_date) = y and month(hire_date) = m; END
call hires(1988,9);
+--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10034 | 1962-12-29 | Bader | Swan | M | 1988-09-21 | | 10035 | 1953-02-08 | Alain | Chappelet | M | 1988-09-05 | | 10088 | 1954-02-25 | Jungsoon | Syrzycki | F | 1988-09-02 | +--------+------------+------------+-----------+--------+------------+ 3 rows in set (0.01 sec)
HINT: To call a procedure with a NULL value for month (assuming in this case month is the second parameter) procedure_name(1985, NULL). To check if a parameter, e.g. m, is NULL say IF M IS NULL THEN To check if a parameter, e.g. m, is not NULL say IF M IS NOT NULL THEN.
CREATE DEFINER=`jattie`@`%` PROCEDURE `hires_2`(y integer, m integer) DETERMINISTIC BEGIN if m is null then select * from employees where year(hire_date) = y; else select * from employees where year(hire_date) = y and month(hire_date) = m; end if; END
mysql> call hires_2(1988, null); +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10021 | 1960-02-20 | Ramzi | Erde | M | 1988-02-10 | | 10034 | 1962-12-29 | Bader | Swan | M | 1988-09-21 | | 10035 | 1953-02-08 | Alain | Chappelet | M | 1988-09-05 | | 10039 | 1959-10-01 | Alejandro | Brender | M | 1988-01-19 | | 10052 | 1961-02-26 | Heping | Nitsch | M | 1988-05-21 | | 10065 | 1963-04-14 | Satosi | Awdeh | M | 1988-05-18 | | 10072 | 1952-05-15 | Hironoby | Sidou | F | 1988-07-21 | | 10088 | 1954-02-25 | Jungsoon | Syrzycki | F | 1988-09-02 | | 10099 | 1956-05-25 | Valter | Sullins | F | 1988-10-18 | +--------+------------+------------+-----------+--------+------------+ 9 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
mysql -u root -p <garage.sql
show tables; describe manufacturer; show create table manufacturer; show create table vehicle;
The manufacturer table contains a manufacturer code, name and description of the manufacturer of the vehicle.
The vehicle table contains the vehicle deteails with a reference to the manufacturer code described as a foreign key constraint. This foreign key links the two table with the manu_code present in both tables.
mysql> SELECT manu_code, manu_name, concat(left(manu_details,10),' ...') as 'manu_details' FROM garage.manufacturer; +-----------+----------------+----------------+ | manu_code | manu_name | manu_details | +-----------+----------------+----------------+ | FOR | Ford | The Ford M ... | | GM | General Motors | General Mo ... | | NIS | Nissan | Nissan Mot ... | | TOY | Toyota | Toyota Mot ... | | VOL | Volkswagen | Volkswagen ... | +-----------+----------------+----------------+ 5 rows in set (0.00 sec)
mysql> SELECT format(avg(length(manu_name)),0) -> as Length -> FROM garage.manufacturer; +--------+ | Length | +--------+ | 8 | +--------+ 1 row in set (0.00 sec)
mysql> SELECT *, if(fuel='petrol', '1.45', '1.30') as cost -> FROM garage.vehicle; +--------------+-----------+---------+----------+--------+--------+------+ | reg | manu_code | mileage | price | colour | fuel | cost | +--------------+-----------+---------+----------+--------+--------+------+ | 2003-LM-201 | TOY | 170000 | 3500.50 | Red | petrol | 1.45 | | 2009-RN-12 | FOR | 98242 | 2500.00 | Red | petrol | 1.45 | | 2010-G-13345 | TOY | 50000 | 8599.00 | Silver | petrol | 1.45 | | 2011-G-995 | FOR | 33500 | 8500.00 | Blue | petrol | 1.45 | | 2011-WH-2121 | FOR | 55998 | 14000.00 | Black | diesel | 1.30 | | 2014-WH-2189 | FOR | 12553 | 11000.00 | Blue | diesel | 1.30 | | 2016-D-12345 | TOY | 3456 | 15000.00 | Red | petrol | 1.45 | +--------------+-----------+---------+----------+--------+--------+------+ 7 rows in set (0.00 sec)
mysql> SELECT gv.reg, gv.manu_code, gm.manu_name FROM garage.vehicle gv -> left join garage.manufacturer gm -> on gv.manu_code=gm.manu_code -> ; +--------------+-----------+-----------+ | reg | manu_code | manu_name | +--------------+-----------+-----------+ | 2009-RN-12 | FOR | Ford | | 2011-G-995 | FOR | Ford | | 2011-WH-2121 | FOR | Ford | | 2014-WH-2189 | FOR | Ford | | 2003-LM-201 | TOY | Toyota | | 2010-G-13345 | TOY | Toyota | | 2016-D-12345 | TOY | Toyota | +--------------+-----------+-----------+ 7 rows in set (0.00 sec)
mysql> SELECT gm.manu_code, gm.manu_name, gv.reg -> FROM garage.manufacturer as gm -> inner join garage.vehicle as gv -> on gm.manu_code=gv.manu_code; +-----------+-----------+--------------+ | manu_code | manu_name | reg | +-----------+-----------+--------------+ | FOR | Ford | 2009-RN-12 | | FOR | Ford | 2011-G-995 | | FOR | Ford | 2011-WH-2121 | | FOR | Ford | 2014-WH-2189 | | TOY | Toyota | 2003-LM-201 | | TOY | Toyota | 2010-G-13345 | | TOY | Toyota | 2016-D-12345 | +-----------+-----------+--------------+ 7 rows in set (0.00 sec)
mysql> SELECT gm.manu_code, gm.manu_name, gv.reg -> FROM garage.manufacturer as gm -> left join garage.vehicle as gv -> on gm.manu_code=gv.manu_code; +-----------+----------------+--------------+ | manu_code | manu_name | reg | +-----------+----------------+--------------+ | FOR | Ford | 2009-RN-12 | | FOR | Ford | 2011-G-995 | | FOR | Ford | 2011-WH-2121 | | FOR | Ford | 2014-WH-2189 | | GM | General Motors | NULL | | NIS | Nissan | NULL | | TOY | Toyota | 2003-LM-201 | | TOY | Toyota | 2010-G-13345 | | TOY | Toyota | 2016-D-12345 | | VOL | Volkswagen | NULL | +-----------+----------------+--------------+ 10 rows in set (0.00 sec)
price_less_than(p decimal(8,2))
The procedure should then return the following details for all vehicles where the price of the vehicle is less than p sorted by ascending price:
CREATE PROCEDURE `price_less_than`(p decimal(8,2)) DETERMINISTIC BEGIN SELECT gv.reg, gv.manu_code, gm.manu_name, gv.mileage, gv.price FROM garage.vehicle gv left join garage.manufacturer gm on gv.manu_code=gm.manu_code where gv.price < p order by gv.price; END
mysql> call price_less_than(15000); +--------------+-----------+-----------+---------+----------+ | reg | manu_code | manu_name | mileage | price | +--------------+-----------+-----------+---------+----------+ | 2009-RN-12 | FOR | Ford | 98242 | 2500.00 | | 2003-LM-201 | TOY | Toyota | 170000 | 3500.50 | | 2011-G-995 | FOR | Ford | 33500 | 8500.00 | | 2010-G-13345 | TOY | Toyota | 50000 | 8599.00 | | 2014-WH-2189 | FOR | Ford | 12553 | 11000.00 | | 2011-WH-2121 | FOR | Ford | 55998 | 14000.00 | +--------------+-----------+-----------+---------+----------+ 6 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
MySQL -u root -p <bus.sql
mysql> show create table driver; +--------+-----------------------------------------------+ | Table | Create Table | +--------+-----------------------------------------------+ | driver | CREATE TABLE `driver` ( `licenceNo` varchar(20) NOT NULL, `name` varchar(30) DEFAULT NULL, `busReg` varchar(15) DEFAULT NULL, PRIMARY KEY (`licenceNo`), KEY `busReg` (`busReg`), CONSTRAINT `driver_ibfk_1` FOREIGN KEY (`busReg`) REFERENCES `bus` (`reg`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------+-----------------------------------------------+ 1 row in set (0.00 sec)
The driver has a foreign key constraint busReg that requires a valid entry that already exists in the table bus.reg to be present. When the bus entry is deleted from thus bus table, the “ON DELET CASCADE” will also delete the driver associated with this bus entry.
mysql> insert into driver (name) values("Mary"); ERROR 1364 (HY000): Field 'licenceNo' doesn't have a default value
This insert fails because the licenseNo field is a primary key value and cannot be NULL and fails when no value is assigned during the insert.
mysql> insert into driver (licenceNo,name) values("RN2423","Bob"); Query OK, 1 row affected (0.01 sec) mysql> select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | F2233 | Alan | 191-G-123 | | L23423 | John | 12-G-1323 | | RN2423 | Bob | NULL | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 4 rows in set (0.00 sec)
The second query works since Bo is added with a licensee number and the minimum criteria is being met.
mysql> insert into driver (licenceNo,name,busReg) values("FF88345","Sean","191-G-123"); Query OK, 1 row affected (0.01 sec) mysql> select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | F2233 | Alan | 191-G-123 | | FF88345 | Sean | 191-G-123 | | L23423 | John | 12-G-1323 | | RN2423 | Bob | NULL | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 5 rows in set (0.00 sec)
The last insert also works since all the fields are populated with valid values.
mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 4 rows in set (0.00 sec) mysql> insert into bus values("12-G-1323",34,"Diesel"); ERROR 1062 (23000): Duplicate entry '12-G-1323' for key 'PRIMARY'
The bus already exists in the table and the primary key constraint prevents duplication in this reg column.
mysql> insert into bus values("171-G885",84,"Petrol"); Query OK, 1 row affected (0.01 sec) mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 171-G885 | 84 | Petrol | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 5 rows in set (0.00 sec)
All the criteria entered mets the requirements and is successfully added to the table.
mysql> insert into bus values("191-D-45890",120,"Ethanol"); ERROR 1265 (01000): Data truncated for column 'fuel' at row 1 mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 171-G885 | 84 | Petrol | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 5 rows in set (0.00 sec)
Ethanol is not in the enumeration list of fuels and is therefore rejected.
Update driver set licenceNo = concat("T-", licenceNo) where licenceNo like "F%" or licenceNo like "R%"; select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | L23423 | John | 12-G-1323 | | T-F2233 | Alan | 191-G-123 | | T-FF88345 | Sean | 191-G-123 | | T-RN2423 | Bob | NULL | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 5 rows in set (0.00 sec)
What happens and why?
mysql> select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | L23423 | John | 12-G-1323 | | T-F2233 | Alan | 191-G-123 | | T-FF88345 | Sean | 191-G-123 | | T-RN2423 | Bob | NULL | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 5 rows in set (0.00 sec) mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 171-G885 | 84 | Petrol | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 5 rows in set (0.00 sec) mysql> delete from driver -> where name = "Alan"; Query OK, 1 row affected (0.01 sec) mysql> select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | L23423 | John | 12-G-1323 | | T-FF88345 | Sean | 191-G-123 | | T-RN2423 | Bob | NULL | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 4 rows in set (0.00 sec) mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 171-G885 | 84 | Petrol | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 5 rows in set (0.00 sec)
Alan was deleted and nothing else happens. Assuming the question is asked to highlight the fworking of the foreign key constraint busReg and the “ON DELET CASCADE” clause.
So in this case nothing happens but if the bus assigned to Alan was deleted Alan would have disappeared too. Is this right?
What happens and why?
mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 171-G885 | 84 | Petrol | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 5 rows in set (0.00 sec) mysql> select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | L23423 | John | 12-G-1323 | | T-FF88345 | Sean | 191-G-123 | | T-RN2423 | Bob | NULL | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 4 rows in set (0.00 sec) mysql> delete from bus -> where reg = "161-D-1323"; Query OK, 1 row affected (0.01 sec) mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 162-D-3433 | 120 | Electric | | 171-G885 | 84 | Petrol | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 4 rows in set (0.00 sec) mysql> select * from driver; +-----------+------+-----------+ | licenceNo | name | busReg | +-----------+------+-----------+ | L23423 | John | 12-G-1323 | | T-FF88345 | Sean | 191-G-123 | | T-RN2423 | Bob | NULL | +-----------+------+-----------+ 3 rows in set (0.00 sec)
So bus “161-D-1323” and driver “Tom” was deleted because of the “ON DELETE CASCADE” foreign key reference applied on the driver table.
To use this database type use bus2;
MySQL -u root -p <bus2.mysql
What happens and why?
mysql> use bus2; Database changed mysql> select * from driver; +-----------+------+------------+ | licenceNo | name | busReg | +-----------+------+------------+ | F2233 | Alan | 191-G-123 | | L23423 | John | 12-G-1323 | | X98983 | Tom | 161-D-1323 | +-----------+------+------------+ 3 rows in set (0.00 sec) mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 161-D-1323 | 80 | Diesel | | 162-D-3433 | 120 | Electric | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 4 rows in set (0.00 sec) mysql> delete from bus -> where reg = "161-D-1323"; Query OK, 1 row affected (0.01 sec) mysql> select * from driver; +-----------+------+-----------+ | licenceNo | name | busReg | +-----------+------+-----------+ | F2233 | Alan | 191-G-123 | | L23423 | John | 12-G-1323 | | X98983 | Tom | NULL | +-----------+------+-----------+ 3 rows in set (0.00 sec) mysql> select * from bus; +------------+---------------+----------+ | reg | maxPassengers | fuel | +------------+---------------+----------+ | 12-G-1323 | 34 | Petrol | | 162-D-3433 | 120 | Electric | | 191-G-123 | 56 | Diesel | +------------+---------------+----------+ 3 rows in set (0.00 sec)
The behavior changed by setting driver Tom busReg foreign key to NULL. This happened because of the amended foreign key clause “ON DELETE SET NULL”
mysql> show create table driver; CREATE TABLE `driver` ( `licenceNo` varchar(20) NOT NULL, `name` varchar(30) DEFAULT NULL, `busReg` varchar(15) DEFAULT NULL, PRIMARY KEY (`licenceNo`), KEY `busReg` (`busReg`), CONSTRAINT `driver_ibfk_1` FOREIGN KEY (`busReg`) REFERENCES `bus` (`reg`) ON DELETE SET NULL
To use this database type use employees2;
MySQL -u root -p <employees2.sql use employees2; Database changed
The average year should be rounded down to the nearest whole number.
For example,
mysql> SELECT emp_no, first_name, last_name, year(birth_date) as bd -> FROM employees2.employees -> where year(birth_date) in ( -> SELECT truncate(avg(year(birth_date)),0) FROM employees2.employees -> ) -> ; +--------+------------+--------------+------+ | emp_no | first_name | last_name | bd | +--------+------------+--------------+------+ | 10007 | Tzvetan | Zielinski | 1957 | | 10045 | Moss | Shanbhogue | 1957 | | 10054 | Mayumi | Schueller | 1957 | | 10080 | Premal | Baek | 1957 | | 10094 | Arumugam | Ossenbruggen | 1957 | +--------+------------+--------------+------+ 5 rows in set (0.00 sec)
or with rounding instead ….
mysql> SELECT emp_no, first_name, last_name, year(birth_date) as bd -> FROM employees2.employees -> where year(birth_date) in ( -> SELECT round(avg(year(birth_date)),0) FROM employees2.employees -> ) -> ; +--------+------------+-----------+------+ | emp_no | first_name | last_name | bd | +--------+------------+-----------+------+ | 10008 | Saniya | Kalloufi | 1958 | | 10017 | Cristinel | Bouloucos | 1958 | | 10024 | Suzette | Pettey | 1958 | | 10025 | Prasadram | Heyers | 1958 | | 10030 | Elvis | Demeyer | 1958 | | 10050 | Yinghua | Dredge | 1958 | | 10071 | Hisao | Lipner | 1958 | +--------+------------+-----------+------+ 7 rows in set (0.00 sec)
mysql> show tables; +----------------------+ | Tables_in_employees2 | +----------------------+ | dept | | employees | | salaries | +----------------------+ 3 rows in set (0.00 sec) mysql> show create table dept; CREATE TABLE `dept` ( `dept_no` varchar(10) NOT NULL, `name` varchar(50) NOT NULL, PRIMARY KEY (`dept_no`) ) mysql> show create table employees; CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`) ) 1 row in set (0.00 sec) mysql> show create table salaries; CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, `dept_no` varchar(10) DEFAULT NULL, PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`), KEY `dept_no` (`dept_no`), CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE, CONSTRAINT `salaries_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`dept_no`) ON DELETE CASCADE )1 row in set (0.00 sec)
So the master table is salaries and contains foreign key to the employees and departments tables.
SELECT DISTINCT(ee.emp_no), ee.first_name, ee.last_name, ed.name AS department FROM employees2.employees AS ee LEFT JOIN employees2.salaries AS es ON ee.emp_no=es.emp_no LEFT JOIN employees2.dept AS ed ON es.dept_no=ed.dept_no
emp_no | first_name | last_name | department |
---|---|---|---|
10001 | Georgi | Facello | Human Resources |
10002 | Bezalel | Simmel | Human Resources |
10003 | Parto | Bamford | Human Resources |
… | |||
10028 | Domenick | Tempesti | Human Resources |
10029 | Otmar | Herbst | Human Resources |
10030 | Elvis | Demeyer | Human Resources |
10031 | Karsten | Joslin | Research & Development |
10032 | Jeong | Reistad | Research & Development |
10033 | Arif | Merlo | Research & Development |
… | |||
10068 | Charlene | Brattka | Research & Development |
10069 | Margareta | Bierman | Research & Development |
10070 | Reuven | Garigliano | Research & Development |
10071 | Hisao | Lipner | Sales |
10072 | Hironoby | Sidou | Sales |
10073 | Shir | McClurg | Sales |
… | |||
10098 | Sreekrishna | Servieres | Sales |
10099 | Valter | Sullins | Sales |
10100 | Hironobu | Haraldson | Sales |
100 rows in set (0.00 sec)