What You’ll Learn in This Hour:
▶ Defining a database query
▶ Using the SELECT statement
▶ Adding conditions to queries using the WHERE clause
▶ Using column aliases
▶ Selecting data from another user’s table
In this hour, you learn about database queries, which involve using the SELECT statement. The SELECT statement is the most frequently used of all SQL commands after a database is created. The SELECT statement enables you to view data that is stored in the database.
SELECT StatementThe SELECT statement, which is known as the Data Query Language (DQL) command, is the basic statement used to construct database queries. A query is an inquiry into the database to extract data from the database in a readable format according to the user’s request. For instance, in the sample database, you have the BIRDS table. You might issue a SQL statement that returns the smallest bird in the database based on columns such as WINGSPAN, HEIGHT, and WEIGHT. This request to the database for usable bird information is a typical query that can be performed in a relational database.
The SELECT statement is by far one of the most powerful statements in SQL. The SELECT statement is not a standalone statement; one or more additional clauses (elements) are required for a syntactically correct query. In addition, optional clauses increase the overall functionality of the SELECT statement. The FROM clause is a mandatory clause and must always be used with the SELECT statement.
Four keywords, or clauses, are valuable parts of a SELECT statement:
▶ SELECT
▶ FROM
▶ WHERE
▶ ORDER BY
Each of these clauses is covered in detail in the following sections.
The capability to effectively and simply retrieve useful data is what SQL and the relational database are all about. Writing SQL queries is simple. Queries allow for fast retrieval of data, they are powerful, and they enable you to easily view deeper perspectives of the data within your database. Writing queries in SQL is very English-like, or language-like. For example, consider these examples of questions a user might ask to get useful information from the BIRDS database:
▶ How many types of birds are in the database?
▶ Which bird has the most diverse diet?
▶ Which is the largest bird?
▶ Which birds have a wingspan that is greater than average?
▶ In which species of birds do both males and females contribute to building the nest?
▶ List all birds in order of their size.
▶ Which bird spends the most total time with its young?
▶ What percentage of birds eat fish?
▶ Which birds are “big” (very subjective)?
SQL makes it easy to communicate with a relational database to ask questions such as these and get useful information back.
SELECT ClauseThe SELECT statement is used with the FROM clause to extract data from the database in an organized, readable format. The SELECT clause of the query is used to select the data you want to see, according to the columns in which they are stored in a table.
The syntax for a simple SELECT statement follows:
SELECT [ * | ALL | DISTINCT COLUMN1, COLUMN2 ] FROM TABLE1 [ , TABLE2 ];
The SELECT clause in a query is followed by a comma-delimited list of column names that you want displayed as part of the query output. The asterisk (*) denotes that all columns in a table should display as part of the output. Check your particular implementation for its usage. The ALL option displays all values for a column, including duplicates. The DISTINCT option suppresses duplicate rows from displaying in the output. The ALL option is an inferred option; think of it as the default—therefore, it does not necessarily need to be used in the SELECT statement. The FROM keyword is followed by a list of one or more tables from which you want to select data. Notice that the columns following the SELECT clause are separated by commas, as is the table list following the FROM clause.
Note
Constructing Lists
Commas separate arguments in a list in SQL statements. Arguments are values that are either required or optional to the syntax of a SQL statement or command. Some common lists include lists of columns in a query, lists of tables to be selected from in a query, values to be inserted into a table, and values grouped as a condition in a query’s WHERE clause.
First, you create two tables here based on the BIRDS table. This is the same syntax that was introduced previously in this book. The first table, called BIG_BIRDS, lists all data about birds whose WINGSPAN is greater than 48 inches. The second table, SMALL_BIRDS, is also based on the BIRDS table and includes any bird that has a WINGSPAN less than or equal to 48 inches. These two tables are used for two reasons. One reason is simply for readability; the BIRDS table has a lot of columns that would wrap on the page in this book. The second reason is to show you how to separate data and categorize it, or look at it differently, as you begin to query a database.
SQL> create table big_birds as 2 select * from birds 3 where wingspan > 48; Table created. SQL> create table small_birds as 2 select * from birds 3 where wingspan <= 48; Table created.
Now let’s perform a simple query from the BIG_BIRDS table to show all records, or rows of data, in that new table. Following that output are the data results from a query showing all records from the SMALL_BIRDS table.
SQL> select * from big_birds;
BIRD_ID BIRD_NAME HEIGHT WEIGHT WINGSPAN
---------- ------------------------------ ---------- ---------- ----------
1 Great Blue Heron 52 5.5 78
3 Common Loon 36 18 54
4 Bald Eagle 37 14 84
5 Golden Eagle 40 15 90
7 Osprey 24 3 72
9 Canadian Goose 43 14 72
13 Ring-billed Gull 19 1.1 50
14 Double-crested Cormorant 33 5.5 54
16 Turkey Vulture 32 3.3 72
19 Mute Swan 60 26 94.8
20 Brown Pelican 54 7.6 90
21 Great Egret 38 3.3 67.2
12 rows selected.
SQL> select * from small_birds;
BIRD_ID BIRD_NAME HEIGHT WEIGHT WINGSPAN
---------- ------------------------------ ---------- ---------- ----------
2 Mallard 28 3.5 3.2
6 Red Tailed Hawk 25 2.4 48
8 Belted Kingfisher 13 .33 23
10 Pied-billed Grebe 13 1 6.5
11 American Coot 16 1 29
12 Common Sea Gull 18 1 18
15 Common Merganser 27 3.2 34
17 American Crow 18 1.4 39.6
18 Green Heron 22 .4 26.8
22 Anhinga 35 2.4 42
23 Black Skimmer 20 1 15
11 rows selected.
Notice that, between the two queries, 23 rows of data are returned. The BIRDS table itself has 23 rows of data, or 23 distinct birds, in the database. Therefore, based on the criteria of these two CREATE TABLE statements, all 23 rows of data should be returned from the BIRDS table itself. The first query in the CREATE TABLE statement looks for any birds that have a wingspan greater than 48 inches. The second CREATE TABLE statement looks for birds that have a wingspan of less than or equal to 48 inches in length.
The asterisk represents all columns in the table. Each column in the output displays in the order that it appears in the table. This table has 23 records, identified by the feedback (12 rows selected and 11 rows selected). This feedback message differs among implementations; for example, another feedback for the same query might be 14 rows affected. Although the asterisk is a helpful piece of shorthand when writing SQL queries, some people consider it best practice to explicitly name the columns that you are returning.
DISTINCT Values from a TableA table can have thousands or millions of rows of data. Sometimes you need to see only distinct or unique values. SQL provides a DISTINCT keyword, or function, that can be used in a query to show only the distinct or unique values within a column of a table. The following example shows a query that returns only the distinct values of the NEST_BUILDER column from the BIRDS table. The NEST_BUILDER column in the BIRDS table is a one-character code that represents male and female birds: F for female, M for male, or N for neither.
SQL> select distinct nest_builder 2 from birds; N - B F N 3 rows selected.
You can also use DISTINCT with parentheses enclosing the associated column, as follows. Parentheses are often used in SQL—as well as many other languages—to improve readability. In the following examples, you select the distinct number of broods from the BIRDS table for a bird. The number of broods represents the number of times birds lay eggs within a season or a year. The first query shows the same results as the first query. The only difference is that the column name is enclosed by parentheses, which is optional in most cases and assists in readability.
SQL> select distinct broods
2 from birds;
BROODS
----------
1
2
2 rows selected.
SQL> select distinct(broods)
2 from birds;
BROODS
----------
1
2
2 rows selected.
FROM ClauseThe FROM clause must be used with the SELECT statement. It is a required element for any query. The purpose of the FROM clause is to tell the database what table(s) to access to retrieve the wanted data for the query. The FROM clause may contain one or more tables. The FROM clause must always list at least one table.
The syntax for the FROM clause follows:
from table1 [ , table2 ]
WHERE ClauseA condition is part of a query that displays selective information as specified by the user. The value of a condition is either TRUE or FALSE, thereby limiting the data received from the query. The WHERE clause places conditions on a query by eliminating rows that would normally be returned by a query without conditions.
You can have more than one condition in the WHERE clause. If more than one condition exists, the conditions connect by the AND and OR operators, which are discussed during Hour 13, “Using Operators to Categorize Data.” As you also learn during the next hour, several conditional operators enable you to specify conditions in a query. This hour deals with only a single condition for each query.
An operator is a character or keyword in SQL that combines elements in a SQL statement.
The syntax for the WHERE clause follows:
select [ all | * | distinct column1, column2 ] from table1 [ , table2 ] where [ condition1 | expression1 ] [ and|OR condition2 | expression2 ]
The following example queries the BIRDS table to show only the BIRD_ID and the BIRD_NAME, but only for birds whose BIRD_NAME is equal to Great Blue Heron.
SQL> select bird_id, bird_name
2 from birds
3 where bird_name = 'Great Blue Heron';
BIRD_ID BIRD_NAME
---------- ------------------------------
1 Great Blue Heron
1 row selected.
The following example uses the BIRD_ID that was obtained from the previous query to select all the nicknames associated with the Great Blue Heron, or any bird that has a BIRD_ID of 1. This is a manual way of selecting data from two tables. Getting information from two tables (joining tables) is discussed in great deal in Hour 14, “Joining Tables in Queries.” In this example, the Great Blue Heron has two nicknames stored in the database, Big Cranky and Blue Crane.
SQL> select nickname 2 from nicknames 3 where bird_id = 1; NICKNAME ------------------------------ Big Cranky Blue Crane 2 rows selected.
Sometimes when querying the database, you are searching not for equality but for potential multiple records. The following example produces a list of birds that have wingspan of greater than 48 inches. Again, this is a similar query used to create a table called BIG_BIRDS that has a subset of data from the main BIRDS table.
SQL> select bird_name, height, weight, wingspan 2 from birds 3 where wingspan > 48; BIRD_NAME HEIGHT WEIGHT WINGSPAN ------------------------------ ---------- ---------- ---------- Great Blue Heron 52 5.5 78 Common Loon 36 18 54 Bald Eagle 37 14 84 Golden Eagle 40 15 90 Osprey 24 3 72 Canadian Goose 43 14 72 Ring-billed Gull 19 1.1 50 Double-crested Cormorant 33 5.5 54 Turkey Vulture 32 3.3 72 Mute Swan 60 26 94.8 Brown Pelican 54 7.6 90 Great Egret 38 3.3 67.2 12 rows selected.
Note
Conditions in a Query
Conditions do not always have to be exact matches of exact terms. Sometimes you want a range of values, as you see in the previous query. There you want to see only birds that have wingspan greater than 48 inches. This is actually the same query used previously to create a table called BIG_BIRDS, based on the BIRDS table.
The following example shows two queries. The first query returns a list of birds that lay exactly five eggs. The second query returns a list of birds that lay more than five eggs.
SQL> select bird_name, eggs 2 from birds 3 where eggs = 5; BIRD_NAME EGGS ------------------------------ ---------- Great Blue Heron 5 Black Skimmer 5 2 rows selected. SQL> select bird_name, eggs 2 from birds 3 where eggs > 5; BIRD_NAME EGGS ------------------------------ ---------- Mallard 10 Belted Kingfisher 7 Canadian Goose 10 Pied-billed Grebe 7 American Coot 12 Common Merganser 11 American Crow 6 Mute Swan 8 8 rows selected.
ORDER BY ClauseYou usually want your output to have some kind of order. Data can be sorted using the ORDER BY clause, which arranges the results of a query in a listing format that you specify. The default ordering of the ORDER BY clause is ascending order; the sort displays in the order A–Z if it is sorting output names alphabetically. Descending order for alphabetical output is displayed in the order Z–A. Ascending order for output for numeric values between 1 and 9 is displayed as 1–9; descending order is displayed as 9–1.
The syntax for the ORDER BY clause follows:
select [ all | * | distinct column1, column2 ] from table1 [ , table2 ] where [ condition1 | expression1 ] [ and|OR condition2 | expression2 ] ORDER BY column1|integer [ ASC|DESC ]
You begin your exploration of the ORDER BY clause here with an extension of one of the previous statements. Suppose that you want to see a list of birds and their associated wingspans from the BIRDS table for birds whose wingspan is greater than 40 inches, but we want to order it by the WINGSPAN column. By default, the ORDER BY clause orders from least to greatest—in other words, from 0 to 10 or from A to Z. In this example, then, birds with the smallest wingspan are listed first, up to the birds with the greatest wingspan.
SQL> select bird_name, wingspan 2 from birds 3 where wingspan > 48 4 order by wingspan; BIRD_NAME WINGSPAN ------------------------------ ---------- Ring-billed Gull 50 Double-crested Cormorant 54 Common Loon 54 Great Egret 67.2 Osprey 72 Canadian Goose 72 Turkey Vulture 72 Great Blue Heron 78 Bald Eagle 84 Brown Pelican 90 Golden Eagle 90 Mute Swan 94.8 12 rows selected.
Note
Rules for Sorting
SQL sorts are ASCII, character-based sorts. The numeric values 0–9 are sorted as character values and are sorted before the characters A–Z. Because numeric values are treated like characters during a sort, an example list of numeric values is sorted in the following order: 1, 12, 2, 255, 3.
You can use DESC, as in the following statement, if you want the same output to be sorted in reverse alphabetical order, or descending order. This example works exactly the same as the previous one, except that you sort the results from the query to show the birds with the greatest wingspan first, all the way down to the birds with the smallest wingspan. Thus, here you show the bigger birds first in the result set.
SQL> select bird_name, wingspan 2 from birds 3 where wingspan > 48 4 order by wingspan desc; BIRD_NAME WINGSPAN ------------------------------ ---------- Mute Swan 94.8 Brown Pelican 90 Golden Eagle 90 Bald Eagle 84 Great Blue Heron 78 Osprey 72 Canadian Goose 72 Turkey Vulture 72 Great Egret 67.2 Common Loon 54 Double-crested Cormorant 54 Ring-billed Gull 50 12 rows selected.
Tip
There Is a Default for Ordering
Because ascending order for output is the default, you do not have to specify ASC.
Shortcuts do exist in SQL. A column listed in the ORDER BY clause can be abbreviated with an integer. An integer is a substitution for the actual column name (an alias, for the purpose of the sort operation), identifying the position of the column after the SELECT keyword.
An example of using an integer as an identifier in the ORDER BY clause follows:
SQL> select bird_name, wingspan 2 from birds 3 where wingspan > 48 4 order by 1; BIRD_NAME WINGSPAN ------------------------------ ---------- Bald Eagle 84 Brown Pelican 90 Canadian Goose 72 Common Loon 54 Double-crested Cormorant 54 Golden Eagle 90 Great Blue Heron 78 Great Egret 67.2 Mute Swan 94.8 Osprey 72 Ring-billed Gull 50 Turkey Vulture 72 12 rows selected.
In this query, the integer 1 represents the column BIRD_NAME, and 2 theoretically represents the WINGSPAN column.
You can order by multiple columns in a query, using either the column name or the associated number of the column in the SELECT:
ORDER BY 1,2,3
Columns in an ORDER BY clause are not required to appear in the same order as the associated columns following the SELECT, as the following example shows:
ORDER BY 1,3,2
The order in which the columns are specified within the ORDER BY clause is the manner in which the ordering process is done. The following statement orders first by the LASTNAME column and then by the FIRSTNAME column:
ORDER BY BIRD_NAME,NICKNAME
Case sensitivity is an important concept to understand when coding with SQL. Typically, SQL commands and keywords are not case sensitive, which enables you to enter your commands and keywords in either upper or lower case, as you prefer. The case may also be mixed (both upper and lower case for a single word or statement), which is often referred to as CamelCase. See Hour 10, “Manipulating Data,” for more on case sensitivity.
Collation is the mechanism that determines how the relational database management system (RDBMS) interprets data. This includes methods of ordering the data, as well as case sensitivity. Case sensitivity in your data is important because it determines how your WHERE clauses, among other components, interpret matches. Check with your specific RDBMS implementation to determine the default collation on your system. Some systems, such as MySQL and Microsoft SQL Server, have a default collation that is not case sensitive; this means that it matches strings without considering their case. Other systems, such as Oracle, have a default collation that is case sensitive; this means that strings are matched with case taken into account. Because case sensitivity is a factor at the database level, its importance as a factor in your queries varies.
Caution
Use a Standard Case in Your Queries
It is a good practice to use the same case in your query as the data that is stored in your database. Moreover, good corporate policy ensures that data entry is handled in the same manner across an enterprise.
Case sensitivity is a factor in maintaining data consistency within your RDBMS. For instance, your data is not consistent if you arbitrarily enter your data using random case:
▶ BALD EAGLE
▶ Bald Eagle
▶ bald eagle
In the BIRDS database, data is stored in mixed case. This is a matter of preference and can vary between different databases—for that matter, your own preference can vary. In the BIRDS database, the value for Bald Eagle is stored as Bald Eagle. With that in mind, review the following queries and the output of the queries; make sure you understand why rows are returned in some instances and no rows are returned in other instances because of the specific data (including case) being searched for within the query.
SQL> select bird_name 2 from birds 3 where bird_name = 'BALD EAGLE'; no rows selected SQL> select bird_name 2 from birds 3 where bird_name = 'bald eagle'; no rows selected SQL> select bird_name 2 from birds 3 where bird_name = 'Bald Eagle'; BIRD_NAME ------------------------------ Bald Eagle 1 row selected. SQL> SELECT BIRD_NAME 2 FROM BIRDS 3 WHERE BIRD_NAME = 'Bald Eagle'; BIRD_NAME ------------------------------ Bald Eagle 1 row selected.
This section provides several examples of queries based on the concepts that have already been discussed. It begins with the simplest query you can issue and progressively builds upon the initial query. All queries use the BIRDS table.
Select all records from the FOOD table and display all columns:
SQL> select * from food;
FOOD_ID FOOD_NAME
---------- ------------------------------
1 Seeds
2 Birds
3 Fruit
4 Frogs
5 Fish
6 Berries
7 Aquatic Plants
8 Aquatic Insects
9 Worms
10 Nuts
11 Rodents
12 Snakes
13 Small Mammals
14 Nectar
15 Pollen
16 Carrion
17 Moths
18 Ducks
19 Insects
20 Plants
21 Corn
22 Crayfish
23 Crustaceans
24 Reptiles
25 Deer
25 rows selected.
Select the BIRD_ID, BIRD_NAME, and HEIGHT from the BIRDS table for only birds that have a height greater than 50 inches. Sort the results by HEIGHT in ascending order, which is the default.
SQL> select bird_id, bird_name, height
2 from birds
3 where height > 50
4 order by height;
BIRD_ID BIRD_NAME HEIGHT
---------- ------------------------------ ----------
1 Great Blue Heron 52
20 Brown Pelican 54
19 Mute Swan 60
3 rows selected.
Create a report that shows the identification of the bird, the bird’s name, the bird’s wingspan, the height, and the weight of only birds that have a wingspan greater than 48 inches. Sort the results by the bird’s wingspan first, then height and weight progressively.
SQL> select bird_id, bird_name, wingspan, height, weight
2 from birds
3 where wingspan > 48
4 order by wingspan, height, weight;
BIRD_ID BIRD_NAME WINGSPAN HEIGHT WEIGHT
---------- ------------------------------ ---------- ---------- ----------
13 Ring-billed Gull 50 19 1.1
14 Double-crested Cormorant 54 33 5.5
3 Common Loon 54 36 18
21 Great Egret 67.2 38 3.3
7 Osprey 72 24 3
16 Turkey Vulture 72 32 3.3
9 Canadian Goose 72 43 14
1 Great Blue Heron 78 52 5.5
4 Bald Eagle 84 37 14
5 Golden Eagle 90 40 15
20 Brown Pelican 90 54 7.6
19 Mute Swan 94.8 60 26
12 rows selected.
Perform the same query as the previous one, but sort the results by wingspan, height, and weight in descending order.
SQL> select bird_id, bird_name, wingspan, height, weight
2 from birds
3 where wingspan > 48
4 order by wingspan desc, height desc, weight desc;
BIRD_ID BIRD_NAME WINGSPAN HEIGHT WEIGHT
---------- ------------------------------ ---------- ---------- ----------
19 Mute Swan 94.8 60 26
20 Brown Pelican 90 54 7.6
5 Golden Eagle 90 40 15
4 Bald Eagle 84 37 14
1 Great Blue Heron 78 52 5.5
9 Canadian Goose 72 43 14
16 Turkey Vulture 72 32 3.3
7 Osprey 72 24 3
21 Great Egret 67.2 38 3.3
3 Common Loon 54 36 18
14 Double-crested Cormorant 54 33 5.5
13 Ring-billed Gull 50 19 1.1
12 rows selected.
Write a query that displays the bird identification, the bird name, the wingspan, the height, and the weight of all birds that have a wingspan greater than 40 inches, but sort the results according to the values in the columns of WINGSPAN, HEIGHT, and WEIGHT in progressive order using integers instead of the column name itself.
SQL> select bird_id, bird_name, wingspan, height, weight
2 from birds
3 where wingspan > 48
4 order by 3, 4, 5;
BIRD_ID BIRD_NAME WINGSPAN HEIGHT WEIGHT
---------- ------------------------------ ---------- ---------- ----------
13 Ring-billed Gull 50 19 1.1
14 Double-crested Cormorant 54 33 5.5
3 Common Loon 54 36 18
21 Great Egret 67.2 38 3.3
7 Osprey 72 24 3
16 Turkey Vulture 72 32 3.3
9 Canadian Goose 72 43 14
1 Great Blue Heron 78 52 5.5
4 Bald Eagle 84 37 14
5 Golden Eagle 90 40 15
20 Brown Pelican 90 54 7.6
19 Mute Swan 94.8 60 26
12 rows selected.
Caution
Ensure That Your Queries Are Constrained
When you select all rows of data from a large table, the results might return a substantial amount of data. In highly transactional databases, this can slow performance not only for the query that is executed but also for the system. Use WHERE clauses whenever possible to work on the smallest subset of your data as possible, to limit the effect your query has on precious database resources.
You can issue a simple query on a table to get a quick count of the number of records in the table or the number of values for a column in the table. A count is accomplished by the function COUNT. Although functions are not discussed until Hours 15 through 17, this function is introduced here because it is often part of one of the simplest queries you can create.
The syntax of the COUNT function follows:
SELECT COUNT(*) FROM TABLE_NAME;
The COUNT function is used with parentheses, which enclose the target column or the asterisk to count all rows of data in the table.
Tip
Counting Basics
Counting the number of values for a column is the same as counting the number of records in a table if the column being counted is NOT NULL (a required column). However, COUNT(*) is typically used to count the number of rows for a table.
The following example is a query that provides a count of the number of birds stored in the BIRDS table.
SQL> select count(*) from birds;
COUNT(*)
----------
23
1 row selected.
The next query provides a count of the birds that have an entry for the WINGSPAN column, which is an optional column, in the BIRDS table. In this situation, every bird in the database has an entry for WINGSPAN, even though it is optional.
SQL> select count(wingspan) from birds;
COUNT(WINGSPAN)
---------------
23
1 row selected.
The following example is the same as the first example; it provides a count of the birds in the BIRDS table. The only characteristic that makes this example different is that it specifies the column name of BIRD_ID (which happens to be a primary key and thus is a required column) instead of using the asterisk (*), which represents every row of data in the table. Again, the same output is derived in this situation.
SQL> select count(bird_id) from birds;
COUNT(BIRD_ID)
--------------
23
1 row selected.
The next query provides a count of the birds that have an entry for the WINGSPAN column, which is an optional column in the BIRDS table. In this situation, every bird in the database has an entry for WINGSPAN, even though it is optional.
This final example shows how to find out how many nicknames a specific bird has. In Hour 14, you learn how to easily join tables to get this information in a single query. This example demonstrates a manual, common-sense process for getting the same information and lays a foundation for better understanding table joins as you progress through your SQL journey.
Let’s say that you want to find out how many nicknames are in the database for the Great Blue Heron. First, you perform the following query from the BIRDS table to get the BIRD_ID for the Great Blue Heron from the database. You do this because only the BIRD_ID itself is stored in the NICKNAMES table, not the BIRD_NAME itself.
SQL> select bird_id
2 from birds
3 where bird_name = 'Great Blue Heron';
BIRD_ID
----------
1
Finally, you write a simple query to select a count of the birds associated with the Great Blue Heron from the NICKNAMES table. This query produces a simple numeric value of 2, which represents the number of nicknames in the NICKNAMES table that are stored at this point for the Great Blue Heron. If you manually look at the data set in the BIRDS table, you can easily verify that the Great Blue Heron has two nicknames associated with it.
SQL> select count(bird_id)
2 from nicknames
3 where bird_id = 1;
COUNT(BIRD_ID)
--------------
2
1 row selected.
Permission must be granted to a user to access another user’s table. If no permission has been granted, access is not allowed. You can select data from another user’s table after access has been granted. (Hour 20, “Creating and Using Views and Synonyms,” discusses the GRANT command.) To access another user’s table in a SELECT statement, precede the table name with the schema name or the username that owns (that is, created) the table, as in the following example:
SELECT BIRD_NAME FROM another_user.BIRDS;
Column aliases temporarily rename a table’s columns for the purpose of a particular query. The following syntax illustrates the use of column aliases:
SELECT COLUMN_NAME ALIAS_NAME FROM TABLE_NAME;
The following example is a simple query that shows the BIRD_NAME column. The literal string bird is used as a column alias, which you can see in the result set from the query. Notice that, by default (at least, within Oracle), the literal string of bird is converted to all upper case. Keep in mind that minor variations can occur between relational database implementations, but the concept is the same.
SQL> select bird_name bird 2 from birds 3 where wingspan > 48; BIRD ------------------------------ Great Blue Heron Common Loon Bald Eagle Golden Eagle Osprey Canadian Goose Ring-billed Gull Double-crested Cormorant Turkey Vulture Mute Swan Brown Pelican Great Egret 12 rows selected.
In Oracle, for example, if you want to create an alias that is more than one word, you simply use double quotation marks to identify the alias, as in the following example.
SQL> select bird_name "BIG BIRD" 2 from birds 3 where wingspan > 48; BIG BIRD ------------------------------ Great Blue Heron Common Loon Bald Eagle Golden Eagle Osprey Canadian Goose Ring-billed Gull Double-crested Cormorant Turkey Vulture Mute Swan Brown Pelican Great Egret 12 rows selected.
You can use column aliases to customize names for column headers and reference a column with a shorter name in some SQL implementations.
Note
Aliasing a Column in a Query
When a column is renamed in a SELECT statement, the name is not a permanent change. The change is only for that particular SELECT statement.
This hour introduced you to the database query, a means of obtaining useful information from a relational database. The SELECT statement creates queries in SQL. You must include the FROM clause with every SELECT statement. You learned how to place a condition on a query using the WHERE clause and how to sort data using the ORDER BY clause. You also learned the fundamentals of writing queries. After a few exercises, you should be prepared to learn more about queries during the next hour.
Q. Why doesn’t the SELECT clause work without the FROM clause?
A. The SELECT clause merely tells the database what data you want to see. The FROM clause tells the database where to get the data.
Q. What is the purpose of using the DISTINCT option?
A. The DISTINCT option causes the query to suppress duplicate rows of columns from appearing in the result set.
Q. When I use the ORDER BY clause and choose the descending option, what does that actually do to the data?
A. Say that you use the ORDER BY clause and select BIRD_NAME from the BIRDS table. If you use the descending option, the order starts with the letter Z and finishes with the letter A. Now say that you use the ORDER BY clause and select the WINGSPAN from BIRDS. If you use the descending option, the order starts with the greatest value for WINGSPAN and goes down to the least.
Q. If I have a DISTINCT option, a WHERE clause, and an ORDER BY clause, in which order are they performed?
A. The WHERE clause is applied first, to constrain the results. Then the DISTINCT is applied and, lastly, the ORDER BY clause is used to order the finalized result set.
Q. What advantage is there to renaming columns?
A. The new column name can fit the description of the returned data more closely for a particular report.
Q. What is the proper ordering of the following statement?
SELECT BIRD_NAME,WINGSPAN,EGGS FROM BIRDS ORDER BY 3,1
A. The query is properly ordered by the EGGS column and then by the BIRD_NAME column. Because no ordering preference is specified, they are properly ordered in ascending order, and the birds that lay the least number of eggs are shown first.
The following workshop consists of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises give you the opportunity to apply the concepts discussed during the current hour, as well as build on the knowledge you acquired in previous hours of study. Be sure to complete the quiz questions and exercises before continuing to the next hour. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
1. Name the required parts for any SELECT statement.
2. In the WHERE clause, are single quotation marks required for all the data?
3. Can multiple conditions be used in the WHERE clause?
4. Is the DISTINCT option applied before or after the WHERE clause?
5. Is the ALL option required?
6. How are numeric characters treated when ordering based on a character field?
7. How does Oracle handle its default case sensitivity differently from Microsoft SQL Server?
8. How is the ordering of the fields in the ORDER BY clause important?
9. How is the ordering determined in the ORDER BY clause when you use numbers instead of column names?
1. Write a query that tells you how many birds are stored in the database.
2. How many types of nest builders exist in the database?
3. Which birds lay more than seven eggs?
4. Which birds have more than one brood per year?
5. Write a query from the BIRDS table showing only the bird’s name, the number of eggs the bird typically lays, and the incubation period.
6. Modify the previous query in exercise 5 to show only the birds that have a wingspan greater than 48 inches.
7. Sort the previous query by WINGSPAN in ascending order.
8. Sort the previous query by WINGSPAN in descending order, to show the biggest birds first.
9. How many nicknames are stored in the database?
10. How many different food items are stored in the database?
11. Using the manual process described in this hour, determine which food items the Bald Eagle consumes.
12. Bonus exercise: Using a manual process and simple SQL queries, provide a list of birds that eat fish.