Create table in Sql/plsql in this topic we covered create table , primary key,constraints,releations in table, datatype,not null constraint and basic course and advance course of create table topic.syntax, parameters, arguments, example

Create Table

SQL CREATE TABLE statement with syntax, examples, and practice exercises. The SQL CREATE TABLE statement allows you to create and define a table.

In this topic cover below sql basic knowledge.

1. Create Table
2. Create table with Primary key constraints
3. Create table with Foreign key constraints
4. Table Relation
5. Add constraints in table
6. Primary key
7. Null and Not null
8. Create table using select 
9. create table with multiple table joins.

Statement of CREATE TABLE - Oracle / PLSQL: CREATE TABLE 

create table : 

Syntax

CREATE TABLE table_name

( 

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  column_n datatype [ NULL | NOT NULL ]

);

while the create table datatype is most important, we can also add null and not null in any column.

null define you can allow a null values

not null define you can not allow null value in specified column.

Example

Oracle CREATE TABLE example.

CREATE TABLE customers

( customer_id number(10) NOT NULL,

  customer_name varchar2(50) NOT NULL,

  address varchar2(150)

);

this will create table with mentioned 3 column in table having not null values. when not null not mentioned it means it will allow null values in address column

datatypes in customers table

number(10) - maximum 10 digit number allowed with no null value allow.

varchar2(50) - maximum 50 character with no null value allow

Primary keys and Foreign keys:

In any master table or transaction table you can add primary key and foreign key

primary key having one or multiple columns.

Example of create table with primary key / constraints

CREATE TABLE customers

( customer_id number(10) NOT NULL,

  customer_name varchar2(50) NOT NULL,

  city varchar2(50),

  CONSTRAINT customers_pk PRIMARY KEY (customer_id)

);

in above example customer id is primary key having constraint name customer_pk , please note that in any constraint you need to add _pk postfix 

to understand the primary key or foreign key as _fk

Foreign key example 

In below table two tables 1. Customers and 2 is section_master , in below example relation between two tables 

CREATE TABLE customers

( customer_id number(10) NOT NULL,

  customer_name varchar2(50) NOT NULL,

  address varchar2(50),

  city varchar2(50),

  state varchar2(25),

  zip_code varchar2(10),

  CONSTRAINT customers_pk PRIMARY KEY (customer_id)

);

CREATE TABLE section_master

( section_id number(10) NOT NULL,

  section_name varchar2(50) NOT NULL,

  CONSTRAINT section_pk PRIMARY KEY (department_id)

);

--Another example of primary key and foreign key.

CREATE TABLE employees

( employee_number number(10) NOT NULL,

  employee_name varchar2(50) NOT NULL,

  department_id number(10),

  salary number(6),

  CONSTRAINT employees_pk PRIMARY KEY (employee_number),

  CONSTRAINT fk_departments

    FOREIGN KEY (department_id)

    REFERENCES departments(department_id)

);

Here one other way to create table using "SELECT" keyword using below syntax 

Syntax:

CREATE TABLE new_table

  AS (SELECT * FROM old_table);

Example

Create Table as select * from employee;

CREATE TABLE Customer_bkp

AS (SELECT *

    FROM customer_master)

;

CREATE TABLE AS SELECT

CREATE TABLE customer_bkp

  AS (SELECT customer_id, address, city, state, zip

      FROM customer_master

      WHERE customer_id < 100);

In above statement you can create the backup of customer master using where condition that only insert the 100 id in table

Create  table as select with multiple tables

CREATE TABLE customers

  AS (SELECT customer_id,city,pin,address,division

      FROM customer_master,customer_division

      WHERE customer_master.customer_id = customer_division.customer_id

      AND division_id < 10);

create table with no values.

syntax:

CREATE TABLE new_table

  AS (SELECT *

      FROM old_table WHERE 1=2);

Example 

create table customer_bkp as select * from customer_master where 1=0;

• Tweet
• Share
• Share
• Share
• Share

About Admin MC3

This is dummy text. It is not meant to be read. Accordingly, it is difficult to figure out when to end it. But then, this is dummy text. It is not meant to be read. Period.

Related Post