Name ______

MIS 408 - Exam #1 (October 10, 2013) 100 points

Write relational algebra (or draw query trees) and SQL commands to answer the following questions

NOTE: Your query trees must be optimized!!

PART I – Use the Company Database (40 points)

1)  For each employee, list the employee’s name, department name he/she works for, total number of projects he/she worked on, and the total number of dependents he/she has.

(10 points)

2)  List the names of all employees who work for the “Research” department and have dependents who are “Spouse,” “Daughter,” and “Son” (all three). – use division (10 points)

3)  For each employee whose salary is less than the average salary of his/her department, list the employee’s name, the department name, the average and lowest salary of the department, and the employee’s salary. (10 points)

4)  For each employee who did not work on projects controlled by the research department, list his/her name, the department name he/she work for, and the project names he/she work on. (10 points)


PART II – Use the University Database (60 points)

5)  For each instructor who teaches more than two courses, list the instructor’s names, his/her office location, and the number of courses he/she teaches. (12 points)

6)  For each student who did not take any course taught by professor Pearsons, list his/her name, course titles he/she took, and the grades he/she received. (12 points)

7)  For each course, list the course id, course title, instructor’s name, and number of students who have an “A” grade from that course. (12 points)

8)  For each course that has less than ten students took it, list the course title, the instructor’s name, and the number of students who took it. (12 points)

9)  List the names of students who take every course taught by Professor Bennett.

– use division (12 points)


University Database:

Student

Student_ID / Student_Name / Campus_Address / Major
111222333 / Williams / 123 Brooks / MIS
123456789 / Smith / 751 Lamar / Acctg
…

Course

Course_ID / Course_Title / Instructor_Name
MIS 350 / Database Mgt / Codd
MIS 370 / Adv. Database Mgt / Codd
MIS 390 / Adv. MIS / Codd
MIS 465 / System Analysis / Pearsons
Acctg 201 / Fund Acctg / Miller
Mktg 300 / Intro Mktg / Bennett
Mktg 360 / Mktg Research / Bennett
…

Instructor

Instructor Name / Instructor_Location
Codd / B104
Pearsons / B317
Miller / H310
Bennett / B212
…

Registration

Student_ID / Course_ID / Grades / Scores
111222333 / MIS 350 / A / 4
111222333 / MIS 465 / B / 3
111222333 / MIS 370 / A / 4
123456789 / MIS 350 / C / 2
123456789 / Acctg 201 / B / 3
123456789 / Mktg 300 / A / 4
123456789 / Mktg 360 / B / 3