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.

Figure 1:


SQL> DESC EMPLOYEE
 Name                          Null?    Type
 ----------------------------- -------- ------------
 USERNAME                      NOT NULL VARCHAR2(30)
 FIRSTNAME                     NOT NULL VARCHAR2(20)
 LASTNAME                               VARCHAR2(30)
 HIRE_DATE                              DATE
 JOB_TITLE                              VARCHAR2(20)

SQL>

Output after executing command 'DESC EMPLOYEE';

Figure 2:


SQL> DESC EMPLOYEE
 Name                          Null?    Type
 ----------------------------- -------- ------------
 USERNAME                      NOT NULL VARCHAR2(30)
 FIRSTNAME                     NOT NULL VARCHAR2(20)
 LASTNAME                               VARCHAR2(30)
 HIRE_DATE                              DATE
 JOB_TITLE                              VARCHAR2(20)

SQL>

SQL> ALTER TABLE EMPLOYEE ADD (BIRTH_DATE DATE);

Table altered.

SQL>

Figure 3:


SQL> DESC EMPLOYEE
 Name                          Null?    Type
 ----------------------------- -------- ------------
 USERNAME                      NOT NULL VARCHAR2(30)
 FIRSTNAME                     NOT NULL VARCHAR2(20)
 LASTNAME                               VARCHAR2(30)
 HIRE_DATE                              DATE
 JOB_TITLE                              VARCHAR2(20)

SQL>

SQL> ALTER TABLE EMPLOYEE ADD (BIRTH_DATE DATE);

Table altered.

SQL>

SQL> DESC EMPLOYEE
 Name                          Null?    Type
 ----------------------------- -------- ------------
 USERNAME                      NOT NULL VARCHAR2(30)
 FIRSTNAME                     NOT NULL VARCHAR2(20)
 LASTNAME                               VARCHAR2(30)
 HIRE_DATE                              DATE
 JOB_TITLE                              VARCHAR2(20)

SQL> ALTER TABLE EMPLOYEE ADD (BIRTH_DATE DATE);

Table altered.

SQL> ALTER TABLE EMPLOYEE
  2  MODIFY (LASTNAME DEFAULT 'LASTNAMEHERE');

Table altered.

SQL>

Figure 4: ✅ 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.

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.
Syntax error
ALTER TABLE EMPLOYEE ADD (BIRTH_DATE DATE);
Finish the statement with a semi-colon so it executes immediately.

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.
ALTER TABLE EMPLOYEE
End the first line with the word EMPLOYEE. Finish the statement with a semicolon so it executes immediately.
MODIFY (LASTNAME DEFAULT 'LASTNAMEHERE');
End the first line with the word EMPLOYEE. Finish the statement with a semicolon so it executes immediately.