PLSQL Developer Interview Questions Part-2

1.What is a constraint? Can you explain each type of constraint?

Constraints mean rules or regulations.By using constraints we can restrict the table data.When we want to put any rule or restriction on column date then we can use constraints. We cannot see or modify the code of constraints, as they are encrypted.

Constraints are of 4 types.

a) Unique key constraint

b) Primary key constraint

c) Check key constraint

d) Foreign key constraint

Understanding Oracle Triggers – A Comprehensive Guide with Examples

Unique key constraint :

When we create Unique key constraint then automatically uk index will be created
Unique won’t allow duplicate values
Table can have any number of unique keys.
If we create unique constraint on more than one column of a table then it is called composite unique key constraint.

Primary key constraint :

When we create Primary key constraint then automatically unique index will be created.
Primary key constraint won’t allow null values and duplicate values.
Table can have only one primary key.
If you create primary key for more than one columns on table at a time then it is called composite primary key

Check constraint:

It is used to specify conditional restrictions.
It will accept both null and duplicate values.
It is user friendly constraint.
It won’t create a unique index automatically.

Foreign key constraint:

Used to specify the relation between two tables.
If you want to create a Foreign key constraint then reference table should be primary or unique.
Foreign key constraint will accept both null values and duplicate values.
Foreign key constraint won’t create index.

2.Difference between unique key and primary key?

UNIQUE	PRIMARY
Unique key allows null values	Primary key always won’t allows null values.
Table can have any no of unique keys	Table can have only one primary key
Unique index will be created automatically	unique index will be created automatically
Unique won’t allow duplicate values	Primary key won’t allow both duplicate and null values
To make unique key as primary key then we need to change the column having unique key to not allow null values	Primary key always wont allows null values.

3.Difference between Primary and foreign key?

PRIMARY	FOREIGN/REFERENCE
Primary key won’t allow duplicate and null values	Foreign key allows null and duplicate values
Table can have only one primary key	Table can have any number of foreign key
Index will be created automatically	Index won’t be created
Primary key can be created on any column	Foreign key can be created on any column but the referenced column must have primary or unique key
Primary key won’t support on delete cascade or on delete set null	Foreign key supports on delete cascade and on delete set null

4.Difference between max and greatest?

MAX	GREATEST
Max is aggregate function	Greatest is the arithmetic function
Max picks greatest value from the given column and returns a single row	Greatest picks the highest value from the given columns and returns a single column
Max is used to go down the column	Greatest is used across the rows
Max gives value in the single row	Greatest gives multiple rows in a single column from the given multiple columns of data
Max is row level function	Greatest is column level function

5. Difference between char and varchar2 ?

CHAR	VARCHAR
Char is fixed length data type	Varchar is variable length data type
Char allocates memory irrespective of the length of the string, which means if char(10) is there and if we have given a string of 4 characters, char will allocate 10 spaces even for that 4 chars	Varchar allocates memory according to length of string, which means if varchar(10) is there and we have given a string of 4 characters, varchar allocates only 4 spaces for the string remaining memory it won’t waste
Char wastes memory means	Varhcar, don’t waste memory
Char max size 2000	Varchar max size 4000 >>>11g Varchar max size 32767>>>12c

6. Difference between named block and unnamed block?

NAMED BLOCK	UN-NAMED BLOCK
Plsql blocks which are having header sections are known as named blocks (i.e we can create by using create or replace)	Blocks which are not having header section are called as anonymous blocks/un_named blocks (i.e we can create by using declare, begin, end)
Named blocks are stored in database	Un-named blocks are not stored in database
Named blocks are reusable	Un-named blocks are not reusable
Named blocks return a value (function must return a value)	Un-named blocks won’t return a value
We can use parameters inside named blocks	We cannot use parameters inside un-named blocks

7. What is on delete cascade and on delete set null?

On delete cascade: When we try to delete data from the parent table, the system prevents deletion if it finds a corresponding child record. To delete data from the parent table, we can use “on delete cascade.” When we apply “on delete cascade” to the foreign key, deleting data from the parent table will automatically delete the corresponding data from the child table.

On delete set null: When we try to delete data from the parent table, the system prevents deletion if it finds a corresponding child record. To delete data from the parent table, we can use “on delete set null.” When we apply “on delete set null” to the foreign key, deleting data from the parent table will automatically set the corresponding data in the child table to null.

8. Difference between an external table, database table and global table?

EXTERNAL TABLE	DATABASE TABLE	GLOBAL TEMPORAY TABLE
It won’t store data physically	It will store data physically	It will store data physically
It won’t require table space	It requires table space	It has default table space called temporary table space
We can’t create indexes, triggers, constraints on external table	We can create triggers, indexes, constraints on database tables	We can create triggers, indexes, constraints on GTT
If we export external table then we will get only table structure	If we export database table then we will get both structure and data of the table	If we export GTT then we will get only table structure
We can’t apply DML operations on external tables	We can apply DML operations on database tables	We can apply DML operations on GTT
If we want to modify data in external table then we need to modify the flat file	If we want to modify data in database table then we need to apply DML operations	If we want to modify data in GTT table then we need to apply DML operations

9.Explain about Global temporary table?

Global temporary table(GTT) store data physically. GTT has table space by default temporary table space. GTT is of two types: 1. Statement level GTT

2. Session level GTT

Statement level GTT: When we use ‘on commit delete rows’ at the end of the GTT creation statement, and we insert data into the GTT and then commit, the system deletes the data from the GTT.

Session level GTT: if we write ‘on commit preserve rows’ and when we insert data and commit, it will store data for that session only. This is known as ‘session level GTT’.

Master Oracle Interview Questions: Key Concepts & Examples for 2024

When we export data in GTT then we will get only table structure. If we want to change the data in the GTT then we need to apply DML operations.

10.Explain about parameter methods?

There are three types of parameters

1) In : through the program we cannot change the value. In parameter is mainly used for the end users (customers).

2) Out : when we need to display output on the screen after calculations, we will use the out parameter (means when the user expecting some value on the screen, then we will use the out parameter). Whatever value we want we can give throughout the program.

3) In out: for which parameter user is passing value, the same parameter will give the output. Throughout the program, we can give any value. Mainly used for collection types.