Question

using the University database

we have provided you with the creation script using the relational schema of a University as shown below. You should run this script in MySQL Workbench and use this database to extract the necessary information.

The script is based on the following relational schema:

Subject (subjectCode, departmentName)

TeachingStaff (employeeNumber, firstName, surname, departmentName)

Instructs (employeeNumber, subjectCode, studentID, position)

Student (studentID, studentSurname, studentInitials, gender)

TeachingCredentials (employeeNumber, degreecode, uni, awardYear)

Enrolment (studentId, degreeCode, year, uni, timeMode, placeMode, completed)

Grades (studentID, subjectCode, grade)

Column Constraints

• completed is either Y or N
• timeMod is either Part time or full time (PT, FT)
• placeMode is either INT (for Internal students) or EXT (for External students)
• position is either LECTURER, TEACHER or OTHER
• departmentName is one of SEF, LAW, CI, BUS, HTH or EDU (SEF is for Science and Eng. Faculty, LAW is for Law faculty, CI is for Creative Industries Faculty, BUS is for Business Faculty, HTH is for Health Faculty, and EDU is for Education Faculty)

Other Information

• subjectCode consists of the subject id, followed by the offering year, then the offering semester.

Query 1

Write a query to list the full names, (i.e. first name and last name combined) and degree code of teaching staff who received their credentials from a university other than QUT.

Query 2

Write a query to list the id, name and gender of all students who are enrolled part time. Sort the result in alphabetical order of student surname.

Query 3

Write a query to count how many students each member of the teaching team instructs. In your result-set, include the teacher’s ID, surname and the number of students they teach.

Query 4

Write a query that lists the first name and degree of all teachers that are not teaching in 2019.

Query 5

Write a query to produce some statistics about each student. Your result-set should include the student ID, their surname, their current GPA, whether they are part time or full time and external or internal. Only show results for students who have received 1 or more grades.

Query 6

Write a query that will produce some statistics about each subject to report to the University exec team. Your result-set should include the following:

• the subject code
• the surname of the employee instructing the subject offering
• the number of students that have taken the subject in that offering
• the lowest grade a student has received in the subject offering
• the highest grade a student has received in the subject offering
• the average grade that students received in the subject offering

Answer 1

Below are the SQL queries for the university database.

1) Function CONCAT() is used to give combines first name and surname of teacher. Two tables are joined here on common attributes and filter is used in where clause.

SELECT CONCAT(t.firstName, ' ', t.surname) AS Name, tc.degreecode
FROM TeachingStaff As t
INNER JOIN TeachingCredentials AS tc
ON t.employeeNumber = tc.employeeNumber
WHERE tc.uni <> 'QUT'

2) Two tables are joined here on common attributes to get all the relevant column. Order by is used to sort the result by studentSurname.

SELECT s.studentID, s.studentSurname, s.studentInitials, s.gender
FROM Student AS s
INNER JOIN Enrolment AS e
ON e.studentID = s.studentID
WHERE e.timeMode = 'PT'
ORDER BY s.studentSurname DESC;

3) Aggregate function count() is used to get the number of student by teacher. The result is grouped by employeeNumber and their surname.

SELECT t.employeeNumber, t.surname, COUNT(i.studentID)
FROM TeachingStaff AS t
INNER JOIN Instructs AS i
ON i.employeeNumber = t.employeeNumber
GROUP BY t.employeeNumber, t.surname;

4) Two tables are joined here to get the result. Filter is done in where clause to get only the desired rows.

SELECT t.firstName, tc.degreeCode
FROM TeachingStaff As t
INNER JOIN TeachingCredentials AS tc
ON t.employeeNumber = tc.employeeNumber
WHERE tc.awardYear <> '2019';

5) Three tables are joined to get all the relevant columns in the result set.

SELECT s.studentID, s.studentSurname, g.grades, e.timeMode, e.placeMode
FROM Student AS s
INNER JOIN Enrolment AS e
ON e.studentID = s.studentID
INNER JOIN Grades AS g
ON g.studentID = e.studentID
WHERE g.grades > 1;