Skip to main content

Activity 2.4D

1

Inserting Records

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.

Report 1

SELECT unit_number "Unit", COUNT(*) "Absences"

FROM attendance

WHERE attended = 'A'

GROUP BY unit_number

HAVING COUNT(*) > 1

ORDER BY unit_number DESC;

Output from SQL command

Report 2

SELECT student_number "Student", forename "Firstname", surname "Surname"

FROM student

WHERE UPPER (surname) LIKE 'S%' OR UPPER(surname) LIKE '%S';

Output from SQL command

Check your answer

Next: Multiple Tables