Originally published by Robert Beisert at fortcollinsprogram.robert-beisert.com

What is SQL? A Basic Introduction

In today’s society, it’s virtually impossible to find anyone who hasn’t worked with Microsoft Excel at some point. We are somewhat familiar with the idea of spreadsheets and tables. We’ve written a simple macro or two. And, of course, there is great value to Excel and its competitors.

But Excel is weak. It’s virtually impossible to work with Excel without using your mouse to navigate a set of menus. Only a few “power users” know how to form the complex macros required to do more than basic work with spreadsheets. It’s really just a tool designed to simplify organizing financial data and performing financial calculations (which is the original purpose of the program).

What happens if you want to, say, keep a library record? Maybe you want to keep track of hundreds of library card holders and what books they’ve checked out/in. Maybe you need to know which books are on the shelves. Maybe you want to find all the books by a particular author, or figure out where a book is located on the shelves. Does THAT sound like a job for Excel?

For data this large and requests this complicated, we need more than a spreadsheet – we need a database. We could use Microsoft Access for our database, but we don’t solve the problem of too-much-GUI that plagues Excel. That’s why, for this kind of problem, most of us turn to SQL.

SQL

SQL (Standard Query Language) is a scripting language designed to create and manage databases on a variety of platforms. It allows us to create tables, put constraints on the contents of those tables, fill the tables, read from the tables, link the tables, and delete the tables. Better still, with a basic knowledge of a few commands you can create extremely complex queries that can produce extremely specific and useful information.

Before we go further, there are a few terms we should address:

A database is a collection of tables and the rules governing those tables (schema). Databases are usually large files containing all your data in an organized structure.

A schema is a design. This covers the design of individual tables – what fields you store and any rules regarding those fields – and the database – how tables interact. The primary job of a SQL programmer is to design and manipulate schema to provide the information we want when we want it.

A query is a request to the database. There are four basic kinds of query – Create, Read, Update, and Delete (CRUD). We use queries to create tables and data, retrieve data based on rules we specify, update the data in tables, and delete data and/or tables.

Is it REALLY that useful?

In a word: yes. In two words: ****, yes.

Without the kind of databases SQL creates, how could you put together something like Amazon?
Without the kind of databases SQL creates, how could you put together something like Amazon?

We’ve already mentioned how we can use SQL to manage a library, but it’s a plain fact that the internet runs on SQL. Almost every website out there requires SQL to operate. A store uses SQL to store every item they sell:

  • Picture
  • Name
  • Tag(s)
  • Categories
  • Descriptions
  • Reviews
  • Star Rating
  • Title
  • User
  • Text
  • Price
  • Etc.

The same store can use complicated SQL commands to turn your search and produce hundreds of products that are closest to what you probably want. They can also use SQL to produce a table called “your cart” which contains a number of items you’ve decided you want to purchase, the number of those items, and the total amount you can expect to pay. Basically, all of e-commerce would be impossible without SQL.

But it’s not just stores that rely on SQL. Most websites use SQL as a back-end to provide you with the web pages or posts you want to see. Heck, this website runs on the WordPress platform, which uses SQL to store all my pages, posts, media, categories, etc. If I REALLY wanted to, I could make changes to my website exclusively with SQL (and PHP, but that’s another story for another day).

And, on a darker note, sites like Facebook can use SQL to figure out your purchasing habits, preferences, and political affiliations from your posting habits and internet history. They can then sell this valuable information to advertisers, who use it to make more enticing (and usually irritating) advertisements that follow you from site to site.

If you want a simple skill that can make you viable in the technical marketplace, SQL is…about as close as you can get.

Note: Ignore anyone who talks about how everyone’s moving away from SQL. The internet giants like Google and Facebook are working on it, but no solid alternatives have come out yet.

Platforms

The two most common SQL database programs are Oracle’s MySQL and SQLite. There are pros and cons to both of these tools.

MySQL

Con: Requires a web-serving platform like Apache to store the databases

Pro: So popular that it’s part of the web-server “LAMP” stack (Linux, Apache, MySQL, PHP/Perl/Python)

Con: Usually relies on GUI interface and built-in IDE to operate

Pro: Comes with GUI tools that can produce Entity-Relationship model diagrams and transform them into databases (and vice versa), print your tables and all data, and show your schema(s)

Con: Requires a saved database before you can run any queries (no transient database)

Pro: Stores your old queries so you can always run them again.

SQLite

Pro: Doesn’t rely on server platform to work

Con: More difficult to work with for online purposes

Pro: Command-line tool only (no GUI)

Con: No GUI tools

Pro: Does not need to save a database at any point (so you can test a bunch of INSERT commands before you apply it to the real database)

Con: Does not store your older queries for future reference

Basically, it’s dealer’s choice which one you work with. Personally, I’ve installed both on my machines, but I find SQLite easier to teach with.

photo by: