Advanced SQL tutorials in Problem/Answer format

I'd like some challenging practice with SQL; general SQL-92 or MS specific (sql server or access).
There's a million sources on the Web, but most are like reference manuals. I already have MSDN with my VS 6.0 and .Net, as well as help files for 3 different versions of Access. I'm looking for something set up more like assignments or challenges. I found a couple sites like this:
SqlZoo.net and SQLCourse.com

SqlZoo has too many mistakes and vague questions. SQLCourse is pretty good, but no advanced stuff (some multiple Joins, subqueries, calculated fields and such). I like the idea of having the same related tables that you keep solving different problems with. This is more like a real-world situation. I would think that there would be a lot of question/answer tutorials for the MS supplied sample db's (Northwind, Pubs, etc.), but I haven't found any.

These forums are full of good questions posed by people, but they always concern unfamiliar data sources and I have to slog through the posts with my slow dial-up connection. I would rather do one big download and work at the problems offline.
Any suggestions?if you want the same related tables that you keep solving different problems with, your best bet is to search for the more common databases like northwind and sakila, perhaps by throwing several of their tables names into a search

if you want advanced question/answers, i humbly offer my own (the actual articles are on the techtarget.com site)

see http://r937.com/sqlate.cfm|||Thanks. There's a lot of good questions in one spot; I can easily download a bunch of those pages. Since the questions are all at the top of the pages, I can read them without seeing the answers. You helped save me a lot of searching time. If I do find some Northwind-based question tutes, I'll post the link.
Thanks again.|||Ahh, I find I learn more than enough from here :p
Just subscribe to SQL/Access threads that you are interested in.
I've knocked up a test database purely for solving problems on here -
trying to recreate peoples problems and see if I can solve them.

It's often a really good way to learn when you're given an unfamiliar problem, because you try and break it down into a generic solution (which means you can use it again later!).

There's my 2 cents. :cool:

advanced SQL tutorials

Dear All,
Can you suggest a good book or set of websites which have advanced SQL tutorials for SQL server? Got the basics but looking to build on my knowledge.
...cause knowledge is power...Here are some books

http://www.sqlteam.com/store.asp

And read every post here, and over at sql team.

Use Books online to try and figure out a posters question in Query Analyzer. Most often than not you will see someone answer the question.

Good Luck|||You might want to be a bit more specific. SQL Server has components that cover a broad range of topics. Are you a developer seeking to increase your knowledge of T-SQL and ADO? Are you a DBA seeking the best practices for securing your data and performance tuning? Are you a business analyst looking to learn MDX and OLAP?

Lots of sites to browse. Google is your friend.

Regards,

hmscott|||You might want to be a bit more specific. SQL Server has components that cover a broad range of topics. Are you a developer seeking to increase your knowledge of T-SQL and ADO? Are you a DBA seeking the best practices for securing your data and performance tuning? Are you a business analyst looking to learn MDX and OLAP?

Lots of sites to browse. Google is your friend.

Regards,

hmscott

Sorry for late reply and thanks for recomendations! Yes trying to learn more about complex queries with t-sql and db design and implementation for real life apps, had DB modules at Uni but it seems like hardly anything compared to the problems I face now in my job!

Advanced programming examples ?

