## SQL interview question

Expand Messages
• Hi there: Could somebody pls help me to answer this question: Find out how many customers have a total credit limit (the sum of Credit Limit on all accounts)
Message 1 of 6 , Jun 15, 2005
Hi there:

Could somebody pls help me to answer this question:

Find out how many customers have a total credit limit (the sum of
Credit Limit on all accounts) more than \$10000?

The query is little complicated. An intermidate table is required.

Thank you!!

Sonia

Here is the table

ACCOUNT_NUMBER date REASON
9801453326 08/05/2001 Purchase/Cash APR
9801453327 19/09/2000 Membership Fee
9801453328 03/08/2001 Membership Fee
9801453329 17/02/2003 Membership Fee
9801453330 14/09/2000 Credit Limit
9801453331 27/09/2001 Membership Fee
9801453332 13/09/2000 Credit Limit
9801453333 06/01/2005 Membership Fee
9801453334 27/09/2000 Other Reason
9801453335 29/09/2000 Membership Fee
9801453336 14/09/2000 Credit Limit
9801453337 20/01/2004 Purchase/Cash APR
9801453338 27/10/2000 Service Issue
9801453339 06/01/2004 Purchase/Cash APR
9801453340 24/11/2003 Purchase/Cash APR
9801453341 16/01/2001 Special Transfer
9801453342 13/10/2001 Membership Fee
9801453343 12/09/2000 Credit Limit
• Yeah very complicated question indeed..you have given the very bare minimum details Please provide more details DDL/current DB structure so that we can help
Message 2 of 6 , Jun 15, 2005
Yeah very complicated question indeed..you have given the very bare
minimum details

Please provide more details DDL/current DB structure so that we
can help u out in the same

--- In sqlcon@yahoogroups.com, "indiangirl164" <indiangirl164@y...>
wrote:
> Hi there:
>
> Could somebody pls help me to answer this question:
>
> Find out how many customers have a total credit limit (the sum of
> Credit Limit on all accounts) more than \$10000?
>
> The query is little complicated. An intermidate table is required.
>
> Thank you!!
>
> Sonia
>
>
> Here is the table
>
> ACCOUNT_NUMBER date REASON
> 9801453326 08/05/2001 Purchase/Cash APR
> 9801453327 19/09/2000 Membership Fee
> 9801453328 03/08/2001 Membership Fee
> 9801453329 17/02/2003 Membership Fee
> 9801453330 14/09/2000 Credit Limit
> 9801453331 27/09/2001 Membership Fee
> 9801453332 13/09/2000 Credit Limit
> 9801453333 06/01/2005 Membership Fee
> 9801453334 27/09/2000 Other Reason
> 9801453335 29/09/2000 Membership Fee
> 9801453336 14/09/2000 Credit Limit
> 9801453337 20/01/2004 Purchase/Cash APR
> 9801453338 27/10/2000 Service Issue
> 9801453339 06/01/2004 Purchase/Cash APR
> 9801453340 24/11/2003 Purchase/Cash APR
> 9801453341 16/01/2001 Special Transfer
> 9801453342 13/10/2001 Membership Fee
> 9801453343 12/09/2000 Credit Limit
• Please find my inline comments . send as the all the info . indiangirl164 wrote: Hi there: Could somebody pls help me to answer this
Message 3 of 6 , Jun 15, 2005
send as the all the info .

indiangirl164 <indiangirl164@...> wrote:

Hi there:

Could somebody pls help me to answer this question:

Find out how many customers have  a total credit limit (the sum of
Credit Limit on all accounts) more than \$10000?

Himanshu : What is this all accts ??

The query is little complicated. An intermidate table is required.

Thank you!!

Sonia

Here is the table

ACCOUNT_NUMBER      date      REASON
9801453326      08/05/2001      Purchase/Cash APR
9801453327      19/09/2000      Membership Fee
9801453328      03/08/2001      Membership Fee
9801453329      17/02/2003      Membership Fee
9801453330      14/09/2000      Credit Limit
9801453331      27/09/2001      Membership Fee
9801453332      13/09/2000      Credit Limit
9801453333      06/01/2005      Membership Fee
9801453334      27/09/2000      Other Reason
9801453335      29/09/2000      Membership Fee
9801453336      14/09/2000      Credit Limit
9801453337      20/01/2004      Purchase/Cash APR
9801453338      27/10/2000      Service Issue
9801453339      06/01/2004      Purchase/Cash APR
9801453340      24/11/2003      Purchase/Cash APR
9801453341      16/01/2001      Special Transfer
9801453342      13/10/2001      Membership Fee
9801453343      12/09/2000      Credit Limit

SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
http://www.mugh.net/sql

Visit http://www.mugh.net/dnug to join .NET User Group.

Regards,
Himanshu Kumar Sinha

MCP
Microsoft Corporation
Office: +91 (40) 5547 3617
Mobile: +91 9440871180
Email: v-hsinha@...

Obstacles are those frightful things you see when you fail to focus on your goal

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

• Hi Select distinct CustomerName from creditdetails where acctno in (select acctno from credit group by acctno having sum(credit) 10000) Hope this solve ypur
Message 4 of 6 , Jun 15, 2005
Hi

Select distinct CustomerName from creditdetails where
acctno in (select acctno from credit group by acctno having sum(credit)>10000)

Hope this solve ypur prbs

Let me know if you have any further ques.

regards

Praveen V
--- indiangirl164 <indiangirl164@...> wrote:

