Lesson 2: Overview of Structured Query Language

Lesson 2: Overview of Structured Query Language

Structured Query Language (SQL) is the universal language of relational databases. SQL can be used to retrieve and filter records from databases, to add records to databases, to delete records from a database, and to change the values of an existing record.

Using SQL statements behind the scenes, ADO .NET handles most of the actual database interaction for you through the classes contained in a data provider. In some cases, though, it is important to be able to generate SQL statements to interact directly with a database. For example, you might want to dynamically build SQL statements in response to user input to retrieve a custom set of rows. In this lesson, you will learn the basics of SQL syntax.

After this lesson, you will be able to

• Describe basic SQL syntax

• Construct simple SELECT, UPDATE, INSERT, and DELETE commands

• Use the WHERE clause to filter the results returned by a SQL query and use the ORDER BY clause to specify a sort order

Estimated lesson time: 30 minutes

Structured Query Language uses four basic statements to interact with a database. The SELECT statement is used to retrieve records from a database; the UPDATE statement is used to update data in a database; the INSERT statement is used to insert a new row into a table; and the DELETE statement is used to delete a record.

NOTE
The information provided in this section covers only the basics of Structured Query Language it should not be considered comprehensive. You are encouraged to supplement your reading with additional texts if you find the subject interesting.

The SELECT Statement

The SELECT statement is the command used to retrieve records from a database. A SELECT statement can be divided into four segments:

• SELECT.

  This segment allows you to specify which fields will be retrieved.

• FROM.

  This segment allows you to specify which table or tables will be used to get the fields specified in the SELECT segment.

• WHERE.

  (Optional) This segment allows you to apply filter conditions to the rows retrieved.

• ORDER BY.

  (Optional) This segment allows you to specify a field by which to order the returned records.

The minimum syntax for an SQL SELECT statement is as follows:

SELECT fields FROM tables;

fields represents the field or fields to be retrieved, and tables represents the table or tables where those fields will be found. For example, to retrieve the EmployeeID and StartDate fields from the Employees table, you use the following statement:

SELECT EmployeeID, StartDate FROM Employees;

NOTE
In SQL Server, SQL statements are terminated with a semicolon (;).

You can use the * character to represent all fields in a table or tables. Thus, if you wanted to retrieve all fields from the Employees table, your query would look like this:

SELECT * FROM Employees;

NOTE
If specific fields are required for a query, you should select the specific fields instead of using the * character. This ensures that the correct fields are always returned in the same order regardless of any data-table changes that might have occurred.

The WHERE clause

You might want to limit or filter the records that you retrieve from the database, such as only the records with a particular value in one field the optional WHERE clause gives you this choice. To illustrate, the following statement selects all records from the Employees table if the value of the FirstName field is Bob :

SELECT * FROM Employees WHERE FirstName = 'Bob';

NOTE
To specify a string literal in an SQL statement, you must enclose the literal in single quotes (' ').

You can use logical operators such as AND or OR to specify multiple conditions. The following code example retrieves all records from the Employees table where the LastName field is Jones and the FirstName field is Joe :

SELECT * FROM Employees WHERE FirstName = 'Joe' AND LastName = 'Jones';

You can use the IN operator to retrieve records by matching the value of a field to values specified in a list. The following SQL statement shows an example of WHERE IN. This statement will retrieve the FirstName and LastName fields from the Employees table if the Country field is US or UK :

SELECT FirstName, LastName FROM Employees WHERE Country IN ('UK', 'US');

You also can use the BETWEEN operator to specify that a value lies within a specified range. This example retrieves all records from the Sales table if the FinalPrice field is between 100 and 200:

SELECT * FROM Sales WHERE FinalPrice BETWEEN 100 AND 200;

The WHERE clause also allows you to search string fields for similar, but not exact, matches using the LIKE operator. The LIKE operator allows you to use wildcard characters to specify a pattern to match. In an SQL query, the underscore (_) character represents any single character, and the percent (%) character represents any number of characters. The following SQL statement is an example:

