Oracle SQL Insert by Using Address Method

Numeric data types are able to store numeric data either positive or negative.

 

NUMBER(Precesion)

1.It used to store numbers without decimal point.

2.Number(2) mean it can store a numeric values between 00 and 99

 

NUMBER(Precesion, [Scale])

1. It is used for values with decimal point.

2.Number(8,2) means it can store values whose after decimal points values can go up to two places 

 

•The precision is the number of digits in a number. It ranges from 1 to 38.

•The scale is the number of digits to the right of the decimal point in a number. It ranges from -84 to 127.

 

Ex:  Col_Name   number(6,2)

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

  12.75

  123.1

  4562.08

  125

  0.75

Ex:  123.456--> Invalid

  0.097----> Invalid

Oracle SQL Insert

 It is used to insert new records into the table.                                                insert into <table_name> (col1, col2,. . . coln) values(val1, val2, .......,val-n);

In above syntax,                                                            

1.    Number of columns and number of values should be same.

2.    Character values and date values should be in single quotes.                         

 Note: Before insert any record, we should ensure, the order of data types of values should be equal to the order of datatypes of columns.

INSERT INTO emp VALUES( 7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'),
 5000, null, 10 );

 

Oracle SQL Create Table Using Select Statement

 Create Table with Select Statement

We can create a table using existing table [along with data].

 

create table emp1 as select * from emp;

 

Creating table with your own column names.

 create table emp2 (empno,ename,job) as select * from emp;

Note: -in the above query two tables no. of columns must be same.

 

Creating table with specified columns.

create table emp3 as select empno,ename from emp;

 

Creating table without table data.

create table emp4 as select * From emp where 1 = 2;

 

  Note: In all databases whenever we are copying a table from another table internally constraints (Primary key, Foreign key) are never copied.

Oracle SQL Create Table Naming Rules

Rules to follow before writing names (Object names, Column Names and Variable Names).    

1.    Name should begin with alphabet.

2.    within the name we can use the below characters.                                              

    a-z, A-Z, 0-9, @, $, # and _ (under score)                                                                    

Ex:       Emp123                                                                                   

            Emp_o11                                                                                 

3.    Names are not case sensitive.                                                                                        

            Emp/emp/EMP all are same.                                                                            

4.    Duplicate names are not allowed.                                                                                  

·       Within a table don't use duplicate column names.                                              

·       Within user account, don’t use duplicate table names.

·       Within a program, don't use duplicate variable names.

5.    Don't use commands or keywords as table names or column names.

6.    spaces are not allowed with in a name.

Min length of name is 1 char and Max length of name is 30 chars.

Valid_Names	Invalid_Names
Emp_info	Emp info  Since blank space with in the name
emp@sal	emp.sal    Since " . " is not valid character
emp123	123emp Since Name is not beginning with alphabet
emp_info	emp-info  Since " -" is not valid char.
emp_table	Table  Since " table " is a reserved word