Can anyone reccomend a book or online tutorials that shows quite
advanced SQL examples.
Most the sites and books I have seen for SQL only give small examples
from the pubs database designed for a few concurrent users. This is
fine for learning but what about real systems with 50+ concurrent users
and millions of records.
I'm looking for best practices and some examples showing heavy duty
stored procedures for high volume batch updates and inserts, triggers
for audit trails, best practice for transactions, efficient crosstab
queries etc.
Can anyone reccomend a website of book for this for SQL 2000.
Thanks
hals_leftThe following books have some great stored procedures and complex queries:
The Guru's guide to Transact-SQL
http://vyaskn.tripod.com/gurus_guid...ransact_sql.htm
Transact-SQL Cookbook
http://vyaskn.tripod.com/transact-sql_cookbook.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<cc900630@.ntu.ac.uk> wrote in message
news:1117094001.602864.285150@.f14g2000cwb.googlegroups.com...
Can anyone reccomend a book or online tutorials that shows quite
advanced SQL examples.
Most the sites and books I have seen for SQL only give small examples
from the pubs database designed for a few concurrent users. This is
fine for learning but what about real systems with 50+ concurrent users
and millions of records.
I'm looking for best practices and some examples showing heavy duty
stored procedures for high volume batch updates and inserts, triggers
for audit trails, best practice for transactions, efficient crosstab
queries etc.
Can anyone reccomend a website of book for this for SQL 2000.
Thanks
hals_left|||Thanks - the gurus guide looks exactly what im after|||Another good book for how to write efficient queries is:
Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook
by Ken England
<cc900630@.ntu.ac.uk> wrote in message
news:1117094001.602864.285150@.f14g2000cwb.googlegroups.com...
> Can anyone reccomend a book or online tutorials that shows quite
> advanced SQL examples.
> Most the sites and books I have seen for SQL only give small examples
> from the pubs database designed for a few concurrent users. This is
> fine for learning but what about real systems with 50+ concurrent users
> and millions of records.
> I'm looking for best practices and some examples showing heavy duty
> stored procedures for high volume batch updates and inserts, triggers
> for audit trails, best practice for transactions, efficient crosstab
> queries etc.
> Can anyone reccomend a website of book for this for SQL 2000.
> Thanks
> hals_left
>

advanced parameter tutorial, lesson 5, multipart identifier error

Hello,

Hope I'm asking this question in the correct forum.

I'm a newbie in Reporting Services and currently working my way through the tutorials with AdventureWorks. Came across this error while doing the MSDN tutorial for Advanced Features, lesson 5 - user defined functions.

http://msdn2.microsoft.com/en-us/library/aa337435.aspx

Created a new report, copied the following to the query screen:

SELECT udf.ContactID, udf.FirstName + N' ' + udf.LastName AS Name,
c.Phone, c.EmailAddress, udf.JobTitle, udf.ContactType
FROM ufnGetContactInformation(@.ContactID) udf
JOIN Person.Contact c ON ufn.ContactID = c.ContactID

I'm following the directions to the letter, and consistently get the following error:

"The multi-part identifier "ufn.ContactID" could not be bound."

"The multip-part identifier "ufn.ContactID" could not be bound. (Microsoft SQL Server, Error: 4104)"

I'm running SQL 2005 Enterprise on Windows XP.

Any help you can give will be much appreciated! Thank you.

Looks like typo in a sample query

try udf.ContactID instead of ufn.ContactID

|||Thank you very much! Now it works.

ADS Wizard, tutorials and examples - Please Help.

Hi,

I'm new to SQL Server Technology and am trying to learn how to create a Mobile Device application and to sync it with a desktop application that uses Access. I have found a lot of information but some of it doesn't seem to make sense.

I found this blog: http://blogs.msdn.com/sqlservereverywhere/archive/2006/08/29/729730.aspx which is the announcement of Access Database Synchronizer (ADS) CTP. I downloaded it and installed it with all the prerequisites. This is the part i can't understand:

"The CTP setup installs the desktop component required for synchronizing Microsoft Access database with SQL Server Everywhere Edition database on the device."

I cannot seem to find the desktop component to try out! The Readme shows you how to pull and push data but i can't see where the desktop data sync wizard is located. Should there be something installed on my desktop that i can see?

Also, there doesn't seem to be much help or examples around on this component as yet. Can anyone point me in the right direction for examples, help, or tutorials on this.

This paragraph also confuses me:

"Note: As of today only SQL Server Mobile Edition is available for devices. SQL Server Everywhere Edition whould be available for devices at time of SQL Server Everywhere Edition RTW whihc is planned for November 2006".

Does this mean SQL Everywhere won't work on mobile devices until November?

Sorry for all the questions but i'm still trying to learn this stuff so i can write my application for Windows CE. Thanks in advance.

Hi

Let me just give you a brief overview here. ADS is a solution which helps to synchronize a Access database on the desktop with a SQL Mobile/SQL Everywhere database on a device. There is no UI associated with the dektop component of ADS. The desktop component just listens for syncronization requests from a client (device with SQL MObile/SQL Everywhere) and performs the required actions.

Generally the applications written to utilize this soltuon would reside on the device. ADS Wizard is one such sample application we have provided along with source code to helpdevelopers write thier own applications.

If you have installed ADS at the default installation path, you will find the ADS wizard at the following path

