SQL Create Table, DROP Table Syntax and SQL Default Command

    7 Votes

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

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

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;
Planning to do an MBA?
A quick all-in-one MBA entry manual for MBA Aspirants. Book covers
  • Possible MBA Tests & Exam Preparation
  • Tips to choose right MBA Program
  • Essay, Resume & Letter of Recommendation
  • MBA Interview Preparation
  • MBA Financial Planning
Price - 6.99$
 

Popular Videos

How to speak to people

How to speak so that people want to listen.

Got a tip or Question?
Let us know

Related Articles

RDBMS and DBMS Introduction, Concepts and Basics
Primary Key, Foreign Key and Unique Key
SQL Select with Order By, Insert, Update and Delete Command
SQL Operators - Where, IN, AND, OR and Between
SQL Like and Distinct Command
SQL Join Command - Inner, Outer and Left Join
SQL Mathematical Functions, Concat and Trim Commands
SQL Group By and Having Command
Difference between having and where clause
Difference between Inner join and Left join
Clustered Index vs Non Clustered Index