| Simple SQL Query. [message #20716] |
Fri, 14 June 2002 20:30  |
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  |
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 |
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()
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
