Home » SQL & PL/SQL » SQL & PL/SQL » Question related to Sql query
Question related to Sql query [message #207457] Tue, 05 December 2006 11:34 Go to next message
g_p_
Messages: 6
Registered: December 2006
Junior Member
Good evening,

I have created 4 tables

Student(iid , lname, address)
Teacher(tid, lname, salary, d_number)
Lesson_teach(tid,les_name)
Lesson_attend(iid,les_name)

I'm trying to find the second biggest salary of the teachers

I do:

select distinct T.lname
from teacher T,teacher S
where T.salary > S.salary

Well,in the output i get all the teachers,but i want to take only one who has the second biggest salary.

What else shall i add in order to take the desirable output?
Re: Question related to Sql query [message #207460 is a reply to message #207457] Tue, 05 December 2006 11:56 Go to previous messageGo to next message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Better go for this

select * from
(select rownum r ,name,sal
from (select t.name
name,
t.salary sal
from teacher t
order by salary desc)) A
where A.r = 2;
Re: Question related to Sql query [message #207485 is a reply to message #207460] Tue, 05 December 2006 14:12 Go to previous messageGo to next message
g_p_
Messages: 6
Registered: December 2006
Junior Member
Hi Smile

rownum is sthg like avg,sum ?

Thanks Smile
Re: Question related to Sql query [message #207489 is a reply to message #207485] Tue, 05 December 2006 14:47 Go to previous messageGo to next message
g_p_
Messages: 6
Registered: December 2006
Junior Member
Thank you very much!

It worked,although the syntax of this sql is a bit difficult for me cause I'm new at sql Smile

Now,i'm trying to find the sudents who attend all the lessons of the processor whose tid = 12 (in relational algebra we use division for this query cause we have "all")

I do :

select Student.lname
from Student,Lesson_attend
where Student.iid = Lesson_attend.iid and Lesson_attend.lno = all(select Lesson_teach.lno
from Lesson_teach,Teacher
where Teacher.tid = Lesson_teach.iid and Teacher.tid= 2)

My tables are :

Student(iid , lname, address)
Teacher(tid, lname, salary, d_number)
Lesson_teach(tid,les_name,lno)
Lesson_attend(iid,les_name,lno) lno is lesson number


The program has to output one Student,cause in my insert.sql file there is only one Student who attends all of his lessons.

I get :no rows selected

Have i done sthg wrong and i don't get the desirable output?
Re: Question related to Sql query [message #207490 is a reply to message #207489] Tue, 05 December 2006 14:49 Go to previous messageGo to next message
g_p_
Messages: 6
Registered: December 2006
Junior Member
where Teacher.tid = Lesson_teach.tid and Teacher.tid= 12
Re: Question related to Sql query [message #207492 is a reply to message #207457] Tue, 05 December 2006 14:52 Go to previous messageGo to next message
g_p_
Messages: 6
Registered: December 2006
Junior Member
if i use any instead of all,I get every student who attends one of tis teacher's lessons or more.
Re: Question related to Sql query [message #207713 is a reply to message #207492] Wed, 06 December 2006 13:30 Go to previous messageGo to next message
g_p_
Messages: 6
Registered: December 2006
Junior Member
Hi,
can anybody help me?
Re: Question related to Sql query [message #208426 is a reply to message #207713] Sun, 10 December 2006 18:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
There are always various ways to do things. You might think in terms of comparing a count of that professor's classes that a student takes versus a count of the classes the professor teaches or you could check that there does not exist a class that the professor teaches that the student does not take.
icon3.gif  Re: Question related to Sql query [message #208588 is a reply to message #207460] Mon, 11 December 2006 07:49 Go to previous message
oracle_faqs
Messages: 5
Registered: December 2006
Location: New Delhi
Junior Member

I think there is some problem in ur query. If there r 3 teachers withdrawing salary which is highest in amount then it will give name of a teacher from 2nd row which is actually withdrawing highest salary, instead of 2nd highest.
Previous Topic: Menu Display Logic Using Tables/Procedures
Next Topic: Out and conditional control
Goto Forum:
  


Current Time: Sun Dec 04 08:57:06 CST 2016

Total time taken to generate the page: 0.08107 seconds