Hour 7. Introduction to the Database Query
What You’ll Learn in This Hour:
• What a database query is
• How to use the SELECT statement
• Adding conditions to queries using the WHERE clause
• Using column aliases
• Selecting data from another user’s table
In this seventh hour, you learn about database queries, which involve the use of the SELECT statement. The SELECT statement is the most frequently used of all SQL commands after a database’s establishment. The SELECT statement enables you to view data that is stored in the database.
What Is a Query?
A query is an inquiry into the database using the SELECT statement. A query is used to extract data from the database in a readable format according to the user’s request. For instance, if you have an employee table, you might issue an SQL statement that returns the employee who is paid the most. This request to the database for usable employee information is a typical query that can be performed in a relational database.
Introduction to the SELECT Statement
The SELECT statement, the command that represents Data Query Language (DQL) in SQL, is the basic statement used to construct database queries. The SELECT statement is not a standalone statement, which means that one or more additional clauses (elements) are required for a syntactically correct query. In addition to the required clauses, there are optional clauses that increase the overall functionality of the SELECT statement. The SELECT statement is by far one of the most powerful statements in SQL. The FROM clause is a mandatory clause and must always be used in conjunction with the SELECT statement.
There are four keywords, or clauses, that are valuable parts of a SELECT statement. These keywords are as follows:
• SELECT
• FROM
• WHERE
• ORDER BY
Each of these keywords is covered in detail during the following sections.
The SELECT Statement
The SELECT statement is used in conjunction with the FROM clause to extract data from the database in an organized, readable format. The SELECT part of the query is for selecting 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 is as follows:
SELECT [ * | ALL | DISTINCT COLUMN1, COLUMN2 ]
FROM TABLE1 [ , TABLE2 ];
The SELECT keyword in a query is followed by a list of columns that you want displayed as part of the query output. The asterisk (*) denotes that all columns in a table should be displayed 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 being displayed in the output. The ALL option is considered an inferred option. It is thought of 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.
By the Way: Use Commas to Separate List Items
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.
Explore the basic capabilities of the SELECT statement by studying the following examples. First, perform a simple query from the PRODUCTS_TBL table:
SELECT * FROM PRODUCTS_TBL;
PROD_ID PROD_DESC COST
-----------------------------------------------
11235 WITCH COSTUME 29.99
222 PLASTIC PUMPKIN 18 INCH 7.75
13 FALSE PARAFFIN TEETH 1.1
90 LIGHTED LANTERNS 14.5
15 ASSORTED COSTUMES 10
9 CANDY CORN 1.35
6 PUMPKIN CANDY 1.45
87 PLASTIC SPIDERS 1.05
119 ASSORTED MASKS 4.95
1234 KEY CHAIN 5.95
2345 OAK BOOKSHELF 59.99
11 rows selected.
The asterisk represents all columns in the table, which, as you can see, are displayed in the form PROD_ID, PROD_DESC, and COST. Each column in the output is displayed in the order that it appears in the table. There are 11 records in this table, identified by the feedback 11 rows selected. This feedback differs among implementations; for example, another feedback for the same query would be 11 rows affected. Although the asterisk is a helpful piece of shorthand when writing SQL queries, it is considered best practice to explicitly name your columns that you are returning.
Now select data from another table, CANDY_TBL. Create this table in the image of the PRODUCTS_TBL table for the following examples. List the column name after the SELECT keyword to display only one column in the table:
SELECT PROD_DESC FROM CANDY_TBL;
PROD_DESC
-----------------
CANDY CORN
CANDY CORN
HERSHEYS KISS
SMARTIES
4 rows selected.
Four records exist in the CANDY_TBL table. The next statement uses the ALL option to show you that the ALL is optional and redundant. There is never a need to specify ALL; it is a default option.
SELECT ALL PROD_DESC
FROM CANDY_TBL;
PROD_DESC
-----------------
CANDY CORN
CANDY CORN
HERSHEYS KISS
SMARTIES
4 rows selected.
The DISTINCT option is used in the following statement to suppress the display of duplicate records. Notice that the value CANDY CORN is printed only once in this example.
SELECT DISTINCT PROD_DESC
FROM CANDY_TBL;
PROD_DESC
-----------------
CANDY CORN
HERSHEYS KISS
SMARTIES
3 rows selected.
You can also use DISTINCT and ALL with parentheses enclosing the associated column. Parentheses are often used in SQL—as well as many other languages—to improve readability.
SELECT DISTINCT(PROD_DESC)
FROM CANDY_TBL;
PROD_DESC
-----------------
CANDY CORN
HERSHEYS KISS
SMARTIES
3 rows selected.
The FROM Clause
The FROM clause must be used in conjunction with the SELECT statement. It is a required element for any query. The FROM clause’s purpose is to tell the database what table(s) to access to retrieve the desired 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 is as follows:
from table1 [ , table2 ]
The WHERE Clause
A 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.
There can be more than one condition in the WHERE clause. If there is more than one condition, the conditions are connected by the AND and OR operators, which are discussed during Hour 8, “Using Operators to Categorize Data.” As you also learn during the next hour, several conditional operators exist that can be used 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 an SQL statement.
The syntax for the WHERE clause is as follows:
select [ all | * | distinct column1, column2 ]
from table1 [ , table2 ]
where [ condition1 | expression1 ]
[ and|OR condition2 | expression2 ]
The following is a simple SELECT statement without conditions specified by the WHERE clause:
SELECT *
FROM PRODUCTS_TBL;
PROD_ID PROD_DESC COST
------------------------------------------------
11235 WITCH COSTUME 29.99
222 PLASTIC PUMPKIN 18 INCH 7.75
13 FALSE PARAFFIN TEETH 1.1
90 LIGHTED LANTERNS 14.5
15 ASSORTED COSTUMES 10
9 CANDY CORN 1.35
6 PUMPKIN CANDY 1.45
87 PLASTIC SPIDERS 1.05
119 ASSORTED MASKS 4.95
1234 KEY CHAIN 5.95
2345 OAK BOOKSHELF 59.99
11 rows selected.
Now add a condition for the same query:
SELECT * FROM PRODUCTS_TBL
WHERE COST < 5;
PROD_ID PROD_DESC COST
-----------------------------------------------
13 FALSE PARAFFIN TEETH 1.1
9 CANDY CORN 1.35
6 PUMPKIN CANDY 1.45
87 PLASTIC SPIDERS 1.05
119 ASSORTED MASKS 4.95
5 rows selected.
The only records displayed are those that cost less than $5.
In the following query, you want to display the product description and cost that matches product identification 119:
SELECT PROD_DESC, COST
FROM PRODUCTS_TBL
WHERE PROD_ID = '119';
PROD_DESC COST
-------------------------------------
ASSORTED MASKS 4.95
1 row selected.
The ORDER BY Clause
You usually want your output to have some kind of order. Data can be sorted by using the ORDER BY clause. The ORDER BY clause arranges the results of a query in a listing format you specify. The default ordering of the ORDER BY clause is an ascending order; the sort displays in the order A–Z if it’s sorting output names alphabetically. A descending order for alphabetical output would be displayed in the order Z–A. Ascending order for output for numeric values between 1 and 9 would be displayed 1–9; descending order would be displayed as 9–1.
The syntax for the ORDER BY clause is as follows:
select [ all | * | distinct column1, column2 ]
from table1 [ , table2 ]
where [ condition1 | expression1 ]
[ and|OR condition2 | expression2 ]
ORDER BY column1|integer [ ASC|DESC ]
Begin your exploration of the ORDER BY clause with an extension of one of the previous statements. You order the product description in ascending order, or alphabetical order. Note the use of the ASC option. You can specify ASC after any column in the ORDER BY clause.
SELECT PROD_DESC, PROD_ID, COST
FROM PRODUCTS_TBL
WHERE COST < 20
ORDER BY PROD_DESC ASC;
PROD_DESC PROD_ID COST
------------------------------------------------
ASSORTED COSTUMES 15 10
ASSORTED MASKS 119 4.95
CANDY CORN 9 1.35
FALSE PARAFFIN TEETH 13 1.1
LIGHTED LANTERNS 90 14.5
PLASTIC PUMPKIN 18 INCH 222 7.75
PLASTIC SPIDERS 87 1.05
PUMPKIN CANDY 6 1.45
8 rows selected.
By the Way: Rules for Sorting
SQL sorts are ASCII, character-based sorts. The numeric values 0–9 would be sorted as character values and sorted before the characters A–Z. Because numeric values are treated like characters during a sort, the following list of numeric values would be 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:
SELECT PROD_DESC, PROD_ID, COST
FROM PRODUCTS_TBL
WHERE COST < 20
ORDER BY PROD_DESC DESC;
PROD_DESC PROD_ID COST
------------------------------------------------
PUMPKIN CANDY 6 1.45
PLASTIC SPIDERS 87 1.05
PLASTIC PUMPKIN 18 INCH 222 7.75
LIGHTED LANTERNS 90 14.5
FALSE PARAFFIN TEETH 13 1.1
CANDY CORN 9 1.35
ASSORTED MASKS 119 4.95
ASSORTED COSTUMES 15 10
8 rows selected.
Did You Know?: 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. The 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:
SELECT PROD_DESC, PROD_ID, COST
FROM PRODUCTS_TBL
WHERE COST < 20
ORDER BY 1;
PROD_DESC PROD_ID COST
------------------------------------------------
ASSORTED COSTUMES 15 10
ASSORTED MASKS 119 4.95
CANDY CORN 9 1.35
FALSE PARAFFIN TEETH 13 1.1
LIGHTED LANTERNS 90 14.5
PLASTIC PUMPKIN 18 INCH 222 7.75
PLASTIC SPIDERS 87 1.05
PUMPKIN CANDY 6 1.45
8 rows selected.
In this query, the integer 1 represents the column PROD_DESC. The integer 2 represents the PROD_ID column, 3 represents the COST column, and so on.
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 shown by the following example:
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. So the statement that follows first orders by the PROD_DESC column and then by the COST column:
ORDER BY PROD_DESC,COST
Case Sensitivity
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 uppercase or lowercase—whatever you prefer. The case may also be mixed (both uppercase and lowercase for a single word or statement), which is often referred to as camel case. See Hour 5, “Manipulating Data,” 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 relation to your data is important because it determines how your WHERE clauses, among other things, interpret matches. You need to check with your specific RDBMS implementation to determine what the default collation is on your system. Some systems, such as MySQL and Microsoft SQL Server, have a default collation that is case insensitive. 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, as described next. Because case sensitivity is a factor at the database level, its importance as a factor in your queries varies.
Watch Out!: 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, it is good to implement a corporate policy to ensure that data entry is handled in the same manner across an enterprise.
Case sensitivity is, however, a factor in maintaining data consistency within your RDBMS. For instance, your data would not be consistent if you arbitrarily entered your data using random case:
SMITH
Smith
smith
If the last name was stored as smith and you issued a query as follows in an RDBMS such as Oracle, which is case sensitive, no rows would be returned:
SELECT *
FROM EMPLOYEE_TBL
WHERE LAST_NAME = 'SMITH';
SELECT *
FROM EMPLOYEE_TBL
WHERE UPPER(LAST_NAME) = UPPER('Smith'),
Examples of Simple Queries
This section provides several examples of queries based on the concepts that have been discussed. The hour begins with the simplest query you can issue and builds upon the initial query progressively. You use the EMPLOYEE_TBL table.
Select all records from a table and display all columns:
SELECT * FROM EMPLOYEE_TBL;
Select all records from a table and display a specified column:
SELECT EMP_ID
FROM EMPLOYEE_TBL;
By the Way: Overcoming Case-Sensitive Issues
In systems that are case sensitive, once again like Oracle, you can overcome the case sensitivity by either ensuring that your data is entered in the same case every time or using SQL functions, which are discussed in later lessons, to modify the case. Following is an example of using the UPPER function to change the cases of the data used in the WHERE clause:
Select all records from a table and display a specified column. You can enter code on one line or use a carriage return as desired:
SELECT EMP_ID FROM EMPLOYEE_TBL;
Select all records from a table and display multiple columns separated by commas:
SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL;
Display data for a given condition:
SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID = '333333333';
By the Way: Ensure That Your Queries Are Constrained
When selecting all rows of data from a large table, the results could return a substantial amount of data.
Display data for a given condition and sort the output:
SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL
WHERE CITY = 'INDIANAPOLIS'
ORDER BY EMP_ID;
Display data for a given condition and sort the output on multiple columns, one column sorted in reverse order. In the instance that follows, the EMP_ID column is sorted in ascending order, whereas the LAST_NAME column is sorted in descending order:
SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL
WHERE CITY = 'INDIANAPOLIS'
ORDER BY EMP_ID, LAST_NAME DESC;
Display data for a given condition and sort the output using an integer in the place of the spelled-out column name:
SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL
WHERE CITY = 'INDIANAPOLIS'
ORDER BY 1;
Display data for a given condition and sort the output by multiple columns using integers. The order of the columns in the sort is different from their corresponding order after the SELECT keyword:
SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL
WHERE CITY = 'INDIANAPOLIS'
ORDER BY 2, 1;
Counting the Records in a Table
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 later in this book, this function should be introduced here because it is often a part of one of the simplest queries that you can create.
The syntax of the COUNT function is as follows:
SELECT COUNT(*)
FROM TABLE_NAME;
The COUNT function is used with parentheses, which enclose the target column to count or the asterisk to count all rows of data in the table.
Did You Know?: 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 for counting the number of rows for a table.
Counting the number of records in the PRODUCTS_TBL table:
SELECT COUNT(*) FROM PRODUCTS_TBL;
COUNT(*)
----------
9
1 row selected.
Counting the number of values for PROD_ID in the PRODUCTS_TBL table:
SELECT COUNT(PROD_ID) FROM PRODUCTS_TBL;
COUNT(PROD_ID)
---------------
9
1 row selected.
If you want to count only the unique values that show up within a table, you would use the DISTINCT syntax within the COUNT function. For example, if you want to get the distinct states represented in the STATE column of the EMPLOYEE_TBL, use a query such as the one that follows:
SELECT COUNT(DISTINCT PROD_ID) FROM PRODUCTS_TBL;
COUNT(DISTINCT PROD_ID)
---------------------------------------
1
Selecting Data from Another User’s Table
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 (the GRANT command is discussed in Hour 20, “Creating and Using Views and Synonyms”). To access another user’s table in a SELECT statement, precede the table name with the schema name or the username that owns (created) the table, as in the following example:
SELECT EMP_ID
FROM SCHEMA.EMPLOYEE_TBL;
Using Column Aliases
Column aliases are used to 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 displays the product description twice, giving the second column an alias named PRODUCT. Notice the column headers in the output.
select prod_desc,
prod_desc product
from products_tbl;
PROD_DESC PRODUCT
-------------------------------------------------
WITCH COSTUME WITCH COSTUME
PLASTIC PUMPKIN 18 INCH PLASTIC PUMPKIN 18 INCH
FALSE PARAFFIN TEETH FALSE PARAFFIN TEETH
LIGHTED LANTERNS LIGHTED LANTERNS
ASSORTED COSTUMES ASSORTED COSTUMES
CANDY CORN CANDY CORN
PUMPKIN CANDY PUMPKIN CANDY
PLASTIC SPIDERS PLASTIC SPIDERS
ASSORTED MASKS ASSORTED MASKS
KEY CHAIN KEY CHAIN
OAK BOOKSHELF OAK BOOKSHELF
11 rows selected.
By the Way: Using Synonyms in Queries
If a synonym exists in the database for the table to which you desire access, you do not have to specify the schema name for the table. Synonyms are alternate names for tables, which are discussed in Hour 21, “Working with the System Catalog.”
Column aliases can be used to customize names for column headers and reference a column with a shorter name in some SQL implementations.
Did You Know?: 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.
Summary
You have been introduced to the database query, a means for obtaining useful information from a relational database. The SELECT statement, which is known as the Data Query Language (DQL) command, creates queries in SQL. You must include the FROM clause with every SELECT statement. You have learned how to place a condition on a query using the WHERE clause and how to sort data using the ORDER BY clause. You have 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&A
Q. Why won’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. When I use the ORDER BY clause and choose the option descending, what does that really do to the data?
A. Say that you use the ORDER BY clause and have selected last_name from the EMPLOYEE_TBL. If you use the descending option, the order starts with the letter Z and finishes with the letter A. Now, let’s say that you have used the ORDER BY clause and have selected the salary from the EMPLOYEE_PAY_TBL. If you use the descending option, the order starts with the largest salary and goes down to the lowest salary.
Q. What advantage is there to renaming columns?
A. The new column name could fit the description of the returned data more closely for a particular report.
Q. What would be the ordering of the following statement:
SELECT PROD_DESC,PROD_ID,COST FROM PRODUCTS_TBL
ORDER BY 3,1
A. The query would be ordered by the COST column, and then by the PROD_DESC column. Because no ordering preference was specified, they would both be in ascending order.
Workshop
The following workshop is composed 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 are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
1. Name the required parts for any SELECT statement.
2. In the WHERE clause, are single quotation marks required for all the data?
3. Under what part of the SQL language does the SELECT statement (database query) fall?
4. Can multiple conditions be used in the WHERE clause?
5. What is the purpose of the DISTINCT option?
6. Is the ALL option required?
7. How are numeric characters treated when ordering based upon a character field?
8. How does Oracle handle its default case sensitivity different from MySQL and Microsoft SQL Server?
Exercises
1. Invoke your RDBMS query editor on your computer. Using your learnsql database, enter the following SELECT statements. Determine whether the syntax is correct. If the syntax is incorrect, make corrections to the code as necessary. We are using the EMPLOYEE_TBL here.
a. SELECT EMP_ID, LAST_NAME, FIRST_NAME,
FROM EMPLOYEE_TBL;
b. SELECT EMP_ID, LAST_NAME
ORDER BY EMPLOYEE_TBL
FROM EMPLOYEE_TBL;
c. SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID = '213764555'
ORDER BY EMP_ID;
d. SELECT EMP_ID SSN, LAST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID = '213764555'
ORDER BY 1;
e. SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID = '213764555'
ORDER BY 3, 1, 2;
2. Does the following SELECT statement work?
SELECT LAST_NAME, FIRST_NAME, PHONE
FROM EMPLOYEE_TBL
WHERE EMP_ID = '333333333';
3. Write a SELECT statement that returns the name and cost of each product from the PRODUCTS_TBL. Which product is the most expensive?
4. Write a query that generates a list of all customers and their telephone numbers.
5. Write a simple query to return a list of customers with a particular last name. Try using a WHERE clause with the name in mixed case and uppercase. What case sensitivity is your RDBMS set to?