Creating a Table

Creating a Table:

• Table is a collection of organized data in the form of rows and columns. 
• It is used to represent the relations.
• We can create a table by to options: 1. create a new table. 2. create a new table from the existing table

1. create a new table.

• The CREATE TABLE statement is used to create a new table in a database.
• Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

• The column parameters specify the names of the columns of the table.
• The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).
• Example:

CREATE TABLE Students

(

   Student_ID int,

   LastName varchar (255),

   FirstName varchar (255),

   Marks int

);

• Example

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

2. create a new table from the existing table

• A copy of an existing table can also be created using CREATE TABLE.
• The new table gets the same column definitions. All columns or specific columns can be selected.
• If you create a new table using an existing table, the new table will be filled with the existing values from the old table.
• Syntax

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

• Example

CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

How to drop table?

• SQL DROP TABLE statement is used to delete a table and all data from the table.
• User needs to be vary careful while using this statement because once the table is deleted, the entire information stored in the table gets lost forever.
• Syntax:

DROP TABLE table name;

• Example: 

DROP TABLE Student;

How to RENAME TABLE?

• SQL RENAME TABLE statement is used to change the name of table.
• Syntax:

ALTER TABLE table name
RENAME TO new table name;

• Example:

ALTER TABLE Customer
RENAME TO Person;

MODIFYING STRUCTURE OF TABLE:

• ALTER command is used to change the table structure.
• This command helps us change the type and size of existing columns.
• It also help us remove columns
• Rules:
• Oracle does not allow decreasing the column size if data available in that column of the table.
• You may add a new column at any time if not null is not specified for the column.
• if the column is null for every row you can freely change the type and size of the column.
• To modify existing columns
• Syntax
• alter table  modify (column_name new_datatype (new_size), column_name new_datatype (new_size)...)
• Example:
• alter table student modify (city varchar2(25));
• To add new columns
• Syntax
• alter table  add (column_name datatype (size), column_name datatype (size)...)
• Example:
• alter table student add (pin number(6));
• To drop an existing columns
• Syntax
• alter table  drop column column_name
• Example:
• alter table student drop column pin;
• To Rename column
• Syntax
• alter table  rename column <old_column_name> to
• Example:
• alter table student rename column rollno to rno;