DATA ANALYTICS REFERENCE DOCUMENT |
|
---|---|
Document Title: | Applied databases - Worksheet Week 4 |
Document No.: | 1550081446 |
Author(s): | Gerhard van der Linde |
Contributor(s): |
REVISION HISTORY
Revision | Details of Modification(s) | Reason for modification | Date | By |
---|---|---|---|---|
0 | Draft release | Applied databases - Worksheet Week 4 | 2019/02/13 18:10 | Gerhard van der Linde |
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)