Getting Started with SQL

A blob of text, spreadsheet, database. That is the Pokemon evolution of data. Maybe you start with a to-do list. You realize you want to keep track of a little more info in your to-do list, maybe you want to add a date and some comments, so you move it to a spreadsheet. 

You realize this is the best to-do list idea ever and you want to bring it to your team. Perhaps you want to track to-do lists inside of to-do lists and query past to-do list performance to optimize everybody’s time. 

You’re insane about productivity, good on you.You move it to a database and scale it for the world. Now everybody in the world is uber productive and you’re rich, congrats!

OK, maybe you’re not going to end all the worlds issues, but a SQL database can get you organized enough to solve some big hairy problems. It sounds like a daunting task, but SQL is surprisingly beginner friendly. 

For one thing, if you’re used to working in advanced spreadsheets its pretty easy jump to using SQL functions. In fact you’ll free yourself up from the agony of writing an advanced excel query and diving into the inception-like layers in that teeny tiny cell. 

If you’ve never worked with advanced spreadsheet functions, well you’re still going to find SQL isn’t so scary because its a lot like spoken language. You might want to say “SQL database, show me the high priority tasks in my to-do list.” 

In SQL that roughly translates to:

SELECT * FROM to-do-list WHERE priority = ‘high’;

This is a basic SQL query:

• Select means get, or show me.
•  The asterisk “*” means everything. 
• FROM indicates the place from which you want to get data. 
• WHERE tells SQL you want to do a comparison of some sort. 
• The semicolon “;” indicates, “OK I’m done talking to you database.”

See? Not so bad. So WHERE (hehe) and how might you run such a query? Well, first you’re going to need to build a database. This is also not as difficult as you may think. 

Setting the Table

It is surprisingly easy (and free) for you to get started with SQL

• You can quickly build and experiment with a database on http://sqlfiddle.com/. 
• You can install SQLite on any operating system. 
• You can use the SQL Workshop in Oracle’s Apex platform 

If you’re brand new you should start with SQLfiddle. Its the most approachable. Regardless of which route you take to learn, the first thing you are going to do is create a table. Lets make a basic table in SQLfiddle.

As you can see on the left side, I’ve created a table. Lets talk about the syntax. In SQL, key words are written in all caps. I like to pretend we're all screaming when working with a database. CREATE TABLE means, well we’re going to make a new table. Its followed by the name, in this case “pokemon.” 

This statement is followed by an open parenthesis “(“ which means there’s some stuff to follow. In this case we’re going to make the “columns” of the database. Lets zoom in on the “pokemon_id” line and take a look at how we define columns.

pokemon_id INT NOT NULL AUTO_INCREMENT,

“pokemon_id” is the name of the column. “INT” is the “integer: data type (there are many data types in SQL.) “NOT NULL” indicates that, when we’re putting data into this column, that we cannot leave a blank. “AUTO_INCREMENT” is handy because it will automatically increment numbers for you. And we finally close off the column with a comma. 

We add a few more columns and then we yell the “PRIMARY KEY” statement and give it “pokemon_id”. Almost every table in SQL will have a primary key. It is not necessary to know to complete this tutorial, but its a concept you should research and understand. 

Finally, we close the entire statement with closed parenthesis “)” and a semi-colon. You can them click “Build Schema” and SQLFiddle will create an empty table. 

Adding Data to the Table

Let’s insert our first row of data! Do you remember the columns we defined when we created the table? They are pokemon_id, pokemon_name, pokemon_type, and entry_date. 

Here we add an INSERT INTO statement:

Notice that we indicate the columns in parenthesis, then add the VALUE statement, then we input our values in parenthesis. There are a few ways to craft an insert statement, this one is the most basic form. Click Build Schema. Now you are ready to query the data!

Gimme Back my Data!

If you’re still with me and using SQLfiddle, you can use the right side of the screen to run SQL statements. Craft the statement, click “Run SQL” and it will return the data at the bottom of the screen.

One of the most common statements you will use is SELECT. Try running the statements below, one after the other and see what you get!

SELECT * FROM pokemon;

SELECT pokemon_id, pokemon_type FROM pokemon;

SELECT pokemon_name FROM pokemon WHERE pokemon_name = “pikachu”;

FIN

There you have it! See it wasn’t as complicated as it seems to get started. Next steps would be to figure out how to add some more rows or maybe delete a row . You could check out the Socratica SQL playlist on youtube. Or you could start working with an Access or SQLite database and figure out some uses at work.