Not sure how to do these Create a new schemadatabase and spe

Not sure how to do these..

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

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.

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

IMPORTANT: Refer to Chapter 7 review and follow the syntax as specified. Also remember the tips to avoid minor syntax errors.

STUDENT

StudentID

StudentName

100

Jones

200

Davis

300

Garrett

400

Jones

Questions:

Using MySQL

Create the Table Student (StudentID, StudentName).

Data Types and Constraints include:

StudentID 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 StudentID.

Modify the table and add the following columns;

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

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 2015, Spring 2015, Spring 2016, Fall 2016)

ACTIVITY

ActivityName

ActivityFee

Golf

65.00

Skiing

200.00

Swimming

50.00

Tennis

65.00

Using MySQL:

Create the Table Activity (ActivityName, ActivityFee).

Data Types and Constraints include:

ActivityName as varchar with a character length 50.

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

Specify the Primary Key as ActivityName.

Populate the table Activity as indicated.

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

PAYMENT

StudentID

Activity

AmountPaid

100

Golf

100

Skiing

200

Skiing

200

Swimming

300

Skiing

200

300

Swimming

400

Golf

400

Swimming

400

Tennis

Using MySQL:

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

Data Types and Constraints include:

StudentID is an integer.

ActivityName as varchar with a character length 50.

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

Specify the Primary Key as StudentID and ActivityName.

Also Specify StudentID and ActivityName as Foreign Keys.

Modify the table Payment and add the following columns;

Status as varchar with a character length 50.

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

For any studentID whose amount paid matches the activity fee, specify the status as PAID

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

For any studentID whose amount paid is 0, specify the status as PENDING

StudentID	StudentName
100	Jones
200	Davis
300	Garrett
400	Jones

CREATE TABLE Student{       //creating table named Student
StudentID int,           //defining StudentId column of type integer
StudentName varchar(50),   //defining StudentName column of type variable character length upto 50
PRIMARY KEY (StudentID)       //making StudentID as primary key
}

ALTER TABLE Student //Altering the table Student
MODIFY COLUMN StudentLevel varchar(50) //Adding new column named StudentLevel of type variable character of lentgth 50

ALTER TABLE Student //Altering the table Student
MODIFY COLUMN EnrollmentDate varchar(50) //Adding new column named EnrollmentDate of type variable character of lentth 50

INSERT INTO Student(StudentID,StudentName,StudentLevel,EnrollmentDate) //inserting values into table
VALUES (100,\'jones\',Undergraduate\',\'Fall 2015\');

INSERT INTO Student(StudentID,StudentName,StudentLevel,EnrollmentDate)
VALUES (200,\'Davis\',Undergraduate\',\'Spring 2015\');

INSERT INTO Student(StudentID,StudentName,StudentLevel,EnrollmentDate)
VALUES (300,\'Garrett\',\'Graduate\',\'Spring 2016\');

INSERT INTO Student(StudentID,StudentName,StudentLevel,EnrollmentDate)
VALUES (400,\'jones\',Graduate\',\'Fall 2016\');

---------------------------------------------------------------------------------------------------------
CREATE TABLE Activity{
ActivityName varchar(50),
ActivityFee numeric(10,2),
PRIMARY KEY (ActivityName)

}

INSERT INTO Activity(ActivityName,ActivityFee)
VALUES (\'Golf\',65);

INSERT INTO Activity(ActivityName,ActivityFee)
VALUES (\'Skiing\',200);

INSERT INTO Activity(ActivityName,ActivityFee)
VALUES (\'Swimming\',50);

INSERT INTO Activity(ActivityName,ActivityFee)
VALUES (\'Tennis\',65);

UPDATE Activity //updating the table
SET ActivityFee=100 WHERE ActivityName=\'Tennis\';