Wise Owl's Blog
Making Sense of Software
This article provides a comprehensive tutorial in Microsoft SQL Server, taking you from the basics of writing SELECT statements all the way through to creating complex stored procedures.
Welcome to the Wise Owl online SQL training course. This free tutorial will teach you how to write queries, user-defined functions and stored procedures in Microsoft SQL Server. Items marked with an asterisk (*) haven't been written yet but we'll add more articles over time (at a rate of approximately one-per-week).
The topics are listed in order of difficulty, so if you've never written a SQL query before you should probably start from the very beginning. If you've been using SQL Server for a while now you should find you'll be able to dip in to later topics without having to go through the basics first.
Enjoy!
The articles in this section aren't essential in order to successfully write queries in SQL, but you might find some of the information interesting nonetheless.
| Tutorial | Learn how to... |
|---|---|
| Designing databases | Design a relational database and discover the most efficient way to store complex information. |
| Introduction to SQL Server Management Studio | Open the Management Studio application, connect to a server and navigate around the screen. |
| Useful Settings in Management Studio | Change some default settings in SQL Server Management Studio to make writing queries just a little easier. |
| Creating a database using SQL Server* | Use SQL Server Management Studio to create tables, choose data types, create relationships and draw database diagrams. |
This section explains the basics of writing queries in SQL using the SQL Server Management Studio application.
| Tutorial | Learn how to... |
|---|---|
| Simple queries* | Write basic queries using the SELECT and FROM keywords, including how to show unique entries in a table. |
| Sorting query results* | Change the order of your query results using the ORDER BY clause in a statement, including how to show just the top ten (or five or seventeen, etc) items in a list. |
| Using criteria* | Use the WHERE clause to add criteria to a query, including how to use wildcards. |
Many people's least favourite part of writing queries is remembering the code needed to join tables together. Unfortunately, you'll need to know this if you ever want to use more than one table in a query!
| Tutorial | Learn how to... |
|---|---|
| Joins* | Write join code to run a query on two (or more) tables at the same time, including the difference between inner and outer joins. |
| Exotic joins* | Create more advanced types of join, including cross joins and self joins. |
You'll encounter calculations, or expressions, almost everywhere in SQL. The articles in this section explain everything from simple arithmetic to using complex sequences of nested functions.
| Tutorial | Learn how to... |
|---|---|
| Calculated fields* | Write simple calculations in queries, including how to use the CASE statement. |
| Using functions* | Use the built-in functions in SQL Server to perform more elaborate calculations. |
Dates can be one of the most frustrating things to work with in SQL Server, so we thought they deserved a section all to themselves!
| Tutorial | Learn how to... |
|---|---|
| Dates and times in SQL | Write criteria and perform calculations using dates and times in SQL Server. |
| Calculating age | Accurately perform one of the most common date calculations in SQL Server. |
This section teaches you how to group records together in order to calculate aggregate statistics such as the sum and average of a range of numbers.
| Tutorial | Learn how to... |
|---|---|
| Grouping and aggregating* | Group rows of data together and create basic aggregate statistics on the values in each group. |
You can create a view in order to avoid having to write a query in SQL. There are a few disadvantages to doing this however, as the articles in this section will explain.
| Tutorial | Learn how to... |
|---|---|
| Views in SQL Server* | Create basic views and execute them to see the results. |
One major disadvantage of using SQL Server Management studio is that it gives you no way to format the results of your queries. This section explains how you can get your query results into another application so that you can make them look pretty!
| Tutorial | Learn how to... |
|---|---|
| Copying and saving query results* | Copy the results of a query into another application, or save the results into a separate file. |
| Importing SQL data* | Use a wizard to get data from a SQL Server database into another application. |
| Using VBA to get SQL data | Write VBA code to link to a SQL Server database. |
A subquery allows you to nest one query inside another allowing you to do all sorts of interesting things. You'll have to read the articles to find out what they are!
| Tutorial | Learn how to... |
|---|---|
| Subqueries* | Write subqueries in SQL Server queries. |
The articles in this section explain some of the fundamental concepts you'll need to get the most out of programming in SQL. Read these articles to prepare yourself for creating stored procedures.
| Tutorial | Learn how to... |
|---|---|
| Variables* | Store information in memory while a program is running. You'll need variables to do anything non-trivial in SQL programming. |
| IF statements* | Use IF to control program flow based on conditional statements. |
| Loops* | Repeat a set of instructions multiple times. You don't use loops often in SQL, but they're still worth learning. |
| Error handling* | Trap errors while your SQL code is running. |
| Debugging* | Find the bugs in your code and stamp them out. |
A stored procedure is a set of SQL instructions stored in a database. This section is designed to get you up to speed with the basics of creating stored procedures.
| Tutorial | Learn how to... |
|---|---|
| Stored procedure basics* | Create simple stored procedures, including passing values to parameters. |
| Returning values from stored procedures* | Get values out of stored procedures using output parameters and return codes. |
User defined functions are a great way to avoid creating the same calculation over and over again in your queries.
| Tutorial | Learn how to... |
|---|---|
| Creating user-defined functions* | Write user-defined functions to save time with complex calculations in SQL Server. |
One of the first articles in this tutorial explained how to create and work with tables using SQL Server Management Studio. This section explains how to do the same things purely in SQL code!
| Tutorial | Learn how to... |
|---|---|
| Creating tables in SQL* | Write code to create tables in a SQL Server database. |
| Working with data* | Write code to insert, delete and modify records in a table in SQL Server. |
Whenever you write code to modify data it's always handy to have the opportunity to undo your changes. Transactions provide you with exactly that.
| Tutorial | Learn how to... |
|---|---|
| Transactions* | Create transactions and then either confirm or undo changes you have made to data. |
There are all sorts of ways of working with temporary data in SQL Server. This series of articles explains how to use the main techniques, along with an analysis of the pros and cons of each.
| Tutorial | Learn how to... |
|---|---|
| Temp tables | Create and use temporary tables in SQL Server to hold a set of records temporarily. |
| Table variables* | Hold records in a table variable. |
| Table-valued functions* | Write a function to return a table of data, including the difference between inline and multi-statement table-valued functions. |
| Common Table Expressions | Create a query using Common Table Expressions, or CTEs for short. |
| Derived Tables* | Create a query using derived tables. |
If you've used crosstab queries in Microsoft Access or pivot tables in Microsoft Excel you'll definitely want to know about how to pivot data in SQL Server.
| Tutorial | Learn how to... |
|---|---|
| The PIVOT Clause* | Rearrange the results of a query using the PIVOT clause. |
Triggers are special stored procedures that are attached to tables. They are executed automatically when data is inserted, edited or deleted in the table they are attached to.
| Tutorial | Learn how to... |
|---|---|
| Creating Triggers* | Create triggers and attach them to specific data change events in a table. |
A cursor allows you to step over a set of records one-by-one. You'll rarely need to use them in SQL Server but this article explains how they work for the rare occasions you do.
| Tutorial | Learn how to... |
|---|---|
| Cursors* | Create a cursor and loop over a set of records in SQL Server. |
Dynamic SQL allows you to build up a string of text and then execute it as a SQL statement. It allows you to create immensely flexible statements but with one major downside...
| Tutorial | Learn how to... |
|---|---|
| Dynamic SQL* | Concatenate a string and execute it as a SQL command, including the pros and cons of doing this. |
Did you know that you can write a query in SQL Server that displays a map in the results panel? Neither did we until quite recently! Here's an article explaining the basic concepts of working with spatial data in SQL.
| Tutorial | Learn how to... |
|---|---|
| Spatial Data in SQL Server* | Use the GEOMETRY and GEOGRAPHY data types to work with spatial data in SQL Server. |
Comments on this blog
This blog currently has no comments.