How to create legal entity registration in Oracle Fusion

The Legal Entity Registration is one of the task in the Legal Structure of the Financials configuration in Oracle Fusion. The Legal Entity Registration in Oracle Fusion is created by default with the same name as the Legal Entity.

Verify the Legal Entity Registration in Oracle Fusion

Perform the below steps to verify the Legal Entity Registration in Oracle Fusion

Navigator -> Setup and Maintenance
Select Manage Legal Entity Registration
Verify the scope for the Legal Entity

Create the Legal Entity Registration in Oracle Fusion

Perform the below steps to create the Legal Entity Registration in Oracle Fusion

Navigator -> Setup and Maintenance
Select Manage Legal Entity Registration
Select Task
Select Create
Enter the Jurisdiction details
Enter the Registered Addresses
Enter the Registered Name
Enter the Alternate Name (optional)
Enter the Registration Number (optional)
Enter the Place of Registration (optional)
Enter the Issuing Legal Authority (optional)
Enter the Issuing Legal Authority Address (optional)
Enter the Start Date (optional)
Enter the End Date (optional)
Click on Save and Close

Query to get organization structure details in Oracle Fusion General Ledger

The Oracle Fusion Application allows to create the Legal Entity, Business Unit to define the association and the balancing segments for the legal entity as per the enterprise structure of the organization

The blog provides the SQL Query to retrieve the Organization Structure details in Oracle Fusion with the association of legal entities, business unit , legal entity registration and general ledger mapping for the defined organization structure

Query to get organization structure details in Oracle Fusion General Ledger

SELECT
HR_ORGN_UNITS_TBL.NAME ORGANIZATION
, HR_ORGN_UNITS_TBL.ORGANIZATION_ID ORGANIZATION_ID
, XLE_REGN_TBL.REGISTERED_NAME
, XLE_REGN_TBL.ALTERNATE_REGISTERED_NAME
, XLE_REGN_TBL.REGISTRATION_NUMBER
, XLE_REGN_TBL.PLACE_OF_REGISTRATION
, XLE_REGN_TBL.EFFECTIVE_FROM
, XLE_REGN_TBL.EFFECTIVE_TO
, XLE_ENTITY_PROF_TBL.NAME "LEGAL_ENTITY_NAME"
, XLE_ENTITY_PROF_TBL.LEGAL_ENTITY_IDENTIFIER
, HR_OPER_UNITS_TBL.BUSINESS_GROUP_ID
, HR_OPER_UNITS_TBL.ORGANIZATION_ID "BU_ID"
, HR_OPER_UNITS_TBL.NAME "BU_NAME"
, HR_OPER_UNITS_TBL.DATE_FROM "BU_FROM_DATE"
, HR_OPER_UNITS_TBL.DATE_TO "BU_TO_DATE"
, HR_OPER_UNITS_TBL.SHORT_CODE "BU_SHORT_CODE"
, HR_OPER_UNITS_TBL.SET_OF_BOOKS_ID
, GL_LEDGERS_TBL.NAME LEDGER_NAME
, GL_LEDGERS_TBL.LEDGER_ID
FROM
XLE_ENTITY_PROFILES XLE_ENTITY_PROF_TBL
, XLE_REGISTRATIONS XLE_REGN_TBL
, HR_OPERATING_UNITS HR_OPER_UNITS_TBL
, HR_ORGANIZATION_UNITS HR_ORGN_UNITS_TBL
, GL_LEDGER_NORM_SEG_VALS GLNSV_TBL
, GL_LEDGERS GL_LEDGERS_TBL
WHERE
XLE_ENTITY_PROF_TBL.LEGAL_ENTITY_ID = XLE_REGN_TBL.SOURCE_ID
AND XLE_REGN_TBL.SOURCE_TABLE = 'XLE_ENTITY_PROFILES'
AND XLE_ENTITY_PROF_TBL.LEGAL_ENTITY_ID = HR_OPER_UNITS_TBL.DEFAULT_LEGAL_CONTEXT_ID(+)
AND HR_ORGN_UNITS_TBL.ORGANIZATION_ID = HR_OPER_UNITS_TBL.BUSINESS_GROUP_ID
AND GLNSV_TBL.LEGAL_ENTITY_ID = XLE_ENTITY_PROF_TBL.LEGAL_ENTITY_ID
AND GL_LEDGERS_TBL.LEDGER_ID = GLNSV_TBL.LEDGER_ID;

Assign Legal Entities and Balancing segments in Oracle Fusion

In Oracle Fusion application , once the Legal Entity is created , it has to be assigned and balancing segments are to be mapped to get them recorded as per the defined Enterprise Structure in the General Ledger

The blog provides the steps to perform the below actions

How to Assign Legal Entities in Oracle Fusion
How to Assign Balancing Segment Values to Legal Entities in Oracle Fusion

How to Assign Legal Entities ?

Before we proceed with the assignment of Legal Entities , please read the blog

How to Create Legal Entity in Oracle Fusion

Follow the below given steps

Go to Configurations
Select Financials -> General Ledger
Select Assign Legal Entities

4. On the Add Legal Entities Page, search the Legal Entity created

Oracle Fusion Add Legal Entities

5. Search for Legal Entity – OAH Pvt Ltd

6. Select the Legal Entity

Legal Entity	Legal Entity Identifier	Country
OAH Pvt Ltd	OAH_PVT_LTD	United States

7. Click Apply button

The Legal Entity will be shown on the Add Legal Entity Page but there is No balancing Segment (Company Value) defined for it.

Legal Entity	Legal Entity Identifier	Country	Company Values
OAH Pvt Ltd	OAH_PVT_LTD	United States	<No Value>

How to Assign Balancing Segment to Legal Entities ?

Follow the below given steps

Go to Configurations
Select Financials -> General Ledger
Select Assign Balancing Segment Values to Legal Entities
Select the Legal Entity OAH Pvt Ltd with No balancing segment defined
click on + icon to map the balancing segment

Assign Company Values

*Legal Entity OAH Pvt Ltd
*Company Value <Select the value from the dropdown based on the Enterprise Structure created for the country United States>
Enter the Description
Enter the Start Date
Enter the End Date <keep blank>
Click Save and Close

Create Legal Entity in Oracle Fusion

Any organization using the Oracle Fusion Application has to setup the Legal Entity as part of the Enterprise Structure. The blog provides the steps to Create Legal Entity in Oracle Fusion Application

What is a Legal Entity ?

A legal entity is a recognized party with rights and responsibilities given by legislation. For example

right to own property
right to repay debt
right to trade
account themselves to regulators, taxation authorities

A legal entity is responsible for :

facilitating local compliance
minimizing enterprise tax liability
isolating the business risk by creating separate business area based on legal entities

In the Configured Enterprise Structure for the Oracle Fusion Application , the contracting party for any transactions is always the legal entity. Lets consider that your enterprise deals from USA and maintains transactions for all states in USA. Thus , each state can be considered as a separate legal entity to record transactions.

Legal Structure in Oracle Fusion Application

The Oracle Fusion Application provides the below given tasks to be created as part of Legal structure in the Financials configuration

Manage Legal Addresses
Manage Legal Entity
Manage Legal Entity Registration
Manage Legal Reporting Unit
Manage Legal Reporting Unit Registration

