STUDENTS
STUDENT_ID VARCHAR(8) NOT NULL PRIMARY KEY
STUDENT_NAME VARCHAR(40) NOT NULL
STUDENT_SURNAME VARCHAR(40) NOT NULL
MODULES
MODULE_ID VARCHAR(8) NOT NULL PRIMARY KEY
MODULE_NAME VARCHAR(40) NOT NULL
MODULE_CREDIT SMALLINT NOT NULL
STUDENT_MODULES
STUDENT_ID VARCHAR(8) NOT NULL PRIMARY KEY
FOREIGN KEY REFERENCES
STUDENTS(STUDENT_ID)
MODULE_ID VARCHAR(8) NOT NULL PRIMARY KEY
FOREIGN KEY REFERENCES
MODULES(MODULE_ID)
LECTURERS
LECTURER_ID VARCHAR(8) NOT NULL PRIMARY KEY
LECTURER_NAME VARCHAR(40) NOT NULL
LECTURER_SURNAME VARCHAR(40) NOT NULL
LECTURER_MODULES
MODULE_ID VARCHAR(8) NOT NULL PRIMARY KEY
FOREIGN KEY REFERENCES
MODULES(MODULE_ID)
LECTURER_ID VARCHAR(8) NOT NULL PRIMARY KEY
FOREIGN KEY REFERENCES
LECTURERS(LECTURER_ID)
20; 21; 22 2022
© The Independent Institute of Education (Pty) Ltd 2022
Page 4 of 10
Requirement Mark Examiner
New database and all tables created correctly. 20
Question 2 (Marks: 20)
Insert the following data into your database tables.
STUDENTS
STUDENT_ID STUDENT_NAME STUDENT_SURNAME
S123456 Neo Petlele
S246810 Derek Moore
S369121 Pedro Ntaba
S654321 Thabo Joe
S987654 Dominique Woolridge
STUDENT_MODULES
STUDENT_ID MODULE_ID
S123456 PROG6211
S123456 PROG6212
S246810 DATA6212
S369121 DATA6212
S369121 INPU221
S369121 WEDE220
S987654 PROG6211
S987654 PROG6212
S987654 WEDE220
MODULES
MODULE_ID MODULE_NAME MODULE_CREDIT
DATA6212 Database Intermediate 30
INPU221 Desktop Publishing 20
PROG6211 Programming 2A 15
PROG6212 Programming 2B 15
WEDE220 Web Development (Intermediate) 20
20; 21; 22 2022
© The Independent Institute of Education (Pty) Ltd 2022
Page 5 of 10
LECTURERS
LECTURER_ID LECTURER_NAME LECTURER_SURNAME
L578963 Kwezi Mbete
L876592 Julia Robins
L916482 Trevor January
LECTURER_MODULES
MODULE_ID LECTURER_ID
DATA6212 L578963
INPU221 L876592
PROG6211 L916482
PROG6212 L916482
WEDE220 L876592
Requirement Mark Examiner
Correct INSERT statements used and all data correctly inserted per
table.
20
20; 21; 22 2022
© The Independent Institute of Education (Pty) Ltd 2022
Page 6 of 10
Question 3 (Marks: 5)
Write an appropriate SQL query to update the STUDENT_SURNAME for the student with
STUDENT_ID ‘S987654’ to ‘Smith’.
Requirement Mark Examiner
Correct UPDATE statement. 1
Correct SET statement. 2
Correct WHERE clause. 2
TOTAL 5
Question 4 (Marks: 10)
Write an appropriate SQL query to display all the STUDENT_SURNAMES and STUDENT_NAMES, as
well as the MODULE_NAMES that the student is registered for. Sort results according to student
surname in ascending order.
Sample results:
STUDENT MODULE
Moore, Derek Database Intermediate
Ntaba, Pedro Database Intermediate
Ntaba, Pedro Desktop Publishing
Ntaba, Pedro Web Development (Intermediate)
Petlele, Neo Programming 2A
Petlele, Neo Programming 2B
Smith, Dominique Programming 2A
Smith, Dominique Programming 2B
Smith, Dominique Web Development (Intermediate)
Joe, Thabo No modules registered
Woolridge, Dominique Database Intermediate
Woolridge, Dominique Programming 2A
Woolridge, Dominique Programming 2B
Woolridge, Dominique Web Development (Intermediate)
Requirement Mark Examiner
Correct SELECT statement with appropriate joining of tables. 5
Correct sorting in ascending order of student surname. 2
Appropriate column names used. 2
TOTAL 10
20; 21; 22 2022
© The Independent Institute of Education (Pty) Ltd 2022
Page 7 of 10
Question 5 (Marks: 15)
Write an appropriate SQL query to display the MODULE_NAME, MODULE_CREDIT and the number of
students registered for each module. Sort the results by the number of students registered,
highest to lowest.
Sample results:
MODULE CREDIT NUMBER OF STUDENTS
Database Intermediate 30 2
Programming 2A 15 2
Web Development (Intermediate) 20 2
Programming 2B 15 1
Desktop Publishing 20 1
Requirement Mark Examiner
Correct SELECT statement with appropriate joining of tables and use of COUNT function. 10
Appropriate column names used. 2
Correct sorting in descending order of number of students. 3
TOTAL 15
20; 21; 22 2022
© The Independent Institute of Education (Pty) Ltd 2022
Page 8 of 10
Question 6 (Marks: 10)
Write an appropriate SQL query to display the LECTURER_NAME and LECTURER_SURNAME of
lecturers who are teaching more than one module. Include the number of modules they are
currently teaching.
Sample results:
LECTURER NAME LECTURER SURNAME NUMBER OF MODULES
Julia Robins 2
Requirement Mark Examiner
Correct SELECT statement with appropriate grouping and use of COUNT function. 6
Appropriate column names used. 2
Correct filtering for lecturers teaching more than one module. 2
TOTAL 10
20; 21; 22 2022
© The Independent Institute of Education (Pty) Ltd 2022
Page 9 of 10
Question 7 (Marks: 10)
Write an appropriate SQL query to display the STUDENT_NAME and STUDENT_SURNAME of students
who are not registered for any modules.
Sample results:
STUDENT NAME STUDENT SURNAME
Thabo Joe
Requirement Mark Examiner
Correct SELECT statement with appropriate joining of tables and filtering for students who are not registered for any modules.
8
Appropriate column names used. 1
Correct filtering for students who are not registered for any modules. 1
TOTAL 10
20; 21; 22 2022
© The Independent Institute of Education (Pty) Ltd 2022
Page 10 of 10
To display all the STUDENT_SURNAMES and STUDENT_NAMES, as well as the MODULE_NAMES that the student is registered for and sort the results according to student surname in ascending order, you can use the following SQL query:
```sql
SELECT STUDENTS.STUDENT_SURNAME, STUDENTS.STUDENT_NAME, MODULES.MODULE_NAME
FROM STUDENTS
JOIN STUDENT_MODULES ON STUDENTS.STUDENT_ID = STUDENT_MODULES.STUDENT_ID
JOIN MODULES ON STUDENT_MODULES.MODULE_ID = MODULES.MODULE_ID
ORDER BY STUDENTS.STUDENT_SURNAME ASC;
```
This query joins the STUDENTS, STUDENT_MODULES, and MODULES tables together and retrieves the STUDENT_SURNAME, STUDENT_NAME, and MODULE_NAME columns. It then sorts the results by STUDENT_SURNAME in ascending order.