Skip to main content

Activity 2.4C

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:

Report 1: What is the last student number allocated?

Report 2: What is the average salary bill for each department, ignoring any departments with an average salary bill less than or equal to 25,000?

When creating the above reports, specify your own column headings rather than using the defaults.

Report 1

SELECT MAX (student_number) "Max No"

FROM student;

Output from SQL command

Report 2

SELECT dept_number "Dept", AVG(salary) "Average Salary"

FROM staff

GROUP BY dept_number

HAVING AVG(salary) > 25000;

Output from SQL command

Check your answer

Next: Activity 2.4D