Oracle Cloud Manage Legal Addresses

What all should be considered while creating legal entities ?

Legal Entities can be created based on the transactions performed. For example , Create a Supplier Legal Entity if you purchase goods from Supplier , Create a Customer Legal Entity if you sell goods to customers.

In case of Oracle Fusion HCM Applications, create a Employer Legal Entity

The below given are the common consideration when creating legal entities

Legal Entity for recording transactions
Legal Entity mapping with Business Units
Legal Entity relationship to Divisions
Legal Entity relationship to Ledgers
Legal Entity relationship to Balancing Segments
Legal Entity relationship to Intercompany Transactions
Legal Entity relationship to Worker Assignments
Legal Entity relationship to Legal Employer
Legal Entity relationship to Payroll

Steps to Create Legal Entity

Perform the below steps to create legal entity in Oracle Fusion Application

Select Navigator -> Setup and Maintenance
Select Configuration Setup as ‘Financials’
Select Legal Structure
Click on Manage Legal Addresses

5. In the Manage Legal Address Page, Click on + icon to search the country as ‘ United States’

Legal Addresses select country

Enter Address Line 1: OAH Pvt Ltd

Enter Address Line 2: Brookside Ct

Enter City: Columbus

Enter State : Indiana

Enter Postal Code: 47201

6. Click OK

The Country has been added as the Legal Address

7. Click on Save and Close

8. Search for ‘Manage Legal Entity’ Task

Oracle Fusion Legal Structure Listing

* Country Untied States

Payroll Statutory Unit (checkbox)

*Name OAH Pvt Ltd

Legal Employer (checkbox)

*Legal Entity Identifier OAH_PVT_LTD

Start Date 9/01/21

End Date

Registration Information

Identifying Jurisdiction US Company Law

* Permanent Account Number MOH12345

Legal Address OAH Pvt Ltd Columbus, Indiana

* Legal Reporting Unit Reg. Number SHAR9876

Place of Registration Indianapolis

Click Save and Close

The Legal Entity is created successfully.

How to set the scope for the Legal Entity ?

Perform the below given steps to set the scope for the created legal entity

Select Navigator > Setup and Maintenance
Select Define Legal Entries > Manage Legal Entity
Select to set scope
Select the Manage Legal Entity
Select -> Add
Click Apply and Go to Task.
Select the created Legal Entity
Click Save and Close

The scope for the task list is defined to the selected legal entity
Save and Close

How to Create Implementation Project in Oracle Fusion

The blog provides the basic steps for creating the implementation project in Oracle Fusion.

Read the below given blog before you proceed with the Implementation Project creation in Oracle Fusion Application

Roles required for creating the Implementation Project in Oracle Fusion

Application Implementation Consultant
Application Implementation Administrator
Application Implementation Manager

Steps required to Create Implementation Project in Oracle Fusion

The below given steps are to be performed while creating Implementation Project in Oracle Fusion

Configure Offerings
Create Implementation Project
Select Offerings
Save Implementation Project
Perform Tasks

Configure Offerings in Oracle Fusion

Offerings in Oracle Cloud / Fusion Applications are the Functional Modules provided to implement the business specific needs . For Example Inventory, Order Management , Procurement, Financials, etc.

Create Implementation Project in Oracle Fusion

Please note that the user who has to Create and Manage Implementation Project should have the Privilege for ASM_MANAGE_IMPLEMENTATION_PROJECT_PRIV

The below steps need to be performed

Click Navigator > Setup and Maintenance
Select Manage Implementation Projects from the task panel
Select Actions -> Create
Enter Name for the Implementation Project
Enter Description (Optional) for the Implementation Project
Assign Implementation Project to the user (optional)
Assign a Start Date ( Optional)
Click Next and Select the Offerings

Roles required for Offerings

Please note that the user who has to Create and Manage Offerings should have the Privilege for ASM_CONFIGURE_OFFERING_PRIV

To Review the Offerings, user should have the Privilege for ASM_REVIEW_APPLICATIONS_OFFERINGS_PRIV

Click Navigator -> My Enterprise -> Offerings

Select Offering ( For Example – Financials, HRM, Order Management. General Ledger) from the Configuration List
Click Opt In Features button
The Opt In Page display the list of Parent Offerings as well as the Functional Hierarchy for the Parent offering
Click Features to enable the Features at the Offerings Level
Click on Save to save the changes for the Offerings

The below given is an example for adding Financials as the Offering.

Offering	Enable	Features
Financials
Supplier Invoice Processing
Expenses
Fixed Assets
Customer Invoice Processing
Collections
Revenue Management
Intercompany
Budget Control and Encumbrance Accounting

To add multiple Offering, Perform the same steps by selecting the required Offerings from the Configurations and select check box ‘Enable’

Purchase Requisition BPM Approval Workflow Tables in Oracle Fusion

The Oracle Fusion allows to approve the Purchase Requisition. The Purchase Requisition is integrated using BPM Approval Workflow in Oracle Fusion.

The blog provides the BPM Approval Workflow table details used in Oracle Fusion

Purchase Requisition BPM Approval Workflow Tables in Oracle Fusion

POR_AMX_RULES
POR_AMX_PARTICIPANTS
POR_AMX_DIMENSIONS
POR_AMX_TASKS
POR_AMX_STAGES
POR_AMX_ACTIONS
POR_AMX_CONDITIONS

The below given SQL Query retrieves the Purchase Requisition BPM Approval data

SELECT POR_AMX_RULES_TBL.rule_id,
       POR_AMX_RULES_TBL.rule_number,
       POR_AMX_RULES_TBL.rule_name,
       POR_AMX_RULES_TBL.DISPLAY_RULE_NAME,
       POR_AMX_RULES_TBL.description,
       POR_AMX_RULES_TBL.CONDITIONS_STRING,
       POR_AMX_RULES_TBL.PRIORITY,
       POR_AMX_RULES_TBL.active_flag,
       POR_AMX_TASKS_TBL.LOOKUP_CODE pat_LOOKUP_CODE,
       POR_AMX_PARTICIPANTS_TBL.PARTICIPANT_KEY,
       POR_AMX_ACTIONS_TBL.ACTION_CODE,
       POR_AMX_ACTIONS_TBL.APPROVAL_GROUP_NAME,
       POR_AMX_CONDITIONS_TBL.condition_number,
       POR_AMX_CONDITIONS_TBL.attribute_key cond_key,
       POR_AMX_CONDITIONS_TBL.dimension_key,
       POR_AMX_CONDITIONS_TBL.operator,
       POR_AMX_CONDITIONS_TBL.condition_string
FROM   POR_AMX_RULES POR_AMX_RULES_TBL,
       POR_AMX_PARTICIPANTS POR_AMX_PARTICIPANTS_TBL,
       POR_AMX_DIMENSIONS   POR_AMX_DIMENSIONS_TBL,
       POR_AMX_TASKS POR_AMX_TASKS_TBL,
       (select RULE_NAME max_rul_name, max(OBJECT_VERSION_NUMBER) max_obj 
        from POR_AMX_RULES 
        group by RULE_NAME) rul_max,
       POR_AMX_STAGES   PAS,
       POR_AMX_ACTIONS POR_AMX_ACTIONS_TBL,
       POR_AMX_CONDITIONS POR_AMX_CONDITIONS_TBL
