1. STORE_JL table columns: (10 points)

Store number (Primary Key)

Store name not null

Street address

Zip code

grand_open_date – use DATE datatype and today’s date as the default value

2. EMPLOYEE_JL table columns: (10 points)

Employee number (Primary Key)

Lastname not null

Firstname

Birthdate – use DATE datatype

Store number (Foreign Key)

Rate (MUST be greater than 7.25 – need constraint for this)

3. INSERT at least three rows in your STORE_JL table. For at least one row, test the default value for grand_open_date so that the DEFAULT value is used (SYSDATE). (6 points)

4. Create a sequence, EMPL_SEQUENCE_JL, that begins with 100 and increments by 100 and uses all other defaults. Use your initials instead of the xx in the sequence name. (5 points)

5. Select the next value of your sequence. Credit is given for the correct command – it does NOT matter what the output value of the sequence is as long as the value is displayed by executing the correct command. (3 points)

6. Insert at least three rows into your EMPLOYEE_xx table. Use the next value of the sequence you created , EMPL_SEQUENCE_JL as the value for the employee ID for every row inserted. (4 points).

7. Test your constraint on the rate column by trying to update a row in the EMPLOYEE_JL table with the value of 6 used for the RATE (you choose the row). It should return an error (3 points).

8. You have been told that you need to store the zip code for employees. Add a column to your EMPLOYEE_JL table which will be used to store each employee’s zipcode. (8 points)

9. Update two rows in the EMPLOYEE_JL table and assign a zipcode (must have a WHERE clause). (you MAY use 2 statements) (10 points).

10. Drop your EMPLOYEE_JL table and make sure it HAS been purged from the recyclebin. (10 points)

DO THIS IN SQL