SELECT training FROM internet WHERE user = 'stymied';
March 20, 2010 10:30 PM Subscribe
I find myself employed (yay!) in a position that requires SQL knowledge that I don't have, and can't quite wrap my head around (boo!). I need practical training/guides to remedy this.
Specifically, I'm dealing with a Teradata setup both as a minor DBA-to-be and as a system engineer/business analyst. My real background is Solaris/Linux administration with some MySQL and Oracle 9i/10g maintenance, so I'm not completely in the dark when it comes to table/view create/alter work, but this is a completely different beast for me in terms of scale and complexity when it comes to select/update work.
I'm in need of training materials that have practical examples of SQL queries/updates. I have PDFs of the Teradata 12 documentation (seriously, that's thousands of pages) but I learn far better by example - brief query examples that use a1, b2, c3 (as the PDF docs mostly seem to contain) don't tell me much, but if I can compare a query against actual tables that have some practical use case, I stand a better chance of grasping what the query is really doing. I have a borrowed copy of "Teradata SQL - Unleash the Power" but it only goes so far.
I probably need actual training classes, but that's not likely to happen. Is Teradata Express worth setting up? Are there ANSI SQL playgrounds that let you build up meaningful complex queries? Am I doomed to be "that guy" that bugs the experienced people for months on end to much eye-rolling?
Specifically, I'm dealing with a Teradata setup both as a minor DBA-to-be and as a system engineer/business analyst. My real background is Solaris/Linux administration with some MySQL and Oracle 9i/10g maintenance, so I'm not completely in the dark when it comes to table/view create/alter work, but this is a completely different beast for me in terms of scale and complexity when it comes to select/update work.
I'm in need of training materials that have practical examples of SQL queries/updates. I have PDFs of the Teradata 12 documentation (seriously, that's thousands of pages) but I learn far better by example - brief query examples that use a1, b2, c3 (as the PDF docs mostly seem to contain) don't tell me much, but if I can compare a query against actual tables that have some practical use case, I stand a better chance of grasping what the query is really doing. I have a borrowed copy of "Teradata SQL - Unleash the Power" but it only goes so far.
I probably need actual training classes, but that's not likely to happen. Is Teradata Express worth setting up? Are there ANSI SQL playgrounds that let you build up meaningful complex queries? Am I doomed to be "that guy" that bugs the experienced people for months on end to much eye-rolling?
Best answer: You just need SQL query instruction?
Maybe this book can help: SQL Queries for Mere Mortals. It was highly recommended to me when I took my Basic SQL course a month ago. And I have friends who have the book, and they find it useful.
posted by spinifex23 at 11:13 PM on March 20, 2010
Maybe this book can help: SQL Queries for Mere Mortals. It was highly recommended to me when I took my Basic SQL course a month ago. And I have friends who have the book, and they find it useful.
posted by spinifex23 at 11:13 PM on March 20, 2010
SQL is very simple.
If I join two tables, I have every row in the first table, paired with every row in the second table. If table one has X rows, and table two has Y rows, the join has X * Y rows.
The join can be thought of as a table, so if I join a third table to the first two, and the third table has Z rows, I have X * Y * Z rows, one row for every row in the first table times one row for every row in the second table times one row for the third.
Now, I get rid of every row that doesn't match the join predicate (for which the join predicate is not true). If my join is table1.x = table2.y, every row where tabe1.x does not equal table2.y goes away.
This subset can be thought of as a table. Now I apply the predicate in the where clause. Any row for which the predicate is not true goes away.
This leaves me with my result set.
Leaving aside distinct, unions, and grouping, the above describes every possible result set.
Distinct just eliminates duplicate rows, leaving one row instead of several. A row is a duplicate of another if every column in one row is the same as the same column in the other row.
Union just combines result sets; by default eliminating duplicate rows.
Grouping eliminates any rows that are duplicative for the subset of columns in the group by, while keeping aggregate data (count, max, etc.) about the group.
And that is the totality of most select queries (I've left off existential queries, and set minus). I also haven't gotten into what null means.
That's it.
posted by orthogonality at 11:30 PM on March 20, 2010 [4 favorites]
If I join two tables, I have every row in the first table, paired with every row in the second table. If table one has X rows, and table two has Y rows, the join has X * Y rows.
The join can be thought of as a table, so if I join a third table to the first two, and the third table has Z rows, I have X * Y * Z rows, one row for every row in the first table times one row for every row in the second table times one row for the third.
Now, I get rid of every row that doesn't match the join predicate (for which the join predicate is not true). If my join is table1.x = table2.y, every row where tabe1.x does not equal table2.y goes away.
This subset can be thought of as a table. Now I apply the predicate in the where clause. Any row for which the predicate is not true goes away.
This leaves me with my result set.
Leaving aside distinct, unions, and grouping, the above describes every possible result set.
Distinct just eliminates duplicate rows, leaving one row instead of several. A row is a duplicate of another if every column in one row is the same as the same column in the other row.
Union just combines result sets; by default eliminating duplicate rows.
Grouping eliminates any rows that are duplicative for the subset of columns in the group by, while keeping aggregate data (count, max, etc.) about the group.
And that is the totality of most select queries (I've left off existential queries, and set minus). I also haven't gotten into what null means.
That's it.
posted by orthogonality at 11:30 PM on March 20, 2010 [4 favorites]
Best answer: Well, no; almost every real-world SQL query of any complexity will include outer joins. sysinfo, I recommend O'Reilly's SQL Cookbook.
posted by nicwolff at 12:41 AM on March 21, 2010
posted by nicwolff at 12:41 AM on March 21, 2010
Hard to tell what level of info you're needing, but my recommended go-to, cheat sheet, on-the-job intro to SQL is Sams Teach Yourself SQL in 10 Minutes by Ben Forta.
Best introduction to *SQL* I've seen. Quick, and concise because it just covers SQL from the perspective of someone jumping in and making queries, updates etc, it just covers what it says on the cover, Structured Query Language, it doesn't bother with database design and normalisation etc since that's usually a very different role (although 'Database Design for Mere Mortals' is pretty good for that, and it's the same author as 'SQL Queries for Mere Mortals'? But Ben Forta's is visibly more succinct).
Use Amazon's read inside, check for it at your library, walk into any good bookstore, and see if it's what you're looking for.
posted by Elysum at 3:08 AM on March 21, 2010
Best introduction to *SQL* I've seen. Quick, and concise because it just covers SQL from the perspective of someone jumping in and making queries, updates etc, it just covers what it says on the cover, Structured Query Language, it doesn't bother with database design and normalisation etc since that's usually a very different role (although 'Database Design for Mere Mortals' is pretty good for that, and it's the same author as 'SQL Queries for Mere Mortals'? But Ben Forta's is visibly more succinct).
Use Amazon's read inside, check for it at your library, walk into any good bookstore, and see if it's what you're looking for.
posted by Elysum at 3:08 AM on March 21, 2010
The SQL Zoo may be what you're looking for.
posted by scalefree at 5:23 AM on March 21, 2010 [1 favorite]
posted by scalefree at 5:23 AM on March 21, 2010 [1 favorite]
Best answer: I think you are looking for Teradata-specific info, since you say you already have some basic SQL skills. There are some pointers over at stackoverflow.com.
posted by SNACKeR at 5:23 AM on March 21, 2010
posted by SNACKeR at 5:23 AM on March 21, 2010
I also haven't gotten into what null means.
Being able to explain null in a single sentence is my gatekeeper question that divides people who call themselves "sql experts" from people who actually are. If you are ever in a future interview where someone asks this, be prepared.
n'thing the o'reilly cook book, as it will provide you with lots of easily modified recipes for your daily use, once you understand how to translate the sample schema they use, to your real world schema.
posted by nomisxid at 7:47 AM on March 21, 2010
Being able to explain null in a single sentence is my gatekeeper question that divides people who call themselves "sql experts" from people who actually are. If you are ever in a future interview where someone asks this, be prepared.
n'thing the o'reilly cook book, as it will provide you with lots of easily modified recipes for your daily use, once you understand how to translate the sample schema they use, to your real world schema.
posted by nomisxid at 7:47 AM on March 21, 2010
The syntax will be slightly different but you can easily pick up the basics using data you understand by creating 2 or 3 tables (customer, product, sales for example) in Access, use the GUI to create a query and then toglling the query view to SQL. I've had several "aah, yes" moments teaching people database and SQL using Access. Once you see what's happening, you can leave Access far behind and start using proper databases.
posted by jontyjago at 9:31 AM on March 21, 2010
posted by jontyjago at 9:31 AM on March 21, 2010
nomiskid, the way I describe NULL's ineffable weirdness is that "NULL is a non-value so unknown that it might even be divisble by zero."
posted by nicwolff at 1:34 PM on March 21, 2010
posted by nicwolff at 1:34 PM on March 21, 2010
Response by poster: Thanks for all the suggestions, everyone - I was able to take a look at O'Reilly's SQL Cookbook & SQL Queries for Mere Mortals, and they both look very helpful. I should have specified my need better - I have a decent grasp of the basics; it's mostly things like outer joins, nested subqueries, proper use of aggregation and a vastly larger database setup than I'm used to that have given me pause. Teradata-specific info regarding the optimizer/parsing engine and specific pitfalls to avoid would be helpful, so I'll take a look at the developer site linked from stackoverflow and I've gone ahead and grabbed the express VM to play with at home.
posted by sysinfo at 6:42 PM on March 22, 2010
posted by sysinfo at 6:42 PM on March 22, 2010
This thread is closed to new comments.
posted by guybrush_threepwood at 11:09 PM on March 20, 2010