Home > Articles > Programming > Windows Programming

This chapter is from the book

21.4 SQL

We now overview SQL in the context of the Books database. Though LINQ to SQL and the Visual C# IDE hide the SQL used to manipulate databases, it is nevertheless important to understand SQL basics. Knowing the types of operations you can perform will help you develop more advanced database-intensive applications.

Figure 21.10 lists some common SQL keywords used to form complete SQL statements—we discuss these keywords in the next several subsections. Other SQL keywords exist, but they are beyond the scope of this text.

Fig. 21.10 Common SQL keywords.

SQL keyword

Description

SELECT

Retrieves data from one or more tables.

FROM

Specifies the tables involved in a query. Required in every query.

WHERE

Specifies optional criteria for selection that determine the rows to be retrieved, deleted or updated.

ORDER BY

Specifies optional criteria for ordering rows (e.g., ascending, descending).

INNER JOIN

Specifies optional operator for merging rows from multiple tables.

INSERT

Inserts rows in a specified table.

UPDATE

Updates rows in a specified table.

DELETE

Deletes rows from a specified table.

21.4.1 Basic SELECT Query

Let us consider several SQL queries that retrieve information from database Books. A SQL query “selects” rows and columns from one or more tables in a database. Such selections are performed by queries with the SELECT keyword. The basic form of a SELECT query is

SELECT * FROM tableName

in which the asterisk (*) indicates that all the columns from the tableName table should be retrieved. For example, to retrieve all the data in the Authors table, use

SELECT * FROM Authors

Note that the rows of the Authors table are not guaranteed to be returned in any particular order. You’ll learn how to specify criteria for sorting rows in Section 21.4.3.

Most programs do not require all the data in a table—in fact, selecting all the data from a large table is discouraged, as it can cause performance problems. To retrieve only specific columns from a table, replace the asterisk (*) with a comma-separated list of the column names. For example, to retrieve only the columns AuthorID and LastName for all the rows in the Authors table, use the query

SELECT AuthorID, LastName FROM Authors

This query returns only the data listed in Fig. 21.11.

Fig. 21.11 AuthorID and LastName data from the Authors table.

AuthorID

LastName

1

Deitel

2

Deitel

3

Ayer

4

Quirk

21.4.2 WHERE Clause

When users search a database for rows that satisfy certain selection criteria (formally called predicates), only rows that satisfy the selection criteria are selected. SQL uses the optional WHERE clause in a query to specify the selection criteria for the query. The basic form of a query with selection criteria is

SELECT columnName1, columnName2, ... FROM tableName WHERE criteria

For example, to select the BookTitle, EditionNumber and Copyright columns from table Titles for which the Copyright date is more recent than 2007, use the query

SELECT BookTitle, EditionNumber, Copyright
FROM Titles
WHERE Copyright > '2007'

Note that string literals in SQL are delimited by single quotes instead of double quotes as in C#. In SQL, double quotes are used around table and column names that would otherwise be invalid—names containing SQL keywords, spaces, or other punctuation characters. Figure 21.12 shows the result of the preceding query.

Fig. 21.12 Books with copyright dates after 2007 from table Titles.

BookTitle

EditionNumber

Copyright

Internet & World Wide Web How to Program

4

2008

Simply Visual Basic 2008

3

2009

Visual Basic 2008 How to Program

4

2009

Visual C# 2008 How to Program

3

2009

Visual C++ 2008 How to Program

2

2008

C++ How to Program

6

2008

The WHERE-clause criteria can contain the comparison operators <, >, <=, >=, = (equality), <> (inequality) and LIKE, as well as the logical operators AND, OR and NOT (discussed in Section 21.4.6). Operator LIKE is used for pattern matching with wildcard characters percent (%) and underscore (_). Pattern matching allows SQL to search for strings that match a given pattern.

A pattern that contains a percent character (%) searches for strings that have zero or more characters at the percent character’s position in the pattern. For example, the following query locates the rows of all the authors whose last names start with the letter D:

SELECT AuthorID, FirstName, LastName
FROM Authors
WHERE LastName LIKE 'D%'

