Home / Interview Questions / Database Interview Questions / PL/Sql Interview Questions Part 1

PL/Sql Interview Questions Part 1

 

1. What are Procedure, functions and Packages?

  • Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
  • Procedures do not return values while Functions return one Value.
  • Packages provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

 

 

2.What is a ROWID and Why does it needed?

ROWID is the physical address (location) of the row on the disk. This is the fastest way to access a row in a table.

 

3.How many types of SQL Statements are there in Oracle?

There are  6 types of SQL statements.

Data Definition Language (DDL): The DDL statements define and maintain objects and drop objects.

Data Manipulation Language (DML): The DML statements manipulate database data.

Transaction Control Statements: Manage change by DML.

Session Control: Used to control the properties of current session enabling and disabling roles.

System Control Statements: Change Properties of Oracle Instance.

Embedded SQL: Incorporate DDL, DML and T.C.S in Programming Language.

 

 

4.How many Integrity Rules are there and what are they?

There are Three Integrity Rules as follows:

Entity Integrity Rule: The Entity Integrity Rule enforces that the Primary key cannot be Null.

Foreign Key Integrity Rule: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.

Business Integrity Rules: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

 

 

5.How to implement the If statement in the Select Statement?

We can implement the if statement in the select statement by using the Decode statement.

e.g select DECODE (EMP_CAT,’1′,’First’,’2′,’Second’Null);

Here, the Null is the else statement where null is done .

 

 

6.How do you use the same LOV for 2 columns?

  • We can use the same LOV for 2 columns by passing the return values in global values and using the global values in the code.

 

 

7.What are snap shots and views?

  • Snapshots are mirror or replicas of tables.
  • Views are built using the columns from one or more tables.
  • The Single Table View can be updated but the view with multi table cannot be updated.

 

 

8.What is the difference between candidate key, unique key and primary key?

  • Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows.
  • Unique key is also useful for identifying the distinct rows in the table.

 

9.What is Row Chaining?

  • The data of a row in a table may not be able to fit the same data block.
  • Data for row is stored in a chain of data blocks.

 

 

10.What is the difference between deleting and truncating of tables?

  • Deleting a table will not remove the rows from the table but entry is there in the database dictionary and it can be retrieved.
  • While truncating a table deletes it completely and it cannot be retrieved.

 

11.What is the Difference between a post query and a pre query?

  • A post query will fire for every row that is fetched but the pre query will fire only once.

 

12.How to Delete the Duplicate rows in the table?

We can delete the duplicate rows in the table by using the Rowid

Example: DELETE FROM table_name a  Where rowid>(select min(rowid) from table_name b where a.table_no=b.table_no);

13.Can we disable database trigger? How?

Yes we can disable database triggers through following issuing statement

ALTER TABLE TABLE [DISABLE all_trigger ]

 

14.Is space acquired in blocks or extents?

  • Space is acquired in extents.

 

 

About Syed Saad Ali

As a certified and experienced Oracle Database Administrator with Almost 10 Years of Experience I am able to perform all levels of database maintenance and am fully qualified to do Oracle updates as well. Capitalizing on my interest in Business & IT both, I chose to navigate my career towards Enterprise Solutions, which has enabled me to excel in the combination of both. I have developed exceptional analytical skills that allow me to diagnose and take care of issues immediately. Extremely skilled in planning and execution of special projects in time- critical environments. Extensive knowledge in directing, coordinating and exercising concern authority for planning, organization, control and successful completions. Very skilled at solving customer relations issues by seeking first to understand and then to be understood. Effectively able to communicate with customers, vendors and management.

Check Also

Apps Technical Interview Questions Part 5

Leave a Reply