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 the 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 a set of statements that can be divided into different types.
DDL(Data Definition Lang): Create, Alter, Drop, Truncate
DML(Data manipulation Lang): Insert, Update, Delete, Merge
DQL(Data Query Language): Select
DCL(Data control Lang): Grant, Revoke
TCL(Transaction Control Lang): Commit, Rollback, Savepoint
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.
Que- Write a query to fetch “Top salary or Second Top salary”
Way1:
SELECT *
FROM (SELECT tab.*, ROWNUM rn
FROM (SELECT e.*
FROM employees e
ORDER BY sallary desc) tab)
WHERE rn = 2Way2:
select * from
(
select a.*, rank() over(order by sallary desc) rn from employees a
)
where rn = 2
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..
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 :).
its very nice tutorial for freshers nd experience people
concepts are explained in sweet and simple, this is the best one i seen ever thank you so much:)
Nice Article,