C:\Program Files\Microsoft SQL Server Everywhere Edition\Access\Sample\AccessSync

Just open the .sln file in Visual Studio, build and deploy the solution on a PPC device and you are ready to go. Please read the "redme.htm" for more details

Hope this helps. If you have any more questions let me know

Regards

Manish

|||

Hi,

Thanks for your reply. I have got the sample working now with an emulator and can sync a test.sdf database with an access database on my desktop. So far so good!

I have another question though....Is it possible to create an application on the desktop to do the same thing or can this only run from the device? I want to create an application on the desktop that can read the SDF file on the device and sync it with the access database on my desktop. The reason for this is that the user must have options to select certain criteria of data to put onto the device. I may need to extract the selected data from my main Access database to a temporary database then push that to the device. Then when the user has used the device for the day, they just cradle it then run this application which syncs the data back to the temporary table. Then the application copies this data back to the main database as it needs to update certain fields, not just synchronize the entire database.

Does this sound possible? I have tried connection strings like below but it doesn't seem to work.

Dim conStringDevice As String = ("Data Source=Mobile Device\Test123.sdf")

m_connDevice.Open()

I just get an error: "The Path is not valid...Path = Mobile Device\Test123.sdf"

The device is connected and cradled and ActiveSync is running. I can use VS2005 to connect to this database no problems. Can i do this programmatically?

Thanks

|||

"Mobile Device" is only for Designers to differentiate between device and desktop connection. The runtime bits does not really understand this. To simply say, try removing "Mobile Device" Prefix.

Thanks,

Laxmi

|||

I tried removing "Mobile Device" and it still doesn't work. Surely there needs to be some reference to where the file is stored?

The ADS sample makes reference to the access database stored on "C:\" and the SQL database as just "Test123.sdf". But this is run from the Mobile device and picks up the local file. If run from the desktop, how can i reference to the mobile device file "Test123.sdf"?

|||Ok to put this simply....Can an ADS application be created to run on a dekstop to pull and push data from and to the mobile device? As i stated before the sample runs on the device itself, but i want to write an application to run on the desktop. Can someone please help?|||

"The_Nod", did you ever find any resolution to this problem? I am in the exact same place you are/were right now? I'm trying to control the sync from a desktop app as well.

|||

Hi mhawb,

No unfortunately. I was writing an application for Windows mobile 5.0 which was to synchronize with a desktop application that is still using an access database but i stopped doing it all together. I found problems with synchronizing as the field type were a lot different than the Access database and thought it couldn't be done that way.

I was going down the path of actually copying the SDF file from the mobile device to the desktop (as you cant read the SDF file directly from the desktop), then writing an application that manually updated the data to the Access Database. I found something called OpenNETCF which uses RAPI to copy the SQL databases to and from the desktop device. Here is a link to RAPI on MSDN: http://msdn2.microsoft.com/en-US/library/ms837846.aspx

Hope this helps. I stopped doing this project as i couldn't find the time. I may get back into it soon so if anyone has any better ideas and any ADS examples that would be great.

ADS Wizard, tutorials and examples - Please Help.

Hi,

I'm new to SQL Server Technology and am trying to learn how to create a Mobile Device application and to sync it with a desktop application that uses Access. I have found a lot of information but some of it doesn't seem to make sense.

I found this blog: http://blogs.msdn.com/sqlservereverywhere/archive/2006/08/29/729730.aspx which is the announcement of Access Database Synchronizer (ADS) CTP. I downloaded it and installed it with all the prerequisites. This is the part i can't understand:

"The CTP setup installs the desktop component required for synchronizing Microsoft Access database with SQL Server Everywhere Edition database on the device."

I cannot seem to find the desktop component to try out! The Readme shows you how to pull and push data but i can't see where the desktop data sync wizard is located. Should there be something installed on my desktop that i can see?

Also, there doesn't seem to be much help or examples around on this component as yet. Can anyone point me in the right direction for examples, help, or tutorials on this.

This paragraph also confuses me:

"Note: As of today only SQL Server Mobile Edition is available for devices. SQL Server Everywhere Edition whould be available for devices at time of SQL Server Everywhere Edition RTW whihc is planned for November 2006".

Does this mean SQL Everywhere won't work on mobile devices until November?

