OraFAQ Forum: SQL & PL/SQL » Question related to Sql query

Home » SQL & PL/SQL » SQL & PL/SQL » Question related to Sql query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tue, 05 December 2006 11:34

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?

Report message to a moderator

Tue, 05 December 2006 11:56

srinivas.k2005
Messages: 404
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;

Report message to a moderator

Tue, 05 December 2006 14:12

g_p_
Messages: 6
Registered: December 2006

Junior Member

rownum is sthg like avg,sum ?

Thanks Smile

Report message to a moderator

Tue, 05 December 2006 14:47

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?

Report message to a moderator

Tue, 05 December 2006 14:49

g_p_
Messages: 6
Registered: December 2006

Junior Member

where Teacher.tid = Lesson_teach.tid and Teacher.tid= 12

Report message to a moderator

Tue, 05 December 2006 14:52

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.

Report message to a moderator

Wed, 06 December 2006 13:30

g_p_
Messages: 6
Registered: December 2006

Junior Member

Hi,
can anybody help me?

Report message to a moderator

Sun, 10 December 2006 18:28

Barbara Boehmer
Messages: 9100
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.

Report message to a moderator

Mon, 11 December 2006 07:49

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.

Report message to a moderator

Previous Topic:	Menu Display Logic Using Tables/Procedures
Next Topic:	Out and conditional control

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 19:34:39 CST 2024