10. Creating Views

Base table—–> view table—> DML operations(insert, update and delete)

Select statement directly from the base table
Not all views are used for insert update and delete
But updatable only on direct selection from the columns
Hide certain columns by selecting a portion of the data

Altering and Managing Views:

1. Views are virtual table which is modified when their underlying query or structure(base table) changes.
2. Additionally, views can be dropped when they are no longer needed.
3. View are not applicable for derived columns

View for frequently selecting columns:

With check option to view only virtual table:

View not updatable:

create view aview as select * from authors where authorID < 15 WITH CHECK Option;

Real-world Examples of Views in MySQL

Creating Simple Views

Create a view called emp_sal on the myemp table by selecting the following fields in the order of highest salary to the lowest salary. emp_no, first_name, last_name, salary

CREATE VIEW department_salaries AS
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department ORDER BY SALARY DESC;

Creating a virtual table:

Task:
Create a view territory_status mentioning the details about no. of employees in each territory.
Tables to be used: Offices, Employees

CREATE VIEW products_status AS
SELECT
    YEAR(o.orderDate) AS orderYear,
    count(od.quantityOrdered) AS 'Tot_Qty_Ordered',
CONCAT('(', ROUND((SUM(od.quantityOrdered * od.priceEach) / (SELECT SUM(quantityOrdered * priceEach) FROM orderdetails)) * 100, 2), '%)') as percentage
FROM
    orders o
JOIN
    orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY
    orderYear;
select * from products_status;

WITH CTE as(
select Year(paymentDate)AS P_Year,
SUM(amount)as Tot_payment from [dbo].[CM_payments]
group by  Year(paymentDate)
),
cte1 as(
select P_Year,Tot_payment , sum(Tot_payment)over(order by P_Year rows between unbounded preceding and current row)as cum_sum from CTE),
cte2 as(
select P_Year, Tot_payment, cum_sum,
lag(cum_sum,1,0)over(order by P_Year)as
cummi_prev_1 from cte1
)
select P_Year, cast(
CASE 
            WHEN cummi_prev_1 = 0 THEN 0 
            ELSE (1.0 * Tot_payment / cummi_prev_1) * 100 
        END AS DECIMAL(10,1)
    ) AS percent_change
FROM cte2;