1) What is a level of Granularity of a fact table?

Level of granularity means level of detail of data available in the fact table

For an instance

in the fact table sales data may available at minute level or day level or month level....

High Granularity: data available at low level EX: Day level.

Low Granularity: data available at high level EX: Month level.

2) DIFFERENCE BETWEEN CO-RELATED SUB QUERY AND NESTED SUB QUERY

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

For example,

Correlated Subquery:

Select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

Select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno

LIST OF COMMON JOBS IN TWO DEPARTMENRTS

SORTING & FETCHING TOP N RECORDS AS PER SALARY

--------- ---------------------------

-------------- --------------------- ------------------

COMPLEX SQL QUERIES

1. To find the nth row of a table 

SQL> Select *from emp where rowid = (select max(rowid) from emp where rownum  

        <= 4); 

Or 

   SQL> Select *from emp where rownum <= 4 minus select *from emp where rownum  

           <= 3; 

2. To find duplicate rows 

SQL> Select *from emp where rowid not in (select max(rowid) from emp group by  

         empno, ename, mgr, job, hiredate, comm, deptno, sal); 

  Or 

 SQL> Select empno,ename,sal,job,hiredate,comm , count(*) from emp group by  

         empno,ename,sal,job,hiredate,comm  having count(*) >=1;  

  

3. To delete duplicate rows 

      SQL> Delete from emp where rowid not in (select max(rowid) from emp group by  

              empno,ename,mgr,job,hiredate,sal,comm,deptno); 

  

4. To find the count of duplicate rows 

      SQL> Select ename, count(*) from emp group by ename having count(*) >= 1; 

5. How to display alternative rows in a table? 

          SQL> select *from emp where (rowid,0) in (select rowid,mod(rownum,2) from emp); 

6. Getting employee details of each department who is drawing maximum sal? 

       SQL> select *from emp where (deptno,sal) in 

      ( select deptno,max(sal)  from emp group by deptno); 

7. How to get number of employees in each department  , in which department is having more than 2500 employees? 

       SQL> Select deptno,count(*) from emp group by  deptno having count(*) >2500; 

8. To reset the time to the beginning of the day 

                  SQL> Select to_char(trunc(sysdate),’dd-mon-yyyy hh:mi:ss am’) from dual; 

9. To find nth maximum sal 

  SQL> Select *from emp where sal in (select max(sal) from (select *from emp order  

          by sal) where rownum <= 5); 

10. Which Employees are not Managers? 

SQL> select * from Emp e where e.empno not in ( select mgr from emp e1 where e.empno= e1.mgr); 

11. Display Names who earns highest Salary? 

SQL> Select ename, sal from emp where sal > ( select max(sal) from emp); 

12. Display Em- from Dept 10 & Greater than any of employee working in other Dept. 

SQL> Select ename, Dept, Sal  from emp where sal > ( select max(sal) from emp where deptno = 10) and deptno = 10 group by deptno ; 

13. Display Employees whose salary is more than their Managers 

SQL> select e.Sal, e1.sal, e.ename, e1.ename from emp e, emp e1 where e.ename=e1.mgr and e.sal>e.sal; 

14. Which Job group having total salary greater than Max Sal for Manager 

SQL> select ename, Job,sum(Sal) from emp group by ename, Job having sal> (select max(sal) from emp where job= ‘Manager’); 

15. Display common jobs in Dept 10, 20 

SQL> select Job from emp where job in ( select job from emp where deptno=20) and deptno=10; 

16. Display top 5 Employees ( Designation) 

SQL> Select level, ename, job, mgr from emp where level<=5 connect by prior empno=mgr start with mgr is null; 

17. Display the Employees who joined in same day? 

SQL> select e.ename, e1.ename from emp e,e1 where e.hiredate = e1.hiredate and e.ename != e1.ename 

18) Display Odd Records and Even Records separately.

Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5

Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6

  

1) How to Get the Difference between 2 Timestamps Into a New Column? 

  

Sol: 

TO_TIMESTAMP( your_column2, 'MM/DD/YYYY HH24:MI') -  TO_TIMESTAMP( your_column1, 'MM/DD/YYYY HH24:MI' 

  

OR 

  

( TO_DATE( your_column2, 'MM/DD/YYYY HH24:MI') -  TO_DATE( your_column1, 'MM/DD/YYYY HH24:MI') ) * 24 

  

2) I need to replace multiple commas  input:  11,0000,AB,015,1111,,,066,5555,,,901,6666,,100,,40 0,7777,,,,5,V,140609,   Output:  11,0000,AB,015,1111,,,~066,5555,,,,,~901,6666,,~10 0,,400,7777,,,~5,V,140609,  

  

SOL: 

replacestr(0,replacestr(0,replacestr(0,input_port, ',,,,',',~'),',,,',',~'),',,',',~') 

  

3) Sorting the columns using multiple columns 

select * 

from (select col1, col2, col3, row_number() over(order by col1, col2, col3) rn 

from ... 

where ... 

) 

where rn < 300 ; 

 1) i have a table like this emp id empname empse  12 ram 1000  13 rohan 1200  14 mohan 1300   i want to output like that    emp id empname empse empadd  12 ram 1000 1000  13 rohan 1200 2200  14 mohan 1300 2500     Sol: You can do it as follows:   select emp_id, empname, empse,  sum(empse)  over(order by emp_id  rows between 1 preceding and current row)  from your_table  order by emp_id  /   or, doing the same with a shorter syntax:   select emp_id, empname, empse,  sum(empse)  over(order by emp_id  rows 1 preceding )  from your_table  order by emp_id  /   You should be careful and specify exactly your required ORDER BY  inside the analytic SUM function.  

1) How to Get the Difference Between 2 Timestamps Into a New Column? 

Sol: 

TO_TIMESTAMP( your_column2, 'MM/DD/YYYY HH24:MI') -  TO_TIMESTAMP( your_column1, 'MM/DD/YYYY HH24:MI' 

OR 

( TO_DATE( your_column2, 'MM/DD/YYYY HH24:MI') -  TO_DATE( your_column1, 'MM/DD/YYYY HH24:MI') ) * 24 

2) I need to replace multiple commas  input:  11,0000,AB,015,1111,,,066,5555,,,901,6666,,100,,40 0,7777,,,,5,V,140609,   Output:  11,0000,AB,015,1111,,,~066,5555,,,,,~901,6666,,~10 0,,400,7777,,,~5,V,140609,  

  

SOL: 

replacestr(0,replacestr(0,replacestr(0,input_port, ',,,,',',~'),',,,',',~'),',,',',~') 

3) Sorting the columns using multiple columnsselect * from (select col1, col2, col3, row_number() over(order by col1, col2, col3) rn from ... where ... ) where rn < 300 ;   3)Retrieve Only Duplicate Records  

Sol:  

SELECT * FROM emp aWHERE rowid = (SELECT max(rowid) FROM emp  WHERE empno = a.empno  GROUP BY empno  HAVING count(*) >1) 

 4) How can we remove the Duplicate records in Oracle? 

  

SOL: 

  

SYNTAX 

delete from <table_name> where rowid not in                     ( select min(rowid)                       from exp group by column1..,column2,...column3..); 

  

EX: 

delete from EMP where rowid not in                     ( select min(rowid)                       from EMP group by Student_Name); 

=====================