Good SQL books for (SQL-server, oracle, DB2, Postgress, MySQL)

Hello All,

Anyone could suggest good books for writing SQL?
(and also optimise the performance of SQL statements).

I am planning to buy 2 or 3 SQL related books. I know the basic SQL (e.g. writing join, left join, nested join). I have been using SQL for 3 months. I am working as a data analyst/ data mining consultant. So I am NOT looking the DBA parts or the programming parts. Just writing adhoc SQL queries to extract data for data mining purpose. The queries have to be optimised and fine tuned.

Any advise would be very much appreciated.

Thanksif you are trying to cover the bases then SQL in a nutshell from O'Reilly is quite usefull. although it has dated very quicky as new verions have come out.

The version I have covers SQL Server, MySQL, Oracle & PostgreSQL.

As to to fine tuning tuning your weaponf fo choice should have suitable tools such as explain etc...|||Thanks, I bought this just now from amazon. Yes, one of them is SQL in a nutshell. Hopefully they are good choices.

-----------------
1 of: SQL Tuning [Paperback]
By: Dan Tow
$26.37


1 of: Data Modeling Essentials, Third Edition (Morgan Kaufmann Series in Data Management Systems) (The Morgan Kaufmann Series in Data Management Systems) [Paperback]
By: Graeme Simsion, Graham Witt
$35.93


1 of: SQL Performance Tuning [Paperback]
By: Peter Gulutzan, Trudy Pelzer
$28.49
-

1 of: SQL Pocket Guide [Paperback]
By: Jonathan Gennick
$9.95


1 of: SQL In A Nutshell, 2nd Edition [Paperback]
By: Kevin Kline, et al
$29.67

Good Query Writing......

SQL Query,
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
IS it possible to run a query where I can the Col2 as sum from previous
column for each row in col1?
Col1
Col2
1
1
2
3
2
5
3
8
4
12
Thanks,
Hilton
depends what you mean by previous column.
1, 2, 2,
Which 2 comes first? To define that you will need some other value to sort on
If neither is before the other then
select i, sum((select t2.i from tbl where t2.i <= t.i)) from tbl
If there's no other column to sort then use a temp table with an identity
select i, id = identity(int,1,1) as id into #a from tbl
select i, sum((select t2.i from #a where t2.i < t.i or (t2.i = t.i and t2.id
<= t.id)))
from #a
"John Hilton" wrote:

> SQL Query,
>
> Col1 in Table1 has the following data.
>
> Col1
> 1
> 2
> 2
> 3
> 4
>
>
> IS it possible to run a query where I can the Col2 as sum from previous
> column for each row in col1?
>
> Col1
> Col2
> 1
> 1
> 2
> 3
> 2
> 5
> 3
> 8
> 4
> 12
>
>
> Thanks,
> Hilton
>
>
|||Nigel Rivett, I really appricate your time in replying this post!!!
Since the formatting is lost in the post,
I am posting this again.
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
Is it possible to run a query where I can get the Col2 as sum from previous
column for each row in col1?
So the query result should be as follows.
Col2
1
3
5
8
12
Thanks,
Hilton
|||Didn't I answer that?
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Here's an alternative to Nigel's:
SELECT [ID] = IDENTITY(INT, 1, 1)
,Col1
INTO #Temp
FROM tbl
ORDER BY Col1 ASC
SELECT Col1 = AVG(t1.Col1)
Col2 = SUM(t2.Col1)
FROM #Temp AS t1
INNER JOIN
#Temp AS t2
ON t1.[ID] >= t2.[ID]
GROUP BY t1.[ID]
ORDER BY t1.[ID]
DROP #Temp
Sincerely,
Anthony Thomas

"John Hilton" <John_Hilton2004@.hotmail.com> wrote in message
news:%23Fw2HOx4EHA.1188@.tk2msftngp13.phx.gbl...
Nigel Rivett, I really appricate your time in replying this post!!!
Since the formatting is lost in the post,
I am posting this again.
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
Is it possible to run a query where I can get the Col2 as sum from previous
column for each row in col1?
So the query result should be as follows.
Col2
1
3
5
8
12
Thanks,
Hilton

Good Query Writing......

SQL Query,
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
IS it possible to run a query where I can the Col2 as sum from previous
column for each row in col1?
Col1
Col2
1
1
2
3
2
5
3
8
4
12
Thanks,
Hiltondepends what you mean by previous column.
1, 2, 2,
Which 2 comes first? To define that you will need some other value to sort on
If neither is before the other then
select i, sum((select t2.i from tbl where t2.i <= t.i)) from tbl
If there's no other column to sort then use a temp table with an identity
select i, id = identity(int,1,1) as id into #a from tbl
select i, sum((select t2.i from #a where t2.i < t.i or (t2.i = t.i and t2.id
<= t.id)))
from #a
"John Hilton" wrote:
> SQL Query,
>
> Col1 in Table1 has the following data.
>
> Col1
> 1
> 2
> 2
> 3
> 4
>
>
> IS it possible to run a query where I can the Col2 as sum from previous
> column for each row in col1?
>
> Col1
> Col2
> 1
> 1
> 2
> 3
> 2
> 5
> 3
> 8
> 4
> 12
>
>
> Thanks,
> Hilton
>
>|||Nigel Rivett, I really appricate your time in replying this post!!!
Since the formatting is lost in the post,
I am posting this again.
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
Is it possible to run a query where I can get the Col2 as sum from previous
column for each row in col1?
So the query result should be as follows.
Col2
1
3
5
8
12
Thanks,
Hilton|||Here's an alternative to Nigel's:
SELECT [ID] = IDENTITY(INT, 1, 1)
,Col1
INTO #Temp
FROM tbl
ORDER BY Col1 ASC
SELECT Col1 = AVG(t1.Col1)
Col2 = SUM(t2.Col1)
FROM #Temp AS t1
INNER JOIN
#Temp AS t2
ON t1.[ID] >= t2.[ID]
GROUP BY t1.[ID]
ORDER BY t1.[ID]
DROP #Temp
Sincerely,
Anthony Thomas
"John Hilton" <John_Hilton2004@.hotmail.com> wrote in message
news:%23Fw2HOx4EHA.1188@.tk2msftngp13.phx.gbl...
Nigel Rivett, I really appricate your time in replying this post!!!
Since the formatting is lost in the post,
I am posting this again.
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
Is it possible to run a query where I can get the Col2 as sum from previous
column for each row in col1?
So the query result should be as follows.
Col2
1
3
5
8
12
Thanks,
Hilton

