Here are my answers to the questions based on AdventureWorks. I recently updated the answers to work with the 2008R2 version of Adventureworks. If your answers are different than mine, please get in touch with me. There are always multiple ways of solving a problem, I need to make sure we’re on the same page,…
SQL101-Homework Assignment #2
SELECT with JOIN Here are a few more problems to practice with. Again, practice these queries against a copy of AdventureWorks. Now we’re going to start retrieving related data. An Employee is a Contact, but a Contact does not have to be an Employee. In adventureworks we store contacts in Person.Contact and we store employees…
SQL101-Homework Assignment #1 Answer Key
As promised, here are my answers to the first homework assignment. If you have something different, please get in touch with me so we can discuss the differences in our answers. There is always more than one way to come to the same result in SQL, but I want to make sure you have the…
SQL101-Homework Assignment #1
Here are some practice problems for you to attempt using what you’ve learned so far about SELECT Statements. Please write the following queries against any instance of Adventureworks. If you do not have access to a copy of this database, please contact me and I’ll get you credentials to connect to my training server. SELECT…
Duplicates
Duplicates. I honestly can’t tell you how many times the root cause of a problem has been records in a table get repeated, where there should never be duplicates. I’ve perfected my method of removing the duplicates, but when it comes time to prevent them in the future, developers scoff. Maybe it has to do…
Happy New Year!
I’d like to wish you all a happy new year! My goal for this year is to bring you all more SQL articles than before. I’d like to work up to at least one a week. Hopefully I can keep up with that. I’m looking forward to finishing my SQL 101 series. I’ve also been…
A Better sp_who2
If you’ve ever needed to work with sp_who2 to figure out what’s going on, then you know it would be nice to be able to sort the results, right? Well, if you use the following code, then you’ll get exactly that functionality. Enjoy! CREATE TABLE #who2 ( spid INT , [status] SYSNAME , [sid]…
Moving Data Between Tables
Very early into my SQL career I had to get comfortable moving records around between tables and databases. There are several ways to do it, you just have to get used to some of the requirements in moving the records. Moving records into a temporary table The first time you move records, you’ll probably need…
70-432:Multiple Solutions, Multiple Instances
At work our database server is humming along. It runs a little under 50% on the CPU, the RAM is a bit high, and the IO is very responsive. But, it could be better. About a quarter of the load on that server comes from one application. It’s a reporting solution built to handle end…
T-SQL Challenge: Find Days of the Week
My personal goal is to post an article every Monday, Wednesday and Friday. What I’d like to do is create stubs in my content database with each of those days already set aside. That way, I can look at the next stub available and simply start writing. The challenge for today is to create a…