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?
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