Sorry for all the questions but i'm still trying to learn this stuff so i can write my application for Windows CE. Thanks in advance.

Hi

Let me just give you a brief overview here. ADS is a solution which helps to synchronize a Access database on the desktop with a SQL Mobile/SQL Everywhere database on a device. There is no UI associated with the dektop component of ADS. The desktop component just listens for syncronization requests from a client (device with SQL MObile/SQL Everywhere) and performs the required actions.

Generally the applications written to utilize this soltuon would reside on the device. ADS Wizard is one such sample application we have provided along with source code to helpdevelopers write thier own applications.

If you have installed ADS at the default installation path, you will find the ADS wizard at the following path

C:\Program Files\Microsoft SQL Server Everywhere Edition\Access\Sample\AccessSync

Just open the .sln file in Visual Studio, build and deploy the solution on a PPC device and you are ready to go. Please read the "redme.htm" for more details

Hope this helps. If you have any more questions let me know

Regards

Manish

|||

Hi,

Thanks for your reply. I have got the sample working now with an emulator and can sync a test.sdf database with an access database on my desktop. So far so good!

I have another question though....Is it possible to create an application on the desktop to do the same thing or can this only run from the device? I want to create an application on the desktop that can read the SDF file on the device and sync it with the access database on my desktop. The reason for this is that the user must have options to select certain criteria of data to put onto the device. I may need to extract the selected data from my main Access database to a temporary database then push that to the device. Then when the user has used the device for the day, they just cradle it then run this application which syncs the data back to the temporary table. Then the application copies this data back to the main database as it needs to update certain fields, not just synchronize the entire database.

Does this sound possible? I have tried connection strings like below but it doesn't seem to work.

Dim conStringDevice As String = ("Data Source=Mobile Device\Test123.sdf")

m_connDevice.Open()

I just get an error: "The Path is not valid...Path = Mobile Device\Test123.sdf"

The device is connected and cradled and ActiveSync is running. I can use VS2005 to connect to this database no problems. Can i do this programmatically?

Thanks

|||

"Mobile Device" is only for Designers to differentiate between device and desktop connection. The runtime bits does not really understand this. To simply say, try removing "Mobile Device" Prefix.

Thanks,

Laxmi

|||

I tried removing "Mobile Device" and it still doesn't work. Surely there needs to be some reference to where the file is stored?

The ADS sample makes reference to the access database stored on "C:\" and the SQL database as just "Test123.sdf". But this is run from the Mobile device and picks up the local file. If run from the desktop, how can i reference to the mobile device file "Test123.sdf"?

|||Ok to put this simply....Can an ADS application be created to run on a dekstop to pull and push data from and to the mobile device? As i stated before the sample runs on the device itself, but i want to write an application to run on the desktop. Can someone please help?|||

"The_Nod", did you ever find any resolution to this problem? I am in the exact same place you are/were right now? I'm trying to control the sync from a desktop app as well.

|||

Hi mhawb,

No unfortunately. I was writing an application for Windows mobile 5.0 which was to synchronize with a desktop application that is still using an access database but i stopped doing it all together. I found problems with synchronizing as the field type were a lot different than the Access database and thought it couldn't be done that way.

I was going down the path of actually copying the SDF file from the mobile device to the desktop (as you cant read the SDF file directly from the desktop), then writing an application that manually updated the data to the Access Database. I found something called OpenNETCF which uses RAPI to copy the SQL databases to and from the desktop device. Here is a link to RAPI on MSDN: http://msdn2.microsoft.com/en-US/library/ms837846.aspx

Hope this helps. I stopped doing this project as i couldn't find the time. I may get back into it soon so if anyone has any better ideas and any ADS examples that would be great.

ADS Wizard, tutorials and examples - Please Help.

Hi,

I'm new to SQL Server Technology and am trying to learn how to create a Mobile Device application and to sync it with a desktop application that uses Access. I have found a lot of information but some of it doesn't seem to make sense.

I found this blog: http://blogs.msdn.com/sqlservereverywhere/archive/2006/08/29/729730.aspx which is the announcement of Access Database Synchronizer (ADS) CTP. I downloaded it and installed it with all the prerequisites. This is the part i can't understand:

"The CTP setup installs the desktop component required for synchronizing Microsoft Access database with SQL Server Everywhere Edition database on the device."