WHERE  POR_AMX_RULES_TBL.active_flag = 'Y'
   and POR_AMX_RULES_TBL.PARTICIPANT_ID = POR_AMX_PARTICIPANTS_TBL.PARTICIPANT_ID
   and POR_AMX_RULES_TBL.task_id = POR_AMX_PARTICIPANTS_TBL.task_id
   and pas.STAGE_ID = POR_AMX_PARTICIPANTS_TBL.STAGE_ID
   and pas.task_id = POR_AMX_PARTICIPANTS_TBL.task_id
   and pas.stage_id = POR_AMX_PARTICIPANTS_TBL.stage_id
   and POR_AMX_TASKS_TBL.task_id = POR_AMX_DIMENSIONS_TBL.task_id
   and rul_max.max_obj = POR_AMX_RULES_TBL.OBJECT_VERSION_NUMBER
   and rul_max.max_rul_name = POR_AMX_RULES_TBL.rule_name
   and POR_AMX_ACTIONS_TBL.rule_id = POR_AMX_RULES_TBL.rule_id
   and POR_AMX_ACTIONS_TBL.task_id = POR_AMX_RULES_TBL.task_id
   and POR_AMX_CONDITIONS_TBL.rule_id = POR_AMX_ACTIONS_TBL.rule_id
   and POR_AMX_CONDITIONS_TBL.task_id = POR_AMX_ACTIONS_TBL.task_id
   and POR_AMX_TASKS_TBL.task_key = 'ReqApproval'
   and POR_AMX_DIMENSIONS_TBL.TABLE_NAME  = 'POR_REQUISITION_HEADERS_ALL';

Query to get Oracle Fusion Payables Cloud approver comments

In Oracle Fusion Payable Approval Process, the approver can add the comments. The blog provides the SQL Query to retrieve the Oracle Fusion Payables Clod Approver Comments table details

SELECT  NOTE_ID, NOTE_TXT, SOURCE_OBJECT_CODE, 
SOURCE_OBJECT_UID,CREATOR_PARTY_ID,CREATED_BY,
CREATION_DATE, NOTE_TYPE_CODE, NOTE_NUMBER 
FROM ZMM_NOTES 
WHERE NOTE_TYPE_CODE LIKE 'AP_APPROVAL'
AND SOURCE_OBJECT_UID IN
(SELECT APPROVAL_HISTORY_ID
FROM AP_INV_APRVL_HIST_ALL
WHERE INVOICE_ID IN
(SELECT INVOICE_ID FROM AP_INVOICES_ALL
WHERE INVOICE_NUM = :PayableInvoiceNum))
ORDER BY LAST_UPDATE_DATE DESC;

How to create implementation user in Oracle Fusion

The implementation Project in Oracle Fusion can be created by the Implementation User. The blog provides the steps for creating the Implementation User in Oracle Fusion.

Read the Blog on Functional Setup Manager in Oracle Fusion

The Implementation User should be assigned with the below given roles:

Application Implementation Consultant Role
IT Security Manager Role
Employee Role

The below given are the steps required for Implementation User in Oracle Fusion

Log into Oracle Fusion Application with the Default Implementation User provided by the Oracle Product
Go to Navigator ( Left side 3 lines on the top)
Select Security Console
Select User Tab from the Left Panel
Click on ‘Add User Account’ button
Enter the below given mandatory fields

First Name
Last Name
Email (Optional)
User Name
Password
Confirm Password
External Identifier ( Optional)

7. Click on ‘Save and Close’

Assign Role to the New User

The below given are the steps for assigning roles to the newly created user

8. Click on ‘Add Role’ button given below on the ‘Add User Account’ Screen after saving the user details.

9. The Add Role Membership Popup screen opens.

10. Search for the Role – ‘Application Implementation Consultant’

( Code – ASM_APPLICATION_IMPLEMENTATION_CONSULTANT_JOB)

11. Click on ‘Add Role Membership’

12. Similarly Search for the Role – ‘IT Security Manager’

(Code – FND_IT_SECURITY_MANAGER_JOB)

13. Click on ‘Add Role Membership’

14. Similarly Search for the Role – ‘Employee’

(Code – PER_EMPLOYEE_ABSTRACT)

15. Click on ‘Add Role Membership’

Other related roles required to create the implementation project in Oracle Fusion

Application Implementation Administrator
Application Implementation Manager

The below given table depicts the privilege’s provided by the roles assigned to the Implementation User

User Role	Privileges Proved by the Role
Application Implementation Consultant	– Allows to create and manage Implementation Project – Allows to Copy Configurations – Allows Configuration Package to another instance – Allows to Import / Export setup data – Allows to manage custom setup data
IT Security Manager	– Allows to Create new user – Allows to assign roles to user
Employee	– Allows access to Report and Analytics -Allows access to Scheduled Processes

Functional Setup Manager in Oracle fusion

Oracle Fusion application is a combination of different product solutions acquired by Oracle ( For example PeopleSoft, Siebel, JD Edwards ) in last few years and plugged -in those solutions as per Oracle Business requirements for ERP Applications to provide a common platform to fulfill all client needs.

What is Functional Setup Manager in Oracle Fusion ?

Oracle Fusion Functional Setup Manager (FSM) allows to manage the Oracle Cloud Applications by providing the self-service administration to the Functional administrators by enabling the implementation and maintenance of Oracle Cloud Applications. The FSM provides the user interface through which Functional Admin can perform all the required tasks for the Functional modules required to be installed / configured in Oracle Cloud Applications with ease of deploying setup /config data to another instances.

Benefits of Functional Setup Manager in Oracle Fusion ?

Provides Application Setup / Configuration Interfaces
Provides Offerings to select for the best-fit business requirements
Provides task lists based on selected offering to setup end to end business requirements
Provides in-built Analytical / Reporting capabilities to understand market trends
Provides the ability to setup /config once and Import /Export to other instances
Minimizes the deployment time between instances

Points to Consider for Oracle Fusion Setup Manager ?

The below given are the basic steps for the Oracle Fusion Setup Manager which can be considered as pre-requisite before we proceed with the Module Level Setup

How to Navigate to Setup Manager in Oracle Fusion ?

The below are the steps to navigate to Setup Manager in Oracle Fusion
1. Log into Oracle Fusion Application
2. Ensure that the logged in user has the Application Implementation Consultant Role and should be different than the default user having Application Implementation Consultant Role
3. Select Navigator
4. Select Tools -> Setup and Maintenance

What do you mean by implementation users and how are they different from other users ?

The implementation user performs the below tasks / activities :

Managing implementation of Fusion Application Offerings
Managing Fusion Application Setup
Managing Fusion Applications Users and Security

What are offerings in Oracle Fusion Application ?

Offerings in Oracle Fusion Application are considered to be the Functional Modules comprising of Business requirements which can be implemented on its own. Offerings provides Configurable list of all required setup tasks to enable specific business functionality. General Ledger , Procurement are examples of offerings

What are Options and Features in Oracle Fusion Application ?

Options are next level grouping of business functionality which are provided as Optional but can be implemented if required as per business need

Payables is an example for Options in the Procurement Offering

Features are the low level grouping of business functionality which provides additional features to be implemented for the specific offering.

