noisyassassin

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

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

Thanks both to obsidian and wickning1, for the help that explained well, and due to that i have now managed to get the oracle mysql queries working.

Thanks obsidian, below i have included the data structure of the tables if that would allow you to help me with the question? Also i am reasonably new to this, using the data included below would you be able to word the answer you have given me to the first question using the data included? Thanks for the speedy response anyway :-) create table customer ( Cust_No number primary key, name varchar2(30) not null, address1 varchar2(40), address2 varchar2(40), area varchar2(40), postcode varchar2(8), phone varchar2(14), credit_limit number default 100 ) pctfree 0 storage ( initial 0K next 2k pctincrease 0); create table part ( part_number varchar2(13) primary key, description varchar2(40), UOM varchar2(10) default 'EACH' not null, PricePerUnit number ) pctfree 0 storage ( initial 0K next 2k pctincrease 0); create table SalesOrder ( Order_No number primary key, cust_no number not null references customer, date_placed date default sysdate not null, date_completed date ) pctfree 0 storage ( initial 0K next 2k pctincrease 0); create table SalesOrderLine ( order_no number, line_no number, part_no varchar2(13) not null references part, Qty_ordered number default 1 not null, Qty_shipped number default 0 not null, date_shipped date, primary key(order_no,line_no) ) pctfree 0 storage ( initial 0K next 2k pctincrease 0);

Hi guys, I am busy doing an database assignment for a hardware supplies company and i am having trouble with two queries. Could anyone help? The queries are what i am trying to do - a ) Write a query to list the customer name and the number of orders they have placed. Include any customers that have not placed orders. Sequence the list by the customer number. b) Write a query to list parts (identity number and first 20 characters of the description) and the total quantity ordered for each part and total value relating to those quantities. Include any parts that have not been ordered. Sequence the list in alphabetical order of description. Thanks in advance!