SQL: Table, Column restrictions, Datatypes, Apply Comments , Concatenation, Distinct

 

Table restrictions

Ø  Table name must be unique in database

Ø  Table name should start with letter and can be 1 to 30 characters long only

Ø  Max 1000 column allowed in a table

Ø  Table names not case sensitive

Column restrictions

Ø  Column name should start with letter and can be 1 to 30 characters long only

Ø  Column name must be unique in Table

 

Data types in oracle

1.Character datatype

2.Number datatype

3.Date & Time datatype

4.Long, RAW, LongRAW datatypes

 

Applying comment on a table

Ø  Comment on table emp is ‘Employee table’;

Ø  Comment on column emp.ename is ‘Employee name’;

Ø  To see comment on a table

Ø  Select * from user_tab_comments where table_name=’EMP’;

·        Note: Table name should be in upper case

·        Instead of tab we use col for column level comments

Ø  Dropping a comment

Ø  Comment on table emp is ‘ ‘;

Ø  Comment on column emp.ename is ‘ ‘;

v  Sequence of athematic operation on a table are *,/,+,-

Concatenation  || It is used to combine columns

Ø  Select ‘The basic salary of’||’ ‘||ename||’ ‘||’is’||’ ‘||sal’ from emp;

Distinct

Ø  select distinct job, deptno from emp;