Set Theory for beginning database developers? 2

[hapax]

Can you recommend a good book or article that will give me a conceptual understanding of what I'm doing when I write SQL joins, etc? Something written at a beginner level would help me the most.

I'm a junior developer and I'd like to understand more of the overall concepts or theory behind what I'm doing when I write SQL joins, etc. Is mathematical Set Theory what I'm looking for?

 

[jrbarnett]

Basically yes. If you consider the results of a SELECT statement as "the set of results that match the criteria I have specified" rather than "the results I have answered" it becomes easier to understand IMHO.
Remember that if there happen to be no results that match your criteria, this is not an error from the perspective of the database (although it may be from an application that uses the database).

John

 

[smedvid]

Not sure what you are asking... but if your desire is to learn the intricate nature of how SQL Joins are constructed and optimized, then refer to "relational algebra sql". I had this in a Master Program at Penn State. It can get very very complicated to understand. There are tons of sources on the I*Net.

But, unless you really plan to be a DBA or really work to optimize large data set queries, this information is a little too much. Personally, as a SQL Server developer, I never expect to use the relational algebra information... but it is nice to know I guess.

This looked like a good overview of relational algebra:

http://www.cs.sjsu.edu/~lee/cs157b/fall2003/RelationalAlgebraToSQL.ppt

Good luck...



Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[hapax]

I want a better understanding of what I'm doing when I write SELECTs/DML and how I should design my queries. The books I have read say "this is how you do a join" but they don't really explain the theory or concepts behind what you are doing.

I want to understand the theory a little to help me design queries, rather just "hacking" in SQL until I get something that seems to work.

Since I read this article,

http://www.15seconds.com/issue/011009.htm,

I've been wanting to understand the underlying "set language" concepts better.

 

[SQLSister]

http://www.tek-tips.com/faqs.cfm?fid=4785

http://www.codinghorror.com/blog/archives/000976.html

These give some good examples of what the different joins are and how they affect the set of data you get back.

In terms of set-theory, you want to understand that you should write queries that affect the entire set of data you want to manipulate not one row at a time. This is most true when doing queries that make changes to the data (insert/update/delete) as most people seem to have no difficulty with the idea of a set of data from a select statement and especially true when processing data in triggers. Use of a cursor is the one of the key signs that you are not doing set-based work. People often don't realize they can use a select statment in an insert and joins in an update or delete statement. They can also do conditional processing using the case statement. Those are the basic tools you use to start thinking in terms of processing in sets vice row-by-row.

"NOTHING is more important in a database than integrity." ESquared

 

[r937]

perhaps chapter 3 of my book can help you understand joins (although it does not use any "set theory" -- if you want set theory, get a book by CJ Date)

you can read this chapter for free here:

http://sitepoint.com/article/simply-sql-the-from-clause/

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[smedvid]

SQLSister - Excellent link on Joins

http://www.codinghorror.com/blog/archives/000976.html

I will use that when teaching staff SQL...


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[chamilz]

this book helped me to explore more behind the T-SQL

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735623139#reader

 

[MichaelRed]

'uderstanding' the theory behind SQL is somwhat akin to learing Calculus to understand Algebra.

It may be interesting, but really provides little pratical benefit to those who actuall just use the algebra.




MichaelRed