Skip to main content

Activity 2.4B

1

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.

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.

Part 1

SELECT course_number "Course", COUNT(unit_number) "Units"

FROM unit

GROUP BY course_number;

Output from SQL command

Part 2

SELECT dept_number "Dept", COUNT(student_number) "Students"

FROM student

GROUP BY dept_number

HAVING COUNT(*) > 3;

Output from SQL command

Part 3

SELECT dept_number "Department", SUM(salary) "Salary Bill"

FROM staff

GROUP BY dept_number;

Output from SQL command

Check your answer

Next: Activity 2.4C