Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 30 March 2017

SQL Create Table




SQL Create Table


 SQL Create statement allows you create database table

Syntax:

CREATE TABLE < table_name>
            (Col1 datatype1,
              Col2 datatype2 …coln datatypen);

Example:
CREATE TABLE Student
(
   no      NUMBER (2),
   name    VARCHAR (10),
   marks   NUMBER (3)
);


Create Table By Using Select Statement


We can create table by existing table or we call it is as copy of existing table by using select statement 

Syntax:

CREATE TABLE < New_table_name> AS
    SELECT  Col1,Col2 …Coln  WHERE ….;


Example:

By following example we can create table along with data.

CREATE TABLE student_copy AS
           SELECT  NO,
                     NAME,
                   MARKS
            FROM student;


Example:

By following example we can create table without data.

CREATE TABLE student_copy AS
           SELECT  NO,
                     NAME,
                   MARKS
            FROM student WHERE 1=2;

Create Table By Using Constrains


 Example:
 
CREATE TABLE emp
(
   empno       NUMBER PRIMARY KEY,
   ename       VARCHAR2 (50) NOT NULL,
   job         VARCHAR2 (10),
   mgr         NUMBER,
   hiredate    DATE DEFAULT (SYSDATE),
   sal         NUMBER,
   hrly_rate   NUMBER,
   comm        NUMBER,
   deptno      NUMBER NOT NULL 
);

Note: To know more about constrains >SQL Constraints
Share this article :

2 comments:

  1. Hi There,
    Grazie! Grazie! Grazie! Your blog is indeed quite interesting around SQL Create statement allows you create database table
    I agree with you on lot of points!

    I have one hierarchy query for tables exchange_grp and node.
    Second hierarchy query for tables node and node_side.


    SELECT E.P_K, E.I4030_EXCH_GRP_CD, N.P_K, N.I4020_NODE_ID, N.I4020_NODE_CAT, LEVEL
    FROM WORK_REP.EXCHANGE_GRP E, WORK_REP.NODE N
    WHERE E.P_K = N.FK_S_EXGRP_NODE
    AND E.I4030_EXCH_GRP_TYPE = 'SVX'
    START WITH N.P_K IN E.NK_S_EXGRP_NODE
    CONNECT BY NOCYCLE N.P_K = PRIOR N.NK_S_EXGRP_NODE

    and

    SELECT N.P_K, NS.P_K, NS.FK_S_NODE_NSIDE, NS.I4040_SIDE_TYPE, LEVEL
    FROM WORK_REP.NODE N, WORK_REP.NODE_SIDE NS
    WHERE N.P_K = NS.FK_S_NODE_NSIDE
    START WITH NS.P_K IN N.NK_S_NODE_NSIDE
    CONNECT BY NOCYCLE NS.P_K = PRIOR NS.NK_S_NODE_NSIDE

    Can we combine these two queries into one.

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Thanks a heaps,
    Preethi

    ReplyDelete
  2. Hi There,

    A spot on observation on what probably is “the” underlying details of the SQL Create Table.Too many people don’t even think about wherever there will be actual demand and more importantly what happens if this demand comes later (or maybe a lot later) than they expect

    How can I amend this select to make a sum & group by a single line....

    SELECT article_ref, status, total_amount
    FROM order_tbl
    GROUP BY article_ref, status, total_amount
    ORDER BY article_ref, status, total_amount;

    tbl_order
    article_ref status total_amount
    123456 Ordered 100
    123456 Ordered 10
    123456 Ordered 80
    123456 Pending 50
    123456 Pending 23
    123456 Pending 50
    123456 Delivered 50
    123456 Delivered 75

    And want the output to be
    123456 Ordered 190
    123456 Pending 123
    123456 Delivered 125
    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Grazie,
    Preethi.

    ReplyDelete