Help with writting SQL code.

Hi All,

I am having trouble generating SQL code and was hoping

one of you guys would help.



The schema is:

student(sid, sname, sex, age, year, gpa)

dept(dname, numphds)

prof(pname, dname)

course(cno, cname, sid)

major(dname, sid)

section(dname, cno, sectno, pname)

enroll(sid, grade, dname, cno, sectno)



The queries i need generated are:



1) Print the age difference between the oldest and youngest Computer Sciences Major(s)

2) For each dept that has one or more major(s) with a gpa < 1.0, print the

name of the dept and the average gpa of it's majors.

3) Print the id's, names, and GPAs of the students who are currently taking

all of the Civil Engineering courses

4) Print the names of students who are taking both Computer Sciences course and

a Mathematical course.

5) print the name(s) abd sid(s) of the student(s) enrolled in the most classes





Thanks very much in advance


Comments

  • Though your schema is pretty implicit and hard to understand the abbrs. Here goes your solutions--



    1)select max(student.age)-min(student.age) "difference" from student,major where student.sid=major.sid and major.dname='Computer science';



    2)select major.dname,avg(student.age)from student,major where student.sid=major.sid and student.gpa<1.0 group by major.dname;



    3)Specify which schema gives the subcourses...you can use ALL to retrive the query results;



    4)select student.sname from student,course,course a where student.sid=course.sid and course.cname='Computer science' and a.cname='Mathematical science';



    5)select sid,sname from student where sid=(select sid from student where sid=(select sid from enroll group by sid having count(sid)=(select max(count(sid)) from enroll group by sid)));



    Query 5 presumes that there is no redundancy or duplication in enroll schema.





    ---Deepak

    India

    deepakshukla@india.com




Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories