Question { Infosys, 6935 }

Q) How to Find Max Date from each Group? (Asked in Infosys
(INFI)Interview)

Answer

AS OUR EXAMPLE HR SCHEMA FOR GROUP WISE MAX DATE...

SELECT * FROM HR.EMPLOYEES
WHERE HIRE_DATE IN
(SELECT MAX(HIRE_DATE) FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID);

FOR SINGLE ROW MAX DATE...
SELECT * FROM
(
SELECT * FROM HR.EMPLOYEES
ORDER BY HIRE_DATE DESC)
WHERE ROWNUM = 1;

Question { IBM, 5869 }

how to retrive xml data for using sql query?

Answer

SELECT XMLELEMENT
("Emp", XMLELEMENT("name", e.fname ||' '|| e.lname),
XMLELEMENT ( "hiredate", e.hire))
AS "RESULT"
FROM employees e
WHERE employee_id > 200 ;


This query produces the following typical XML result:

RESULT
-----------------

John Smith
2000-05-24


Mary Martin
1996-02-01