Oracle SQL Interview Questions and Answers

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.

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

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.

select sal, (to_char(to_date(sal,'j'), 'jsp'))output from emp;

800 eight hundred
1600 one thousand six hundred 

To achieve this use rownum pseudo column with query, 

Select rownum, ename from emp; 
1 Scott 
2 Millor 
3 Jiyo 
4 Smith

“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.