Semi-advanced SQL tutorial/ebook/video/something

Question

I've recently been posting quite a few questions about database design and I've got some great answers. However, lots of the things that have been mentioned I've never heard of! These things include triggers on databases, constraints (using what looks like a SQL CHECK keyword) and various other things like that.

I guess this is to be expected when most of my SQL and RDBMS knowledge I got from various appendices in the back of programming books giving a "Quick into to SQL".

So...I want to try and fix this now. What I'm looking for are recommendations for good tutorials, webpages, ebooks or anything else that might help me with learning these concepts. I'd prefer something that wasn't directly the documentation for a tool (like MySQL) but was a bit more explained (and if possible more database-agnostic).

Update: Just wanted to emphasise that I'm looking for relatively DB-agnostic responses here if at all possible. If there were to be any leaning towards a particular database then it should be MySQL - I have no experience at all with SQLServer and am unlikely to start using it.

Answer 1

I'd recommend Itzik Ben-Gan's Inside SQL Server: T-SQL Querying. For SQL Server, I haven't read anything better. He gives some really good advanced examples in there.

Answer 2

An awesome site is www.sqlservercentral.com. You have to register to view most of it, but it has everything there. There's a section of multiple choice questions (Question of the Day), which explains the correct answer, obviously daily. It's got a load of scripts as well, with explanations and articles on them, which should give you a good overview of what you can do.

Definitely worth a visit!

BTW, I am in no way affiliated with SQL Server Central :)

Answer 3

For good intermediate-advanced but platform agnostic books anything by Joe Celko or Chris Date is a good start, in particular SQL for Smarties, which covers many nuances and has a reasonably practical approach.

For data modelling some of the patterns books like Data Model Patterns or The Data Model Resource book are a good start as they have a good collection of worked examples.

Aside from that, some basic pointers:

1. Understand 1NF, 2NF and 3NF. Otherwise known as: The Key, The Whole Key and Nothing But the Key (so help me Codd). See This Stackoverflow discussion for a somewhat heated but cogent discussion about normalisation.

2. Stackoverflow has quite a few tags with reasonably good database content.

3. Set operations are your friend. If you're coming from a prodecural/OO background make the effort to do the paradigm shift.

Answer 4

There is a website that offers a fairly good summary of the basic concepts of data modeling and database design. It's part of the University of Texas Website. Unfortunately, this modeling and design website has been archived and is no longer maintained. But you can still access it.

http://www.utexas.edu/its/redirect.php?loc=windows/database/datamodeling/

Don't let the word "windows" in the URL fool you. The content has almost nothing to do with windows. It's reasonably "agnostic". It should apply pretty easily to MySQL, Postgres, Oracle, SQL Server, etc.

You won't get the kind of tutorial material and the wealth of examples from this website that you will get from a good book. And there are a few places where I view their presentation as unfortunate. But by and large, it's helpful in the areas it covers. And it's concise.

Data modeling and Database design isn't the same thing as developing application code that uses a database. However, you can't really learn good database development skills without understanding the basics of modeling and design. And you can't profitably use modeling and design skills without getting involved in development.

This website might be part of your overall search for database oriented tutorials, books, and websites.

Edit: After posting this, I followed the link and tried to navigate the site. For some reason, it repeats the "entering UTexas archives" page every time you follow an internal link. This is terribly distracting. It didn't do this the last time I visited the site. I'm sorry if this interferes with using the site.

Answer 5

Here are the SQL books on my handiest bookshelf:

• SQL for Smarties, Joe Celko
• SQL Programming Style, Joe Celko
• SQL Performance Tuning, Peter Gulutzan and Trudy Pelzer
• SQL-99 Complete, Really, Peter Gulutzan and Trudy Pelzer

Answer 6

See this SO question.

Answer 7

Check the Erland Sommarskog's website.

It would give you a very comprehensive insight into some more advanced aspects of MS SQLServer. It is SQLServer specific but I belive that you can apply many concepts to other DB systems

It covers topics like:

• The Curse and Blessings of Dynamic SQL
• Arrays and Lists in SQL Server
• Dynamic Search Conditions in T-SQL

and more