Help with Some Sql Statements

Pre MidTerm Study Help

Page 1 of 1

7 Replies - 780 Views - Last Post: 25 March 2009 - 06:18 AM Rate Topic: -----

#1 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Help with Some Sql Statements

Posted 22 March 2009 - 09:41 AM

Im trying to finish up 8 questions out of 50 in a Study Guide for my Midterm SQL Exam. I cant figure out 8 of the questions at all and so I cant use it to study by. Can anyone help me with this?

This is the Schema:

Posted Image

These are the questions(SQL statements):
1.The SID, first name, last name, and number of classes taken for every student. As before, name your count column as NumOfClasses. Don't bother including students that have taken no classes.

2.The SID and grade for all enrollment records for the course with IndexNum 1001 offered in the term 'Fall 02'.

3.The SID, first name, last name, and grade for all enrollment records for the course with IndexNum 1001 offered in the term 'Fall 02'.

4.The course number, course name, term, instructor, credits, and grade for all courses taken by student 223451. Courses should be listed in order by term and within term by the course number.

5. The faculty name, course number, days, start time, ending time, and location for all course sections offered in the term 'Spring O3' in order by faculty name.

6.The SID of all students that have not yet taken a course.

7.The SID of every student that has taken both 'ACTG 313' and 'ACTG 314'. (even if they haven't been assigned a grade yet in one of them)

8.The FID, name, and number of classes taught (renamed as NumOfClasses) for all faculty members that have taught more than 5 courses.

Any help would be appreciated. Owe ya one!

This post has been edited by absynthe: 22 March 2009 - 09:42 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Help with Some Sql Statements

#2 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help with Some Sql Statements

Posted 22 March 2009 - 10:50 AM

These much be much harder than I thought. Almost got the first one but getting errors:

Select Students.SID, Students.first, Students.last, enrollments.indexnum as NumOfClasses from Students Inner Join Enrollments On Enrollments.SID=Students.SID

I got a list but cant get the count

God I hate joins! :)

Why the hell is this not working?

1. Select SID, First, Last, count(indexNum) as NumOfClasses from Enrollments Inner Join Students on Students.SID=Enrollments.SID
Group By SID

This post has been edited by absynthe: 22 March 2009 - 12:05 PM

Was This Post Helpful? 0
  • +
  • -

#3 brds  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 76
  • View blog
  • Posts: 515
  • Joined: 22-October 08

Re: Help with Some Sql Statements

Posted 22 March 2009 - 12:09 PM

Try this:
Select Students.SID, Students.first, Students.last, Count(enrollments.indexnum) as NumOfClasses from Students Inner Join Enrollments On Enrollments.SID=Students.SID Where NumOfClasses >= 0


It's been a while since I last did this :(
Was This Post Helpful? 0
  • +
  • -

#4 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help with Some Sql Statements

Posted 22 March 2009 - 12:15 PM

Got the second one:

Select SID, grade from enrollments where indexnum='1001' and term='Fall 02'

and the third:

Select students.SID, students.First, students.Last, enrollments.grade from enrollments
inner join Students
on Students.SID=Enrollments.SID where IndexNum='1001' and term='Fall 02'

Sweet!

View Postbrds, on 22 Mar, 2009 - 01:09 PM, said:

Try this:
Select Students.SID, Students.first, Students.last, Count(enrollments.indexnum) as NumOfClasses from Students Inner Join Enrollments On Enrollments.SID=Students.SID Where NumOfClasses >= 0


It's been a while since I last did this :(



Nope..ugh! haha! I hate these things! :) Thank you so much for trying though!
Was This Post Helpful? 0
  • +
  • -

#5 absynthe  Icon User is offline

  • DIC Tease
  • member icon

Reputation: 28
  • View blog
  • Posts: 2,807
  • Joined: 20-September 08

Re: Help with Some Sql Statements

Posted 22 March 2009 - 12:21 PM

Got another :)

Select courses.CourseNum, courses.Name, Sections.Term, Sections.Instructor, Courses.Credits, Enrollments.Grade From Courses Inner Join Sections on Courses.CourseNum=Sections.CourseNum
Inner Join Enrollments on Sections.IndexNum=Enrollments.IndexNum where SID='223451' Order by Term, CourseNum
Was This Post Helpful? 0
  • +
  • -

#6 nofear217  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 14
  • View blog
  • Posts: 323
  • Joined: 08-November 07

Re: Help with Some Sql Statements

Posted 24 March 2009 - 02:01 PM

This is what I can get so far though I may be slightly misunderstanding the schema:

SELECT Faculty.Name, Sections.CourseNum, Sections.Days, Sections.StartTime, Sections.EndTime, Sections.Location
FROM Sections INNER JOIN Faculty
	ON Sections.Instructor = Faculty.FID
WHERE Enrollments.Term = 'Spring 03'
ORDER BY Faculty.Name

SELECT Students.SID
FROM Students LEFT OUTER JOIN Enrollments
	ON Students.SID = Enrollments.SID
WHERE Enrollments.SID IS NULL

SELECT Enrollments.SID
FROM Enrollments INNER JOIN Sections
	ON Enrollments.Term = Sections.Term AND Enrollments.IndexNum = Sections.IndexNum
INNER JOIN Courses
	ON Sections.CourseNum = Courses.CourseNum
WHERE Courses.Name IN ('ACTG 313', 'ACTG 314')

Was This Post Helpful? 0
  • +
  • -

#7 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Help with Some Sql Statements

Posted 25 March 2009 - 05:43 AM

I think this should work for #8. I think is pretty self explanatory but if you have questions LMK.
select F.FID, F.Name, sum(S.CourseNum) as NumOfClasses from Faculty as F
        join Sections S on S.Instructor=F.FID
        join Courses C on C.CourseNum=S.CourseNum
group by F.FID, F.Name
having count(*)>5

This post has been edited by xerxes333: 25 March 2009 - 05:50 AM

Was This Post Helpful? 0
  • +
  • -

#8 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Help with Some Sql Statements

Posted 25 March 2009 - 06:18 AM

Another easy way to accomplish #6 is. . .
select SID From Students where SID not in (select SID from Enrollments)

Obviously it is not using joins so if the purpose of the exercise is to learn joins then I recommend nofear217's way
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1