Help!! Trouble getting answers using sub-queries!!

[noisyassassin]

Hi guys hope you can help me with my queries!

What i need to do is the following queries, using sub-queries to get the answers:

Query 1
Give the names of those people who own a Cat but not a Dog. Concatenating the forename and surname into one column with the heading Owner Name. This is the only column that should be output.

Query 2
Give the name of the pets that have visited the vets on the most occasions. Outputing only the pet names.

Query 3
Give the names of the two pets that have cost the most in vets bills. Outputing only the pet names.


Thanks in advance!!

p.s.

Below is the data that creates the tables if it helps:

create table owner
(
ownerID number(2),
surname varchar2(10),
forename varchar2(10),
address varchar2(20),
dateofbirth date,
primary key (ownerID)
) pctfree 0
storage (
initial 0K
next 2k
pctincrease 0);

create table vet
(
vetID number(3),
name varchar2(10),
primary key (vetID)
) pctfree 0
storage (
initial 0K
next 2k
pctincrease 0);

create table pettype
(
pettypeID number(2),
pettypename varchar2(10),
legcount number(2),
primary key (pettypeID)
) pctfree 0
storage (
initial 0K
next 2k
pctincrease 0);

create table pet
(
petID number(2),
ownerID number(2),
pettypeID number(2),
petname varchar2(10),
dateofbirth date,
primary key (petID),
foreign key (ownerID) references owner(ownerID),
foreign key (pettypeID) references pettype(pettypeID)
) pctfree 0
storage (
initial 0K
next 2k
pctincrease 0);

create table food
(
foodID number(2),
food varchar2(20),
primary key (foodID)
) pctfree 0
storage (
initial 0K
next 2k
pctincrease 0);

create table diet
(
pettypeID number(2),
foodID number(2),
primary key (pettypeID,foodID),
foreign key (pettypeID) references pettype(pettypeID),
foreign key (foodID) references food(foodID)
) pctfree 0
storage (
initial 0K
next 2k
pctincrease 0);

create table visit
(
visitID number(5),
petID number(2),
vetID number(3),
visitdate date,
treatment varchar2(20),
cost number(4,2),
primary key (visitID),
foreign key (petID) references pet(petID),
foreign key (vetID) references vet(vetID)
) pctfree 0
storage (
initial 0K
next 2k
pctincrease 0);

[wickning1]

Looks like homework, so instead of one solution, I'll give you plenty to think about. I'm only going to answer Question 1 for now though.

This is probably the first one somebody would come up with. It has a subquery in the WHERE:
[code]SELECT CONCAT(o.forename, ' ', o.surname) AS name FROM owner o
INNER JOIN pet p ON p.ownerID=o.ownerID
INNER JOIN pettype t ON t.pettypeID=p.pettypeID
WHERE t.pettypename = 'Cat' AND o.ownerID NOT IN (
    SELECT DISTINCT p1.ownerID FROM pet p1
    INNER JOIN pettype t1 ON t1.pettypeID=p1.pettypeID
    WHERE t1.pettypename = 'Dog'
)[/code]

This is another version, with the subquery in the FROM. It more closely resembles set subtraction:
[code]SELECT CONCAT(o.forename, ' ', o.surname) AS name FROM owner o
INNER JOIN (
    SELECT DISTINCT p.ownerID FROM pet p
    INNER JOIN pettype t ON t.pettypeID=p.pettypeID
    WHERE t.pettypename = 'Cat'
) c ON c.ownerID=o.ownerID
LEFT JOIN (
    SELECT DISTINCT p.ownerID FROM pet p
    INNER JOIN pettype t ON t.pettypeID=p.pettypeID
    WHERE t.pettypename = 'Dog'
) d ON d.ownerID=o.ownerID
WHERE d.ownerID IS NULL[/code]

The two solutions above are MySQL solutions, here's an Oracle solution (since it looks like the homework calls for Oracle). The syntax may still be incorrect, my Oracle is rusty:
[code]SELECT o.forename||' '||o.surname AS name FROM owner o
INNER JOIN (
    SELECT p.ownerID FROM pet p
    INNER JOIN pettype t ON t.pettypeID=p.pettypeID
    WHERE t.pettypename = 'Cat'
MINUS
    SELECT p.ownerID FROM pet p
    INNER JOIN pettype t ON t.pettypeID=p.pettypeID
    WHERE t.pettypename = 'Dog'
) c ON c.ownerID=o.ownerID[/code]

[fenway]

Wow... I wish I had homework that I needed help with... what a guy!