Oracle FAQs #2

41. WHAT IS DEFAULT VALUE FOR EVERY COLUMN OF A TABLE?
Ans: NULL.

42. WHAT IS CREATED IMPLICITLY FOR EVERY UNIQUE AND PRIMARY KEY COLUMNS?
Ans: Index.

43. WHAT ARE LIMITATIONS OF CHECK CONSTRAINT?
Ans: In this we can't specify Pseudo Columns like sysdate etc.

44. WHAT IS DIFFERENCE BETWEEN REFERENCES AND FOREIGN KEY CONSTRAINT?
Ans: References is used as column level key word where as foreign key is used as table level constraint.

45. WHAT IS "ON DELETE CASCADE"?
Ans: when this key word is included in the definition of a child table then whenever the records from the parent table is deleted automatically the respective values in the child table will be deleted.

46. WHAT IS PARENT-CHILD OR MASTER-DETAIL RELATIONSHIP?
Ans: A table which references a column of another table(using References)is called as a child table(detail table) and a table which is being referred is called Parent (Master) Table .

47. HOW TO DROP A PARENT TABLE WHEN IT'S CHILD TABLE EXISTS?
Ans: Using "on delete cascade".

48. IS ORACLE CASE SENSITIVE?
Ans: NO

49. HOW ORACLE IDENTIFIES EACH RECORD OF TABLE UNIQUELY?
Ans: By Creating indexes and reference IDs.

50. WHAT IS A PSEUDO-COLUMN? NAME SOME PSEUDO-COLUMNS OF ORACLE?
Ans: Columns that are not created explicitly by the user and can be used explicitly in queries are called Pseudo-Columns.
Ex:currval,nextval,sysdate….

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.

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, INTERSECT or MINUS is called 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,
Abs (m),
Ceil (m),
Floor (m),
Mod (m, n)
Character Functions:
Chr (x)
Concert (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).
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.

76. WHAT IS A VIEW? HOW IT IS DIFFERENT FROM A TABLE?
Ans: View is database object, which exists logically but contains no physical data and manipulates the base table. View is saved as a select statement in the database and contains no physical data whereas Table exists physically.

77. WHAT IS DIFFERENCE BETWEEN SIMPLE AND COMPLEX VIEWS?
Ans: Simple views can be modified whereas Complex views(created based on more than one table) cannot be modified.

78. WHAT IS AN INLINE VIEW?
Ans: Inline view is basically a subquery with an alias that u can use like a view inside a SQL statement. It is not a schema object like SQL-object.

79. HOW TO UPDATE A COMPLEX VIEW?
Ans: Using 'INSTEAD OF' TRIGGERS Complex views can be Updated.

80. WHAT FOR "WITH CHECK OPTION" FOR A VIEW?
Ans: "WITH CHECK OPTION" clause specifies that inserts and updates r performed through the view r not allowed to create rows which the view cannot select and therefore allows integrity constraints and data validation checks to be enforced on data being inserted or updated.

81. WHAT IS AN INDEX? ADVANTAGE OF AN INDEX
Ans: An Index is a database object used n Oracle to provide quick access to rows in a table. An Index increases the performance of the database.

82. WHAT IS A SEQUENCE? PSEUDO-COLUMNS ASSOCIATED WITH SEQUENCE?
Ans: Sequence is a Database Object used to generate unique integers to use as primary keys. Nextval, Currval are the Pseudo Columns associated with the sequence.

**83. WHAT IS A CLUSTER? WHEN TO USE A CLUSTER? HOW TO DROP A CLUSTER WHEN CLUSTERED TABLE EXISTS?
Ans: Cluster and Indexes are transparent to the user. Clustering is a method of storing tables that are intimately related and are often joined together into the same area on the disk. When cluster table exists then to drop cluster we have to drop the table first then only cluster is to be dropped.

84. WHAT IS A SNAPSHOT OR MATERIALIZED VIEW?
Ans: Materialized views can be used to replicate data. Earlier the data was replicated through CREATE SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be used as synonym for CREATE SNAPSHOT. Query performance is improved using the materialized view as these views pre calculate expensive joins and aggregate operations on the table.

85. WHAT IS A SYNONYM?
Ans: A Synonym is a database object that allows you to create alternate names for Oracle tables and views. It is an alias for a table, view, snapshot, sequence, procedure, function or package.

86. WHAT IS DIFFERENCE BETWEEN PRIVATE AND PUBLIC SYNONYM?
Ans: Only the user or table owner can reference Private synonym whereas any user can reference the Public synonym.

87. WHAT IS DIFFERENCE BETWEEN "SQL" AND "SQL*PLUS" COMMANDS?
Ans: SQL commands are stored in the buffer whereas SQL*PLUS are not.

**88. NAME SOME SQL*PLUS COMMANDS?
Ans: DESC [CRIBE], START, GET, SAVE, / are SQL*PLUS COMMANDS.

89. WHAT ARE "SQL*PLUS REPORTING" COMMANDS?
Ans: SPOOL file-name, SPOOL OUT, TTITLE, BTITLE, BREAK ON, COMPUTE OF [break] ON etc are SQL*PLUS REPORTING COMMANDS.

90. WHAT ARE SYSTEM AND OBJECT PRIVILEGES?
Ans: Connect and Resource etc are System Privileges. Create < object >, Select, Insert, Alter etc are Object Privileges.