DATABASE SCHEMA Professor(ssn, profname, status, salary) (PRIMARY KEY ssn) Cours

Question

DATABASE SCHEMA

Professor(ssn, profname, status, salary) (PRIMARY KEY ssn)

Course(crscode, crsname, credits) (PRIMARY KEY crscode)

Taught(crscode, semester, ssn) (PRIMARY KEY crscode)

Assumptions: (1) Each course has only one instructor in each semester; (2) all professors have different salaries; (3) all professors have different names; (4) all courses have different names; (5) status can take values from “Full”, “Associate”, and “Assistant”.

Please write SQL command for the followings:

a. Return those courses’ crscode that have been taught by at least 5 different professors.

b. In chronological order, list the number of courses that the professor with ssn = 123456789 taught in each semester.

c. For each professor, return the total number of credits that she/he taught for all semesters. (Hint: return ssn for the professor)

Explanation / Answer

Answer:--------------

a).

SELECT crscode FROM ( SELECT DISTINCT crscode, ssn FROM Taught ) GROUP BY crscode HAVING COUNT ( * ) >= 5

b).

SELECT semester, COUNT( * ) FROM Taught WHERE ssn = '123456789' GROUP BY semester ORDER BY semester ASC

c).

SELECT SUM(credits) FROM ( SELECT DISTINCT ssn FROM Professor ) WHERE Professor.ssn = Taught.ssn AND Taught.crscode = Course.crscode

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.