Creating Views

1. Creating Views

2. What Is a View? ID LAST_NAME FIRST_NAME TITLE DEPT_ID -- ------------ ---------- -------------------- ------- 1 Velasquez Carmen President 50 2 Ngao LaDoris VP, Operations 41 3 Nagayama Midori VP, Sales 31 4 Quick-To-See Mark VP, Finance 10 5 Ropeburn Audry VP, Administration 50 6 Urguhart Molly Warehouse Manager 41 7 Menchu Roberta Warehouse Manager 42 8 Biri Ben Warehouse Manager 43 9 Catchpole Antoinette Warehouse Manager 44 10 Havel Marta Warehouse Manager 45 11 Magee Colin Sales Representative 31 12 Giljum Henry Sales Representative 32 13 Sedeghi Yasmin Sales Representative 33 14 Nguyen Mai Sales Representative 34 15 Dumas Andre Sales Representative 35 16 Maduro Elena Stock Clerk 41 17 Smith George Stock Clerk 41 18 Nozaki Akira Stock Clerk 42 19 Patel Vikram Stock Clerk 42 20 Newman Chad Stock Clerk 43 21 Markarian Alexander Stock Clerk 43 22 Chang Eddie Stock Clerk 44 23 Patel Radha Stock Clerk 34 24 Dancs Bela Stock Clerk 45 25 Schwartz Sylvie Stock Clerk 45 S_EMP Table EMPVU45 View ID LAST_NAME TITLE -- ----------- --------------- 10 Havel Warehouse Manager 24 Dancs Stock Clerk 25 Schwartz Stock Clerk

3. Creating a View: Syntax • Embed a subquery within the CREATE VIEW statement. CREATE VIEW view AS subquery

4. Creating a View: Example CREATE VIEW empvu45 AS SELECT id, last_name, title FROM s_emp WHERE dept_id = 45;

5. CREATE VIEW empvu45 (id_number, employee, job) AS SELECT id, last_name, title FROM s_emp WHERE dept_id = 45;

6. Removing a View: Example DROP VIEW empvu45; View dropped.