SQL learning exercises
November 9, 2010 7:39 PM Subscribe
Suggestions for real-life(ish) SQL exercises?
I've started playing around with SQL (specifically MySQL) thanks to these and these basic tutorials. I started using the Sakila sample database so I would have some decent data to work with. I managed a few basics, but it's definitely a use it or lose it situation, and I don't have anything to use it on! I'd like to practice writing SQL queries for realistic problems on a realistic data set and find out if I got the right output.
So: has anyone run across some good web resources for SQL exercises? I've run out of scenarios I can invent based on the Sakila database, and there's no guarantee that I'm getting the right result with my joins anyway. Ideally I'd like a resource that poses a problem to solve with SQL and will tell me what the correct answer (i.e., the correct output of my queries) is once I've tried to solve it on my own. I'm not looking for anything fancy, just "Here's a data set. Try to answer [question] about the data. Here's what you should have gotten, and how you should have gotten it."
If there aren't any online resources for this, suggestions for a book of exercises are welcome, though (as with everything on AskMe) free is always better!
I've started playing around with SQL (specifically MySQL) thanks to these and these basic tutorials. I started using the Sakila sample database so I would have some decent data to work with. I managed a few basics, but it's definitely a use it or lose it situation, and I don't have anything to use it on! I'd like to practice writing SQL queries for realistic problems on a realistic data set and find out if I got the right output.
So: has anyone run across some good web resources for SQL exercises? I've run out of scenarios I can invent based on the Sakila database, and there's no guarantee that I'm getting the right result with my joins anyway. Ideally I'd like a resource that poses a problem to solve with SQL and will tell me what the correct answer (i.e., the correct output of my queries) is once I've tried to solve it on my own. I'm not looking for anything fancy, just "Here's a data set. Try to answer [question] about the data. Here's what you should have gotten, and how you should have gotten it."
If there aren't any online resources for this, suggestions for a book of exercises are welcome, though (as with everything on AskMe) free is always better!
SQLZoo is straight out of 1998, but it sorted out a lot of confusing SQL stuff with useful contextual exercises (i.e., movies and actors, as opposed to ONE TO MANY).
posted by GilloD at 7:58 PM on November 9, 2010 [1 favorite]
posted by GilloD at 7:58 PM on November 9, 2010 [1 favorite]
GalaxQL is an awesome hands-on interactive tutorial. You manipulate stars in a rotating galaxy via a built-in copy of SQLite.
posted by Monsieur Caution at 8:26 PM on November 9, 2010 [1 favorite]
posted by Monsieur Caution at 8:26 PM on November 9, 2010 [1 favorite]
2nd GalaxQL...it's awesome :)
posted by AltReality at 8:28 PM on November 9, 2010
posted by AltReality at 8:28 PM on November 9, 2010
Time to learn some theory then. Analogy: you've learned to add and multiply, but now you need to learn about number lines and binary operations and the ints and the rationals and the reals. (Best day in math for me, as kid: when I learned a fraction was just multiplying by the numerator then dividing by the denominator.)
Google the difference between OLTP and DSS databases, and decide which kind you're interested in. Very different approaches in terms of aim, design, implementation.
Learn about Normal Form, at least tothe Third Normal Form, or to the Sixth if you're ambitious.
Learn what SQL is good at: (it's all about sets, or relational algebra). Learn what it's bad at: (unstructured data, arbitrary level hierarchical data). Learn what it doesn't care about: (ordering of set elements).
Learn to write SQL in terms of sets. Don't do things element-wise, and so avoid stored procedures (for now) and cursors (forever). Try to write everything as a view; eventaully you'll build a "tool kit" of views.
First, write a view. Then write more views that are compositions of views. What a function is to a procedural language (C and C-derived languages) the view is to SQL. Just as in a procedural language a function should do one thing and do it well, so in SQL: a view does one thing and does it well.
Learn about aggregate functions. Learn when to use group by, when not to, and above all, what to group on. I make an extra 20K last year, by nowing what not to group on.
Design a database. Figure out what you want to represent (a billing system, a company's employees, genetics assays, whatever) and design the tables. You'll learn a lot from this. Hint: when the SQL is hard to write, either you're writing the wrong SQL, or your design is bad. Because SQL easy, so long as you think in sets. (SQL optimization is less easy, and database design is hard.)
If you want real world problems, look at teh SQL category questions here or on StackOverflow.
OK, one SQL teaser: Using only one table with only two rows, and as many views as you need, produce as your result set all prime numbers below 10000.
posted by orthogonality at 9:59 PM on November 9, 2010 [4 favorites]
Google the difference between OLTP and DSS databases, and decide which kind you're interested in. Very different approaches in terms of aim, design, implementation.
Learn about Normal Form, at least tothe Third Normal Form, or to the Sixth if you're ambitious.
Learn what SQL is good at: (it's all about sets, or relational algebra). Learn what it's bad at: (unstructured data, arbitrary level hierarchical data). Learn what it doesn't care about: (ordering of set elements).
Learn to write SQL in terms of sets. Don't do things element-wise, and so avoid stored procedures (for now) and cursors (forever). Try to write everything as a view; eventaully you'll build a "tool kit" of views.
First, write a view. Then write more views that are compositions of views. What a function is to a procedural language (C and C-derived languages) the view is to SQL. Just as in a procedural language a function should do one thing and do it well, so in SQL: a view does one thing and does it well.
Learn about aggregate functions. Learn when to use group by, when not to, and above all, what to group on. I make an extra 20K last year, by nowing what not to group on.
Design a database. Figure out what you want to represent (a billing system, a company's employees, genetics assays, whatever) and design the tables. You'll learn a lot from this. Hint: when the SQL is hard to write, either you're writing the wrong SQL, or your design is bad. Because SQL easy, so long as you think in sets. (SQL optimization is less easy, and database design is hard.)
If you want real world problems, look at teh SQL category questions here or on StackOverflow.
OK, one SQL teaser: Using only one table with only two rows, and as many views as you need, produce as your result set all prime numbers below 10000.
posted by orthogonality at 9:59 PM on November 9, 2010 [4 favorites]
I have been meaning to learn some SQL for a while, and GalaxQL is just the thing.
posted by scose at 10:25 PM on November 9, 2010
posted by scose at 10:25 PM on November 9, 2010
My database is Oracle so I can't recommend any MySQL books, but I really liked learning SQL by doing all the exercises in books. A good thing about a book is that often you think you understand a subject, but then in the exercises find out that you still missed something and with complicated queries the author will also explain *why* that specific answer is correct. I'm sure there are lots of good books about MySQL, so I'd just check the Amazon reviews. Any SQL textbook would work as well of course. I just checked out Introduction to SQL by Van der Lans from the library and it looks good, though I have only skimmed it and not really worked through it.
I also found it useful to download a data set about something that I am actually interesting in and try to get the data I want. I can easily check myself if the results make sense. For example: I wondered if the weather in a particular month was indeed much worse than usual. I downloaded a data set with all the weather data of the last 60 years in my country and made many queries about average temperature/rain fall, per day/month/year. I made walking averages, etc. It is a lot of fun! If you don't know any intereseting datasets there is always the Infodump (that page links to instructions for getting the files into MySQL).
posted by davar at 12:47 AM on November 10, 2010 [1 favorite]
I also found it useful to download a data set about something that I am actually interesting in and try to get the data I want. I can easily check myself if the results make sense. For example: I wondered if the weather in a particular month was indeed much worse than usual. I downloaded a data set with all the weather data of the last 60 years in my country and made many queries about average temperature/rain fall, per day/month/year. I made walking averages, etc. It is a lot of fun! If you don't know any intereseting datasets there is always the Infodump (that page links to instructions for getting the files into MySQL).
posted by davar at 12:47 AM on November 10, 2010 [1 favorite]
(And if you are unsure if your queries against the Infodump actually give the correct results, you can start with not too complicated queries that you can verify with the Infodumpster).
posted by davar at 12:50 AM on November 10, 2010
posted by davar at 12:50 AM on November 10, 2010
Joe Celko's SQL Puzzles and Answers, Second Edition. His other works may also be instructive.
posted by ob1quixote at 4:02 AM on November 10, 2010
posted by ob1quixote at 4:02 AM on November 10, 2010
I like to do SQL at a restaurant: 'SELECT * FROM MENU WHERE CALORIES LT 500 AND TASTE FACTOR GT 5 UNION SELECT * FROM WIFE_APPROVAL WHERE APPROVAL=1'
The trick is getting the syntax right. Oh, and I need a clause to make sure I don't order what she ordered.
posted by joecacti at 8:05 AM on November 10, 2010 [1 favorite]
The trick is getting the syntax right. Oh, and I need a clause to make sure I don't order what she ordered.
posted by joecacti at 8:05 AM on November 10, 2010 [1 favorite]
I like to do SQL at a restaurant: 'SELECT * FROM MENU WHERE CALORIES LT 500 AND TASTE FACTOR GT 5 UNION SELECT * FROM WIFE_APPROVAL WHERE APPROVAL=1'
The trick is getting the syntax right. Oh, and I need a clause to make sure I don't order what she ordered.
I don't want to derail, but I believe that should be INTERSECT instead of UNION
posted by alligatorman at 10:12 AM on November 10, 2010 [1 favorite]
The trick is getting the syntax right. Oh, and I need a clause to make sure I don't order what she ordered.
I don't want to derail, but I believe that should be INTERSECT instead of UNION
posted by alligatorman at 10:12 AM on November 10, 2010 [1 favorite]
This thread is closed to new comments.
posted by alligatorman at 7:49 PM on November 9, 2010