PLSQL Developer Interview Questions

Plsql Interview Questions:

1.Difference Between ROWID and ROWNUMBER

ROWID	ROW NUMBER
Row id is a static value (always same row has same rowed)	Row number is not a static values(row number changes dynamically based on select statement)
Row id is hexadecimal value	Row number is numeric value
Row id is combination of object id, file number, block number, row number	Row number is temporary number based on select statement
Oracle automatically generate row id when user insert new row into table	Row number will be generated based output of select statement
Row id will be stored in database	Row number won’t be stored in the database

2.Difference between Truncate and Delete

TRUNCATE	DELETE
Truncate removes all the rows from the table	Delete removes all rows and selected rows from the table
Truncate won’t support where clause	Delete supports where clause
Truncate is ddl command	Delete is dml command
After truncate we don’t need to give and tcl command as truncate is ddl command and all ddl commands are auto commit	After delete we need to give tcl command like commit or rollback
Truncate is faster than delete as it doesn’t have undo segment	Delete is slower as it uses undo segment
Truncate clears the memory allocated to rows(internal memory will be cleared )	Delete won’t clear the internal memory and internal memory will be still exists
Truncate resets the high level water mark and reclaims the space used by the table	Delete won’t reset the high level water mark and won’t reclaims the space used by the table

3.Difference between Case and Decode

CASE	DECODE
Case is statement and it provides if else type logic	Decode is function and it provides if else type logic
Case can be used along with sql statements and without sql statements also we can use case	Decode can be used in sql statements only
Case supports operators like <,>,<=,>=,like, in, between, not	Decode won’t support any operators
By using case we can assign any value to a variable in the plsql block	Decode can’t be used inside plsql block
Case allows only one data type in single statement	Decode allows any no of data types in single statement
Case is easy to understand	Decode is difficult to understand

4.Difference between where and having

WHERE	HAVING
Where clause is used to get specific data from the table	Having clause is used to apply conditions on grouped data
Where clause filters row data	Having clause filters grouped data
Where conditions are applied before group by clause	Having conditions are applied after group by clause
Where won’t support aggregate functions	Having clause supports aggregate functions
After where clause we will write normal columns only	After having clause we can write aggregate functions only
By using where clause we can join any number of tables	By using having clause we can’t join tables

5.Difference between Table and View

TABLE	VIEW
Table is combination of rows and columns	View is stored select statement
Table occupy some space in database	View won’t occupy any space in database
Table stores some data in their self	View is a logical component(it won’t occupy any space and stores only select statement)
Table is the preliminary storage object for storing data and information in RDBMS	View won’t contain a set of values in database
Table is collection of related data and it consists of rows and columns	View is virtual table and its content depends on the query
If we apply and dml command on related view, it will impact the view	If we apply any dml command on related table, it will impact the table
If drop the related view, it won’t impact the table	If we drop the related table, it will impact the view and view will become invalid
Table can have maximum of 1000 columns	View can have maximum of 999 columns
To see tables in our schema: Select * from user_tabs	To see views in our schema: Select * from user_views

6. Difference between Synonym and View

SYNONYM	VIEW
Synonym will hide the original name of the object. It is alias name of the object.	View is stored select statement
Synonym will be created on only one object	View can be created and any no. of objects
Synonym will be created on tables, views, functions, procedures…	Views can be created on tables, views, synonyms
To see synonyms in our schema: Select * from user_synonyms	To see views in our schema: Select * from user_views
If we drop the related table, then synonym will become invalid and if we recreate the table	If we drop the related table view will become invalid and if we recreate the table then also

7. a) What is group by clause?

• Group by clause is used to group row based on specific column.
• If want to write normal column along with aggregate function in select statement, then that normal column should come after group by clause in that select statement.

b) What is order by clause?

• Order by clause is used to arrange select statement output in ascending order or descending order.
• By default order by clause has ascending order. If want to arrange select statement output in descending order then after order by clause we need to write desc keyword.
• We can write any no of columns in order by clause but make sure that the column that is used to sort, that column will exist in the column_list of the select statement.

C) What is having clause?

• Having clause is used to apply conditions to grouped data. Having clause filters grouped data.
• Having conditions are written after group by clause. Having clause always follows group by clause.
• After having clause we must write aggregate functions only.

8. What is view and what are the advantages of views?

• View is stored select statement. View won’t store any data physically on it. View won’t occupy any space in the database.
• Views can be created on tables, views, synonyms.
• Any dml operation on the view will impact the original object.
• If we drop the related table, view will be invalid. And if we recreate the table even then also view will be in invalid state only. If we want that view then we need to recreate or recompile it.

Advantages of views:

• View will hide the complexity of the query.
• It supports and provides high security while sharing selected rows/information to other users.
• If we want to share data of more tables then we can do it simply by using as single view.
• When the frontend resource uses the same select statement multiple times and he needs some time for it to exist in the frontend. So, instead of that, we will create a view on that query.

9. Why sequence is required?

Sequence is required to generate unique and order wise values.Sequence consists of start with, increment by, minimum value, maximum value, cycle, cache. Advantages of sequence:

• No need to create any table for sequence.
• If the related table is dropped, it won’t impact the sequence. • Sequence gives unique and order wise values.
• Sequence won’t belong to any object. Disadvantages of sequence:
• We cannot rollback sequence (we cannot get the start value again once the sequence is started.
• Sequence doesn’t belong to any object.

1. To get the next value of sequence: Select seq_name.nextval from dual;
2. To get the current value of sequence: Select seq_name.curval from dual;

10. What is synonym & uses of synonym?

• Synonym is used to share information of an object of one user to other user. And both the users should be connected to same database.
• Synonyms can be created on tables, views, functions, procedures. One synonym can be used for only one object.
• Synonym hides the original name of the object.
• We can create synonym on another synonym.
• Synonym provides high security while sharing information to other users. Synonyms are of two types.

They are 1) Private synonyms: select * from user_synonyms;

2) Public synonyms: select * from all_synonyms;

If the related table is dropped then synonym will become invalid. And if the table is recreated then the synonym will be in valid state. No need to recompile or recreate it.

A synonym is nothing but alias name of the original object. Whatever changes are done on synonym that will impact on the original object and whatever changes are done on the original object that will impact on the synonym.