Home » SQL & PL/SQL » SQL & PL/SQL » help with queries
help with queries [message #237094] Fri, 11 May 2007 23:15 Go to next message
cb153
Messages: 7
Registered: May 2007
Junior Member
Help, please?

If I have a database like the following:

create table category (
categoryname varchar(20),
description varchar(20),
overduerate decimal(8,2),
Primary Key (categoryname)
);

create table staff (
name varchar(50),
payrate decimal(8,2),
Primary Key (name)
);

create table datesworked (
workdate date,
name varchar(50),
shiftstart timestamp(0),
shiftend timestamp(0),
Primary Key (workdate, name),
Foreign Key (name) references staff(name)
);

create table customer (
customerid char(7) NOT NULL,
name varchar(50),
birthdate date NOT NULL,
address varchar(100),
telephone char(10),
Primary Key (customerid)
);

create table loanitem (
itemid char(8) NOT NULL,
title varchar(100),
price decimal(8,2),
numberofdays int,
categoryname varchar(20),
Primary Key (itemid),
Foreign Key (categoryname) references category(categoryname)
);

create table loan (
loanid char(6) NOT NULL,
itemid char(8) NOT NULL,
customerid char(7) NOT NULL,
loandate date,
staffname varchar(50),
duedate date,
returndate date,
Primary Key (loanid),
Foreign Key (itemid) references loanitem(itemid),
Foreign Key (customerid) references customer(customerid),
Foreign Key (staffname) references staff(name)
);

How do I do the following queries?

The total number of video and book items on loan?
The names of customers who are not holding any items now?
The average number of items borrowed by a customer on a date?
The names of customers who have got the maximum number of overdue items in the past?

Re: help with queries [message #237096 is a reply to message #237094] Sat, 12 May 2007 00:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hi,

Welcome to the forum.
I am afraid you are going to be disappointed by this post, because it looks like I will not help you. (In fact, I think I do)
This forum is not a please-do-my-homework-for-me. We are professional developers helping out people that have specific problems.
So, the first try is up to you. Try to solve these questions yourself first, and feel welcome to come back when you are stuck. If you do, show us what you tried, what happened and why you don't think it is correct.
Read the sticky on how to get a quick answer.

Good luck!
icon14.gif  Re: help with queries [message #237102 is a reply to message #237094] Sat, 12 May 2007 02:08 Go to previous messageGo to next message
hvvaghani
Messages: 7
Registered: August 2006
Location: Surat,(guj),India
Junior Member

hi friend,

i welcome you to Software industry rather than Forum.

solution of your quesion is simple.hire one oracle-SQL/PL-SQL Developer for your project.


i can look your table design.i think you have to practice for one year under any developer. so you can be able to putup quetions to this forum. this forum is for them who knows some thing and facing difficulties to do something.so don't use them to do your home work.


Best of luck
Re: help with queries [message #237157 is a reply to message #237102] Sat, 12 May 2007 14:26 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
you have to practice for one year (...) so you can be able to put questions to this forum


Right. Don't forget to present some kind of proof to Frank Naude (that you used Oracle for at least one year; preferably certified by Oracle authority on your location) so that he will grant you access to OraFAQ.

./fa/1598/0/
Re: help with queries [message #237543 is a reply to message #237096] Mon, 14 May 2007 18:16 Go to previous messageGo to next message
cb153
Messages: 7
Registered: May 2007
Junior Member
OK, its taken a few days but I managed to figure out 3 of the 4 queries, except for the last one, the names of the customers who have got the maximum number of overdue items in the past.

So if I've got the following:

create table category (
categoryname varchar(20),
description varchar(20),
overduerate decimal(8,2),
Primary Key (categoryname)
);

create table staff (
name varchar(50),
payrate decimal(8,2),
Primary Key (name)
);

create table datesworked (
workdate date,
name varchar(50),
shiftstart timestamp(0),
shiftend timestamp(0),
Primary Key (workdate, name),
Foreign Key (name) references staff(name)
);

create table customer (
customerid char(7) NOT NULL,
name varchar(50),
birthdate date NOT NULL,
address varchar(100),
telephone char(10),
Primary Key (customerid)
);

create table loanitem (
itemid char(8) NOT NULL,
title varchar(100),
price decimal(8,2),
numberofdays int,
categoryname varchar(20),
Primary Key (itemid),
Foreign Key (categoryname) references category(categoryname)
);

create table loan (
loanid char(6) NOT NULL,
itemid char(8) NOT NULL,
customerid char(7) NOT NULL,
loandate date,
staffname varchar(50),
duedate date,
returndate date,
Primary Key (loanid),
Foreign Key (itemid) references loanitem(itemid),
Foreign Key (customerid) references customer(customerid),
Foreign Key (staffname) references staff(name)
);

insert into category values ('popular', 'new release', '3.00');
insert into category values ('fiction', 'fiction', '1.00');
insert into staff values ('Smith John', '12.50');
insert into datesworked values ('5-May-2007', 'Smith John', to_timestamp('2007-05-05 16:00:00', 'YYYY-MM-DD HH24:MI:SS'), to_timestamp('2007-05-05 22:00:00', 'YYYY-MM-DD HH24:MI:SS'));
insert into customer values ('jonj001', 'Jones John', '18-Aug-1969', '1 Nowhere St Adelaide SA', '0886492142');
insert into customer values ('doej001', 'Doe Jane', '1-Apr-1987', '3 Nowhere St Adelaide SA', '0886451142');
insert into loanitem values ('vd000001', 'A Night At The Museum', '3.00', '1', 'popular');
insert into loanitem values ('bk000002', 'The Lord Of The Rings Book 1', '1.00', '7', 'fiction');
insert into loan values ('000002', 'bk000002', 'jonj001', '5-May-2007', 'Smith John', '12-May-2007', '12-May-2007');
insert into video values ('vd000001', 'A Night At The Museum', '3.00', '1', 'popular', '1.5', 'R', 'adult');
insert into book values ('bk000002', 'The Lord Of The Rings Book 1', '1.00', '7', 'fiction', '500');

How do I do this query? (I know that it uses HAVING):

The names of customers who have got the maximum number of overdue items in the past?

I've tried many different formats of queries, including

select name, customerid
from customer2, loan2
group by name
having max(count(returndate > duedate));

select name, customerid
from customer2, loan2
where count(returndate > duedate) AS LateCount
having max(LateCount);

select name, max(count(*))
from customer2, loan2
group by name
having returndate > duedate;

but I can't get it to work.

Have I now shown that I have made enough of an effort to answer my own homework myself?
Could I please have some help, now?
Just a little teeny bit?

Re: help with queries [message #237579 is a reply to message #237543] Tue, 15 May 2007 00:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Smile You're a good boy Wink
(Didn't you enjoy finding the answers yourself a whole lot better than reading them here?)

The way to tackle such queries is to work/think in steps:

To get the name of the people that have the most overdue items, you first have to determine what the "most overdue items" is.
Then you select the people whose overdue items are equal to that max.
So, something like (note: just to help you getting on your way, this is not a valid query)
select name
,      count(overdue)
from   ....
having count(overdue) = select max(count(overdue))
                        from   ....


Will this get you going?
Re: help with queries [message #237683 is a reply to message #237579] Tue, 15 May 2007 08:12 Go to previous messageGo to next message
cb153
Messages: 7
Registered: May 2007
Junior Member
Yes, that helps, thank you.
By the way, I'm a girl not a boy.
Re: help with queries [message #237689 is a reply to message #237683] Tue, 15 May 2007 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
By the way, I'm a girl not a boy

And C is for?

Regards
Michel
Re: help with queries [message #237746 is a reply to message #237689] Tue, 15 May 2007 10:08 Go to previous messageGo to next message
cb153
Messages: 7
Registered: May 2007
Junior Member
C is for Camille.
Re: help with queries [message #237756 is a reply to message #237746] Tue, 15 May 2007 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So, welcome Camille. ./fa/451/0/

Michel
Re: help with queries [message #237882 is a reply to message #237683] Wed, 16 May 2007 01:23 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
cb153 wrote on Tue, 15 May 2007 15:12
Yes, that helps, thank you.
By the way, I'm a girl not a boy.

Good girl..
Wink
Previous Topic: dynamic table name
Next Topic: TABLESPACE PERCENT
Goto Forum:
  


Current Time: Mon Dec 05 15:13:55 CST 2016

Total time taken to generate the page: 0.19074 seconds