SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 32
  1. #1
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DB Tennis - Bank - Question 1

    We're starting afresh with DB tennis (original thread), and all the rules are restated here.

    1. We have a relational schema, with sample data and an ER (Entity-Relationship) diagram.
    2. Someone asks a question and the person who gets the first correct answer gets to post a question of his/her own.
    3. Nobody gets to post a question until it is certified correct. DB tennis threads will have an appointed referee who will run submitted queries against the sample database to judge for correctness. In the event that the referee is unavailable, any member of good standing can volunteer to take his place (since correctness simply means running against the sample database).
    4. We'll currently only ask questions on writing SQL queries until we decide a good way to judge more "theoretical" questions (in the vein of "How can we better design the tables?", etc.).
    5. Use only syntax from the Core SQL package of the SQL:1999 standard. In any case, we're non-too-strict about this unless you use DBMS-specific features.
    6. Practice a good formatting style for writing SQL queries. Here's a good short article (Thanks M@rco for pointing this out in another thread). Also it would be good form to not let your query flow in one long line which causes the webpage to scroll.
    7. Correctness is the deciding factor. Even if more efficient answers come up, the 1st correct answer is the winner. For our purposes, please do come up with suggestions for improvement in terms of efficiency, clarity, etc., regardless of the question already being answered.
    8. Each new DB tennis thread (except those using the same schema) requires a good sample database with sample data. Model the new tennis thread after this one, but make any improvements as you deem fit.
    9. If you intend to re-use the schema (which will be more often the case), please post in the same thread with a subject "DB tennis - id - Question x", where id is a identifying string (use the database purpose - in this thread it is "Bank") and x is the question number. Note this change!
    10. This is not so much a competition as it is (hopefully) a learning experience. I've been wanting to learn from the veterans and experts around here and I figured this would be a good way, and get everyone else in on it too. Hopefully, we can get them to pop in now and then with precious tidbits of advice, demonstrate classic examples of newbie mistakes, etc.
    11. When you submit an answer, do try to explain what you are trying to do for the benefit of everyone else.


    --------------

    The referee for this match is me (redemption).

    DDL (Data Definition Language) to create the tables (MySQL users, change "numeric" to "int", or leave it and MySQL should convert it to a decimal type - let me know of other differences with other DBMSes so I can highlight them):
    Code:
    create table branch
       (branch_name varchar(15) not null unique,
        branch_city varchar(15) not null,
        assets numeric not null,
        primary key(branch_name));
    
    create table account
       (account_number varchar(15) not null unique,
        branch_name varchar(15) not null,
        balance numeric not null,
        primary key(account_number),
        foreign key(branch_name) references branch(branch_name));
    
    create table customer
       (customer_name varchar(15) not null unique,
        customer_street varchar(12) not null,
        customer_city varchar(15) not null,
        primary key(customer_name));
    
    create table loan
       (loan_number varchar(15) not null unique,
        branch_name varchar(15) not null,
        amount numeric not null,
        primary key(loan_number),
        foreign key(branch_name) references branch(branch_name));
    
    create table depositor
       (customer_name varchar(15) not null,
        account_number varchar(15) not null,
        primary key(customer_name, account_number),
        foreign key(account_number) references account(account_number),
        foreign key(customer_name) references customer(customer_name));
    
    create table borrower
       (customer_name varchar(15) not null,
        loan_number varchar(15) not null,
        primary key(customer_name, loan_number),
        foreign key(customer_name) references customer(customer_name),
        foreign key(loan_number) references loan(loan_number));
    Sample data:
    http://openlinx.sf.net/dbtennis/bankData.sql

    ER Diagram (thanks to jofa):
    Attached Images Attached Images
    Last edited by redemption; Aug 31, 2003 at 00:06.

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the 1st question:

    Code:
    Find the names of the branches whose assets are greater
    than the assets of all branches in Brooklyn

  3. #3
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT h.branch_name
    FROM branch h 
    WHERE h.branch_city != 'Brooklyn'
    AND h.assets > 
    (
    	SELECT SUM(l.assets) 
    	FROM branch l
    	WHERE l.branch_city = 'Brooklyn'
    )
    ?


  4. #4
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's incorrect.

    By "whose assets are greater than the assets of all branches in Brooklyn", I meant that a satisfying branch would have more assets than all branches in Brooklyn. I didn't mean "all branches whose assets are greater than the total assets of all branches in Brooklyn" Sorry if I wasn't clear.

  5. #5
    SitePoint Guru
    Join Date
    Sep 1999
    Location
    Singapore
    Posts
    854
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then its a matter of using MAX instead of SUM.

    As in

    Code:
    SELECT h.branch_name
    FROM branch h 
    WHERE h.branch_city != 'Brooklyn'
    AND h.assets > 
    (
    	SELECT MAX(l.assets) 
    	FROM branch l
    	WHERE l.branch_city = 'Brooklyn'
    )
    <edit>
    If this is correct, dhtmlgod deserves the points.
    </edit>

    <edit:2>
    Maybe the question should have been phrased as "whose assets are greater than the assets of any branch in Brooklyn". I misunderstood the question also.
    </edit:2>
    Last edited by duckie; Feb 22, 2003 at 12:54.

  6. #6
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    2nd take

    Code:
    SELECT h.branch_name
    FROM branch h 
    WHERE h.branch_city != 'Brooklyn'
    AND h.assets > 
    ALL(
    	SELECT l.assets
    	FROM branch l
    	WHERE l.branch_city = 'Brooklyn'
    )
    <edit>
    Didn't see Duckie's post
    </edit>

  7. #7
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by duckie

    Code:
    SELECT h.branch_name
    FROM branch h 
    WHERE h.branch_city != 'Brooklyn'
    AND h.assets > 
    (
    	SELECT MAX(l.assets) 
    	FROM branch l
    	WHERE l.branch_city = 'Brooklyn'
    )
    Originally posted by dhtmlgod

    Code:
    SELECT h.branch_name
    FROM branch h 
    WHERE h.branch_city != 'Brooklyn'
    AND h.assets > 
    ALL(
    	SELECT l.assets
    	FROM branch l
    	WHERE l.branch_city = 'Brooklyn'
    )
    These are both correct. There is no need for the 'branch_city != 'Brooklyn' condition though because you'd never get a branch whose city is in Brooklyn.

    Because of my ambiguous question, dhtmlgod will get the next question. Fire away with the next question D. You can post in this thread instead of starting a new one. I'm changing the rules - I think things won't get so messy now to warrant a separate thread.

    Note to MySQL users: the above query won't work in MySQL because of the subselect. How would one write this without a subselect?

    [edit] I forgot to add that the answer is
    Code:
    branch_name
    -----------
    Round Hill
    which has assets of 8000000, while the 'richest' bank in Brooklyn has 7000000.
    Last edited by redemption; Feb 22, 2003 at 13:12.

  8. #8
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by duckie

    <edit:2>
    Maybe the question should have been phrased as "whose assets are greater than the assets of any branch in Brooklyn". I misunderstood the question also.
    </edit:2>
    I would misunderstand that as meaning
    Code:
    SELECT h.branch_name
    FROM branch h
    WHERE h.assets > ANY(
    	SELECT l.assets
    	FROM branch l
    	WHERE l.branch_city = 'Brooklyn'
    )

  9. #9
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I gotta go do some stuff, so duckie can have the question, in fairness, he got the correct answer first, and his would be more efficient (I know it doesn't count, but it is)


  10. #10
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK duckie could you please?

    I'm going away to sleep so could someone volunteer to take over the referee's position and verify correctness while I'm gone? Thanks

  11. #11
    SitePoint Guru
    Join Date
    Sep 1999
    Location
    Singapore
    Posts
    854
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by redemption

    I would misunderstand that as meaning
    Code:
    SELECT h.branch_name
    FROM branch h
    WHERE h.assets > ANY(
    	SELECT l.assets
    	FROM branch l
    	WHERE l.branch_city = 'Brooklyn'
    )
    "whose assets are greater than the asset of the branch in Brooklyn with the largest assets".? Heh...

    Eh, I'm from the same place where redemption is from...its almost 4 a.m. here.

    I doubt if I'll wake before you've finished whatever you need to do dhtmlgod, so please go ahead.

  12. #12
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by redemption
    I'm going away to sleep so could someone volunteer to take over the referee's position ...
    OK, here's your substitute referee


    BTW, you don't need the aliases
    select branch_name
    from branch
    where assets >
    (select max(assets)
    from branch
    where branch_city = 'Brooklyn')


    Next question?
    dhtmlgod or duckie, please

  13. #13
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok... lemme think...

    Code:
    Return the name of each customer and the total amount 
    they have deposited in each bank and how much they owe 
    each bank
    That ok?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, that's a good one

    i haven't seen the data yet but i hope there are some customers with loans and no deposits at all, and vice versa

    bonus marks for when the customer happens to have both a loan and a deposit at the same bank, then collapsing the two into one output row

    this is fun

    going back to the first problem for a moment, how well supported is the ANY keyword? i suppose that as well as correctness, and efficiency, we should also acknowledge portability...

    rudy

  15. #15
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ANY is more MSSQL centric, the ANSI compliant equivilant is SOME I think...

  16. #16
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Errr... jofa, where you post go?

  17. #17
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Errr... the results were sort of incorrect

  18. #18
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahhh, I didn't get a chance to test them before you got rid of them.


  19. #19
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is more correct values, but still a really bad query...
    Code:
    create table #cols
    (
    	col varchar(15) 
    )
    insert #cols 
    select branch_name
    from branch
    
    declare @query varchar(8000)
    declare @query1 varchar(4000)
    declare @query2 varchar(4000)
    declare @col varchar(15)
    select @col = min(col)
    from #cols
    
    set @query1 = 'select ' + char(10) + 'c.customer_name + '' +'' '
    set @query2 = 'select ' + char(10) + 'c.customer_name + '' -'' '
    while @col is not null
    begin
    	set @query1 = @query1 + 
    		',' + char(10) + 'sum(' +
    		'case a.branch_name ' +
    		'when ''' + @col + ''' ' +
    		'then a.balance else 0 end) ' + 
    		'[' + @col + '] '
    	set @query2 = @query2 + 
    		',' + char(10) + 'sum(' +
    		'case l.branch_name ' +
    		'when ''' + @col + ''' ' +
    		'then -l.amount else 0 end) ' +
    		'[' + @col + '] '
    	select @col = min(col)
    	from #cols
    	where col > @col
    end
    drop table #cols
    set @query = @query1 +
    	char(10) + 'from customer c ' +
    	char(10) + 'left join (depositor d ' +
    	char(10) + 'inner join account a ' +
    	char(10) + 'on d.account_number = a.account_number) ' +
    	char(10) + 'on c.customer_name = d.customer_name ' +
    	char(10) + 'group by c.customer_name + '' +'' ' +
    	char(10) + 'union' +
    	char(10) + @query2 +
    	char(10) + 'from customer c ' +
    	char(10) + 'left join (borrower b ' +
    	char(10) + 'inner join loan l ' +
    	char(10) + 'on l.loan_number = b.loan_number) ' +
    	char(10) + 'on c.customer_name = b.customer_name ' +
    	char(10) + 'group by c.customer_name + '' -'' ' +
    	char(10) + 'order by 1 '
    print @query
    exec(@query)
    Final query looks like...
    Code:
    select 
    c.customer_name + ' +' ,
    sum(case a.branch_name when 'Brighton' then a.balance else 0 end) [Brighton] ,
    sum(case a.branch_name when 'Central' then a.balance else 0 end) [Central] ,
    sum(case a.branch_name when 'Downtown' then a.balance else 0 end) [Downtown] ,
    sum(case a.branch_name when 'Mianus' then a.balance else 0 end) [Mianus] ,
    sum(case a.branch_name when 'North Town' then a.balance else 0 end) [North Town] ,
    sum(case a.branch_name when 'Perryridge' then a.balance else 0 end) [Perryridge] ,
    sum(case a.branch_name when 'Pownal' then a.balance else 0 end) [Pownal] ,
    sum(case a.branch_name when 'Redwood' then a.balance else 0 end) [Redwood] ,
    sum(case a.branch_name when 'Round Hill' then a.balance else 0 end) [Round Hill] 
    from customer c 
    left join (depositor d 
    inner join account a 
    on d.account_number = a.account_number) 
    on c.customer_name = d.customer_name 
    group by c.customer_name + ' +' 
    union
    select 
    c.customer_name + ' -' ,
    sum(case l.branch_name when 'Brighton' then -l.amount else 0 end) [Brighton] ,
    sum(case l.branch_name when 'Central' then -l.amount else 0 end) [Central] ,
    sum(case l.branch_name when 'Downtown' then -l.amount else 0 end) [Downtown] ,
    sum(case l.branch_name when 'Mianus' then -l.amount else 0 end) [Mianus] ,
    sum(case l.branch_name when 'North Town' then -l.amount else 0 end) [North Town] ,
    sum(case l.branch_name when 'Perryridge' then -l.amount else 0 end) [Perryridge] ,
    sum(case l.branch_name when 'Pownal' then -l.amount else 0 end) [Pownal] ,
    sum(case l.branch_name when 'Redwood' then -l.amount else 0 end) [Redwood] ,
    sum(case l.branch_name when 'Round Hill' then -l.amount else 0 end) [Round Hill] 
    from customer c 
    left join (borrower b 
    inner join loan l 
    on l.loan_number = b.loan_number) 
    on c.customer_name = b.customer_name 
    group by c.customer_name + ' -' 
    order by 1
    Results:
    Code:
    ;Brighton;Central;Downtown;Mianus;North Town;Perryridge;Pownal;Redwood;Round Hill
    Adams +;0;0;0;0;0;0;0;0;0
    Adams -;0;0;0;0;0;-1300;0;0;0
    Brooks +;0;0;0;0;0;0;0;0;0
    Brooks -;0;0;0;0;0;0;0;0;0
    Curry +;0;0;0;0;0;0;0;0;0
    Curry -;0;0;0;-500;0;0;0;0;0
    Glenn +;0;0;0;0;0;0;0;0;0
    Glenn -;0;0;0;0;0;0;0;0;0
    Green +;0;0;0;0;0;0;0;0;0
    Green -;0;0;0;0;0;0;0;0;0
    Hayes +;0;0;500;0;0;400;0;0;0
    Hayes -;0;0;0;0;0;-1500;0;0;0
    Jackson +;0;0;0;0;0;0;0;0;0
    Jackson -;0;0;-1500;0;0;0;0;0;0
    Johnson +;0;0;500;0;0;900;0;0;0
    Johnson -;0;0;0;0;0;0;0;0;0
    Jones +;750;0;0;0;0;0;0;0;0
    Jones -;0;0;-1000;0;0;0;0;0;0
    Lindsay +;0;0;0;0;0;0;0;700;0
    Lindsay -;0;0;0;0;0;0;0;0;0
    Majeris +;0;850;0;0;0;0;0;0;0
    Majeris -;0;0;0;0;0;0;0;0;0
    McBride +;0;0;0;0;0;0;0;0;0
    McBride -;0;0;0;0;-7500;0;0;0;0
    Smith +;0;0;0;700;625;0;0;0;0
    Smith -;0;-570;0;0;0;0;0;-2000;-900
    Turner +;0;0;0;0;0;0;0;0;350
    Turner -;0;0;0;0;0;0;0;0;0
    Williams +;0;0;0;0;0;0;0;0;0
    Williams -;0;0;-1000;0;0;0;0;0;0
    Last edited by jofa; Feb 22, 2003 at 20:14.

  20. #20
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Erm I think it would be good if everyone posted an explanation of what they're doing along with their answers.

    The "case a.branch_name when 'Brighton' then a.balance else 0 end" is standard SQL?

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    > The "case a.branch_name when 'Brighton'
    > then a.balance else 0 end" is standard SQL?

    yes it is but in this situation it's ugly



  22. #22
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  23. #23
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by r937
    ...in this situation it's ugly
    I know

    Originally posted by jofa
    ... a really bad query...

  24. #24
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by redemption
    Erm I think it would be good if everyone posted an explanation of what they're doing along with their answers.
    I was trying to create some kind of funky crosstab query, that's why I needed the temp table #cols, to create the names of the columns
    Anyway, it was late, and it got really messy when I tried to figure out how to handle the two M:M relationships to customer, and collapse deposits and loans into one row

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, here's my solution, with explanations, as requested

    i solved this in microsoft access 97 using query of query

    in other databases you would define views for the saved queries

    and if your database doesn't support views *cough*mysql*cough* you would have to use temp tables

    Query 2a summarize customer accounts at each branch:
    Code:
    SELECT customer.customer_name, account.branch_name
         , Sum(account.balance) AS total_deposit
    FROM ( customer 
    INNER JOIN depositor 
          ON customer.customer_name = depositor.customer_name )
    INNER JOIN account 
          ON depositor.account_number = account.account_number
    GROUP BY customer.customer_name, account.branch_name;
    Query 2b summarize customer loans at each branch:
    Code:
    SELECT customer.customer_name, loan.branch_name
         , Sum(loan.amount) AS total_loan
    FROM ( customer 
    INNER JOIN borrower 
          ON customer.customer_name = borrower.customer_name )
    INNER JOIN loan 
          ON borrower.loan_number = loan.loan_number
    GROUP BY customer.customer_name, loan.branch_name;
    in each of the above queries, you would use COALESCE instead of the msaccess IIF function in order to make the syntax conform to ANSI SQL

    the next step is a FULL OUTER JOIN between the above views

    one technique to achieve this in databases which do not support the FULL syntax is to UNION the results of a LEFT OUTER JOIN and a RIGHT OUTER JOIN

    in other words,

    table1 full outer join table2

    is equivalent to

    table1 left outer join table2
    union
    table1 right outer join table2


    now, each of the LEFT and RIGHT outer joins will return matched rows, but the UNION removes the duplicates

    (neat, eh?)

    but we are not done yet

    the FULL OUTER JOIN gives us customers with accounts only, customers with loans only, and customers with accounts and loans

    what about customers that have neither accounts nor loans?

    gotcha! does your solution include Brooks, Glenn, and Green?

    the way i read the question, these should be included

    since we are already using UNION to produce our FULL OUTER JOIN, we simply throw the NOT EXISTS query in there as well

    thus we have our final solution:

    Query 2c customers, with accounts and loans by branch:
    Code:
    select Query2a.customer_name, Query2a.branch_name
         , Query2a.total_deposit  
         , IIF( isnull(Query2b.total_loan), 0, Query2b.total_loan ) as total_loan
      from Query2a 
    LEFT JOIN Query2b 
        on Query2a.branch_name = Query2b.branch_name
       and Query2a.customer_name = Query2b.customer_name
    
    UNION
    
    select Query2b.customer_name, Query2b.branch_name
         , IIF( isnull(Query2a.total_deposit), 0, Query2a.total_deposit ) as total_deposit
         , Query2b.total_loan
      from Query2a 
    RIGHT JOIN Query2b 
        on Query2a.customer_name = Query2b.customer_name
       and Query2a.branch_name = Query2b.branch_name
    
    UNION
    
    select customer.customer_name, null, null, null
      from customer 
     where NOT EXISTS
           ( select 1 from borrower 
              where customer_name = customer.customer_name )
       and NOT EXISTS
           ( select 1 from depositor 
              where customer_name = customer.customer_name )
    
    ORDER BY 1, 2;
    Results:
    Code:
    customer   branch       total    total
    name       name        deposit    loan
    Adams      Perryridge      0      1300
    Brooks              
    Curry      Mianus          0       500
    Glenn              
    Green              
    Hayes      Downtown      500         0
    Hayes      Perryridge    400      1500
    Jackson    Downtown        0      1500
    Johnson    Downtown      500         0
    Johnson    Perryridge    900         0
    Jones      Brighton      750         0
    Jones      Downtown        0      1000
    Lindsay    Redwood       700         0
    Majeris    Central       850         0
    McBride    North Town      0      7500
    Smith      Central         0       570
    Smith      Mianus        700         0
    Smith      North Town    625         0
    Smith      Redwood         0      2000
    Smith      Round Hill      0       900
    Turner     Round Hill    350         0
    Williams   Downtown        0      1000
    Quod Erat Demonstrandum


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •