I am a microbiologist and molecular biologist turned tenured biotech faculty turned bioinformatics scientist turned entrepreneur. My passion is developing instructional materials for 21st century biology (
DigitalBio RSS feed
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).
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.****
Comments
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).
Posted by: Greg Laden | February 16, 2008 8:45 PM
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 =) somevalue.
And it can get more complicated than that. You can select columns from multiple tables, and so on. It's very powerful.
Posted by: Karl | February 17, 2008 7:14 PM
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.
Posted by: Sandra Porter | February 17, 2008 8:41 PM