Here we will discuss the some key areas of Oracle which will be usefull for Java beginners(Not Oracle Expert).
Oracle has Schemas which is nothing but User. Whenever a user has been created a new schema is also created. If it has grant of session then User can login to Oracle.
Objects are also there in Oracle as in Java.
There are several Table which contains the details of these objects. Oracle Provides Views to see available Objects.
- View, which contains the Description of user’s own relational tables
- Description of the user functions/procedures/packages/types/triggers
- View for showing Objects owned by the user
- View of data dictionary for source, Source of stored objects accessible to the user
- Columns of user’s tables, views and clusters
- Is a view, same as user_tables
- View for Description of the user’s own indexes
- View for Constraint definitions on user’s own tables
- Data dictionary which contains all types of objects details for all users.
- Description of the user’s own sequences
Oracle has set of statements which can be divided in different types.
- Create, Alter, Drop, Truncate (Data Definition Lang)
- Insert, Update, Delete, Merge (Data manipulation Lang)
- Select (Data Query Language)
- Grant , Revoke (Data control Lang)
- Commit, Rollback, Savepoint (Transaction Control Lang)
Oracle supports different type of constraints which can be applied to different Objects.
- P – Primary
- U – Unique
- R – Foreign Key
- O – Created on Views – Used for Read Only Views
- C – Check Constraints
- V – View Checks Constraint
Que: Difference between Delete and Truncate?
Ans: Differences are as below
- Delete is DML statement while Truncate is a DDL statement.
- After deleting data will go to rollback block, while data will not go to rollback block in case of Truncate.
- Size of the table will be reduced in case of Truncate, In case of Delete size will not be reduced.
- Auto commit will happen in case of Truncate which is not true in case of Delete.
Que: Difference between Procedure & Function?
Ans: Differences are
- Function needs to have return keyword but procedure do not have any Return keyword.
- Function can be used in select query but procedure can not be.
- Procedure can be used with exec but function can not be.
- Function can be used with call but procedure can not be.
- Procedure may or may not return any value but funnction has to return some value.
Que: Type of Views?
Ans: Types are
- Simple Views
- Complex Views
- Materialized VIews
Que: Type of Collection Oracle?
Ans: Types are
- VARRAY – Size is fixed and stored in DB
- NestedTable – Variable Sized, Needs to be initialized, Stored in DB
- Binary Table – Variable Sized, Local Scope, Can not be stored in DB.
1- Query to fetch “Top sallary or Second Top sallary”
SELECT * FROM (SELECT tab.*, ROWNUM rn FROM (SELECT e.* FROM employees e ORDER BY sallary desc) tab) WHERE rn = 2
select * from ( select a.*, rank() over(order by sallary desc) rn from employees a ) where rn = 2