Give query for Relational Algebra and Relational Calulus . TABLES: STUDENT ( int

Question

Give query for Relational Algebra and Relational Calulus.

TABLES:

STUDENT ( int:student number, varchar(20):name, varchar(20):address, primary key (student number))
INSTRUCTOR ( int:instructor ID, varchar(20):name, varchar(20):address, primary key (instructor ID))
COURSE ( int:CRN, varchar(4):subject code, int:course number, varchar(50):name, int:term, int:instructor ID,
primary key (item ID))
REGISTRATION ( int:student number, int:CRN, int:grade )

QUERIES:

1. List the details of the courses with subject code CMPT.
2. For every registration list the name of the student and the name of the course.
3. List the details of all students who have enrolled in CMPT courses.
4. List the information about students who have not enrolled in CMPT courses.
5. List the students who have enrolled in every CMPT course.
6. SQL only: Find the average for each student. Report the student's name and their average. Note:
You will have to use the "group by" operator.

Explanation / Answer

1. List the details of the courses with subject code CMPT.
select * from COURSE where subject_code='CMPT'

2. For every registration list the name of the student and the name of the course.
select student.name,COURSE.name from student,COURSE,registration where registration.student_number=student.student_number and COURSE.CRN=registration.CRN;

3. List the details of all students who have enrolled in CMPT courses.
select student.student_number,student.name,student.address from student,registration where registration.student_number=student.student_number and registration.CRN=(SELECT CRN from COURSE where subject_code='CMPT');

4. List the information about students who have not enrolled in CMPT courses.

select student.student_number,student.name,student.address from student,registration where registration.student_number=student.student_number and registration.CRN!=(SELECT CRN from COURSE where subject_code='CMPT');

5. List the students who have enrolled in every CMPT course.
select student.name from student,registration where registration.student_number=student.student_number and registration.CRN!=(SELECT CRN from COURSE where subject_code='CMPT');

6. SQL only: Find the average for each student. Report the student's name and their average. Note:
You will have to use the "group by" operator.

select student.name,avg(grade) from registration,student where student.student_number=registration.student_number group by(registration.student_number);

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.