October 10, 2018

Sreekanth B

Amazon Most Frequently Asked Latest Oracle Interview Questions Answers

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;
Amazon Most Frequently Asked Latest Oracle Interview Questions Answers
Amazon Most Frequently Asked Latest Oracle Interview Questions Answers

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 NULL value in oracle?

NULL value represents missing or unknown data. This is used as a place holder or represented it in as default entry to indicate that there is no actual data present.

Subscribe to get more Posts :