GeekInterview.com
Series: Subject: Topic:
Question: 63 of 89

SQL Query to find Nth maximum of Nth row?

1. Queries for Nth maximum Nth row?2. In One table there are 5 fields. empno, ename, deptcd, managr_id, salary.Select the departments whose sum of the salary greater than the sum of salaries of any department?3. When index will be usd in the Query?
Asked by: Interview Candidate | Asked on: Jul 14th, 2006
Showing Answers 1 - 14 of 14 Answers
Ron

Answered On : Jul 21st, 2006

1. Queris for Nth maximum Nth row?

Select * from (select column_name1, column_name1 from table_name order by column_name) where rownum <= N


2. In One table there are 5 fields. empno, ename, deptcd, managr_id, salary.
Select the departments whoose sum of the salary greater than the sum of salaries of any department?

Select deptcd, sum(salary) from table_name group by deptcd orderby salary desc

3. When index will be usd in the Query?
Index is used by the Optimizer when a query is fired in Oracle.

Yes  1 User has rated as useful.
  
Login to rate this answer.
pavithra T.M

Answered On : Sep 18th, 2006

how to find starting letters in names in sql query

e.g sachin tendulkar

i want to display S & T in this name

  
Login to rate this answer.
pavi

Answered On : Sep 18th, 2006

write the query hike the sal of employee

1 year experence 10%

less than one year experence 5%

  
Login to rate this answer.
Jagan

Answered On : Oct 12th, 2006

select salary

from (select rownum rank, salary

        from ( select salary f

 rom emp order by salary desc))where rank=&N
/

  
Login to rate this answer.
Jagan

Answered On : Oct 12th, 2006

select salary

     from(select rownum rank, salary

            from ( select salary

                  from emp order by salary desc))

                           where rank=&N
/

this should do the job . . . exactly what you are asking

  
Login to rate this answer.
Ratheesh

Answered On : Jun 1st, 2007

select a.dept from emp a ,emp b
having sum(a.salary)>sum(b.salary)
group by a.dept

  
Login to rate this answer.
dj_dj_dj

Answered On : Aug 13th, 2009

View all answers by dj_dj_dj

RE: 1. Queris for Nth maximum Nth row?2. In One table there are 5 fields. empno, ename, deptcd, managr_id, salary.Select the departments whoose sum of the salary greater than the sum of salaries of any department?3. When index will be usd in the Query?*))select department, max(sum(salary)) from table_name group by department *) Function based index will be used for the above querry.

Yes  1 User has rated as useful.
  
Login to rate this answer.
aswini kumar das

Answered On : Nov 16th, 2011

select * from hr.employees e
where n-1 = (select count(salary) from hr.employees where e.salary < salary)

  
Login to rate this answer.
Verdict

Answered On : Jan 20th, 2012

View all answers by Verdict

Code
  1. SELECT emplid, Sal,
  2. Case when Total_experience = 1 then (Sal+Sal*0.10)
  3. end AS Salary
  4. FROM Employee
  5.  
  6. SELECT emplid, Sal,
  7. Case when Total_experience < 1 then (Sal+Sal*0.05)
  8. end AS Salary
  9. FROM Employee

  
Login to rate this answer.
nehu

Answered On : Feb 14th, 2012

The query to your second question will return all the departments even that department whose total salary is not greater than any department. I guess if the question is to find all departments whose total is greater than any department then the query should be

Code
  1.  
  2. declare
  3. cursor c1 IS SELECT department,sum(salary) total FROM emp GROUP BY department;
  4. cursor c2 IS SELECT department,sum(salary) total FROM emp GROUP BY department;
  5. /* hv declared the same cursor bcoz we cannt access the already open cursor in second loop*/
  6. begin
  7. FOR cur IN c1
  8. loop
  9. FOR cur1 IN c2
  10. loop
  11. IF cur.total>cur1.total
  12. then
  13. INSERT INTO result VALUES(cur.department);
  14. /* result is a table which store values of desired departments*/
  15. end IF;
  16. end loop;
  17. end loop;
  18. end;
  19. /
  20.  

  
Login to rate this answer.
nehu

Answered On : Feb 15th, 2012

The query to hike the sal of employee 1 year experience 10% less than one year experience 5% :-

Code
  1. declare
  2. cursor c1 IS SELECT experience,empno FROM emp;/* empno is a unique key in table emp*/
  3. begin
  4. FOR cur IN c1
  5. loop
  6. IF(cur.experience<1) then
  7.  UPDATE emp SET salary=salary+0.05*salary WHERE empno=cur.empno;/* it is necessary to put where clause here otherwise all salaries in table emp will be updated*/
  8. else
  9. UPDATE emp SET salary=salary+0.1*salary WHERE empno=cur.empno;
  10. end IF;
  11. end loop;
  12. end;
  13. /

  
Login to rate this answer.
KALYAN KUMAR

Answered On : Feb 23rd, 2012

SELECT*FROM EMP WHERE ENAME LIKE S%T;

  
Login to rate this answer.
mksakeesh

Answered On : Mar 6th, 2012

View all answers by mksakeesh

window function

Code
  1. SELECT* FROM (SELECT rank() over(ORDER BY price DESC) odrd,products.* FROM products) WHERE odrd =5;

  
Login to rate this answer.
priyank

Answered On : May 10th, 2012

Use Case in update statement

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Question Categories

Oracle Backup and Recovery Interview Questions

Oracle Basics Interview Questions

Oracle Architecture Interview Questions

D2K Interview Questions

Data Access Interview Questions

Oracle DBA Interview Questions

Oracle security Interview Questions

Database Tuning Questions

Oracle Distributed Processing Interview Questions

Oracle Concepts Interview Questions

Oracle Memory Management Interview Questions

Oracle Forms Interview Questions

PL/SQL Interview Questions

Programmatic Constructs Interview Questions

RMAN Interview Questions

Real time Oracle Interview Questions

Oracle SQL Interview Questions

SQL*Plus interview Questions

Oracle System Architecture Interview Questions

Interview Question

 Ask Interview Question?

 

Latest Questions

Ads

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.