How do I love thee SQL? Let me COUNT the ways. Number 1...

I love the way you show me secret things.
All I do is type: Select * from name_of_a_table
And you share everything with me.
Without you, my vision is obscured, and all I see is the display on the page.

In fact, this was the push that finally made me decide to learn SQL.

In our bacterial metagenomics experiment, I realized that my students could use FinchTV to enter their blast results into our iFinch database.

That was cool, but with the web interface, we could only view one result at a time.

On the other hand, if we use the right SQL query in the iFinch query window, we can see everything! My eyes were opened.

What is a relational database?
SQL depends on something called a "relational database." In a relational database, data are organized in tables and the tables have some kind of relationship to one another. This whole relationship between different tables is described by something called a "schema."

The image below shows a very simple schema with two tables that are related by a common set of identifiers (ID's).

i-140a7003f125ca593176ac69858ed147-tables.gif
inset class="center">

My web application might show me some of the information in the tables or it might not show me anything.

If I can query the database with SQL, I can ask it to show me everything in a table by saying: SELECT * from name_of_table.

I don't speak programmer, what does that mean in English?

The asterisk (*) is a wildcard. Just like in poker, where a wildcard is a wonderful thing and can stand in for any card, in SQL, the wildcard means "all" or "everything." If I were to say "SELECT *", I would be telling SQL to show me everything.

It would probably be bad to end my request there though, since there's lots of stuff in our database. So, I need to tell SQL where to look.

I do that by using the word "from" and the name of the table that I want to know about.

Thus, in English, the phrase "SELECT * from table1" says "Show me everything in table1"

And that hidden information is no longer hidden from me.

DISCLAIMER:
*** I am not a programmer and I'm trying to describe these mysteries to biology students. Since many of my readers know more about programming than me, I would appreciate it if you point out my mistakes in the comments.****

More like this

One of my chief joys in life, often to dismay of others, is working with data. Some members of my family (who shall remain nameless) would characterize this fixation as bordering on obsessive, others just call me a "geek" and leave it at that. I don't care. Give me a data set and I can play…
A long standing debate in my field is whether or not biologists, who work with computers, need to learn how to program. I usually say "no." Let the programmers program, the biologists interpret the results, and let everyone can benefit from each other's expertise. Well, I've changed my mind in…
This the third part of case study where we see what happens when high school students clone and sequence genomic plant DNA. In this last part, we use the results from an automated comparison program to determine if the students cloned any genes at all and, if so, which genes were cloned. (You can…
We have lots of DNA samples from bacteria that were isolated from dirt. Now it's time to our own metagenomics project and figure out what they are. Our class project is on a much smaller scale than the honeybee metagenomics project that I wrote about yesterday, but we're using many of the same…

I think you are explaining it just fine. The best way is to experiment!

It is helpful to point out that a "database" is made up of one or more "tables" and each tables has "records" made up of "fields"

Usually one field is an "index" (often called "ID" or something) so that for each record there is a unique value. This index field (and there can be more than one index field) is useful to link the tables in a "relation" (as you discuss).

The greatest use of SQL is to see specified subsets (of both rows and columns) of your whole table (or tables). You do this by saying "Select columnname1, columnname2, etc,
from tablesuchandsuch
WHERE
columnnamesoandso operator(< or > or =) somevalue.
And it can get more complicated than that. You can select columns from multiple tables, and so on. It's very powerful.

Thanks Greg!

I will add something about records and fields at a later point.

Karl,

I agree. That post is on the way. We're going to take small steps.