Skip to main content

Activity 2.2E

1

Producing Reports

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.

Report 1

SELECT student_number, title, forename, surname, dept_number

FROM student

WHERE (surname = 'Jones' OR surname = 'Wong')

AND (dept_number = 'DEP01' OR dept_number = 'DEP04')

ORDER BY student_number;

Output from the SELECT student_number, title, forename, surname, dept_number FROM student WHERE (surname = 'Jones' OR surname = 'Wong') AND (dept_number = 'DEP01' OR dept_number = 'DEP04') ORDER BY student_number; command

Report 2

SELECT student_number, unit_number, attendance_date, attended

FROM attendance

WHERE attended = 'P'

AND NOT (unit_number = 'U00313' OR unit_number = 'U00333')

ORDER BY attendance_date DESC, student_number;

Output from the SELECT student_number, unit_number, attendance_date, attended FROM attendance WHERE attended = 'P' AND NOT (unit_number = 'U00313' OR unit_number = 'U00333') ORDER BY attendance_date DESC, student_number; command

Check your answer

Next: Further Selection of Rows