USE UFL; GO SELECT * FROM Departments; SELECT * FROM Faculty;

1. Write an inner join that joins rows in the Departments table to their corresponding rows in the Faculty table.

SOLUTION:

2. Your solution to part a) should have returned 12 rows in the result set. If there are 13 rows in the Faculty table, why did the inner join only return 12 rows?

Answer:

3. Explain why no information from the department 9700 is included in the results of the inner join.

Answer:

Modify your solution to EX-03 a) to only include information for departments 8401 and 9500.

SOLUTION:

At UFL, each class is a specific offering of a course (subject). Each row in the Courses table contains information on a single course or subject taught a UFL and each row in the Classes table contains information on a single offering of a course during a specific term. Investigate the two tables, and then write a query that returns the CourseTitle, ClassID, Term, SectionID and FacultyID for classes taught by FacultyID 5000009.

SOLUTION:

Write a query that returns just the CourseTitle, CreditHours, Term, and SectionID for each class that is for an MIS course and that was offered in the fall of 2012 (term is FA2012). Make sure that all references to CourseID are written as Courses.CourseID (even in a WHERE clause).

SOLUTION:

Write a left outer join between the Departments and Faculty table that returns the department name, faculty ID, faculty last name and faculty first name. The results should include all faculty rows, even those not assigned to a department.

SOLUTION:

Write a right outer join between the Departments and Faculty tables that returns the department name, faculty ID, faculty last name and faculty first name. The results should include all Departments rows, even those not associated with a member of the faculty.

SOLUTION:

Write a full outer join between the Departments and Faculty tables that returns the department name, faculty ID, faculty last name and faculty first name. The results should include all faculty rows and all department rows, even those without a corresponding row in the other table.

SOLUTION:

Write a query that returns just the CourseTitle, CreditHours, Term, and SectionID for each class offered at UFL. Also, make sure that the result set includes the course information for all courses, even those that have never been offered as a class.

SOLUTION: