~~NOTOC~~ |<100% 25% - >| ^ \\ DATA ANALYTICS REFERENCE DOCUMENT\\ \\ ^^ ^ Document Title:|Applied databases - Worksheet Week 4| ^ Document No.:|1550081446| ^ Author(s):|Gerhard van der Linde| ^ Contributor(s):| | **REVISION HISTORY** |< 100% 10% - - 10% 17% 10% >| ^ \\ Revision\\ \\ ^\\ Details of Modification(s)^\\ Reason for modification^ \\ Date ^ \\ By ^ | [[:doku.php?id=worksheet:databases:topic4&do=revisions|0]] |Draft release|Applied databases - Worksheet Week 4| 2019/02/13 18:10 | Gerhard van der Linde | ---- ====== Applied Databases - Week 4 ====== ===== 1. Get garage.sql from Moodle and import it into MySQL. ===== mysql -u root -p ===== 2. How are the tables in the database related? ===== 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. ===== 3. Show the manu_code, manu_name and the first 10 characters of the manu_details followed by three dots (…) for each manufacturer. ===== 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) ===== 4. Show the average length of the manu_name (displayed as “Length”) with 0 characters after the decimal point. HINT: Functions needed are avg(), length() and format(). ===== mysql> SELECT format(avg(length(manu_name)),0) -> as Length -> FROM garage.manufacturer; +--------+ | Length | +--------+ | 8 | +--------+ 1 row in set (0.00 sec) ===== 5. Show all details of all vehicles plus an extra column called “cost” which has the value 1.45 if the fuel is petrol otherwise has the value 1.30. ===== 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) ===== 6. Show all the reg, manu_code and associated manu_name for each vehicle. ===== 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) ===== 7. Show the manu_code and manu_name as well as associated reg, for each manufacturer who has vehicles listed in the vehicle table. ===== 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) ===== 8. Show the manu_code and manu_name as well as associated reg, for all manufacturers and if they have vehicles listed in the vehicle table, show the reg of it. ===== 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) ===== 9. Write a stored procedure called price_less_than that takes one parameter of type decimal(8,2) which represents the price of a vehicle: ===== 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: * Reg * Manu_code * Manu_name * Mileage * Price ==== Procedure ==== 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 ==== Testing Procedure ==== 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)