SQL queries for nontechnical users

From Visual Basic to GNU C, this is the place to talk programming.

Moderators: SecretSquirrel, just brew it!

SQL queries for nontechnical users

Postposted on Wed Feb 23, 2011 8:35 am

My father works as a molecular biologists. As part of his job he needs to be able to determine which genes match certain criteria. Fortunately large databases exist which hold the information he's looking for. Unfortunately, he has no idea how to search these databases. This means whenever he wants to look up which genes have parameters A,B, and C, he has to go to bioinformatics guy, describe what he's looking for and hope that the bioinformatics guy picks appropriate values for A,B and C. While this process works, it is quite slow. It also doesn't allow him to tweak his queries to easily expand or limit his search based on the results of his first query.
What he really needs is a way to build or at least tweak queries on his own. He already understands the database is sorted into columns. He also has an idea of what values might be appropriate for a column(the biology part). What he has no idea how to do, is tell a computer what values to look for in those columns. Actually he has very little idea how to use a computer for anything but checking e-mail and browsing the web. I need a way of teaching him to be able to do basic SQL queries. More importantly I need to teach him regular expressions. As I have very little experience in the subject I was planning to teach myself first so that I can help him learn. Googling around I have found any number of resources that should suffice to teach me regex. However I am not certain they will work for my father, reading a tutorial once or twice is not going to be very illuminating or lasting. I would like a way for him to be able to practice using the expressions as he learns them. Even better would be a way they he could use them in conjunction SQL commands so that they are in the appropriate context for when he needs them. I could set up a simple database at home and teach him to bounce commands off of that until he becomes proficient in searching it, but why reinvent the wheel unnecessarily. Do any gerbils out there know of a resource that allows you to practice SQL/regex as you learn it?


TLDR = What's the best SQL/regex tutorial you know of?
sironomus
Gerbil
 
Posts: 65
Joined: Sun Feb 22, 2009 9:33 pm

Re: SQL queries for nontechnical users

Postposted on Wed Feb 23, 2011 8:45 am

I'd install a copy of MySQL and start working through the MySQL manual as there are plenty of examples in there, especially in the tutorial chapter. Skip the admin heavy parts of the manual. This will get you a basic database up and running that you can execute queries against and play with.
notfred
Grand Gerbil Poohbah
 
Posts: 3751
Joined: Tue Aug 10, 2004 10:10 am
Location: Ottawa, Canada

Re: SQL queries for nontechnical users

Postposted on Wed Feb 23, 2011 9:33 am

You can also get free copies of MS Sql Server Express along with the manager. I'd point him to a simple reference guide/examples such as the W3C found at http://www.w3schools.com/sql/default.asp
Corsair 600T | ASUS P8P67 PRO | Intel 2500k @ 4.4Ghz | EVGA 560 TI | G.SKILL Ripjaws Series 8GB | Corsair HX650 650W
steelcity_ballin
Gerbilus Supremus
Silver subscriber
 
 
Posts: 11921
Joined: Mon May 26, 2003 5:55 am
Location: Pittsburgh PA

Re: SQL queries for nontechnical users

Postposted on Wed Feb 23, 2011 11:27 am

Where is the data housed? Can you setup SSRS and then use Report Builder? If so, that is the way to go for it is very simple and easy to use.
Usacomp2k3
Gerbil God
 
Posts: 21318
Joined: Thu Apr 01, 2004 4:53 pm
Location: Orlando, FL

Re: SQL queries for nontechnical users

Postposted on Mon Feb 28, 2011 10:03 pm

So yeah, there's a standard for SQL that some databases adhere or ignore however they please. We can probably help you better by knowing what kind of database holds the data.
The best things in life are free.
http://www.gentoo.org
Guy 1: Surely, you will fold with me.
Guy 2: Alright, but don't call me Shirley.
titan
Grand Gerbil Poohbah
 
Posts: 3276
Joined: Mon Feb 18, 2002 7:00 pm
Location: Great Smoky Mountains

Re: SQL queries for nontechnical users

Postposted on Mon Feb 28, 2011 10:57 pm

I have been writing SQL queries for about 5 years now, prior to this I had studied Oracle in school. What I had learned in school was useful but nowhere near to what I have learned over the years. I write queries for clients' custom reports when static reports do not have info that a client needs and as a troubleshooting technique to debug software and find discrepancies on the application's interface. Complexity of SQL statements varies greatly. Fundamentally Access, SQL Server and Oracle SQL statements are the same with some syntax differences.

