Home > Articles > Data > SQL

Query Types

There are four basic types of queries:

  • Queries that read from the database

  • Queries that write to the database

  • Queries that update existing information in the database

  • Queries that delete information from the database

The following sections examine the important details of each type of query, and apply them to the GuestBook table we created in the database tutorial.

SELECT: Reading from the Database

You use a SQL SELECT query—often simply called a SELECT query or just a SELECT—to read information from one or more tables in your database. This is the general syntax of the simplest form of this query:

SELECT fieldname(s) FROM table(s)

For example, if you want to view all the information in the GuestBook table, the SELECT query would look like this:

SELECT email, comment, age, dateposted, hideEmail, name, gender FROM GuestBook

Ready to get started? Follow these steps:

  1. Enter the above query into the query window as shown in Figure 9.

    NOTE

    Important: You can enter the entire query on one line in the SQL query window or, for readability purposes, add extra spaces and line breaks.

    Figure 9 Entering a SELECT query into the query window.

    The database doesn't actually execute the query until you click the Run button, which is the button in the toolbar with the big red exclamation mark in the middle (see Figure 10).

    Figure 10 The Run button starts the query process.

  2. Click the Run button to execute the query. The result appears in the query window (see Figure 11).

    Figure 11 Results of the SELECT query.

  3. The records displayed are indeed the ones we manually entered in the last tutorial, so our query works! Let's go back to the query window and experiment with more sophisticated queries.

  4. To get back to the query window, click the down arrow on the View button and select SQL View from the drop-down list (see Figure 12).

    Figure 12 To get back to the query window, use the View button.

Displaying All Fields with a Wildcard

It can get tedious typing all the fieldnames for your SELECT query. SQL allows you to use an asterisk (*), also known as a wildcard, to indicate that you want all the fields. So, if you want to view all the records in the GuestBook table, here's the equivalent and now much shorter SELECT query (see Figure 13):

SELECT * FROM GuestBook

Figure 13 Using a wildcard to list all fields.

As Figure 14 shows, when you run the query with the wildcard, you get the same results as in the longer version of the query (refer to Figure 11).

Figure 14 Query results for SELECT * FROM GuestBook.

It's also the same information you would get by "manually" opening the GuestBook table (clicking the GuestBook table icon in the starting window). So you're probably wondering what the big deal is with the SELECT query. The answer will become apparent in the next couple of examples, in which we use SELECT to view different parts of our table.

Viewing Partial Fields

Suppose you aren't interested in viewing all the information in a record; you simply want to look at one or two fields. In that case, a query that returns everything wastes both your time and your computer's memory. Thankfully, SQL allows you to restrict the fields that the database returns. To do so, in your SELECT query, simply list only the fields you want to see. For example, if you want to view just the names and comments for all the GuestBook entries, use this query (see Figure 15):

SELECT name, comment FROM GuestBook

Figure 15 Restricting the SELECT query to a couple of fields.

When you run the query by clicking the Run button, the query window displays just the name and comment fields (compare Figure 16 with Figure 14).

Figure 16 Results of the restricted SELECT query.

Restricting the fields that are returned (displayed) is just one useful action you can perform with the SELECT query. However, the real power of the SELECT query comes from the flexibility it provides in terms of accessing information according to different criteria.

Specifying Numeric Conditions

Imagine you have a database full of customer information and you want to know how many of your customers over the age of 30 bought your products. How would you do this? There are many situations in which you only want to look at database information that fits a certain set of conditions (called criteria). By specifying one or more conditions in your SELECT query, you can narrow the information the database returns to just the records that you're interested in. You specify these conditions in a WHERE clause at the end of the SELECT query:

SELECT fieldname(s) FROM table(s) WHERE condition(s)

For example, suppose you want to find everyone in your GuestBook table who is at least 30 years of age. Here's the SELECT query (see Figure 17):

SELECT * FROM GuestBook WHERE age>=30

