Practice Exercise - Views - Types of Views - MySQL

[akash-coded]

1. Updatable View:

   • Create an updatable view that includes customerNumber, customerName, contactLastName, and contactFirstName from the customers table. Then, try to update the contactFirstName for a specific customerNumber.

2. Read-Only View:

   • Create a read-only view that joins the orderdetails table and the products table on productCode and includes orderNumber, productName, and quantityOrdered. Try to update the quantityOrdered for a specific orderNumber and see what happens.

3. Inline View:

   • Write a query that uses an inline view to get the total number of orders for each customer. The inline view should select customerNumber and orderNumber from the orders table. The main query should then group by customerNumber.

4. Materialized View:

   • Note that MySQL does not natively support materialized views, but you can mimic them with a combination of stored procedures and triggers. The task here would be to create a stored procedure that creates a new table with productName and totalQuantityOrdered (this total should be aggregated from the orderdetails table). Then, create an AFTER INSERT trigger on the orderdetails table that calls this stored procedure to update the table (acting as a materialized view) whenever a new order detail is inserted.

Remember, these exercises are for practice purposes. The structure of the database and business requirements would dictate whether these views are needed or if they would be created in this exact way. Also, always be careful when updating data in a database. If this is a live database or contains important data, you should backup the database before attempting to update or delete data.

[akash-coded]

Solution Approach

Updatable View:

First, we create the view.

CREATE VIEW customer_view AS
SELECT customerNumber, customerName, contactLastName, contactFirstName
FROM customers;

Then, to update a specific record, we do something like:

UPDATE customer_view 
SET contactFirstName = 'NewFirstName'
WHERE customerNumber = some_number;

Read-Only View:

Here's how you can create a read-only view.

CREATE VIEW order_product_view AS
SELECT od.orderNumber, p.productName, od.quantityOrdered
FROM orderdetails od 
JOIN products p ON od.productCode = p.productCode;

If you try to update this view, you'll get an error because it involves multiple base tables.

Inline View:

Here's a query with an inline view.

SELECT iv.customerNumber, COUNT(iv.orderNumber) as total_orders
FROM (
  SELECT customerNumber, orderNumber 
  FROM orders
) as iv
GROUP BY iv.customerNumber;

Materialized View:

You'd need to create a stored procedure and a trigger.

Here's the stored procedure to create the table:

CREATE PROCEDURE refresh_materialized_view()
BEGIN
  DROP TABLE IF EXISTS materialized_view;
  
  CREATE TABLE materialized_view AS
  SELECT p.productName, SUM(od.quantityOrdered) as totalQuantityOrdered
  FROM orderdetails od 
  JOIN products p ON od.productCode = p.productCode
  GROUP BY p.productName;
END;

Here's the trigger to refresh the "materialized view" after each insert:

CREATE TRIGGER orderdetails_after_insert
AFTER INSERT ON orderdetails
FOR EACH ROW
CALL refresh_materialized_view();

Every time a row is inserted into orderdetails, this will refresh the materialized_view table. This approach, however, might have performance implications on a big database as it rebuilds the entire table every time there's a new row in orderdetails.

Remember to adjust these SQL statements according to the rules and standards in your database schema.