> Hi there:
>
> Could somebody pls help me to answer this question:
>
> Find out how many customers have a total credit limit (the sum of
> Credit Limit on all accounts) more than \$10000?
>
> The query is little complicated. An intermidate table is required.
>
> Thank you!!
>
> Sonia
>
>
> Here is the table
>
> ACCOUNT_NUMBER date REASON
> 9801453326 08/05/2001 Purchase/Cash APR
> 9801453327 19/09/2000 Membership Fee
> 9801453328 03/08/2001 Membership Fee
> 9801453329 17/02/2003 Membership Fee
> 9801453330 14/09/2000 Credit Limit
> 9801453331 27/09/2001 Membership Fee
> 9801453332 13/09/2000 Credit Limit
> 9801453333 06/01/2005 Membership Fee
> 9801453334 27/09/2000 Other Reason
> 9801453335 29/09/2000 Membership Fee
> 9801453336 14/09/2000 Credit Limit
> 9801453337 20/01/2004 Purchase/Cash APR
> 9801453338 27/10/2000 Service Issue
> 9801453339 06/01/2004 Purchase/Cash APR
> 9801453340 24/11/2003 Purchase/Cash APR
> 9801453341 16/01/2001 Special Transfer
> 9801453342 13/10/2001 Membership Fee
> 9801453343 12/09/2000 Credit Limit
>
>
>
>
>
>
>

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
• Hi You are grouping the amount in the base of emplyee so the query is Select sum(creditlimit) as taotal,ACCOUNT_NUMBER from group by ACCOUNT_NUMBER
Message 5 of 6 , Jun 15, 2005
Hi

You are grouping the amount in the base  of emplyee

so the query is

Select sum(creditlimit) as taotal,ACCOUNT_NUMBER from <Table>
group by  ACCOUNT_NUMBER having Total > \$10000

indiangirl164 <indiangirl164@...> wrote:
Hi there:

Could somebody pls help me to answer this question:

Find out how many customers have  a total credit limit (the sum of
Credit Limit on all accounts) more than \$10000?

The query is little complicated. An intermidate table is required.

Thank you!!

Sonia

Here is the table

ACCOUNT_NUMBER      date      REASON
9801453326      08/05/2001      Purchase/Cash APR
9801453327      19/09/2000      Membership Fee
9801453328      03/08/2001      Membership Fee
9801453329      17/02/2003      Membership Fee
9801453330      14/09/2000      Credit Limit
9801453331      27/09/2001      Membership Fee
9801453332      13/09/2000      Credit Limit
9801453333      06/01/2005      Membership Fee
9801453334      27/09/2000      Other Reason
9801453335      29/09/2000      Membership Fee
9801453336      14/09/2000      Credit Limit
9801453337      20/01/2004      Purchase/Cash APR
9801453338      27/10/2000      Service Issue
9801453339      06/01/2004      Purchase/Cash APR
9801453340      24/11/2003      Purchase/Cash APR
9801453341      16/01/2001      Special Transfer
9801453342      13/10/2001      Membership Fee
9801453343      12/09/2000      Credit Limit

SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
http://www.mugh.net/sql

Visit http://www.mugh.net/dnug to join .NET User Group.

Biswajit Das
System Analyst(05)
Microsoft.GDCI.FOIT

Discover Yahoo!
Get on-the-go sports scores, stock quotes, news & more. Check it out!

• Hi sonia, Select customername from tablename group by custiomername having sum(creditlimt) 10000 Sanat
Message 6 of 6 , Jun 17, 2005

Hi sonia,
Select customername from tablename group by custiomername having sum(creditlimt)>10000

Sanat

On Thu, 16 Jun 2005 indiangirl164 wrote :

>Hi there:
>
>Could somebody pls help me to answer this question:
>
>Find out how many customers have  a total credit limit (the sum of
>Credit Limit on all accounts) more than \$10000?
>
>The query is little complicated. An intermidate table is required.
>
>Thank you!!
>
>Sonia
>
>
>Here is the table
>
>ACCOUNT_NUMBER     date     REASON
>9801453326     08/05/2001     Purchase/Cash APR
>9801453327     19/09/2000     Membership Fee
>9801453328     03/08/2001     Membership Fee
>9801453329     17/02/2003     Membership Fee
>9801453330     14/09/2000     Credit Limit
>9801453331     27/09/2001     Membership Fee
>9801453332     13/09/2000     Credit Limit
>9801453333     06/01/2005     Membership Fee
>9801453334     27/09/2000     Other Reason
>9801453335     29/09/2000     Membership Fee
>9801453336     14/09/2000     Credit Limit
>9801453337     20/01/2004     Purchase/Cash APR
>9801453338     27/10/2000     Service Issue
>9801453339     06/01/2004     Purchase/Cash APR
>9801453340     24/11/2003     Purchase/Cash APR
>9801453341     16/01/2001     Special Transfer
>9801453342     13/10/2001     Membership Fee
>9801453343     12/09/2000     Credit Limit
>
>
>
>
>
>
>
>
>SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
>http://www.mugh.net/sql
>
>Visit http://www.mugh.net/dnug to join .NET User Group.
>
>
><*> To visit your group on the web, go to:
>    http://groups.yahoo.com/group/sqlcon/
>
><*> To unsubscribe from this group, send an email to:
>    sqlcon-unsubscribe@yahoogroups.com
>
><*> Your use of Yahoo! Groups is subject to:
>    http://docs.yahoo.com/info/terms/
>
>
>

Your message has been successfully submitted and would be delivered to recipients shortly.