What is an Implementation Project in Oracle Fusion Application ?

Implementation Project can be created by the Implementation User having ‘Application Implementation Consultant Role’ . The implementation Project includes the listing of All Offerings which needs to be configured as part of Implementation covering the overall scope of the business requirements for the user.

What is Scope and Task List in Oracle Fusion Application ?

Task List depicts the sequential list of setup required to be implemented in a particular offering.

Scope sets the context for the Task List and is used to Export and Import configurations

How the offerings can be provisioned in Oracle Fusion Application ?

The offering provides the list of task which can be selected from the Offering. If an implementer selects ENABLE FOR IMPLEMENTATION (checkbox) of an Offering or Option, then it can be provisioned / installed

Is it possible to update the tasks in the existing Implementation Project in Oracle Fusion Application ?

Yes, the Oracle Fusion allows to update / add the task list which will be effective once updated

What are the roles required to create the implementation project ?

The below given are the roles which are required to create the implementation project

IT Security Manager
Application Implementation Administrator
Application Implementation Consultant
Application Implementation Manager
Application Diagnostics Administrator
Application Diagnostics Advanced User
Application Diagnostics Regular User
Application Diagnostics Viewer
Administrators
Application Administrator
Application Developer

XMLHttpRequest Property	Description
onReadyStateChange	This is invoked whenever readystate attribute changes
readyState	0- request is not generated 1- connection with the server is made 2- the server receives request 3- request is processing 4- response is received, and request is finished.
responseText	returns the response in Text File Format
responseXML	returns the response in XML Format
status	returns the request status number
statusText	returns the status details

XMLHttpRequest Method	Description
void open (method, URL)	This method Opens the request specifying GET /POST method for the URL
void open (method, URL, async)	This method Opens the request specifying GET /POST method for the URL for with additional parameter for asynchronous requests or not
void open (method, URL, async, username, password)	This method Opens the request specifying GET /POST method for the URL for with additional parameter for asynchronous requests or not and includes username and password
void send()	This method is used to send the request
void send (string)	This method is used to send the request with POST method
setRequestHeader(header,value)	This method is used to send the request with header values
abort()	This method is used to abort the request
getAllResponseHeaders()	This method returns the response header in the form of a string
getResponseHeader()	This method returns the specific response header value

JavaScript	AJAX
JavaScript is a object based scripting language	AJAX uses XMLHttpRequest object for communicating with server and uses JavaScript, XML, HTML and CSS
JavaScript send and received data in synchronous way	AJAX sends the request and does not wait for the response supporting asynchronous transactions
JavaScript does not support partial page update	AJAX supports partial page update

Synchronous AJAX Request	Asynchronous AJAX Request
The request sent to the server waits for the response to complete the transaction
Blocks the JavaScript Engine until the request is completed	Does not block the JavaScript Engine
The page refresh happened whenever the request is submitted	Partial page is updated with the received response and does not require page refresh
User need to wait until the request is processed	User can perform other tasks while the request is processed

SPA	AJAX
SPA is single page application runs within the single web page	AJAX is used to communicate with server for processing the request
SPA is used to develop a standalone application	AJAX is used to perform the back end communication for the received user request
SPA being a single page application does not communicate with different web pages	AJAX is used to transfer data among different web pages as per user action

Top 50 SQL Interview Questions and Answers

The blog provides the commonly asked Oracle RDBMS SQL Interview questions and answers for the interview preparation. SQL is Structured Query Language used by the Oracle Database Developer , Oracle DBA Administrators. The below given SQL Interview questions and answers can be helpful in preparing for Database Job Interviews for MNC IT Companies like Google, Amazon, Microsoft, Infosys , Wipro , TCS, etc.

The blog contains the basic as well as advanced questions on SQL which could be helpful for technical interviews for both freshers as well as experienced professionals

SQL Interview Questions and Answers for beginners and experienced professionals

What is SQL?

SQL stands for Structured Query Language used in Relational Databases like Oracle and helps in performing the data operation ( Create , Update, Delete) on the Oracle tables, Views, Indexes, etc.
SQL (Structured Query Language) was initially developed in 1974 for the relational databases.
SQL became the standard of the American National Standards Institute (ANSI) in 1986 and ISO (International Organization for Standardization) in 1987

What is DBMS?

DBMS stands for Database Management System which helps in managing the Creation , maintenance and use of the database.

What is RDBMS?

RDBMS stands for Relational Database Management System which stores the data into the collection of tables. As multiple times can be used to retrieve user specific information and each table is linked to another with the use of primary key, foreign key , unique key and makes the relation among each other for the data linkage.

What are the components of the DBMS?

The Database Management System (DBMS) broadly uses the below components:
Software
Data
Procedures
Database Languages
Query Processor
Database Manager
Database Engine
Reporting

What are the different types of DBMS?

The below given are the different types of Database Management System (DBMS)
Relational databases
NoSQL Databases
Document databases
Graph databases
ER model databases
Network databases
Hierarchical databases
Object-oriented databases

What is advantages of using SQL in relational databases?

The advantages of SQL are given below
SQL helps in creating / dropping relational database
SQL helps in performing operations against relational database
SQL helps in inserting data into relational database table
SQL helps in data retrieval from relational database table
SQL helps in updating data into relational database table
SQL helps in deleting data into relational database
SQL helps in creating / dropping tables into relational database
SQL helps in creating / dropping views into relational database
SQL helps in creating / dropping indexes into relational database table

Does SQL provides programming language features ?

SQL is not a programming features and only support DDL and DML Statement like CREATE , INSERT , UPDATE, DELETE and consider as a Query Language which allows to perform operation on relational database.
PL/SQL is required if conditional branching , loop statements are to be used to write complex programming logic.

What does SQL contains ?

The SQL is comprised of the below given components:
Data Definition Language (DDL) : used to define the data structure and provide SQL commands like CREATE, ALTER, DROP, etc.
Data Manipulation Language (DML): allows to update the database table data by performing operation like SELECT, UPDATE, INSERT, DELETE
Data Control Language (DCL) : manages the data access in the database with SQL commands like REVOKE , GRANT
Transaction Control Language( TCL) : manages the transaction processing in the database with SQL commands like COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT

What is a Primary Key in relational database?

A primary key must enforce the below points
A primary key could be a single field or a combination of fields which should uniquely define a table record
A table can have only one primary key
A primary key cannot have NULL values

CREATE Table Primary Key example:
CREATE TABLE student_table
( student_id int NOT NULL,
student_name char(50) NOT NULL,
course char(50),
CONSTRAINT student_table_pk PRIMARY KEY (student_id)
);

Give example of ALTER command to add Primary Key?

ALTER TABLE student_table
ADD CONSTRAINT student_table_pk
PRIMARY KEY (student_id);

How to DROP a primary key from the table ?

ALTER TABLE student_table
DROP PRIMARY KEY;

What is a Unique Key ?

A unique key must enforce the below points
A unique key could be a single column or combination of columns in a table
A unique key does not allow duplicate values
A table can contain multiple unique key columns but only 1 primary key

CREATE Table Unique Key example:
CREATE TABLE student_table
( student_id int NOT NULL,
student_name char(50) NOT NULL,
course char(50),
email varchar(100) UNIQUE ,
CONSTRAINT student_table_pk PRIMARY KEY (student_id)
);

