Home » SQL & PL/SQL » SQL & PL/SQL » Simple SQL Query.
Simple SQL Query. [message #20716] Fri, 14 June 2002 20:30 Go to next message
karunamoorthy_p
Messages: 13
Registered: April 2002
Junior Member
Could you please help me to solve the following..
Database consists of the following tables.
STUDENT(S#, SNAME)
COURSE(C#, TITLE, TEACHER_NAME)
RESULT(S#, C#, MARKS)

Queries:-
1) List of students who appear in all courses taught by a teacher named "PKM" and scored more than 60 marks.

2) The subject title(s) in which there are maximum failures. (passing marks in a subject is 40).

3) Name(s) of student(s) obtaining highest marks in course number(C#) is 101.
Re: Simple SQL Query. [message #20733 is a reply to message #20716] Sun, 16 June 2002 15:02 Go to previous messageGo to next message
Su
Messages: 154
Registered: April 2002
Senior Member
Here they are.
1)
SELECT S.S#, S.SNAME, R.C#, R.MARKS FROM STUDENT S, RESULT R WHERE S.S#=R.S# AND R.MARKS>60 AND NOT EXISTS
(SELECT C# FROM COURSE WHERE TEACHER_NAME = 'PKM'
MINUS
SELECT C# FROM RESULT R_R WHERE R_R.S# = R.S#);

The subquery should not return any row as the MINUS operator works out to return if there are any unmatching rows. So here the subquery confirms that the student, who is being checked in the main query, attended all of the courses offered by PKM and there are no rows (courses) which student did not attend.

2)SELECT C#, CNT FROM (SELECT R.C#, COUNT(*) CNT FROM RESULT R WHERE MARKS<40 GROUP BY C#) WHERE CNT = (
SELECT MAX(CNT) FROM (SELECT COUNT(*) CNT FROM RESULT R WHERE MARKS<60 GROUP BY C#))

Here I used a virtual table which extracts only the desired data from the actual physical table first, and then compares its each course's count with the maximum count found in the subquery. The subquery extracts only the maximum count out of which it uses a virtual table for finding out the count for each course (by counting marks records).

3) I am not sure what you are asking to return Name(s) of student(s) obtaining highest marks. Is that you want to see only one who obtained highest marks (the top and highest) or do you want to top 3 (or more) highest scorers?
If it is that you want see the highest scored for C#=101, here it is...
SELECT S.S#, S.SNAME, R.MARKS FROM STUDENT S, RESULT R WHERE S.S# = R.S# AND R.MARKS = (SELECT MAX(MARKS) FROM MYP_RESULT R_R WHERE C# = 12)

Otherwise, you want to see top 3 rankers for the course 101, here is the one too...
SELECT S.S#, S.SNAME, R.MARKS FROM STUDENT S, RESULT R WHERE S.S# = R.S# AND
R.MARKS = ANY(SELECT DISTINCT MARKS FROM RESULT WHERE C# = 12 AND ROWNUM < 4
)ORDER BY R.MARKS DESC

You can change the number for ROWNUM if you want to see other number of top rankers. But remember, dont ever give = with ROWNUM. So if you want to see top 5, give ROWNUM < 6, but not ROWNUM <= 5; And remember if there are students who scored same in the top, this query returns those numbers too.

Check it out and let me know if you still need any help. (I guess you are a student or not experienced). Take it easy. Good luck :)
Re: Simple SQL Query. [message #20741 is a reply to message #20716] Mon, 17 June 2002 06:31 Go to previous message
x
Messages: 5
Registered: December 2000
Junior Member
1.select st.sname from student st,course c,result r where c.teacher_name = 'PKM' and r.marks > 60 group by st.sname
2.select c.title,r.marks from course c,result r where r.marks < 40 group by c.title,r.marks
3.use a rank function()
Previous Topic: ENTERING THE TIME
Next Topic: MONTHWISE PARTITION OF TABLE
Goto Forum:
  


Current Time: Sat Oct 20 17:13:00 CDT 2018