AdvancedMySQLTopics

Welcome to my repo this is a summary of my journey through the following advanced MySQL topics after working with database structures and management with MySQL.

1. Creating and working with functions and complex stored procedures in MySQL, make use of variables and parameters to create more complex stored functions and procedures in MySQL.
2. Using MySQL triggers to automate database tasks.
3. Creating scheduled events to ensure that your database tasks and events are completed at specific times.
4. Guidelines for database optimization by:
   • targeting required columns
   • working with indexes in MySQL to speed up the performance of data retrieval queries.
   • using MySQL transaction statements to manage database transactions.
   • using common table expressions to manage complex sequel queries
   • use of prepared statements
   • Interacting with the MySQL database using the JSON data type.
5. Different types of data analysis that can be performed within a database using MySQL queries like joins, sub queries, and views.
6. Emulating a full outer join in MySQL to extract all records from two tables including those that don't match.
7. Extracting data from multiple tables using joins

Mission 1: Developing functions & stored procedures in MySQL

Lucky Shrub needs to query their database. They can do this by using functions and stored procedures in MySQL.

Task 1:

Create a SQL function that prints the cost value of a specific order based on the user input of the OrderID.

Call the function with an OrderID of 5.

Task 2:

Create a stored procedure called GetDiscount. This stored procedure must return the final cost of the customer's order after the discount value has been deducted. The discount value is based on the order's quantity. The stored procedure must have the following specifications:

• The procedure should take one parameter that accepts a user input value of an OrderID.
• The procedure must find the order quantity of the specific OrderID.
• If the value of the order quantity is more than or equal to 20 then the procedure should return the new cost after a 20% discount.
• If the value of the order quantity is less than 20 and more than or equal to 10 then the procedure should return the new cost after a 10% discount.

Call the procedure with OrderID 5.

Mission 2: Working with triggers

Lucky Shrub needs to impose business rules for inserting, updating and deleting product data in their database. Implement triggers on the Products table.

The Products table contains the following information about each product:

• ProductID
• ProductName
• BuyPrice
• SellPrice
• NumberOfItems

The main objective of this activity is to develop INSERT, UPDATE and DELETE triggers.

Task 1:

Create an INSERT trigger called ProductSellPriceInsertCheck. This trigger must check if the SellPrice of the product is less than the BuyPrice after a new product is inserted in the Products table. If this occurs, then a notification must be added to the Notifications table to inform the sales department. The sales department can then ensure that the incorrect values were not inserted by mistake.

The notification message should be in the following format: A SellPrice less than the BuyPrice was inserted for ProductID + ProductID

Insert a product ProductID P7 with the SellPrice being less than BuyPrice.

The alert sent to the Notifications table

Task 2:

Create an UPDATE trigger called ProductSellPriceUpdateCheck. This trigger must check that products are not updated with a SellPrice that is less than or equal to the BuyPrice. If this occurs, add a notification to the Notifications table for the sales department so they can ensure that product prices were not updated with the incorrect values.

The notification message should be in the following format: ProductID + was updated with a SellPrice of + SellPrice + which is the same or less than the BuyPrice

Updating ProductID P6 with a SellPrice that is less than BuyPrice

The alert sent to the Notifications table

Task 3:

Create a DELETE trigger called NotifyProductDelete. This trigger must insert a notification in the Notifications table for the sales department after a product has been deleted from the Products table.

The notification message should be in the following format: The product with a ProductID + ProductID + was deleted

Deleting ProductID P7 from the Products table

The alert sent to the Notifications table

Mission 3: Working with MySQL Scheduled Events

Task 1

Lucky Shrub’s Finance Department has just requested a report on all orders received this month. They need the report generated at 11:59 pm on the last day of the month. However, it's now the last day of the month and it's also approaching 12 noon. So, they need the report 12 hours from now.

Create a one-off event named GenerateRevenueReport to achieve this, instruct MySQL to select all data inserted into the orders table this month and to place that data within a report data table

Task 2

Lucky shrub is reviewing their stock and needs to make sure that they have at least 50 units available for each item on sale. Use a recurring event named DailyRestock to achieve this. The event should occur once a day.

MySQL must check if the number of items for any record in the products table is below 50. If MySQL locates a record below 50, then the number of items must be updated.

