Re: Need SQL Experts Help : Create View !!!!!!!!

Eric , Chow Hoi Ka wrote:
>
> Hello, SQL Experts,
> I meet some problem in creating views. Could you please to help me ?
>
> The following are the Tables :
> 1) Student
> * Student#
> * Name
> * Faculty
> * Major
> * Address
>
> 2) Course
> * Course#
> * Title
> * Credits
>
> 3) Grade
> * Student#
> * Course#
> * Grade
>
> Questions :
> 1) How to create a VIEW that shows
> * Course#
> * TotEnrol => the total number of enrolled students in a
> COURSE
> * NumFac => the number of different FACULTIES that enrolled
> students are from
> (for example, if two students from
> FST and one from FBA are enrolled in a course,
> then the NumFac should be 2)
> * NumMaj => same as NumFac, but is will shows the total
> number of different Majors that the enrolled
> students are from
>
> I am sincerely to ask all the EXPERTS for help !!!!!!!
> I think it is very easy for those experience SQL EXPERTS.
>
> Best regards,
> Eric
> --
 

Something like this should do it:

select g.course, count(*) as "TotEnrol", count(distinct s.faculty) as "NumFac",
count(distinct s.major)as "NumMaj"
from grade g, student s
where g.studentid = s.studentid
group by g.course
go  

-- 
Bret Halford			Imagine my disappointment
Sybase Technical Support	in learning the true nature		
3665 Discovery Drive		of rec.humor.oracle...
Boulder, CO 80303