CREATE TABLE

So far in the SQL programming series, we have been using a sample database that came pre-installed with DBeaver software. Now we are going to learn some essential techniques to create our own tables and populate them with data. We begin with the fundamental CREATE TABLE statement.

The CREATE TABLE statement is used to create a new table within a database. We can create a table from scratch by defining its structure, or use a query from another table as the foundation for creating a new table with pre-populated data.

CREATE TABLE SYNTAX

To create a table with the CREATE TABLE statement, we need to define the column names and their corresponding data types. Below is an example of the CREATE TABLE statement for a table designed to store stock market data.

CREATE TABLE test_stock
(
    Ticker VARCHAR(10) NOT NULL,
    Open DOUBLE NOT NULL,
    High DOUBLE NOT NULL,
    Low DOUBLE NOT NULL,
    Close DOUBLE NOT NULL,
    Volume INT
);

In the example above, we have created a table named test_stock with columns named Ticker, Open, High, Low, Close, and Volume. Notice that we have specified their data types such as DOUBLE, INT, and VARCHAR. Data types are essential for creating a table and will always be enforced when inserting data, so it's crucial to choose the appropriate data types for the expected data in each column. Another important specification is whether the column can accept NULL values. We use NOT NULL to ensure that the column always contains a value, preventing empty entries.

We can query our new table to verify that these columns have been created and confirm that no data has been inserted into the table yet.

CREATE TABLE test_stock
(
    Ticker VARCHAR(10) NOT NULL,
    Open DOUBLE NOT NULL,
    High DOUBLE NOT NULL,
    Low DOUBLE NOT NULL,
    Close DOUBLE NOT NULL,
    Volume INT
);
SELECT *
FROM test_stock

CREATE TABLE FROM QUERY

Another powerful alternative is to create tables using queries that return data from existing tables. This approach is much more straightforward as it relieves you from having to manually define the correct data types—the new table automatically inherits the data types from the source table. The syntax to create a table from a query looks like this:

CREATE TABLE test_stock_data AS
SELECT *
FROM stock_data

As we can see from the execution, the table has been successfully created. The new table now contains all the rows returned by the query. We can verify that it contains all the expected data by querying the new table using the following statement:

select *
from test_stock_data

As demonstrated in the output, all the records that were retrieved in the original query used to create the table are now permanently stored in our new table. This method is particularly useful for creating backup tables, filtered datasets, or transformed versions of existing data.

This concludes our comprehensive discussion on the CREATE TABLE statement. In the next section, we will explore IF NOT EXISTS as a best practice for creating tables and databases safely without encountering errors.