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

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)

My code for the tables and the above DML commands

/*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

SELECT * FROM AccountState INNER JOIN AccountUserState ON AccountState.accountNr = AccountUserState.accountNr;

branchNr accountNr balance branchNr accountNr clientNr
--------------------------------------------------------------------------------
10 54 3000.00 10 54 1001
10 54 3000.00 10 54 1002
23 54 1000.00 10 54 1001
23 54 1000.00 10 54 1002
10 77 500.55 10 77 2013
23 88 2000.00 11 88 3913