Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

statement

ALTER TABLE Statement in Oracle SQL – PLSQL

by

The ALTER TABLE statement in PLSQL allows us to alter or change the following:

1. Rename an already existing table in database.
2. Add, modify or delete/drop column(s) from an already existing table in database.

It is a Data Definition Language (DDL) statement.
Syntax for the ALTER TABLE to rename an existing table in PLSQL is:

ALTER TABLE table_name
RENAME TO new_table_name;

Let’s take an example for understanding:
Suppose we have a table named “Employee” in database as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 1:

Here we will see how we can use ALTER TABLE statement to rename the ’employee’ table.

ALTER TABLE employee
RENAME TO employee_new;

The above ALTER TABLE statement will rename table ’employee’ as ’employee_new’ in the database. It does not affect the data of the table, hence if you query ’employee_new’ table as;

SELECT *
FROM employee_new;

You will get the following result:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 2:

Here we will see how we can use ALTER TABLE statement to add a column to already existing table named ’employee’.

The syntax for adding a column to already existing table is:

ALTER TABLE table_name
ADD column_name datatype;

Suppose our ’employee’ table has the data as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we will add one more column named “nick_name” in the ’employee’ table by writing the ALTER TABLE statement as:

ALTER TABLE employee
ADD nick_name VARCHAR2(100);

If we query the ’employee’ table as;

SELECT *
FROM employee;

We will get the following output:

Employee_ID Employee_Name Salary Department Commission Nick_Name
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we have successfully added a new column “nick_name” to ’employee’ table.

Scenario 3:

Adding multiple columns to existing database table, the syntax for adding multiple columns using ALTER TABLE statement is:

ALTER TABLE table_name
ADD(column_name1 datatype
,column_name2 datatype
,column_name3 datatype
.
.
.
,column_nameN datatype
);

Suppose we have ’employee’ table as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we want to add 3 columns namely “nick_name”, “city” and “state” to the ’employee’ table. We can achieve the same by writing ALTER TABLE statement as:

ALTER TABLE employee
ADD (nick_name VARCHAR2(100)
     ,city VARCHAR2(200)
     ,state VARCHAR2(200)
    );

If we query the ’employee’ table as;

SELECT *
FROM employee;

We will get the following output:

Employee_ID Employee_Name Salary Department Commission Nick_Name City State
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we have successfully added 3 new columns “nick_name”,”city” and “state” to ’employee’ table using ALTER TABLE statement.

Scenario 4:

Altering or modifying already existing columns in a table.
The syntax of the ALTER TABLE statement to achieve this is;

ALTER TABLE table_name
MODIFY column_name datatype;

Suppose we create an ’employee’ table using the code shown below;

CREATE TABLE employee
(employee_id   NUMBER(10)    NOT NULL
,employee_name VARCHAR2(500) NOT NULL
,salary        NUMBER(20)    NOT NULL
,department    VARCHAR2(300) NOT NULL
,commission    VARCHAR2(100)
);

But then we realize that the “commission” column of the ’employee’ table should have been a NUMBER type rather than being a VARCHAR2 type and it should not allow NULL values.
So instead of deleting the ’employee’ table and creating the same again, we can use the ALTER TABLE statement to change the datatype of the “commission” column from VARCHAR2 to NUMBER as follows:

ALTER TABLE employee
MODIFY commission NUMBER(20) NOT NULL;

The NOT NULL in the code above will force the commission column to have values and will not allow NULL.

Scenario 5:

Altering or modifying multiple columns in a table at once using ALTER TABLE statement.
The syntax of the ALTER TABLE statement to achieve this is;

ALTER TABLE table_name
MODIFY (column_name1 datatype,
,column_name2 datatype
,column_name3 datatype
.
.
,column_nameN datatype
) ;

Suppose we create an ’employee’ table using the code shown below;

CREATE TABLE employee
(employee_id   NUMBER(10)    NOT NULL
,employee_name VARCHAR2(500) NOT NULL
,salary        NUMBER(20)    NOT NULL
,department    VARCHAR2(300) NOT NULL
,commission    VARCHAR2(100)
);

But then we realize that the “commission” column of the ’employee’ table should have been a NUMBER type rather than being a VARCHAR2 type and should not allow NULL values. Also we want the employee_name column to be of 300 length instead of 500.
So instead of deleting the ’employee’ table and creating the same again, we can use the ALTER TABLE statement to make the necessary changes as follows:

