SQL tutorial /website ?

[GOBI]

Hi ,

i was wondering if any one know of any free sql tutorials or quiz website to share as i would like to test mine knowledge ?


TIA

[si_the_geek]

In the Database FAQ (link below, or via the sticky post at the top of this forum) there are links to a few tutorials.

[szlamany]

Show us what you know - post some queries you have developed...

[mendhak]

www.w3schools.com/sql/

www.sqlcourse.com

[GOBI]

Show us what you know - post some queries you have developed...

Hmm..
I think mine standard is somewhat around this thread.
http://www.vbforums.com/showthread.php?t=412595

But i would have problems with understanding or formulating a query like this without help .
http://www.vbforums.com/showthread.php?t=413333

So anyway for me to improve ?

[szlamany]

btw - that second link is not going to the right place - but I found it anyway.

I participated in both those threads with you.

Here's a neat thread going right now with a complex query - try to absorb what's going on in this one - feel free to post questions in that thread - I'll be monitoring it.

http://www.vbforums.com/showthread.php?t=415757

Testing your query as you build it is an important step in understanding the changes you are making. We build all our queries in QUERY ANALYZER and slowly test those queries - adding joins and verifying the affect they have - that's paramount to building a complex query.

If the query is for an UPDATE or INSERT, having a BEGIN TRAN/ROLLBACK wrapped around it makes great sense for testing.

But I am unclear what your real goal is? The syntax of SQL is extremely easy - very few keywords to deal with. But the ability to pile them on top of each other is where the power and difficulty is - and that can only be overcome, in my opinion, through practice and patience.

The second link that Mendhak suggested seems to have some nice tutorials - once you have those under your belt the syntax should be second nature.

[GOBI]

i am kinda of lost when it comes to creating correlated sub-queries and how it actually work or when shld it be used ..

[szlamany]

I bought and read a great book - Inside MS SQL Server 2000 by Kalen Delaney...

This book takes the hood off of the SQL Server engine - and once you truly understand and appreciate how the index leaves work and the data storage is accomplished then when to attack data using JOINS or sub-queries becomes almost second nature.

What do you mean by correlated sub-queries? Give an example if you can.

[GOBI]

I bought and read a great book - Inside MS SQL Server 2000 by Kalen Delaney...

This book takes the hood off of the SQL Server engine - and once you truly understand and appreciate how the index leaves work and the data storage is accomplished then when to attack data using JOINS or sub-queries becomes almost second nature.

What do you mean by correlated sub-queries? Give an example if you can.

http://www.vbforums.com/showthread.php?t=413333
Select RE.No, PR.PersonId, PR.PersonName
,RE.Destination
From Person PR
Left Join Records RE on RE.PersonId=PR.PersonId
Where RE.no=(Select Max(RE2.no) From Records RE2
Where RE2.PersonId=RE.PersonId)

Something like this ?

[si_the_geek]

I see, well that could be re-written using Group By, like this:

Select Max(RE.No), PR.PersonId, PR.PersonName, RE.Destination
From Person PR
Left Join Records RE on RE.PersonId = PR.PersonId
Group By PR.PersonId, PR.PersonName, RE.Destination

..however (and it's a big one), this assumes that the Destination is the same for all rows in the Records table for that person - if not you will get duplicate rows for each different value (each with a different No, as it will be the maximum for that Destination value).

The sub-query works better in this kind of situation as you want to select multiple values from a row in one of the tables, but only where a certain field (No) has a particular value (the Max for that person). I could be wrong here but I dont think that there is a 'reasonable' way (in terms of speed or complexity), if at all, to do that without a sub-query.

If you only wanted the value for No (or more fields, but all with an aggregate function like Max), then I would expect a Group By to be more efficient, and a bit easier to read IMO.

[szlamany]

I realize that the syntax is outside the norm for a query - but it is simple in that it's just calling the sub-query for each row in the FROM table and in that WHERE clause comparing the RE.NO value to one that is derived from the sub-query.

That's where the true power comes in though - as that sub-query is free to do whatever kind of select/join/where it wants to!