CarsProcedures

Setting environment for using XAMPP for Windows.
nelly@NELLYIVANOVA c:\xampp
# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 10.4.21-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| cars               |
| clients2610        |
| company            |
| continents         |
| datachanges        |
| information_schema |
| mysql              |
| performance_schema |
| personal           |
| phpmyadmin         |
| reservations       |
| shoot              |
| test               |
| uni                |
+--------------------+
14 rows in set (0.021 sec)

MariaDB [(none)]> use cars;
Database changed
MariaDB [cars]> INSERT INTO used_cars_data (model, year, price, transmission, mileage, fueltype, tax, mpg, enginesize) VALUES("mercedes benz", "2015", "80000", "automatic" ,"18" ,"gasoline",10, 20, 2.3);
Query OK, 1 row affected (0.025 sec)

MariaDB [cars]> INSERT INTO used_cars_data (model, year, price, transmission, mileage, fueltype, tax, mpg, enginesize) VALUES("BMW G32", "2018", "80000", "automatic" ,"18" ,"gasoline",10, 20, 2.2);
Query OK, 1 row affected (0.009 sec)

MariaDB [cars]> select* from used_cars_data;
+-------------------+------+--------+--------------+---------+----------+------+------+------------+
| model             | year | price  | transmission | mileage | fueltype | tax  | mpg  | enginesize |
+-------------------+------+--------+--------------+---------+----------+------+------+------------+
| volkswagen tiguan | 2010 |  12500 | manual       |  180000 | diesel   |    5 |    6 |          2 |
| toyota taris      | 2019 | 125000 | automatic    |    1800 | hybrid   |    5 |   10 |          2 |
| toyota hilux      | 2020 | 160000 | automatic    |     100 | hybrid   |    5 |   18 |        2.2 |
| mercedes benz     | 2021 | 200000 | manual       |      20 | electric |   10 |   20 |        2.3 |
| mercedes benz     | 2018 | 100000 | automatic    |      18 | diesel   |   10 |   20 |        2.3 |
| mercedes benz     | 2015 |  80000 | automatic    |      18 | gasoline |   10 |   20 |        2.3 |
| BMW G32           | 2018 |  80000 | automatic    |      18 | gasoline |   10 |   20 |        2.2 |
+-------------------+------+--------+--------------+---------+----------+------+------+------------+
7 rows in set (0.010 sec)

MariaDB [cars]> DELIMITER |
MariaDB [cars]>
MariaDB [cars]> CREATE PROCEDURE Showcars()
    -> BEGIN
    -> SELECT model, year, price  FROM used_cars_data;
    -> END |
Query OK, 0 rows affected (0.027 sec)

MariaDB [cars]>
MariaDB [cars]> DELIMITER ;
MariaDB [cars]> select* from used_cars_data where model="toyota%";
Empty set (0.011 sec)

MariaDB [cars]> select* from used_cars_data where model='toyota %';
Empty set (0.001 sec)

MariaDB [cars]> SELECT * FROM table WHERE myword REGEXP '^.*toyota.*$
    '> ;
    '> ;
    '> Bye

nelly@NELLYIVANOVA c:\xampp
# mysql -uroot;
ERROR 1045 (28000): Access denied for user 'root;'@'localhost' (using password: NO)

nelly@NELLYIVANOVA c:\xampp
# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.4.21-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use cars;
Database changed
MariaDB [cars]> SELECT * FROM table WHERE model REGEXP '[[:<:]]toyota[[:>:]]'
    ->
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table WHERE model REGEXP '[[:<:]]toyota[[:>:]]'' at line 1
MariaDB [cars]> SELECT * FROM used_cars_data WHERE model REGEXP '[[:<:]]toyota[[:>:]]';
+--------------+------+--------+--------------+---------+----------+------+------+------------+
| model        | year | price  | transmission | mileage | fueltype | tax  | mpg  | enginesize |
+--------------+------+--------+--------------+---------+----------+------+------+------------+
| toyota taris | 2019 | 125000 | automatic    |    1800 | hybrid   |    5 |   10 |          2 |
| toyota hilux | 2020 | 160000 | automatic    |     100 | hybrid   |    5 |   18 |        2.2 |
+--------------+------+--------+--------------+---------+----------+------+------+------------+
2 rows in set (0.006 sec)

