October 27, 2007 at 11:09 pm
STUDENT (sid, sname, sex, age, year, averagegrade)
SCHOOL (dname, numhonours)
PROF (pname, dname)
COURSE (dname, cno, cname)
MAJOR (dname, sid)
CLASS (dname, cno, classno, pname)
ENROLL (sid, dname, cno, classno, grade)
Design SQL queries that answer the questions given below (one query per question). The answer
to each query should be duplicate free, but you should use DISTINCT only when necessary.
Questions
1. Show the names of courses offered by schools that have more than 50 honours students.
2. For every class offered by the business school, display the cno, classno, and the average age of
the students enrolled in the class.
3. Show the course names and the class numbers of all classes with fewer than 10 students
enrolled in.
4. List the course names and class numbers in which all its students are older than twenty-five
years old.
5. List the professors in which every class they teach has at least one student who is younger
than twenty.
6. Find the names and majors of the students who are taking at least two database related
courses, i.e, courses containing the word "database".
7. List the name of the schools and its number of honours students if the school has no major
containing “database” courses.
8. For each class from the SIT school, display the course number, class number, and the lowest
average grade of the students enrolled in that class. Sort the results in ascending order of
lowest average grade.
9. List the student names with the second highest average grade in their major schools.
10. List the student names who are taking courses from either the SIT school or the business
school, but not from both.
October 27, 2007 at 11:29 pm
C'mon man... do your own homework... you haven't even tried...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2007 at 12:17 am
i would hv if i knew,, mine keeps getting wrng...
if you have any spare time and wouldnt mind helping someon out plzzz
October 28, 2007 at 8:57 am
Do you have a specific question or do you just want us to do all of your homework for you?
You're in class to learn this stuff and these are not difficult problems even for a beginner... put your shoulder to the wheel and push.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2007 at 8:40 pm
Jeff's right, we're all willing to help someone who's stuck. Unless you show that you've tried, I'm not gonna make it easy for ya. 😀
Show what you've tried for each question and we'd be more than happy to help you get the answer on your own by pointing you in the right direction.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 29, 2007 at 9:06 am
It's just a series of joins & where clauses. If you've paid any attention in the course, you should be able to do this stuff. If you get stuck on an individual question, show the work you did that failed and you can probably get some help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2007 at 10:12 am
Hi. I am new to this forum and also a student of SQL (but not with this homework) preparing for certification testing. These looked like very good exercises so I gave them a shot. The first 3 I think I have. Can you tell me if I am close on:
4) select c.cname, e.classno from student s
join enroll e on e.sid = s.sid join course c on c.dname = e.dname and c.cno = e.cno
where 25 < ALL (select age from student join enroll e on e.sid = s.sid)
5) for professors with any class with a student under 20 (if #4 is right this part is easy). to figure all classes would you just add a condition to the where ? ==>...( 20 > any (select age....)) and not exists(select MIN(age) from student s join enroll e on s.sid = e.sid
join class c on e.dname = c.dname and e.cno = c.cno and e.classno = c.classno
Join prof p on c.pname = e.pname WHERE Min(AGE) >= 20) *** left off the where clause **
6) Find the names and majors of the students who are taking at least two database related
courses... I can find students taking db courses (select sid from enroll e join course c on c.dname = e.dname and c.cno = e.dno where 'database' in (c.cname)) but am not sure how to determine those taking 2 classes with db in it... unless it is: where 2 >= (select count('database' in (select cname from course co join enroll en on en.dname = co.dname and en.cno = co.cno and en.sid = e.sid)))
For the rest... if you can help me with these I will try them later.... (an old mainframe sysprog trying to retool) Thanks!!!
October 29, 2007 at 10:19 am
If there is a way to best do this without giving away solutions to the person who doesn't want to do homework(who would be in bad shape if they just copied from me). please let me know. Thanks again! toniupstny@hotmail.com
October 30, 2007 at 2:20 am
I just thought id try it out, n i came up with
SELECT dbo.course.cname, dbo.enroll.classno
FROM dbo.course INNER JOIN
dbo.enroll ON dbo.course.dname = dbo.enroll.dname INNER JOIN
dbo.student ON dbo.enroll.sid = dbo.student.sid
GROUP BY dbo.course.cname, dbo.enroll.classno
HAVING (MAX(dbo.student.age) > 25)
i think im in the right direction, but im not sure. feedback is always welcome 🙂
October 30, 2007 at 6:12 am
happyme_01_cool (10/30/2007)
I just thought id try it out, n i came up withSELECT dbo.course.cname, dbo.enroll.classno
FROM dbo.course INNER JOIN
dbo.enroll ON dbo.course.dname = dbo.enroll.dname INNER JOIN
dbo.student ON dbo.enroll.sid = dbo.student.sid
GROUP BY dbo.course.cname, dbo.enroll.classno
HAVING (MAX(dbo.student.age) > 25)
i think im in the right direction, but im not sure. feedback is always welcome 🙂
Looks good. Nice set based solution, no unnecessary derived tables or correlated queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 31, 2007 at 6:35 am
Ok... Since you tried 4 we will continue with it.
SELECT course.cname,class.classno
FROM course
INNER JOIN class
ON COURSE.cno = CLASS.cno
AND COURSE.dname = CLASS.dname
INNER JOIN ENROLL
ON CLASS.classno = ENROLL.classno
AND CLASS.cno = ENROLL.cno
AND CLASS.dname = ENROLL.dname
INNER JOIN STUDENT
ON ENROLL.sid = STUDENT.sid
GROUP BY course.cname,class.classno
HAVING MIN(student.age)> 25
Since ALL students have to be older than 25 in the class you have use a group by and a having clause (required to look at the set results). But it is the MIN age must be > than 25, not the max age. That will give you all classes where at least 1 student is over 25 years old.
October 31, 2007 at 6:39 am
oops unnecessary table.
SELECT course.cname,ENROLL.classno
FROM course
INNER JOIN ENROLL
ON COURSE.cno = ENROLL.cno
AND COURSE.dname = ENROLL.dname
INNER JOIN STUDENT
ON ENROLL.sid = STUDENT.sid
GROUP BY course.cname,ENROLL.classno
HAVING MIN(student.age)> 25
November 8, 2007 at 4:08 am
hey thanks for ur attempt..... you got some write.... thanks heaps...
for the other guys who commented read this...
am an off campus student (reason not enough money to be on campus) i have no personal tutoring to help me with this... thats why i had to ask you guys to help me but instead you guys made remarks...
i tried doing these by my self but i failed... THIS IS NOT HOMEWORK its possible question i found thought case studies for the exam....
but how ever i managed to find a true friend who would help thanks again newbie..
November 8, 2007 at 6:39 am
I think you misunderstood the comments about it being homework. EVERYONE here is doing this because they apparenly like helping 🙂 But when it is obviously a learning exercise and not a mission critical business problem, we like to get the posters to actually show us what they know, and to try to LEARN. We aren't here to do someones homework for them. So posting a question without any attempt is open for harrasment.
Had you simply put up the question and say the truth and what you tried and asked why it didn't work, you would have gotten more help than you needed.
November 8, 2007 at 6:43 am
Point well made Bob. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply