Thursday, 12 October 2017

create table, create table with constraint, create table with virtual column, create table from another table

Create Table:- Below are the ways to create table 
1) Table without any constraint
2) Table with constraint
        a) with column level constraint
                i) with system defined constraint name
                ii) with user defined constraint name
        b) with table level constraint
                i) with system defined constraint name
                ii) with user defined constraint name
3) Table with default value of column
4) Table with virtual column
5) Table from another table

1) Table without any constraint :-
Example:-       
SQL>create table student1 (regno number(10),name varchar2(25),dob date,course varchar2(10));

2) Table with constraint :-
    a) with column level constraint
                i) with system defined constraint name

Example:-
SQL>create table student2 (regno number(10) primary key ,name varchar2(25),dob date,course varchar2(10));
               
                ii) with user defined constraint name 
Example:-               
SQL>create table student3 (regno number(10) constraint pk_regno primary key ,name varchar2(25),dob date,course varchar2(10));
     
b) with table level constraint
                i) with system defined constraint name
 

Example:-               
SQL>create table student4 (regno number(10),name varchar2(25),dob date,course varchar2(10),primary key(regno));
SQL>create table student5 (regno number(10),name varchar2(25),dob date,course varchar2(10),
                       primary key(regno,name));               
               
                ii) with user defined constraint name
Example:-               
SQL>create table student6 (regno number(10),name varchar2(25),dob date,course varchar2(10),
                       constraint pk_regno_name primary key(regno,name));

More details on constraint please click on constraint-in-oracle-sql

 3) Table with default value of column :- 
Example:-
SQL>create table student1 (regno number(10),name varchar2(25),dob date,course varchar2(10) default 'MCA');

4) Table with virtual column :- From 11g onward,we can create virtual column in a table. Virtual column does not store any data. Data of virtual column is calculated based on the given expression.
We can not perform any DML operation on virtual column but we can use virtual column in "where" condition.  
Example:-
SQL>create table employee (empno number(5),ename varchar2(20),sal number(5),
                         annual_sal generated always as (sal*12) virtual, deptno number(5));

SQL>CREATE TABLE employees (id NUMBER,first_name VARCHAR2(10),last_name VARCHAR2(10),salary NUMBER(9,2),
                        comm1 NUMBER(3),comm2 NUMBER(3),
                        salary1 AS (ROUND(salary*(1+comm1/100),2)),
                        salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,CONSTRAINT employees_pk PRIMARY KEY (id));

5) Table from another table:-We can create a table based on another table
Example:- Create table with data and same structure from another table
SQL>create table employee1 as select * from employee;
Create table with data from another table with selected columns
SQL>create table employee1 as select empno,ename,sal,deptno from employee;
Create table structure only from another table
SQL>create table employee1 as select * from employee where 1=2;
Note:- "1=2" is a false condition, we can give any false condition and query will not select any data.
SQL>create table employee1 as select * from employee where rownum<1;
Note:- "rownum<1" will select no data. that's why only structure will created 

1 comment:

  1. Thanks for the given information you are providing content very usefull to users.
    Learn How to Work With Ref Cursor in ORACLE 12C For Beginner Actual cost of this course is 9,999/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.


    Hurry-Up Enroll Now and get Free Access to the Entire Course


    ReplyDelete

Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...