MariaDB [cars]> DELIMITER |
MariaDB [cars]>
MariaDB [cars]> CREATE PROCEDURE showCarsModel()
    -> BEGIN
    -> SELECT * FROM used_cars_data WHERE model REGEXP '[[:<:]]toyota[[:>:]]'
    -> END |
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END' at line 4
MariaDB [cars]>
MariaDB [cars]> DELIMITER ;
MariaDB [cars]>
MariaDB [cars]> SELECT * FROM used_cars_data ORDER BY fueltype ASC;
+-------------------+------+--------+--------------+---------+----------+------+------+------------+
| model             | year | price  | transmission | mileage | fueltype | tax  | mpg  | enginesize |
+-------------------+------+--------+--------------+---------+----------+------+------+------------+
| volkswagen tiguan | 2010 |  12500 | manual       |  180000 | diesel   |    5 |    6 |          2 |
| mercedes benz     | 2018 | 100000 | automatic    |      18 | diesel   |   10 |   20 |        2.3 |
| mercedes benz     | 2021 | 200000 | manual       |      20 | electric |   10 |   20 |        2.3 |
| mercedes benz     | 2015 |  80000 | automatic    |      18 | gasoline |   10 |   20 |        2.3 |
| BMW G32           | 2018 |  80000 | automatic    |      18 | gasoline |   10 |   20 |        2.2 |
| toyota taris      | 2019 | 125000 | automatic    |    1800 | hybrid   |    5 |   10 |          2 |
| toyota hilux      | 2020 | 160000 | automatic    |     100 | hybrid   |    5 |   18 |        2.2 |
+-------------------+------+--------+--------------+---------+----------+------+------+------------+
7 rows in set (0.009 sec)

MariaDB [cars]> DELIMITER |
MariaDB [cars]>
MariaDB [cars]> CREATE PROCEDURE showCarsModel()
    -> BEGIN
    -> SELECT * FROM used_cars_data ORDER BY fueltype ASC;
    -> END |
Query OK, 0 rows affected (0.006 sec)

MariaDB [cars]>
MariaDB [cars]> DELIMITER ;
MariaDB [cars]>  CREATE PROCEDURE priceUpdate (money DOUBLE)
    ->        BEGIN
    ->               START priceUpdate;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'priceUpdate' at line 3
MariaDB [cars]>
MariaDB [cars]>               UPDATE price
    ->               SET price = amount - amount*money
    ->
    ->            COMMIT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMMIT' at line 4
MariaDB [cars]>
MariaDB [cars]>        END       |
    ->
    -> mysql> DELIMITER ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END       |

mysql> DELIMITER' at line 1
MariaDB [cars]>
MariaDB [cars]> DELIMITER |
MariaDB [cars]>
MariaDB [cars]>  CREATE PROCEDURE priceUpdate (money DOUBLE)
    ->        BEGIN
    ->
    ->               SET price = price - price*money
    ->
    ->        END       |
ERROR 1193 (HY000): Unknown system variable 'price'
MariaDB [cars]>
MariaDB [cars]> mysql> DELIMITER ;
    -> DELIMITER |
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysql> DELIMITER ;
DELIMITER' at line 1
MariaDB [cars]>
MariaDB [cars]>  CREATE PROCEDURE priceUpdate (money DOUBLE)
    ->        BEGIN
    ->               Update used_cars_data;
    ->               SET price = price - price*money;
    ->
    ->        END       |
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ';
              SET price = price - price*money;

       END' at line 3
MariaDB [cars]>
MariaDB [cars]> mysql> DELIMITER ;
    ->  DELIMITER // ;
    ->  Create Procedure Update_carPrice ( IN p_price Double )
    ->    -> BEGIN
    ->    -> UPDATE used_cars_data
    ->    -> SET
    ->    -> price = price+price*p_price WHERE;
    ->    -> END //
    ->  DELIMITER ;
    ->
    ->
    -> Bye

nelly@NELLYIVANOVA c:\xampp
# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.4.21-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>  DELIMITER //
MariaDB [(none)]>  Create Procedure Update_carPrice ( IN p_price Double )
    ->    -> BEGIN
    ->    -> UPDATE used_cars_data
    ->    -> SET
    ->    -> price = price+price*p_price WHERE;
    ->    -> END //
ERROR 1046 (3D000): No database selected
MariaDB [(none)]>  DELIMITER ;
MariaDB [(none)]> use cars;
Database changed
MariaDB [cars]>  DELIMITER //
MariaDB [cars]>  Create Procedure Update_carPrice ( IN p_price Double )
    ->    -> BEGIN
    ->    -> UPDATE used_cars_data
    ->    -> SET
    ->    -> price = price+price*p_price WHERE;
    ->    -> END //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-> BEGIN
   -> UPDATE used_cars_data
   -> SET
   -> price = price+price*p_pr...' at line 2
