1 Selection Queries with DISTINCT
Not all features of SQL can be combined in the same query, but the majority can. For example, you can easily use the keyword DISTINCT when writing a query that uses WHERE.
Suppose you wanted to find
the distinct discountPrice values of movies that have won Academy
Awards. Example Query 1 shows this query.
Example Query 1
SELECT DISTINCT discountPrice
FROM Movie
WHERE awardsWon <> 0;
The complete output of Example Query 1 is shown in the Example Query 5-1 Results.
Example Query 1 Results
| discountPrice |
|---|
| $7.99 |
| $9.99 |
| $14.99 |
| $17.99 |
| $22.99 |
| $24.99 |
| $29.99 |
2 Selection Queries with Calculated Columns and DISTINCT
Calculated columns can be used in queries containing the keyword WHERE, and DISTINCT
can be used with calculated columns. For example, suppose you wanted to
find the distinct differences between the DVDPrice and the
discount-Price of movies that have won Academy Awards. Example Query 2
shows this query.
Example Query 2
SELECT DISTINCT DVDPrice - discountPrice
FROM Movie
WHERE awardsWon <> 0;
The complete output of Example Query 2 is shown in the Example Query 2 Results.
Example Query 2 Results
| Expr1000 |
|---|
| $0.00 |
| $2.00 |
| $3.00 |
| $5.00 |
3 Selection Queries with GROUP BY
GROUP BY can also be used in queries containing the keyword WHERE.
For example, suppose you wanted to find the movieID and the count of
the roleID values in it for female roles only, grouped by movieID. In
other words, suppose you wanted to find how many female roles were
recorded in the database for each movie. Example Query 3 shows this
query.
Example Query 3
SELECT movieID, COUNT(roleID)
FROM Role
WHERE gender = 'F'
GROUP BY movieID;
A representative subset of the output of Example Query 5-3 is shown in the Example Query 3 Results.
Example Query 3 Results
| movieID | Expr1001 |
|---|
| . . . | . . . |
| 016 | 6 |
| 017 | 3 |
| 018 | 2 |
| 019 | 1 |
| . . . | . . . |
4 The Join and Qualified Column Names
In a correctly designed database,
for every kind of entity there is a separate table, and the information
about one instance of the entity is stored in a row of the table. The
information in a database consists of both the information stored in
individual tables and the relationships between entries in different
tables. Take actors and roles, for example. One actor can play many
roles. The actorID exists as a primary key in the Actor table and as a
foreign key in the Role table. The presence of the actorID column in
both tables forms a primary key to foreign key relationship. This
relationship between the columns in the tables captures the relationship
between the actor and role entities stored in the tables.
Table 1 contains a subset of the Actor table, and Table 2
contains a subset of the Role table. Tom Hanks' actorID, 00001, appears
twice as a foreign key value in the subset of the Role table. From
these occurrences, you can determine that Tom Hanks played the roles of
Forrest Gump and Jim Lovell. Gary Sinise's actorID, 00004, appears once
as a foreign key value in the subset of the Role table. From this
occurrence, you can determine that Gary Sinise played the role of
Lieutenant Dan Taylor.
Table 1. Actor
| actorID | lastName | firstName | middleName | suffix | gender | birthDate | deathDate |
|---|
| 00001 | Hanks | Tom | | | M | 7/9/1956 | |
| 00002 | Paxton | Bill | | | M | 5/17/1955 | |
| 00003 | Bacon | Kevin | | | M | 7/8/1958 | |
| 00004 | Sinise | Gary | | | M | 3/17/1955 | |
| . . . | . . . | . . . | | | . . . | . . . | . . . |
Table 2. Role
| roleID | roleName | gender | actorID | movieID |
|---|
| . . . | . . . | . . . | . . . | . . . |
| 00057 | Forrest Gump | M | 00001 | 007 |
| 00058 | Jenny Curran | F | 00030 | 007 |
| 00059 | Lieutenant Dan Taylor | M | 00004 | 007 |
| 00060 | Private Benjamin Buford "Bubba" Blue | M | 00031 | 007 |
| 00061 | Mrs. Gump | F | 00032 | 007 |
| 00062 | Young Forrest Gump | M | 00033 | 007 |
| 00063 | Jim Lovell | M | 00001 | 001 |
| . . . | . . . | . . . | . . . | . . . |
Every example query up to this
point has retrieved information from just one table. In SQL, however,
you can also retrieve information from more than one related table at a
time. The word "join" is used to describe this kind of query. SQL has
several alternative ways of forming join queries—but note that the way
shown here does not use a keyword named "JOIN."
The relationships between entities may be one-to-one, one-to-many, or
many-to-many. For any pair of tables that form a relationship, the
primary key of the one table is embedded as a foreign key in the many
table. The presence of the primary key of one table as a foreign key in
another is the basis for a join query. This query is structured to find
matches between the corresponding columns in the two tables. With the
Actor and Role tables, for example, you would be interested in finding
the rows in the Actor table with a given actorID and finding the rows in
the Role table that have a matching value of actorID.
Suppose you wanted to write an
SQL query to these specifications: Each row in the results would
contain the complete information for one of the actors, followed by the
complete information for one of the roles the actor played. Altogether
there would be one row in the results for each role. Because an actor
can play more than one role, the information for an actor could appear
in more than one row of the results. Example Query 4 illustrates this
idea. Explanations of the syntax will follow.
Example Query 4
SELECT *
FROM Actor, Role
WHERE Actor.actorID = Role.actorID;
Line by line, this is what you find in Example Query 4:
The SELECT
expression is not unusual. The * symbol is used to keep the query
uncomplicated. The Actor table has 8 columns, and the Role table has 5
columns. Thus, the results would have 13 columns. This is too many
columns to show conveniently on the printed page, so the results of this
query are not provided here. If desired, the results can be viewed by
running Example Query 4 in the example database.
The two tables involved in the join are listed following the FROM keyword, separated by a comma. If a query involves more than one table, each one has to be included in this way.
The join is defined after the WHERE keyword. This is where the corresponding columns of the two tables are given. The condition Actor.actorID = Role.actorID
specifies that the rows of interest in the two tables are those rows
where the actorID values are the same. Because the columns of interest
in the two tables have the same name, the columns' names must be
qualified by the name of the table in which they appear. Dot notation is
used for this purpose.
