Chapter 2. Structured Query Language Queries and Commands

IN THIS CHAPTER

• What Is a Query?

• Testing Queries with the Server Explorer

• Retrieving Records with the SELECT Clause

• Designating a Record Source with the FROM Clause

• Specifying Criteria with the WHERE Clause

• Sorting Results with ORDER BY

• Displaying the Top or Bottom of a Range with TOP

• Joining Related Tables in a Query

• Performing Calculations in Queries

• Aliasing Field Names with AS

• Queries That Group and Summarize Data

• Union Queries

• Subqueries

• Manipulating Data with SQL

• Using Data Definition Language

The discussion of database and table structure in Chapter 1 demonstrated how to create a database by using VB.NET and SQL Server. In this chapter we're concerned with manipulating data in tables and creating and changing the structure of tables by using Structured Query Language (SQL).

SQL queries give you the ability to retrieve records from a database table, match related data in multiple tables, and manipulate the structure of databases. SQL queries are also used when you manipulate databases in code.

SQL is a standard way of manipulating databases. It's implemented in various forms in many relational database systems, including Microsoft Access and SQL Server, and systems provided by other vendors such as Oracle and IBM. (In fact, IBM gets the credit for inventing SQL.) Generally, SQL is used for creating queries that extract data from databases, although a large subset of SQL commands perform other functions on databases, such as creating tables and fields.

Generally, SQL commands fall into two categories:

• Data Definition Language (DDL) commands, which are used to create and alter components of the database, such as the structure of tables, fields, and indexes

• Data Manipulation Language (DML) commands, designed to retrieve, create, delete, and update records in databases

In this chapter we demonstrate how to use both kinds of commands.