An already existing table named ENROLLMENT exists with 3 collumns, CustomerNumbe
ID: 3854704 • Letter: A
Question
An already existing table named ENROLLMENT exists with 3 collumns, CustomerNumber, CourseNumber, AmountPaid.
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.)
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.
COURSE: Course Number Course name CourseDate Fee Adv Pastels 10/1/2011 500 Beg oils 9/15/2011 350 2 2 3/15/2011 350 3 3 Int Pastels Beg oils 10/15/2011 350 4 4 Adv Pastels 11/15/2011 500 5 5 CUSTOMER: CustomerNumber CustomerLastName CustomerFirstName Phone 206-567-1234 J Johnson Ariel 2 2 Robin Green 425-678-8765 3 3 Jackson 360-789-3456 Charles 4 4 Pearson Jeffry 206-567-2345 5 5 Sears Miguel 360-789-4567 6 6 425-678-7654 Kyle Leah Myers 7 7 Lynda 360-789-5678Explanation / Answer
ALTER TABLE ENROLLMENT
ADD FullFeePaid varchar(3);
UPDATE ENROLLMENT E
SET E.FullFeePaid= "YES"
where EXISTS(select 1 from COURSE C where C.CourseNumber=E.CourseNumber and C.Fee=E.AmountPaid);
UPDATE ENROLLMENT E
SET E.FullFeePaid= "NO"
where NOT EXISTS(select 1 from COURSE C where C.CourseNumber=E.CourseNumber and C.Fee=E.AmountPaid)
3.ALTER TABLE ENROLLMENT
ADD CONSTRAINT CK_Feepaid CHECK(FullFeePaid in('YES','NO'))
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.