Oracle SQL Interview Questions and Answers

What is Correlated Subquery?
Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete.
What is Sequence?
Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.
What is the Purpose of HAVING Clause?
The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.
What is INLINE View in SQL?
The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.
Does the view exist if the table is dropped from the database?
Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.
What is the difference between a “where” clause and a “having” clause?

“Where” is a kind of restriction statement. You use where clause to restrict all the data from DB. Where clause is used before result retrieving. 

Having clause is using after retrieving the data. Having clause is a kind of filtering command.

What is the difference between the snapshot and synonym?
A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity. A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.
What is the difference between data types char and varchar?
Char reserves the number of memory locations mentioned in the variable declarations, even though not used. Where as Varchar does not reserve any memory locations when the variable is declared, it stores the values only after they are assigned.
What is Rowid?
It is a Hexadecimal Representation of a Row in a Table. Rowid can only be Changed if we ‘Enable Row Movement’ on a Partitioned Table. Rowid’s of Deleted Rows can be Reused if Transaction is Committed.
What is the Difference between Translate & Replace?
Translate function converts each character in String with specified one whereas Replace function replaces part of the string in continuity by another sub-string.
What is NVL?
NVL: Null value function converts a null value to a non-null value for the purpose of evaluating an expression.
Operators used in SELECT statements are?
= Equal <> or != Not equal > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern

Latest Updates

Ratio and Proportion

A certain sum of money is divided between A and B in the ratio of 5/4 : 5/3 and A got Rs. 360/-. Find the total sum of money and B’s share?

In an office, the ratio of the salaries of an officer and a clerk is 15:7. If the salary of a clerk is Rs. 720/- less than the salary of the officer, find the salary of each?

In an election to a municipal council, the votes secured by two candidates are in the ratio 13:19. The elected candidates gets 312 votes more than the second candidate. How many votes are secured by the second candidate?

A certain sum of money is divided between Murthy and Prasad in the ratio 22/7 : 8/3. If Murthy gets Rs. 120/- more than Prasad, find the share of each?

In a cricket test series, the runs made by Raju and Tendulkar are in the ratio 5:9 and Tendulkar and Azar are in the ratio 6:7. What are the runs made by them if Azar makes 187 runs more than Raju?

If A:B = 2:3 and B:C = 9:5 find A:B:C?

If A:B = 3/5:5/7 and B:C = 3/4:2/5, find A:B:C?

If R:S = 0.01:0.11 and S:T = 2.2:1, Find R:S:T?

In a school, Under a Saving Scheme, Rekha saves 3/2 times the savings of Bindu, Geeta saves 4 times the savings of Rekha. Find the ratio of the savings of all the three in least terms?

Simplification

A man left 1/3 rd of his property to his wife and 3/5 th of the remainder to his daughter. He gave the rest to his son who received Rs. 6400/-. How much was his original property worth?

Ratio and Proportion

In a school 4/9 of the pupils are boys. There are 125 girls. How many boys are there?

If A:B = 3:4 and B:C = 5:6, find A:C?

IF X:Y = 3:4 AND Y:Z = 4:5, FIND X:Y:Z?

Area

A house site is in the shape of a square. Its area is 1225 sq metres. What is its perimeter?

The area of a square field is 7396 sq metres. Find its perimeter?

Numbers

Twice the first of three consecutive even integer is 6 more than the second integer. What is the third integers?

Ratio and Proportion

If 2x=3y and 4y=5z, then what is x:z=?

What quantity must be added to each term of the ratio a + b : a – b to make it equal to (a + b)2 : (a – b)2 ?