Slashdot Mirror
Beginning SQL?
$ynergy writes "I have been seeing more and more job listings for SQL programmers so, naturally, my interest as been sparked. I have picked up a few materials but, soon realized that it would be easier to apply if I had experience using database software. Would everyone agree? So I am looking for resources, online or in print, that would give a beginner a real in depth look at using database SW." There are at least two issues here: a) learning standard SQL (pick a standard, any standard :) and b) learning all the idiosyncracies of a particular database system. Probably learning basic SQL is the way to start.
Some slashdotter's may tell you to learn MySQL or PostgreSQL because they are open source. This is true, and it's good because they come with almost any Linux distribution. Unfortunately, business aren't looking for those skills, so it won't help you.
Here are some Monster stats (for open US jobs):
Tips and Tricks for Mozilla
Phillip Greenspun's book SQL for Web Nerds is a very nice introduction to SQL. It would be a good idea to grab a copy of PostgreSQL or one of those Oracle demo cds that are as common as AOL cds, and work through the exercises in it.
Please avoid MySQL if you are just learning SQL. You'll just have to unlearn all of the workarounds for the features (such as real transactions, and referential integrity to name two) which it is missing when you move to a real database.
My favorites are:
www.sqlcourse.com
www.sqlcourse2.com
These are good beginner sites that allow you to practice through a java app.
iRepairIT - iPhone, Mac, & PC Repair
This issue is a *lot* more complex than it first seems. There's a lot of really bad SQL code out there, and many of the authors don't even realize how little they know.
The problem is that it takes time and experience to really develop a sense for how to use the data. If you're a programmer, you should have at least some familiarity with performance issues even if you don't always pick the best algorithm for the problem. Likewise with a SQL database you really need to understand why 3NF is important, why referential integrity is a really good idea, etc. It's not uncommon for databases to span many gigabytes and a bad design can literally cost millions of dollars as you throw more hardware and expensive database licenses at the problem.
This isn't just theoretical - ghosting can be a problem with 3NF data, and you need to know how to recognize it and fix it. (More precisely, how to fix it without using 1NF or 2NF, which both have serious problems that 3NF fixes.)
Then there's the issues of views. It's easy to understand read-only views, but updateable views make life incrediby interesting. But this is critical - a bad updateable view will create a lot of subtle errors in your database.
Other issues - how do you access the data? This is everything from JDBC or Pro*C to JSP tag libraries. How do you handle bad data, or bad assumptions? (Nothing teaches you how hard it is to get a unique identifier like trying to actually find unique identifiers for real data.)
Finally, many of these sites aren't just looking for SQL knowledge, they're looking for specific packages like Oracle Financials.
I think the best way to illustrate just how much there is to learn is that a friend recently decided to get Oracle certification to help land jobs. She's been focusing on databases for almost a decade, yet she still had to study hard for the exams. I've been doing intermittent database work for even longer and have pulled several rabbits out of my hat - yet I know I would struggle to pass just one part (of four) of the exams.
But on the question at hand, my advice is to get an introductory text and start solving some problems. Create a database listing your CDs, then extend it to handle DVDs and VHS tapes, then extend it again to handle books and magazines. Create an index to keep track of your softball or bowling league stats - the teams, the players, the individual and team stats. You'll learn more from one or two reasonably large problems than you'll learn from a dozen books.
For every complex problem there is an answer that is clear, simple, and wrong. -- H L Mencken
Do *not* learn the actual query language first. Learn database theory and design before anything else. Don't even consider doing anything with a database until you know the six forms of normalization (at a bare minimum you need to know the first three; the second three are "gravy" for many applications and not even appropriate all the time). This includes knowing the requirements to reach each level of normalization within a database.
I have seen so many database layouts for various applications that have practically brought me to tears through their sheer stupidity. These were layouts designed purportedly by people who "knew SQL." There is a tremendous difference between "knowing SQL" and "knowing proper database design and implementation." Unfortunately, many people who claim to be database programmers do not realize there is a difference and assume that since they know the syntax of SQL, they know how to design a database.
I would recommend that you begin with "Database Systems" by Connolly and Begg. Read it cover to cover, then read it again. When you're done reading it the second time, skip through to the end of each section and do all the exercises without rereading any of the text. Once you can answer a majority of the questions correctly, then begin to consider designing database layouts. Before you look the book up on fatbrain or amazon, be warned that it is not light reading. It's 1,200+ pages, but is well worth it.
The ISBN is: 0201708574.
When you actually understand how databases work and how to effectively use them, you will thank yourself tremendously for taking the time up front. If you dive right in to learning the syntax of the query language without understanding the basics of design and implementation, you will make one stupid mistake after another with no end in sight. Then, someone more knowledgeable than yourself will come along and will have to start everything over from scratch to fix your screwups.
Doing it right the first time is especially important when designing databases for large systems. If you screw something up and don't learn from your mistake until you have millions of records in tables that are being quickly updated 24/7, fixing that mistake is going to be a nightmare and could very well cost your company a tremendous amount of money through downtime and resources spent on the fix and conversion.
Trying to keep this post from getting too long: the key is that there is absolutely no substitute for a solid understanding of the theory behind database design. You simply cannot be anything more than a witless hack at databases without this understanding. You will churn out terrible database layouts almost every time (unless you have an unbelievably lucky streak) and your projects will suffer because of this.
Sorry if this sounds harsh, but it really, truly is worth spending the time to learn the theory and design before trying to apply your efforts to a real world project. Of course, if you're impatient you can play around with a server at the same time you learn the theory. But do not make the mistake of neglecting the theory in favor of quickly learning the syntax.
Well that is kinda why people flame MySQL so much (*). In PostgreSQL and Oracle you have triggers and things like pl/sql and much more that I don't know about. Basically you can put a lot of the business logic in the database and use features that tie records together and protect against accidental deletions. Basically imagine another layer that handles a lot of checks and balances that free you from having to do the same in your perl/php/tcl/whatever code.
That's why I have learned so far...
* Plus of course ACID and all that other stuff that some people really love to argue about.