| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Need SQL Experts Help : Create View !!!!!!!!
Hello Eric,
Based on your design and brevity, I'm going to use tables Student
and Grade and omit the non-essential fields.
Here's some test data for table Student
studentid faculty major 01 FST MATH 02 FST BIOL 03 FBA CS 04 SRD MATH 05 JBF BUS 06 FST BIOL 07 DSH PS 08 LML PE 09 FST MATH
Here's some test data for table Grade.
Each student can obviously take many courses.
key studentid courseid 1 01 A 2 01 B 3 02 B 4 02 D 5 03 A 6 03 E 7 04 A 8 04 E 9 05 G 10 05 B 11 05 D 12 06 B 13 06 D 14 07 E 15 08 E 16 09 A 17 09 B
Step 1 is to create a view based on a simple join of the 2 tables. This appends each student's faculty and major to course info. Call the view (query) "ericwork".
SELECT key, courseid, student.studentid, faculty, major FROM student INNER JOIN grade ON student.studentid = grade.studentid;
For course A the data looks like:
key courseid studentid faculty major 16 A 09 FST MATH 1 A 01 FST MATH 7 A 04 SRD MATH 5 A 03 FBA CS
Step 2 is to create the view you want using the view from step 1.
Query "ericview"
SELECT b.courseid, count(*) AS cnt,
Count(Switch( b.key= Dlookup("[key]","ericwork","[courseid]='"&b.courseid&"'"
& "AND" &"[faculty]='"&b.faculty&"'"),1)) AS [dist cnt faculty],
Count(Switch( b.key= Dlookup("[key]","ericwork","[courseid]='"&b.courseid&"'"
& "AND" &"[major]='"&b.major&"'"),1)) AS [dist cnt majors]
FROM ericwork AS b
The four fields correspond to your fields Course#, TotEnrol, NumFac
and NumMaj.
The two fancy count statements get the distinct count of faculty and majors
for each course. View "ericwork" is necessary until someone figures out how
to represent the Dlookup part of the expression over multiple tables. (The
same
count technique was used in my recent post "FYI on count distinct").
You can now use the view "ericview" any way you want. SELECT * FROM ericview;
courseid cnt dist cnt faculty dist cnt majors A 4 3 2 B 5 2 3 D 3 2 2 E 4 4 4 G 1 1 1
HTH
Steve Dassin
Eric writes,
> 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
> --
>
> _ |~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| _
> / ) | Eric, Chow Hoi Ka ICQ# : 3463514 | ( \
> / / | | \ \
> _( /_ | _ ICQ-Page : http://wwp.mirabilis.com/3463514 _ | _) )_
> (((\ \> |/ ) ( \| </ /)))
> (\\\\ \_/ / E-Mail : d951686_at_sftw.umac.mo \ \_/ ////)
> \ / \ /
> \ _/ http://www.sftw.umac.mo/~d951686/ \_ /
> / / |_____________________________________________________| \ \
> / / \ \
>
>
Received on Thu Mar 19 1998 - 00:00:00 CST
 |
 |