When designing your database, you can keep your tables private and allowing your users to access data only through a set of views.

What are Views?

A view is simply a mechanism for querying data. You assign a name to a select statement, then store it so others can use to view what they want.

create view customer_vw (customer_id, first_name, last_name, email)
as
select customer_id, first_name, last_name,
	concat(substring(email,1,2), '******', substring(email,-6))
from customer;

select * from customer_vw;

The first part of the statement lists the view’s column names, which can be different from those of the underlying table.

The second part of the statement is a select statement, which must contain one expression for each column in the view.

<aside> 💡 You are free to use any clauses of the select statement when querying through a view, including group by, having, order by or join.

</aside>

select cv.first_name, cv.last_name, p.amount
from customer_vw as cv
	inner join payment as p
  on cv.customer_id = p.customer_id
where p.amount > 11;

Why Use Views?

Data Security

• Uncensored data can bring risk as it is expose to all users, violate policies of the company, state or federal laws.
• You should keep the table private (i.e., don’t grant select permission to any users) and then to create one or more views that either omit or obscure (such as '*****') the sensitive columns.

<aside> 💡 Oracle has Virtual Private Database for securing both rows and columns of a table.

</aside>

Data Aggregation

• Reports usually require aggregated data, and views are a great way to make it appear as though data is being preaggregated and stored in the database.
• Rather than writing query, you can provide a view for it, therefore saving time.

create view sales_by_film_category
as
select c.name as category, sum(p.cmount) as total_sales
from category as c
	inner join film_category as fc on fc.category_id = c.category_id
  inner join film as f on f.film_id = fc.film_id
  inner join inventory as i on i.film_id = f.film_id
  inner join rental as r on r.inventory_id = i.inventory_id
  inner join payment as p on p.rental_id = r.rental_id
group by c.name
order by 2 desc;

<aside> 💡 You can also create a table like this temporarily for performance boost.

</aside>

Hiding Complexity