Home » SQL & PL/SQL » SQL & PL/SQL » Newbie Query
Newbie Query [message #223733] Sat, 10 March 2007 16:24 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #223914 is a reply to message #223907] Mon, 12 March 2007 04:44 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
"GOUP NY" must be "GROUP BY" I suppose
Re: Newbie Query [message #223922 is a reply to message #223914] Mon, 12 March 2007 04:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Everyone's a critic...... Cool
Re: Newbie Query [message #223924 is a reply to message #223922] Mon, 12 March 2007 04:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Anyway, it says 'GROUP NY', not 'GOUP NY', so that's one typo each.
Re: Newbie Query [message #223928 is a reply to message #223924] Mon, 12 March 2007 05:05 Go to previous message
martijn
Messages: 283
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.

Smile
Previous Topic: How to order data table data on update?
Next Topic: How to get record number of a table without using ROWNUM?
Goto Forum:
  


Current Time: Sun Dec 11 08:16:44 CST 2016

Total time taken to generate the page: 0.04541 seconds