Complex SQL Queries Interview Questions

[Mr. Anand]

Hi,

This post shall be covering some of the most complex real time
situation we come across in our database testing . Let us have a go
through each of these quality sql queries and gain more on the
technical front for the same.

1. Determine the name sex and age of the oldest student.
SELECT Name, Gender, (CURRENT_DATE-Dtnaiss)/365 AS Age
FROM Student
WHERE (CURRENT_DATE-Dtnaiss) /365 =
( SELECT MAX(( CURRENT_DATE-Dtnaiss) /365) FROM Student);


2. Display the marks of the student number 1 which are equal to the
marks of the student number 2.
SELECT Note
FROM NOTES
WHERE Num=1 AND Note IN
(SELECT Note FROM NOTES WHERE Num=2);

3. Finding the names of everybody who works in the same department as
a person called James
SELECT name FROM emp WHERE dept_no =
(SELECT dept_no FROM emp WHERE name = 'James')
or as a join statement, like this:-
SELECT e1.name FROM emp e1,emp e2
WHERE e1.dept_no = e2.dept_no AND e2name = 'James'


4. The SQL statement to find the departments that have employees with
a salary higher than the average employee salary
SELECT name FROM dept
WHERE id IN
(
SELECT dept_id FROM emp
WHERE sal >
(SELECT avg(sal)FROM emp)
)

Continued at http://www.articlesontesting.com/2011/07/common-sql-queries-interview-questions.html