Chapter 3
CHAPTER 3
------
SINGLE TABLE QUERY BASICS
This chapter focuses on learning to write SELECT statements to retrieve information from tables. This is termed querying a table, and this chapter covers single-table queries, meaning that the information retrieved will come from a single database table. As you have probably determined by this point in your studies, a database can be queried to produce both small and large quantities of information. Simple queries often tend to produce large quantities of information in terms of row output, while more complex queries are capable of extracting specific information from a database. If a query produces a large quantity of information, managers who seek to use the information to aid them in decision making may be overwhelmed by the sheer volume of information. Usually, managers need specific pieces of information to help them make decisions. In this chapter, you will initially learn to write simple queries, and will progress to increasingly complex queries.
Objectives
You query relational database queries through use of the SELECT statement. Chapter 1 introduced the SELECT statement. In this chapter, you will master the basics of the SELECT statement. You will learn to write queries to select both specific rows and specific columns from a table. You will also learn to sort the output in various ways. Additionally, you will learn to avoid some of the common errors that can be made when writing a SELECT statement. The learning objectives for this chapter are:
· Write simple SELECT statements.
· Use the COLUMN command to format output.
· Use the DISTINCT clause to eliminate duplicate rows in a result table.
· Use the WHERE clause to specify selection criteria and conditions.
· Order rows with the ORDER BY clause.
SIMPLE SELECT STATEMENTS
The main element in a SQL query is the SELECT statement. A properly written SELECT statement will always produce a result in the form of one or more rows of output. The SELECT statement chooses (selects) rows from one or more tables according to specific criteria. In this chapter we focus on selecting rows from a single table. SQL Example 3.1 shows the simplest form of a query.
/* SQL Example 3.1 */
SELECT *
FROM Employee;
EMPLOYEEID SSN LASTNAME FIRSTNAME
67555 981789642 Simmons Lester
33355 890536222 Boudreaux Beverly
33344 890563287 Adams Adam
more rows and columns will be displayed…
This query selects rows from the employee table. The asterisk (*) tells Oracle to select (display) all columns contained in the table employee. The resulting output is termed a result table. The result table displayed in SQL Example 3.1 only lists the first three rows and first four columns of the result table. The full employee table is described in Appendix A and is part of the Madison Hospital database. The full result table output will wrap around your computer screen because the rows of data are too large to display to a single line. Additionally, you will notice that the LastName, FirstName, and MiddleName columns are quite wide when they display to your computer monitor because when the employee table was created, these columns were defined to be large enough to store the largest last, first, or middle name data values that might ever occur for an employee. Later in this chapter, you will learn to format the width of result table columns.
This particular type of query uses an asterisk (*) symbol to tell Oracle: "Give me everything you have on employees. Don't hold anything back." All rows and all columns are selected. The SELECT statement in SQL Example 3.2 produces an identical result table by listing all column names in the employee table.
/* SQL Example 3.2 */
SELECT EmployeeID, SSN, Lastname, FirstName, MiddleName, DepartmentNumber, Office, DateHired, Title, WorkPhone,
PhoneExtension, LicensureNumber, Salary, WageRate, ParkingSpace, Gender, SupervisorID
FROM Employee;
Clearly, it is simpler to type the first query as opposed to the one given above that lists each column name individually, but you would only use the asterisk (*) in a SELECT statement if you wished to display all columns in the result table. Normally, this is not the case.
Note that a comma separates each column name. This syntax is required. The SELECT statement also specifies the table name in a FROM clause. Finally, the semicolon at the end of the query tells Oracle that this is the end of the query. You may wonder about the ordering of the column names. It happens that the ordering of column names is immaterial except that the result table will display the columns in the order specified in the SELECT statement. Let's examine the data stored in the department table. We'll begin by first describing the department table.
/* SQL Example 3.3 */
DESC Department;
Name Null Type
------
DEPARTMENTNUMBER NOT NULL NUMBER(4)
DEPARTMENTNAME NOT NULL VARCHAR2(25)
MANAGERID CHAR(5)
MANAGERASSIGNEDDATE DATE
4 rows selected
There are only four columns in the department table. These columns store the department number, department name, department manager's identifying number,, and the date that each department manager was assigned to the job of department manager. The two queries in SQL Examples 3.4 and 3.5 produce exactly the same result table.
/* SQL Example 3.4 */
SELECT *
FROM Department;
/* SQL Example 3.5 */
SELECT DepartmentNumber, DepartmentName, ManagerID, ManagerAssignedDate
FROM Department;
DEPARTMENTNUMBER DEPARTMENTNAME MANAGERID MANAGERASSIGNEDDATE
------
1 Medical Surgical Ward 1 23232 21-AUG-97
2 Radiology 23244 10-JAN-01
3 Emergency-Surgical 10044 15-SEP-02
more rows will be displayed…
SQL Example 3.6 is a revision of SQL Example 3.5 to reorder the columns. The output in terms of rows is identical, but the ordering of the columns changes to match the ordering in the SELECT statement.
/* SQL Example 3.6 */
SELECT DepartmentName, DepartmentNumber, ManagerAssignedDate, ManagerID
FROM Department;
DEPARTMENTNAME DEPARTMENTNUMBER MANAGERASSIGNEDDATE MANAGERID
------
Medical Surgical Ward 1 1 21-AUG-97 23232
Radiology 2 10-JAN-01 23244
Emergency-Surgical 3 15-SEP-02 10044
more rows will be displayed…
Indenting SQL Code
Have you noticed that we always start a new line for the FROM clause in a SELECT statement. We have also indented four characters in listing the columns to be displayed where the list of columns is too large to fit within a single line. It is fairly common to follow this type of indentation convention when writing a query because it makes a query easier to read. However, Oracle will process a query regardless of whether you type an entire query on one line or indent. Oracle simply looks for the semicolon marking the end of the query. This is because SQL is a free-form language. This means that there are no rules about how many words you can put on a line or where you break a line. For example, the SQL statements in SQL Examples 3.7 and 3.8 are considered exactly the same.
/* SQL Example 3.7 */
SELECT * FROM Employee;
/* SQL Example 3.8 */
SELECT
*
FROM
Employee;
Although Oracle does not require it, a new line should be started for each clause in a SQL statement. This will increase maintenance and readability. The following keywords are your signal to start a new line:
· SELECT
· FROM
· WHERE
· GROUP BY
· HAVING
· ORDER BY
Selecting Specific Columns
As you can see, using asterisk (*) is a quick and easy way to list all column names in a table. However, in day-to-day queries you will rarely need to specify all of the available column names in a table. If you provided your boss with all of the columns and rows from a table, the boss would likely tell you that you were providing too much detail! Let's suppose that your boss wants a listing of only employee identifying numbers, last names, and first names. The SELECT statement to produce this output is shown in SQL Example 3.9.
/* SQL Example 3.9 */
SELECT EmployeeID, Lastname, FirstName
EMPLOYEEID LASTNAME FIRSTNAME
------
67555 Simmons Lester
33355 Boudreaux Beverly
33344 Adams Adam
more rows will be displayed…
NOTE: Throughout this manual, the rows of a result table produced by a query are limited to only the number of rows needed in order for you to understand the query. As you execute each query, you will sometimes see that we have omitted rows of data output for the purpose of brevity or clarity.
To review, the rules for writing a simple SELECT query are:
· Specify the column names you want displayed in the result set by typing the exact, complete column names.
· Separate each column name with a comma (,).
· Specify the name of the table after the FROM clause.
· Terminate the query with a semi-colon (;).
USING COLUMN COMMANDS TO FORMAT OUTPUT
At times you will write a query where the columnar output will not fit onto a single display line. When this happens, the result table will display lines that "wrap" around to the next line and the information will be difficult to read. SQL Example 3.10 produces output that wraps to the next line.
/* SQL Example 3.10 */
SELECT EmployeeID, LastName, FirstName, DateHired, SupervisorID
FROM Employee;
EMPLO LASTNAME
------
FIRSTNAME DATEHIRED SUPER
------
67555 Simmons
Lester 03-MAR-98
33355 Boudreaux
Beverly 15-OCT-01 67555
33344 Adams
Adam 29-JAN-85 33355
more rows will be displayed…
You can clean up the result table of such a query by modifying the output display size of specific columns. This is termed formatting the result table. Column output is formatted with the COLUMN-FORMAT command. Note that the Oracle SQL Developer software does not support the COLUMN command within a Sql Worksheet window.
The example shown below formats the LastName and FirstName columns to restrict their output to 15 characters each. This is significantly smaller than the 50-character column width specified for each of these columns in the employee table. This means that employees with first or last names larger than 15 characters will have all characters after 15 wrap to the next line. If you do not want the output to wrap to the next line, you can use the SET WRAP OFF command to cause any characters beyond 15 to simply truncate in the display of the result table. The advantage is that each single row of output is displayed on a single line; thereby improving the readability of the information as is done in SQL Example 3.11.
/* SQL Example 3.11 */
SET WRAP OFF;
COLUMN EmployeeID FORMAT A10;
COLUMN LastName FORMAT A15;
COLUMN FirstName FORMAT A15;
COLUMN SupervisorID FORMAT A12;
SELECT SSN, LastName, FirstName, DateHired, SupervisorID
FROM Employee;
SSN LASTNAME FIRSTNAME DATEHIRED SUPERVISORID
------
981789642 Simmons Lester 03-MAR-98
890536222 Boudreaux Beverly 15-OCT-01 67555
890563287 Adams Adam 29-JAN-85 33355
more rows will be displayed…
SQL Example 3.11 formatted character data. If you are using the Oracle SQL Developer software to code statements, the SET WRAP OFF command will be ignored and column width formatting will not affect the display for the Script Output window. The width of column output display in the Results window of Oracle SQL Developer is automatically adjusted to the largest value displayed in the column.
You can also format the output of numeric columns. By default, a numeric column displays with a width equal to the width of the heading. You can both increase or decrease the width of a numeric column displayed in a result table. Additionally, SQL*PLUS displays numbers with as many digits as necessary in order to ensure that the numeric value is accurately displayed. You can set the default width of numeric column displays with the SET command. SQL Example 3.12 sets the NUMWIDTH parameter to 8 characters of output display (the default value of NUMWIDTH is normally 10). You should understand that formatting commands such as NUMWIDTH do not affect the actual table's structure in any way —only the output displayed in a result table is affected. If you are using the Oracle SQL Developer software to code statements, the SET NUMWIDTH command will be ignored
/* SQL Example 3.12 */
SET NUMWIDTH 8;
Setting the NUMWIDTH parameter value overrides the width of numeric column displays to ensure that the columnar output is at least NUMWIDTH digits in width! When using the COLUMN command to format numeric data, the placeholders "9" and "0" are used to denote a position to display one digit of output. The placeholder "9" will format numeric output, but values are not required to be displayed. The placeholder "0" also formats numeric output, but requires output. SQL Example 3.13 shows the use of the "9" placeholder.
/* SQL Example 3.13 */
COLUMN Salary FORMAT 99999.99;
SELECT Salary
FROM Employee;
SALARY
------
22000.00
17520.00
5500.00
more rows will be displayed…
If the Salary is formatted as 0099999.99, the output display changes to reflect that output is required in the first two-digit locations.
/* SQL Example 3.14 */
COLUMN Salary FORMAT 0099999.99;
SELECT Salary
FROM Employee;
SALARY
------
0022000.00
0017520.00
0005500.00
more rows will be displayed…
The output display is fairly "plain" and may not satisfy managers simply because large numbers are difficult to interpret if they are not displayed in the manner in which managers are used to seeing them displayed. In this case, managers might prefer that salary figures be displayed with a dollar sign ($) and appropriate comma to separate the thousands and hundreds digits. This is demonstrated in SQL Example 3.15.
/* SQL Example 3.15 */
COLUMN Salary FORMAT $99,999.99;
SELECT Salary
FROM Employee;
SALARY
------
$22,000.00
$17,520.00
$5,500.00
more rows will be displayed…
COMMON ERRORS
Although SQL is a free-form language, there are still syntactical rules that you must follow or you will receive an error message instead of the desired result table. The Oracle relational database management system communicates errors in SELECT statements by providing unique error numbers and accompanying error descriptions. Let's examine some example errors.
Invalid Column Name
The SELECT statement in SQL Example 3.16 has the employee SSN column name spelled incorrectly.
/* SQL Example 3.16 */
SELECT Socsecno