AdvancedMySQLTopicsFinalProject

My final project applying advanced MySQL topics not limited to stored functuions, stored complex procedures, triggers, temporary tables, CTEs and json data type.

Task 1:

Lucky Shrub need to find out what their average sale price, or cost was for a product in 2022. Create a FindAverageCost() function that returns the average sale price value of all products in a specific year based on the user input.

The average cost returned from the FindAverageCost() function based on the user input of the year 2022 will be

Task 2:

Lucky Shrub need to evaluate the sales patterns for bags of artificial grass over the last three years.

Steps:

• Create the EvaluateProduct stored procedure that outputs the total number of items sold during the last three years for the P1 Product ID. Input the ProductID when invoking the procedure.
• Call the procedure.
• Output the values into outside variables.

Task 3:

Lucky Shrub need to automate the orders process in their database. The database must insert a new record of data in response to the insertion of a new order in the Orders table. This new record of data must contain a new ID and the current date and time.

Create a trigger called UpdateAudit that must be invoked automatically AFTER a new order is inserted into the Orders table.

For example, when you insert three new orders in the Orders table, then three records of data are automatically inserted into the Audit table.

Task 4:

Lucky Shrub needs location data for their clients and employees. Create an optimized query that outputs the following data:

• The full name of all clients and employees from the Clients and Employees tables in the Lucky Shrub database.
• The address of each person from the Addresses table.

The data should be ordered by the street name.

Task 5:

Lucky Shrub need to find out what quantities of wood panels they are selling. The wood panels product has a Product ID of P2. The following query returns the total quantity of this product as sold in the years 2020, 2021 and 2022:

SELECT CONCAT (SUM(Cost), " (2020)") AS "Total sum of P2 Product" FROM Orders WHERE YEAR (Date) = 2020 AND ProductID = "P2"

UNION

SELECT CONCAT (SUM(Cost), "(2021)") FROM Orders WHERE YEAR (Date) = 2021 AND ProductID = "P2"

UNION

SELECT CONCAT (SUM (Cost), "(2022)") FROM Orders WHERE YEAR (Date) = 2022 AND ProductID = "P2";

Optimize this query by recreating it as a common table expression (CTE).

Task 6:

Lucky Shrub want to know more about the activities of the clients who use their online store. The system logs the ClientID and the ProductID information for each activity in a JSON Properties column inside the Activity table. This occurs while clients browse through Lucky Shrub products online.

Utilize the Properties data to output the following information:

• The full name and contact number of each client from the Clients table.
• The ProductID for all clients who performed activities.

Task 7:

Lucky Shrub need to find out how much revenue their top selling product generated. Create a stored procedure called GetProfit that returns the overall profits generated by a specific product in a specific year. This should be based on the user input of the ProductID and Year.

The output result of GetProfit procedure with the P1 ProductID and Year 2020 will should be ………

Task 8:

Lucky Shrub need a summary of their client's details, including their addresses, order details and the products they purchased. Create a virtual table called DataSummary that joins together the four tables that contain this data. These four tables are as follows:

• Clients
• Addresses
• Orders
• Products

The virtual table must display the following data:

• The full name and contact number for each client from the Clients table.
• The county that each client lives in from the Addresses table.