Howdy, Stranger!

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

Categories

Help needed in SQL .....

bmurlitharanbmurlitharan Member Posts: 4

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

  • nneomanneoma Member Posts: 61
    :
    : 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.