1 / 6

Understanding SQL Views: Creation and Management

This guide explains the concept of SQL views, including their creation and removal within a database. Learn how to embed subqueries while creating a view through the `CREATE VIEW` statement. The example provided demonstrates how to create a specific view, `empvu45`, which selects employee IDs, last names, and titles from a specified department. Additionally, it covers how to drop an existing view using the `DROP VIEW` command. Mastering views is essential for efficient data management and retrieval in SQL.

hoshi
Download Presentation

Understanding SQL Views: Creation and Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

More Related