SQL6: Create, Delete, and Drop

Creating a Table, Deleting some rows, Dropping a Table, and some exercises/interview questions

This post will be focused on creating tables, deleting rows, and dropping tables. We’ll also talk about some real life good coding habits you’ll need to start developing.

I’ll be using the titanic dataset for this section. Let’s get it.

Table of Contents

1. Creating a Table

2. Deleting Rows

3. Dropping A Table

4. Exercises/Interview Questions

1 - Create a Table

1.1 Empty Table

As we start to get more and more data, sometimes it makes sense to create a seperate table for these new entries. To create a table in our SQL database, we use the CREATE TABLE command.
The generic template for this command is:

CREATE TABLE *my_table*
(my_column1 DATA_TYPE,
my_column2 DATA_TYPE, …);

If you forgot all your data types, the minor nuances for T-SQL, MySQL, and PostGreSQL, click here.

Example: Let’s say I want to make a table called Britannic. Britannic is one of Titanic’s sisters. I want the table to have a similar structure to the Titanic table. To achieve this, I can use the CREATE command to get an empty table.

You can see the Britannic Table is added to our list of tables, alongside the data types.

Once we have an empty table, we can use the INSERT statements previously discussed to start filling it up.

1.2 Use Another Table

If I wanted to make a new table, and I wanted it to get all the same entries as my Titanic table. One way to do this is to build a new blank table. Then use the INSERT command over and over again. Unfortunately, that would be a huge waste of time.

Instead, I can combine the INSERT TABLE with the SELECT command. This will tell SQL to make all the values in my new table to be copied off something else. Here is the generic SQL template for this:

CREATE TABLE *my_table* AS
SELECT *
FROM my_old_table;

Example:

I’m going to discard my old Britannic table first (DROP). Then use SQL to make a clone of the titanic table called Britannic. I have to discard the old table, because SQL does not let you have 2 tables with the same names.

Copy all values from the Titanic table.

You can further extend this further by using the WHERE/HAVING clause. You can use them to subset which entries are allowed to be ported over, and which ones are not.

1.4 - Create Table Only If It Doesn’t Exist

If you tried to create a table that already exists then SQL will give you an error.

SQL Error: Brittanic table already exists

The error says the table you are trying to create already exists. For MLE/DS manually checking if a table exists, and altering the SQL tables manually is a giant pain. For the purposes of automation, all our CREATE TABLE have a IF NOT EXISTS addon. This addon will only create the brand new table if it doesn’t already exist.
Here is how it fits in:

CREATE TABLE IF NOT EXISTS my_table AS
SELECT *
FROM my_old_table

2 - Deleting Rows

2.1 Yeet Everything

Note: Please don’t do this in real life, unless you have a really good reason.

To delete rows from your SQL table, you will use the DELETE command.
To yeet all the rows in your table, just type in:

DELETE
FROM my table;

Yeet all the rows.

Another way to yeet all the rows from your table is to use the TRUNCATE command.
It would look like this:

TRUNCATE my_table;

Although both DELETE, and TRUNCATE can be used to wipe out all the rows in the table. In terms of performance, TRUNCATE ends up being faster than delete.

2.2 Delete specific rows

When we want to delete rows from a table, we want to only delete specific rows, not everything. To achieve this, we use the WHERE clause with our DELETE command. If I kick out all the entries that are LIKE ‘%fe%‘, this will kick out all the female entries, and preserve only male.

Using the WHERE clause to only remove those which have a ‘fe’.

2.3 Good Habits

Given that the DELETE command makes permanent changes to our table, we will want to be careful with it. A good practice is to always use SELECT *, first. This will let us see what would be getting removed. Then once you are happy with it, go ahead with the DELETE command.

3 - Dropping A Table

3.1 - Drop a table

Sometimes, we are not interested in just removing the rows in a table. Sometimes, we want to make the table itself disappear like Thanos.

Thanos did nothing wrong.

To make a SQL table disappear, we use the DROP TABLE command.
Here is the template for it:

DROP TABLE my_table;

Now, let’s use the DROP TABLE on the Britannic to make it disappear, then use Select * to see the error message.

SQL Error: Britannic table not found.

3.2 - Drop if it exists

From above, you saw that we can only execute the DROP TABLE command, if and only if our table exists. This means if we were running a batch SQL job, and if we got an error there, everything after it would not get executed.

To remedy this problem, we can use the IF EXISTS to check if the table does exist first, before dropping it.
So, the code form would be:

DROP TABLE IF EXISTS my_table;

The great thing about IF EXISTS is there’s only 2 scenarios, and both give the outcome we want when running a batch job:

1. Table exists - It gets dropped

2. Table doesn’t exist - No error reported, onto the next SQL script

4 - Exercises/Interview Questions

4.1 Exercises

Here’s some exercises you can do to assess your knowledge.

1. Make a new table called the Olympic. This table should copy all of the rows from the titanic dataset, WHERE all of the entries are female.

2. Make a new table called the Brittanic. This table should be an empty table, but with all of the columns in the same structure as the Olympic.

   1. Now insert this row in there: (‘BowTied_Raptor’, ‘1st’, 69, ‘Yes’, 1)

3. Remove all of the rows in the Titanic, Brittanic, and Olympic in 1 Batch job.

4. Drop the Titanic, Brittanic, and Olympic Tables in 1 Batch job.

4.2 Interview Questions

Here are some interview questions, with the answers based off this specific topic.

1. Can you tell me the steps you’d go about removing some rows?
   Ans: They are testing you on whether you just DELETE FROM, or whether you run a SELECT statement first to see what you are going to delete. Here’s the steps:

   1. SELECT * FROM my table WHERE <condition> to see what gets removed

   2. Now execute the DELETE command

2. Can you tell me how you’d go about removing all of the rows for a SQL table, in a production database?
   Ans: They are testing to see whether you understand that performance matters a lot in a production database. Since we know TRUNCATE is faster than DELETE when it comes to removing all rows, the answer is to use the TRUNCATE command on my table.

3. Can you tell me how to make a new SQL table called my_table. This table has 1 column. This table is meant to work with JSON data.

   Ans: They want to see if you know that only MySQL, and PostGres support JSON, while T-SQL does not. They also want to know that you know how to make a blank table in SQL. Here’s the answer for all 3.
   
   T-SQL:

   CREATE TABLE my_table
   (my_column1 text);
   
   MySQL, PostGreSQL:

   CREATE TABLE my_table
   (my_column1 JSON);

Click here to continue to SQL 7 - Keys, Aliases, & joins. Don’t have to do this one if you understand all 4 joins in SQL already.