the relations are

MEMBERSHIP(membership_number PK, cover_type PK, start_date, end_date)
PERSON(person_number PK, surname, initials, street_address, suburb, , membership_number)
DOCTOR(doctor_number PK, surname, initials, street_address, suburb)
PROVIDED-SERVICE(person_number PK, doctor_number PK, service_date PK, service_number)
REBATE(service_number PK, cover_type PK, refund)
SERVICE(service_number PK, service_name, service_description)


1)What are the surnames, initials, membership-nos and cover-types of those persons who were provided less than the average number of unique services provided to persons in the month of August, 2003.

for this question the explaination is

you first need to calculate what is the average number of unique services provided to people in August 2003. For example, suppose you find that the occurred in August:

person 1 was provided services 1, 2 and 3 (I.e. 3 unique services) during August
person 2 was provided services 1,1,1 and 1 (1 unique service) during August
person 3 was provided services 1,2,3,3 (3 unique services) during August

then the average number of unique services is (3+1+3)/3=7/3=2.33

the person(s) provided less than the average number of unique services (2.33)in August is therefore only person 2 in this example.


i came up with the sql query

select pe.surname,pe.initials,pe.membership_number,m.cove r_type
from membership m,person pe
where pe.membership_number=m.membership_number
and person_number in
(select person_number,
count(*) as unique_service
from provided_service
where service_date between '01-aug-2003' and '30-aug-2003'
group by person_number);

it displays

(select person_number,
*
ERROR at line 5:
ORA-00913: too many values

can anyone tell me how i can correct this one


thanks in advance

You could try something like this:

(UNTESTED)

the above query just returns all the rows from my database

can anyone give me an idea
please

thanks in advance

Wrong, the proof is in the pudding:

SQL>Create table MEMBERSHIP
2 (membership_number number(6)
3 ,cover_type varchar2(3)
4 ,start_date date
5 ,end_date date);

Table created.

SQL>
SQL>insert into membership values (10,'A1','01-JAN-2000','31-DEC-2005');

1 row created.

SQL>insert into membership values (20,'A2','01-JAN-2001','31-DEC-2006');

1 row created.

SQL>insert into membership values (30,'A3','01-JAN-2002','31-DEC-2007');

1 row created.

SQL>
SQL>Create table PERSON
2 (person_number number(7)
3 ,surname varchar2(10)
4 ,initials varchar2(10)
5 ,street_address varchar2(20)
6 ,suburb varchar2(20)
7 ,membership_number number(6));

Table created.

SQL>
SQL>insert into person values (1,'Person-1','P1','Addr1','s1',10);

1 row created.

SQL>insert into person values (2,'Person-2','P2','Addr2','s2',20);

1 row created.

SQL>insert into person values (3,'Person-3','P3','Addr3','s3',30);

1 row created.

SQL>
SQL>Create table PROVIDED_SERVICE
2 (person_number number(7)
3 ,doctor_number number(7)
4 ,service_date date
5 ,service_number number(3));

Table created.

SQL>
SQL>--person 1 was provided services 1, 2 and 3 (I.e. 3 unique services) during August
SQL>--person 2 was provided services 1,1,1 and 1 (1 unique service) during August
SQL>--person 3 was provided services 1,2,3,3 (3 unique services) during August
SQL>
SQL>insert into PROVIDED_SERVICE values(1,11,'01-AUG-2003',1);

1 row created.

SQL>insert into PROVIDED_SERVICE values(1,11,'11-AUG-2003',2);

1 row created.

SQL>insert into PROVIDED_SERVICE values(1,11,'21-AUG-2003',3);

1 row created.

SQL>insert into PROVIDED_SERVICE values(2,12,'02-AUG-2003',1);

1 row created.

SQL>insert into PROVIDED_SERVICE values(2,12,'12-AUG-2003',1);

1 row created.

SQL>insert into PROVIDED_SERVICE values(2,12,'22-AUG-2003',1);

1 row created.

SQL>insert into PROVIDED_SERVICE values(3,13,'03-AUG-2003',1);

1 row created.

SQL>insert into PROVIDED_SERVICE values(3,13,'13-AUG-2003',2);

1 row created.

SQL>insert into PROVIDED_SERVICE values(3,13,'23-AUG-2003',3);

1 row created.

SQL>insert into PROVIDED_SERVICE values(3,13,'24-AUG-2003',3);

1 row created.

SQL>
SQL>commit;

Commit complete.

SQL>select pe.surname
2 , pe.initials
3 , pe.membership_number
4 , m.cover_type
5 from membership m
6 , person pe
7 where pe.membership_number = m.membership_number
8 and (
9 select count(distinct service_number)
10 from provided_service s
11 where s.person_number = pe.person_number
12 and service_date between '01-aug-2003' and '30-aug-2003')
13 < (
14 select AVG(cnt)
15 from (
16 select person_number, count(distinct service_number) cnt
17 from provided_service
18 where service_date between '01-aug-2003' and '30-aug-2003'
19 group by person_number));

SURNAME INITIALS MEMBERSHIP_NUMBER COV
---------- ---------- ----------------- ---
Person-2 P2 20 A2

1 row selected.

SQL>

thanks for your help