Loading ...
Sorry, an error occurred while loading the content.
 

SQL interview question

Expand Messages
  • indiangirl164
    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
    • VISHNAT
      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
      • himanshu sinha
        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
          Please find my inline comments .
          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

        • Praveen.V.
          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
          • Biswajit Das
            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!

            • sanat kumar mishra
              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.
                >
                >Yahoo! Groups Links
                >
                ><*> 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.