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.
Oracle Objects
-
Procedure
-
Function
-
Collection
-
Type
-
Trigger
-
Cursor
-
RefCursor
-
Record
-
Views
-
Package
-
Synonym
-
Sequence
There are several Table which contains the details of these objects. Oracle Provides Views to see available Objects.
Important Views Contains list of Objects
- USER_TABLES
- View, which contains the Description of user’s own relational tables
- USER_PROCEDURES
- Description of the user functions/procedures/packages/types/triggers
- USER_OBJECTS
- View for showing Objects owned by the user
- USER_SOURCE
- View of data dictionary for source, Source of stored objects accessible to the user
- USER_TAB_COLUMNS
- Columns of user’s tables, views and clusters
- TAB
- Is a view, same as user_tables
- USER_INDEXES
- View for Description of the user’s own indexes
- USER_CONSTRAINTS
- View for Constraint definitions on user’s own tables
- ALL_OBJECTS
- Data dictionary which contains all types of objects details for all users.
- USER_SEQUENCES
- Description of the user’s own sequences
Oracle Statement Type
Oracle has set of statements which can be divided in different types.
- DDL
- Create, Alter, Drop, Truncate (Data Definition Lang)
- DML
- Insert, Update, Delete, Merge (Data manipulation Lang)
- DQL
- Select (Data Query Language)
- DCL
- Grant , Revoke (Data control Lang)
- TCL
- Commit, Rollback, Savepoint (Transaction Control Lang)
Oracle Constraints
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
Important Question Asked in Java Interview
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.
Queries asked in Java Interview
1- Query to fetch “Top sallary or Second Top sallary”
Way1:
SELECT *
FROM (SELECT tab.*, ROWNUM rn
FROM (SELECT e.*
FROM employees e
ORDER BY sallary desc) tab)
WHERE rn = 2
Way2:
select * from
(
select a.*, rank() over(order by sallary desc) rn from employees a
)
where rn = 2
Nice Article,
concepts are explained in sweet and simple, this is the best one i seen ever thank you so much:)
its very nice tutorial for freshers nd experience people
It is very useful information. Concepts are explained in precise and easily understandable format. Like it a lot. This article is very useful for fresher as well as experienced professional. 🙂
Thank you Vivekanand :).
Hello Sir,
I started reading your tutorials..it’s very nice and I can able to understand properly..It is very useful for beginners like me..Thank you very much for doing these things..