how to create and drop a table in SQL
If you are working on data analytics , and have access to databases that you can load data into and manipulate, then read on.
There maybe a reason for you have your own partitioned table with data in it, but you need to know how to create a database.
The creation of a table can be achieved as follows;
- Decide on the table name.
- Within the table you will have columns, they need to be named.
- Next for each column name a datatype needs to be decided upon.
It is important to note that the data type you decide is very important before you create the table.
This is because once you start loading data into the table if you decide to change the data type for a column, it could change the data contained within that column.
As a result a review of the data to be loaded should be completed, looking at the type of data, its length and any attributes of the data that need careful consideration.
Lets create our table and talk through the different aspects of the SQL used.
So in the below we have the create table statement
The SQL is broken down as follows:
- The first line creates the table within the schema. It has its own unique area in there.
- Lines 2,3,4 create the columns with their datatypes.
When this is run , the schema is updated, one thing to note it is empty, and requires an insert statement to populate the columns.
And if we do a select all from the table, it returns empty:
How to drop a table that you have created?
Sometimes you may have a need to drop tables from your schema.
Before proceeding there are a couple of things to consider:
- Have you backed up the data contained within it?
- You understand the deletion is permanent ( unless you can rebuild from backup)
- Any batch runs that run off the table will fail once it is deleted.
- Any tables that have queries that join to the table been removed will fail.
- This might be part of a wider database cleanup and may improve performance.
Below is the one line of code ( yes you are reading that correct), that will delete the table.
In most roles the ability to drop tables will be restricted by the database administrator.
After the deletion it will look like this. As you can see the table no longer exists.
In conclusion the deletion of a database table is very easy to do , but comes with caveats as outlined above, so proceed with caution.