SQL5: Inserting and Updating Data

Inserting Rows, Inserting Columns, Updating Values, Special Characters, and Exercises/Interview Questions

Not going to continue showcasing all the jobs that need SQL as a skillset. I made my point in the past 4 posts. But, I will show the result of the SQL poll:

20% said they had no idea SQL was important.

This explains why so many people believe they have the skills for MLE/DS roles, even though they do not.

Let’s get into the meat grinder. This is one of the harder ones, but nothing crazy. I’ll be using the titanic dataset for this section.

Table of Contents:

1. Inserting Rows

2. Inserting Columns

3. Updating Values

4. Special Characters

5. Exercises & Interview Questions

*Important*

So far, we’ve only covered SQL statements that manipulate the data, and give us an output for our query. Here, we’ll cover the INSERT INTO statement which will be used to make a permanent change to your SQL tables.

When I say this makes permanent changes to your SQL tables, I mean it.
Take a look at this:

Give writing access to prod to a guy at your own risk.

Here is the link to the reddit thread: Rekt

1 - Inserting Rows

When we want to insert some rows to our table, we will use the INSERT INTO clause. It will tell SQL that I’ll be inserting some rows into a table. I’ll then need to tell it for which columns I’ll be adding in the values for.

This is then followed up by VALUES (…..) to tell SQL what values I’ll be using.

Example: To make a new row which has the Name = ‘Hot Person’, and Sex = ‘Yes Please’ in my Titanic Database, I’ll do this:

To see the changes, you’ll use the SELECT * statement to see everything.

Notice that the columns which I did not select are filled with NULL. If I have a large table, and I want SQL to know that I want to add in a row for every column. I would say INSERT INTO TITANIC, without the brackets or column names.

If you try to insert the wrong data type into your column…. It will actually add it in there, like it’s no big deal. In the real world, when someone makes a column, they’ll add in some checks to prevent that from happening. But, in our case, since there’s no checks, it gets added in.

See how I can add in Numeric values to a column meant for TEXT

Note: you can keep repeating the Values (….), over and over again to add in multiple rows in 1 SQL query.

2 - Inserting Columns

We can insert new columns into our SQL table, by using the ALTER TABLE, followed by the ADD command.

The ADD command requires 2 things:

• Name of our new column

• Type of data this column accepts

• Optional: The default value for all entries in this column

Example:
I’ll make a new column called not survive, where every entry is NULL, and everything is an INT.

ALTER TABLE, and ADD being used to add in a column

Note: the ALTER statement is an example of a DDL (click here if you forgot your definitions). This is because it is used to alter the structure of a database by adding in an extra column. This means the database now needs to keep track of this as well.

3 - Updating Values

Let’s say we already have a SQL table, but we want to alter some values to something else. To do this, we’ll use the UPDATE command, alongside the SET command. It’s very simple to the method above.

Example:
Let’s say I wanted to change the above Dead column to say it’s 1-Survived. Here’s how the query would look:

UPDATE, and SET to change all the values in a column.

You can combine the above UPDATE, and SET with WHERE from our earlier SQL post to only update certain values.