Oracle TOP interview question :)

Oracle TOP interview question :)

1)How to find the Nth highest salary from a table in SQL?

Solution:
Query to find the Nth highest salary:

SELECT DISTINCT  (a.sal) 
FROM EMP A 
WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) 
            FROM EMP B 
            WHERE a.sal<=b.sal)

Replace &N in the above query with 2 if you want to know the second highest salary and so on.

1)How to display employee records who gets more salary than the average salary in the department?

Select * from employee where salary>(select avg(salary) from dept, employee where dept.deptno = employee.deptno;

2)

Difference between View vs Materialized View in database

Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :

1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

2) Another difference between View vs materialized view is that, when we create view using any table,  rowid of view is same as original table but in case of Materialized view rowid is different.

3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

4) Performance of View is less than Materialized view.

5) This is continuation of first difference between View and Materialized View, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table

6) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

When to Use View vs Materialized View in SQL

Mostly in application we use views because they are more feasible,  only logical representation of table data no extra space needed. We easily get replica of data and we can perform our operation on that data without affecting actual table data but when we see performance which is crucial for large application they use materialized view where Query Response time matters so Materialized views are used mostly with data ware housing or business intelligence application

Difference between Clustered and Nonclustered Indexes in SQL

What is Clustered and Nonclustered index and How they resolve physical location of the record. Let's see some more difference between them to answer this SQL interview question :

1) Clustered Index physically sort all rows while Nonclustered Index doesn't.

2) In SQL, one table can only have one Clustered  Index but there is no such restriction on NonClustered Index.

3) In many relational databases, Clustered Index is automatically created on primary key column.

How will you delete duplicating rows from a base table

DELETE FROM table_name A WHERE rowid>(SELECT min(rowid) from table_name B where B.table_no=A.table_no);

1) How do you find MONTH and YEAR from a date in Oracle?
Answer : By using EXTRACT function, you can use it to get MONTH and YEAR from a DATE object. By the way, you can also use EXTRACT function to find number of months, years between two dates , as follows :

Extracting YEAR from DATE, and finding number of YEARS between dates :

SELECT EXTRACT(YEAR FROM TO_DATE('30/07/2011', 'DD/MM/YYYY')) TRADE_YEAR,
EXTRACT(YEAR FROM TO_DATE('30/10/2018', 'DD/MM/YYYY')) MATURITY_YEAR,
(EXTRACT(YEAR FROM TO_DATE('30/07/2011', 'DD/MM/YYYY')) - EXTRACT(YEAR FROM TO_DATE('30/10/2018', 'DD/MM/YYYY'))) NUM_OF_YEARS
FROM DUAL;

Result:
TRADE_YEAR MATURITY_YEAR NUM_OF_YEARS
2011 2018 -7

Similarly, you can find MONTH from DATE and calculate number of months between them :

---------------------------------------
SELECT EXTRACT(MONTH FROM TO_DATE('30/07/2011', 'DD/MM/YYYY')) TRADE_MONTH,
EXTRACT(MONTH FROM TO_DATE('30/10/2018', 'DD/MM/YYYY')) MATURITY_MONTH,
(EXTRACT(MONTH FROM TO_DATE('30/07/2011', 'DD/MM/YYYY')) - EXTRACT(MONTH FROM TO_DATE('30/10/2018', 'DD/MM/YYYY'))) NUM_OF_MONTHS
FROM DUAL;
----------------------------------------

Result :
TRADE_MONTH MATURITY_MONTH NUM_OF_MONTHS
7 10 -3

Write a SQL query to find the 10th highest employee salary from an Employee table. Explain your answer.

