Task1 Construct and run the code for a natural inner join on

Task1: Construct and run the code for a natural inner join on the two tables

                        AccountState and AccountUserState

Task 2: Construct and run the code for a natural inner join of

                        AccountState, AccountUserState, ClientProperty

Task 3: Construct and run the code for Right outer join on ClientProperty and

                                    AccountUserState

Task 4: Construct and run the code to sum the account totals for each branch.

Construct your own database for this and enter the data.

AccountNr, BranchNr, ClientNr PlaceCode, are all Int. MoneyAmout is Decimal with 7 digits total with 2 digits to the right of the decimal. CityName and ClientName are Varchar(15)

/*A12.6DB assignment and also used for final quiz

*

* Author: rob r

* Created: Nov 27, 2016

* An extension of Fig 12.22 plus another branch from the

* branch office, see A12.6DBAssignment.doc in IFT 200 folder

*/

drop table AccountState;

drop table AccountUserState;

drop table BranchProperty;

drop table ClientProperty;

create table AccountState(

branchNr Int not null,

accountNr Int not null,

balance Decimal(7,2) not null

);

insert into AccountState values

(10, 54, 3000.00),

(10, 77, 500.55),

(23, 88, 2000.00),

(23, 54, 1000.00);

create table AccountUserState(

branchNr Int not null,

accountNr Int not null,

clientNr Int not null

);

insert into AccountUserState values

(10, 54,1001),

(10, 54,1002),

(10, 77,2013),

(23, 54,7654);

create table BranchProperty(

branchNr Int not null Primary Key,

placeCode Int not null,

cityName Varchar(15)not null

);

insert into BranchProperty values

(10, 228, \'Heber\'),

(23, 357, \'Cornville\'),

(15, 210, \'HorseGulch\');

create table ClientProperty(

clientNr Int not null Primary Key,

clientName Varchar(15) not null

);

insert into CLientProperty values(1001 , \'Jones, ME\'),

(1002, \'Jones,TA\'),

(2013, \'Jones,TA\'),

(7654, \'Seldon, H\'),

(8005, \'Shankara, TA\');

--select *

--from AccountState natural join AccountUserState;

select *

Client ClientName inr has Account N is for is used by Account Money Amount (usd:) Branch (inn) L has balance Place Description has property name near referenced By A12.6DBAssignment questions on various SQL commands 2016rr: A client has a bank account this is identified by n account number and a branch office An account has a balance. A branch office has a location that is near a city this could be a rural branch office That location has a escription. I

Solution

Task 1: select * from AccountState as inner join AccountUserState aus on as.branchNr = aus.branchNr

Task 2: select * from AccountState as inner join AccountUserState aus on as.branchNr = aus.branchNr inner join ClientProperty cp on cp.clientNr = aus.clientNr

Task 3: select * from AccountUserState aus right outer ClientProperty cp on cp.clientNr = aus.clientNr

                                    AccountUserState