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:
Part 1: How many units are there within each course?
Part 2: Which departments have more than three students?
Part 3: What is the total salary bill for each department in the college?
When creating the above reports, specify your own column headings rather than using the defaults.
SELECT course_number "Course", COUNT(unit_number) "Units"
GROUP BY course_number;
SELECT dept_number "Dept", COUNT(student_number) "Students"
GROUP BY dept_number
HAVING COUNT(*) > 3;
SELECT dept_number "Department", SUM(salary) "Salary Bill"
GROUP BY dept_number;
Next: Activity 2.4C
(c) SQA 2009