ORACLE SQL

1.)Let's say you had a column in the Student table named 'Gender'. Datatype is char(1). Values for all rows for Gender are 'X'.

You want to add a new check constraint to restrict the values to be 'M' or 'F'. If you tried to add that check constraint, it would fail because you have data that already violated the constraint (all those 'X' values).

Here's the SQL to add the check constraint:

ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_CHK1 CHECK (gender in ('M','F') ) ENABLE ;

What option could you add on this constraint that would ignore the existing values? In other words... the 'X' values are ignored, but any new insert/update will check the constraint...?

2.)For triggers... if you had an update trigger... and you wanted to abort the update (stop the update) what is the strategy?

What is the predicate for the trigger? What action can you do to stop the update?

3.)For triggers... if you wanted capture information every time someone created or dropped an object... what is the trigger signature.

4.)How do you write a trigger that would stop an update any time COST in COURSE is increased by more than 10%.

I only want this trigger to fire if COST is updated.

5.)For triggers... give an example of using the 'when' clause in the trigger signature. When should 'when' be used?