What is a Foreign Key ?

A Foreign Key also known as Reference Key is used to link one or more tables.
A Foreign Key used in the table must be an existing primary key of another table.
A Foreign Key maintains the referential integrity by identifying each row of another table uniquely

Explain the difference between Primary Key and Unique Key ?

The below given are the main differences between a primary key and a unique key:

A primary key could be a single field or a combination of fields which should uniquely define a table record whereas a unique key prevents two rows from having duplicate entries in a column

A table can have only one primary key but can have multiple unique keys per table

A primary key does not support NULL values, whereas a unique key can have NULL values but only one NULL is allowed in a table

A primary key should be unique, but a unique key cannot necessarily be the primary key

A primary key by default is a clustered index (sequential data) whereas a unique key is a non-clustered index

A primary key implements entity integrity, whereas a unique key enforces unique data

Explain the difference between Primary Key and Foreign Key ?

The below given are the main differences between a primary key and the foreign key:

A primary key could be a single field or a combination of fields which should uniquely define a table record whereas the foreign key is used to link one or more tables

A primary key identifies a record in the relational database table whereas the foreign key identifies the primary key of another table

A table can have only one primary key but can have multiple foreign keys per table

A primary key does not support NULL values, whereas the foreign key can have NULL values

What is an Index in SQL ?

An INDEX is:
– a pointer to the data into the table which helps database search engine to retrieve the data at faster speed.
– technique of minimizing the query’s cost
– always UNIQUE for the table data
– creation of Index or dropping an Index does not impact the table data
– works faster when SELECT and WHERE clause are used in SQL statements compared to
INSERT and UPDATE SQL statements

Syntax for Create Index Command :
CREATE INDEX index_name ON <table_name>;

What are the different types of indexes in SQL?

The below given are the different types of indexes in SQL
– Unique Index
– Clustered Index
– Non-Clustered Index
– Bit-Map Index
– Normal Index
– Composite Index
– B-Tree Index
– Function-Based Index

What is a Unique Index in SQL?

A unique index ensure that the index key column does not contain DUPLICATE values
A unique index could be a combination of one or more table columns
A unique index with multiple columns should have Unique combination of value for the columns

Example:
CREATE TABLE student_table
( student_id int NOT NULL,
student_name char(50) NOT NULL,
course char(50),
email char(50)
);
CREATE UNIQUE INDEX student_id_indx ON student_table (student_id);

What is a clustered Index in SQL?

– Clustered index sort and stores the data into table or vies based on the key values.
– A table supports only one clustered index
– Clustered index could have one or more table columns
– A table with clustered index is also known as clustered table
– A table with no clustered index, stores the data in an unordered structure called a heap

What are the advantages of clustered Index in SQL?

The advantages of a Clustered index are:
– A clustered index is useful for range or group queries to get max, min, and count values
– A clustered index uses location mechanism for finding an index entry for data search
– A clustered index is useful to maximize the cache hits and minimizes the page transfer

What is a non-clustered Index in SQL?

– The indexes created on the table other than the Clustered Indexes are called as Non-
Clustered Indexes.
– The Non-Clustered index contains the non-clustered index key values and each key
value entry has a pointer to the data row that contains the key value
– The Non -Clustered indexes are created when SQL query requires multiple join condition
for the data retrieval
– The Non- Clustered indexes can be unique

What is a View in SQL?

A View is:
– a database object that has no value of its own
– always have a unique name
– represents the sub set of columns from one or more tables
– a virtual table which depicts data at run-time
– reflects the change in data if the underlying table data is changed

Syntax for Creating View in SQL:

CREATE or REPLACE VIEW view_name
AS SELECT column_name(s)
FROM table_name
WHERE condition

Explain the OFFSET and FETCH clause in SQL?

– The OFFSET clause allows to define the starting point to return rows from a result set
– The FETCH clause allows to return a set of number of rows and should be used in
conjunction with OFFSET
– The FETCH Clause requires the mandate use of OFFSET clause
– The FETCH Clause CANNOT be used with the ORDER BY clause
– The OFFSET/FETCH row count expression can be only be any arithmetic, constant, or
parameter expression and should return positive integer value
– ORDER BY is mandatory to be used with OFFSET and FETCH clause
– TOP cannot be combined with OFFSET and FETCH
– OFFSET value CANNOT be negative, else return error

What is the difference between ORDER BY and GROUP BY in SQL ?

ORDER BY CLAUSE :
The Order By Clause is used to sort the table data in ascending or descending order.
Syntax for Order by Clause:
SELECT column1, column2, column3, column4, …..columnN
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

GROUP BY CLAUSE :
The Group By Clause is used to arrange the table columns into group.
The Group by Clause is followed by the WHERE Clause
The Group by Clause precedes the Order by Clause
Syntax for Group by Clause:
SELECT column1, column2, column3, column4, columnN
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

What is the difference between HAVING and GROUP BY clause in SQL ?

HAVING CLAUSE :
The Having Clause is used with Aggregate functions followed by the Group By clause in order to find rows with certain conditions.
When Having clause is used then WHERE Clause is not required.

Example for Having Clause:
SELECT COUNT (SALARY) AS Salary_Count
FROM EMPLOYEES
GROUP BY SALARY
HAVING COUNT(SALARY) > 50000;

GROUP BY CLAUSE :
The Group By Clause is used to arrange the table columns into group.
The Group by Clause is followed by the WHERE Clause
The Group by Clause precedes the Order by Clause
Syntax for Group by Clause:
SELECT COUNT (SALARY) AS Salary_Count
FROM EMPLOYEES
GROUP BY SALARY

What is Online Transaction Processing (OLTP)?

What are the differences between OLTP and OLAP?

Online Transaction Processing (OLTP) is a software system providing transaction-oriented applications which can be used for data entry, data retrieval and data processing.
OLTP allows to perform real -time transactions and also ensures consistency and atomicity for the data transactions.
Examples: Order Entry, Retail Sales, Financial Transaction Systems

Online Analytical Processing (OLAP) is a software system primarily used for data mining or maintaining aggregated, historical data, usually in multi-dimensional schemas.
OLAP generated reports which allows Business Analyst to conclude on the business trends and creating business strategies for the product growth

What are Aggregate Functions and its types in SQL ?

Aggregate functions allows to perform operations on the table column values and returns the single value as result.

The below given are the types of Aggregate functions
– AVG(): returns the AVERAGE for all the values from the table column
– COUNT() : returns the COUNT for all the values from the table column
– FIRST() : returns the FIRST value from the table column
– LAST() : returns the LAST value from the table column
– MAX() : returns the MAXIMUM value from the table column
– MIN() : returns the MINIMUM value from the table column
– SUM() : returns the SUM of all the table column values

What are Scalar Functions and its types in SQL ?

Scalar functions allows to perform operations based on user input and returns the single value as result

The below given are the types of Aggregate functions
– UCASE() : returns the UPPERCASE Text for the table column
– LCASE() : returns the LOWERCASE Text for the table column
– LEN() : returns the LENGTH for the table column
– ROUND() : returns the ROUND value for the table column
– NOW() : returns the Current Time
– FORMAT() : returns the FORMATTED value for the table column

What are CONSTRAINTS in SQL ?

