Oracle SQL in 10 Minutes - Asim Abbasi
CH1: SQL Basics | CH2: SQL Operators | CH3: SQL Built-in Functions | CH4: SQL Joins | CH5: UPDATE, INSERT & DELETE Statements | CH6: CREATE, ALTER & DROP Statements | CH7: Constraints | CH8: Linking Tables vs Joining Tables | CH9: SQL Statements for Other Database Objects | CH10: SQL Statements for Database Security
Chapter 9: SQL Statements for Other Database Objects
Other Database Objects:
Figure 18: Managing other database objects: Views,
Indexes, Sequences & Synonyms.
________________________________________
Besides tables that we create within our schema, there are other logical structures called database objects. One of those objects is called a View. Like the name says, it’s a view, a window through which we can see only the data we need or in other words we can create a view and grant users to view the information. While creating the view we will include only those information that we want others to have access.
Views are comprised of one or more tables or views and they themselves
contain no data and the underlying tables are called based tables.
Creating views is exactly similar creating tables, the only difference
is we use CREATE VIEW statement instead of CREATE TABLE statement.
Example:
CREATE VIEW emp_view AS
SELECT *
FROM employees
WHERE department_id = 20;
Dropping a view will not drop the underlying base tables or views. Also
you can insert and update the base tables through the view provided the
constraints implemented on the based tables won’t get violated. We can
use any of the form of Join-based SELECT statement to have the view
based on more than one table.
We can also create a read only view using the WITH READ ONLY clause and
grant access to users so that then they would not be able to modify the
base tables.
Example:
CREATE VIEW emp_view AS
SELECT *
FROM employees
WHERE department_id = 20
WITH READ ONLY;
Example:
DROP VIEW emp_view;
Dropping view neither drops base tables or views nor the data residing
in each of those base tables.
Chapter 9: SQL Statements for Other Database Objects
CH1: SQL Basics | CH2:
SQL Operators |
CH3: SQL Built-in Functions | CH4:
SQL Joins | CH5:
UPDATE, INSERT & DELETE Statements |
CH6: CREATE, ALTER & DROP Statements | CH7:
Constraints | CH8:
Linking Tables vs Joining Tables | CH9:
SQL
Statements for Other Database Objects | CH10:
SQL Statements for
Database Security Share with others: