In all the previous articles I’ve written on SQL I’ve showed you how to get data out of the database. Now, we’re switching gears. I’m going to show you how to put data into the database. The command to put data into the database is INSERT. There are four main ways to use this command,…
Category: Microsoft SQL
SQL 101 – UPDATE
Previously, I showed you how to get new information into a SQL database. This time I’ll show you how to make changes to that data, once it’s in there. The basic structure of the change command is: UPDATE tableName SET columnName = ‘value’ WHERE <some test> I can’t tell you how important that WHERE clause…
Comparing Query Performance
A significant portion of your time as a database programmer or administrator will be to improve the performance of queries. While there are several benchmarks you can compare two or more queries, the actual comparision of the two queries can lean you do make false assumptions. When you run a query, data and execution plans…
SQL101-Homework Assignment #2 Answer Key
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…
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…