Constraints in SQL are provided to restrict the table data based on specific rule to maintain the accuracy and data integrity of the rows in the table.

Constraints can be classified as:
Column level constraints: Limits the column data only
Table level constraints: Limits the complete table data

Name few SQL constraints ?

The below given are the commonly used Constraints in SQL :
– DEFAULT: Automatically assigns a default value for the table column if no value is
specified
– UNIQUE : Ensures unique values to be inserted into the table column.
– PRIMARY KEY: Uniquely identifies each record in a table.
– FOREIGN KEY : Ensures referential integrity for a record in another table.
– INDEX : Indexes a field providing faster retrieval of records.
– NOT NULL: Restricts NULL value for the table column
– CHECK : Verifies that all values in a field satisfy a condition

How to create an empty table from an existing table?

Select * into Employee_temp
from Employee
where 1=2

How to select UNIQUE records from a table?

Select DISTINCT EMP.ID, EMP.Name
from EMP

Which SQL comamnd can be used to get first 5 characters of the Employee Name?

Select SUBSTRING(EMP.NAME,1,5) as EmployeeName
from EMP

What is the difference between TRUNCATE , DROP and DELETE commands in SQL ?

– The TRUNCATE SQL command allows to eliminate all the rows from the table. Once TRUNCATE command is executed, then eliminated data CANNOT be rolled back.
– The DROP SQL command allows to DROP the table itself from the database and eliminated data CANNOT be rolled back
– The DELETE SQL command allows to delete all rows or specific rows based on WHERE clause from the database table. The Deleted data CAN be rolled back

What is the difference between UNION, MINUS and INTERSECT commands in SQL?

The UNION, MINUS and INTERSECT SQL commands allows to combine the result of separate SQL queries to provide a common table data result set

– The UNION SQL command allows to combine the results of two tables by eliminating the duplicate rows from the tables
– The MINUS SQL command allows to return rows based on matched records that are found in the first query and not in the second query and thus eliminates rows that are found only in the second query
– The INTERSECT SQL command allows to return rows that are matched in both queries

What are Set Operators in SQL ?

The Set Operators in SQL are used to perform operation on table data based on specific conditions
There are 4 types of Set Operators in SQL:
– UNION
– UNION ALL
-INTERSECT
-MINUS

What are Operators in SQL ?

Explain Arithmetic Operators in SQL ?

Arithmetic Operators in SQL are used to perform mathematical operation on the table columns.
The below given are the types of Arithmetic Operators:
– Addition (+) : allows to ADD the table column data values
– Subtraction (-) : allows to SUBTRACT the table column data values
– Division (/) : allows to calculate the DIVISION of the table column data values
– Multiplication : allows to MULTILY the table column data values
– Modulus (%) : allows to calculate the MODULUS of the table column data values

Explain Wildcard Operators in SQL ?

Wildcard Operator (LIKE) is used to perform the operation on the table column by providing the range of characters or partial text to get the related data from the table

Example:

SELECT * FROM Student WHERE FIRST_NAME LIKE ‘A%‘;

SELECT * FROM Student WHERE NAME LIKE ‘ANU_‘;

What is a SEQUENCE in SQL ?

A SEQUENCE in SQL is an object that allows to generate a number sequence for the table column value

Example for creating sequence in SQL :
CREATE SEQUENCE student_seq
MINVALUE 1
MAXVALUE 9999999
START WITH 1
INCREMENT BY 1
CACHE 20;

Example for Dropping Sequence in SQL:
DROP SEQUENCE student_seq

what does CACHE and NOCACHE options mean in SEQUENCE ?

WHEN CACHE Option is provided, it specifies how many sequence values to be stored in memory for access
When NOCACHE is provided, then no sequence value is stored in memory and is retrieved by calling the Sequence directly

Explain Transaction Control Language(TCL) in SQL ?

The Transaction Control Language (TCL) are used to manage the transaction processing in the relational database.
The types of TCL commands are :
– COMMIT : The changes done by the DML commands like INSERT, UPDATE and DELETE and not permanent and transaction can be rolled back in the current session and thus COMMIT saves the transaction data permanently and data CANNOT be rolled back
– ROLLBACK : allows the transaction to be rolled back in the current session until COMMIT is not performed
– SAVEPOINT: allows to save the transaction with a save point ( depicts till what point last committed transaction is saved) so that if required transaction can be Rolled Back to the save point

What is AUTO INCREMENT in SQL?

Auto increment keyword allows the user to generate a unique number for a specific column whenever a new record is inserted into the table.

AUTO INCREMENT keyword is used in Oracle whereas IDENTITY keyword is used in SQL SERVER for generating auto incremented number.

What is a subquery in SQL?

A subquery is a query within another SQL query
The Outer query is called as MAIN query and INNER query is called subquery
A subquery is always executed first.
A subquery result becomes the input to the Outer query

The below given are the types of subquery:
Correlated subquery:
A correlated subquery CANNOT be executed independently and is referred as INNER Query in the MAIN Query
A correlated subquery is allowed to refer the table column FROM the list of the MAIN query

Non-Correlated subquery:
A Non-Correlated subquery CAN be executed independently
A Non-Correlated subquery result is substituted in the MAIN query

What is the difference between SQL and MySQL?

SQL (Structured Query Language) is a standard language for retrieving and manipulating structured databases whereas MySQL is a Relational Database Management System (RDBMS) like SQL Server, Oracle or IBM DB2 for managing and maintaining SQL databases

What is the difference between UNION and UNION ALL in SQL ?

UNION: The UNION command allows to fetch results from multiple tables eliminating the duplicate rows from the result

SELECT customer_Id FROM Customers
UNION
SELECT customer_Id FROM Orders;

UNION ALL: The UNION command allows to fetch results from multiple tables including the duplicate rows in the result

SELECT customer_Id FROM Customers
UNION ALL
SELECT customer_Id FROM Orders;

What is Collation? What are the different types of Collation Sensitivity?

Collation provides the rules that helps in determining how the stored data is to be sorted and compared.
Collation Rules defining the correct character sequence are used to sort the character data.
Collation Rules provides options for specifying case-sensitivity, accent marks, kana character types and character width

The types of collation sensitivity are:
– Case Sensitivity: A and a are treated differently.
– Accent Sensitivity: a and á are treated differently.
– Kana Sensitivity: Japanese kana characters Hiragana and Katakana are treated differently.
– Width Sensitivity: Same character represented in single-byte (half-width) and double-byte (full-width) are treated differently

Explain TOP clause in SQL ?

The TOP clause in SQL is useful when we need to fetch a TOP N number or X percent records from the table.
MySQL supports the LIMIT clause to fetch limited number of records
ORACLE supports the ROWNUM command to fetch limited number of records

Syntax for TOP Clause in SQL:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition]

Top 50 PL/SQL Interview Questions and Answers

The blog provides the commonly asked PL/SQL Interview questions and answers for the interview preparation. PL/SQL is mainly used by the Oracle Database Developer for writing Database specific SQL programs including Stored Procedures, functions, etc.

The blog contains the basic as well as advanced questions on PL/SQL which could be helpful for technical interviews for both freshers as well as experienced professionals.

What is PL/SQL ?

