SQL Create Table, DROP Table Syntax and SQL Default Command

SQL Create Table Command

To create a table in database, “Create Table” SQL command is used. To create a table, following information need to be specified :

• Command should start with "Create Table"
• After CREATE TABLE SQL command, table name should be specified
• Specify column name with data type and size
• Additional info like table location can also be specified

• create table EMPLOYEE
  (
   EMPLOYEE_ID int not NULL ,
   EMPLOYEE_NAME VARCHAR2(40) not NULL ,
   AGE int
  )
  

  By specifying not NULL for EMPLOYEE_NAME and EMPLOYEE_ID column, we are specifying that there should be value for these two columns when a row is inserted. Since AGE column does not have NULL restriction, data can be inserted in table with no value for age field.

SQL Default Keyword Syntax

Default keyword is used to insert a value into a column when no value is supplied to that column. Let us modify employee table with SQL DEFAULT keyword for AGE field.

create table EMPLOYEE
(
 EMPLOYEE_ID int not NULL ,
 EMPLOYEE_NAME VARCHAR2(40) not NULL ,
 AGE int default 25
)

When a row is inserted into employee table, if age is null 25 will be inserted. In case value is supplied for that field, the value provided by the user will get stored in the age column.

SQL ALTER Table Command

To update a table, “SQL ALTER Table ” statement is used. To alter a table, following information need to be specified :

• After ALTER TABLE keywords, table name should be specified
• To add a column, specify "ADD" followed by column name data type and size after "Alter table" table name
• To Drop a column, specify "DROP" followed by column name after "Alter table" table name

SQL Command for Adding / deleting column example is given below

ALTER Table employee add second_name varchar(50);
ALTER Table employee drop column second_name varchar(50);

SQL DROP Table Command

To delete a table, use “DROP Table” statement followed by table name. SQL syntax for drop table is given below

DROP Table employee;