-- (5) Show the ids and names of the students and the semesters that they have -- enrolled in the course with id 2000. -- output: stuId, fname, lname, semester -- source: student(stuId, fname, lname), class(semester) -- enroll: to link student to class through enrollment. -- class: a class offering in a particular semester. -- condition: (1) courseId = 2000 -- (2) To link tables enroll and class -> enroll.classId = class.classId (‘common’ attribute) -- (3) To link/join tables enroll and student -> enroll.stuId = Student.stuId select distinct student.stuId, student.fname, lname, semester, courseId -- stuId of student or enroll from student, enroll, class where courseId = 2000 and enroll.classId = class.classId and enroll.stuId = Student.stuId; select distinct student.stuId, student.fname, lname, semester from student, enroll, class where courseId = 2000 and enroll.classId = class.classId and enroll.stuId = Student.stuId; -- stuId that are enrolled (in some classes. select stuId from enroll; select distinct stuId from enroll; select distinct s.stuId, s.fname, s.lname, c.semester from student s, enroll e, class c -- aliases where c.courseId = 2000 and e.classId = c.classId and e.stuId = s.stuId; -- list all student ids, names and the classId that they enrolled in. output: stuId, fname, lname, classId Sources: student(stuId, fname, lname), enroll(classId) condition: join condition: enroll.stuId = student.stuId select distinct s.stuId, s.fname, s.lname, e.stuId, e.classId from student s, enroll e; -- incorrect, -- include too many incorrect rows about two different students, select distinct s.stuId, s.fname, s.lname, e.stuId, e.classId from student s, enroll e where s.stuId = e.stuId; select distinct s.stuId, s.fname, s.lname, e.classId from student s, enroll e where s.stuId = e.stuId;