PL Stands for Procedural language .
SQL stands for Structured Query language .
PL/SQL is a Procedural language which acts as an extension to SQL.
PL /SQL allows SQL functions, conditional branching, statements and iteration also.
PL /SQL supports to execute multiple statements using Stored Procedure
PL /SQL also support writing customer error handling
PL/SQL also support the usage of functions, cursors and triggers

What is the latest PL/SQL Version?

PL/SQL latest version is 11.0 which comes as part of Oracle 11g Database. It supports all existing features like functions, stored procedures, user-defined record types, tables, packages and extensions as well as advanced features like native compilation, PL/SQL function result cache, and minute dependency tracking

What are the features of PL/SQL ?

PL/SQL is block structured language
PL /SQL programs can be deployed with ease to any environment where Oracle
Database is supported.
PL/SQL is integrated with Oracle Data Dictionary
PL /SQL can be embedded with Cursors, triggers, functions and stored procedures to develop complex database programming logic

What is the difference between SQL and PL/SQL ?

SQL is a Query Language used to interact with Database whereas PL/SQL is a Procedural language which acts as an extension to SQL
SQL is primarily used to perform table operation like create, insert, update and delete where as PL /SQL being a procedural language supports conditional branching , loops and statements, functions , cursors and usage of stored procedure for writing complex programming logic
SQL is single command statements whereas PL /SQL is a block of code executing multiple statements at once
SQL does not support error handling whereas PL /SQL code blocks can be supported with custom error handling
SQL statements can be used in PL /SQL whereas PL /SQL cannot be embedded in SQL

What is the difference between % ROWTYPE and TYPE RECORD

%ROWTYPE returns an entire row of a table or view when the query is executed.
TYPE RECORD returns column of different tables or views when the query is executed.

What data types are supported in PL/SQL ?

PL /SQL supports the below given data types:
Scalar Data Types: includes NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN
Composite Data Types: includes RECORD, TABLE

What are PL/SQL Exceptions ?

PL/SQL Exception is the error condition or warning raised when the condition fails are run time. Exception blocks are written to capture these error condition and handle using custom defined exceptions

What are the types of PL/SQL Exceptions ?

PL/SQL Exceptions can be classified into below 2 categories
System-defined Exceptions
User-defined Exceptions

Name 5 system defined PL/SQL Exceptions ?

The below given are the few PL/SQL Exceptions
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER
TOO_MANY_ROWS
ZERO_DIVIDE

Explain User defined PL/SQL Exceptions ?

The custom exceptions are known as user defined exceptions
The user defined exceptions must be declared and then raise explicitly ( using RAISE Statement or Stored Procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR
Syntax for user defined exceptions
<exception_name> EXCEPTION;

What all components are part of PL/SQL ?

The PL/SQL may consist of below given components
Variables and constants
Table and record TYPE statements
Stored Procedures
Functions
Cursors
PL/SQL Exceptions

What are the possible options to trace the PL/SQL code ?

The below given options / techniques can be used for PL/SQL code tracing
DBMS_APPLICATION_INFO
DBMS_TRACE
DBMS_SESSION and DBMS_MONITOR
TRCSSES and TKPROOF utilities

Explain PL/SQL hierarchical profiler and its benefits ?

The PL/SQL hierarchical profiler helps in capturing and analyzing hierarchical profiler
data for PL/SQL programs.
The PL/SQL hierarchical profiler consists of the DBMS_HPROF package(similar to
DBMS_PROFILER and DBMS_TRACE packages)
The PL/SQL hierarchical profiler also contains the plshprof command line utility to
converts the profile information into HTML format
Benefits :
Provides SQL and PL/SQL reporting for time consumption
Provides sub-programs calls count in the PL/SQL
Provides the time spent with each subprogram call
Provides Multiple interactive analytics reports in HTML format

Explain the main difference between function, stored procedure and a package ?

Function is quite useful when we need to compute the values at run-time . A function returns the value and its return type is defined in the function specification

Stored Procedure is useful when multiple statements are to be executed, conditional branching , loop statements are required to be used. It does not return any value.

Package is a scheme object which helps in grouping the PL/SQL components like Stored procedure, functions, triggers, cursors ,etc to get it executed as a single program.

What all parts PL/SQL consists of ?

PL/SQL packages have the following two parts:
Specification part: specifies the interface for the PL/SQL program
Body part: specifies the implementation of the PL/SQL program

How can a package be deleted ?

The DROP Package command is used to delete the PL/SQL package

Explain Trigger in PL/SQL ?

Trigger is a PL/SQL program which is invoked by Oracle Engine automatically when the specified event occurs.
Triggers can be executed in response to below given events:
DML statements like DELETE, INSERT, or UPDATE
DDL statements like CREATE, ALTER, or DROP
Database operations like SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN

Explain the advantages of using Trigger in PL/SQL ?

Trigger advantages are:
Trigger also prevents invalid transactions
Trigger helps in deriving column values based on table attribute data
Trigger helps in logging table access related information
Trigger helps in auditing
Trigger helps in synchronous table replication
Trigger proivdes security authorization
Trigger helps in enforcing referential integrity

Describe the Trigger Syntax ?

Syntax for creating Trigger:
CREATE [OR REPLACE ] TRIGGER <trigger_name>
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
   Declaration-statements
BEGIN
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

How many triggers can be applied on table ?

Oracle allows maximum of 12 triggers to be applied on a table.

What are the Trigger Types in PL/SQL ?

The triggers can be classified into 3 types:
Level Triggers
Event Triggers
Timing Triggers

Level Triggers:
The Level Triggers can be further classified into 2 types:
Row Level Trigger: Triggers fires when the row data is updated using the DML statements like DELETE, INSERT, or UPDATE
Statement Level Trigger: Triggers which used FOR EACH Row Clause for triggering the statement

Event Triggers:
DML Event Trigger: Triggers fires when DML statements like DELETE, INSERT, or UPDATE is executed
DDL Event Trigger: Triggers fires when DDL statements like CREATE, ALTER, or DROP is executed
Database Event Triger: Trigger fires when Database operations like SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN is executed

Timing Trigger:
BEFORE Trigger: Trigger fires BEFORE the DML statement is executed
AFTER Trigger: Trigger fires AFTER the DML statement is executed

How the trigger can be disabled ?

ALTER TRIGGER <trigger_name> DISABLE;

How to create a BEFORE DELTE Triggger using Oracle PL/SQL ?

A BEFORE DELETE Trigger are those trigger which gets fired to execute the defined statements before the DELETE operation is executed

Syntax for BEFORE DELETE Trigger:
CREATE [ OR REPLACE ] TRIGGER <trigger_name>
BEFORE DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
— variable declarations
BEGIN
— trigger code
EXCEPTION
WHEN …
— exception handling
END;

How to DISABLE all the triggers on the table ?

ALTER TABLE <table_name> DISABLE ALL TRIGGERS;

How to ENABLE the Trigger ?

ALTER TRIGGER <trigger_name> ENABLE;

Explain PL/SQL Cursors ?

Cursor is the name given to the temporary work area( also called Context Area) used by Oracle to execute the SQL commands which is also known as Private SQL Area.
A Cursor is is used to retrieve the table data and process the rows (one at a time) returned by the SQL statement
The PL/SQL Cursors can be broadly classified into below 2 types:
Implicit Cursors
Explicit Cursors

Explain the PL/SQL Implicit Cursor Type ?

The PL/SQL implicit Cursor are executed internally by Oracle.
When the user executes a SELECT statement or DML statement (INSERT, UPDATE, DELETE), Oracle reserves a private SQL area in memory called cursor and executes the SQL commands. The implicit cursor returns one row at a time.

Explain the PL/SQL Explicit Cursor Type ?

The PL/SQL explicit cursors are the custom cursors created by the developers to execute a specific set of SQL commands . The below steps need to be performed for using the explicit cursor
1. Declare the custom cursor
2. Open the cursor to reserve the memory area
3. Populate the memory area
4. Fetch the records from the active data set one row at a time
5. Validate and perform program logic
6. Close the cursor

Explain the PL/SQL Implicit Cursor attributes ?

The below given are the Implicit Cursor Attributes and their description
Cursor Attribute: %ISOPEN
Cursor Variable: SQL%ISOPEN
If cursor opens return TRUE else return FALSE
Cursor Attribute: %FOUND
Cursor Variable: SQL%FOUND
If SELECT statement return one or more rows , then returns TRUE else retrun FALSE
Cursor Attribute: %NOTFOUND
Cursor Variable: SQL%NOTFOUND
If SELECT statement does not return one or more rows , then returns TRUE else retrun FALSE
Cursor Attribute: %ROWCOUNT
Cursor Variable: SQL%ROWCOUNT
If returns the number of rows affected by a SELECT statement or DML statement then returns TRUE else return FALSE

Explain the COMMIT Statement in PL/SQL ?

COMMIT in PL/SQL is used to commit the Database SQL Statement data. The below steps are performed when the COMMIT executes:
1. The data in the PL/SQL program gets INSERTED/ UPDATED in the oracle table data as permanent record
2. The locks acquired by the transaction to commit the changes will be Released
3. The table data changes can be queried now

Describe FETCH Cursor example in PL/SQL ?

Create a cursor
CURSOR c1
IS
SELECT course_name
FROM Courses
WHERE course_id = course_id_in;

Now Fetch data for cursor c1
OPEN c1;
FETCH c1 into c1_courseName;

Explain the ROLLBACK Statement in PL/SQL ?

ROLLBACK in PL/SQL is used to UNDO the changes executed in the Database SQL Statement data before the COMMIT is performed. The below steps are performed when the ROLLBACK executes:
1. The executed changes are REVERSED / UNDO to ensure no update on table data
2. The locks acquired by the transaction to commit the changes will be Released

Explain the SAVEPOINT Statement in PL/SQL ?

SAVEPOINT in PL/SQL depicts the current point in the processing of a transaction. In case of transaction ROLLBACK , SAVEPOINT undo parts of a transaction only

What are user -defined functions and its types ?

User defined functions are the custom functions to implement a specific custom logic.
The User defined functions could be of 3 types:
– Scalar Functions
– Inline Table valued functions
– Multi statement valued functions

Explain LOCK TABLE statement in PL/SQL ?

The LOCK TABLE statement is used to lock tables, table partitions, or table sub partitions

Syntax for LOCK TABLE:
LOCK TABLE tables IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];

