Given these 3 already existing tables 1. Write a set of SQL statements (hint: Us

Question

Given these 3 already existing tables

1. Write a set of SQL statements (hint: Use the SQL ALTER TABLE command) to add a FullFeePaid column to ENROLLMENT and populate the column, assuming that the column is NULL. The only possible values for this column are Yes and No. (Compare COURSE.Fee to ENROLLMENT.AmountPaid to determine data values.)

2. Write a set of SQL statements (hint: Use the SQL ALTER TABLE command) to add a FullFeePaid column to ENROLLMENT and populate the column, assuming that the column is NOT NULL. The only possible values for this column are Yes and No. (Compare COURSE.Fee to ENROLLMENT.AmountPaid to determine data values.) What is the difference between the statements for #1 and #2?

3. Write an ALTER TABLE statement to add a CHECK constraint to the ENROLLMENT table to ensure that the value of FullFeePaid is either Yes or No.

ENROLLMENT CustomerNumber CourseNumber Amountpaid 250 350 350 500 500 350 250 0 0 0 0 0 0 0 n5550055 A23355320 C-32-1254 N: E m ... M sto st LI -C1-1 2 3 4 5 6 7 12345678

Explanation / Answer

1.
ALTER TABLE ENROLLMENT
ADD FullFeePaid CHARACTER(3) NULL;

UPDATE ENROLLMENT
SET FullFeePaid='Yes'
Where ENROLLMENT.AmountPaid == COURSE.Fee;

UPDATE ENROLLMENT
SET FullFeePaid='No'
Where NOT ENROLLMENT.AmountPaid == COURSE.Fee;

2.
ALTER TABLE ENROLLMENT
ADD FullFeePaid CHARACTER(3) NOT NULL;

UPDATE ENROLLMENT
SET FullFeePaid='Yes'
Where ENROLLMENT.AmountPaid == COURSE.Fee;

UPDATE ENROLLMENT
SET FullFeePaid ='No'
Where NOT ENROLLMENT.AmountPaid == COURSE.Fee;

3.
ALTER TABLE ENROLLMENT
ADD CHECK FullFeePaid IN('Yes','No');

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.