SELECT TOP (1) Salary FROM
(
    SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESCss

What is wrong with this SQL query? Correct it so it executes properly.

SELECT Id, YEAR(BillingDate) AS BillingYear 
FROM Invoices
WHERE BillingYear >= 2010;

The expression BillingYear in the WHERE clause is invalid. Even though it is defined as an alias in the SELECT phrase, which appears before the WHERE phrase, the logical processing order of the phrases of the statement is different from the written order. Most programmers are accustomed to code statements being processed generally top-to-bottom or left-to-right, but T-SQL processes phrases in a different order.

The correct query should be:

SELECT Id, YEAR(BillingDate) AS BillingYear
FROM Invoices
WHERE YEAR(BillingDate) >= 2010;

) AS Emp ORDER BY Salary

NVL AND NVL2

Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null.

With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1.

With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.




Q)When you use WHERE clause and when you use HAVING clause

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.

18. Which is more faster - IN or EXISTS

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

Appropriate answer will be....

Result of the subquery is small Then "IN" is typicaly more appropriate. and Result of the subquery is big/large/long Then "EXIST" is more appropriate




Q) Diff bw DENSE RANK() AND RANK().

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.




Q)How you will avoid your query from using indexes

SELECT * FROM emp Where emp_no+' '=12345;

i.e you have to concatenate the column name with space within codes in the where condition.

SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234;

i.e using HINTS

2nd highest salary in Oracle using ROW_NUMBER
Here is the SQL query to find the second highest salary in Oracle using row_number()

function:

select * from (select e.*, row_number() over (order by salary desc) as row_num from Employee e) 

where row_num = 2;Output:NAME    SALARY  ROW_NUMMr. B    5000     2

2nd maximum salary in Oracle using RANK
select * from (
select e.*, rank() over (order by salary desc) as rank from Employee e
) where rank = 2;





2nd highest salary in Oracle using DENSE_RANK
select * from (
select e.*, dense_rank() over (order by salary desc) as dense_rank from Employee e
) where dense_rank = 2;

To fetch ALTERNATE records from a table. (EVEN NUMBERED)select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
To select ALTERNATE records from a table. (ODD NUMBERED)select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
Find the 3rd MAX salary in the emp table.select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
Find the 3rd MIN salary in the emp table.select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
Select FIRST n records from a table.select * from emp where rownum <= &n;
Select LAST n records from a tableselect * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
List dept no., Dept name for all the departments in which there are no employees in the department.select * from dept where deptno not in (select deptno from emp);  
alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
How to get 3 Max salaries ?select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
How to get 3 Min salaries ?select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);
How to get nth max salaries ?
select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);
Select DISTINCT RECORDS from emp table.select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);
How to delete duplicate rows in a table?delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);
Count of number of employees in  department  wise.select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;

 Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?

select ename,sal/12 as monthlysal from emp;


Select all record from emp table where deptno =10 or 40.

select * from emp where deptno=30 or deptno=10;


Select all record from emp table where deptno=30 and sal>1500.

select * from emp where deptno=30 and sal>1500;


Select  all record  from emp where job not in SALESMAN  or CLERK.

select * from emp where job not in ('SALESMAN','CLERK');


Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.

select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');


Select all records where ename starts with ‘S’ and its lenth is 6 char.

select * from emp where ename like'S____';


Select all records where ename may be any no of  character but it should end with ‘R’.

select * from emp where ename like'%R';


Count  MGR and their salary in emp table.

select count(MGR),count(sal) from emp;


In emp table add comm+sal as total sal  .

select ename,(sal+nvl(comm,0)) as totalsal from emp;


Select  any salary <3000 emp="" font="" from="" nbsp="" table.="">

select * from emp  where sal> any(select sal from emp where sal<3000 font="">


Select  all salary <3000 emp="" font="" from="" nbsp="" table.="">

select * from emp  where sal> all(select sal from emp where sal<3000 font="">


Select all the employee  group by deptno and sal in descending order.

select ename,deptno,sal from emp order by deptno,sal desc;


How can I create an empty table emp1 with same structure as emp?

Create table emp1 as select * from emp where 1=2;


How to retrive record where sal between 1000 to 2000?
Select * from emp where sal>=1000 And  sal<2000 font="">


Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)


If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)


How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)


 How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)


Count the totalsa  deptno wise where more than 2 employees exist.
SELECT  deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2