Oracle SQL Interview Questions and Answers

What is a view?

A View in Oracle and in other database systems is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.

Since a view is based on one common set of SQL, this means that when it is called it’s less likely to require parsing. 

Security Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to.

What is difference between SUBSTR and INSTR?

INSTR (String1, String2,n)  INSTR returns the position of the m-th occurrence of the String 2 in string1. The search begins from nth position of string1 and provides character position in which a pattern is found in a string.
Example: INSTR ('ABC-DC-F','-',2) 
The result will be: 7 (2nd occurrence of '-')

SUBSTR (String1, n, m) SUBSTR returns a character string of size m in string1, starting from n-th position of string1 and It returns a specified portion of a string.
Example: SUBSTR ('BCDEF',4)
The result will be: BCDE

What is a synonym?
A synonym is an alternative name for objects such as tables, views, sequences.
Can a primary key contain more than one column?
Yes
What is a JOIN? Explain types of JOIN in oracle?

A SQL join clause combines records from two or more tables in a database.

Inner join : Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate.

SELECT *FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID;
SELECT *FROM employee, department WHERE employee.DepartmentID = department.DepartmentID;

Equi-join : An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join

SELECT *FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID;
SELECT *FROM employee INNER JOIN department USING (DepartmentID);

Natural join : A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-names in the joined tables.

SELECT *FROM employee NATURAL JOIN department;

Cross join : CROSS JOIN returns the Cartesian product of rows from tables in the join. 

SELECT *FROM employee CROSS JOIN department;
SELECT *FROM employee, department;

Outer joins : An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists.
Left outer join : The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).

SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

Right outer join : A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed.

SELECT * FROM employee RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

Full outer join : Conceptually, a full outer join combines the effect of applying both left and right outer joins

SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

Self-join : A self-join is joining a table to itself.

What are some SQL aggregates and other built-in functions?
The common aggregate, built-in functions are AVG, SUM, MIN, MAX, COUNT and DISTINCT.
Display the number value in Words ?
select sal, (to_char(to_date(sal,'j'), 'jsp'))output from emp;

800 eight hundred
1600 one thousand six hundred 

How do I display row number with records?

To achieve this use rownum pseudo column with query, 

Select rownum, ename from emp; 
1 Scott 
2 Millor 
3 Jiyo 
4 Smith
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.

Latest Updates

Pipes and Cistern

Three taps A,B and C can fill a tank in 12, 15 and 20 hours respectively. If A is open all the time and B and C are open for one hour each alternatively, the tank will be full in?

Two pipes A and B can fill a tank in 6 hours and 4 hours respectively. If they are opened on alternate hours and if pipe A s opened first, in how many hours, the tank shall be full?

A large tanker can be filled by two pipes A and B in 60 min and 40 min respectively. How many minutes will it take to fill the tanker from empty state if B is used for first half of the time, and A and B fill it together for the other half?

A tank is filled by 3 pipes with uniform flow. The first two pipes operating simultaneously fill the tank in the same time during which the tank is filled by the third pipe alone. The 2nd pipe fills the tank 5 hours faster than first pipe and 4 hours slower than third pipe. The time required by first pipe is?

Two pipes A and B together can fill a cistern in 4 hours. Had they been opened separately, then B would have taken 6 hours more than A to fill the cistern. How much time will be taken by A to fill the cistern separately?

Two pipes A and B can fill a tank in 24 min and 32 min respectively. If both the pipes are opened simultaneously, after how much time B should be closed so that the tank is full in 18 min?

Two pipes A and B can fill a tank in 36 min and 45 min respectively. A water pipe C can empty the tank in 30 min. First A and B are opened. after 7 min, C is also opened. In how much time, the tank is full?

Two pipes can fill a cistern in 14 hours and 16 hours respectively. The pipes are opened simultaneously and it is found that due to leakage in the bottom it took 32 min more to fill the cistern. When the cistern is full, in what time will the leak empty it?

Bucket P has thrice the capacity as Bucket Q. It takes 60 turns for Bucket P to fill the empty drum. How many turns it will take for both the buckets P and Q, having each turn together to fill the empty drum?

Probability

In a class , 30 % of the students offered English, 20 % offered Hindi and 10 % offered Both.If a student is offered at random, what is the probability that he has offered English or Hindi?

In a lottery ,there are 10 prizes and 25 blanks.A lottery is drawn at random. what is the probability of getting a prize ?

Two dice are thrown simultaneously .what is the probability of getting two numbers whose product is even?

Two diced are tossed the probability that the total score is a prime number?

Two cards are drawn at random from a pack of 52 cards What is the probability that either both are black or both are queens?

Two dice are thrown together .What is the probability that the sum of the number on the two faces is divisible by 4 or 6?

A bag contains 6 white and 4 black balls .Two balls are drawn at random .Find the probability that they are of the same colour?

In a simultaneous throw of a pair of dice,find the probability of getting a total more than 7?

An unbiased die is tossed.Find the probability of getting a multiple of 3?

Latest Job Notifications

Banking Jobs

बैंकिंग कार्मिक चयन संस्थान Institute of Banking Personnel Selection(IBPS) - 7855 पद - 29/07/2022 अंतिम तिथि

SSC Recruitment

कर्मचारी चयन आयोग (एसएससी) Staff Selection Commission (SSC) - 1411 पद - 29/07/2022 अंतिम तिथि

Jobs in Rajasthan

राजस्थान अधीनस्थ और मंत्रिस्तरीय सेवा चयन बोर्ड Rajasthan Subordinate and Ministerial Services Selection Board (RSMSSB) - 5546 पद - 22-Jul-2022 अंतिम तिथि

Railway Recruitment

उत्तर मध्य रेलवे भर्ती प्रकोष्ठ Railway Recruitment Cell(NCR) - 1659 पद - 01/08/2022 अंतिम तिथि

Jobs in West Bengal

इंडियन पोस्ट सर्विस Indian Post Service - 2357 पद - 19/08/2021 अंतिम तिथि - पश्चिम बंगाल पोस्टल सर्कल

Banking Jobs

भारतीय स्टेट बैंक State Bank of India(SBI) - 6100 पद - 26/07/2021 अंतिम तिथि - CRPD/APPR/2021-22/10

SSC Recruitment

कर्मचारी चयन आयोग Staff Selection Commission(SSC) - 25271 पद - 31/08/2021 अंतिम तिथि

Jobs in Madhya Pradesh

राष्ट्रीय स्वास्थ्य मिशन मध्य प्रदेश National Health Mission MP(NRHM) - 5215 पद - 22/06/2021 अंतिम तिथि

Jobs in Punjab

पंजाब स्टेट पावर कॉर्पोरेशन लिमिटेड Punjab State Power Corporation Limited (PSPCL) - 2632 पद - 20/06/2021 अंतिम तिथि

Jobs in Chhattisgarh

स्वास्थ्य सेवा निदेशालय, छत्तीसगढ़ Department of Health & Family Welfare, Chhattisgarh - 267 पद - 26/06/2021 अंतिम तिथि