Beginning SQL? 50
$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.
postgresql book online (Score:1)
I learned (Score:2)
Re:I learned (Score:4, Informative)
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 (Score:4, Insightful)
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):
Re:Learning Database Systems (Score:1)
Re:Learning Database Systems (Score:2)
That's all the documentation I ever use, apart from a crappy wrox book on sql server 7.
graspee
SQL for web nerds (Score:4, Informative)
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 (Score:1)
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 (Score:2)
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 [postgresql.org], 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 (Score:2)
Sybase SQL guides (Score:1)
2 Good Sites (Score:5, Informative)
www.sqlcourse.com [sqlcourse.com]
www.sqlcourse2.com [sqlcourse2.com]
These are good beginner sites that allow you to practice through a java app.
Re:2 Good Sites (Score:2, Informative)
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.
Re:Tired of doing others homework (Score:1)
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.
Re:Tired of doing others homework (Score:1)
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
Much more complex than it first seems (Score:5, Interesting)
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.
3rd Normal Form and split-happy (Score:1)
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.
Design (Score:5, Insightful)
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 (Score:2)
Re:Design (Score:1)
Re:Design (Score:2)
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 [amazon.co.uk]. 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 (Score:1)
The book seems to also have gone up in price since I bought it (by about 15$!), but used is great too :).
-Tammie
Book recommendation (Score:1)
I would recommend Database Processing [amazon.com] 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 (Score:3, Informative)
Postgresql doesn't do quite as well as Oracle (much much smaller gap now though) but it has a smaller starting size.
Re:Because MySQL isn't a real database (Score:2)
Re:Because MySQL isn't a real database (Score:2)
Next time you do an install, maybe you can uncheck the parts of the distribution that you don't want.
Use what you can apply. (Score:1)
Ugh! (Score:3)
Why can't people be clear?!
First you say: job listings for SQL programmers
:) and b) learning all the idiosyncracies of a particular database system. Probably learning basic SQL is the way to start.
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
You're all wrong!
What do you want? There are *four* separate issues here.
- Learning SQL
- Learning embedded SQL
- Learning DB management (and CASE, etc.)
- 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.For issue 2, read the documentation of the language you need. They all do it differently.
Oracle and PostgreSQL (Score:1)
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!
Great SQL book for DB2 (Score:1, Informative)
dbo.Slashdot (Score:3, Funny)
SELECT FreeTips FROM SlashdotAudience WHERE Subject = 'SQL';
Learning SQL (Score:2)
That's where I learned SQL. It uses an interpreter and a live practice database.