complex query stuff

complexqueries.md

Complex queries

Complex queries for the Advanced Java programming assignment.

Without generality:

/*!
 * Benjamin Williams <eeu222@bangor.ac.uk>
 * Liam Chapman <eeu239@bangor.ac.uk>
!*/

USE `topifyco_eeueeu`;

-- All modules taught by a member of staff
SELECT s.`module_Id`, `module_name` FROM `teaches` t
	JOIN `module` s ON t.`module_Id` = s.`module_Id`
    AND t.`staff_Id` = 'E10010';

-- All students registered on a specified module
SELECT s.`student_Id`, `student_name` FROM `registered` r
	JOIN `student` s ON r.`student_Id` = s.`student_Id`
    AND r.`module_Id` = 'CS101';
    
-- All staff who teach modules on which the student is registered for?
SELECT t.`staff_Id`, `staff_name`, r.`module_Id` FROM `registered` r
	JOIN `teaches` t ON t.`module_Id` = r.`module_Id`
    JOIN `staff` s on t.`staff_Id` = s.`staff_Id`
    AND r.`student_Id` = 'S10345';
    
-- All staff who teach on more than one module
SELECT s.`staff_Id`, `staff_name` FROM `teaches` t
	JOIN `staff` s ON s.`staff_Id` = t.`staff_Id`
	GROUP BY s.`staff_Id` 
	HAVING COUNT(*) > 1;

---

###With generality:

-- All modules taught by a member of staff
SELECT m.`module_Id`,  `module_name` FROM `teaches` t
    JOIN `module` m ON t.`module_Id` = m.`module_Id`
    JOIN `staff` s ON t.`staff_Id` = s.`staff_Id`
    AND `staff_name` = 'Alan Turing';

-- All students registered on a specified module
SELECT s.`student_Id`, `student_name` FROM `registered` r
    JOIN `student` s ON r.`student_Id` = s.`student_Id`
    JOIN `module` m ON r.`module_Id` = m.`module_Id`
    AND `module_name` = 'Computer Architecture';

-- All staff who teach modules on which the student is registered for?
SELECT t.`staff_Id`, `staff_name`, r.`module_Id` FROM `registered` r
    JOIN `teaches` t ON t.`module_Id` = r.`module_Id`
    JOIN `staff` s on t.`staff_Id` = s.`staff_Id`
    JOIN `student` stu on stu.`student_Id` = r.`student_Id`
    AND `student_name` = 'John Smith';

-- All staff who teach on more than one module
SELECT s.`staff_Id`, `staff_name` FROM `teaches` t
    JOIN `staff` s ON s.`staff_Id` = t.`staff_Id`
    GROUP BY s.`staff_Id` 
    HAVING COUNT(*) > 1;