SQL Fundamentals: Getting Information From a Table
- The Select Statement
- The Select Clause
- The Where Clause
- The Order By Clause
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Introduction
This chapter explains the basic technique for getting the information you want from a table when you do not want to make any changes to the data and when all the information is in one table. The table may be very large and you might only want a small amount of data from it.
The Select Statement
In SQL, the select statement is used to get information from a table. Much of this book is concerned with the select statement. This chapter explains its four basic clauses and the options available for three of these clauses.
2-1 The goal: Get a few columns and rows from a table
Our goal is to get the data we want from a table. The table may be large and contain a lot of data. We only want a small part of it and we do not want to change the data in any way. The select statement allows us to retrieve a few columns and a few rows of data from the table.
Let's put some numbers on this. The particular numbers are not important, but they draw the picture more clearly. Suppose that printing all the data in the table would take 1,000 pages, and suppose we want only two pages of data from it. The select statement allows us to get just the two pages of data we want.
It is as if we want to read an article on redwood trees from an encyclopedia. We only want to see that one article. We do not want to read the entire encyclopedia from the beginning to the end. The select statement allows us to find the particular article we want to read.
The following diagram shows a large table of data. A small amount of that data is being retrieved into the result of the select statement. In this diagram, the data we want is scattered throughout the table in various columns and rows. It is collected together by the select statement.
Handling small tables of data
If a table of data is small, there might not be much reason to write a select statement. For instance, if we can print the entire table in two pages, then why not print it completely and let people work to find the information they want? In many situations, this approach makes sense.
In this book, we use small tables as learning tools. With tables this size, there is not much reason to use select statements. However, these tables are being used as examples to show how the select statement works when it is used with larger tables.
2-2 Overview of the select statement
The select statement is used to get some of the data from a table. It has six clauses.
|
select |
Which columns of data to get |
|
from |
Which table has the data |
|
where |
Which rows of data to get |
|
group by |
Described in chapter 12 |
|
having |
Described in chapter 12 |
|
order by |
Which columns are used to sort the result |
They must be written in this order. Group by and having are used in summarizing data, and we examine them later.
This chapter discusses the options available for the select, where, and order by clauses. For now, the from clause will always list only one table.
A select statement is often called a query. These two terms are used interchangeably. The term "select statement" emphasizes the syntax of the SQL command. The term "query" emphasizes the purpose of the command.
Task
Show an example of a select statement that uses all of the clauses listed above. Show the employee_id, last_name, and credit_limit columns from the l_employees table of the Lunches database. Show only the employees who have a credit limit greater than $20.00. Sort the rows of the result by the last name of the employee.
Oracle & Access SQL
select employee_id,last_name, credit_limit from l_employees
where credit_limit > 20.00
order by last_name;
Beginning table (l_employees table)
Result table 
Notes
|
|
The select clause lists the columns you want to show in the result table. They can be listed in any order. Their order in the select clause determines their order within the result table. When the computer sees a column name that is not followed by a comma it expects to see the next clause, the from clause. Also, note that the names of these columns do not contain spaces. Access allows this, but I do not recommend it because a space is usually used as a delimiter. The underscore character (_) is usually used instead of a space to separate the words in the name of each column. By typing last_name with an underscore, you are telling the computer that this is the name of a single column. If you typed last name with a space, the computer would try to find a column named last and it would not find any column with that name. This would cause an error and the computer would not process the select statement. Chapter 3 discusses the issue of using spaces in column names in more detail. |
|
|
The from clause names the table that the data comes from — the l_employees table of the Lunches database. In the naming scheme used here, the prefix "l_" indicates that the employees table is part of the Lunches database. This table is shown as the beginning table. |
|
|
The where clause indicates which rows to show in the result table. The condition where credit_limit > 20.00 eliminates the rows for employees 204 and 209 because they have a $15.00 credit limit, and employee 206, which has a null value. Note that the dollar amount is written without the dollar sign. It must also be written without any commas. The decimal point is acceptable, but not required. The condition could also be written as follows: where credit_limit > 20. In this SQL code, two zeros are put after the decimal point to make it look more like a currency value. This does not format the results. |
|
The order by clause specifies that the rows of the result table should be sorted in alphabetical order by the last_name column. A semicolon marks the end of the SQL statement. In Oracle, this statement will not run without the semicolon. In Access, it is optional. In Oracle, you could put a slash (/) on the next line as an alternative to the semicolon. Because using a semicolon is valid within both products, in this book I use a semicolon at the end of every SQL statement. |
|
|
Some people would call this a query result listing. This name has some merit, because it is not a table. It is the result of running a query, also known as a select statement. In Oracle, these results are shown on the screen as if they are printed out on paper. In Access, they are shown on the screen as if they are in a table, with some interactive elements, in datasheet view. In other books you may find the terms derived table and virtual table. I call this a result table because according to relational database theory, tables are the only database structure. The input to a query is a table, and the output of a query is a table. This result table appears only on the screen. It is not stored on the disk. |