1 / 18

Views in Database - Creating, Modifying, and Retrieving Data

This chapter focuses on creating, modifying, and retrieving data from views in a database, including advantages, creating views, checking structure and content, using column aliases and functions, modifying views, and creating views from multiple tables.

warrenv
Download Presentation

Views in Database - Creating, Modifying, and Retrieving Data

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. Chapter 2 Views

  2. Objectives • Create views • Modify/Drop Views • Insert/Delete/Update to/from views • Retrieve data from views

  3. Facts • Views as logical representation of data • Views are queried like tables • Views are objects similar to tables • Views are stored queries • Every time a view is uses the query is run (Views are not stored database)

  4. Advantages of Views • Restrict database access • Represent data from different tables • Make complex query easy • Calculate values of formula • Represent different views of the same data • Views can be created from other views

  5. Creating Views • CREATE [OR REPLACE] VIEW name AS sub-query [WITH READ ONLY]

  6. Creating Views • Example:CREATE OR REPLACE VIEW salesEmpl AS SELECT * FROM testdata.Employee WHERE dept=‘Sales’;

  7. Check the Structure of Views • DESC salesEmpl;NameNull?TypeF_Name VARCHAR2(20)L_Name VARCHAR2(20)SSN NUMBER(9)Dept VARCHAR2(20)B_Date DATE

  8. Check the Content of ViewsSELECT *FROM SalesEmpl; Retrieve data from ViewsSELECT L_Name, F_Name, SSNFROM SalesEmplWHERE TO_CHAR(B_Date, ‘YYYY’)=1973;

  9. Column Aliases:CREATE VIEW GoodStudents AS SELECT Name, SSN ID, Major Area, Email Contact FROM Students Where GPA>3; Alternative Renaming:CREATE OR REPLACE VIEW GoodStudents(Name, ID, Area, Contact)AS SELECT Name, SSN, Major, Email FROM Students WHERE GPA>3;

  10. Use of Functions: • CREATE VIEW EmployeeData(Dept, MinSal, MaxSal, AvgSal, CountEmp)AS SELECT Dept, MIN(PayRate), Max(PayRate), AVG (PayRate), Count(*) FROM Employee;

  11. Use of Functions: SELECT * FROM EmployeeData; • How to modify Views?

  12. SELECT only Views:CREATE View EmployeeDataAS SELECT L_Name, F_Name, Dept FROM EmployeeWITH READ ONLY; Why do we need a read only view?

  13. Delete Views: DROP VIEW Name) DROP VIEW EmployeeData;

  14. Check the list of viewsSELECT *FROM USER_VIEWS;

  15. INSERT Data into ViewINSERT INTO GoodStudent (ID, Name, Area, Contact) VALUES (211202111, ‘John Smith’, ‘COSC’, ‘Jsmith@fsu.edu’);

  16. Insert with NULL value: INSERT INTO GoodStudent (ID, Name, Area, Contact)VALUES (211202111, ‘John Smith’, NULL, ‘Jsmith@fsu.edu’);

  17. Delete data from views: DELETE GoodStudentWHERE ID=211202111; Update Views:UPDATE GoodStudentSET Area=‘MATH’WHERE ID=211202111;

  18. Create Views from Multiple table • CREATE OR REPLACE VIEW SalesInfo (Name, ID, Item, Cost)AS SELECT F_Name || ‘ ‘ || L_Name, ID, HowMany, Price FROM Employee, Sales WHERE ID=E_ID;

More Related