Learning SQL. Guides, etc?

[Mageling]

I've been at my current job for almost a year now, and we use SQL databases for most everything, but do very little work perosnally with them. I've found that there are some job openings in the near future for people with a bit more SQL experience and wanted to learn more about it for a possible promotion to a new department.

Any ideas on places to really make headway with understanding and learning SQL?

Links that I've come across so far:

http://sqlzoo.net/

Deep Thought: Basic Database Design

While I feel like I understand database structure and design from a high level, I find myself struggling to create more than your very basic queries and update statements.

Have there been any places that people have seen walkthroughs in regards to joining tables and creating more advanced queries and really working up from there.

Any assistance would be great. Hopefully there are a few DB geeks out there.

[Maio]

For SQL:
SQL Tutorial

Database design depends on what your team prefers. Developers love simple databases (so they can create simple queries) while database administrators go nuts on functional dependencies and what not. It depends on wether you are concerned about easier human interaction with the database or performance/optimization. Good luck.

[Jedite]

Originally Posted by Mageling

I've been at my current job for almost a year now, and we use SQL databases for most everything, but do very little work perosnally with them. I've found that there are some job openings in the near future for people with a bit more SQL experience and wanted to learn more about it for a possible promotion to a new department.

Any ideas on places to really make headway with understanding and learning SQL?

Links that I've come across so far:

http://sqlzoo.net/

Deep Thought: Basic Database Design


Any assistance would be great. Hopefully there are a few DB geeks out there.

I assume you have some programming experience correct? What language??

If you know C# I found a nice little book that throws you into the mix sort of speak as far as SQL and interacting with an SQL DB through C#. Its just a beginners guide but it helped me a lot as far as getting my feet wet with SQL and writting a few applications for my previous employer
While I feel like I understand database structure and design from a high level, I find myself struggling to create more than your very basic queries and update statements.

Have there been any places that people have seen walkthroughs in regards to joining tables and creating more advanced queries and really working up from there.

[Rhuobhe]

buy any decent book, look for something simple and easy to understand

learn the basics

get some job experience at it

don't count on that promotion. but hey who knows

the important thing is to learn so you can apply at the your future higher salary job

[Mageling]

I think that was the motivation to learn. Not exactly to pick up this current opportunity, but to put myself in a position where I can learn more through job experience where I am currently. We have the ability to work with test databases here at work, and I was hoping that by learning a bit more about SQL, would give me either a chance when a position comes along like this in the future, or expands my ability to sell myself to another employer sometime in the future.

I really don't expect to be considered for the job opening as it may have sounded in the original post, but instead it sparked my interest in finding out more about SQL and seeing where that takes me.

Jedite: You mentioned a book in your post, do you have the name of it perhaps? I'm pretty interested in starting fresh and learning from the ground up.

Thanks!

[Zippygoose]

I have and would recommend:

Amazon.com: Professional SQL Server 2005 Programming (Programmer to Programmer): Books: Robert Vieira

I've always been a fan of the Programmer to Programmer books.

[Zippygoose]

If you really really wanted to get hardcore about it, you could also look into a MCITP: Database Administrator cert (assuming you are on the Microsoft platform).

Even if you don't want to go that far but would like to get some very very in-depth knowledge you could check out these books:

Microsoft® SQL Server™ 2005: Applied Techniques Step by Step

Microsoft® SQL Server™ 2005: Database Essentials Step by Step

MCTS Self-Paced Training Kit (Exam 70-431): Microsoft® SQL Server™ 2005—Implementation and Maintenance

MCITP: Database Administrator web site:

MCITP: Database Administrator

That may be biting off a bit more than anyone would like to chew though

[tikkus]

I've been working with SQL Server 2k/2k5 in my current job and, having read a VB.NET Databases book prior, I'd have to say that actual experience counts a lot more than anything you could read.

Its been a rocky process but I've written so many stored procedures now that I feel like I'm turning into a SQL pro. :P

