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.

Comments

postgresql book online

[Brandon T.]

The oreilly book 'practical postgresql' is online for free at http://www.commandprompt.com/ppbook/. It has some useful information about postgresql and sql in general.

I learned

[redhotchil]

I learned SQL (MySQL style I guess, thats all I've ever used, flame me please, its only a filesystem or something) just by reading the online manual. After you see what it does, theres really not much to it. I think the programming is more on the other end, rather than on the SQL end.

Re:I learned

[cymen]

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.

Learning Database Systems

[jefflinwood]

I would recommend that you learn either Microsoft's SQL Server, or Oracle 9i. Both are freely downloadable (for a trial, or for non-commercial purposes) from their respective web sites. A great resource for learning more is InformIT. Their database section requires you to create an account though.

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):

• Oracle: More than 5000 (could include Oracle Apps)
• SQL Server: 2686
• MySQL: 101
• PostgresSQL: 16 (under postgres, postgressql, postgresql)
• IBM db2: 1100
• Informix: 286
• Sybase: 738
• Microsoft Access: More than 5000.

Re:Learning Database Systems

[Urgoll]

Yes, Oracle is the way to go. Not only because that's where the market is (though it does help), but also because most free databases don't implement full relational databases (they don't have all the ACID properties. While it's possible to work around those limitations, that's no way to learn a new skillset.

Re:Learning Database Systems

[Graspee_Leemoor]

And you can get the complete SQL server 2000 documentation here

That's all the documentation I ever use, apart from a crappy wrox book on sql server 7.

graspee

SQL for web nerds

[Pathwalker]

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.

Re:SQL for web nerds

[maeglin]

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.

Actually, for those reasons I'd say MySQL is a great starting point. With MySQL you can start with SELECT * FROM my_table without having to worry about the extra stuff PostgreSQL offers (and the added administration burden). Simple and clean. From that point you can move to PostgreSQL and learn constaints, transactions, PL/pgSQL, etc. When you're ready to play with the big dogs, you can then move on to Oracle 9i or DB2 where 90% of your time is spent on configuration, optimization and maintenance.

PostgreSQL isn't a terrible starting point. Getting used to it's more unique features like table inheritance and virtually infinite extensibility, however, will bite you in the ass when moving on more than knowing how to survive without transactions will.

dan

Re:SQL for web nerds

[Pathwalker]

While I agree with you that some of the more advanced features of PostgreSQL would confuse someone who is learning SQL (At least Epochs on queries [1] are no longer an issue!) I feel that this is not a major issue as the fact that the features exist, does not mean that a new user will encounter them.

Someone can start out just using the same basic SQL that would work on MySQL, Oracle, DB2 or any other database without having to worry about transactions (PostgreSQL will wrap each statement in a transaction if you do not set up a transaction block) or other concepts beyond using the database as a simple data store.

When the user is starting to encounter tasks that Referential Integrity, server side functions, or transactions could make easier, they don't have to go through the effort of installing another database system, and moving their projects over. They can continue working in the same enviroment, and start using advanced concepts at their own rate.

I suspect that a lot of people continue to use MySQL for projects that have grown beyond what it is suitable for because they do not want to have to go through the trouble of installing another database system, and instead apply far more effort in the creative use of table locks, and extensive application level logic to simulate the capabilities that a more complete database would give them. I suspect that had they learned on a different database, they would be more likely to realize when the time comes to move to a more complete system, as they would not have had the barrier of a platform change discouraging them from learning about these concepts.

Personally, I think someone should start learning on PostgreSQL until they reach the level where they are using transactions and foreign keys. Then they should play with MySQL, and learn how useful it can be for read dominated tasks with lots of simple queries. After getting used to the speed of MySQL, and seeing under what circumstances it falls down, and PostgreSQL pulls ahead, they should experiment with tuning PostgreSQL to give them an introduction to how to tweak a database. After that, they should be ready for the powerful beast that is Oracle.

1. Postgres used to track all of the changes to the database over it's lifetime. By specifying an Epoch for a query, you could run the query on the database as it appeared at a specific point in time in the past. I think this was removed in the change from Postgres95 to PostgreSQL.

Re:SQL for web nerds

[sigwinch]

With MySQL you can start with SELECT * FROM my_table without having to worry about the extra stuff PostgreSQL offers.

I disagree. If you are reading along with a tutorial, none of PostgreSQL's advanced features will bite you. You have to explicity use the features to run into them, and table inheritance is not the sort of thing you do accidentally, especially if you're using a generic "SQL For Dummies" type book.

Getting used to it's more unique features like table inheritance and virtually infinite extensibility, however, will bite you in the ass when moving on more than knowing how to survive without transactions will.

As long as you aren't trying to read straight through the reference manual, you won't come across the advanced non-portable features until you know enough to ignore them.

Sybase SQL guides

[roc_machine]

I always had a hard time finding these on the Sybase site, so I thought I would throw them up on my webspace for this post. I used these for my University Databases course and they were golden. They're not great for learning SQL from scratch, but they're an excellent reference for those tricky queries or table manipulations you might run into. I still use them occasionally for non-sybase DBMSs as well. I hope you find them as useful as I did.

2 Good Sites

[UnifiedTechs]

My favorites are:

www.sqlcourse.com

www.sqlcourse2.com

These are good beginner sites that allow you to practice through a java app.

Re:2 Good Sites

[wrinkledshirt]

I second the recommendation of these two sites. Great quick guides to the syntax.

Another good way to learn SQL (don't laugh) is to practice using Access databases queries. You can design the query visually, and then switch views into SQL view to see what code you end up with. There's some inevitable mangling that goes on, but I regularly do this when I've got several tables and I can't remember where the brackets need to go for all those inner joins. I'm sure others offer this ability too, but if they don't, at least you have that one.

There are a few free database engines out there that have command line interfaces, so that'll force you to learn the language.

Also, if you're hoping to get into SQL in general, learn it for two different engines. That'll give you some idea of what to prepare for, since invariably every SQL implementation is a little bit different (the way they handle strings, escape characters or wildcards, or certain features available in one that aren't SQL-compliant, etc.).

Finally, if you're hoping to learn an API with it, get into PHP. It has very clean interfaces with several different databases.

Much more complex than it first seems

[coyote-san]

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.

Design

[highcaffeine]

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.

Re:Design

[cymen]

I don't put a lot of stock in Amazon.com reviews but this one is odd - almost half are negative and from apparently younger readers while a bit more than half are positive (and generally written with more care). The 2nd edition paperback can be brought for $26 used from Amazon.com.

Re:Design

[Urgoll]

I can only second that. After trying to design databases before learning the theory in depth (thinking that an introductory undergrad-level class was enough), I had to face the truth: Database design is a complicated task.

Re:Design

[sql*kitten]

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.

I agree with all the points in the parent post - "knowing" SQL (or indeed, any other programming language) is to software engineering as bricklaying is to civil engineering. SQL is like playing the piano, easy to learn, hard to master. I have personally experienced large scale projects that have literally cost millions of dollars to re-work after someone who "knew SQL" had made a mess of the database. Probably the most laughable was a billing system that updated customers balances in-place (in the same table that stored the customer's name and address!) rather than recording transactions and calculating balances with a roll-up. The front-end software the same team had written was flawed and recording the wrong numbers (in some cases, they had bound the columns to the wrong fields, in others they had added where they meant to subtract, etc), and since no raw data history was recorded anywhere (another bit of bad design was automating dumping the audit logs whenever that disk looked like it was getting full), all that money had simply... disappeared.

The book I recommend is this one. Ignore the reviewer, who has missed the point: good database design is difficult, and this book is for people who can handle the (math) theory behind it.

Re:Design

[belldandy]

I agree with the majority of this post, except for the book choice. I found Connolly And Begg to be a much less idea book as compared to "Fundamental of Database Systems" by Elmarsri/Navathe ISBN 0805317481. Granted it has been about 4 years since I last really read either book, but from what I remember, Elmarsri was a much more consice and clear read.

The book seems to also have gone up in price since I bought it (by about 15$!), but used is great too :).

-Tammie

Re:Tired of doing others homework

[Ambush_Bug]

Enough already with the "use a search engine" comment. I think it's safe to assume that almost any "Ask Slashdot" question could be answered to some degree by typing the question into google...
what you won't get from google is a decent, "peer reviewed" answer... just because some tutorial or site is on the 10th page of a google search doesn't mean it's not the best. Likewise, the first ten results might not be the most relelvant. People ask things of slashdot because they are looking for answers from people who have opinions and experience, not from bots who have been tricked by judicious usage of meta tags. I guess the next "First Post" will be "First Use Google Answer to Ask Slasdot".... geeze.

That being said, fair enough on the rest of the comment.

Book recommendation

[crudeboy]

I think it's more or less necessary to learn and understand the concepts of database design, including modelling, normalization etcetera before picking up a specific dbms-product.

I would recommend Database Processing by David M. Kroenke, ISBN 0130648396.

After having read and understood that book I would start looking at a commercial dbms like Oracle, DB2 or MS SQL Server as they are they most frequently used.

In my opinion MySQL and Postgres are fine products, but if you're looking to get an overpaid job, go with Oracle...

Re:Size

[rtaylor]

Load a billion records into your MySQL database through 20 tables, then do random 10 table joins. Thats why.

Postgresql doesn't do quite as well as Oracle (much much smaller gap now though) but it has a smaller starting size.

Use what you can apply.

[mountainhouse]

I'd use whatever SQL I could easily lay my hands on, and that allowed me to build some sort of application. I find it real hard to learn a tool just for the sake of learning it. It comes so much more easily when you apply it. I see a number of comments comparing SQLs. Personally, I started with Oracle, and currently use mostly NCR Teradata (Try inserting 30 million 200 bytes rows into a table in 8 seconds with Oracle ;-) ). I'd say about 80% of my Oracle knowledge transferred, even though Teradata is pretty strange animal (very distributed). Do others have opinions about what percentage of SQL knowledge transfers from one flavor to another?

Ugh!

[Chacham]

Why can't people be clear?!

First you say: job listings for SQL programmers

Then you think you wish you: had experience using database software

and compound it by believing: that would give a beginner a real in depth look at using database SW.

Then michael (with his double at-sign) comments: 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.

You're all wrong!

What do you want? There are *four* separate issues here.

1. Learning SQL
2. Learning embedded SQL
3. Learning DB management (and CASE, etc.)
4. Learning DB ideosynchracies

I will assume that issue 3 and 4 are not the case. (because anyone who gets these things mixed up probably would be pretty horrible at designing tables anyway. :-)) Rather 1 and 2 are important. Number 1 is easy. Just find a good book. The most up-to-date standard is probably a waste of time since it adds little, and is most likely not yet fully implemented. Personally, I found the book, "Understanding SQL" by Martin Gruber to be an excellent book. He skips design until the end of the book. Which is a good thing. Learning design first is silly, since you have no idea as to what you are designing for. But, most likely, you should skip the designing pages yourself, and let the DBA create the tables in the company's well-defined format.

For issue 2, read the documentation of the language you need. They all do it differently.

Re:Because MySQL isn't a real database

[mnordstr]

Yes, I know that. But with 2.5gigs it should be able to do genetic research =)

Oracle and PostgreSQL

[RinkSpringer]

We learn Oracle quite extensively at school. I have found that PostgreSQL resembles Oracle far better than MySQL did.

As for anything, to learn is to do... therefore, I recommend you get both PostgreSQL and Oracle from the websites. Oracle is freely available for educational purposes, and PostgreSQL is free anyway.

Besides, PostgreSQL has a very good SQL reference, which also lists what is and what is not ANSI SQL (boy, it came as a great surprise to me that LIMIT is *not* ANSI SQL!)

Anyway, I recommend you get both of these database systems, find some tutorials here and possibly using Google, and learn by experimenting... that usually is the best way.

Good luck!

Re:Because MySQL isn't a real database

[bakes]

The 2.5 gig also includes stuff like the OEM agent and management server, the management tools (all with GUI), the names server, development libraries (for C and Java, plus others like Cobol and Fortran), network configuration tools (also GUI), the graphical installer tools, documentation, and sample database. There is undoubtedly lots of other stuff but I don't have access to an Oracle installation at the moment to tell you what is in each directory.

Next time you do an install, maybe you can uncheck the parts of the distribution that you don't want.

Great SQL book for DB2

[Anonymous Coward]

There is an amazing SQL book by Graeme Birchall called DB2 SQL Cookbook, downloadable as an PDF. It contains all the funky stuff you can do with SQL on DB2. DB2 is pretty close to the ANSI SQL standard so a most of the stuff should work on other databases as well... The url is: "http://ourworld.compuserve.com/homepages/Graeme_B irchall/HTM_COOK.HTM

Re:Tired of doing others homework

[Ambush_Bug]

Pure lameness? I guess being a beginner at something doesn't measure up to your level of "nerdiness"... that's sad. The guy said he had done a bit of research, but it seeemed like everything he found was geared to people who already knew what they were doing.... it's like telling a newbie "read the man page"... with experience, this is perfectly acceptable, but when you're just starting out, terse documentation doesn't make sense.

I guess I won't debate whether it's nerd news or not... but it obviously matters to all those companies who are willing to pay big $$ for a database programmer.
I still maintain my point... if you don't have anything productive to say, don't bother with posting... if you think the question was lame, tell CowboyNeal or somebody /. they picked a lame question... Geek snobbery isn't nice, and it isn't very useful... and you certainly won't stop "lusers" from being "lusers" by being mean to them.

dbo.Slashdot

[PDHoss]

SELECT FreeTips FROM SlashdotAudience WHERE Subject = 'SQL';

Learning SQL

[hether]

Try www.sqlcourse.com and www.sqlcourse2.com
That's where I learned SQL. It uses an interpreter and a live practice database.

3rd Normal Form and split-happy

[Tablizer]

(* 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.) *)

Sometimes people take the normalization rules too extremes and try to divide entities into sub-entities. I suggest one not get split-happy.

Normalization should remove unnecessarily duplicate data, but if you start to use it as a sub-grouping mechanism, then you can create problems IMO. Just because two fields are somewhat "related" today, does not mean they will be tomarrow. The normalization rules as worded sometimes don't consider possible future changes.

Just a caution to keep in mind.