The preceding query selects the two rows shown in Fig. 21.13, because two of the four authors in our database have a last name starting with the letter D (followed by zero or more characters). The % in the WHERE clause’s LIKE pattern indicates that any number of characters can appear after the letter D in the LastName column. Note that the pattern string is surrounded by single-quote characters.

Fig. 21.13 Authors from the Authors table whose last names start with D.

AuthorID

FirstName

LastName

1

Harvey

Deitel

2

Paul

Deitel

An underscore (_) in the pattern string indicates a single wildcard character at that position in the pattern. For example, the following query locates the rows of all the authors whose last names start with any character (specified by _), followed by the letter y, followed by any number of additional characters (specified by %):

SELECT AuthorID, FirstName, LastName
FROM Authors
WHERE LastName LIKE '_y%'

The preceding query produces the row shown in Fig. 21.14, because only one author in our database has a last name that contains the letter y as its second letter.

Fig. 21.14 The only author from the Authors table whose last name contains y as the second letter.

AuthorID

FirstName

LastName

3

Greg

Ayer

21.4.3 ORDER BY Clause

The rows in the result of a query can be sorted into ascending or descending order by using the optional ORDER BY clause. The basic form of a query with an ORDER BY clause is

SELECT columnName1, columnName2, ... FROM tableName ORDER BY column ASC
SELECT columnName1, columnName2, ... FROM tableName ORDER BY column DESC

where ASC specifies ascending order (lowest to highest), DESC specifies descending order (highest to lowest) and column specifies the column on which the sort is based. For example, to obtain the list of authors in ascending order by last name (Fig. 21.15), use the query

SELECT AuthorID, FirstName, LastName
FROM Authors
ORDER BY LastName ASC

Fig. 21.15 Authors from table Authors in ascending order by LastName.

AuthorID

FirstName

LastName

3

Greg

Ayer

1

Harvey

Deitel

2

Paul

Deitel

4

Dan

Quirk

The default sorting order is ascending, so ASC is optional in the preceding query. To obtain the same list of authors in descending order by last name (Fig. 21.16), use

SELECT AuthorID, FirstName, LastName
FROM Authors
ORDER BY LastName DESC

Fig. 21.16 Authors from table Authors in descending order by LastName.

AuthorID

FirstName

LastName

4

Dan

Quirk

1

Harvey

Deitel

2

Paul

Deitel

3

Greg

Ayer

Multiple columns can be used for sorting with an ORDER BY clause of the form

ORDER BY column1 sortingOrder, column2 sortingOrder, ...

where sortingOrder is either ASC or DESC. Note that the sortingOrder does not have to be identical for each column. For example, the query

SELECT BookTitle, EditionNumber, Copyright
FROM Titles
ORDER BY Copyright DESC, BookTitle ASC

returns the rows of the Titles table sorted first in descending order by copyright date, then in ascending order by title (Fig. 21.17). This means that rows with higher Copyright values are returned before rows with lower Copyright values, and any rows that have the same Copyright values are sorted in ascending order by title.

Fig. 21.17 Data from Titles in descending order by Copyright and ascending order by BookTitle.

BookTitle

EditionNumber

Copyright

Simply Visual Basic 2008

3

2009

Visual Basic 2008 How to Program

4

2009

Visual C# 2008 How to Program

3

2009

C++ How to Program

6

2008

Internet & World Wide Web How to Program

4

2008

Visual C++ 2008 How to Program

2

2008

C How to Program

5

2007

Java How to Program

7

2007

The WHERE and ORDER BY clauses can be combined. If used, ORDER BY must be the last clause in the query. For example, the query

SELECT ISBN, BookTitle, EditionNumber, Copyright
FROM Titles
WHERE BookTitle LIKE '%How to Program'
ORDER BY BookTitle ASC

returns the ISBN, BookTitle, EditionNumber and Copyright of each book in the Titles table that has a BookTitle ending with “How to Program” and sorts them in ascending order by BookTitle. The query results are shown in Fig. 21.18.

