Task 2 - Database CRUD Operations

Database Tables

Modules

moduleID	moduleCode	moduleName
1	ICT2611	Internet Programming
2	ICT2613	Internet Programming
3	ICT3612	Advanced Internet Programming
4	ICT3715	Web Application Development
5	ICT3721	Database Design
6	cos2614	Test module

Lecturers

lecturerID	firstName	lastName	email	Actions
2	Mary	Johnson	johnsonm@unisa.ac.za	Edit
3	Peter	Williams	williamsp@unisa.ac.za	Edit
4	Susan	Brown	browns@unisa.ac.za	Edit
5	David	Jones	jonesd@unisa.ac.za	Edit

Roles

roleID	roleName	roleDescription
1	Primary Lecturer	Main lecturer responsible for the module
2	Secondary Lecturer	Supporting lecturer for the module
3	Internal Moderator	Internal quality assurance
4	External Moderator	External quality assurance
5	Critical Reader	Reviews module content

ModuleLecturerRole

Assignment ID	Module	Lecturer	Role	Actions
1	ICT2611	Mary Johnson	Primary Lecturer	Edit
2	ICT2611	Mary Johnson	Secondary Lecturer	Edit
3	ICT2611	Peter Williams	Internal Moderator	Edit
4	ICT2613	Mary Johnson	Primary Lecturer	Edit
6	ICT2613	Mary Johnson	External Moderator	Edit
5	ICT2613	Susan Brown	Secondary Lecturer	Edit
7	ICT3612	Mary Johnson	Primary Lecturer	Edit
9	ICT3612	Mary Johnson	Internal Moderator	Edit
8	ICT3612	Peter Williams	Secondary Lecturer	Edit
12	ICT3715	Mary Johnson	External Moderator	Edit
10	ICT3715	Susan Brown	Primary Lecturer	Edit
11	ICT3715	David Jones	Secondary Lecturer	Edit
15	ICT3721	Mary Johnson	Internal Moderator	Edit
13	ICT3721	Peter Williams	Primary Lecturer	Edit
14	ICT3721	David Jones	Secondary Lecturer	Edit

CRUD Operations

SELECT Queries

ORDER BY: SELECT * FROM Modules ORDER BY moduleCode
LIKE: SELECT * FROM Lecturers WHERE email LIKE '%@unisa.ac.za'
INNER JOIN: SELECT m.moduleCode, CONCAT(l.firstName, ' ', l.lastName) as lecturer, r.roleName FROM ModuleLecturerRole mlr JOIN Modules m ON mlr.moduleID = m.moduleID JOIN Lecturers l ON mlr.lecturerID = l.lecturerID JOIN Roles r ON mlr.roleID = r.roleID
WHERE AND: SELECT * FROM Lecturers WHERE firstName LIKE 'J%' AND email LIKE '%@unisa.ac.za'
MAX: SELECT MAX(moduleID) as maxModuleID FROM Modules