SELECT * FROM Employees WHERE FirstName LIKE 'Wil_';

This statement matches Will but not Willy or William . The scenario changes in the following code example:

SELECT * FROM Employees WHERE FirstName LIKE 'Wil%';

In this case, the statement matches Will , William , Wilhelmina , and any other FirstName values that begin with Wil .

The ORDER BY Clause

The ORDER BY clause, which is also optional, allows you to specify the order in which records are returned. You can use the ASC option to specify ascending order, or the DESC option to specify descending order. The default order is ascending (A to Z, 0 to 9). The following code example selects all fields from the Employees table and returns them in descending order by salary:

SELECT * FROM Employees ORDER BY Salary DESC;

The DELETE Statement

A DELETE statement is used to delete records from the database. You can use a delete statement to delete single records or groups of data based on criteria.

WARNING
The DELETE statement is irreversible. Once data is deleted, it cannot be recovered unless the database was previously archived. Take great care when using DELETE statements.

In general, the DELETE statement follows the same syntax as the SELECT statement. The difference is that you do not specify fields to delete, as you can only delete entire rows. You can use the WHERE clause to specify the record or records to be deleted. The following statement deletes any records from the Employees table where the FirstName is Joe and the LastName is Jones :

DELETE FROM Employees WHERE FirstName = 'Joe' AND LastName = 'Jones';

Note that you can use the same operators in the WHERE clause of the DELETE statement that you can use in the SELECT statement, such as logical operators IN, LIKE, and BETWEEN.

The UPDATE statement

You might need to change the values of existing records to reflect changes in real-world situations. For example, customers move, their addresses change, and the records that represent them must be changed to take the new information into account. Database records can be updated using the UPDATE statement.

The general syntax for an UPDATE statement is as follows:

UPDATE tablename  SET column1 = value1, ,columnN = valueN [WHERE predicates];

In this syntax, tablename represents the name of the table in which the updated records exist; column1 represents the first column to be updated; and value1 represents the value to be updated in that column. As many column-value pairs as necessary can be specified this way, separated by commas. The WHERE clause of an UPDATE statement is optional, as it is with the SELECT and DELETE statements. Also, like the DELETE statement, an UPDATE statement cannot be rolled back. To reverse an UPDATE statement, you must perform another UPDATE statement to reset the data or restore the database from an existing backup.

The following code example shows how to update the LastName field of the Employees table if the FirstName is Mary and the LastName is Smith :

UPDATE Employees SET LastName = 'Jones' WHERE FirstName = 'Mary' AND LastName = 'Smith';

Note that you can use IN, BETWEEN, LIKE, and other operators in the WHERE clause, just as you would with a SELECT statement.

The INSERT INTO Statement

When it is necessary to add new records to a database, you can use the INSERT INTO statement. This statement causes the insertion of a new record into the specified table. The general syntax for the INSERT INTO statement is as follows:

INSERT INTO table [(column1, , columnN)] VALUES (value1, , valuen);

In this example, table represents the table that will receive the new record. column1 through columnN are optional. If particular columns are not specified, records will be inserted in the default order specified by the table. Note that this can be hazardous, as the structure of the table might change without the knowledge of the developer. Whenever possible, you should specify the affected columns in an INSERT INTO statement. Values for the new record are represented by value1 through valuen. If a value is not specified for a column, it will remain a null value. The following code example demonstrates adding a new record to the Employees table:

INSERT INTO Employees (FirstName, LastName, Title, Salary) VALUES ('Joe', 'Jones', 'Boss', 100000);

Lesson Summary

Structured Query Language (SQL) is a language used for communicating with databases. Although ADO.NET handles most of the database interaction, you can manually retrieve, update, insert, or delete records using well-formed SQL statements. The SELECT statement is used to retrieve records, and is divided into four major sections: SELECT, FROM, WHERE, and ORDER BY. Each section allows you to specify different aspects of the records to be retrieved. You can use the DELETE statement to remove records from a database. The UPDATE statement is used to update values in existing records, and the INSERT INTO statement is used to add new records to an existing tables.