Jump to content

Archived

This topic is now archived and is closed to further replies.

noisyassassin

Help!! Trouble using sub-queries!!

Recommended Posts

Hi guys hope you can help me with my queries!

What i need to do is the following queries for a vets database, 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)
);

create table vet
(
vetID number(3),
name varchar2(10),
primary key (vetID)
);

create table pettype
(
pettypeID number(2),
pettypename varchar2(10),
legcount number(2),
primary key (pettypeID)
);

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)
);

create table food
(
foodID number(2),
food varchar2(20),
primary key (foodID)
);

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)
);

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)
);

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.