1. Column Aliases and the Keyword AS
Column names in table
designs can't have spaces in them, and it is helpful if they're
short—which explains the use of column names such as roleID. A column
alias is an alternative name that can be displayed in the results of a
query, making it more user friendly. (An alias is a substitute name for
something.) To create aliases, you use the keyword AS plus square brackets, which serve as a kind of quotation mark. Example Query 1 illustrates this practice.
Example Query 1
SELECT roleID AS [role identifier], roleName AS [role name],
gender
FROM Role;
The output of Example
Query 1 would start as shown in the Example Query 1 Results. In the
results, the column headings contain the phrases given in brackets in
the query instead of the names of the table columns.
Example Query 1 Results
| role identifier | role name | gender |
|---|
| 00001 | Dorothy Gale | F |
| 00002 | The Wizard of Oz | M |
| 00003 | The Scarecrow | M |
| . . . | . . . | . . . |
2. Selection on Text Columns and the Keyword WHERE
Selection is the
technical term that describes choosing to include only some of the rows
in the results of a query. The keyword that makes this kind of
selectivity possible is WHERE. It follows the FROM
keyword and uses the operators for equality and inequality: <,
<=, =, >=, and >. In SQL, the following pair of symbols stands
for "not equals": <>. Selection is usually accomplished by
comparing a given value with the contents of a particular column. Only
those rows that meet the condition applied to the column are included
in the results. If the comparison value is text, then it must be
enclosed in single quotes.
Suppose you wanted to find the roleID, roleName, and gender for those roles in the movie with movieID equal to '014'.
This query is applied to the Role table. Although movieID is the
primary key of the Movie table, it also appears in the Role table as a
foreign key. You are making a condition on the movieID column, but it
is not necessary to include this column in the results. The movieID
value in the query has to appear in single quotes because movieID is a
text column. Example Query 2 illustrates this.
Example Query 2
SELECT roleID, roleName, gender
FROM Role
WHERE movieID = '014';
The complete output of Example Query 2 is shown in Example Query 2 Results.
Example Query 2 Results
| roleID | roleName | gender |
|---|
| 00037 | The Terminator | M |
| 00038 | Kyle Reese | M |
| 00039 | Sarah Connor | F |
3 Selection on Number Columns
The WHERE
keyword works in the same way for number columns as it does for text
columns. The inequality operators work for text columns, but they are
more commonly used for number columns. When you use a numeric value in
a query, that value cannot contain any commas and can contain at most
one decimal point. Otherwise, the value can consist of only digits.
Suppose you wanted to find the
movieID, title, year, and totalNoms of all movies that received more
than 10 Academy Award nominations. Example Query 3 shows this query.
Example Query 3
SELECT movieID, title, year, totalNoms
FROM Movie
WHERE totalNoms > 10;
The complete output of Example Query 3 is shown in the Example Query 3 Results.
Example Query 3 Results
| movieID | title | year | totalNoms |
|---|
| 007 | Forrest Gump | 1994 | 13 |
| 009 | Gone with the Wind | 1939 | 15 |
| 024 | Amadeus | 1984 | 11 |
4. Selection on Currency Columns
Currency columns are
displayed with dollar ($) signs and two decimal places, but in queries
they work just like regular number columns. When comparing a currency
column with a numeric value in the query, the numeric value cannot
contain a dollar sign or any commas and can contain at most one decimal
point. Otherwise, it can consist of only digits.
Suppose you wanted to find the
movieID, title, year, DVDPrice, and discountPrice of all movies where
the discountPrice is greater than or equal to $24.99. Example Query 4
shows this query.
Example Query 4
SELECT movieID, title, year, DVDPrice, discountPrice
FROM Movie
WHERE discountPrice >= 24.99;
The complete output of Example Query 4 is shown in the Example Query 4 Results.
Example Query 4 Results
| movieID | title | year | DVDPrice | discountPrice |
|---|
| 011 | Naked City, The | 1948 | $29.99 | $29.99 |
| 019 | Batman | 1989 | $26.99 | $24.99 |
| 020 | Batman Returns | 1992 | $26.99 | $24.99 |
| 021 | Batman Forever | 1995 | $26.99 | $24.99 |
5. Selection on Date Columns
A date value can be given in
mm/dd/yy or mm/dd/yyyy form, using digits for the month, day, and year
values. If desired, you can use dashes as separators instead of
slashes. Dates are composite data types, consisting of three parts.
Each of the individual parts is numeric in nature. Dates have a
property in common with text fields: They have to be enclosed in
punctuation marks. This practice prevents the slashes or dashes used as
separators in them from being interpreted as arithmetic operations. A
date value can be enclosed in # signs rather than single quotes. The #
signs serve as a reminder that dates have a numeric aspect.
Suppose you wanted to find the
actorID, lastName, firstName, middleName, suffix, and birthDate of all
actors who were born before January 1, 1885. Example Query 5 shows
this query.
Example Query 5
SELECT actorID, lastName, firstName, middleName, suffix,
birthDate
FROM Actor
WHERE birthDate < #1/1/1885#;
The complete output of Example Query 5 is shown in the Example Query 5 Results.
Example Query 5 Results
| actorID | lastName | firstName | middleName | suffix | birthDate |
|---|
| 00068 | Blandick | Clara | | | 6/4/1880 |
| 00012 | Greenstreet | Sydney | | | 12/27/1879 |
| 00096 | Grapewin | Charley | | | 12/20/1869 |
6. Selection with NULL, IS, and NOT
NULL is a keyword that signifies a column contains no value for a given row. The keyword NULL is not a value, so it should not be enclosed in quotes. When checking to see whether a column contains NULL, instead of using the equality operator, (=), you use the keyword IS.
Every person is born on some
date, but not all of the actors have birthdates recorded for them in
the Actor table. Suppose you wanted to find the actorID, lastName,
firstName, middleName, and suffix of all actors whose birthDate is
null. Example Query 6 shows this query.
Example Query 6
SELECT actorID, lastName, firstName, middleName, suffix
FROM Actor
WHERE birthDate IS NULL;
The complete output of Example Query 6 is shown in the Example Query 6 Results.
Example Query 6 Results
| actorID | lastName | firstName | middleName | suffix |
|---|
| 00033 | Humphreys | Michael | Conner | |
| 00069 | | Terry | | |
If you want to test for inequality with NULL, you need to use the keyword IS with the keyword NOT.
Suppose you wanted to find the actorID, lastName, firstName,
middleName, and suffix of all actors whose suffix is not null. Example
Query 7 shows this query.
Example Query 7
SELECT actorID, lastName, firstName, middleName, suffix
FROM Actor
WHERE suffix IS NOT NULL;
The complete output of Example Query 7 is shown in the Example Query 7 Results.
Example Query 7 Results
| actorID | lastName | firstName | middleName | suffix |
|---|
| 00047 | Gooding | Cuba | | jr. |
7. Queries with the Keyword LIKE
Quite often with text
fields the user does not have a perfect recollection of the value or
values of interest. If a query is written using the = sign, results
will be returned only on an exact match. It is possible to write more
flexible queries using the keyword LIKE
along with the wildcard symbol, *. In this usage, the* represents any
sequence of characters that can appear in a text column. A value for
comparison is formed by mixing * with actual letters, digits, and so
forth, and enclosing the result in single quotes. For example, when
used with the keyword LIKE, the value ' Batman*' would match with any movie title that started with the word "Batman." It is important to note that LIKE is used in place of the = sign and does not require the keyword IS. If you wrote a query containing IS LIKE, it would not work.
Suppose you wanted to find
the movieID and title of the movies where the title starts with
"Batman." Example Query 8 shows this query.
Example Query 8
SELECT movieID, title
FROM Movie
WHERE title LIKE 'Batman*';
The complete output of Example Query 8 is shown in the Example Query 8 Results.
Example Query 8 Results
| movieID | title |
|---|
| 019 | Batman |
| 020 | Batman Returns |
| 021 | Batman Forever |
| 022 | Batman & Robin |
| 023 | Batman Begins |
8. The Logical Operators AND and OR
More than one condition can be included after the WHERE in a query by combining the conditions with the keywords AND or OR. If conditions are combined with AND, each one of the conditions has to be true for the combination to be true. If conditions are combined with OR, if one or both of the conditions are true, then the combination is true. Both AND and OR
can be applied to conditions that are placed on the same column or on
different columns. If there are two conditions on the same column, each
of the conditions has to be complete; they cannot be shortened. For
example, you can write WHERE X < 3 OR X > 5, but you cannot write WHERE X < 3 OR > 5. It is also incorrect to use WHERE more than once, so you cannot write WHERE X < 3 OR WHERE X > 5.
Suppose you wanted to
find the actorID, lastName, firstName, middleName, suffix, and
birthDate of all female actors who were born after January 1, 1975. The
two conditions that you want to hold true are the facts that the actor
is female and that the actor was born after January 1, 1975. This case
is an example of a query using AND where the two conditions are applied to two different columns. Example Query 9 shows this query.
Example Query 9
SELECT actorID, lastName, firstName, middleName, suffix,
birthDate
FROM Actor
WHERE gender = 'F'
AND birthDate > #1/1/1975#;
The complete output of Example Query 9 is shown in the Example Query 9 Results.
Example Query 9 Results
| actorID | lastName | firstName | middleName | suffix | birthDate |
|---|
| 00025 | Hoffman | Gaby | | | 1/8/1982 |
| 00085 | Barrymore | Drew | | | 2/22/1975 |
| 00089 | Silverstone | Alicia | | | 10/4/1976 |
| 00094 | Holmes | Katie | | | 12/18/1978 |
Suppose you'd like to find the
movieID, title, DVDPrice, and discountPrice of all movies where the
DVDPrice is either less than $10.00 or greater than $27.00. This case
is an example of a query using OR where the two conditions are applied to the same column. Example Query 10 shows this query.
Example Query 10
SELECT movieID, title, DVDPrice, discountPrice
FROM Movie
WHERE DVDPrice < 10.00
OR DVDPrice > 27.00;
The complete output of Example Query 10 is shown in the Example Query 10 Results.
Example Query 10 Results
| movieID | title | DVDPrice | discountPrice |
|---|
| 011 | Naked City, The | $29.99 | $29.99 |
| 013 | Sudden Impact | $9.99 | $9.99 |
| 015 | Terminator 2: Judgment Day | $9.99 | $7.99 |
It is possible to create arbitrarily complex WHERE conditions by using AND, OR, NOT, and parentheses to group expressions.
