(a) What SQL statement would you use to create the table ”staff”, including specifying the primary key? (b) What SQL statement would you use to find out how many lecturers there are in the School of Computing? (c) What SQL statement would you use to list the lecturers of the modules COMP1121 and COMP1211?
(a) What SQL statement would you use to create the table ”staff”, including specifying the primary key?
(b) What SQL statement would you use to find out how many lecturers there are in the School of Computing?
(c) What SQL statement would you use to list the lecturers of the modules COMP1121 and COMP1211?
(d) Write an SQL statement, using the inner join construct, that will, for the lecturer John Smith, list in alphabetical order the students taking any module which he teaches.
(e) What information does the output from the following SQL query provide?
SELECT Lecturer, COUNT(DISTINCT Student)
FROM class INNER JOIN staff
ON class.Module = staff.Module
WHERE Grade < 40
GROUP BY Lecturer;
(f) Write an SQL query which would obtain the following information: for each lecturer, what is the average grade obtained by students across all of the modules which they have taught?
Step by step
Solved in 4 steps
