ORACLE Database

Oracle shell:-

Create USer:-
>CREATE USER books_admin IDENTIFIED BY MyPassword;

GRANT Statement(Access):-
adding privileges to the new user using GRANT statement.

PROVIDING Roles:-
>GRANT CONNECT To books_admin;

Oracle Users:-
SELECT * FROM ALL_USERS;

DROP User:-
>DROP USER sidney;

If user Sidney's schema contains no objects, then you can drop sidney by issuing the statement.

Drop User and User Objects:-
>DROP USER sidney CASCADE;

If sidney schema contains objects, then you must use the CASCADE clause to drop sidney and the objects.

Oracle View

Oracle, view is a virtual table that does not physically exist. 

It is stored in Oracle data dictionary and do not store any data. 

It can be executed when called.

A view is created by a query joining one or more tables.

Example:

Let's take an example to create view. In this example, we are creating two tables suppliers and orders first

Suppliers table:

1. CREATE TABLE  "SUPPLIERS"  
2.    (    "SUPPLIER_ID" NUMBER,   
3.     "SUPPLIER_NAME" VARCHAR2(4000),   
4.     "SUPPLIER_ADDRESS" VARCHAR2(4000)  
5.    )  
6. /  

Orders table:

1. CREATE TABLE  "ORDERS"   
2.    (    "ORDER_NO." NUMBER,   
3.     "QUANTITY" NUMBER,   
4.     "PRICE" NUMBER  
5.    )  
6. /  

Execute the following query to create a view name sup_orders

Create View Query:

1. CREATE VIEW sup_orders AS  
2. SELECT suppliers.supplier_id, orders.quantity, orders.price  
3. FROM suppliers  
4. INNER JOIN orders  
5. ON suppliers.supplier_id = supplier_id  
6. WHERE suppliers.supplier_name = 'VOJO';

Output:

View created.
0.21 seconds

You can now check the Oracle VIEW by this query:

SELECT * FROM sup_orders;

Output:

SUPPLIER_ID QUANTITY PRICE
  3          35           70
  3          26          125
  3          18          100
3 rows returned in 0.00 seconds

Oracle Update VIEW

Oracle, the CREATE OR REPLACE VIEW statement is used to modify the definition of an Oracle VIEW without dropping it.

Example:

Execute the following query to update the definition of Oracle VIEW called sup_orders without dropping it.

1. CREATE or REPLACE VIEW sup_orders AS  
2.   SELECT suppliers.supplier_id, orders.quantity, orders.price  
3.   FROM suppliers  
4.   INNER JOIN orders  
5.   ON suppliers.supplier_id = supplier_id  
6.   WHERE suppliers.supplier_name = 'HCL';

You can now check the Oracle VIEW by this query:

SELECT * FROM sup_orders;

Output:

SUPPLIER_ID QUANTITY PRICE
      1          35          70
      1          26         125
      1          18         100
row(s) 1 - 3 of 3

Oracle DROP VIEW

DROP VIEW statement is used to remove or delete the VIEW completely.




Example:




DROP VIEW sup_orders;