Query Optimization Techniques - Advanced Guided Assignment - MySQL

[akash-coded]

Absolutely, here are more complex exercises which involve a variety of SQL constructs.

Exercise 1: CTE and Views

Given the following query that calculates the total sales per product for each year, create a CTE and a corresponding view for this query.

SELECT 
    products.productCode,
    products.productName,
    YEAR(orders.orderDate) as year,
    SUM(orderdetails.quantityOrdered * orderdetails.priceEach) as totalSales
FROM 
    products
JOIN 
    orderdetails ON products.productCode = orderdetails.productCode
JOIN 
    orders ON orderdetails.orderNumber = orders.orderNumber
GROUP BY 
    products.productCode, YEAR(orders.orderDate);

Exercise 2: Stored Procedures and Triggers

Create a stored procedure to add a new product to the products table. After adding the new product, the stored procedure should calculate and update the average product price in the productlines table for the product line of the new product.

Now, write a trigger that calls this stored procedure whenever a new product is inserted into the products table.

Exercise 3: Conditional Statements and Cursors

Write a stored procedure that uses a cursor to iterate over all orders that have a status of 'In Process'. For each such order, if the total quantity of products ordered exceeds the quantity in stock for any product, set the status of the order to 'On Hold'. Use conditional statements to implement this logic.

Exercise 4: Optimizing Complex Joins

Given the following query that joins four tables, optimize the query by choosing the correct order of joins and other optimizations.

SELECT 
    customers.customerNumber, 
    customers.customerName,
    SUM(orderdetails.quantityOrdered * orderdetails.priceEach) as totalSales,
    COUNT(DISTINCT products.productLine) as numberOfProductLinesPurchased,
    employees.employeeNumber,
    CONCAT(employees.firstName, ' ', employees.lastName) as employeeName
FROM 
    customers
JOIN 
    orders ON customers.customerNumber = orders.customerNumber
JOIN 
    orderdetails ON orders.orderNumber = orderdetails.orderNumber
JOIN 
    products ON orderdetails.productCode = products.productCode
JOIN 
    employees ON customers.salesRepEmployeeNumber = employees.employeeNumber
GROUP BY 
    customers.customerNumber, employees.employeeNumber;

Sure, let's continue with more assignments using advanced SQL constructs and optimization techniques.

Exercise 5: Handling NULLs

Given the following query, optimize it and handle the NULL values in a way that doesn't disrupt the calculations or results. Use the COALESCE function or any other relevant method for this.

SELECT 
    customers.customerNumber,
    customers.customerName,
    SUM(orderdetails.quantityOrdered * orderdetails.priceEach) as totalSales,
    AVG(products.buyPrice) as averageProductPrice
FROM 
    customers
LEFT JOIN 
    orders ON customers.customerNumber = orders.customerNumber
LEFT JOIN 
    orderdetails ON orders.orderNumber = orderdetails.orderNumber
LEFT JOIN 
    products ON orderdetails.productCode = products.productCode
GROUP BY 
    customers.customerNumber;

Exercise 6: Subqueries and HAVING Clause

Write a query that shows the customers who have made total purchases of more than the average purchase amount. Use a subquery to calculate the average purchase amount, and a HAVING clause to filter the customers.

Exercise 7: UNION and Window Functions

Create a query that shows the total sales for each product line for the year 2003 and the year 2004. Use the UNION operator to combine the results for the two years into a single result set. Then, over this result set, use a window function to calculate the year-on-year growth rate in sales for each product line.

Exercise 8: Case Statements and Complex Aggregates

Write a query that categorizes customers into 'High Value', 'Medium Value', and 'Low Value' based on their total purchases. Use a CASE statement to create the categories, and complex aggregates to define the thresholds for each category.

Exercise 9: Optimizing Joins with Derived Tables

In the following query that joins five tables, optimize the query by using derived tables.

SELECT 
    customers.customerNumber,
    customers.customerName,
    employees.employeeNumber,
    CONCAT(employees.firstName, ' ', employees.lastName) as employeeName,
    offices.city as officeCity,
    SUM(orderdetails.quantityOrdered * orderdetails.priceEach) as totalSales,
    COUNT(DISTINCT products.productLine) as numberOfProductLinesPurchased
FROM 
    customers
JOIN 
    orders ON customers.customerNumber = orders.customerNumber
JOIN 
    orderdetails ON orders.orderNumber = orderdetails.orderNumber
JOIN 
    products ON orderdetails.productCode = products.productCode
JOIN 
    employees ON customers.salesRepEmployeeNumber = employees.employeeNumber
JOIN 
    offices ON employees.officeCode = offices.officeCode
GROUP BY 
    customers.customerNumber, employees.employeeNumber, offices.officeCode;

Consider the sizes of the tables, the number of rows that will be returned after each join, and the use of indexes when optimizing this query.

Make sure you explain the optimization steps you took and why they improve the performance of the query. Also, consider the trade-offs of using these advanced SQL constructs and optimization techniques. Remember to explain your reasoning and the impact of your optimizations for each exercise.