Computer Sciences Corporation Most Frequently Asked Oracle Interview Questions Answers
What is difference between SUBSTR and INSTR?
SUBSTR returns specific portion of a string and INSTR provides character position in which a pattern is found in a string.
SUBSTR returns string whereas INSTR returns numeric.
What is the parameter mode that can be passed to a procedure?
IN, OUT and INOUT are the modes of parameters that can be passed to a procedure.
What are the different Oracle Database objects?
There are different data objects in Oracle –
Tables – set of elements organized in vertical and horizontal
Views – Virtual table derived from one or more tables
Indexes – Performance tuning method for processing the records
Synonyms – Alias name for tables
Sequences – Multiple users generate unique numbers
Tablespaces – Logical storage unit in Oracle
What are the differences between LOV and List Item?
LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column, LOV can have one or more columns.
What are privileges and Grants?
Privileges are the rights to execute SQL statements – means Right to connect and connect. Grants are given to the object so that objects can be accessed accordingly. Grants can be provided by the owner or creator of an object.
What is the difference between $ORACLE_BASE and $ORACLE_HOME?
Oracle base is the main or root directory of an oracle whereas ORACLE_HOME is located beneath base folder in which all oracle products reside.
What is the fastest query method to fetch data from the table?
Row can be fetched from table by using ROWID. Using ROW ID is the fastest query method to fetch data from the table.
What is the maximum number of triggers that can be applied to a single table?
12 is the maximum number of triggers that can be applied to a single table.
How to display employee records who gets more salary than the average salary in the department?
This can be done by this query –
Select * from employee where salary>(select avg(salary) from dept, employee where dept.deptno = employee.deptno;
1
Select * from employee where salary>(select avg(salary) from dept, employee where dept.deptno = employee.deptno;
What do you mean by GROUP BY Clause?
A GROUP BY clause can be used in select statement where it will collect data across multiple records and group the results by one or more columns.
What is a sub query and what are the different types of subqueries?
Sub Query is also called as Nested Query or Inner Query which is used to get data from multiple tables. A sub query is added in the where clause of the main query.
There are two different types of subqueries:
Correlated sub query
A Correlated sub query cannot be as independent query but can reference column in a table listed in the from list of the outer query.
Non-Correlated subquery
This can be evaluated as if it were an independent query. Results of the sub query are submitted to the main query or parent query.
What is cross join?
Cross join is defined as the Cartesian product of records from the tables present in the join. Cross join will produce result which combines each row from the first table with the each row from the second table.
What are temporal data types in Oracle?
Oracle provides following temporal data types:
Date Data Type – Different formats of Dates
TimeStamp Data Type – Different formats of Time Stamp
Interval Data Type – Interval between dates and time
How do we create privileges in Oracle?
A privilege is nothing but right to execute an SQL query or to access another user object. Privilege can be given as system privilege or user privilege.
[sql]GRANT user1 TO user2 WITH MANAGER OPTION;[/sql]
What is the difference between TRANSLATE and REPLACE?
Translate is used for character by character substitution and Replace is used substitute a single character with a word.
How do we display rows from the table without duplicates?
Duplicate rows can be removed by using the keyword DISTINCT in the select statement.
What is the usage of Merge Statement?
Merge statement is used to select rows from one or more data source for updating and insertion into a table or a view. It is used to combine multiple operations.
What is difference between SUBSTR and INSTR?
SUBSTR returns specific portion of a string and INSTR provides character position in which a pattern is found in a string.
SUBSTR returns string whereas INSTR returns numeric.
What is the parameter mode that can be passed to a procedure?
IN, OUT and INOUT are the modes of parameters that can be passed to a procedure.
What are the different Oracle Database objects?
There are different data objects in Oracle –
Tables – set of elements organized in vertical and horizontal
Views – Virtual table derived from one or more tables
Indexes – Performance tuning method for processing the records
Synonyms – Alias name for tables
Sequences – Multiple users generate unique numbers
Tablespaces – Logical storage unit in Oracle
What are the differences between LOV and List Item?
LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column, LOV can have one or more columns.
What are privileges and Grants?
Privileges are the rights to execute SQL statements – means Right to connect and connect. Grants are given to the object so that objects can be accessed accordingly. Grants can be provided by the owner or creator of an object.
 |
| Computer Sciences Corporation Most Frequently Asked Oracle Interview Questions Answers |
What is the difference between $ORACLE_BASE and $ORACLE_HOME?
Oracle base is the main or root directory of an oracle whereas ORACLE_HOME is located beneath base folder in which all oracle products reside.
What is the fastest query method to fetch data from the table?
Row can be fetched from table by using ROWID. Using ROW ID is the fastest query method to fetch data from the table.
What is the maximum number of triggers that can be applied to a single table?
12 is the maximum number of triggers that can be applied to a single table.
How to display employee records who gets more salary than the average salary in the department?
This can be done by this query –
Select * from employee where salary>(select avg(salary) from dept, employee where dept.deptno = employee.deptno;
1
Select * from employee where salary>(select avg(salary) from dept, employee where dept.deptno = employee.deptno;
A GROUP BY clause can be used in select statement where it will collect data across multiple records and group the results by one or more columns.
What is a sub query and what are the different types of subqueries?
Sub Query is also called as Nested Query or Inner Query which is used to get data from multiple tables. A sub query is added in the where clause of the main query.
There are two different types of subqueries:
Correlated sub query
A Correlated sub query cannot be as independent query but can reference column in a table listed in the from list of the outer query.
Non-Correlated subquery
This can be evaluated as if it were an independent query. Results of the sub query are submitted to the main query or parent query.
What is cross join?
Cross join is defined as the Cartesian product of records from the tables present in the join. Cross join will produce result which combines each row from the first table with the each row from the second table.
What are temporal data types in Oracle?
Oracle provides following temporal data types:
Date Data Type – Different formats of Dates
TimeStamp Data Type – Different formats of Time Stamp
Interval Data Type – Interval between dates and time
How do we create privileges in Oracle?
A privilege is nothing but right to execute an SQL query or to access another user object. Privilege can be given as system privilege or user privilege.
[sql]GRANT user1 TO user2 WITH MANAGER OPTION;[/sql]
What is the difference between TRANSLATE and REPLACE?
Translate is used for character by character substitution and Replace is used substitute a single character with a word.
How do we display rows from the table without duplicates?
Duplicate rows can be removed by using the keyword DISTINCT in the select statement.
What is the usage of Merge Statement?
Merge statement is used to select rows from one or more data source for updating and insertion into a table or a view. It is used to combine multiple operations.
Post a Comment