Ron

• 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.

pavithra T.M

• 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

pavi

• Sep 18th, 2006

 

write the query hike the sal of employee

1 year experence 10%

less than one year experence 5%

Jagan

• Oct 12th, 2006

 

select salary

from (select rownum rank, salary

        from ( select salary f

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

Jagan

• 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

Ratheesh

• Jun 1st, 2007

 

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

dj_dj_dj

 Profile Answers by dj_dj_dj 

• Aug 13th, 2009

 

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.

aswini kumar das

• Nov 16th, 2011

 

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

Verdict

 Profile Answers by Verdict 

• Jan 20th, 2012

 

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

nehu

• 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
 


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;


/


 

nehu

• Feb 15th, 2012

 

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;


/

KALYAN KUMAR

• Feb 23rd, 2012

 

SELECT*FROM EMP WHERE ENAME LIKE S%T;

mksakeesh

 Profile Answers by mksakeesh 

• Mar 6th, 2012

 

Window function

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

priyank

• May 10th, 2012

 

Use Case in update statement

mahesh

• Aug 12th, 2013

 

Code
SELECT* FROM (SELECT RANK() 


over(ORDER BY price DESC) 


odrd,products.* FROM products) 


WHERE odrd =5;