schema is :
Department (D-code, D-Name, Chair-SSn)
Course (D-code, C-no, Title, Units)
Prereq (D-code, C-no, P-code, P-no)
Class (Class-no, D-code, C-no, Instructor-SSn)
Faculty (Ssn, F-Name, D-Code, Rank)
Student (Ssn, S-Name, Major, Status)
Enrollment (Class-no, Student-Ssn)
Transcript (Student-Ssn, D-Code, C-no, Grade)...

In the above tables, primary keys are underlined. Some explanations of attributes are as
follows. Chair-SSn in Department refers to Ssn in Faculty table. Instructor-SSn refers to
Ssn in Faculty table, and Student-Ssn refers to Ssn in Student table. Class-no is a unique
ID for each offering of a course. (For example, a class with Class-no='12020' may be an
offering of INFS 614, in which case, we may have a row like <12020, INFS, 614,
100009399> in the Class table.) <P-code, P-no> in Prereq table refers to <D-code, C-no>
in the Course table, i.e., they refer to courses. A example row in Prereq table is <INFS,
614, INFS, 501> , which means that INFS614 needs INFS501 as a prerequisite course.
Other foreign keys and meanings of the tables should be obvious.


Query is:
List the percentage of undergraduate students who do not have a major.

Wow, is it exam time already ? Good luck!

-PatP

Just remember that the ability to caluclate a percentage implies that there is at least one row in your target table. Otherwise the result of the ratio that you are calculating is undefined. Your professor may give you extra credit if you code that into your SQL.

Hi All,

I am newbie to the forum. I noticed that this question above was never answered. I know it has been a long time the question was asked without answer. Maybe because "krishna17" did not state what s/she expected. Therefore, I went ahead adding some queries as follows:

1. List the courses (D-code and C-no), along with the names of the students who are currently taking them.

2. List all the courses (D-code and C-no) that John (i.e., S-Name=``John'') got 'A' grade.

3. List the courses (D-Code and C-No) that do not require any pre-requisites.

4. Give the students (Ssn) who are enrolled in INFS614 (i.e., D-code=``INFS'' and C-no=``614'') and have satisfied all its prerequisites.

For instance, below is what I came up with #2

select distinct class_no
from enrollment e, student s
where e.student_ssn = s.ssn and s.s_name="John";

Angela

Originally Posted by krishna17
schema is :
Department (D-code, D-Name, Chair-SSn)
Course (D-code, C-no, Title, Units)
Prereq (D-code, C-no, P-code, P-no)
Class (Class-no, D-code, C-no, Instructor-SSn)
Faculty (Ssn, F-Name, D-Code, Rank)
Student (Ssn, S-Name, Major, Status)
Enrollment (Class-no, Student-Ssn)
Transcript (Student-Ssn, D-Code, C-no, Grade)...

In the above tables, primary keys are underlined. Some explanations of attributes are as
follows. Chair-SSn in Department refers to Ssn in Faculty table. Instructor-SSn refers to
Ssn in Faculty table, and Student-Ssn refers to Ssn in Student table. Class-no is a unique
ID for each offering of a course. (For example, a class with Class-no='12020' may be an
offering of INFS 614, in which case, we may have a row like <12020, INFS, 614,
100009399> in the Class table.) <P-code, P-no> in Prereq table refers to <D-code, C-no>
in the Course table, i.e., they refer to courses. A example row in Prereq table is <INFS,
614, INFS, 501> , which means that INFS614 needs INFS501 as a prerequisite course.
Other foreign keys and meanings of the tables should be obvious.


Query is:
List the percentage of undergraduate students who do not have a major.


Hi All,

I noticed that this question above was never answered in this forum. I am interested to know the answers. I know it has been a long time the question was asked without answer. Moderator pardon me if I am cross posting. Maybe because "krishna17" original poster did not state clearly what s/she expected. I went ahead adding some queries as follows:

1. List the courses (D-code and C-no), along with the names of the students who are currently taking them.

2. List all the courses (D-code and C-no) that John (i.e., S-Name=``John'') got 'A' grade.

3. List the courses (D-Code and C-No) that do not require any pre-requisites.

4. Give the students (Ssn) who are enrolled in INFS614 (i.e., D-code=``INFS'' and C-no=``614'') and have satisfied all its prerequisites.

For instance, below is what I came up with #2

select distinct class_no
from enrollment e, student s
where e.student_ssn = s.ssn and s.s_name="John";

Angela