SQL (3) Creating a table

Let’s learn how to create a simple table.

■ CREATE TABLE

CREATE TABLE clause create a table by entering the table name, data type, and condition of the column .

CREATE TALBE Table-Name
 (
 First row-name data-type condition,
 Second row-name data-type condition
 );

Depending on the DBMS you use, it’s a little different, so I recommend checking with your DBMS again. For example, if you are using mySQL, please refer to the following link:

Data types

Conditions: NOT NULL, NULL, PRIMARY KEY, AND SO ON.

All columns must be NULL or NOT NULL. If you don’t give a value for the condition NOT NULL, you will see the error. Because DBMS expects to have a some value for the record.

The caveat here is that NULL is not an empty string. NULL should literally be in a state where nothing exists. PRIMARY KEY cannot be NULL. That is, you should always give some value.

The conditions also vary slightly depending on the DBMS. If you want to check the concepts of KEYS, please refer to the previous article. Let’s write an example.

-- Create "SHIRTS" table
CREATE TABLE SHIRTS
 (
 ProductID char(10) PRIMARY KEY,
 Name CHAR(10) NOT NULL,
 Size CHAR(10) NOT NULL,
 Color CHAR(250) NOT NULL,
 Price DECIMAL(5,1) NOT NULL, 
 Summary VARCHAR(750) NULL
 );

In the above example, DECIMAL (5,1) means that a total of five numbers can be entered, including one decimal point. That is, you can enter the numbers from -9999.9 to 9999.9.

■ INSERT INTO: Adding a Row

To add row data, you can use the INSERT INTO clause.

-- Add rows
INSERT INTO table-name
VALUES ('First Row Value',
        'Second row value'
       );

However, if you have a lot of columns, you may be confused about the order, so I recommend adding:

-- Add rows (recommended)
INSERT INTO table-name
       (First row-name,
        Second row-name
       )
VALUES ('First-row-value',
        'Second-row-value'
       );

If you use the SHIRTS table you created earlier, it looks like this:

-- Add row data (recommended)
INSERT INTO SHIRTS
       (Name,
        Size,
        Color,
        Price,
        Summary
       )
VALUES ('0001',
        'Beach',
        'M',
        'Blue',
        '40.5',
        NULL
       );

■ CREATE TEMPORARY TABLE: Create a Temporary Table

A temporary table is a table that will be deleted at the end of a current session. This is useful for writing faster, more complex query statements than creating regular tables. For example, as follows:

-- Create a temporary table
CREATE TEMPORARY TABLE Blues AS
 (
 SELECT *
 FROM SHIRTS
 WHERE Color = 'Blue'
 )