VIEWS Pertemuan 7

1. VIEWSPertemuan 7 Matakuliah : T0413/Current Popular IT II Tahun : 2007

2. AGENDA:•What are Views?•Updating View•Naming Columns•Grouped Views•Views and Joins•View and Subqueries •Using Multiple Queries in Views•What Views Can Not Do•Dropping Views•Updatable Views•Read Only Views•Checking the Values in Views Book: Mastering SQL by Martin Gruber Sybex (2000) Chapter : 14-15

3. What are Views? • Views are tables whose contents are taken or derived from other tables. • Also knows as virtual tables, or viewed tables. • Using the CREATE VIEW Statement: • CREATE VIEW viewname AS query;

4. What are Views? (cont’d) • CREATE VIEW Londonstaff AS SELECT snum, sname, city, comm FROM Salespeople WHERE city = ‘London’; • CREATE VIEW Salesown AS SELECT snum, sname, city FROM Salespeople;

5. Updating View • We can modify the content of a view by using data manipulation statement, but the modifications do not affect the view itself. • UPDATE Salesown SET city = ‘Palo Alto’ WHERE snum = 1004; • Remember that some views can not be updated (will be discussed later).

6. Naming Columns • So far, we have taken names of columns of our views directly from the names of the columns in the underlying tables. • However, sometimes we need to provide new names for our columns in the views. • On condition such as: • When some of the columns in the SELECT clause of the underlying query are expressions or derived values and therefore unnamed. • When the query in the view accesses multiple underlying tables, and two or more columns in those tables have the same name. • To support this, we can use AS argument.

7. Naming Columns (cont’d) • Example: CREATE VIEW Londonstaff AS SELECT snum AS Salesperson_Number, sname AS Last_name, city, comm AS Commission FROM Salespeople WHERE city = ‘London’;

8. Grouped Views • Grouped Views are views that contain a GROUP BY clause or that are based on other grouped views. • Example: CREATE VIEW Totalforday(Date, Custcount, Salescount, Ordercount, Averageamt, Totalorders) AS SELECT odate, COUNT(DISTNCT cnum), COUNT(DISTINCT snum), COUNT(onum), AVG(amt), SUM(amt) FROM Orders GROUP BY odate; • We can access it by: SELECT * FROM Totalforday;

9. Views and Joins • Views can be drawn from more than one tables. • Example: CREATE VIEW Nameorders AS SELECT onum, amt, a.snum, sname, cname FROM Orders a, Customers b, Salespeople c WHERE a.cnum = b.cnum AND a.snum = c.snum; • We can access it by: SELECT * FROM Nameorders WHERE snum = 1007;

10. View and Subqueries • Views can also use subqueries, including correlated subqueries. • Suppose we would like to track the bonus of the salesperson who has the customer with the highest order on any given date : • Example: CREATE VIEW Elitesalesforce AS SELECT b.odate, a.snum, a.sname FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX(amt) FROM Orders C WHERE c.odate = b.odate);

11. View and Subqueries (cont’d) • Suppose we would like to track the bonus of the salesperson who has the customer with the highest order at least ten times on any given date : • Example: CREATE VIEW Bonus AS SELECT DISTINCT snum, sname FROM Elitesalesforce a WHERE 10 <= (SELECT COUNT(*) FROM Elitesalesforce b WHERE a.snum = b.snum;

12. Using Multiple Queries in Views • Views can be based on multiple queries combined with the UNION, EXCEPT, and INTERSECT operators. • Meaning that you can treat material from several tables as though it were from one table. • Example: CREATE VIEW Londonpeople AS SELECT snum, sname, ‘salesperson’ FROM Salespeople WHERE city = ‘London’ UNION SELECT cnum, cname, ‘ customer’ FROM Customers WHERE city = ‘London’;

13. What Views Can Not Do • So far, types of views are read only. • Meaning that you can query them, but you can not subject them to update statements (further details will be discussed later). • We can not use ORDER BY in the definition of a view. It is unordered list output. • However, queries on views may use ORDER BY. • Do differentiate queries within a view and queries on the view.

14. Dropping Views • The syntax to eliminate a view from the database is similar to that for removing tables. • Syntax: DROP VIEW viewname; • Example: DROP VIEW Bonus;

15. Changing Values Through Views Updating Views • What happen when you update a view? The simple answer is that the changes are transferred to the base tables that underlie the view. Determining Whether a View is Updatable • If you can perform update statements on a view, it is called updatable, otherwise it is read-only. • Updating a view means that by executing any of the three DML statements (INSERT, UPDATE, and DELETE).

16. Updatable Views • The criteria that determine whether a view is updatable are based on the content of the underlying query, which must satisfies rules as below: • It must be drawn on one and only one underlying table. • It must have no aggregate functions • It must not specify DISTINCT • It must not use GROUP BY or HAVING • It may be defined on another view, but that view must also be updatable • It must not use constants, strings, or value expressions among the selected output columns • For INSERT, it must include any columns of the underlying table that have the NOT NULL constraint, unless another default value has been specified. • Not all SQL version support subqueries as a standard. • It should include primary key of the underlying table.

17. Read Only Views • Read-only views allow you to derive and reformat data extensively. • They give you a library pf complex queries that you can execute and re-execute. Keeping your derived information strictly up to the minute. • It also have the security appliactions.

18. Determining Which Views are Updatable • CREATE VIEW Dateorders(odate, ocount) AS SELECT odate, COUNT(*) FROM Orders GROUP BY odate; • Is it updatable ? Why ? • CREATE VIEW Londoncust ASSELECT * FROM Customers WHERE city = ‘London’ • Is it updatable ? Why ?

19. Checking the Values in Views • You can ensure against modifications of the views by using WITH CHECK OPTION in the definition of the view. • For example: CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300; • And then we try to insert a data: INSERT INTO Highratings VALUES (2018, 200) • The values will be inserted and successed, but then disappear because the rating is below 300.

20. Checking the Values in Views (cont’d) • Try to prevent that, by using WITH CHECK OPTION CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300 WITH CHECK OPTION; • The query to insert the new data will rejected and failed to insert.

21. End of V I E W S Thank you