What are the different LOCK Modes provided in SQL ?

The below given are the different lock modes in SQL
– SHARE : allows to execute concurrent queries but does not allow users to update the locked table
– ROW SHARE: allows concurrent access to the table, but does not allow users to lock the entire table for exclusive access.
– SHARE UPDATE : allows concurrent access to the table, but does not allow users to lock the entire table for exclusive access.
– ROW EXCLUSIVE: allows concurrent access to the table, but does not allow users to lock the entire table for exclusive access. and locking the table in share mode
– SHARE ROW EXCLUSIVE: Users can view records in table but does not allow users to update the locked table or from locking the table in SHARE mode.
– EXCLUSIVE: allows queries on the locked table, but no other activities

Q

User

Q

User

Q

User

How to Assign Balancing Segment to Legal Entities ?

AJAX Interview Questions and Answers

What is AJAX ?

What are the advantages of AJAX ?

What are the disadvantages of AJAX ?

What are the formats supported by AJAX ?

What are the features of AJAX ?

Name few applications currently using AJAX ?

What is XMLHttpRequest ?

What are the properties of XMLHttpRequest?

What are the methods of XMLHttpRequest?

How can we abort the current Xmlhttprequest?

How asynchronous partial page postbacks can be detected ?

What are the security drwabacks using AJAX?

What are the common AJAX Frameworks ?

Which are the methods used for handling Cross-Domain AJAX Calls?

What is the difference between JavaScript and AJAX ?

What is “AsyncPostBackTimeout” Property?

What is the default value for “AsyncPostBackTimeout” Property?

What are the different ready states in AJAX Request ?

What is polling in AJAX ?

What is the difference between Synchronous and Asynchronous Ajax requests?

What is the difference between SPA and AJAX?

SQL Interview Questions and Answers for beginners and experienced professionals

What is SQL?

What is DBMS?

What is RDBMS?

What are the components of the DBMS?

What are the different types of DBMS?

What is advantages of using SQL in relational databases?

Does SQL provides programming language features ?

What does SQL contains ?

What is a Primary Key in relational database?

Give example of ALTER command to add Primary Key?

How to DROP a primary key from the table ?

What is a Unique Key ?

What is a Foreign Key ?

Explain the difference between Primary Key and Unique Key ?

Explain the difference between Primary Key and Foreign Key ?

What is an Index in SQL ?

What are the different types of indexes in SQL?

What is a Unique Index in SQL?

What is a clustered Index in SQL?

What are the advantages of clustered Index in SQL?

What is a non-clustered Index in SQL?

What is a View in SQL?

Explain the OFFSET and FETCH clause in SQL?

What is the difference between ORDER BY and GROUP BY in SQL ?

What is the difference between HAVING and GROUP BY clause in SQL ?

What is Online Transaction Processing (OLTP)?

What are the differences between OLTP and OLAP?

What are Aggregate Functions and its types in SQL ?

What are Scalar Functions and its types in SQL ?

What are CONSTRAINTS in SQL ?

Name few SQL constraints ?

How to create an empty table from an existing table?

How to select UNIQUE records from a table?

Which SQL comamnd can be used to get first 5 characters of the Employee Name?

What is the difference between TRUNCATE , DROP and DELETE commands in SQL ?

What is the difference between UNION, MINUS and INTERSECT commands in SQL?

What are Set Operators in SQL ?

What are Operators in SQL ?

Explain Arithmetic Operators in SQL ?

Explain Wildcard Operators in SQL ?

What is a SEQUENCE in SQL ?

what does CACHE and NOCACHE options mean in SEQUENCE ?

Explain Transaction Control Language(TCL) in SQL ?

What is AUTO INCREMENT in SQL?

What is a subquery in SQL?

What is the difference between SQL and MySQL?

What is the difference between UNION and UNION ALL in SQL ?

What is Collation? What are the different types of Collation Sensitivity?

Explain TOP clause in SQL ?

What is PL/SQL ?

What is the latest PL/SQL Version?

What are the features of PL/SQL ?

What is the difference between SQL and PL/SQL ?

What is the difference between % ROWTYPE and TYPE RECORD

What data types are supported in PL/SQL ?

What are PL/SQL Exceptions ?