Sunday, October 14, 2012

Data Retrival Language(DRL) Overview || Alias Names || Miscellaneous Operations



Data Retrival Language(DRL):
               This is one of the sub languages of SQL. This language is used to retrieve the data from database. It contains only one command.

SELECT:
     Using this command we can retrieve the column data from the existing table. Using this command we can retrieve all the records in a table and also we can retrieve specific records in the table (Using where clause-Discussed in the later sessions).

Syntax: Select * from Table_Name;

Example:

  -----------------------------------------------------------------------------------

ALIAS:
          Alias is a duplicate name or a alternate name for the original column names or expression names or any table names. Whenever we need to present the understandable or meaningful reports to the end user then use alias names. We can provide alias names in 3 levels.

Column Level Alias: Providing alias names for the column is known as column level alias.

Syntax: Select col1 as “<alias_name>”,col2 as “<alias_name>” from <table_name>;

  •  We can write the above command without using ‘as’ also. 

Example: Select eid Employee_Id,ename Employee_Name,Sal from emp;

Expression Level Alias: Providing alias names for the expression is known as expression level alias.

Syntax: Select col1,col2,col3 aliasname from table_name

NOTE: We can’t check the conditions on alias names.
Example: Select col1,col2,col3 aliasname from where aliasname>10000; (WRONG

Table Level Alias: Providing alias names for the tables is known as table level alias.
Example : Select e.eid,e.ename from emp e;


------------------------------------------------------------------------------------------
Other Miscellaneous Operations : 

  • Copy one table to another table. 
           Syntax: create table <table_name> as select * from <table_name>
           Example: create table javanib as select * from emp; 
  • Copy a table without data.
          Syntax: create table <table_name> as select * from <table_name> where <wrong_condition>;
          Example: create table javanib as select * from emp where 1=2; 
  • Copy a table with specific columns. 
          Syntax: create table <table_name> as select col1,col2 from <table_name>;
          Example: create table javanib as select eid,ename from emp;
  • Copy a table with specific columns and without data. 
        Syntax: create table <table_name> as select col1,col2 from <table_name>where <wrong_condition>;
        Example: create table javanib as select eid,ename from emp where 1=2; 
  • Copy the records from one table to another table. 
           Syntax: insert into <table_name> select * from <table_name>;
           Example: insert into javanib select * from emp;

ROWID,ROWNUM :
 =============
          These pseudo columns used to locate the particular row in the table. They give the row description.

Example: select rowid,rownum from javanib;
Example: select * from javanib where row id like ‘C%’;


No comments:

Post a Comment

back to top