I cannot seem to find the desktop component to try out! The Readme shows you how to pull and push data but i can't see where the desktop data sync wizard is located. Should there be something installed on my desktop that i can see?

Also, there doesn't seem to be much help or examples around on this component as yet. Can anyone point me in the right direction for examples, help, or tutorials on this.

This paragraph also confuses me:

"Note: As of today only SQL Server Mobile Edition is available for devices. SQL Server Everywhere Edition whould be available for devices at time of SQL Server Everywhere Edition RTW whihc is planned for November 2006".

Does this mean SQL Everywhere won't work on mobile devices until November?

Sorry for all the questions but i'm still trying to learn this stuff so i can write my application for Windows CE. Thanks in advance.

Hi

Let me just give you a brief overview here. ADS is a solution which helps to synchronize a Access database on the desktop with a SQL Mobile/SQL Everywhere database on a device. There is no UI associated with the dektop component of ADS. The desktop component just listens for syncronization requests from a client (device with SQL MObile/SQL Everywhere) and performs the required actions.

Generally the applications written to utilize this soltuon would reside on the device. ADS Wizard is one such sample application we have provided along with source code to helpdevelopers write thier own applications.

If you have installed ADS at the default installation path, you will find the ADS wizard at the following path

C:\Program Files\Microsoft SQL Server Everywhere Edition\Access\Sample\AccessSync

Just open the .sln file in Visual Studio, build and deploy the solution on a PPC device and you are ready to go. Please read the "redme.htm" for more details

Hope this helps. If you have any more questions let me know

Regards

Manish

|||

Hi,

Thanks for your reply. I have got the sample working now with an emulator and can sync a test.sdf database with an access database on my desktop. So far so good!

I have another question though....Is it possible to create an application on the desktop to do the same thing or can this only run from the device? I want to create an application on the desktop that can read the SDF file on the device and sync it with the access database on my desktop. The reason for this is that the user must have options to select certain criteria of data to put onto the device. I may need to extract the selected data from my main Access database to a temporary database then push that to the device. Then when the user has used the device for the day, they just cradle it then run this application which syncs the data back to the temporary table. Then the application copies this data back to the main database as it needs to update certain fields, not just synchronize the entire database.

Does this sound possible? I have tried connection strings like below but it doesn't seem to work.

Dim conStringDevice As String = ("Data Source=Mobile Device\Test123.sdf")

m_connDevice.Open()

I just get an error: "The Path is not valid...Path = Mobile Device\Test123.sdf"

The device is connected and cradled and ActiveSync is running. I can use VS2005 to connect to this database no problems. Can i do this programmatically?

Thanks

|||

"Mobile Device" is only for Designers to differentiate between device and desktop connection. The runtime bits does not really understand this. To simply say, try removing "Mobile Device" Prefix.

Thanks,

Laxmi

|||

I tried removing "Mobile Device" and it still doesn't work. Surely there needs to be some reference to where the file is stored?

The ADS sample makes reference to the access database stored on "C:\" and the SQL database as just "Test123.sdf". But this is run from the Mobile device and picks up the local file. If run from the desktop, how can i reference to the mobile device file "Test123.sdf"?

|||Ok to put this simply....Can an ADS application be created to run on a dekstop to pull and push data from and to the mobile device? As i stated before the sample runs on the device itself, but i want to write an application to run on the desktop. Can someone please help?|||

"The_Nod", did you ever find any resolution to this problem? I am in the exact same place you are/were right now? I'm trying to control the sync from a desktop app as well.

|||

Hi mhawb,

No unfortunately. I was writing an application for Windows mobile 5.0 which was to synchronize with a desktop application that is still using an access database but i stopped doing it all together. I found problems with synchronizing as the field type were a lot different than the Access database and thought it couldn't be done that way.

I was going down the path of actually copying the SDF file from the mobile device to the desktop (as you cant read the SDF file directly from the desktop), then writing an application that manually updated the data to the Access Database. I found something called OpenNETCF which uses RAPI to copy the SQL databases to and from the desktop device. Here is a link to RAPI on MSDN: http://msdn2.microsoft.com/en-US/library/ms837846.aspx

Hope this helps. I stopped doing this project as i couldn't find the time. I may get back into it soon so if anyone has any better ideas and any ADS examples that would be great.