QUESTION A3. (6 marks) The following tables form part of a database held at Exce
ID: 3743450 • Letter: Q
Question
QUESTION A3. (6 marks) The following tables form part of a database held at Excelsior University AcadStaff (staffID, fName, 1Name, address, highestDegree, deptNo) Department (deptNo, deptName, head) PhDStudent (stuID, stuName, deptNo, commencementDate, staffID) 1. AcadStaff table contains details of academic staff, and staffID is the primary key. 2. Department table contains department details and deptNo is the primary key. The head identifies the Head of the Department of a department by his or her stafflD Each department has only one head 3. PhDStudent table contains details of the PhD students, and the primary key is stulD. time during the PhD student's PhD program, the student At Excelsior University, any as one and only one supervisor who is an academic staff working at the university In this table staffID is the staff ID of the PhD student's supervisor (a) (3 marks) For each department. list the total number of PhD students. The query result needs to be ordered by department name. (b) (3 marks) For each department, list the supervisor's name, jpb title, and all the names of the PhD students the supervisor is supervising. The query result needs to be ordered by departmeut nameExplanation / Answer
[1]
SELECT T1.deptNo, T2.deptName , T1.TOTAL as COUNT
FROM (
(
SELECT deptNo,count(stuID) TOTAL
FROM PhDStudent
GROUP BY deptNo) T1
JOIN
(SELECT distinct deptNo,deptName
FROM Department) T2
ON(T1.deptNo = T2.deptNo)
)
ORDER BY T2.deptName;
[2]
SELECT TABLE1.deptNo, TABLE1.deptName , TABLE1.fname, TABLE1.lName , TABLE2.stuName
FROM (
(SELECT T1.deptNo, T1.deptName, T2.staffID , T2.fname, T2.lName
From department T1 JOIN
AcasStaff T2
ON(T1.deptNo = T2.deptNo AND T1.head is NOT NULL)
) TABLE1
JOIN
(SELECT stuName,staffID
FROM PhdStudent
) TABLE2
ON(TABLE1.staffID = TABLE2.staffID)
)
ORDER BY TABLE2.deptName;