INF3707 - School of Computing (database design and implementation)

INF3707/202/1/2010




SCHOOL OF COMPUTING

INF3707




TUTORIAL LETTER 202/1/2010




Contents

1 Study material received...........................................................................................................................1

2 Lecturers responsible for INF3707...........................................................................................................2

3. Assignment 2...........................................................................................................................................2




1 Study material received

You should have received the following tutorial letters
Tutorial letter 101 First Tutorial Letter containing important
administrative information

1|Page

, INF3707/202/1/2010


Tutorial Letter 102 Second tutorial letter containing oracle
installation instructions, assignment 1 & 2
Tutorial Letter 201 Solutions to assignment 1
Tutorial Letter 202(This one) Solution to assignment 2


If you are missing any study material, please contact the UNISA Contact Centre at (+27 861 670 411
for RSA calls only or +27 11 670 9000 for international calls) or download it from the myUnisa
website at http://my.unisa.ac.za. Do not contact us for missing or lost study material since we are
unable to assist you with any missing or lost study material.


2 Lecturers responsible for INF3707

The lecturers are Mr Baldreck Chipangura and Dr E Kritzinger.

Please phone us on +27 12 429 6376 or send e‐mails to


3. Assignment 2

Question 1 (Lesson 4)
a. The Human Resources department needs a report that displays the job ID and the average monthly
salary for each job that has an average payroll that is between 5000 and 10000. The report should
not include clerks. Sort the list in descending order of average salary. Sample results are shown
below. Save your query as Q1A.SQL.
[6]
JOB_ID Average Salary
IT_PROG 5760

MK_REP 6000

HR_REP 6500

ST_MAN 7280

FI_ACCOUNT 7920

AC_ACCOUNT 8300

SA_REP 8350

PR_REP 10000



Solution

select job_id, avg(salary)"Average salary" from
employees
where job_id not like '%CLER%' group
by job_id
having avg(salary) between 5000 and 10000 order
by avg(salary)desc



2|Page