Updated March 6, 2023
Introduction to DB2 Create Table
DB2 create table statement is used to create the logical entities that are present inside the database called as tables. Tables are used to hold and store the data in the format of rows and columns similar to spreadsheet format. The table name that we create should necessarily be unique inside a particular schema or database we create. The rows of the table do not have any specified order when they store the data in them. However, the columns of the table have predefined order and also have a data type assigned to it. The data type of the column tells about what kind of value will be stored in that column. A single column can store the data of only a particular type in it.
In this article, we will study how we can make the use of DB2 CREATE TABLE statement to create the tables in the schema and also discuss its implementation with the help of examples.
Syntax:
The syntax of the CREATE TABLE statement is shown below –
CREATE TABLE [name of the schema.]name of the table (
name of the column 1 data_type constraints of the column NOT NULL,
name of the column 2 data_type constraints of the column DEFAULT value,
name of the column 3 data_type constraints of the column CHECK(expression),
...,
);
In the above syntax of the CREATE TABLE, the name of the table is the table name that we have to assign to the new table that we are about to create. We can optionally specify the name of the schema in which we wish to create the table. Further, we need to specify the list of the columns, their names, the type of data they are going to hold, and also any other constraint such as NOT NULL, DEFAULT to give the default value or CHECK for security reasons. Note that this list of columns should be separated by a comma between each of the column specifications in the syntax. At last, we can specify any constraints that the table needs to have such as FOREIGN KEY constraint or PRIMARY KEY constraint and CHECK constraints. The type of data that the column can hold is called datatype and generally be a string, numeric or temporal value specification.
Example
Let us create the table called sales_customers in the same schema that we are currently working in with the help of CREATE TABLE statement and the following DB2 query statement –
CREATE TABLE sales_customers
( customer_id NUMBER(6)
, f_name VARCHAR2(20)
, l_name VARCHAR2(25)
, email_id VARCHAR2(40)
, mobile_number VARCHAR2(20)
, purchase_date DATE
, store_id VARCHAR2(20)
, bill_amount NUMBER(8,2)
, salesman_id NUMBER(6)
, department_id NUMBER(4)
, PRIMARY KEY (customer_id)
) ;
We have specified the name of the table as sales_customers after which we have specified the list of the column names and their corresponding datatypes and the length of the column that we want for each of the columns. The order in which we specify the names of the columns is very important as while inserting or retrieving the data stored inside the table, the default ordered followed is the same as specified while creating any table. At last, we have specified the PRIMARY KEY constraint on the customer_id column that we need to apply to the sales_customers table. The execution of the above query statement gives the following output as the results and creates the table sales_customers in the database that we are using currently.
In order to retrieve the data that is present inside the sales_customer table, we can make use of the following query statement. After creating the table the query will retrieve a blank result set.
SELECT * FROM sales_customers;
Let us try to create one more table named paint_shades which will contain the data of all the paint buckets available in stock for a particular paint shop. We have the main constraint on the column named shade_id which will act as the primary key. We have also applied the NOT NULL constraint on the shade_id column and have kept it on AUTO_INCREMENT as the value for that column will keep on generating uniquely automatically for that particular column. We can store the details of cost price and sales price in the columns with a datatype of float.
CREATE TABLE paint_shades(
shade_id INT NOT NULL AUTO_INCREMENT,
color_name VARCHAR(50) NOT NULL,
quantity INT NOT NULL,
cost_pricing FLOAT(10, 2) NULL,
number_of_buckets INT NULL,
light/dark VARCHAR(50) NULL,
interior_or_exterior BOOLEAN NULL,
Sales_pricing FLOAT(10, 2) NULL,
PRIMARY KEY (shade_id )
)
The execution of the above query statement gives the following output as the results and creates the table paint_shades in the database that we are using currently
In order to retrieve the data that is present inside the paint_shades table, we can make use of the following query statement. After creating the table the query will retrieve a blank result set.
SELECT * FROM paint_shades;
Now, we will create a table named playlist which will contain the playlist_id as the primary key column which will be set on auto-increment so that every time a new record is inserted a unique number gets generated automatically for the column. Further, we will store the playlist name, song_id, and number_of_songs columns to store the additional details in the table. We can create the table by using the following query statement –
CREATE TABLE playlist(
playlist_id INT NOT NULL AUTO_INCREMENT,
playlist_name VARCHAR(50) NULL,
song_id INT NULL,
number_of_songs INT NULL,
PRIMARY KEY (playlist_id )
);
The execution of the above query statement gives the following output as the results and creates the table playlist in the database that we are using currently –
In order to retrieve the data that is present inside the playlist table, we can make use of the following query statement.
SELECT * FROM playlist;
Conclusion
We can make the use of the CREATE TABLE statement in DB2 to create a logical entity which can store the data in the form of rows and columns and which helps to give different constraint and order to the columns.
Recommended Articles
This is a guide to DB2 Create Table. Here we discuss the Introduction, syntax, examples with code implementation. You may also have a look at the following articles to learn more –
