• 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.
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.
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 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.
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 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 ]
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.
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.
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.
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 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.
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'),
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;
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';
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;
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.
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
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;
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.
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.
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.
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. 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.
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.
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?
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?
18.191.168.8