SUBMIT ALL your sql statements/scripts in an organized text file. You MUST write
ID: 3864483 • Letter: S
Question
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
StudentNo
StudentName
1100
Jones
1200
Davis
1300
Garrett
1400
Jones
Explanation / Answer
Below is the required SQL statements for the given question. We use
1. CREATE TABLE statement when ever a new Table is to be created
2. CONSTRAINT <constraintname> is used to specify PRIMARY KEY and FOREIGH KEY constraints
3. ALTER TABLE command is used to new column to table
4. INSERT INTO statement is used to insert data values into table. 2 variations of using INSERT is shown - one where we specify teh column names and other when we are providing all values to all columns in the order
5. UPDATE statment is used when we want to change the contents of the table based on some condition
Hope the answers given help. Your feedback is valuable. Please comment and rate the answer if it helped. Thanks.
Answer 3-a
CREATE TABLE STUDENT
(
StudentNo integer,
StudentName varchar(50),
CONSTRAINT PK_Student PRIMARY KEY (StudentNo)
);
Answer 3-b
ALTER TABLE STUDENT
ADD COLUMN StudentLevel varchar(50);
ALTER TABLE STUDENT
ADD COLUMN EnrollmentDate varchar(50);
Answer 3-c
INSERT INTO STUDENT(StudentNo,StudentName,StudentLevel,EnrollmentDate)
VALUES(1100,'Jones','Undergraduate','Fall 2016');
INSERT INTO STUDENT(StudentNo,StudentName,StudentLevel,EnrollmentDate)
VALUES(1200,'Davis','Undergraduate','Sprint 2016');
INSERT INTO STUDENT(StudentNo,StudentName,StudentLevel,EnrollmentDate)
VALUES(1300,'Garrett','Graduate','Spring 2017');
INSERT INTO STUDENT(StudentNo,StudentName,StudentLevel,EnrollmentDate)
VALUES(1400,'Jones','Graduate','Fall 2017');
Answer 3-d
CREATE TABLE ACTIVITY
(
ActivityName varchar(50),
ActivityFee numeric(10,2),
CONSTRAINT PK_Activity PRIMARY KEY (ActivityName)
);
Answer 3-e
INSERT INTO ACTIVITY
VALUES('Golf',65.00);
INSERT INTO ACTIVITY
VALUES('Skiing',200.00);
INSERT INTO ACTIVITY
VALUES('Swimming',50.00);
INSERT INTO ACTIVITY
VALUES('Tennis',85.00);
Answer 3-f
UPDATE ACTIVITY
SET ActivityFee=100
WHERE ActivityName="Tennis";
Answer 3-g
CREATE TABLE Payment
(
StudentNo integer,
ActivityName varchar(50),
AmountPaid numeric(10,2),
CONSTRAINT PK_Payment PRIMARY KEY (StudentNo,ActivityName),
CONSTRAINT FK_Payment_Student FOREIGN KEY (StudentNo) REFERENCES Student(StudentNo),
CONSTRAINT FK_Payment_Activity FOREIGN KEY (ActivityName) REFERENCES Activity(ActivityName)
);
Answer 3-h
ALTER TABLE Payment
ADD COLUMN Status varchar(50)
Answer 3-i
INSERT INTO Payment
VALUES(1100,'Golf',0,'UNPAID');
INSERT INTO Payment
VALUES(1100,'Tennis',110,'PAID');
INSERT INTO Payment
VALUES(1200,'Skiing',0,'UNPAID');
INSERT INTO Payment
VALUES(1200,'Swimming',50,'PAID');
INSERT INTO Payment
VALUES(1300,'Skiing',200,'PAID');
INSERT INTO Payment
VALUES(1300,'Swimming',20,'PENDING');
INSERT INTO Payment
VALUES(1400,'Golf',65,'PAID');
INSERT INTO Payment
VALUES(1400,'Swimming',50,'PAID');
INSERT INTO Payment
VALUES(1400,'Tennis',40,'PENDING');
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.