Fig. 21.18 Books from table Titles whose BookTitles end with How to Program in ascending order by BookTitle.

ISBN

BookTitle

EditionNumber

Copyright

0132404168

C How to Program

5

2007

0136152503

C++ How to Program

6

2008

0131752421

Internet & World Wide Web How to Program

4

2008

0132222205

Java How to Program

7

2007

013605305X

Visual Basic 2008 How to Program

4

2009

013605322X

Visual C# 2008 How to Program

3

2009

0136151574

Visual C++ 2008 How to Program

2

2008

21.4.4 Retrieving Data from Multiple Tables: INNER JOIN

Database designers typically normalize databases—i.e., split related data into separate tables to ensure that a database does not store redundant data. For example, the Books database has tables Authors and Titles. We use an AuthorISBN table to store “links” between authors and titles. If we did not separate this information into individual tables, we would need to include author information with each entry in the Titles table. This would result in the database storing duplicate author information for authors who have written more than one book.

Redundant data in a database increases the likelihood of errors when manpulating the data. Figure 21.1 contains redundant information between the Department and Location columns—for each department number, there is a single location and vice versa. This relationship is not enforced by the table’s structure. Normalization eliminates redundant data and allows the DBMS to prevent problems that could arise if queries depend on the one-to-one mapping between Department and Location.

Often, it is desirable to merge data from multiple tables into a single result—this is referred to as joining the tables. There are several kinds of joins, but the most common one is specified by an INNER JOIN operator in the query. An INNER JOIN merges rows from two tables by testing for matching values in a column that is common to the tables (though the column names can differ among the tables). The basic form of an INNER JOIN is:

SELECT columnName1, columnName2, ...
FROM table1 INNER JOIN table2
   ON table1.columnName = table2.columnName

The ON clause of the INNER JOIN specifies the columns from each table that are compared to determine which rows are merged. For example, the following query produces a list of authors accompanied by the ISBNs for books written by each author:

SELECT FirstName, LastName, ISBN
FROM Authors INNER JOIN AuthorISBN
   ON Authors.AuthorID = AuthorISBN.AuthorID
ORDER BY LastName, FirstName

The query combines the FirstName and LastName columns from table Authors and the ISBN column from table AuthorISBN, sorting the results in ascending order by LastName and FirstName. Note the use of the syntax tableName.columnName in the ON clause. This syntax (called a qualified name) specifies the columns from each table that should be compared to join the tables. The “tableName.” syntax is required if the columns have the same name in both tables. The same syntax can be used in any query to distinguish columns that have the same name in different tables.

As always, the query can contain an ORDER BY clause. Figure 21.19 depicts the results of the preceding query, ordered by LastName and FirstName.

Fig. 21.19 Authors and ISBNs for their books in ascending order by LastName and FirstName.

FirstName

LastName

ISBN

Greg

Ayer

0136053033

Harvey

Deitel

0131752421

Harvey

Deitel

0132222205

Harvey

Deitel

0132404168

Harvey

Deitel

0136053033

Harvey

Deitel

013605305X

Harvey

Deitel

013605322X

Harvey

Deitel

0136151574

Harvey

Deitel

0136152503

Paul

Deitel

0131752421

Paul

Deitel

0132222205

Paul

Deitel

0132404168

Paul

Deitel

0136053033

Paul

Deitel

013605305X

Paul

Deitel

013605322X

Paul

Deitel

0136151574

Paul

Deitel

0136152503

Dan

Quirk

0136151574

21.4.5 INSERT Statement

The INSERT statement inserts a row into a table. The basic form of this statement is

INSERT INTO tableName ( columnName1, columnName2, ..., columnNameN )
VALUES ( value1, value2, ..., valueN )

where tableName is the table in which to insert the row. The tableName is followed by a comma-separated list of column names in parentheses. The list of column names is followed by the SQL keyword VALUES and a comma-separated list of values in parentheses. The values specified here must match up with the columns specified after the table name in both order and type (e.g., if columnName1 is supposed to be the FirstName column, then value1 should be a string in single quotes representing the first name). Although the list of column names is not required if the INSERT operation specifies a value for every table column in the correct order, you should always explicitly list the columns when inserting rows—if the order of the columns in the table changes, using only VALUES may cause an error. The INSERT statement

