Basic SQL Commands - Operations in Single Table

Basic SQL Commands - Operations in Single Table

Select, Insert, Update and Delete operations

In this post I aim to show some of the essential operations a data analyst does with a single table in SQL. The example database software used in this post is MariaDB, which is a fork of MySQL, and data shown is from its example Sakila database.

SELECT

We select data with the select command in SQL. Its basic usage can be summed up as:

SELECT [column_names or * to show all] FROM [table_name] WHERE [criteria];

So for example when I want to list all the actors contained in the Sakila I can write this:

SELECT * FROM actor;

This will show a result like this, if you are using MySQL Workbench like me:

The proper English of this query can be summed up like this:

Select ALL(*) from actor table.

The beauty of SQL can actually be seen from this simple example. It is really near to the spoken language level.

Limiting Results

So what happens if I want to show only the five records from this table?

SELECT * FROM actor LIMIT 5;

This query will give me the top 5 in the actor table

How about the bottom five? To do that I need to introduce another concept, ORDER BY into the mix:

SELECT * FROM actor ORDER BY actor_id desc LIMIT 5;

Result:

As you can see we can chain the statements one after another. But SQL is picky about the placement of the LIMIT statement. It has to be placed at the end!

It can be translated to human language as:

Select all from the actor table, order them by actor_id field in a descending manner (highest to lowest) and take five records from the top.

Ordering Results

I can use order by to order my result alphabetically as well:

SELECT * FROM actor ORDER BY first_name;

This will sort my data by the first_name column:

I can introduce another column, in this case it’ll be last_name, to make another ordering based on it:

SELECT * FROM actor ORDER BY first_name, last_name;

This statement will order the data first by first_name column and then it’ll order them again based on the last name. So I’ll see actors whose name starts with A first, and the first actor I’ll see is a person who’ll have his last name starting with A as well.

Selecting with Criteria

In addition to ordering and limiting by size, I can filter my results based on the data criteria I set. For example if I want to report all the actors with the name Michael I should write a query like this:

SELECT * FROM actor WHERE first_name = 'Michael';

This will return all the actors with the first name Michael:

Say I am not sure about the spelling of Michael, say if there were actors like Mikael or Michel. How can go and select them? Easy. With like and ilike keywords!

SELECT * FROM actor WHERE first_name LIKE 'Mi%';

The LIKE 'Mi%' usage basically says

I want any record that starts with Mi and I don’t care about how it goes afterwards.

Be aware that it’ll return Milla and Michelle as well;

Selecting some fields

So far we have selected all the fields in a table. We also can select some columns in a table by explicitly stating their names:

SELECT actor_id, first_name FROM actor WHERE last_name = 'Nicholson';

This will select only the id column and first name column in the actor table. I also chained a WHERE to limit the selection:

INSERT

In addition to selecting, which is an operation we’ll never get tired from in any business setting, we can insert new data to a table.

The basic syntax for INSERT can be listed as thus:

INSERT INTO table_name (column_name1,column_name2) VALUES (value1,value2);

Important thing to note is the column size Must match the value size. Let me introduce four actors to my table:

INSERT INTO actor (first_name, last_name) VALUES ('Ozgur','Cakmak');
INSERT INTO actor (first_name, last_name) VALUES ('Ozgur','Sensoy');
INSERT INTO actor (first_name, last_name) VALUES ('Ozgur','Sensoy');
INSERT INTO actor (first_name, last_name) VALUES ('Ozgur','Tahir');

Oops, I made an error with the third insert. How can I update this? Can I update this? Of course we can!

UPDATE

The basic syntax for update can be summed up as:

UPDATE table_name set (column_name1 = value, column_name2=value) WHERE condition;

It is VERY important to write the condition part here, because if you omit this the values will be written to ALL the data the table contains which is something you RARELY want to do.

A good practice is before updating a data select it with the criteria you’ll use in your update query:

SELECT * FROM actor WHERE actor_id = 202;
UPDATE actor SET first_name = 'Ferhan' where actor_id = 202;
SELECT * FROM actor WHERE actor_id = 202;

We can also use first_name and/or last_name as criteria here.

DELETE

Usually we don’t want to delete data from our tables, but sometimes due to erroneous processes or actions there might be redundant data which are not wanted. To delete these we use the DELETE statement. Its basic syntax is:

DELETE FROM table_name WHERE criteria

It should be noted that, like UPDATE if you don’t pass a critera this will delete ALL data in the table! So be VERY careful if you write something like this:

DELETE FROM table_name

I repeat, this will delete ALL the data in the specified table name!

As an example, let me delete the last actor I inserted here:

DELETE FROM actor WHERE actor_id = 204;

With delete you might encounter errors if this table is connected to other tables with foreign keys and the key relation is set to NO ACTION.

The introduction of data operations with multiple tables will be the subject of our next post by the way.