learning SQL - any advice on sources?

[JeenLeen]

I'm trying to learn some more SQL language. My knowledge is pretty basic, mainly what I've gleamed from a little bit of looking at SQL in MS Access and Visual Basic in Excel.

Any recommendations on some good sites? The only stuff I found so far is either just the very basics (simple Search commands) or stuff above my level.

Also, I found through a simple search some downloadable (as far as I can tell, legally) PDFs for 'teach yourself SQL in x minutes/days'. The site names look fairly legit, but I'm cautious. Any recommendations on those, and any to particularly avoid? (I'd post a link or two, but I don't want to accidently cause someone to get a virus.)

[TuggyNE]

"Teach yourself X in Y days" stuff is usually stupid, not illegal; it tends to take a foolishly short-sighted approach, and misrepresents a deep subject as being one you can master trivially. In reality, SQL is something you could spend a thousand hours learning.

Still, the basics are simple enough for the most part, and you can get some useful practice with e.g. SQLIsHard.com (http://www.sqlishard.com/). My usual method of learning a programming technology is to find some good sources of general advice and in-depth articles, then flesh out the majority of my understanding by reading references (Technet has some good stuff listed here, as well as a basic tutorial (http://technet.microsoft.com/en-us/library/2addc9be-67d0-423d-a457-192fe9d7d058)), using it in practical code, and searching for solutions to any problems that arise. To be honest, I think this organic approach has served me quite well: I learn the non-obvious pitfalls from articles and guides, but develop the core skills simply from doing, not watching.

[pendell]

I'm still learning SQL myself, and it's been 14 years. The way I learn is: Find out something I need to do, then from there learn how to do it. But abstract SQL is like learning any other language -- useless if not practiced in daily conversation.

So I would suggest grab something like SQLite (http://www.sqlite.org/) and build an application on it. For instance, the nethack spoilers are in flat files (http://www.statslab.cam.ac.uk/~eva/nethack/spoilerlist.html). How about putting all weapons, say, in a relational database? Then start thinking about queries you can write on that. Then add in other tables for other inventory items. Then you can start thinking about join queries to determine things like, say, food consumption or encumbrance.

Once you know what you want to do , finding the tools to make it happen with SQL is easy. But simply reading the material will give you an overview which you will forget five minutes after you put the book down.

My experience, anyway.

Respectfully,

Brian P.

[valadil]

I haven't been at it for quite as long as Pendell, but it's close. The problem with SQL for me is that I'm never doing a project in SQL. I'm doing a project in some other language that sometimes needs SQL. It's a side dish. And because of that I just haven't picked up as much as I should.

Anway, I find google usually tells me what I need to know as long as I can phrase it correctly. Here are some topics you should read about:

Queries: select, insert, update, delete.

Those are the basics. You probably already know them.

Joins. The difference between an inner and outer job will come up in job interviews all the time, but in my experience 99% of the joins I do are inner. Joins let you connect a couple unrelated tables against a common parameter.

Fancy stuff: group by, subqueries, concat/group_concat. Read up on them at your leisure. Or ask here if they're confusing.

Finally, I find it helpful to keep complex queries once coded. A lot of my SQL is a one time thing so I don't really need to reuse it. But I often run into the situation where I remember finding that magic command I need 6 months ago, but I can't quite recall its name. Having all my interesting SQL in one place makes that less of a pain. Since I use github at work, all my good SQL ends up in a gist. Really easy to find and reference at a later date.

[olelia]

On the whole join thing the easiest way I've found myself explaining it to others is Inner joins will restrict your data to the table you joined it to. Left joins allow for nulls to appear.

Slight side note...I assume you are just wanting to learn SQL and not SQL Server correct? While both the languages are very similar they do have different built in functions available to them.

[Razanir]

Slight side note...I assume you are just wanting to learn SQL and not SQL Server correct? While both the languages are very similar they do have different built in functions available to them.

SQLServer should be SQL. The difference between it, and say, MySQL is just in a handful of functions. For the most part, I'd imagine the syntax is similar. :smallconfused:

[factotum]

Check out http://www.sqlservercentral.com/. They have a whole series of "Stairway" articles which are designed to lead beginners through basic SQL concepts. It's rather heavily specific for Microsoft SQL server, but the basic concepts are the same in any version of SQL, so that shouldn't be too much of an issue.

(Oh, and you can download and install Microsoft SQL Server Express for free, so that's an alternative to things like SQLite for experimentation).

[TuggyNE]

(Oh, and you can download and install Microsoft SQL Server Express for free, so that's an alternative to things like SQLite for experimentation).

Even deploy it, for that matter.

[Manga Shoggoth]

SQLServer should be SQL. The difference between it, and say, MySQL is just in a handful of functions. For the most part, I'd imagine the syntax is similar. :smallconfused:

In my experience (Oracle, Ingres, SQL Server, Sybase) all the SQL dialects are sufficiently similar to each other that the main hurdle is learning the first one. After that you are looking for differences.

There are "free" versions of various DBMS offerings - MS SQL Express springs to mind - so you can set up a database and play with it - this is the best way to learn.

Books are good, but "teach yourself" books vary wildly in quality, and even then tend not to go too far beyond the basics. Why not visit your local library and see if they have any textbooks?

If you are looking at SQL Server the online manual and documentation on the Microsoft web site aren't too bad.

[valadil]

Here's another thought. Learning from trivial examples is well and good, but sometimes you need to work with real data. One source you could use that you may already have is your firefox bookmarks. They live in an sqlite file called places.sqlite, although its location will vary from OS to OS. Make a copy of that and explore!

Try to figure out what all the stuff they store means. Do queries for your most used bookmarks. If you use keyword searches, write a query that takes a keyword and returns a url (hint: this is where JOIN is your friend).

[factotum]

Along those lines, Microsoft provide a free training database download called AdventureWorks you can grab, but again, you'd need to use that with SQL Server.

[Cerussite]

First things first, SQL as a standalone language is pretty useless in terms of stuff you can do with only that. I'd find it better to learn something like php/django (for writing data-backed websites) or c++/java (for pretty much anything) alongside SQL. This way, you can learn SQL while building an application based on that, which leads to my second point:

You don't learn programming by opening a book and doing its assignments. Think of something you'd want to do using relational databases. Maybe you want to index your huge music collection so it's easily searchable by an application that generates playlist files for you based on what you want to hear. Maybe you want to organize your product catalog for your home business and put it in a website. It can be anything, and it can be really silly, as long as you're interested in it.

With that in hand, grab SQLite (the simplest SQL database I can think of) and the development environment of your choice and off with making it happen!

[pendell]

While all of the above is true, if you're looking to be a DBA you really want to pick a particular database and stick with it.

If all you care about is to be a jack-of-all-trades programmer like me, a standard knowledge of general SQL in any dialect is fine.

However, I recall one time we had a query against multiple databases that took an hour to run on an oracle database. Our DBA took my query and rewrote it to take six minutes.

The difference is that I had written using general SQL, the features that are common to all languages. Burvin (for that was his name), however, knew about a hidden index column Oracle used, which made the data retrieval much faster.

It seems like in every major database there is a common, generic SQL which will work on pretty much everything. But if you want to optimize your queries, you need to be familiar with the specific features of the particular database. Those features, of course, are not portable to other databases, since they constitute the Unique Selling Point for the product in the first place.

So, if all you want to do is program and use SQL on occasion, a general knowledge of SQL is enough. If you're trying to become a DBA, take the DBA certification classes in the relevant database.

Respectfully,

Brian P.

[Zherog]

First things first, SQL as a standalone language is pretty useless in terms of stuff you can do with only that. I'd find it better to learn something like php/django (for writing data-backed websites) or c++/java (for pretty much anything) alongside SQL. This way, you can learn SQL while building an application based on that, which leads to my second point:

This depends very strongly on the environment. In an Oracle Applications environment (what I work in), almost everything will be SQL, PL*SQL, or a standard Oracle tool such as Forms and Reports - which use SQL and PLSQL as the engine, and then add fluffy stuff around that.

In an environment with a RDBMS back end, the front end definitely can vary from site to site. It could be the Oracle tools I mentioned, or it could be php, java, C, C++, etc. For example, any message board you use that uses phpBB as the software has a MySQL backend and uses php to build the screens and fetch the data. vBulletin - this forum software - also has a SQL database on the backend; I'm not 100% sure what the front-end is written in, but I'd guess php as well.


However, I recall one time we had a query against multiple databases that took an hour to run on an oracle database. Our DBA took my query and rewrote it to take six minutes.

Take a look at what he did to your query and try to understand it. Ask him questions about why. SQL Tuning is a great skill to possess and increases your value as a developer. There are a metric ton of people who can write SQL queries. There is a significantly fewer number of people who can write well-tuned queries.


The difference is that I had written using general SQL, the features that are common to all languages. Burvin (for that was his name), however, knew about a hidden index column Oracle used, which made the data retrieval much faster.

That's the easiest trick, and the first one "tuners" will look for. Hitting an index - even if it's non-unique - will make the query faster if the problem is that it's doing a full table scan. (And then there's the opposite: there are a small handful of situations where you would actually prefer doing a single full-table scan rather than multiple index lookups; rare, but it does happen.)

Make sure that wasn't the only change he made, though. Depending on the version of the Oracle database he may have made subtle changes that you wouldn't think would affect performance, but have a huge impact. For example, in versions of the database prior to v10 (or was it v9?) the order of your tables in your WHERE clause affected performance. The trick back then was to have the smallest table that has a unique index you can use at the bottom of the WHERE clause, and then build from there.

Another caveat: Oracle has it's own syntax in addition to "standard" SQL syntax - and that can be another performance tuning aspect.

For example, in Oracle you could write:



SELECT a.first_name,
a.last_name,
b.job_title
FROM employee a,
jobs b
WHERE a.employee_id = b.employee_id

(simplified; assumes there is only one row in jobs that you want to join on.)

But standard SQL can also write that query as:



SELECT a.first_name,
a.last_name,
b.job_title
FROM employee a
JOIN jobs b
ON a.employee_id = b.employee_id


Both of those queries will work in an Oracle database, but in some RDBMSs, only the second query will work.

[valadil]

Re: sql tuning.

Are there any optimizations I can learn that are DB agnostic? Or are they all going to be optimizations specific to one particular DB?

[Zherog]

Making sure you hit an index - unique-index if possible - should be agnostic. I've not had to do tuning outside of Oracle, though, so I don't know which other tricks are Oracle-specific and which work everywhere.

[JeenLeen]

Thank you.

I am focusing on SQL in general, although I reckon I'll use SQLServer as well. There's a job I'm applying for and I meet the qualifications except for the one rather major one of knowing SQL queries. The job does require knowledge of writing SQL queries but not necessarily maintaining a SQL database as a Database Administrator.
I'd be performing a lot of data querying from various databases, compiling and analyzing the data, preparing data for integration between systems, etc., which honestly sounds fun if I can get competent enough.

It's in a field I've worked with, and I enjoy what coding & similar things I've done, but so far I've only worked with Access, Excel, and a little bit of SAS and R (plus C++ from about a decade ago.)

I appreciate the online sources. I picked up SQL for Dummies yesterday and have read through about a tenth of it. I look forward to downloading one of the database applications y'all mentioned earlier and testing it out tonight.

[Manga Shoggoth]

Re: sql tuning.

Are there any optimizations I can learn that are DB agnostic? Or are they all going to be optimizations specific to one particular DB?

Outside of making sure any joins use indexes properly there isn't that much.

Each RDBMS has its own query optimiser that tries to work out the best way of executing a query. Any tweaks tend to be specific to the optimizer, and hence the product in question.

That said, most of the time the the optimizers do a decent job. Most of the queries that really need optimising tend to be the autromatically generated ones from varioust tools.

[factotum]

Outside of making sure any joins use indexes properly there isn't that much.

I'm no expert on this, but I was under the impression that Oracle programmers tend to make heavy use of cursors, because they work well in that RDBMS, whereas if you use a cursor in a SQL server query you will be laughed at by small children in the street? :smallwink:

[Manga Shoggoth]

I'm no expert on this, but I was under the impression that Oracle programmers tend to make heavy use of cursors, because they work well in that RDBMS, whereas if you use a cursor in a SQL server query you will be laughed at by small children in the street? :smallwink:

Technically you are using a cursor nearly every time you run a SQL statement. These days you submit a query and get back a dinky little dataset object, and the cursor is hidden in the lower levels of the code.

Not that I do that much development these days, being a humble DBA*.

Bah. In my day you got up at the crack of dawn, wrote your SQL Statement, chipped the bind variables out of bits of waste silica, declared your cursor, opened it, fetched from it, checked your own status variables, closed it and then deallocated it by hand, compiled and relinked your code, and all before breakfast. Then you listened to the old farts** complaining about how much worse it was when all you had were OCI calls***.



* Actually, I am a psychotic DBA. It saves time.
** Relatively speaking.
*** It was: I started programing just after Oracle started using precompilers. If I had started with OCI calls my programming career would have been much shorter...

[factotum]

Technically you are using a cursor nearly every time you run a SQL statement. These days you submit a query and get back a dinky little dataset object, and the cursor is hidden in the lower levels of the code.

I don't think the actual SQL code generated uses a cursor--the client side code just grabs all the data from the query and then you use a client cursor to step through it. Willing to be proven wrong on that!

[Manga Shoggoth]

Willing to be proven wrong on that!

Likewise. However I haven't really noticed either camp avoiding client-side cursors more than the other. But, as I said, I haven't been close in on the development side for some time.

[Zherog]

I'm no expert on this, but I was under the impression that Oracle programmers tend to make heavy use of cursors, because they work well in that RDBMS, whereas if you use a cursor in a SQL server query you will be laughed at by small children in the street? :smallwink:

Depends.

If you're inside a PL*SQL program unit (report, form, trigger, whatever) and you're doing a lookup query, something like this:



SELECT job_title
INTO v_job_title
FROM jobs
WHERE job_id = :v_job_id


(assuming this query is designed to only ever bring back one row - that is, job_id is unique)

It's more efficient in Oracle to actually make that a cursor and then perform an open/fetch/close, because your open/fetch/close will only ever return one row. But if you do the query above, the Oracle engine actually turns it into a cursor and then tries to fetch two rows - the first to give you your data and the second to ensure there isn't a TOO_MAN_ROWS error.

Assuming job_id is indexed (and if it's not, then you have a lousy DB design), it's going to be fast regardless of which way you do it. But if that lookup is, for example, inside a big loop generated from your main cursor then it's going to be performing that lookup multiple times. And it's way better to run that query 500 times (for example) rather than 1000 times. It's actually one of the things I look at when tuning; you'd be surprised how often there's a cursor that fetches thousands of rows and then does three or four or five lookup queries inside the cursor. And all those implicit cursors add up to slower performance.

However, if you're sitting at the SQL prompt and run the same query, I'm pretty sure (but not 100%) that Oracle doesn't turn that into a cursor. And instead just runs it against the DB to return your rowset.