Figure 17 Entering a simple numeric condition for the SELECT query.

When you run the query by clicking the Run button, the query window displays the results shown in Figure 18.

Figure 18 Results of a numeric conditional query.

The query returns only Fred's and Barney's records in this example, because they're the only people listed in the table whose ages are 30 or over (Wilma and Betty are in their 20s).

Besides specifying a single numeric condition, you can specify multiple conditions and "connect" them using and and or. For example, suppose you want to see the records of anyone who signed your guest book who was between 20 and 30 years of age. The query would look like this (see Figure 19):

SELECT * FROM GuestBook WHERE age>=20 and age<=30

Figure 19 Specifying two numeric conditions in the WHERE clause of the SELECT query.

Figure 20 shows the results. The query returns Betty's and Wilma's records, because they're both between the ages of 20 and 30.

Figure 20 Results of specifying two numeric conditions in the WHERE clause.

Specifying String Conditions

Suppose you want to find specific individuals in your database whose names match a certain criterion, such as anyone who signed your guest book and whose last name is Smith. Or suppose you want to find obscene comments in your guest book so that you can remove them. How would you find such records? Well, just as we specified numeric conditions in the SELECT query to restrict the records the database returned, we can specify textual or string conditions as well. A key difference is that you must enclose string values between single quotes ('). Forgetting to put single quotes around string values is the most common source of errors for beginning SQL writers.

As with numeric conditions, you attach a string condition to the end of your basic SELECT query via the WHERE clause. To see an example of adding a string condition to a SELECT query, let's write a query to see whether Fred Flintstone signed our guest book. Here's the query:

SELECT * FROM GuestBook WHERE name='Fred Flintstone'

Notice the single quotes around the string Fred Flintstone (see Figure 21).

Figure 21 Specifying a string condition in the WHERE clause of the SELECT query.

Figure 22 shows the results. Since there's only one Fred Flintstone in the GuestBook table, the database returns just one record.

Figure 22 Results of specifying a string condition in the WHERE clause.

Finding a specific name isn't usually very useful. But by employing the LIKE operator, you can find names, words, and strings that fit a given pattern. We examine this topic next.

Combining String Conditions with LIKE

Suppose you want to find everyone in the database with a specific last name, such as Flintstone. Would you try the following query?

SELECT * FROM GuestBook WHERE name='Flintstone'

It looks like it should work, doesn't it? But this query is actually looking for anyone in the database whose name consists of one word: Flintstone. Don't believe it? Try running the query.

You need to be able to specify that you don't care what's before the name Flintstone; you just want the name to end in Flintstone. This goal requires that you use the LIKE operator in place of the equals (=) operator. Here's our (working) query (see Figure 23):

SELECT * FROM GuestBook WHERE name LIKE 'Flintstone'

Figure 23 Using LIKE in the string condition.

Figure 24 shows the results. Both Fred and Wilma—and only Fred and Wilma, so far in our database—have the last name Flintstone.

Figure 24 Results of using LIKE in the string condition.

Combining Field Restrictions with String Conditions

You can combine field restrictions with wildcards to perform any number of bizarre searches. For example, suppose you want to find the name, email address, and age of anyone in your guest book whose name contains the letter a. Here's the query; notice the two wildcards—an asterisk before the a and one after it (see Figure 25):

SELECT name,email,age FROM GuestBook WHERE name LIKE '*a*'

Figure 25 Combining wildcards and restricted fields.

Figure 26 shows the results. The database correctly returns Wilma's and Barney's records because each includes the letter a in the name.

Figure 26 Finding anyone whose name contains a.

This may seem like a useless query, but it can be modified easily to find entries in the table with obscene words. Simply substitute your favorite obscenity for the letter a, and specify the comment field in place of the name field.

Combining Field Restrictions with Numeric and String Conditions

You can combine string and numeric conditions by using and or or. Our final example in this section combines field restrictions with both numeric and string conditions. Suppose you want to find the name, e-mail address, and age of anyone in your guest book who is between the ages of 20 and 30 and whose name contains the letter a. Here's the query (see Figure 27):

SELECT name,email,age FROM GuestBook
WHERE name LIKE '*a*' and age>=20 and age<=30

Figure 27 Combining field restrictions with string and numeric conditions.

Notice how the string condition (name like '*a*') and the numeric conditions (age>=20, age<=30) are combined using and. Figure 28 shows the results.

Figure 28 Results of combining field restrictions with string and numeric conditions.

Of the entries in the database, only Wilma has the letter a in her name and is 20–30 years old; thus the query returned the correct results.

We've covered all the basic SQL SELECT queries you need to know in order to read information out of a table in your database. Now, SELECT queries can become quite complex, especially if you search over more than one table, or when you start embedding queries. However, you need to understand and become comfortable using these basic SELECT queries before you attempt the more complex ones. So practice experimenting with these SELECT queries and making up your own.

Next we look at using SQL to write information into a database table.

INSERT: Writing into the Database

As you've probably surmised, if there's a SQL command to read information out of a table, there's a SQL command to write information into a table. That command is INSERT. The syntax of the basic INSERT command is as follows:

INSERT INTO table(fieldname(s)) VALUES (values(s))

This command is best illustrated by an example using the GuestBook table. Suppose you want to insert a 30-year old male named Nick Flor, whose email address is nick@flor.com, and whose comment is I love guitars. The INSERT query would look like this (see Figure 29):

INSERT INTO GuestBook(name, email, age, gender, comment, hideEmail)
[ccc]VALUES ('Nick Flor', 'nick@flor.com', 30, 'male', 'I love guitars', Yes)

Figure 29 INSERT query.

There are two important details you should note:

  • The order in which you specify the fields in the INSERT query doesn't have match the order of the fields in the GuestBook table. You can specify the fields in whatever order you want, but you must list the values in the same order as the fields. If your INSERT query specifies name followed by email address, the VALUES list must specify the actual name to insert, followed by the email address. Otherwise you'll get incorrect data in your table—a name where an email address ought to be and vice versa—or, worst case, your query won't run at all.

  • You must enclose entries for text and memo field values in single quotes ('). The age field takes a numeric value, so it doesn't have to be enclosed in quotes. Similarly, the hideEmail field takes a Boolean value (Yes/No or True/False), which you don't have to surround with single quotes.

The dialog box shown in Figure 30 pops up, warning you that you're about to append information to your table. Click Yes to complete the insert process.

Figure 30 Append warning.

Unlike the SELECT query, the INSERT query doesn't return any results; Access just returns you to the query window (see Figure 31). Notice that the window's title bar changes from reading Select Query to Append Query.

Figure 31 The SQL query window after running the INSERT query.

To verify that the INSERT query worked, open the GuestBook table and look for the new entry. In case you forgot how, follow these steps:

  1. Activate the opening window. It's usually behind the query window, and you can activate it by simply clicking the title bar or the edge of the window (see Figure 32).

    Figure 32 Opening the GuestBook table to verify that the INSERT query worked.

  2. Click the Tables button in the Objects list on the left side of the window.

  3. Double-click the GuestBook table's icon to open the table (see Figure 33). Notice that the INSERT query did indeed insert the new data into the GuestBook table.

    Figure 33 The GuestBook table contains the new entry.

Exercise: INSERT Query

Now you try using the SQL INSERT query to add information to the database. For this example, add a 29-year-old female named Nancy Flor to the GuestBook table, with the email address nancy@flor.com (don't hide the address), and the comment I love pianos (see Figure 34):

Figure 34 Exercise: INSERT query.

If you wrote the INSERT query correctly, when you open the GuestBook table you should see the entry shown in Figure 35.

Figure 35 Contents of the GuestBook table after completing the exercise.

INSERT queries are not as difficult to write as SELECT queries. At worst, you have to do a lot of typing if your tables have many fields defined.

Do you have to specify all fields in an INSERT query? Usually not. We examine a partial INSERT procedure in the next section.

Exploiting Default Values: Inserting a Partial Record into the Database

When we created the GuestBook table in the last tutorial, we set it up so that all the fields except email and comment had default values. Thus, it's okay if in our INSERT query we leave out a field that has a default value—the database automatically inserts the default value we defined. Did you notice that none of the INSERT queries above mentioned the dateposted field? Yet the GuestBook table included a value in that field for each of the entries we just added. (Don't believe me? Hop back to Figures 33 and 35 and take a look.)

To show how these default values are set, suppose we write an INSERT query that just inserts an email address and comment into the database (see Figure 36):

INSERT INTO GuestBook(email, comment)
VALUES ('bowser@flor.com', 'I love biscuits')

Figure 36 Partial INSERT query.

Now open the GuestBook table and look for the new entry (see Figure 37). The database automatically inserted default values for fields that we didn't define in the query. Specifically, the database inserted this info:

  • 0 for the age field

  • The current time for the dateposted field

  • Yes for the hideEmail field

  • Declined to Specify for the name field

  • female for the gender field

Figure 37 Default values were supplied by the database.

DELETE and UPDATE Queries

In addition to the INSERT and SELECT queries for storing information in and retrieving information from a table, there are queries for removing and updating information. It's beyond the scope of this tutorial to cover these queries in detail, but we'll show you at least the general syntax of these commands in the following sections.

DELETE Query

Anything you can SELECT you can DELETE. The DELETE query has almost the same syntax as the SELECT query, except that the word SELECT is replaced by DELETE and you don't specify any fields—you can only delete the entire record, not fields within a record. This is the general syntax for the DELETE query:

DELETE FROM table WHERE condition(s)

For example, suppose you want to delete the records of all guest book users who from 20–30 whose names contain the letter a. Here's the query:

DELETE FROM GuestBook
WHERE name LIKE '*a*' and age>=20 and age<=30

This query would delete Wilma Flintstone's record from our GuestBook table, because this record fits the conditions specified in the WHERE clause (refer to Figure 28). Compare the DELETE query above with the corresponding SELECT query that selected Wilma's record in Figure 28. We've boldfaced the differences below:

SELECT name,email,age FROM GuestBook
WHERE name LIKE '*a*' and age>=20 and age<=30

If you're comfortable writing the WHERE conditions for SELECT queries, you should have no problems writing DELETE queries.

CAUTION

Be very careful with DELETE queries. There's no "undo" operation as in a word processor. Once you execute a DELETE query, the only way you can recover the deleted information is via a backup copy of the database.

UPDATE Query

There are many situations where you want to change a couple of fields in a record, usually because the information was entered incorrectly (such as a spelling error) or changed (such as a raise in salary). To change one or more fields in a record, you use the UPDATE query, which has this general syntax:

UPDATE table SET field1=value1, field2=value2, ..., fieldN=valueN
WHERE condition

For example, if you want to set Fred Flintstone's age in the database to 40, you would use the following query:

UPDATE GuestBook SET age=40 WHERE name='Fred Flintstone'

The WHERE clause usually refers to a single record. If your WHERE clause specifies more than one record, all those records will have their field values changed. For example, the following UPDATE query refers to more than one record (refer to Figure 26):

UPDATE GuestBook set age=7,name='Dino'
WHERE name LIKE '*a*'

Specifically, the records for both Wilma and Barney fit the conditions specified in the WHERE clause. Thus, the database will change both Wilma's and Barney's names to Dino and set both their ages to 7. The "tricky" part to writing an UPDATE query is getting the WHERE clause correct. However, as with the DELETE query, if you're comfortable writing WHERE clauses for your SELECT queries, you should have little problem generalizing your knowledge to UPDATE queries.

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