oracle sql plsql interview questions and answers

Oracle Interview Questions and Answers

51. WHAT FOR "ORDER BY" CLAUSE FOR A QUERY?

Ans: To arrange the query result in a specified

Order (ascending,descending) by default it takes ascending order.

52. WHAT IS "GROUP BY" QUERIES?

Ans: To group the query results based on condition.

53. NAME SOME AGGREGATE FUNCTIONS OF SQL?

Ans: AVG, MAX, SUM, MIN,COUNT.

54. WHAT IS DIFFERENCE BETWEEN COUNT (), COUNT (*) FUNCTIONS?

Ans: Count () will count the specified column whereas count (*) will

count total no. of rows in a table.

55. WHAT FOR ROLLUP AND CUBE OPERATORS ARE?

Ans: To get subtotals and grand total of values of a column.

56. WHAT IS A SUB-QUERY?

Ans: A query within a query  is called a sub query where the result of

inner query will be used by the  outer query.

57. WHAT ARE SQL OPERATORS?

Ans: Value (), Ref () is SQL operator. ( Used with Objects )

58. EXPLAIN "ANY","SOME","ALL","EXISTS" OPERATORS?

Ans: Any: The Any (or it’s synonym SOME) operator computes the lowest

value from the set and compares a value to each returned by a sub query.

All: ALL compares a value to every value returned by SQL.

Exists: This operator produces a BOOLWAN results. If a sub query

produces any result then it evaluates it to TRUE else it evaluates it to FALSE.

59. WHAT IS A CORRELATED SUB QUERY, HOW IT IS DIFFERENT FROM A NORMAL SUB QUERY?

Ans: A correlated subquery is a nested subquery, which is executed once

for each ‘Candidate row’ by the main query, which on execution uses a value from a column in the outer query. In normal sub query the result of inner query is dynamically substituted in the condition of the outer query where as in a correlated subquery, the column

value used in inner query refers to the column value present in the

outer query forming a correlated subquery.

60. WHAT IS A JOIN - TYPES OF JOINS?

Ans: A join is used to combine two or more tables logically to get

query results.

    There are four types of Joins namely

     EQUI Join

     NON-EQUI Join

     SELF Join

     OUTER Join.

61. WHAT ARE MINIMUM REQUIREMENTS FOR AN EQUI-JOIN?

Ans: There shold be atleast one common column between the joining tables.

62. WHAT IS DIFFERENCE BETWEEN LEFT, RIGHT OUTER JOIN?

Ans:If there r any values in one table that do not have corresponding values in the other,in an equi join that row will not be selected.Such rows can be forcefully selected by using outer join symbol(+) on either of the sides(left or right)  based on the requirement. 

63. WHAT IS DIFFERENCE BETWEEN EQUI AND SELF JOINS?

Ans:  SELF JOIN is made within the table whereas

         EQUI JOIN is made between  different tables having common column.

64. WHAT ARE "SET" OPERATORS?

Ans: UNION ALL,UNION, INTERSECT ,MINUS are SET OPERATORS.

65. WHAT IS DIFFERENCE BETWEEN "UNION" AND "UNION ALL" OPERATORS?

Ans: UNION will return the values distinctly whereas UNION ALL will

return even duplicate values.

66. NAME SOME NUMBER, CHARACTER, DATE, CONVERSION, OTHER FUNCTIONS?

Ans: 

      Number Functions:

                Round (m, [n]),  Trunc (m, [n]),  Power (m, n),  Sqrt(n),

                Abs (m), Ceil (m),  Floor (m), Mod (m, n) ,sign(n)                                 

      Character Functions:

                Chr (x), Concat (string1, string2), Lower (string)

                      Upper (string), Substr (string, from_str, to_str), ASCII (string)

                      Length (string), Initcap (string). 

    Date Functions:

                 Sysdate, Months between (d1, d2), To_char (d, format)

                 Last day (d), Next_day (d, day).add_months(d,n), Extract

   Conversion Functions:     To_char,  To_date, To_number

67. WHAT IS DIFFERENCE BETWEEN MAX () AND GREATEST () FUNCTIONS?

Ans: MAX is an aggregate function which takes only one column name of a table as parameter whereas Greatest is a general function which can take any number of values and column names from dual and table respectively.

68. WHAT FOR NVL () FUNCTION IS?

Ans: NVL Function helps in substituting a value in place of a NULL.

69. WHAT FOR DECODE () FUNCTION IS?

Ans: It is substitutes value basis and it actually does an

'if-then-else' test.

70. WHAT IS DIFFERENCE BETWEEN TRANSLATE () AND REPLACE () FUNCTIONS?

Ans: Translate()   is a superset of functionality provided by Replace().

71. WHAT IS DIFFERENCE BETWEEN SUBSTR () AND INSTR () FUNCTIONS?

Ans:   Substr() will return the specified part of a string whereas

          Instr() return the position of the specified part of the string.

72. WHAT IS A JULIAN DAY NUMBER?

Ans: It will return count of the no. Of days between January 1, 4712 BC

and the given date.

73. HOW TO DISPLAY TIME FROM A DATE DATA?

Ans: By using time format as 'hh [hh24]: mi: ss' in to_char() function.

74. HOW TO INSERT DATE AND TIME INTO A DATE COLUMN?

Ans: By using format 'dd-mon-yy hh [hh24]: mi: ss' in to_date() function.

75. WHAT IS DIFFERENCE BETWEEN TO_DATE () AND TO_CHAR () CONVERSION FUNCTIONS?

Ans:   To_date converts character date to date format whereas

          To_char function converts date or numerical values to characters.