[Niceshot23]

SQL is easy, once you learn the basics, picking up new syntax and replacing old methods is a breeze.....

a few of my notes:

- temp tables are more efficient then declarative tables
- use minimal amount of cursors
- don't join in the where clause (meaning select a.*, b.* from table a table b where a.field = b.field) and instead, use microsoft standard as (select a.*, b.* from table a [inner / left outer] join table b on a.field = b.field)
- to reduce the amount of deadlocks, use "with (nolock)" on your joins (i.e. "select a.*, b.* from table a with(nolock) [inner / left outer] join table b with(nolock) on a.field = b.field"
- in the begining of your stored procs, insert this line "SET NOCOUNT ON", it removes the messages like "1 records affected" ... if you're using these stored procs for most reporting tools, it can cause problems
- avoid using the distinct statement as much as possible .... it slows down the query/proc .... most of the time, its just incorrect joining of the tables if you're having duplicate results

[Vithe]

W3Schools Online Web Tutorials has some decent tutorials for beginners etc for most anything you want to get your hands wet in. I used it for ASP and thought it was pretty good.

[lendarios]

Originally Posted by Zippygoose

I have and would recommend:

Amazon.com: Professional SQL Server 2005 Programming (Programmer to Programmer): Books: Robert Vieira

I've always been a fan of the Programmer to Programmer books.

I read that book and its garbage. I am very disapointed of Wrox as a publisher.

Go with an APress book. They go more into the details of how things happen. Wroxs book only say "bla bla its beyond our scope.."

[lendarios]

Originally Posted by Niceshot23

SQL is easy, once you learn the basics, picking up new syntax and replacing old methods is a breeze.....

a few of my notes:

- temp tables are more efficient then declarative tables
- use minimal amount of cursors
- don't join in the where clause (meaning select a.*, b.* from table a table b where a.field = b.field) and instead, use microsoft standard as (select a.*, b.* from table a [inner / left outer] join table b on a.field = b.field)
- to reduce the amount of deadlocks, use "with (nolock)" on your joins (i.e. "select a.*, b.* from table a with(nolock) [inner / left outer] join table b with(nolock) on a.field = b.field"
- in the begining of your stored procs, insert this line "SET NOCOUNT ON", it removes the messages like "1 records affected" ... if you're using these stored procs for most reporting tools, it can cause problems
- avoid using the distinct statement as much as possible .... it slows down the query/proc .... most of the time, its just incorrect joining of the tables if you're having duplicate results

avoid Select * from ... Know exactly where your data is. getting all the columns is ineficient.

avoid using Select top (x). no need to have sql rearrange the data once you have fetched it, if you need a sample use tablesample.

Views are slower than store procedures. DBA like views because they shield the underliyng table structure, but then a simple select from one table, becomes a series of chained selects harming performance.

Know your @@IDENTITY vs SCOPE_IDENTITY() functions, knowing the difference between those two landed me one of my previous jobs.

Dont rely on cursors, they break Sql set programing into iteration programing.

Learn by heart, the differences between data types.
bit, int, char, nchar, varchar, nvarchar, small int, datetime, smalldatetime, decimal, money, etc.
Learn what are indexes ( the two types) and how you can use them to enhance performance.
Learn what are keys, foreign and primaries. Learn what are constrains and triggers ( the two types).

[lendarios]

Originally Posted by Zippygoose

I have and would recommend:

Amazon.com: Professional SQL Server 2005 Programming (Programmer to Programmer): Books: Robert Vieira

I've always been a fan of the Programmer to Programmer books.

Also that book is aimed at programmers with experience.
Dont buy it if you dont know t-sql ( the book only has 1-2 chapters on it.... )

[Zippygoose]

Originally Posted by lendarios

Also that book is aimed at programmers with experience.
Dont buy it if you dont know t-sql ( the book only has 1-2 chapters on it.... )

He knows t-sql and seems to be a programmer with some experience...