Top 10 Oracle Interview Questions

Last Updated on March 3, 2022 by Ria Pathak

Want to work for Oracle? Here we discuss some of the commonly asked interview questions.

Explain differences between DDL and DML with examples

DDL(Data Definition Language): DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in database.

Examples of DDL commands:

CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers).
ALTER-is used to alter the structure of the database.
TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.
COMMENT –is used to add comments to the data dictionary.
RENAME –is used to rename an object existing in the database.

DML(Data Manipulation Language): The SQL commands that deals with the manipulation of data present in database belong to DML or Data Manipulation Language and this includes most of the SQL statements.
Examples of DML commands:
SELECT – is used to retrieve data from the a database.
INSERT – is used to insert data into a table.
UPDATE – is used to update existing data within a table.
DELETE – is used to delete records from a database table.

Write an efficient C program to find the sum of contiguous subarray within a one-dimensional array of numbers which has the largest sum.

Above problem can be solved using Kadane’s Algorithm Kadane’s Algorithm:

C Program to find the contiguous subarray with largest sum

int maxSubArraySum(int a[], int size) {
  int max_so_far = INT_MIN, max_ending_here = 0;

  for (int i = 0; i < size; i++) {
    max_ending_here = max_ending_here + a[i];
    if (max_so_far < max_ending_here)
      max_so_far = max_ending_here;

    if (max_ending_here < 0)
      max_ending_here = 0;
  }
  return max_so_far;
}

int main() {
  int a[] = {
    -2,
    -3,
    4,
    -1,
    -2,
    1,
    5,
    -3
  };
  int n = sizeof(a) / sizeof(a[0]);
  int max_sum = maxSubArraySum(a, n);
  printf(“Maximus continous sum is % d”, max_sum)
  return 0;
}

Given linked list, write a function to detect if there is a loop in it.

Given problem can be solved using Floyd’s Cycle-Finding AlgorithmFloyd’s Cycle-Finding Algorithm:

This is the fastest method. Traverse linked list using two pointers. Move one pointer by one and other pointer by two. If these pointers meet at some node then there is a loop. If pointers do not meet then linked list doesn’t have loop.

Implementation of Floyd’s Cycle-Finding Algorithm:

int detectloop(struct Node * list) {
  struct Node * slow_p = list, * fast_p = list;

  while (slow_p && fast_p && fast_p - > next) {
    slow_p = slow_p - > next;
    fast_p = fast_p - > next - > next;
    if (slow_p == fast_p) {
      printf("Found Loop");
      return 1;
    }
  }
  return 0;
}

Output:
Found loop
Time Complexity: O(n)
Auxiliary Space: O(1)

Write a code for reversing a string , example if input is “PrepBytes” output should be “setyBperp”

C Program to Reverse the String using Recursion

void reverse(char[], int, int);
int main() {
    char str1[20];
    int size;

    printf("Enter a string to reverse: ");
    scanf("%s", str1);
    size = strlen(str1);
    reverse(str1, 0, size - 1);
    printf("The string after reversing is: %s
      ", str1);
      return 0;
    }

    void reverse(char str1[], int index, int size) {
      char temp;
      temp = str1[index];
      str1[index] = str1[size - index];
      str1[size - index] = temp;
      if (index == size / 2) {
        return;
      }
      reverse(str1, index + 1, size);
    }

What are collections in Java?

Collections in java is a framework that provides an architecture to store and manipulate the group of objects.
All the operations that you perform on a data such as searching, sorting, insertion, manipulation, deletion etc. can be performed by Java Collections.
Java Collection framework provides many interfaces (Set, List, Queue, Deque etc.) and classes (ArrayList, Vector, LinkedList, PriorityQueue, HashSet, LinkedHashSet, TreeSet etc).

What is a Friend function?

A friend function can be given special grant to access private and protected members. A friend function can be:

• A method of another class
• A global function

To declare a function as a friend of a class, precede the function prototype in the class definition with keyword friend as follows:

class Volume {
  private:
    int width, height, breadth;

  friend long Box::calculateVolume();
  //only calculateVolume() of Box class can access private members of Volume
};

Friendship is not mutual. If a class A is friend of B, then B doesn’t become friend of A automatically.

What is the difference between class and structure?

In terms of language, except one little detail, there is no difference between struct and class.
Contrary to what younger developers, or people coming from C believe at first, a struct can have constructors, methods (even virtual ones), public, private and protected members, use inheritance, be templated… just like a class.

The only difference is if you don’t specify the visibility (public, private or protected) of the members,
they will be public in the struct and private in the class AND the visibility by default goes just a little further than members:
for inheritance if you don’t specify anything then the struct will inherit publicly from its base class

What is convoy effect?

Convoy effect occurs in case of FCFS when all the other processes wait for one big process to get off the CPU.
This effect results in lower CPU and device utilization that might be possible if the shorter processes were allowed to go first.

What is Table Normalization in DBMS?

Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly. Let’s discuss about anomalies first then we will discuss normal forms with examples.Anomalies in DBMS

There are three types of anomalies that occur when the database is not normalized. These are – Insertion, update and deletion anomaly. Let’s take an example to understand this.

Example: Suppose a textile company stores the employee details in a table named employee that has four attributes: emp_id for storing employee’s id, emp_name for storing employee’s name, emp_address for storing employee’s address and emp_dept for storing the department details in which the employee works. At some point of time the table looks like this:

emp_id	emp_name	emp_address	emp_dept
101	Krishna	Delhi	D001
101	Krishna	Delhi	D002
123	Asha	Agra	D890
166	Nisha	Chennai	D900
166	Nisha	Chennai	D004

The above table is not normalized. We will see the problems that we face when a table is not normalized.

Update anomaly: In the above table we have two rows for employee Krisha as he belongs to two departments of the company. If we want to update the address of Krisha then we have to update the same in two rows or the data will become inconsistent. If somehow, the correct address gets updated in one department but not in other then as per the database, Krishna would be having two different addresses, which is not correct and would lead to inconsistent data.

Insert anomaly: Suppose a new employee joins the company, who is under training and currently not assigned to any department then we would not be able to insert the data into the table if emp_dept field doesn’t allow nulls.

Delete anomaly: Suppose, if at a point of time the company closes the department D890 then deleting the rows that are having emp_dept as D890 would also delete the information of employee Maggie since she is assigned only to this department.

To overcome these anomalies we need to normalize the data

Explain Abstraction with example.

Abstraction in Java is achieved by using interface and abstract class in Java.
An interface or abstract class is something which is not concrete , something which is incomplete. We just declare the methods without implementing it.
In order to use interface or abstract class, we need to extend and implement an abstract method with concrete behavior.

One example of Abstraction is creating interface to denote common behavior without specifying any details about how that behavior works e.g.

You create an interface called Vehicle which has the start() and stop() method.

This is called abstraction of Vehicle because every server should have a way to start and stop and details may differ.