Newbie Query [message #223733] |
Sat, 10 March 2007 16:24  |
fes937
Messages: 7 Registered: March 2007
|
Junior Member |
|
|
I've creating a simple telecommunications database with 2 tables.
1. Customer
Customer_ID
Forename
Surname
Age
Tel_Code
Tel_Number
Customer_ID is set as the Primary Key.
2. Phone Call
Call_ID
Call_From
Call_To
Start_Date_Time
End_Date_Time
Charge
Call_ID is set as primary key.
Call_From and Call_To are both set as foreign keys in the customer table.
I've got to create a query as follows:
I've got to show the phone calls made from the telephone number they were made from, with the total number of phone calls, total number of minutes and the total charge.
It might be a simple query but i'm finding it difficult.
I've inserted data in the format below:
INSERT INTO Customer
(Customer_ID, Forename, Surname, Age, Tel_Code, Tel_No)
VALUES (customer_id_Seq.nextval, 'Faisal', 'Arshad', '19', '0123', '1234567');
INSERT INTO Phone_Call(Call_ID, Call_From, Call_To, Start_Date_Time, End_Date_Time, Charge)
VALUES (call_id_Seq.nextval, '1', '2', TO_DATE('12-JAN-2006 10:15:00', 'DD-MON-YYYY HH:MI:SS'), TO_DATE('12-JAN-2006 10:15:55', 'DD-MON-YYYY HH:MI:SS'), '0.95');
I've had no problems inserting the data but I’m struggling with the queries.
I'd appreciate any help.
Thanks
|
|
|
Re: Newbie Query [message #223791 is a reply to message #223733] |
Sun, 11 March 2007 09:53   |
Chris Empatge
Messages: 4 Registered: March 2007
|
Junior Member |
|
|
Ok queries are simpele I am gonna show you one of mine to try and hepl you just anotae it for your problem I hope this helps
SELECT name,address,phone,city
FROM customer
WHERE name='Shape Up';
all quieries look like this i hope it helps you just anote where is used to put in condations ok
|
|
|
Re: Newbie Query [message #223907 is a reply to message #223791] |
Mon, 12 March 2007 04:33   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
To be fair, there are a few optional clauses that your query doesn't demonstrate.
Try something like (untested):SELECT call_from
,call_to
,count(*)
,trunc(sum(end_date_time - start_date_time)*24*60)
,sum(charge)
FROM phone_call
GROUP NY call_from,call_to
|
|
|
|
|
|
Re: Newbie Query [message #223928 is a reply to message #223924] |
Mon, 12 March 2007 05:05  |
martijn
Messages: 286 Registered: December 2006 Location: Netherlands
|
Senior Member |
|
|
aaaaaaaaaaarrrrrrrggggggggghhhhhhhhhh
I was allready wondering ... should I post the message, it's to obvious that NY must be BY.
I guess I got what I deserved.
|
|
|