Mission 4: SELECT statement optimization in MySQL

Lucky Shrub need to execute a series of SELECT queries against their database to retrieve information on their employees and client orders. However, they need to optimize these queries first to make sure that they execute quickly and efficiently using MySQL database optimization techniques.

Task 1

Lucky Shrub needs data on client orders. They have written the following SELECT query to retrieve all data from the Orders table:

SELECT * FROM Orders;

However, the data they need is contained within the OrderID, ProductID, Quantity and Date columns. So, these are the important fields that their query must target. Targeting other columns in the table is an inefficient use of resources.

Rewrite the SELECT statement so that it is optimized.

Task 2

Lucky Shrub need to find the order placed by the client Cl1. They have written the following query to complete this task:

SELECT * FROM Orders WHERE ClientID ='Cl1';

However, this query’s execution plan shows that it does not use an index to perform this search, as indicated by the NULL values in possible_keys and keys columns.

Optimize this query by creating an index named IdxClientID on the required column of the Orders table.

Run the same SELECT statement with the EXPLAIN statement.

Task 3

Lucky Shrub have written the following SELECT query to find the details of the employee whose last name is 'Tolo'

SELECT * FROM Employees WHERE FullName LIKE '%Tolo';

However, there’s an index on the FullName column which the query cannot use because it contains a leading wildcard (%) in the WHERE clause condition.

Steps

• Add a new column to the Employees table called ReverseFullName.
• Populate the ReverseFullName column with the name of each employee as its values, but in reverse.
• Create an index named IdxReverseFullName on the ReverseFullName column.
• Rewrite the SELECT query so that it uses a trailing wildcard instead of the leading wild card.

The Employee table before changes

Adding new column called ReverseFullName

Populate the ReverseFullName column with the name of each employee as its values, but in reverse.

The Employees table would now look like this

Create an index named IdxReverseFullName on the ReverseFullName column

Use the SELECT query using a trailing wildcard instead of the leading wildcard

Mission 5: Working with CTEs, Prepared Statement & JSON

Task 1

Lucky Shrub need to find out how many orders were placed by clients with the following Client IDs in 2022; Cl1, Cl2 and Cl3. They have created the following query to extract this information.

SELECT CONCAT("Cl1: ", COUNT(OrderID), "orders") AS "Total number of orders" FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl1" UNION SELECT CONCAT("Cl2: ", COUNT(OrderID), "orders") FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl2" UNION SELECT CONCAT("Cl3: ", COUNT(OrderID), "orders") FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl3";

Optimize this query by recreating it as a CTE

Task 2

Create a prepared statement called GetOrderDetail that should accept two input arguments: a ClientID value and a year value. The statement should return the order id, the quantity, the order cost and the order date from the Orders table.

Use the prepared statement with the parameters of ClientID (Cl1) and Year (2020)

Task 3

The Lucky Shrub system logs the ClientID of each client, and the ProductID of the products they order, in a JSON Properties column in the Activity table as shown in the below table

Utilize the Properties column data to output the product id, name, buy price and sell price of the product where the Order value in the Activity table is True. The product name, buy price and sell price data must be extracted from the Products table.

Mission 6: Data analysis in MySQL

Objectives:

• Analyze data to gain insight into Lucky Shrub’s business performance.
• Query data from multiple tables.

Task 1: Emulating full outer join in MySQL

Lucky Shrub need to find out how many sycamore trees they’ve sold over the past few years. Sycamore trees have been assigned an ID of P4 in the products table in the database. Create a query that returns the total quantity of all products with the ID of P4 sold in the years 2020, 2021 and 2022.

Task 2: Using joins to extract data from multiple tables

Lucky Shrub needs information on all their clients and the orders that they placed in the years 2022 and 2021. Extracting the required information from each of the following tables:

• Clients table: The client id and contact number for each client who placed an order
• Addresses table: The street and county for each client’s address
• Orders table: The order id, cost and date of each client’s order.
• Products table: The name of each product ordered.

Task 3

Lucky Shrub needs to analyse the sales performance of their Patio slates product in the year 2021. This product has a Product ID of P3. Create a function called FindSoldQuantity that enables them to:

• Input a ProductID and a year from which they can capture data
• Display the total quantity of the product sold in the given year.

If we enter a product id P3 and the year 2021, then the output result will be…….