List the titles of all the courses that student ‘Alice’ has not taken. List all
ID: 3890902 • Letter: L
Question
List the titles of all the courses that student ‘Alice’ has not taken.
List all the students and their GPA (result schema: sid and GPA). The GPA is calculated by summing up the grade of each course multiplied the number of credits for the course and then dividing the result by the total number of credits the student has taken.
Consider the following database schema and example instance: Course Student title Databases dept credits cid CS425 name dept sid 001 Alice CS 002 Bob EE 003 Carol CS 004 David PHYS CS CS595 Database Security CS EE VLSI Design EE 3 591 Microcomputers EE401 4 PHYS571 Radiation Physics PHYS 3 Enroll sid grade gradepoint cid CS425001A CS595 001B CS595 002A EE401 001 A EE401 002 B EE401 004 A 4.0 3.0 4.0 4.0 3.0 4.0 2.0 4.0 Prereq cid pi CS595 CS425 EE591 EE401 PHYS571 002 C PHYS571 004A Hints: Attributes shown with grey background form the primary key of a relation.Explanation / Answer
List the titles of all the courses that student ‘Alice’ has not taken.
Query:
select title
from course
inner join enroll on enroll.cid = course.cid
inner join student on student.sid = enroll.sid
where name != 'Alice';
Explanation: The query select the title from the table course, it combines with the other two tables by using inner join where their cid and sid are equal and it selects the name of the student which is not 'Alice' to select the subject which has not taken by Alice.
---------------------------------------------------------------------------------------------------------------------------------------------
List all the students and their GPA (result schema: sid and GPA). The GPA is calculated by summing up the grade of each course multiplied the number of credits for the course and then dividing the result by the total number of credits the student has taken.
Query:
select student.sid, (sum(enroll.gradep) * course.cred)/sum(course.cred) as GPA
from student
inner join enroll
inner join course on enroll.cid = course.cid;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.