# Database: University Example: Exercises: Joins

Name __________________________________________
These questions all apply to the University database.
All solutions are written using the lowest common denominator join which places the key equality tests in the WHERE clause. A couple of alternatives are shown - an INNER JOIN...ON in one question and a NATURAL JOIN in another. These, or the USING syntax, could be used for all of the questions.

1. List all the courses (crs_id) and the department names they are taught in.

       SELECT crs_id, dep_name
           FROM  Course, Department
           WHERE Course.dep_id = Department.dep_id;

   or with another syntax

       SELECT crs_id, dep_name
           FROM Course INNER JOIN Department ON Course.dep_id=Department.dep_id;

2. Which Ed Centers (`edc_name`) are teaching IFSM410? Note that IFSM410 is a value in the `Section.crs_id` field. If an Ed Center is teaching more than one section of IFSM410, it should only be listed once.

       SELECT DISTINCT edc_name
           FROM  Ed_Center, Section
           WHERE Section.edc_id = Ed_Center.edc_id
                 AND crs_id     = 'IFSM410';

   Note: The Course table isn't needed because Section.crs_id has the info.
3. Who's currently teaching? Give their id, last name and first name (ordered by last name and first names in ascending order). Assume that the Section table contains only sections that are currently being taught. Don't list instructors more than once even if they are teaching more than once.

       SELECT DISTINCT Instructor.ins_id, ins_lname, ins_fname
           FROM  Instructor, Section
           WHERE Instructor.ins_id = Section.ins_id
           ORDER BY ins_lname, ins_fname;

4. Who's teaching in which Ed Centers in Germany? Give the Ed Center followed by the instructors' last name and first name, all in ascending order. Germany is identified by an edc_country value of 'de'.

       SELECT DISTINCT ins_lname, ins_fname, edc_name
           FROM  Instructor, Section, Ed_Center
           WHERE Instructor.ins_id = Section.ins_id
             AND Section.edc_id    = Ed_Center.edc_id
             AND edc_country       = 'de'
           ORDER BY edc_name, ins_lname, ins_fname;

5. Which students are taking courses from Fred Swartz? Make your own decisions about what to display.

       SELECT Student.student_id, student_lname, student_fname, crs_id
           FROM  Student, Enrollment, Section, Instructor
           WHERE Student.student_id    = Enrollment.student_id
                 AND Enrollment.sec_id = Section.sec_id
                 AND Section.ins_id    = Instructor.ins_id
                 AND ins_fname         = 'Fred'
                 AND ins_lname         = 'Swartz'
           ORDER BY student_lname, student_fname;

   or a natural join [which MS Access doesn't accept]

       SELECT Student.student_id, student_lname, student_fname, crs_id
           FROM  Student NATURAL JOIN Enrollment
                         NATURAL JOIN Section
                         NATURAL JOIN Instructor
           WHERE ins_fname='Fred' AND ins_lname='Swartz'
           ORDER BY student_lname, student_fname;