Oracle Interview Questions Set - 02
What is Normal Index or B-Tree index in Oracle?
Normal Index also called as B-Tree index which is default index Create on table in Oracle.
Example:
Create index IDX_EMP_NAME on EMP(NAME);
What is Unique Index in Oracle?
If table contains uniquely identified values in specified column, then you should use unique index. Especially while creating the table if we specify the primary key then unique index is automatically created on that column.
Example:
CREATE UNIQUE INDEX Idx1_EMP on EMP(EMP_ID);
What is Bit-Map Index in Oracle?
If Table contains the distinct values which are not more than 20 distinct values then user should for Bit map indexes. The bit map indexes are very much useful in dataware housing where there are low level of concurrent transactions.
Example:
CREATE BITMAP index Idx_DEPT_NAME on DEPT(Department_name);
What is Composite Index in Oracle?
- When 2 or more columns in single table are related which each other and used in ‘Where’ condition of select statement then user should create composite index on the columns which are created.
- If all columns selected by in query are in composite index, then oracle will return the values from the index without accessing the table.
- Composite indexes should be avoided as they are large in size
Ex: Suppose we want to do indexing on Employee and Department table. Here in above example Empno and Deptno are related to each other. So, we can create index on Empno and Deptno
Create index IDX_ENO_DEPTNO on Employee(Empno,Deptno);
What is Function Based Index in Oracle?
- Function based indexes allows us to index on the functional columns so that oracle engine will take the index and improves the performance of the query.
- As per requirements we are using lot of SQL functions to fetch the results.
- Function based indexes gives ability to index the computed columns.
- Function based indexes are easy to implement and it also provides immediate value.
- These indexes speeds up the application without changing application code or query.
Example:
Create index IDX2_Employee on Employee(trunc(Hire_date));
What are Clustered Indexes in Oracle?
- The clustered indexes are indexes which are physically stored in order means it stores in ascending or descending order in Database.
- Clustered indexes are created one for each table.
- When primary key is created then clustered index has been automatically created in the table.
- If table is under heavy data modifications the clustered indexes are preferable to use.
What are Non-Clustered Indexes in Oracle?
- The clustered indexes are used for searching purpose as we can create clustered indexes where primary is defined. But Non-Clustered indexes are indexes which will be created on the multiple joining conditions, multiple filters used in query.
- We can create 0 to 249 Non-Clustered indexes on single table.
- Foreign keys should be Non-Clustered. When user wants to retrieve heavy data from fields other than primary key the Non-Clustered indexes are useful.
What are Analytical or Analytic Functions in Oracle?
The below are few examples of Analytical Functions in Oracle.
▹ Rank()
▹ Dense_Rank()
▹ First_value()
▹ Last_Value()
▹ LEAD()
▹ LAG()
What is the difference between rank () and Dense_rank ()?
▹ RANK() assigns the same rank to rows with equal values, leaving gaps.
▹ DENSE_RANK() assigns the same rank to equal values without gaps, resulting in consecutive ranks.
Example:
SELECT v,
ROW_NUMBER() OVER (ORDER BY v) row_number,
RANK() OVER (ORDER BY v) rank,
DENSE_RANK() OVER (ORDER BY v) dense_rank
FROM t
ORDER BY v;
What are Pseudo columns in Oracle?
The pseudo columns are just like table columns. There are two types of Pseudo columns available in Oracle. They are as follows.
▹ ROWID
▹ ROWNUM
▹ ROWID Pseudo Column in Oracle:
- A new row inserts into a table
- Unique id address/number for each row-wise.
- Store in database
Ex: SELECT ROWID, ENAME FROM EMPLOYEE;
▹ ROWNUM Pseudo Column in Oracle:
- Generate numbers to each row wise / group of rows wise.
- Not saved in database (temporary).
Ex: SELECT ROWNUM, ENAME FROM EMPLOYEE;
What is a Synonym?
A Synonym is an alternative name for tables, views, procedures and other database objects.
Generally, when we have more than one schema and we wish to access an object of a different schema then we create synonyms for that object in the schema where we wish to access the object.
Example:
CREATE SYNONYM emp FOR HR.EMPLOYEES;
What are the different types of Synonyms?
There are two types of Synonyms or alias:
- Private: It can only be accessed by the owner.
- Public: It can be accessed by any database user.
What are the advantages of having Synonyms?
Synonyms are often used for security and convenience.
For ex, they can do the following:
- Mask the name and owner of an object.
- Provide location transparency for remote objects of a distributed database.
- Simplify SQL statements for database users.
What is the significance of Materialized Views in data warehousing?
In data warehouses, materialized views are used to precompute and store aggregated data such as sums and averages. Materialized views in these environments are typically referred to as summaries because they store summarized data. They can also be used to precompute joins with or without aggregations.
Cost-based optimization can use materialized views to improve query performance by automatically recognizing when a materialized view can and should be used to satisfy a request. The optimizer transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables or views.
What are Set operators in Oracle?
There are 4 Set operators in Oracle:
▹ UNION ALL
▹ UNION
▹ INTERSECT
▹ MINUS
Rule to use Set operators: The NUMBER OF COLUMNS, ORDER OF THE COLUMNS, DATATYPES OF THE COLUMNS should be SAME.
What is Union All in Oracle SQL?
UNION ALL Operator is used to combine result set of two or more SELECT queries.
The UNION ALL operator does not remove duplicate rows from SELECT statement result set.
UNION and UNION ALL operators works same. Only difference is UNION operator exclude duplicate rows from result set.
Example:
SELECT first_name, last_name, email, 'contact' FROM contacts;
UNION ALL
SELECT first_name, last_name, email, 'employee' FROM employees;
What is Union in Oracle SQL?
- The UNION operator is used to combine result set of two or more SELECT queries.
- The UNION operator automatically removes duplicate rows from SELECT statement result set.
Example:
SELECT first_name, last_name, email, 'contact' FROM contacts;
UNION
SELECT first_name, last_name, email, 'employee' FROM employees;
What is intersect in Oracle SQL?
- The INTERSECT operator returns common result set of two or more SELECT queries.
- It returns all common records that are exist in the result set of all INTERSECT sub-queries.
Example:
SELECT first_name, last_name, email, 'contact' FROM contacts;
INTERSECT
SELECT first_name, last_name, email, 'employee' FROM employees;
What is Minus in Oracle SQL?
- The MINUS operator returns all the records in the first SELECT query that are not returned by the second SELECT query.
- It will give All the Records from First query which are not in second Query.
Example:
SELECT first_name, last_name, email, 'contact' FROM contacts;
Minus
SELECT first_name, last_name, email, 'employee' FROM employees;
Differentiate between the WHERE clause and the HAVING clause in Oracle SQL?
- The WHERE clause filters rows before grouping – that is, before they’re included in the result set. Filtering is also based on certain conditions.
- The HAVING clause, on the other hand, filters data post-grouping – meaning after aggregation.
Example:
SELECT DEPARTMENT_ID, JOB_ID, MAX(SALARY)
FROM HR.EMPLOYEES
WHERE job_id LIKE '%REP%'
GROUP BY DEPARTMENT_ID, JOB_ID
HAVING JOB_ID='MK_REP';
What are SQL functions? Describe in brief different types of SQL functions?
SQL Functions are a very powerful feature of SQL. These functions can take arguments but always return some value. There are two distinct types of SQL functions available.
They are:
- Single-Row functions: These functions operate on a single row to give one result per row.
Types of Single-Row functions are:
▹ Character
▹ Number
▹ Date
▹ Conversion
▹ General - Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.
Types of Multiple-Row functions:
▹ avg
▹ count
▹ max
▹ min
▹ sum
▹ stddev
▹ variance
What is the use of Aggregate functions in Oracle?
An aggregate function in Oracle is a function where values of multiple rows or records are joined together to get a single value output. It performs the summary operations on a set of values in order to provide a single value. There are several aggregate functions that you can use in your code to perform calculations.
Some common Aggregate functions are:
▹ Average
▹ Count
▹ Sum
▹ Min
▹ Max
If we have Having, Where, Order by, Group by statements in Oracle, What is the order you will follow?
The below is the order:
▹ Where
▹ Group by
▹ Having
▹ Order by
Example:
SELECT DEPARTMENT_ID,JOB_ID ,MAX(SALARY)
FROM HR.EMPLOYEES
WHERE job_id LIKE '%REP%'
GROUP BY DEPARTMENT_ID, JOB_ID
HAVING JOB_ID='MK_REP'
ORDER BY DEPARTMENT_ID, JOB_ID;
Describe different types of General Function used in SQL?
General functions are of following types:
- NVL: Converts a null value to an actual value. NVL (exp1, exp2). If exp1 is null then NVL function return value of exp2.
- NVL2: If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)
- NULLIF: Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)
- COALESCE: Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over NVL function is that the COALESCE function can take multiple alternative values.
- Conditional Expressions: Provide the use of IF-THEN-ELSE logic within a SQL statement.
Example:
CASE Expression and DECODE Function.
What is an NVL function? How can it be used?
NVL is a function that helps the user to substitute value if null is encountered for an expression.
Example:
NVL (Salary,0)
Why do we use COALESCE function in Oracle?
COALESCE function is used to return the first non-null expression from the list of arguments provided in the expression. There must be a minimum of two arguments in an expression.
Example:
SELECT COALESCE (NULL, 'X', 'Y') AS RESULT;
Output will be X
Few Oracle SQL Queries asked in Informatica Interview
Write a query to Display Odd/ Even number of records
Odd number of records:
Select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
Even number of records:
Select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp);
How do you return the top 5 highest salaries in Oracle?
SELECT * FROM
(
SELECT *FROM emp
ORDER BY Salary desc
)
WHERE rownum <= 5
ORDER BY Salary;
Query to retrieve every Nth row from an Oracle table?
Select * from my_table where (rowid,0) in (Select rowid, mod(rownum,n) from my_table);
How does one eliminate or delete duplicate rows in an Oracle Table?
You can delete the duplicates by using with any of the examples below,
Example 1:
delete from emp e1
where rowid not in
(select max(rowid) from emp e2
where e1.empno = e2.empno );
Example 2:
delete from emp
where rowid not in
(select max(rowid) from emp group by empno);
Example 3:
delete from emp where rowid in
(
select rid from
(
select rowid rid,
row_number() over(partition by empno order by empno) rn
from emp
)
where rn > 1
);
Query to retrieve Nth row from an Oracle table
Select * from my_table where rownum <= n
MINUS
Select * from my_table where rownum < n;
Write an Oracle SQL query to find the average salary of employees within each department.
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;
Write an Oracle SQL query to find employees who earn more than their managers
SELECT emp.* FROM Employee emp
INNER JOIN Employee mgr ON emp.manager_id = mgr.employee_id
WHERE emp.salary > mgr.salary;
How to convert a string to a date in Oracle database?
Syntax: to_date (string, format)
Example:
select to_date ('2012-12-12', 'YYYY/MM/DD') from Dual;
How do you find current date and time in Oracle?
The SYSDATE() function is used in Oracle to find the current date and time of operating system on which the database is running.
Example:
SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Current_Date" FROM DUAL;
What will be the syntax to find current date and time in format "YYYY-MM-DD"?
Example:
SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "Current_Date" FROM DUAL;
How can we find the duplicate rows in an Oracle table?
We can use the below example query to fetch the duplicate records.
SELECT COUNT(*), Emp_id, Empname, Salary
FROM EMP
GROUP BY EMP_NAME
HAVING COUNT(*)>1;
Write a query to get 5th RANK students from the table STUDENT_REPORT?
SELECT TOP 1 RANK
FROM (SELECT TOP 5 RANK
FROM STUDENT_REPORT
ORDER BY RANK DESC) AS STUDENT
ORDER BY RANK ASC;
Write a query to get departments where any manager is managing more than 5 employees
SELECT DISTINCT DEPARTMENT_ID
FROM hr.EMPLOYEES
GROUP BY DEPARTMENT_ID, MANAGER_ID
HAVING COUNT(EMPLOYEE_ID) > 5
Write a query to get manager ID and number of employees managed by the manager.
SELECT MANAGER_ID, COUNT(*) FROM HR.EMPLOYEES GROUP BY MANAGER_ID;
Write a query to get how many employees joined in each month of the current year.
SELECT TO_CHAR(HIRE_DATE,'MM'), COUNT (*) FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'YYYY') = TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(HIRE_DATE,'MM')
Write a query to get manager ID and number of employees managed by the manager.
SELECT MANAGER_ID, COUNT(*) FROM HR.EMPLOYEES GROUP BY MANAGER_ID;
Leave a comment