Task 1: The Finance team wants to calculate a VAT (8%) on the amount of the stay reservations in the database. However, the team wants to calculate the VAT after the discounts are applied on the prices. In addition, it is important to remember if there is no discount/offers, then the value is NULL in the TRAVELS table. Create a procedure called VATCalculator to calculate the VAT amount and return with the corresponding travel ID. In addition, execute the procedure to account for NULL values and send the respective result to the team. The VAT value should be rounded to 2 decimals. Remember to redefine the MYSQL delimiter (;) when creating the procedure. This will prevent the commands from executing separately. Create a procedure to calculate the VAT. Make sure you are using the following format to change the delimiter for the procedure: DELIMITER $$ -- statements -- END $$ DELIMITER ; Test Query: CALL VATCalculator() Expected Results TRAVEL_ID VAT 5001 1.24 5002 1.64 5003 0.99 5004 2.12 5005 0.89 5006 1.05 5007 0.30 5008 1.51 5009 1.08 5010 1.64
Control structures
Control structures are block of statements that analyze the value of variables and determine the flow of execution based on those values. When a program is running, the CPU executes the code line by line. After sometime, the program reaches the point where it has to make a decision on whether it has to go to another part of the code or repeat execution of certain part of the code. These results affect the flow of the program's code and these are called control structures.
Switch Statement
The switch statement is a key feature that is used by the programmers a lot in the world of programming and coding, as well as in information technology in general. The switch statement is a selection control mechanism that allows the variable value to change the order of the individual statements in the software execution via search.
Task 1:
The Finance team wants to calculate a VAT (8%) on the amount of the stay reservations in the
Remember to redefine the MYSQL delimiter (;) when creating the procedure. This will prevent the commands from executing separately.
Create a procedure to calculate the VAT.
Make sure you are using the following format to change the delimiter for the procedure:
DELIMITER $$
-- statements --
END $$
DELIMITER ;
Test Query:
CALL VATCalculator()
Expected Results
| TRAVEL_ID | VAT |
|---|---|
| 5001 | 1.24 |
| 5002 | 1.64 |
| 5003 | 0.99 |
| 5004 | 2.12 |
| 5005 | 0.89 |
| 5006 | 1.05 |
| 5007 | 0.30 |
| 5008 | 1.51 |
| 5009 | 1.08 |
| 5010 | 1.64 |
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