Go and goto in one sql script gives error label not declared

Hi,

I have a problem:
I am writing an update script for a database and want to check for the
version and Goto the wright update script.

So I read the version from a table and if it match I want to "Goto
Versionxxx"

Where Versionxxx: is set in the script with the right update script.

Whenever I have some script which need Go commands I get error in the
output that

A GOTO statement references the label 'Versionxxx' but the label has
not been declared.

But the label is set in the script by 'Versionxxx:'

Is there a way I can solve this easily?

Thanks in advanceHere's the trick with "GO":

It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO')" in Query
Analyzer.)

Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.

So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.

-Dave Markle
http://www.markleconsulting.com/blog
BF wrote:

Quote:

Originally Posted by

Hi,
>
I have a problem:
I am writing an update script for a database and want to check for the
version and Goto the wright update script.
>
So I read the version from a table and if it match I want to "Goto
Versionxxx"
>
Where Versionxxx: is set in the script with the right update script.
>
Whenever I have some script which need Go commands I get error in the
output that
>
A GOTO statement references the label 'Versionxxx' but the label has
not been declared.
>
But the label is set in the script by 'Versionxxx:'
>
Is there a way I can solve this easily?
>
Thanks in advance

|||Thanks for the quick respond.

The solution is not quite what I was hoping for.

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.

For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.

When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

Grtx Bob

dmarkle schreef:

Quote:

Originally Posted by

Here's the trick with "GO":
>
It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO')" in Query
Analyzer.)
>
Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.
>
So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.
>
-Dave Markle
http://www.markleconsulting.com/blog
>

|||To be totally honest with you, I think the easiest/best way to solve
this would be to write a batch file that calls OSQL or SQLCMD against
the proper version of the file. Put your version-switching logic in
the batch file, and simply run OSQL on the appropriate files.

Some people execute their batches using sp_executesql, but it's really
messy and I don't really recommend it. Basically, using this method,
you'd be doing things like:

EXEC sp_executesql 'CREATE TABLE dbo.foo'
EXEC sp_executesql 'CREATE INDEX IX_xxx ON dbo.foo'
...

instead of:

CREATE TABLE dbo.foo
GO
CREATE INDEX IX_xxx ON dbo.foo
...

AFAIK, that's the only way to do what you want to do in 100% pure
T-SQL.

-Dave

BF wrote:

Quote:

Originally Posted by

Thanks for the quick respond.
>
The solution is not quite what I was hoping for.
>
For each new version I create an update script, We have an app which
does that and there are lots of Go commands.
>
I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.
>
For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.
>
When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.
>
Grtx Bob
>
dmarkle schreef:

Quote:

Originally Posted by

Here's the trick with "GO":

It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO')" in Query
Analyzer.)

Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.

So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.

-Dave Markle
http://www.markleconsulting.com/blog

|||BF (bob@.faessen.net) writes:

Quote:

Originally Posted by

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

No there isn't. There are a lot of GO separators.

Quote:

Originally Posted by

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.
>
For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.
>
When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

Right. The best way is to solve this is to write a little script runner that
reads a suite of files, and from the file names decudes which version the
file applies to, and then runs the file if needed. Your script would have to
break the script apart on the "go" separator, but this is trivial stuff.
(Hint: don't worry about "go" being entwined in comments ot string literals.
The standard query tools don't do that either. But care about leading and
trailing blanks, and inconsistent use of upper/lowercase.)

You can write this simple script runner in about any language - except for
T-SQK.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ok, great answers:

I build the updates with installshield 12 and I am no programmer so I
will go and try which will fit for me.

Probably I will place all scripts in the support dir from installshield
12 and run the from a vbscript with sqlcmd based on some tests.

I will try some things this week.

Thanks for the replies.

Grtx Bob

Erland Sommarskog schreef:

Quote:

Originally Posted by

BF (bob@.faessen.net) writes:

Quote:

Originally Posted by

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

>
No there isn't. There are a lot of GO separators.
>

Quote:

Originally Posted by

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.

For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.

When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

>
Right. The best way is to solve this is to write a little script runner that
reads a suite of files, and from the file names decudes which version the
file applies to, and then runs the file if needed. Your script would have to
break the script apart on the "go" separator, but this is trivial stuff.
(Hint: don't worry about "go" being entwined in comments ot string literals.
The standard query tools don't do that either. But care about leading and
trailing blanks, and inconsistent use of upper/lowercase.)
>
You can write this simple script runner in about any language - except for
T-SQK.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx