Using the solution to Activity 1.5, found in the previous section, develop a command file to create the student table and insert records into that table. Use SQL to display the entered rows.
Produce the following reports:
Report 1: the student numbers, name details and department numbers for students 'Jones' or 'Wong' studying in department 'DEP01' or 'DEP04' - sort the results by student number
Report 2: a full attendance report of only those students who attended, excluding those students on unit number 'U00313' or 'U00333' - sort the results by student number within descending attendance date.
SELECT student_number, title, forename, surname, dept_number
WHERE (surname = 'Jones' OR surname = 'Wong')
AND (dept_number = 'DEP01' OR dept_number = 'DEP04')
ORDER BY student_number;
SELECT student_number, unit_number, attendance_date, attended
WHERE attended = 'P'
AND NOT (unit_number = 'U00313' OR unit_number = 'U00333')
ORDER BY attendance_date DESC, student_number;
Next: Further Selection of Rows
(c) SQA 2009