Creating Tables

Following along with my current course ‘SQL for Data Science’. We are now learning about Creative Tables using SQL.

We can create tables with SQL. In this article we will learn the following:

• Discuss situations where it is beneficial to make a new table.
• Create a new table within an existing database.
• Write data to a new table.
• Defining whether columns can accept NULL values or not.

Why tables are useful

We can use tables to do the following:

• Use tables to make models and predictions.
• Create dashboards.
• Visualise data using other tools.
• Extract data from other sources.

Here is how we can create our own table.

CREATE TABLE Pokemon_cards
  (
  ID     char(10)     PRIMARY KEY,
  NAME   char(30)     NOT NULL,
  TYPE   char(30)     NOT NULL,
  PRICE  decimal(8,2) NOT NULL,
  DESC   char(250)    NULL,
  );

There is actually a lot going on in the above code block. We have defined the column names, column data types and if they can be NULL or NOT NULL. There is also a PRIMARY KEY which is linked to the ID column. This gives us the basic structure of creating a table.

An important note is that PRIMARY KEYs are unable to be NULL.

If you do try to omit data into a column you have defined as NOT NULL you will get an error.

Adding Data to the Table

We can use the INSERT INTO statement to add data. Take a look at the following:

INSERT INTO Pokemon_cards
VALUES ('0001'
       ,'Bulbasaur'
       ,'Grass'
       ,'399.50'
       ,NULL
            );

You can see these values can be mapped to the column names and make some sense. You will have noticed that the last value is not wrapped in single quotation marks and simply reads NULL. Wrapping this in a string will input the word ‘NULL’ inside the ‘DESC’ column. This is why we omit the quotation marks and simply use NULL.

However, using this method there is some degree of error that can occur. We are just hoping that we have done a good job and that all the correct column values will map to the column names. We can be a lot more specific about this using the following:

INSERT INTO Pokemon_cards
       (ID    
       ,NAME  
       ,TYPE  
       ,PRICE 
       ,DESC  
       )
VALUES ('0001'
       ,'Bulbasaur'
       ,'Grass'
       ,'399.50'
       ,NULL
            );

This method is much more specific and decreases the degree of error.

I would also love to connect on Twitter, LinkedIn or on my Website. I am not sure what value I could provide you with but if you reach out, I would be more than happy to have a conversation or talk about what I have written here.