In this article, we will have a look at the commonly used SQL scripts to create, drop and alter a database table.
SQL Create Table Command
- 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
To create employee table using SQL, create table syntax is as shown below.
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.
Read DBMS Interview Questions for frequently asked questions on SQL.
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;