Examples to CREATE and DROP tables

 

• Create and Drop tables in the database.

Estimated time to complete: 5 minutes

CREATE TABLE statement

In the previous video, we saw the general syntax to create a table:

1
2
3
4
5
6
CREATE TABLE TableName (
   COLUMN1 datatype,
   COLUMN2 datatype,
   COLUMN3 datatype, 
   ...
);

Consider the following examples:

1. Create a TEST table with two columns - ID of type integer and NAME of type varchar. For this, we use the following SQL statement.

1
2
3
4
CREATE TABLE TEST (
   ID int,
   NAME varchar(30)
);

2. Create a COUNTRY table with an integer ID column, a two-letter country code column, and a variable length country name column. For this, we may use the following SQL statement.

1
2
3
4
5
CREATE TABLE COUNTRY (
   ID int,
   CCODE char(2),
   Name varchar(60)
);

3. In the example above, make ID a primary key. Then, the statement will be modified as shown below.

1
2
3
4
5
6
CREATE TABLE COUNTRY (
   ID int NOT NULL,
   CCODE char(2),
   Name varchar(60)
   PRIMARY KEY (ID)
);

In the above example, the ID column has the NOT NULL constraint added after the datatype, meaning that it cannot contain a NULL or an empty value. This is added since the database does not allow Primary Keys to have NULL values.

DROP TABLE

If the table you are trying to create already exists in the database, you will get an error indicating table XXX.YYY already exists. To circumvent this error, create a table with a different name or first DROP the existing table. It is common to issue a DROP before doing a CREATE in test and development scenarios.

The syntax to drop a table is:

1
DROP TABLE TableName;

For example, consider that you wish to drop the contents of the table COUNTRY if a table exists in the dataset with the same name. In such a case, the code for the last example becomes

1
2
3
4
5
6
7
DROP TABLE COUNTRY;
CREATE TABLE COUNTRY (
   ID int NOT NULL,
   CCODE char(2),
   Name varchar(60)
   PRIMARY KEY (ID)
);

WARNING: Before dropping a table, ensure it doesn't contain important data that can't be recovered easily.

Note that if the table does not exist and you try to drop it, you will see an error like XXX.YYY is an undefined name. You can ignore this error if the subsequent CREATE statement is executed successfully.

In a hands-on lab later in this module, you will practice creating tables and other SQL statements.

Author(s)

Rav Ahuja

Additional Contributor

Abhishek Gagneja

Changelog

Date	Version	Changed by	Change Description
2023-10-10	2.6	Mercedes Schneider	QA Pass w/Edits
2023-10-07	2.5	Misty Taylor	ID Check
2023-09-09	2.4	Abhishek Gagneja	Updated instructions
2023-05-10	2.3	Eric Hao & Vladislav Boyko	Updated Page Frames
2023-05-10	2.2	Eric Hao & Vladislav Boyko	Updated Page Frames
2023-05-10	2.1	Eric Hao & Vladislav Boyko	Updated Page Frames
2020-08-31	2.0	Malika Singla	Markdown file creation