INSERT INTO Authors ( FirstName, LastName )
VALUES ( 'Sue', 'Smith' )

inserts a row into the Authors table. The statement indicates that the values 'Sue' and 'Smith' are provided for the FirstName and LastName columns, respectively.

Some database tables allow NULL columns—that is, columns without values. Though the capitalization is different, NULL in SQL is similar to the idea of null in C#. All of the columns in the Books database are required, so they must be given values in an INSERT statement.

We do not specify an AuthorID in this example, because AuthorID is an identity column in the Authors table (see Fig. 21.3). For every row added to this table, SQL Server assigns a unique AuthorID value that is the next value in an autoincremented sequence (i.e., 1, 2, 3 and so on). In this case, Sue Smith would be assigned AuthorID number 5. Figure 21.20 shows the Authors table after the INSERT operation.

Fig. 21.20 Table Authors after an INSERT operation.

AuthorID

FirstName

LastName

1

Harvey

Deitel

2

Paul

Deitel

3

Greg

Ayer

4

Dan

Quirk

5

Sue

Smith

21.4.6 UPDATE Statement

An UPDATE statement modifies data in a table. The basic form of the UPDATE statement is

UPDATE tableName
SET columnName1 = value1, columnName2 = value2, ..., columnNameN = valueN
WHERE criteria

where tableName is the table to update. The tableName is followed by keyword SET and a comma-separated list of column name/value pairs in the format columnName = value. The optional WHERE clause provides criteria that determine which rows to update. While it is not required, the WHERE clause is almost always used, in an UPDATE statement because omitting it updates all rows in the table—an uncommon operation. The UPDATE statement

UPDATE Authors
SET LastName = 'Jones'
WHERE LastName = 'Smith' AND FirstName = 'Sue'

updates a row in the Authors table. Keyword AND is a logical operator that, like the C# && operator, returns true if and only if both of its operands are true. Thus, the preceding statement assigns to LastName the value Jones for the row in which LastName is equal to Smith and FirstName is equal to Sue. [Note: If there are multiple rows with the first name “Sue” and the last name “Smith,” this statement modifies all such rows to have the last name “Jones.”] Figure 21.21 shows the Authors table after the UPDATE operation has taken place. SQL also provides other logical operators, such as OR and NOT, which behave like their C# counterparts || and !.

Fig. 21.21 Table Authors after an UPDATE operation.

AuthorID

FirstName

LastName

1

Harvey

Deitel

2

Paul

Deitel

3

Greg

Ayer

4

Dan

Quirk

5

Sue

Jones

21.4.7 DELETE Statement

A DELETE statement removes rows from a table. Its basic form is

DELETE FROM tableName WHERE criteria

where tableName is the table from which to delete. The optional WHERE clause specifies the criteria used to determine which rows to delete. As with the UPDATE statement, the DELETE applies to all rows of the table if the WHERE clause is omitted. The DELETE statement

DELETE FROM Authors
WHERE LastName = 'Jones' AND FirstName = 'Sue'

deletes the row for Sue Jones in the Authors table. DELETE statements can delete multiple rows if the rows all meet the criteria in the WHERE clause. Figure 21.22 shows the Authors table after the DELETE operation has taken place.

Fig. 21.22 Table Authors after a DELETE operation.

AuthorID

FirstName

LastName

1

Harvey

Deitel

2

Paul

Deitel

3

Greg

Ayer

4

Dan

Quirk

SQL Wrap-Up

This concludes our SQL introduction. We demonstrated several commonly used SQL keywords, formed SQL queries that retrieved data from databases and formed other SQL statements that manipulated data in a database. Next, we introduce LINQ to SQL, which allows C# applications to interact with databases. As you will see, LINQ to SQL translates LINQ queries like the ones you wrote in Chapter 9 into SQL statements like those presented here.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020