Time to learn!
After helping others learn SQL for several years now, I keep coming back to the same topics over and over. So, I decided to write down these SQL tutorials, and continually improve them over the years. What I’ve been able to collect here is just the introduction.
This series is an introduction to Microsoft SQL database programming. You’ll need to have a good grip on these concepts before the other classes will be as useful to you. I plan to create several more classes in addition to this class.
- x01 series will be database programming
- x02 series will be database administration
- x03 series will be database architecture
- x04 series will be about data mining and analysis
I may add in more series as I go, but for now, all the stuff I’ve taught fits easily into one of these categories. Let me tell you about the lesson plan.
Syllabus
- What is a database — Introduction, vocabulary, and basic concepts
- SELECT — Getting data out of your database
- Filtering Results — limiting the number of results you get
- Filtering Results (Part 2)
- Advanced LIKE Clauses — an introduction to pattern matching strings in Microsoft SQL
- Calculated Fields — Concatenating and performing mathematics
- Summarizing Data — counting, finding the maximum and minimum values
- GROUP BY — grouping together similar data
- Sub Queries — using one query as a value in another
- JOIN — using the relationship part of relational database system
- INSERT — putting data into your database
- UPDATE — making changes to your data
- TRANSACTION — as good as a bullet-proof vest!
- DELETE — removing data from your database
These articles are here to help you understand Microsoft SQL. If you have any questions, comments, or concerns, please let me know. I can only teach you what you want to learn, if you let me know what that is!
Let’s Get Started >
As I create more SQL 101 articles, they will appear below.
- SQL 101 Posted on: 20050601
After helping others learn SQL for several years now, I keep coming back to the same topics over and over. So, I decided to write down these SQL tutorials, and continually improve them over the years. What I’ve been able to collect here is just the introduction. This series is an introduction to Microsoft [...] - SQL 101 - What is a Database Posted on: 20050601
The term database is used for many different meanings. But if you compared them all, I think you would find that databases are all collections of data that are organized in some way. I usually like to refer to an Excel document when I try to explain databases. An excel file has a collection [...] - SQL 101 - What is SQL? Posted on: 20050615
SQL, pronounced S – Q – L or “sequel”, stands for structured query language. It’s the language you use to tell a database what you want to do. It’s been around since the early 1970s[1], so it’s pretty well established. One of the most awesome things about learning SQL is once you learn it, [...] - SQL 101 - SELECT Posted on: 20050702
The first thing you need to know when learning SQL is how to get data out of a database. This means learning the SELECT command. Using this command will get the SQL server to return data to you. You can use this command to do some simple math, or to do the common “Hello [...] - SQL 101 - SELECT, Filtering Results Posted on: 20050730
After you learn how to get data out of a table with the SELECT command, you’ll soon ask the question, how do I limit the number of results I get back. Well, If you don’t ask the question, your DBA will. I don’t think he or she would like it if you only ever [...] - SQL 101 – SELECT, Filtering Results (Part 2) Posted on: 20050810
In the previous post, I covered the WHERE clause. You should now feel pretty comfortable limiting the number of rows you get to return based on the values in a column. But I’m sure you’ve already asked “How can I limit based on two different columns?” I’m glad you asked! You can chain together [...] - SQL 101 - The Advanced LIKE Clauses Posted on: 20050820
As I promised, this is my post on advanced LIKE clauses. Previously I’ve only shown you how to do a wildcard search that would act like the dos command “dir A*.*”, returning all the files that start with the letter A. But there is far more you can do with the LIKE operator. Wildcards [...] - SQL 101 - Calculated Fields Posted on: 20050905
When working with SQL you will inevitably be asked to return data from your database in a way it’s not stored. You’ll be asked to return city, state and zip as a single string, yet they are stored separately. You’ll be asked to return grand totals, but you only have line item totals. You’ll [...] - SQL 101 – Date Manipulation Functions Posted on: 20050925
Welcome back! This post is a followup to the post about string functions. The difference is this time, we’re discussing functions that manipulate dates. GETDATE The most often used function I can think of, with the possible exception of COUNT(*) is GETDATE(). When called this gives you the current datestamp from the server. This [...] - SQL 101 - System Functions Posted on: 20051009
There are many functions built in to SQL 2000. I’m not going to cover them all, since many of them you won’t have a need for until you really dig in deep with SQL programming. But there are seven you should really be familiar with. CASE You have no idea how much you’ll use [...] - SQL 101 - Summarizing Data Posted on: 20051020
This is the last of the lessons on functions for the 100 level. I hope you’ve enjoyed them so far, but it’s time to wrap these up! When beginning to learn SQL, it won’t be long until you have to answer questions that require you to summarize the data. It’s one of the primary [...] - SQL 101 - GROUP BY Posted on: 20051108
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 [...] - SQL 101 - JOIN Posted on: 20051202
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 [...] - SQL101-Homework Assignment #1 Posted on: 20060104
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. [...] - SQL101-Homework Assignment #1 Answer Key Posted on: 20060106
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 [...] - SQL101-Homework Assignment #2 Posted on: 20060109
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 [...] - SQL101-Homework Assignment #2 Answer Key Posted on: 20060111
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 [...] - SQL 101 - UPDATE Posted on: 20060201
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 [...] - SQL 101 - INSERT Posted on: 20060215
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 [...] - SQL101-Homework Assignment #3 Posted on: 20060217
1. Write the query that would put the full name (as much of it as you know) of your favorite Star Wars character into the Person.Contact table. Use the title field to identify them as ‘SWChar’. You can use the INSERT that uses VALUES or SELECT to do this, again, extra points if you [...] - SQL101-Homework Assignment #3 Answer Key Posted on: 20060219
Here are the answers to the previous practice problems. 1. Write the query that would put the full name (as much of it as you know) of your favorite Star Wars character into the Person.Contact table. Use the title field to identify them as ‘SWChar’. You can use the INSERT that uses VALUES or [...] - SQL 101 - TRANSACTION Posted on: 20060301
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 [...] - SQL 101 – DELETE Posted on: 20060315
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 [...] - SQL101-Homework Assignment #4 Posted on: 20060317
Here are some more practice problems. This time you’re going to practice using DELETE. 1. Write a DELETE query that would remove a record from person.contact by contactID, pick a contactID that does not exist in person.contact. 2. Write a DELETE query that would remove a record from person.contact by first and last name. [...] - SQL101-Homework Assignment #4 Answer Key Posted on: 20060320
Here are my solutions updated to work with AdventureWorks on 2008R2. 1. Write a DELETE query that would remove a record from person.contact by contactID, pick a contactID that does not exist in person.contact. DELETE FROM Person.Contact WHERE ContactID = -1 2. Write a DELETE query that would remove a record from person.contact by [...] - SQL101-Homework Assignment #5 Posted on: 20060322
Here’s another set of questions for you to practice on. As always, if you have any questions, let me know and I’ll help you. UPDATE 1. Write an UPDATE query that would set your middle name, for only your record by personID. (if there is no record for you, add it in first, then [...] - SQL101-Homework Assignment #5 Answer Key Posted on: 20060324
These answers have been updated to work with Adventureworks on 2008R2. 1. Write an UPDATE query that would set your middle name, for only your record by personID. (if there is no record for you, add it in first, then show me the update to set your middle name.) UPDATE Person.Contact SET MiddleName = [...] - Alias Posted on: 20070301
Within a few days of using SQL, you’re going to start wondering if there is a way to speed up your code writing. The good news is you can do that with Aliases. You can give a table or a column another name by using an alias. This can be a good thing to [...] - SQL 101 Training Posted on: 20101115
Wednesday November 24th, from 5 – 7pm I’ll be offering my SQL 101 course. I was thinking about adding a remote option for those of you who can’t make it up to Concord. I’d like to know how many of you there will be so I can arrange GoToMeeting, or something similar so you [...] - SQL 101 — the 11/24 presentation Posted on: 20101129
Wednesday night I had the opportunity to give my SQL 101, the basics presentation. I hadn’t given this one since just before leaving IntegrationPoint earlier this year. I spent a little time updating the presentation based on past evaluations and some changes in SQL. My new version includes some newer data types that have [...]