ALTER TABLE employee
MODIFY (employee_name VARCHAR2(300)
        ,commission NUMBER(20) NOT NULL
       );

Scenario 6:

Deleting or dropping column in a table using ALTER TABLE statement
The syntax of the ALTER TABLE statement to achieve this is;

ALTER TABLE table_name
DROP COLUMN column_name
) ;

Suppose we create an ’employee’ table using the code shown below;

CREATE TABLE employee
(employee_id   NUMBER(10)    NOT NULL
,employee_name VARCHAR2(500) NOT NULL
,salary        NUMBER(20)    NOT NULL
,department    VARCHAR2(300) NOT NULL
,commission    VARCHAR2(100)
);

But then we realize that the “commission” column is not required and can be dropped from the ’employee’ table.

So instead of deleting the ’employee’ table and creating the same again, we can use the ALTER TABLE statement to make the necessary changes as follows:

ALTER TABLE employee
DROP COLUMN commission;

Scenario 7:

Renaming a column in a table using ALTER TABLE statement

The syntax of the ALTER TABLE statement to rename a column in a table is;

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

Suppose we create an ’employee’ table using the code shown below;

CREATE TABLE employee
(employee_id   NUMBER(10)    NOT NULL
,employee_name VARCHAR2(500) NOT NULL
,salary        NUMBER(20)    NOT NULL
,department    VARCHAR2(300) NOT NULL
,commission    VARCHAR2(100)
);

But then we realize that the “commission” column should be named as “comm”, so instead of deleting the ’employee’ table and creating the same again, we can use the ALTER TABLE statement to make the necessary changes as follows:

ALTER TABLE employee
RENAME COLUMN commission TO comm;

CREATE TABLE Statement in Oracle SQL – PLSQL Using Already Existing Tables

by

The CREATE TABLE statement in SQL allows us to create or define a new table in our database. It is a Data Definition Language (DDL) statement.

Here we will learn how to use CREATE TABLE statement to create new tables using already existing tables in the database.

Syntax for using CREATE TABLE statement to create a new table by already existing table(s) is:

CREATE TABLE new_table_name
AS
(SELECT *
FROM
old_table_name
);

Let’s take an example for understanding:

Suppose we already have a table named “Employee” as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 1:

We want to create a new table named “employee_copy” which should be copy of “Employee” table. For this we need to write the CREATE TABLE statement as follows.

CREATE TABLE employee_copy
AS
(SELECT *
FROM employee);

The above CREATE TABLE statement will create a new table named as employee_copy in the data base having 5 columns as ‘employee_id’,’employee_name’,’salary’,’department’ and ‘commission’. Also all the data from the employee table is copied to the employee_copy table.

If you query employee_copy table as,

SELECT *
FROM employee_copy;

You will get the following result.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 2:

We want to create a new table named “employee_copy” which should be contain only 3 columns of “Employee” table e.g(employee_id, employee_name and salary) . For this we need to write the CREATE TABLE statement as follows.

CREATE TABLE employee_copy
AS
(SELECT employee_id
        ,employee_name
        ,salary
 FROM employee);

The above CREATE TABLE statement will create a new table named as employee_copy in the database having 3 columns as ‘employee_id’,’employee_name’ and ’salary’ from employee table. Also all the data from the employee table of the aforementioned 3 columns will be copied to the employee_copy table.

If you query employee_copy table as,

SELECT *
FROM employee_copy;

You will get the following result:

Employee_ID Employee_Name Salary
101 Emp A 10000
102 Emp B 20000
103 Emp C 28000
104 Emp D 30000
105 Emp E 32000

Scenario 3:

Suppose we also have a department table in our database which has the list of departments as shown below:

Department_Id Department
1000 Sales
2000 IT
3000 Support

And we want to create a new table named “employee_copy” which should be contain only 3 columns of “Employee” table e.g(employee_id, employee_name and salary) and 1 column from the “Department” table e.g. (department). For this we need to write the CREATE TABLE statement as follows.

CREATE TABLE employee_copy
AS
(SELECT employee_id
        ,employee_name
        ,salary
        ,dept.department
 FROM employee emp
      ,department dept
 WHERE emp.department = dept.department);

