Help needed in SQL .....


i have 3 tables as follows....


CUSTOMER(Cust_id[PKEY],Name,Address,City)

VENDOR(Vendor_id[PKEY],Name,Address,City)

TRANSACTION(Tran_id[PKEY],Cust_id,date,Cash_or_credit)


1.I want a List of the names of all those cities which has more vendors operating than the number of customers.

2.I want a List the id and name of all the customers who has done a transaction of more than Rs.1 lakh on December 31, 2000 through credit.


pls help me to get the list..

Thanks in advance

Balaji.C
Balaji

Comments

  • :
    : i have 3 tables as follows....
    :
    :
    : CUSTOMER(Cust_id[PKEY],Name,Address,City)
    :
    : VENDOR(Vendor_id[PKEY],Name,Address,City)
    :
    : TRANSACTION(Tran_id[PKEY],Cust_id,date,Cash_or_credit)
    :
    :
    : 1.I want a List of the names of all those cities which has more vendors operating than the number of customers.
    :
    : 2.I want a List the id and name of all the customers who has done a transaction of more than Rs.1 lakh on December 31, 2000 through credit.
    :
    :
    : pls help me to get the list..
    :
    : Thanks in advance
    :
    : Balaji.C
    : Balaji
    :
    :
    try using this query
    i.
    SELECT DISTINCT customer.city FROM customer, vendor
    GROUP BY customer.city
    HAVING COUNT(vendor.vendor_id) > COUNT(customer.cust_id)

    ii.
    THERE IS NO COLUMN HOLDING THE TRANSACTION AMOUNT. ASSUME THE COLUMN NAME IS TRAN_AMT, TRY USING THE FOLLOWING QUERY
    SELECT customer.cust_id,customer.name FROM customer, transaction
    WHERE transaction.cash_or_credit = 'CREDIT'
    AND transaction.DATE < '31-12-2000'
    GROUP BY customer.cust_id,customer.name
    HAVING SUM(transaction.tran_amt) > Rs.1 lakh

    the date in the second query should be formatted to match that in ur database. also, the sum of 'Rs.1 lakh ' should be written as stored in ur database cos i think that transaction amount should be a number column. just a little modification should make this work but this is the general idea. pls let me know if it works.

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion