Summary: Learn to be more productive with SQL Server 2005 Express Edition with this quick introduction to the T-SQL language and the basics of getting information from the database using the SELECT statement.
Introduction
With the availability of ever more powerful programming tools and environments such as Visual Basic and Visual Studio.NET, as well as the availability of powerful database engines such as the free SQL Server 2005 Express Edition, more and more people find themselves having to learn the basics of SQL queries and statements. Sometimes they are professional developers who are experienced in other types of programming, and sometimes they are individuals whose expertise lies in other areas, but they suddenly find themselves programming database applications for fun and/or profit. If you fall into one of these categories, or are just curious about database programming, then this article is for you.
SQL Server 2005 Express offers you the opportunity to dive deeply into advanced databases and database applications, while still being free of charge. It is the same core database engine as all of the other versions in the SQL Server 2005, but it allows for easier setup and distribution all at no cost. It supports all of the advanced database features including, views, stored procedures, triggers, functions, native XML support, full T-SQL support, and high performance.
The purpose of this article is to lay out the basic structure and use of SQL SELECT queries and statements. These statements are part of Transact-SQL (T-SQL) language specification and are central to the use of Microsoft SQL Server. T-SQL is an extension to the ANSI SQL standard and adds improvements and capabilities, making T-SQL an efficient, robust, and secure language for data access and manipulation.
Although many tools are available for designing your queries visually, such as the Visual Database Tools that are available with Microsoft Visual Studio, it is still worthwhile and important to understand the SQL language. There is a real benefit to understanding what the visual tools are doing and why. There are also times when manually writing the necessary SQL statement is the only, or simply the fastest, way to achieve what you want. It is also an ideal way to learn how to use the full power of a relational database such as SQL Express.
Relational Databases: A 30 Second Review
Although there exist many different types of database, we will focus on the most common type—the relational database. A relational database consists of one or more tables, where each table consists of 0 or more records, or rows, of data. The data for each row is organized into discrete units of information, known as fields or columns. When we want to show the fields of a table, let's say the Customers table, we will often show it like this:
Many of the tables in a database will have relationships, or links, between them, either in a one-to-one or a one-to-many relationship. The connection between the tables is made by a Primary Key – Foreign Key pair, where a Foreign Key field(s) in a given table is the Primary Key of another table. As a typical example, there is a one-to-many relationship between Customers and Orders. Both tables have a CustID field, which is the Primary Key of the Customers table and is a Foreign Key of the Orders Table. The related fields do not need to have the identical name, but it is a good practice to keep them the same.
Fetching Data: SQL SELECT Queries
It is a rare database application that doesn't spend much of its time fetching and displaying data. Once we have data in the database, we want to "slice and dice" it every which way. That is, we want to look at the data and analyze it in an endless number of different ways, constantly varying the filtering, sorting, and calculations that we apply to the raw data. The SQL SELECT statement is what we use to choose, or select, the data that we want returned from the database to our application. It is the language we use to formulate our question, or query, that we want answered by the database. We can start out with very simple queries, but the SELECT statement has many different options and extensions, which provide the great flexibility that we may ultimately need. Our goal is to help you understand the structure and most common elements of a SELECT statement, so that later you will be able to understand the many options and nuances and apply them to your specific needs. We'll start with the bare minimum and slowly add options for greater functionality.
Note: For our illustrations, we will use the Employees table from the Northwind sample database that has come with MS Access, MS SQL Server and is available for download at the Microsoft Download Center.
- SELECT
- FROM
- WHERE
- ORDER BY
The SELECT ... FROM Clause
The most basic SELECT statement has only 2 parts: (1) what columns you want to return and (2) what table(s) those columns come from.
If we want to retrieve all of the information about all of the customers in the Employees table, we could use the asterisk (*) as a shortcut for all of the columns, and our query looks like
