It looks like I’m getting close to the end of my 101 series. After I show you how to remove data from tables, the last topic I plan to cover is creating tables. It’s been quite a journey, collecting all this knowledge and putting it out there for you. I had hoped to get some…
Tag: T-SQL
SQL 101 – TRANSACTION
As I mentioned in my previous post, before you move on to more difficult topics I need to share with you what a TRANSACTION is, and how to use them. By now I’m sure you’ve seen t-SQL on tutorials. The T stands for transactional. If you don’t know what a TRANSACTION is, that’s pretty meaningless,…
SQL 101 – INSERT
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,…
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…
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…
SQL 201 – Combining Query Results
Overview There comes a point in your SQL career where you will need to combine the results of several queries into one data set, and present it in one view. When you get to this point, you’ll need to be familiar with the UNION command. With Microsoft SQL 2005, there are two new functions, similar…
SQL 101 – JOIN
Overview I want to cover a fundamental topic for SQL programming. This is the topic you should start studying right after you have a basic understanding of what a relational database, what tables are, and that tables can be related to each other. If you’re a fan of Duct tape, then Joins will become the…
SQL 201 – Sub Queries
Sub queries or nested queries are complete queries wrapped inside of another complete query. Some times this technique is used as a replacement for a JOIN statement. Some times it’s the only way to define exactly what you you want to get from a database. The important thing to learn about sub queries is they…
SQL 101 – GROUP BY
Consider the following table, orders. buyer productName purchaseDate qtyPurchased pricePaid ————- ————— ————— ———– ——— Shannon Lowder pencil 1/1/2000 1 .25 Shannon Lowder paper 1/1/2000 2 1.00 Shannon Lowder Mountain Dew 1/1/2000 1 1.25 Shannon Lowder pencil 1/5/2000 1 …