Modifying Table Columns

View All Scripts Login to Run Script

Script Name Modifying Table Columns
Description Create a table and then modify its definition. Includes adding new columns, altering existing column, renaming columns, and dropping columns.
Area SQL General / SQL Query
Referenced In Database SQL Language Reference
Contributor Oracle
Created Monday October 05, 2015

Statement 1

Selecting rows from the Jobs table, which is inside the HR space. This example uses the existing Jobs table to create a new table. Therefore, checking if there are rows available within the Jobs table.

Display the Jobs table

SELECT * FROM HR.JOBS


JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20080
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2008 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500

19 rows selected.

Statement 2
Creating a new table JOBS_Temp for data manipulation. The table will be same as the existing Jobs table. This is to avoid modifying the original Jobs table.
Create a new table using Jobs table
```
CREATE TABLE JOBS_Temp AS SELECT * FROM HR.JOBS
```
```
Table created.
```
Statement 3
First, verifying if the information from the existing JOBS table has been copied when we created our new table, JOBS_Temp. Therefore, selecting some rows based on a criteria, just to check. Note: We used a criteria to avoid displaying all the rows.
Displaying the information in the newly created table
```
SELECT * FROM JOBS_Temp WHERE MAX_SALARY > 30000
```
```
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
AD_PRES President 20080 40000
```
Statement 4
Altering the data type of the column named JOB_TITLE.
Modifying an existing column definition in the new table
```
ALTER TABLE JOBS_Temp MODIFY(JOB_TITLE VARCHAR2(100))
```
```
Table altered.
```

JOB_ID	JOB_TITLE	MIN_SALARY	MAX_SALARY
AD_PRES	President	20080	40000
AD_VP	Administration Vice President	15000	30000
AD_ASST	Administration Assistant	3000	6000
FI_MGR	Finance Manager	8200	16000
FI_ACCOUNT	Accountant	4200	9000
AC_MGR	Accounting Manager	8200	16000
AC_ACCOUNT	Public Accountant	4200	9000
SA_MAN	Sales Manager	10000	20080
SA_REP	Sales Representative	6000	12008
PU_MAN	Purchasing Manager	8000	15000
PU_CLERK	Purchasing Clerk	2500	5500
ST_MAN	Stock Manager	5500	8500
ST_CLERK	Stock Clerk	2008	5000
SH_CLERK	Shipping Clerk	2500	5500
IT_PROG	Programmer	4000	10000
MK_MAN	Marketing Manager	9000	15000
MK_REP	Marketing Representative	4000	9000
HR_REP	Human Resources Representative	4000	9000
PR_REP	Public Relations Representative	4500	10500

JOB_ID	JOB_TITLE	MIN_SALARY	MAX_SALARY
AD_PRES	President	20080	40000

Statement 5

Adding new columns to the JOBS_Temp table using alter table command.

Adding new columns into the new table

ALTER TABLE JOBS_Temp   
      ADD (BONUS NUMBER (7,2), COMM NUMBER (5,2), DUMMY NUMBER(2))

Table altered.

Statement 6

Verifying if the newly added columns have been created correctly, by displaying the information.

Displaying the newly added columns from the table

SELECT JOB_ID, BONUS, COMM, DUMMY FROM JOBS_Temp WHERE MAX_SALARY > 20000


JOB_ID BONUS COMM DUMMY
AD_PRES  -  -  - 
AD_VP  -  -  - 
SA_MAN  -  -  - 

3 rows selected.

Statement 7
Renaming an existing column to a new name using alter table command.
Renaming a column using alter table
```
ALTER TABLE JOBS_Temp  
      RENAME COLUMN COMM TO COMMISSION
```
```
Table altered.
```

JOB_ID	BONUS	COMM	DUMMY
AD_PRES	-	-	-
AD_VP	-	-	-
SA_MAN	-	-	-

Statement 8

Verifying if the modification was applied correctly using Select statement.

Displaying the modified column

SELECT JOB_ID, COMMISSION FROM JOBS_Temp WHERE MAX_SALARY > 20000


JOB_ID COMMISSION
AD_PRES  - 
AD_VP  - 
SA_MAN  - 

3 rows selected.

Statement 9
Drop a column using alter table command.
Dropping a column using alter table
```
ALTER TABLE JOBS_Temp DROP COLUMN DUMMY
```
```
Table altered.
```
Statement 10
Dropping multiple columns using alter table command.
Dropping multiple columns using alter table
```
ALTER TABLE JOBS_Temp DROP (BONUS, COMMISSION)
```
```
Table altered.
```
Statement 11
Display the table after dropping the columns. Note: This example uses a condition only to restrict the number of rows displayed on the screen. You can display all the columns, if you need to.
Display the table information after dropping the columns
```
SELECT * FROM JOBS_Temp WHERE MAX_SALARY > 20000
```
```
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
SA_MAN Sales Manager 10000 20080

3 rows selected.
```

JOB_ID	COMMISSION
AD_PRES	-
AD_VP	-
SA_MAN	-

JOB_ID	JOB_TITLE	MIN_SALARY	MAX_SALARY
AD_PRES	President	20080	40000
AD_VP	Administration Vice President	15000	30000
SA_MAN	Sales Manager	10000	20080

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database