The above CREATE TABLE statement will create a new table named as employee_copy in the database having 4 columns as ‘employee_id’,’employee_name’ and ’salary’ from employee table and ‘department’ from department table.

The employee_copy table will have the data as shown below:

Employee_ID Employee_Name Salary Department
101 Emp A 10000 Sales
102 Emp B 20000 IT
103 Emp C 28000 IT
104 Emp D 30000 Support
105 Emp E 32000 Sales

CREATE TABLE Statement in Oracle SQL – PLSQL

by

The CREATE TABLE statement in SQL allows us to create or define a new table in our database. It is a Data Definition Language (DDL) statement.

Syntax for the CREATE TABLE statement is:

CREATE TABLE table_name
(column_name1 datatype NULL/NOT NULL
,column_name2 datatype NULL/NOT NULL
,column_name3 datatype NULL/NOT NULL
.
.
);

Each column must have a data type. Also every column has to be defined as NULL or NOT NULL, if nothing is specified in NULL or NOT NULL place then the default value will be assumed as NULL.

Let’s take an example for understanding:

Suppose we want to create a table named “Employee” as shown below (without data).

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we will see how we can use CREATE STATEMENT to create or define a new table in data base.

CREATE TABLE employee
(employee_id   NUMBER(10)    NOT NULL
,employee_name VARCHAR2(500) NOT NULL
,salary        NUMBER(20)    NOT NULL
,department    VARCHAR2(300) NOT NULL
,commission    NUMBER(20)
);

The above CREATE TABLE statement will create a new table named as employee in the data base having 5 columns as ‘employee_id’,’employee_name’,’salary’,’department’ and ‘commission’.

SQL SELECT Statement

by

SQL SELECT StatementThe SQL SELECT Statement allows us to retrieve or fetch records from one or more (by using Oracle SQL Joins) tables from our database.

SQL SELECT Statement Syntax

SELECT column(s)
FROM table_name(s)
WHERE conditions;

SQL SELECT Statement Examples

Suppose we have a table named “Employee” with the data as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we will see what the SQL SELECT Statement does when used in different scenarios

SQL SELECT Statement – Using ‘*’ to view all records from a table

The SQL SELECT Statement can be used with the ‘*’ wildcard character to fetch all the columns and rows from a table.

For example, the below SQL SELECT Statement, once executed, will return all the data from the employee table.

SELECT *
FROM employee;

The result of the above SQL SELECT Statement query will be:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Again, in the above SQL SELECT Statement query, we have used the ‘*’ wildcard character to view all the records from the employee table.

SQL SELECT Statement – Selecting individual fields from a table

We can use the SQL SELECT Statement to select and view only certain fields from a table.

For example, the below SQL SELECT Statement query will return “employee_id”, “employee_name” and “department” fields only from the employee table.

SELECT employee_id
       ,employee_name
       ,department
FROM employee;

The result of the above SQL SELECT Statement query will be;

Employee_ID Employee_Name Department
101 Emp A Sales
102 Emp B IT
103 Emp C IT
104 Emp D Support
105 Emp E Sales

Here we can see that in the SQL SELECT Statement we have explicitly mentioned that we wish to see only the “employee_id”, “employee_name” and “department” columns from the employee table.

SQL SELECT Statement – Using WHERE Clause

The SQL SELECT Statement can be used with the SQL WHERE Clause to filter records based on requirements.

For example, the below SQL SELECT Statement will return “employee_name” and “Salary” of employees working in “Sales” department from the employee table.

SELECT employee_name
       ,salary
FROM employee
WHERE department='Sales';

The data returned by the above SQL SELECT Statement will be:

Employee_Name Salary
EMP A 10000
EMP E 32000

The column names after the SQL SELECT Statement mentions that we want to view the data only for “Employee_Name” and “Salary” columns for “Sales” department only from employee table.

SQL SELECT Statement – Selecting fields from several tables

The SQL SELECT Statement can fetch columns from many tables at once.

For example,

SELECT orders.order_id
       ,suppliers.supplier_name
FROM suppliers
    ,orders
WHERE suppliers.supplier_id = orders.supplier_id;

The above SQL SELECT Statement joins two database tables namely “Orders” and “Suppliers” and returns us the result based in condition that the value for “Supplier_Id” field is available in both the joined tables.

Learn more about Oracle SQL Joins.