A Guide to MySQL, Chapter 4: Single-Table Queries

CIS 331: Database Management Using SQL

A Guide to MySQL, Chapter 4: Single-Table Queries

Objectives:

This chapter introduces the student to the relations, relational algebra, and several functions common to queries. The objectives important to this chapter are:

Retrieving data
Compund conditions
Computed columns
SQL operators and functions
Sorting
Nesting queries

Concepts:

Relational Model

Retreiving data begins with the SELECT command. A query using this command may have several variations. A classic form is given as the first example in the chapter:

SELECT column list FROM data table WHERE conditional test ;

SELECT is followed by a list of columns, which may be any columns in the table being accessed, separated by commas. If you want all columns, you can use a wildcard character. In MySQL, the wild card for "all" is the asterisk. FROM is followed by the name of the table to be read. WHERE is optional, and can be used to specify which rows to retrieve. If the WHERE clause is not used, all rows will be be retrieved.

Remember that the command must end with a semi-colon, and that you must space between words. Other than that, the syntax for the command is free form, which means that you can put line returns in it to make it easier to read, and the command will perform the same way.

The WHERE clause can be a simple condition, such as testing for a particular value in one field. If so, you can use the standard comparison operators listed in the text. You are probably familiar with most of them:

Operator Meaning

= is equal to

< is less than

> is greater than

<= is less than or equal to

>= is greater than or equal to

<> does not equal

!= does not equal

Example:

SELECT CustomerNum, CustomerName
FROM Customer
WHERE Balance > '3000' ;

Note that this comparison operator has a field name on its left, and a comparison value on its right. The comparison value is enclosed in what some people call single quotes. Other people call these apostrophes. The purpose of the quotes is twofold: to show that the value is not the name of a field, and to enclose the exact value to compare.

Compound conditions are just two or more conditions linked by logical operators: AND, OR, and NOT. These are also called Boolean operators. One way to use the not operator is to compose a comparison, and to precede it with NOT. Example:

SELECT CustomerNum, CustomerName
FROM Customer
WHERE NOT (Balance = '3000') ;

In this example, the query is looking for the customers whose balances are not equal to 3000. It is not required to put the condition in parentheses, but it helps make the meaning of the operation clear.

Computed columns are created by using math operators with the names of fields. The standard math operators are used: + for addition, - for subtraction, * for multiplication, and / for division. The example in the text suggests that we create a column that computes the available balance for a customer by subtracting their balance from their credit limit.

SELECT CustomerNum, CustomerName, (CreditLimit - Balance) AS AvailableCredit
FROM Customer;

This command would create a computed column, and display the results under the heading "AvailableCredit", which does not exist as a field name, but is used as a column heading in this case.

A useful operator for times when you are searching for a partial match is the LIKE operator. When searching for a character string that occurs somewhere in a field, use the LIKE operator with a comparison value enclosed in percent signs. Example:

SELECT CustomerNum, CustomerName
FROM Customer
WHERE CustomerName LIKE ('%Sport%') ;

This command would retrieve the selected fields from all records having Sport anywhere in their names.

I used parentheses to mark the argument being handed to LIKE.
I used single quotes to mark the beginning and end of the argument.
The two % characters placed like this will match any number of characters before or after the comparison value. Placing a % only before or only after the comparison value would match any characters at that position. (In some systems, you will use the * instead of the %.)

The IN operator is used to match against any of a set of values. For example the text asks for customers having any of several specific credit limits:

SELECT CustomerNum, CustomerName
FROM Customer
WHERE CreditLimit IN (5000, 10000, 150000) ;

This query will report all customers whose credit limits match any of the three listed values. Note that these values are not markers for ranges: a record's CreditLimit must match one of those three numbers, or that record is not retrieved.

Sorting is accomplished with the ORDER BY clause. ORDER BY is followed by the names of the fields on which to sort the output. If sorting on more than one field, the first listed is the key for the primary sort, the second is the key for the secondary sort, and so on. Example:

SELECT CustomerNum, CustomerName, CreditLimit
FROM Customer
ORDER BY CreditLimit, CustomerName;

A sort will display results sorted in ascending order, unless the DESC switch is used. This switch follows the name of field to be sorted in descending order. Example:

SELECT CustomerNum, CustomerName, CreditLimit
FROM Customer
ORDER BY CreditLimit DESC, CustomerName;

Remember that the ORDER BY command will sort the rows in your output based on a specified column. It does NOT sort only the named column, which would disrupt the integrity of the data records.

A list of functions common to spreadsheets is offered, called aggregate functions. They give us the ability to provide a summary report from a query, rather than a detail report.

Function Result

AVG Average (mean) of a column

COUNT The number of rows matching retrieval criteria.

MAX Greatest value in a column

MIN Smallest value in a column

SUM The total of the values in a column

These functions can be combined with WHERE criteria, in order to apply them to subsets of the data. Example:

SELECT COUNT(*) FROM Customer WHERE CustomerName LIKE ('%Sport%');

This query would reply with the number 2, which is the number of records matching the criteria. MySQL works with this syntax, and with the alternate syntax in the text, replacing the * with the name of any field.

It is possible to run a query on the results of another query by nesting the first within the second. Figure out what you want to do for each query. The first one you need to run must be enclosed in parentheses, it must follow the IN operator, and that operator comes at the end of the second query to run. Example (colors are added for reference):

SELECT OrderNum FROM OrderLine
WHERE PartNum IN
(SELECT PartNum FROM Part
WHERE Class = 'AP') ;

In this example the blue query runs first, and results in a list of part numbers that fall in the AP class. The orange query then runs, matching records in the OrderLine table against the list of part numbers from the first query. I have made the semi-colon orange because the command does not end until the second query runs.

When you are troubleshooting commands, it is useful to ask for a bit more information, to make sure you are getting the results you want. If I were trying to troubleshoot the orange section of the command above, I would change it this way:

SELECT OrderNum, PartNum FROM OrderLine
WHERE PartNum IN
(SELECT PartNum FROM Part
WHERE Class = 'AP') ;

I have asked SQL to do the same thing, except that this time I want to see the part numbers that match the order numbers in the result. This way, I can read the output and make sure that only the records I want to see are being reported. After I was sure that the query worked, I would change it so that the output only contained what I (or my customer) really wanted in the result.

That might not tell me enough, so I would also run the blue section by itself, with a similar change:

SELECT PartNum, Class FROM Part
WHERE Class = 'AP';

This command would show me the output of the inner query, along with the class for each part number selected. That output would be easy to check visually, to verify that I am only getting part numbers for the correct class. Again, I would only do this to check the command. If I ran the nested query with this change in it, I might get results that I do not want. It is more important to remove these visual checks from the inner query than from the outer one, because we are feeding the result of the inner query to the outer one. We don't want the outer query to choke on more data than it expects.

Apply this troubleshooting concept to any SQL command that is giving you trouble, and every command you are trying to check for errors.