SQL Lesson 4

Lesson 4:

3)   RENAME:

This is the third command in DDL. This is used to rename the data base object like Table, view, private Synonym and sequence. We can’t rename public synonym instead drop existing and create new with another name as same as old one.

Now we are discussing table, we will discuss about view, synonyms and sequences later.

Syntax: Rename old_table_name to new_table_name;

While doing rename always keep in mind the naming conditions. Don’t use existing object names and oracle keywords.

Ex: Rename EMP to employee;

After rename check this.

Desc EMP;

Then it will throw an error like below.

Then check this.

Desc EMPLOYEE;

Note: Desc means description. It is non SQL command. Actually it doesn’t require SQL Statement terminator (“;”). But I am using SQL Developer so if we didn’t use it, next statements will not work.  Here ‘;’ (Semi colon) is a Sql statement terminator should require in SQL Star Plus.

We can’t rename multiple tables at a time in SQL.

Note: To rename table we will use rename old_name to new_name

         To rename a column we will use, alter table table_name old_col_name to new_col_name.

4)   DROP:

This is also one among the other DDL commands which is used to drop or remove the data base objects. Once we drop the object we can’t get back again up to 9i version. Now with FLASHBACK command we can get back dropped table with data also. We will discuss about ‘Flashback’ later.

So by using drop we can remove table, view, index, synonym …etc

Syntax: DROP TABLE table_name;

Note: Drop column is different which we discussed in ALTER. This is used to remove entire table.

It will remove entire data also. So take care while doing this command.

Ex: Drop table EMP;

Desc EMP;

We can’t drop two or more tables at a time by using this command.

5)   TRUNCATE:

It is used to remove the entire data from table or cluster (We will discuss about cluster in coming lessons). It remains structure of the table. Truncate is faster than DELETE. We can’t use WHERE condition in this so conditional delete is not possible in truncate.

Syntax: Truncate table/cluster table_name/cluster_name

Ex: Truncate table EMP;

After truncate we can’t get back the data. So take care while doing this command.

Difference between Truncate and Drop:

Drop command removes the table as well as it’s structure and data.

Truncate removes only data and it will keep the table structure as it is.

These are all the DDL commands. For all DDL operations implicit commit works so we can’t rollback them. We will talk about Commit and Rollback in TCL Commands section.

Lesson 1
Lesson 2
Lesson 3
Lesson 5
Lesson 6
Lesson 7