If this is something simple you can start off by using a built-in query builder and/or report builder if it is available. You have to understand relational database concept and be familiar with your database structure such as relationships between tables in terms of Primary and Foreign keys. Learning SQL is a long process itself and frankly molecular biologist should not waste his time on this. Any database management application should have a report functionality that will enable the end-user retrieve the necessary information with relative ease.

Which architecture does your father's database use? Did your father contact IT team for help? If this is a proprietary software did you contact the vendor for help?

Having said all that I need to reiterate that SQL query writing is not something a scientist should be involved with especially that he is not very technical. This is the software vendor's responsibility to provide all the necessary tools for the end-user.
Core i7 960 3.2GHz | 12GB RAM | 1.5TB HD | Asus GTX Titan | Dell WFP 3008 30" 2560x1600 | Windows 7 Ultimate 64 Bit
michael_d
Gerbil Elite
 
Posts: 562
Joined: Sun Dec 16, 2007 2:42 pm

Re: SQL queries for nontechnical users

Postposted on Tue Mar 01, 2011 11:49 pm

update:

IT at my fathers work was less then excited about letting novice users start poking around their database server. They have offered me the solution of giving me the required files in a tab-delimited .txt format. At this point I think it's easiest to complete the comparative analysis using a simple awk command. I could of course, set up my own database with the files they provided, but since awk is quite capable of doing what I need, I think this would be adding unneeded complexity. Is there any reason I should consider setting up a database when awk seems quite capable of completing the task?

michael_d wrote:Learning SQL is a long process itself and frankly molecular biologist should not waste his time on this.

Having said all that I need to reiterate that SQL query writing is not something a scientist should be involved with especially that he is not very technical. This is the software vendor's responsibility to provide all the necessary tools for the end-user.


It seems this is a conclusion held by many in the field. There are efforts underway to develop a simple to use(most likely GUI) form of query builder tailored to these kinds of searches. Unfortunately there is no real organization and funding behind the project. At the moment the people involved are doing it as a sort of weekend project to help friends.
sironomus
Gerbil
 
Posts: 65
Joined: Sun Feb 22, 2009 9:33 pm

Re: SQL queries for nontechnical users

Postposted on Wed Mar 02, 2011 12:12 am

If the queries are relatively simple, something like awk may be adequate. But once you start doing more complex queries -- and especially if you're doing joins across multiple data tables, or handling large datasets -- you really want a real database. Otherwise you're just reinventing the wheel.
(this space intentionally left blank)
just brew it!
Administrator
Gold subscriber
 
 
Posts: 37891
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: SQL queries for nontechnical users

Postposted on Wed Mar 02, 2011 2:29 pm

Yup, I'm with JBI on this. I'm probably one of the worst abusers of awk around (I use awk, sed, grep and shell scripting way after others have jumped to perl), but even I wouldn't try doing something like writing a DB query engine in it :-) If it's much more than can be achieved by grepping across flat files, I'd just load it in to another DB engine and run the queries on that.
notfred
Grand Gerbil Poohbah
 
Posts: 3751
Joined: Tue Aug 10, 2004 10:10 am
Location: Ottawa, Canada

Re: SQL queries for nontechnical users

Postposted on Wed Mar 02, 2011 2:47 pm

Although this may come as a shock, Microsoft Access is good for this purpose (too bad that it's useless as a database product ;) ). The reason being that, IIRC, you can design queries in the graphical UI, then you can see what SQL it generated for you. In fact, I'll go this far: if you can set up an ODBC link to whatever-database-that-is, you can use the Access frontend to query it.

This goes as general advice too: assuming your queries aren't extremely fancy, the first steps should be with a graphical UI that shows relations between tables and all that jazz, so that he can get the mental model right. Once he's understood some simple queries that way, he can proceed to actually writing SQL.
There is a fixed amount of intelligence on the planet, and the population keeps growing :(
morphine
Grand Admiral Gerbil
Silver subscriber
 
 
Posts: 10033
Joined: Fri Dec 27, 2002 8:51 pm
Location: Portugal (that's next to Spain)

Re: SQL queries for nontechnical users

Postposted on Fri Mar 04, 2011 2:12 am

A good SQL tutorial that I'd recommend is http://www.1keydata.com/sql/sql.html. This tutorial provides examples for each of the SQL commands and is pretty easy to follow.
doraimo
Gerbil In Training
 
Posts: 1
Joined: Fri Mar 04, 2011 2:09 am


Return to Developer's Den

Who is online

Users browsing this forum: No registered users and 1 guest