SQL Query of following table: Tables are in attached images: 1. List the details

Question

SQL Query of following table:

Tables are in attached images:

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

-- List the details of the courses with subject code CMPT.
SELECT *
FROM   course c
WHERE c.crn = 'CMPT';

-- For every registration list the name of the student and the name of the course.
SELECT s.name student_name, c.name course_name
FROM   student s, registration r, course c
WHERE s.student_number = r.student_number
AND    r.crn = c.crn;

-- List the details of all students who have enrolled in CMPT courses.
SELECT DISTINCT s.name student_name, s.address
FROM   student s, registration r, course c
WHERE s.student_number = r.student_number
AND    r.crn = c.crn
AND    c.crn = 'CMPT';

-- List the information about students who have not enrolled in CMPT courses.
SELECT DISTINCT s.name student_name, s.address
FROM   student s, registration r, course c
WHERE s.student_number = r.student_number
AND    r.crn = c.crn
AND    c.crn != 'CMPT';

-- List the students who have enrolled in every CMPT course.
SELECT s.name student_name, c.term
FROM   student s, registration r, course c
WHERE s.student_number = r.student_number
AND    r.crn = c.crn
AND    c.crn = 'CMPT';

-- Find the average for each student. Report the student's name and their average.
SELECT s.name student_name, AVG(r.grade)
FROM   student s, registration r
WHERE s.student_number = r.student_number
GROUP BY s.name;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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