SQL TABLE

What is a table?

Table is a collection of organized data in the form of rows and columns. It is used to represent the relations.

Example : Information of students is represented in the form of table as:

STUDENT_NAMEADDRESSPHONE
JohnPune9800000000
PaulDelhi9100000000

1. SQL CREATE TABLE

  • SQL CREATE TABLE statement is used to create a table in database.
  • To create a table, it is necessary to assign name initially and then define its columns and the data types of each column.
Syntax:
CREATE TABLE table_name
(
    column_name1 data_type (size),
    column_name2 data_type (size),
    column_name3 data_type (size),
    ….....
    column_namen data_type (size)
);

Example

Create a table of 'Students' that contains four columns; Student ID, Last Name, First Name, Marks.

CREATE TABLE Students
(
   Student_ID int,
   LastName varchar (255),
   FirstName varchar (255),
   Marks int
);


The empty 'Students' table will be visible as:

Student_IDLastNameFirstNameMarks

2. SQL 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: To delete table 'Student' from database, the following syntax is used.

DROP TABLE Student;

3. SQL 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

Consider a table name 'Customer' that already exists in the database and user wishes to change it as 'Person'.

ALTER TABLE Customer
RENAME TO Person;

4. SQL ALTER TABLE

SQL ALTER TABLE statement is used to add, modify or delete columns from existing table.

i) Add new column in existing table.

Syntax:
ALTER TABLE table name ADD column_name column data_type;

Example:

Existing table
Student_IDLastNameFirstNameMarks

Write a query to add new column 'Grade' by using ALTER Statement.
ALTER TABLE 'Student' ADD Grade char (1);

Student_IDLastNameFirstNameMarksGrade

ii) To Drop column in table.

Syntax:
ALTER TABLE table name DROP COLUMN column_name;

iii) To Modify an existing column in SQL.

Syntax:
ALTER TABLE table name;

iv) To Rename column

Syntax:
ALTER TABLE table_name RENAME COLUMN previous name TO new name;
▲