SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)

    DB Tennis - Bank - Question 2

    reprinting the ddl so people don't have to go dig through the other threads to find it:
    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));
    
    
    insert into customer  values ('Jones', 'Main', 'Harrison');
    insert into customer  values ('Smith', 'Main', 'Rye');
    insert into customer  values ('Hayes', 'Main', 'Harrison');
    insert into customer  values ('Curry', 'North', 'Rye');
    insert into customer  values ('Lindsay', 'Park', 'Pittsfield');
    insert into customer  values ('Turner', 'Putnam', 'Stamford');
    insert into customer  values ('Williams', 'Nassau', 'Princeton');
    insert into customer  values ('Adams', 'Spring', 'Pittsfield');
    insert into customer  values ('Johnson', 'Alma', 'Palo Alto');
    insert into customer  values ('Glenn', 'Sand Hill', 'Woodside');
    insert into customer  values ('Brooks', 'Senator', 'Brooklyn');
    insert into customer  values ('Green', 'Walnut', 'Stamford');
    insert into customer  values ('Jackson', 'University', 'Salt Lake');
    insert into customer  values ('Majeris', 'First', 'Rye');
    insert into customer  values ('McBride', 'Safety', 'Rye');
    
    insert into branch  values ('Downtown', 'Brooklyn',   900000);
    insert into branch  values ('Redwood', 'Palo Alto',  2100000);
    insert into branch  values ('Perryridge', 'Horseneck',  1700000);
    insert into branch  values ('Mianus', 'Horseneck',   400200);
    insert into branch  values ('Round Hill', 'Horseneck',  8000000);
    insert into branch  values ('Pownal', 'Bennington',   400000);
    insert into branch  values ('North Town', 'Rye',    3700000);
    insert into branch  values ('Brighton', 'Brooklyn',  7000000);
    insert into branch  values ('Central', 'Rye',     400280);
    
    insert into loan  values ('L-17', 'Downtown',  1000);
    insert into loan  values ('L-23', 'Redwood',  2000);
    insert into loan  values ('L-15', 'Perryridge',  1500);
    insert into loan  values ('L-14', 'Downtown',  1500);
    insert into loan  values ('L-93', 'Mianus',  500);
    insert into loan  values ('L-11', 'Round Hill',  900);
    insert into loan  values ('L-16', 'Perryridge',  1300);
    insert into loan  values ('L-20', 'North Town',  7500);
    insert into loan  values ('L-21', 'Central',  570);
    
    insert into account  values ('A-101', 'Downtown',  500);
    insert into account  values ('A-215', 'Mianus',  700);
    insert into account  values ('A-102', 'Perryridge',  400);
    insert into account  values ('A-305', 'Round Hill',  350);
    insert into account  values ('A-201', 'Perryridge',  900);
    insert into account  values ('A-222', 'Redwood',  700);
    insert into account  values ('A-217', 'Brighton',  750);
    insert into account  values ('A-333', 'Central',  850);
    insert into account  values ('A-444', 'North Town',  625);
    
    insert into depositor values ('Johnson','A-101');
    insert into depositor values ('Smith', 'A-215');
    insert into depositor values ('Hayes', 'A-102');
    insert into depositor values ('Hayes', 'A-101');
    insert into depositor values ('Turner', 'A-305');
    insert into depositor values ('Johnson','A-201');
    insert into depositor values ('Jones', 'A-217');
    insert into depositor values ('Lindsay','A-222');
    insert into depositor values ('Majeris','A-333');
    insert into depositor values ('Smith', 'A-444');
    
    insert into borrower values ('Jones', 'L-17');
    insert into borrower values ('Smith', 'L-23');
    insert into borrower values ('Hayes', 'L-15');
    insert into borrower values ('Jackson', 'L-14');
    insert into borrower values ('Curry', 'L-93');
    insert into borrower values ('Smith', 'L-11');
    insert into borrower values ('Williams','L-17');
    insert into borrower values ('Adams', 'L-16');
    insert into borrower values ('McBride', 'L-20');
    insert into borrower values ('Smith', 'L-21');
    QUESTION 2 (a bit easier than the last one):

    list the branches by city, along with the average loan per branch, and flag the branches where the average loan is greater than one tenth of one percent of the branch assets

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select 
    	branch_city, 
    	b.branch_name, 
    	right(space(8) + cast(assets as varchar(8)), 8) assets,
    	left(right(space(12) + cast(coalesce(avg(amount), 0) as varchar(12)), 12), 5) avg_loan,
    	case 
    		when ((assets / 1000) < coalesce(avg(amount), 0)) 
    		then '!' 
    		else '' 
    	end flag
    
    from 
    	branch b
    	left join loan l 
    	on b.branch_name = l.branch_name
    group by 
    	branch_city, b.branch_name, assets
    order by 
    	branch_city, b.branch_name
    Result:
    Code:
    branch_city     branch_name     assets   avg_loan flag 
    --------------- --------------- -------- -------- ---- 
    Bennington      Pownal            400000     0    
    Brooklyn        Brighton         7000000     0    
    Brooklyn        Downtown          900000  1250    !
    Horseneck       Mianus            400200   500    !
    Horseneck       Perryridge       1700000  1400    
    Horseneck       Round Hill       8000000   900    
    Palo Alto       Redwood          2100000  2000    
    Rye             Central           400280   570    !
    Rye             North Town       3700000  7500    !
    Last edited by jofa; Feb 23, 2003 at 13:58.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    beautiful, jofa

    your turn to make up the next question


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


    While I'm trying to think, can someone find a better way to format the numbers?

  5. #5
    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)
    Damn, missed teh last question! Shouldn't have been playing Red Alert!


  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "i'm trying to think, but nothing happens"

    formatting is better left to application code

    you got something against integers, my friend?

  7. #7
    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
    you got something against integers, my friend?
    No, it was more a question about getting a nice right aligned output of the results in this special case
    Integers? I thought the values were decimals?



    And here's #3:
    http://www.sitepointforums.com/showt...threadid=97137
    Last edited by jofa; Feb 23, 2003 at 19:34.


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
  •