SQL Query to find Nth maximum of Nth row?

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.

how to find starting letters in names in sql query

e.g sachin tendulkar

i want to display S & T in this name

write the query hike the sal of employee

1 year experence 10%

less than one year experence 5%

select salary

from (select rownum rank, salary

        from ( select salary f

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

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

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

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.

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

Code
SELECT emplid, Sal,
Case when Total_experience = 1 then (Sal+Sal*0.10) 
end AS Salary
FROM Employee
 
SELECT emplid, Sal,
Case when Total_experience < 1 then (Sal+Sal*0.05) 
end AS Salary
FROM Employee

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
 
declare
cursor c1 IS SELECT department,sum(salary) total FROM emp GROUP BY department;
cursor c2 IS SELECT department,sum(salary) total FROM emp GROUP BY department;
/* hv declared the same cursor bcoz we cannt access the already open cursor in second loop*/
begin
FOR cur IN c1
loop
FOR cur1 IN c2 
loop
IF cur.total>cur1.total
then
INSERT INTO result VALUES(cur.department);
/* result is a table which store values of desired departments*/
end IF;
end loop;
end loop;
end;
/
 

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

Code
declare
cursor c1 IS SELECT experience,empno FROM emp;/* empno is a unique key in table emp*/
begin
FOR cur IN c1
loop
IF(cur.experience<1) then
 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*/
else
UPDATE emp SET salary=salary+0.1*salary WHERE empno=cur.empno;
end IF;
end loop;
end;
/

SELECT*FROM EMP WHERE ENAME LIKE S%T;

window function

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

Use Case in update statement