Question { 12138 }

How to select the name of employee who is getting maximum
sal with out using subquery

Answer

select b.ename from emp b,(select max(sal) k from emp) a
where a.k=b.sal;

Question { Ford, 19949 }

select Nth highest salary by using rownum

Answer

SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM ( SELECT
DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<'&N');

ANS::

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
------ ---------- --------- ---------- --------- ----------
---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975
20

SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM ( SELECT
SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<'&N');



EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
------ ---------- --------- ---------- --------- ----------
---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7902 FORD ANALYST 7566 03-DEC-81 3000
20

Question { 4900 }

i have a customer table.
trans_id trans_date trans_amt debit_credit_indicator
001 01-JAN-13 1099 cr
001 12-JAN-13 500 db
002 24-FEB-13 400 db
002 23-MAR-13 345 cr
001 18-APR-13 800 cr
002 15-MAR-13 600 db
001 12-FEB-13 200 cr

i want like this output.

trans_id trans_amt debit_credit_indicator

i want get highest credit amount and lowest credit amount
and highest debit amount and lowest debit amount for each
trans_id.

pls give me answer. i want urgent

Answer

SELECT
trans_id,MAX(TRANS_AMOUNT),MIN(TRANS_AMOUNT),DEBIT_CREDIT_INDICATOR
FROM CUSTOMER WHERE DEBIT_CREDIT_INDICATOR='cr'
UNION ALL
SELECT
trans_id,MAX(TRANS_AMOUNT),MIN(TRANS_AMOUNT),DEBIT_CREDIT_INDICATOR
FROM CUSTOMER WHERE DEBIT_CREDIT_INDICATOR='db'

Question { Infosys, 7278 }

what is self join and how it works??

Answer

Joining the Table data it self is called self join,it will work based on the join condition