some tricky SQL query

SQL query sometimes go very interesting. With a little bit trick you can do a lot with it. It is like playing a game with words!
To have some fun first download this sql file from http://kuetcse2k5.googlepages.com/course.SQL
and execute it in your DBMS.

Now try to answer the following:
(Presented from a set of question by
Rushdi Shams,
Lecturer,Dept of CSE,KUET,BD
http://rushdishams.googlepages.com/
)

1. See all the data in instructor table. Use only SUM and COUNT aggregate functions to find out the average salary of the instructors. The answer should be 3791.66667. Validate the answer by using AVG aggregate function.

2. See all the data in site table. Find out the site_id for the location named WASHINGTON. Use LIKE clause. Answer would be 6.

3. From instructor table, find all the instructors and their mentor’s name (hint: requires a self-join or table aliasing). The output will be-

INSTRUCTOR_NAME	MENTOR_NAME
SHELLEY	WAYNE
BOGART	WAYNE
NEWMAN	WAYNE
MONROE	WAYNE
STEEL	CAINE
SPARKS	STEEL
LAUREL	STEEL
LODGE	SPARKS
JOHNSON	SPARKS
TUCKER	SPARKS

4. From instructor table, find all the instructors that have no mentors. The output will be-

INSTRUCTOR_NAME

WAYNE

CAINE

5. From instructor table, find the names who are mentors (hint: you will have to make nested query). The output will be-

INSTRUCTOR_NAME

WAYNE

CAINE

STEEL

SPARKS

6. From instructor table, find the IDs who are not mentors (hint: you may use set operators here). The output will be-

INSTRUCTOR_ID
243
263
453
515
560
628
790

7. in plain English, tell what the following query is doing by running the query and seeing the result. Remember- in plain English!

select a.id, a.value, b.value from data a, data b where a.name_id=9 and a.id=b.id and a.prefix!=b.prefix; commit;

ANSWERS:
-->1
select sum(salary)/count (salary) from instructor;

-->2
select site_id from site where location like '%WASHINGTON%';

-->3
select i.instructor_name ,m.instructor_name "MENTOR_NAME"
from instructor i join instructor m
on i.mentor_id=m.instructor_id;

-->4
select instructor_name from instructor where mentor_id is null;

-->5
select instructor_name from
instructor where instructor_id in
(select mentor_id from instructor where mentor_id is not null)
order by instructor_name;

-->6
select instructor_id from
instructor where instructor_id not in
(select mentor_id from instructor where mentor_id is not null)
order by instructor_id;

-->7
-->first check the query:
select a.mentor_id, a.salary, b.salary from
instructor a, instructor b
where a.instructor_id=243 and a.mentor_id=b.mentor_id and a.instructor_name!=b.instructor_name;

--> it will select those entries with
--> the name_id=9 and has same id for two different entries.

So check these out!

this blog will be removed! soon

Thanks...