SQL; Even Better Than the First One!

Hello code heads. Recently, as I’ve been job seeking with every spare hour at my disposal, I’ve noticed that more and more of the companies I’m looking into are expecting an intimate knowledge of the database query language known as SQL. As such I’ve taken a deep dive into the world of that very same language, and have the honor of presenting before you today the results of that investigation.

For those of you who are historically inclined I’ll start with a little backstory. SQL was developed in the early 1970s by a little company known as IBM. SQL stands for structured query language, and it was built to “query” information from databases. Now that we’ve got that exhaustive dive out of the way, let’s talk about syntax and usage.

SQL is always used to query information from databases. That is its whole purpose. Databases consist of rows and columns. Each column in a database describes an attribute of the instances therein. Let’s say we had a database that held different faces. Each column would be a different feature of that face. For example: an eye color, hair color, or nose shape. Rows of a database represent the individual instances of whatever is in the database. So a row from our previous example might look something like “blue, red, terrifyingly oblong.”

Now that we know what a database looks like, let’s talk about the actual structure and syntax of SQL. Most SQL commands are structured as queries. We are trying to get information out of the database. The first SQL command to discuss is SELECT. All SQL commands are written in capitol letters. SELECT accepts arguments for attributes to pull from a table. If we wanted to find out the eye colors from our previous table we could write

SELECT eye_color FROM face_table

This would give us all the eye colors from the table. Note that anything that is not a SQL command is not capitalized. The FROM specifies which table we are talking about. Another option is to, instead of specifying one or more columns, use the * symbol. This asterisk is a universal signifier, meaning that it will request all columns from the table in question. If you want to pull more than one column out from the table, you can use commas to separate the values, like so

SELECT eye_color, face_color FROM face_table;

With the knowledge we have right now we are pulling every single row from the table. Most of the time we want to pull just a few or even one value. To do that we use the WHERE command to specify a certain row based on a given criteria. For example if we wanted to find just the eye_color of people who had red hair we could execute a command like this

SELECT eye_color FROM face_table WHERE hair_color = 'Red'

I’m not using any line breaks at the moment but it’s important to note that SQL does not read white space. The only thing you must absolutely be sure of is to put a semicolon after all SQL queries, otherwise the script will crash.

So now we know how to get data from database. Let’s talk about how to add or manipulate data. The SQL command in the spotlight here is INSERT INTO. INSERT INTO will allow you to add rows to a database. The syntax for this is as follows.

INSERT INTO face_table (eye_color, hair_color, nose_shape)
VALUES ('Green', 'Brown', 'Eerily pointy'); 

There are two sets of arguments to be passed here. The first is which values you are going to be adding, which is to say the columns. Secondly, the arguments that are passed to the VALUES command are what is actually going to be added to the row in question. Make sure that these values are sequenced with respect to the columns in question.

We know how to pull data, we know how to insert data, let’s talk about getting a little frisky with out data. SQL has many commands which can be used to run calculations on the data in the table. These are called aggregate functions. Let’s say we had a column for the amount of ears a person had on our face_table table. Furthermore, let’s say we wanted to calculate how many ears there were in total for individuals with blue eyes. The command for that would look something like this:

SUM(ear_amount) from face_table WHERE eye_color = 'Blue'
//Return value -- probably an even number.

Pretty thrilling stuff. A couple other aggregate functions that can used are COUNT(), AVG(), and MIN/MAX which are used to find the highest or lowest value of a certain column.

And those are a few of the basic commands for SQL. Armed with these tools you should be able to delve deep into any database you come across. Join us next week for a deeper look into the interrelational side of SQL, which is to say, what happens when you want to pull data from more than one database.

Stay sane codeheads!