SQL Views

1. SQL Views Chapter 3A

2. Appendix Objectives • Learn basic SQL statements for creating views • Learn basic SQL statements for using views • Learn basic SQL statements for deleting views • Understand the reasons for using views

3. SQL Views • A SQL view is a virtual table that is constructed from other tables or views • A view has no data of its own, but uses data stored in tables or other views • Views are created using SQL SELECT statements • Views are used in other SELECT statements just as if they were a table • In some environments, the SQL statements that create the views may not contain an ORDER BY clause. If the results of a query using a view need to be sorted, the sort order must be provided by the SELECT statement that processes the view • However, Oracle allows you to create the views that contain an ORDER BY clause

4. SQL CREATE VIEW Statement • The SQL CREATE VIEW statement is used to create view structures. CREATE VIEW ViewName AS {SQL SELECT statement};

5. SQL CREATE VIEW Example Create a view to list the city and product name for each product ordered by its quantity: create view v_products as select city, pname as myname from products order by quantity;

6. Using an SQL SELECT Statement • Once the view is created, it can be used in the FROM clause of SELECT statements just like a table. select * from v_products;

7. Some Uses for SQL Views • Hide columns or rows • Display results of computations • Hide complicated SQL syntax • Layer built-in functions

8. Using SQL Views: Hide columns or rows I Why do we want to hide columns/rows? 1. simplify results 2. security reasons prevent the display of sensitive data create view v_products as select city, pname as myname from products order by quantity; select * from v_products;

9. Using SQL Views: Hide columns or rows II How do we hide rows? • Using WHERE clause Create a view to list the city and product name for cities whose names start from ‘D’ ordered by its quantity: create view v_products_2 as select city, pname as myname from products where city like 'D%' order by quantity ;

10. Using SQL Views: Display results of computations – SQL Statement What are the benefits? 1. it saves users from having to know/remember how to calculate 2. it makes the results consistent Create a view to list the city and the total product quantities for each city: create view v_products_3 as select city, sum(quantity) as product_quantity from products group by city; select * from v_products_3;

11. Using SQL Views: Hide complicated SQL syntax – SQL Statement Create a view to list the product ids and the total order quantity for each of them: create view v_products_4 as select p.pid, sum(o.qty) as order_quantity from products p, orders o where p.pid = o.pid group by p.pid; select * from v_products_4;

12. Using SQL Views:Layering Computations and Built-in Functions1st SQL Statement Create a view to list the product ids, along with the product quantity and the total order quantity for each of them: create view v_products_5 as select p.pid, p.quantity, sum(o.qty) as order_quantity from products p, orders o where p.pid = o.pid group by p.pid, p.quantity; select * from v_products_5;

13. Using SQL Views:Layering Computations and Built-in Functions2nd SQL Statement Check those products that the total order quantity is over the product quantity: create view v_products_6 as select pid from v_products_5 where quantity < order_quantity;

14. Using SQL Views:Layering Computations and Built-in FunctionsResults insert into orders (ordno,month,cid,aid,pid,qty,dollars) values (1034,'jan','c001','a01','p01',700000,450.00); Select * from v_products_6;