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

Applied Databases - Week 4

1. Get garage.sql from Moodle and import it into MySQL.

mysql -u root -p <garage.sql

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)