SUBMIT ALL your sql statements/scripts in an organized text file. You MUST write down the sql statement for questions No. 3 (a-i) below.

SUBMIT THREE screenshots showing the THREE Final tables respectively.

1. Create a new schema/database and specify the name as College.

2 . Within the new schema/database create the following tables as specified below. NOTE: In order to confirm that you are working within the named database, the database name should be in bold to indicate that it is active. If not, simply double click the database name in the left pane of the workbench to make it active.

3. Using SQL, create the tables and the instructions that follow

STUDENT

StudentNo	StudentName
1100	Jones
1200	Davis
1300	Garrett
1400	Jones

Questions:

Using MySQL

a) Create the Table Student (StudentNo, StudentName).

Data Types and Constraints include:

StudentNo is an integer.

StudentName is string/text using varchar (50) where 50 is the character length/number of characters in the string.

Specify the Primary Key as StudentNo.

b) Modify the table and add the following columns;

StudentLevel, EnrollmentDate both as varchar with a character length 50.

c) Populate the table Student as indicated as above AND insert data into the new columns as follows to match the records respectively;

For StudentLevel (Undergraduate, Undergraduate, Graduate, Graduate)

For EnrollmentDate (Fall 2016, Spring 2016, Spring 2017, Fall 2017)

ACTIVITY

ActivityName	ActivityFee
Golf	65.00
Skiing	200.00
Swimming	50.00
Tennis	85.00

Using MySQL:

d) Create the Table Activity (ActivityName, ActivityFee).

Data Types and Constraints include:

ActivityName as varchar with a character length 50.

ActivityFee as currency in the form numeric(10,2).

Specify the Primary Key as ActivityName.

e) Populate the table Activity as indicated.

f) Update the table Activity and change the activity fee of Tennis 100.

PAYMENT

StudentNo	ActivityName	AmountPaid
1100	Golf	0
1100	Tennis	110
1200	Skiing	0
1200	Swimming	50
1300	Skiing	200
1300	Swimming	20
1400	Golf	65
1400	Swimming	50
1400	Tennis	40

g) Using MySQL:

Create the Table Payment (StudentNo, ActivityName, AmountPaid).

Data Types and Constraints include:

StudentNo is an integer.

ActivityName as varchar with a character length 50.

AmountPaid as currency in the form numeric(10, 2).

Specify the Primary Key as StudentNo and Activity.

Also Specify StudentNo and ActivityName as Foreign Keys.

h) Modify the table Payment and add the following columns;

Status as varchar with a character length 50.

i) Populate the table Payment as indicated as above AND insert data into the new column as follows to match the records respectively;

For any StudentNo whose amount paid matches or is greater than the activity fee, specify the status as PAID

For any StudentNo whose amount paid is 0, specify the status as UNPAID

For any StudentNo whose amount paid is less than the ActivityFee but greater than 0, specify the status as PENDING