Additional Practice for Creating a Script File to Use DDL and DML Statements

Additional Practice for Creating a Script File to use DDL and DML Statements

Created by: Christy Chambers

Create script files that will do all of the following actions:

Script File One:

1.)  Create 2 new tables, one called BankAccount and the other called BankCustomer, using the following guidelines for defining the columns in each:

Bank Account Table:

a.)  account type – 1 character long, values of ‘s’ or ‘c’ only

b.)  account number – 10 characters long

c.)  date opened – a date

d.)  date closed – also a date

e.)  customer id – 10 characters long

f.)  joint customer 1 id – 10 characters long

g.)  joint customer 2 id – 10 characters long

h.)  joint customer 3 id – 10 characters long

i.)  current account balance – should be able to hold up to 999 trillion dollars

j.)  a flag indicating if a minimum balance is required – 1 character only (‘y’ or ‘n’)

k.)  date of last transaction – a date

l.)  type of last transaction – a character indicating if a ‘d’eposit, ‘w’ithdrawl, or balance ‘i’nquiry was the last transaction.

Bank Customer Table:

a.)  customer id – 10 characters long, alphanumeric

b.)  customer first name – 15 characters

c.)  customer last name – 20 characters

d.)  customer middle initial – 1 character

e.)  customer’s data of birth – a date

f.)  customer’s Social Security Number – 9 characters long

g.)  customer’s address line 1 - 20 characters long

h.)  customer’s address line 2 – 20 characters long

i.)  customer’s city – 10 characters long

j.)  customer’s state – 2 characters long

k.)  customer’s zip code – 5 characters long

l.)  customer’s home phone – 10 characters long

m.) customer’s business phone – 10 characters long

n.)  customer’s cell phone – 10 characters long

o.)  customer’s beeper phone – 15 characters long

2. Populate each of these 2 tables with at least 10 customers, and 20 bank accounts. Make sure each account has a matching customer.

Script File Two:

3. Create the following 4 transactions using your tables, (discuss as a group how you would use savepoints, commits and rollbacks in the iSQL*Plus environment, although these commands are not available in Project Marvel):

a.)  Withdraw $100 from a customer’s savings account

b.)  Deposit $100 into a customer’s checking account

c.)  Close another customer’s checking account

d.)  Cancel the closing of the checking account

Script File Three:

4. Modify the tables in the following ways:

a.)  add an “active” account column that will have a value of ‘y’ or ‘n’ (BankAccount table)

b.)  add a minimum balance required column, holding a dollar amount (Bank Account table)

c.)  modify the current balance column to allow upto quadrillion dollars (Bank Account table)

d.)  drop the minimum balance required 1-character column (Bank Account table)

e.)  rename the bank account table to just “Account”; rename the bank customer table to just “Customer”.

f.)  Describe each of the 2 tables, and verify the columns in each.

g.)  Query the user_tables tables, and select the rows where table name is one of the 2 tables created.

After Covering Ch. 11, Constraints, Make these Changes to the Above Scripts:

1.) Change both table definition script files to include specifications about which columns should be:

a.)  Not Null

b.)  Unique

c.)  Primary Key

d.)  Default Values

e.)  Foreign Key

f.)  Checks on specific range of values

2.)  Recreate both tables with new constraints, then re-populate the tables using the same Insert scripts used previously. Review an rejections of rows due to the establishment of new constraints. Make modifications to the insert script where necessary.

3.)  Query the user_constraints table to view all new constraints on the tables.