MariaDB [cars]>  DELIMITER ;
MariaDB [cars]>  DELIMITER //
MariaDB [cars]>  Create Procedure Update_carPrice ( IN p_price Double )
    ->    -> BEGIN
    ->    -> UPDATE used_cars_data
    ->    -> SET
    ->    -> price = price+price*p_price ;
    ->    -> END //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-> BEGIN
   -> UPDATE used_cars_data
   -> SET
   -> price = price+price*p_pr...' at line 2
MariaDB [cars]>  DELIMITER ;
MariaDB [cars]>  DELIMITER //
MariaDB [cars]>  Create Procedure Update_carPrice ( IN p_price Double )
    ->    -> BEGIN
    ->    -> UPDATE used_cars_data
    ->    -> SET price = price+price*p_price ;
    ->    -> END //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-> BEGIN
   -> UPDATE used_cars_data
   -> SET price = price+price*p_price ;
...' at line 2
MariaDB [cars]>  DELIMITER ;
MariaDB [cars]>  DELIMITER //
MariaDB [cars]>  Create Procedure Update_carPrice ( IN p_price Double )
    ->    -> BEGIN
    ->    -> UPDATE used_cars_data
    ->    -> SET price = SUM(price)+SUM(price*p_price) ;
    ->    -> END //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-> BEGIN
   -> UPDATE used_cars_data
   -> SET price = SUM(price)+SUM(price*p...' at line 2
MariaDB [cars]>  DELIMITER ;
MariaDB [cars]> DELIMITER //
MariaDB [cars]>  Create Procedure Update_carPrice ( IN p_price Double )
    ->    -> BEGIN
    ->    -> UPDATE used_cars_data;
    ->    -> SET price = SUM(price)+SUM(price*p_price) ;
    ->    -> END //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-> BEGIN
   -> UPDATE used_cars_data;
   -> SET price = SUM(price)+SUM(price*...' at line 2
MariaDB [cars]>  DELIMITER ;
MariaDB [cars]> Call Showcars;
+-------------------+------+--------+
| model             | year | price  |
+-------------------+------+--------+
| volkswagen tiguan | 2010 |  12500 |
| toyota taris      | 2019 | 125000 |
| toyota hilux      | 2020 | 160000 |
| mercedes benz     | 2021 | 200000 |
| mercedes benz     | 2018 | 100000 |
| mercedes benz     | 2015 |  80000 |
| BMW G32           | 2018 |  80000 |
+-------------------+------+--------+
7 rows in set (0.012 sec)

Query OK, 0 rows affected (0.031 sec)

MariaDB [cars]> call showCarsModel;
+-------------------+------+--------+--------------+---------+----------+------+------+------------+
| model             | year | price  | transmission | mileage | fueltype | tax  | mpg  | enginesize |
+-------------------+------+--------+--------------+---------+----------+------+------+------------+
| volkswagen tiguan | 2010 |  12500 | manual       |  180000 | diesel   |    5 |    6 |          2 |
| mercedes benz     | 2018 | 100000 | automatic    |      18 | diesel   |   10 |   20 |        2.3 |
| mercedes benz     | 2021 | 200000 | manual       |      20 | electric |   10 |   20 |        2.3 |
| mercedes benz     | 2015 |  80000 | automatic    |      18 | gasoline |   10 |   20 |        2.3 |
| BMW G32           | 2018 |  80000 | automatic    |      18 | gasoline |   10 |   20 |        2.2 |
| toyota taris      | 2019 | 125000 | automatic    |    1800 | hybrid   |    5 |   10 |          2 |
| toyota hilux      | 2020 | 160000 | automatic    |     100 | hybrid   |    5 |   18 |        2.2 |
+-------------------+------+--------+--------------+---------+----------+------+------+------------+
7 rows in set (0.012 sec)

Query OK, 0 rows affected (0.103 sec)

MariaDB [cars]> DELIMITER |
MariaDB [cars]>
MariaDB [cars]> CREATE PROCEDURE showToyotal()
    -> BEGIN
    -> SELECT * FROM used_cars_data  WHERE model REGEXP '[[:<:]]toyota[[:>:]]'
    ->
    ->
    -> END |
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END' at line 6
MariaDB [cars]>
MariaDB [cars]> DELIMITER ;
MariaDB [cars]>
MariaDB [cars]> DELIMITER |
MariaDB [cars]>
MariaDB [cars]> CREATE PROCEDURE showToyotal()
    -> BEGIN
    -> SELECT * FROM used_cars_data  WHERE model REGEXP '[[:<:]]toyota[[:>:]]';
    ->
    ->
    -> END |
Query OK, 0 rows affected (0.018 sec)

MariaDB [cars]>
MariaDB [cars]> DELIMITER ;
MariaDB [cars]> call Toyota