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.
Using the student tables created earlier, create the following reports:
Report 1: Which units have the worst absentee record? Ignore any that have fewer than two absentees. (Hint - in the report, display the worst unit first, the best last.)
Report 2: Which students have a surname starting or ending with the letter 's'?
When creating the above reports, specify your own column headings rather than using the defaults.
SELECT unit_number "Unit", COUNT(*) "Absences"
WHERE attended = 'A'
GROUP BY unit_number
HAVING COUNT(*) > 1
ORDER BY unit_number DESC;
SELECT student_number "Student", forename "Firstname", surname "Surname"
WHERE UPPER (surname) LIKE 'S%' OR UPPER(surname) LIKE '%S';
Next: Multiple Tables
(c) SQA 2009