| |
GeekInterview.com > Interview Questions > Oracle > SQL Plus
| Print | |
Question: List all customer (user id, name, and e-mail), number of charging
Answer: List all customer (user id, name, and e-mail), number of charging, total charging,
number of transaction and total number of transaction value?
If u have these tables:
1- 'Credit_card'
columns: credit_number,type,value,Customer_ID.
2-'Coupon'
columns: Coupon_number,value,password,Customer_ID
3-'Customer'
columns: Customer_ID,Fname,Mname,lname,E_mail,password,bdate,tot_trans,mobil_no,
hom_no,ex_id_date,current_Balance. |
| February 02, 2008 01:17:53 |
#1 |
| friends_mukesh |
Member Since: February 2008 Total Comments: 1 |
RE: List all customer (user id, name, and e-mail), number of charging
|
Hi, One of the solution is as follow:
select cust.customer_id,fname,mname,lname,e_mail,credit.tot_credit+coupan.tot_coup no_of_recharge, credit.tot_credit_val+coupan.tot_coup_val tot_recharging,tot_trans,credit.tot_credit_val+coupan.tot_coup_val-current_balance transaction_val,cust.current_balance from Customer cust , (select customer_id,count(value) tot_credit,sum(value) tot_credit_val from credit_card group by customer_id) credit, (select customer_id,count(value) tot_coup,sum(value) tot_coup_val from coupan group by coupan.customer_id) coupan where coupan.customer_id=cust.customer_id and cust.customer_id=credit.customer_id; |
| |
Back To Question | |
