SQL Extensions   «Prev  Next»

Adding or Modifying Column - Exercise

Course project: Adding and modifying columns

Objective Add a column and change another column in an existing table.

Exercise scoring

This auto-scored exercise is worth 20 points. When you have completed the simulation, click the Submit button to receive full credit and to review the exercise solution.

Instructions

In the following simulation exercise, you will be asked to perform a task or set of tasks you have learned in the previous lesson. You will be working with the course project, a pet store called House-O-Pets. The instructions will tell you what you need to do at each step but will not tell you exactly how.
There is a correct way to proceed through each screen of the simulation by typing the code in the field requested. If at any point you find that you cannot proceed, go back and re-read the lesson and then navigate through the simulation again.
Identify the states.
1) Identify the states.
Identify the events that trigger the transition between states.
2) Identify the events that trigger the transition between states.
Draw the statechart diagram.
3) Draw the statechart diagram.
Create Table
✅ Initial Table Description 
DESC EMPLOYEE;

Result: 
| Name       | Null?      | Type         |
| ---------- | ---------- | ------------ |
| USERNAME   | NOT NULL   | VARCHAR2(30) |
| FIRSTNAME  | NOT NULL   | VARCHAR2(20) |
| LASTNAME   | (nullable) | VARCHAR2(30) |
| HIRE_DATE  | (nullable) | DATE         |
| JOB_TITLE  | (nullable) | VARCHAR2(20) |
➕ Add a New Column 
ALTER TABLE EMPLOYEE 
ADD (BIRTH_DATE DATE);

✔️ Adds a new column `BIRTH_DATE` of type `DATE`.
✏️ Modify Column with Default Value 
ALTER TABLE EMPLOYEE 
MODIFY (LASTNAME DEFAULT 'LASTNAMEHERE');

✔️ Adds a default value `'LASTNAMEHERE'` for the `LASTNAME` column.
📏 Modify Data Type of an Existing Column 
ALTER TABLE EMPLOYEE 
MODIFY (FIRSTNAME VARCHAR2(35));

✔️ Expands the `FIRSTNAME` column from 20 to 35 characters in width.

  1. This shows you the columns in the EMPLOYEE table as it is now. Your first task is to add a new column at the end of the table. The column is named BIRTH_DATE. It can be null and has no constraints. Type in the command and execute it by pressing ENTER.
  2. Syntax error
  3. ALTER TABLE EMPLOYEE ADD (BIRTH_DATE DATE);
  4. Finish the statement with a semi-colon so it executes immediately.

  1. Change the LASTNAME column to have a default value of 'LASTNAMEHERE'. Type in the command on two lines (refer to the sample syntax in the lesson) and execute it by pressing ENTER.
  2. ALTER TABLE EMPLOYEE
  3. End the first line with the word EMPLOYEE. Finish the statement with a semicolon so it executes immediately.
  4. MODIFY (LASTNAME DEFAULT 'LASTNAMEHERE');
  5. End the first line with the word EMPLOYEE. Finish the statement with a semicolon so it executes immediately.