COURSE NAME: DATA MANAGEMENT

Customer

PKCust_ID

Name

Address

Phone

SSN

Account

PKAcct_ID

Acct_num

Acct_Type

Open_date

Interest_Rate

Transaction

PKDate

PK,FK1Acct_ID

PK,FK2Cust_ID

Amount

Type

Using the data model shown above write SQL statements as specified below. Some questions may require more than one SQL statement to get the full answer. You may make up whatever data values you like to complete the assignments.

SQL Exercises

1. Where are transactions being made? Write SQL statement(s) that will create a new table to track the branches where transactions are performed. Store data about the name, location, and phone number of each branch. Be sure to also modify whatever table is necessary to connect this new table to the existing data.

2. What happens when a new account is opened? Write SQL statement(s) to add data to the tables for a new account. (Go ahead, give yourself a million dollars!)

3. How much money do I have? Write SQL statement(s) to retrieve the current balance for all of your accounts.

4. What happens when an account is closed? Write SQL statement(s) to delete all the data associated with a single account. Extra Credit: Why is deleting these records a bad idea and what can be done differently to close an account?