CERTIFICATION OBJECTIVES
This chapter explores the concepts of extracting or retrieving data stored in relational tables using the SELECT statement. The statement is introduced in its basic form and is progressively built on to extend its core functionality. As you learn the rules governing this statement, an important point to remember is that the SELECT statement never alters information stored in the database. Instead, it provides a read-only method of extracting information. When you ask a question using SQL SELECT, you are guaranteed to extract results, even from the difficult questions.
CERTIFICATION OBJECTIVE 2.01
List the Capabilities of SQL SELECT Statements
Knowing how to retrieve data in a set format using a query language is the first step toward understanding the capabilities of SELECT statements. Describing the relations involved provides a tangible link between the theory of how data is stored in tables and the practical visualization of the structure of these tables. These topics form an important precursor to the discussion of the capabilities of the SELECT statement. The three primary areas explored are as follows:
Introducing the SQL SELECT statement The DESCRIBE table command
Capabilities of the SELECT statement
Introducing the SQL SELECT Statement
The SELECT statement from Structured Query Language (SQL) has to be the single most powerful nonspoken language construct. The SELECT statement is an elegant, flexible, and highly extensible mechanism created to retrieve information from a database table. A database would serve little purpose if it could not be queried to answer all sorts of interesting questions. For example, you may have a database that contains personal financial records like your bank statements, your utility bills, and your salary statements. You could easily ask the database for a date-ordered list of your electrical utility bills for the last six months or query your bank statement for a list of payments made to a certain account over the same period. The beauty of the SELECT statement is encapsulated in its simple English-like format that allows questions to be asked of the database in a natural manner.
Tables, also known as relations, consist of rows of information divided by columns. Consider two of the sample tables introduced in the previous chapter: the EMPLOYEES table and the DEPARTMENTS table. This sample dataset is based on the Human Resources (HR) information for some fictitious organization. In Oracle terminology, each table belongs to a schema (owner), in this case the HR schema. The EMPLOYEES table stores rows or records of information. These contain several attributes (columns) that describe each employee in this organization. The DEPARTMENTS table contains descriptive information about each department within this organization, stored as rows of data divided into columns.
Assuming a connection to a database containing the sample HR schema is available, then using either SQL*Plus or SQL Developer you can establish a user session. Once connected to the database, you are ready to begin your tour of SQL.
SQL*Plus has an extensive runtime command environment which can be explored using the online documentation or the HELP INDEX command. This lists available SQL*Plus commands such as the SHOW command, which shows the value of a SQL*Plus variable. For example, SHOW USER shows the name of the currently connected user.
The DESCRIBE Table Command
To get the answers one seeks, one must ask the correct questions. An understanding of the terms of reference, which in this case are relational tables, is essential for the formulation of the correct questions. A structural description of a table is useful to establish what questions can be asked of it. The Oracle server stores information about all tables in a special set of relational tables called the data dictionary, in order to manage them. The data dictionary is quite similar to a regular language dictionary. It stores definitions of database objects in a centralized, ordered, and structured format.
A clear distinction must be drawn between storing the definition and the contents of a table. The definition of a table includes information such as table name, table owner, details about the columns that comprise it, and its physical storage size on disk. This information is also referred to as metadata. The contents of a table are stored in rows and are referred to as data.
The structural metadata of a table may be obtained by querying the database for the list of columns that comprise it using the DESCRIBE command. The general form of the syntax for this command is intuitive:
DESC[RIBE] <SCHEMA>.tablename
This command will be systematically unpacked. The DESCRIBE keyword can be shortened to DESC. All tables belong to a schema or owner. If you are describing a table that belongs to the schema to which you are connected, the “<SCHEMA>.” portion of the command may be omitted. Figure 2-1 illustrates the use of the SHOW user command to verify that the current connected user is HR. While connected to the database as the HR user, the EMPLOYEES table is described from SQL*Plus with the DESCRIBE employees command and the DEPARTMENTS table is described using the shorthand notation DESC hr.departments. The HR. notational prefix could be omitted since the DEPARTMENTS table belongs to the HR schema. The HR schema (and every other schema) has access to a special table called DUAL, which belongs to the SYS schema. This table can be structurally described with the command DESCRIBE sys.dual.
FIGURE 2-1 Describing the EMPLOYEES, DEPARTMENTS, and DUAL tables
Describing tables yields interesting and useful results. You know which columns of a table can be selected since their names are exposed. You also know the nature of the data contained in these columns since the column data type is exposed. Column data types are discussed in detail in Chapter 11. For the current discussion, the following explanation of the different column data types is sufficient.
Numeric columns are often specified as NUMBER(p,s), where the first parameter is precision and the second is scale. In Figure 2-1, the SALARY column of the EMPLOYEES table has a data type of NUMBER(8,2). This means that the values stored in this column can have at most 8 digits. Of these 8 digits, 2 may be to the right of the decimal point and up to 6 may be to the left. If more than two digits are to the right of the decimal point, the number will be rounded to 2 decimal places as long as there are at most 8 digits. A SALARY value of 999999.99 is acceptable, but a SALARY value of 9999999.9 is not, even though both these numbers contain 8 digits.
VARCHAR2(length) data type columns store variable length alphanumeric character data, where length determines the maximum number of characters a column can contain. The FIRST_NAME column of the EMPLOYEES table has data type VARCHAR2(20), which means it can store employees’ names of up to 20 characters. Note that if this column contains no data or its content is less than 20 characters, it will not necessarily use the same space as it would use to store a name that is 20 characters long. The CHAR(size) column data type specifies fixed-length columns, where row space is preallocated to contain a fixed number of characters regardless of its contents. CHAR is much less commonly used than VARCHAR2. Unless the length of the data is predictable and constant, the CHAR data type utilizes storage inefficiently, padding any unused components with spaces.
DATE and TIMESTAMP column data types store date and time information. DATE stores a moment in time with precision, including day, month, year, hours, minutes, and seconds. TIMESTAMP(f) stores the same information as DATE but is also capable of storing fractional seconds.
A variety of data types is available for use as column data types. Many have a specialized purpose like Binary Large Objects (BLOBs), used for storing binary data like music or video data. The vast majority of tables, however, use the primitive column data types of NUMBER, VARCHAR2, and DATE. The TIMESTAMP data type has become widely used since its introduction in Oracle 9i. Becoming familiar and interacting with these generic primitive data types prepares you for dealing with a significant range of database-related queries.
Mandatory columns, which are forced to store data for each row, are exposed by the “Null?” column output from the DESCRIBE command having the value NOT NULL. You are guaranteed that any column of data that is restricted by the NOT NULL constraint when the table is created must contain some data. It is important to note that NULL has special meaning for the Oracle server. NULL refers to an absence of data. Blank spaces do not count as NULL since they are present in the row and have some length even though they are not visible.
EXERCISE 2-1
Describing the Human Resources Schema
The HR schema contains seven tables representing a data model of a fictitious Human Resources department. The EMPLOYEES table, which stores details of the staff, and the DEPARTMENTS table, which contains the details of the departments in the organization, have been described. In this step-by-step exercise, a connection is made using SQL Developer as the HR user and the remaining five sample tables are described. They are the JOBS table, which keeps track of the different job types available in the organization, and the JOB_HISTORY table, which keeps track of the job details of employees who changed jobs but remained in the organization. To understand the data model further, the LOCATIONS, COUNTRIES, and REGIONS tables, which keep track of the geographical information pertaining to departments in the organization, will be described.
1. Launch SQL Developer and choose New from the File menu. Choose Database Connection. If this is the first time you are connecting to the database from SQL Developer, you are required to create a connection. Provide a descriptive connection name and input HR as the username. The remaining connection details should be obtained from your database administrator. Once the connection is saved, click the Connect button.
2. Navigate to the SQL Worksheet, which is the tab titled Worksheet.
3. Type in the command DESCRIBE jobs. Terminating this command with a semicolon is optional.
4. Execute the DESCRIBE command, either by pressing the F5 key or by clicking the solid green triangular arrow icon located on the toolbar above the SQL Editor.
5. The JOBS table description appears in the Results frame as shown in the following illustration.
6. Steps 3 to 5 can be repeated to describe the remaining JOB_HISTORY, LOCATIONS, COUNTRIES, and REGIONS tables.
SQL Developer provides an alternative to the DESCRIBE command when it comes to obtaining the structural information of tables.
7. Navigate to the LOCATIONS table using the Tree navigator located on the left frame underneath the connection name.
8. SQL Developer describes the table automatically on the right side of the tool as shown in the following illustration.
Capabilities of the SELECT Statement
Relational database tables are built on a strong mathematical foundation called relational theory. In this theory, relations, or tables, are operated on by a formal language called relational algebra. SQL is a commercial interpretation of the relational algebraic constructs. Three concepts from relational theory encompass the capability of the SELECT statement: projection, selection, and joining.
Projection refers to the restriction of attributes (columns) selected from a relation or table. When requesting information from a table, you can ask to view all the columns. For example, in the HR.DEPARTMENTS table, you can retrieve all rows and all columns with a simple SELECT statement. This query will return DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID information for every department record stored in the table. What if you wanted a list containing only the DEPARTMENT_NAME and MANAGER_ID columns? Well, you would request just those two columns from the table. This restriction of columns is called projection.
Selection refers to the restriction of the tuples or rows selected from a relation (table). It is often not desirable to retrieve every row from a table. Tables may contain many rows and, instead of asking for all of them, selection provides a means to restrict the rows returned. Perhaps you have been asked to identify only the employees who belong to department 30. With selection it is possible to limit the results set to those rows of data which have a DEPARTMENT_ID value of 30.
Joining, as a relational concept, refers to the interaction of tables with each other in a query. Third normal form, as discussed in Chapter 1, presented the notion of separating different types of data into autonomous tables to avoid duplication and maintenance anomalies and to associate related data using primary and foreign key relationships. These relationships provide the mechanism to join tables with each other. Joining is discussed extensively in Chapter 7.
Assume there is a need to retrieve the e-mail addresses for all employees who work in the Sales department. The EMAIL column belongs to the EMPLOYEES table, while the DEPARTMENT_NAME column belongs to the DEPARTMENTS table. Projection and selection from the DEPARTMENTS table may be used to obtain the DEPARTMENT_ID value that corresponds to the Sales department. The matching rows in the EMPLOYEES table may be joined to the DEPARTMENTS table based on this common DEPARTMENT_ID value. The EMAIL column may then be projected from this set of results.
The SQL SELECT statement is mathematically governed by these three tenets. An unlimited combination of projections, selections, and joins provides the language to extract the relational data required.
The three concepts of projection, selection, and joining, which form the underlying basis for the capabilities of the SELECT statement, are always measured in the exam. These concepts may be presented in a list with other false concepts, and you may be asked to choose the correct three fundamental concepts. Or a list of SQL statements may be presented, and you may be asked to choose the statement that demonstrates one or more of these concepts.
CERTIFICATION OBJECTIVE 2.02
Execute a Basic SELECT Statement
The practical capabilities of the SELECT statement are realized in its execution. The key to executing any query language statement is a thorough understanding of its syntax and the rules governing its usage. This topic is discussed first. It is followed by a discussion of the execution of a basic query before expressions and operators, which exponentially increase the utility of data stored in relational tables, are introduced. Next, the concept of a null value is demystified, as its pitfalls are exposed. These topics will be covered in the following four sections:
The primitive SELECT statement
Syntax rules
SQL expressions and operators
The NULL concept
The Primitive SELECT Statement
In its most primitive form, the SELECT statement supports the projection of columns and the creation of arithmetic, character, and date expressions. It also facilitates the elimination of duplicate values from the results set. The basic SELECT statement syntax is as follows:
SELECT *|{[DISTINCT] column|expression [alias],…}
FROM table;
The special keywords or reserved words of the SELECT statement syntax appear in uppercase. When using the commands, however, the case of the reserved words in your query statement does not matter. The reserved words cannot be used as column names or other database object names. SELECT, DISTINCT, and FROM are three keyword elements. A SELECT statement always comprises two or more clauses. The two mandatory clauses are the SELECT clause and the FROM clause. The pipe symbol | is used to denote OR. So you can read the first form of the above SELECT statement as:
SELECT *
FROM table;
In this format, the asterisk symbol (*) is used to denote all columns. SELECT * is a succinct way of asking the Oracle server to return all possible columns. It is used as a shorthand, time-saving symbol instead of typing in SELECT column1, column2,…,columnX, to select all the columns. The FROM clause specifies which table to query to fetch (project) the columns requested in the SELECT clause.
You can issue the following SQL command to retrieve all the columns and all the rows from the REGIONS table in the HR schema:
As shown in Figure 2-2, when this command is executed in SQL*Plus, it returns all the rows of data and all the columns that belong to this table. Use of the asterisk in a SELECT statement is sometimes referred to as a “blind” query because the exact columns to be fetched are not specified.
FIGURE 2-2 Projecting all columns from the REGIONS table
The second form of the basic SELECT statement has the same FROM clause as the first form, but the SELECT clause is different:
SELECT {[DISTINCT] column|expression [alias],…}
FROM table;
This SELECT clause can be simplified into two formats:
SELECT column1 (possibly other columns or expressions) [alias optional]
OR
SELECT DISTINCT column1 (possibly other columns or expressions) [alias optional]
An alias is an alternative name for referencing a column or expression. Aliases are typically used for displaying output in a user-friendly manner. They also serve as shorthand when referring to columns or expressions to reduce typing. Aliases will be discussed in detail later in this chapter. By explicitly listing only the relevant columns in the SELECT clause, you, in effect, project the exact subset of the results you wish to retrieve. The following statement will return just the REGION_NAME column subset of the REGIONS table, as shown in Figure 2-2:
You may be asked to obtain all the job roles in the organization that employees have historically fulfilled. For this you can issue the command SELECT * FROM JOB_HISTORY. However, in addition, the SELECT * construct returns the EMPLOYEE_ID, START_DATE, and END_DATE columns. The uncluttered results set containing only JOB_ID and DEPARTMENT_ID columns can be obtained with the statement shown in Figure 2-3, executed in SQL*Plus.
FIGURE 2-3 Projecting specific columns from the JOB_HISTORY table
Using the DISTINCT keyword allows duplicate rows to be eliminated from the results set. In numerous situations a unique set of rows is required. It is important to note that the criterion employed by the Oracle server in determining whether a row is unique or distinct depends entirely on what is specified after the DISTINCT keyword in the SELECT clause. Selecting distinct JOB_ID values from the JOB_HISTORY table will return the eight distinct job types, as shown in Figure 2-4.
FIGURE 2-4 Selecting unique JOB_IDs from the JOB_HISTORY table
Compare this output to Figure 2-3, where ten rows are returned. Can you see that there are two occurrences of the AC_ACCOUNT and ST_CLERK JOB_ID values? These are the two duplicate rows that have been eliminated by looking for distinct JOB_ID values. Selecting the distinct DEPARTMENT_ID column from the JOB_HISTORY table returns only six rows, as Figure 2-5 demonstrates. DEPARTMENT_ID values 50, 80, 90, and 110 each occur twice in the JOB_HISTORY table, and thus four rows have been eliminated by searching for distinct DEPARTMENT_ID values.
FIGURE 2-5 Selecting unique DEPARTMENT_IDs from the JOB_HISTORY table
An important feature of the DISTINCT keyword is the elimination of duplicate values from combinations of columns. There are ten rows in the JOB_HISTORY table. Eight rows contain distinct JOB_ID values. Six rows contain distinct DEPARTMENT_ID values. Can you guess how many rows contain distinct combinations of JOB_ID and DEPARTMENT_ID values? As Figure 2-6 reveals, there are nine rows returned in the results set that contain distinct JOB_ID and DEPARTMENT_ID combinations, with one row from Figure 2-3 having been eliminated. This is, of course, the row that contains a JOB_ID value of ST_CLERK and a DEPARTMENT_ID value of 50.
FIGURE 2-6 Unique JOB_ID, DEPARTMENT_ID combinations from JOB_HISTORY
The ability to project specific columns from a table is very useful. Coupled with the ability to remove duplicate values or combinations of values this empowers you to assist with basic user reporting requirements. In many application databases, tables can sometimes store duplicate data. End user reporting frequently requires this data to be presented as a manageable set of unique records. This is something you now have the ability to do. Be careful, though, when using blind queries to select data from large tables. Executing a SELECT * FROM huge_table; statement may cause performance issues if the table contains millions of rows of data.
Syntax Rules
SQL is a fairly strict language in terms of syntax rules, but it remains simple and flexible enough to support a variety of programming styles. This section discusses some of the basic rules governing SQL statements.
Uppercase or Lowercase
It is a matter of personal taste about the case in which SQL statements are submitted to the database. The examples used thus far have been written in mixed case, uppercase for reserved words and lowercase for the remainder of the statement for illustrative purposes. Many developers, including the author of this book, prefer to write their SQL statements in lowercase. There is also a common misconception that SQL reserved words need to be specified in uppercase. Again, this is up to you. Adhering to a consistent and standardized format is advised.
The following three statements are syntactically equivalent:
There is one caveat regarding case sensitivity. When interacting with literal values, case does matter. Consider the JOB_ID column from the JOB_HISTORY table. This column contains rows of data which happen to be stored in the database in uppercase, for example, SA_REP and ST_CLERK. When requesting that the results set be restricted by a literal column, the case is critical. The Oracle server treats the request for all the rows in the JOB_HISTORY table that contain a value of St_Clerk in the JOB_ID column differently from the request for all rows which have a value of ST_CLERK in the JOB_ID column.
SQL statements may be submitted to the database in lowercase, uppercase, or mixed case. You must pay careful attention to case when interacting with character literal data and aliases. Asking for a column called JOB_ID or job_id returns the same column, but asking for rows where the JOB_ID value is PRESIDENT is different from asking for rows where the JOB_ID value is President. Character literal data should always be treated in a case-sensitive manner.
Metadata about different database objects is stored by default in uppercase in the data dictionary. If you query a database dictionary table to return a list of tables owned by the HR schema, it is likely that the table names returned are stored in uppercase. This does not mean that a table cannot be created with a lowercase name; it can be. It is just more common and is the default behavior of the Oracle server to create and store tables, columns, and other database object metadata in uppercase in the database dictionary.
Statement Terminators
Semicolons are generally used as SQL statement terminators. SQL*Plus always requires a statement terminator, and usually a semicolon is used. A single SQL statement or even groups of associated statements are often saved as script files for future use. Individual statements in SQL scripts are commonly terminated by a line break (or carriage return) and a forward slash on the next line, instead of a semicolon. You can create a SELECT statement, terminate it with a line break, include a forward slash to execute the statement, and save it in a script file. The script file can then be called from within SQL*Plus. Note that SQL Developer does not require a statement terminator if only a single statement is present, but it will not object if one is used. It is good practice to always terminate your SQL statements with a semicolon. Several examples of SQL*Plus statements follow:
The first example of code demonstrates two important rules. First, the statement is terminated by a semicolon. Second, the entire statement is written on one line. It is entirely acceptable for a SQL statement to either be written on one line or to span multiple lines as long as no words in the statement span multiple lines. The second sample of code demonstrates a statement that spans three lines that is terminated by a new line and executed with a forward slash.
Indentation, Readability, and Good Practice
Consider the following query:
This example highlights the benefits of indenting your SQL statement to enhance the readability of your code. The Oracle server does not object if the entire statement is written on one line without indentation. It is good practice to separate different clauses of the SELECT statement onto different lines. When an expression in a clause is particularly complex, it is often better to separate that term of the statement onto a new line. When developing SQL to meet your reporting needs, the process is often iterative. The SQL interpreter is far more useful during the development process if complex expressions are isolated on separate lines, since errors are usually thrown in the format of: “ERROR at line X:”. This makes the debugging process much simpler.
A common technique employed by some exam question designers measures attention to detail. A single missing punctuation mark like a semicolon may make the difference between a correct answer and an incorrect one. Incorrect spelling of object names further tests attention to detail. You may be asked to choose the correct statement that queries the REGIONS table. One of the options may appear correct but references the REGION table. This misspelling can lead to an incorrect statement being chosen.
| SCENARIO & SOLUTION |
| You want to construct and execute queries against tables stored in an Oracle database. Are you confined to using SQL*Plus or SQL Developer? |
No. Oracle provides SQL*Plus and SQL Developer as free tools to create and execute queries. There are numerous tools available from Oracle (for example, Discoverer, APEX, and JDeveloper) and other third-party vendors that provide an interface to the tables stored in an Oracle database. |
| To explore your database environment further, you would like a list of tables, owned by your current schema, available for you to query. How do you interrogate the database dictionary to provide this metadata? |
The data dictionary is a set of tables and views of other tables that can be queried via SQL. The statement SELECT table_name FROM user_tables; queries the database dictionary for a list of table names that belong to the current user. |
| When querying the JOBS table for every row containing just the JOB_ID and MAX_SALARY columns, is a projection, selection, or join being performed? |
A projection is performed since the columns in the JOBS table have been restricted to the JOB_ID and MAX_SALARY columns. |
EXERCISE 2-2
Answering Our First Questions with SQL
In this step-by-step exercise, a connection is made using SQL*Plus as the HR user to answer two questions using the SELECT statement.
Question 1: How many unique departments have employees currently working in them?
1. Start SQL*Plus and connect to the HR schema.
2. You may initially be tempted to find the answer in the DEPARTMENTS table. A careful examination reveals that the question asks for information about employees. This information is contained in the EMPLOYEES table.
3. The word “unique” should guide you to use the DISTINCT keyword.
4. Combining steps 2 and 3, you can construct the following SQL statement:
5. As shown in the following illustration, this query returns 12 rows. Notice that the third row is empty. This is a null value in the DEPARTMENT_ID column.
6. The answer to the first question is therefore: Eleven unique departments have employees working in them, but at least one employee has not been assigned to a department.
Question 2: How many countries are there in the Europe region?
1. This question comprises two parts. Consider the REGIONS table, which contains four regions each uniquely identified by a REGION_ID value, and the COUNTRIES table, which has a REGION_ID column indicating which region a country belongs to.
2. The first query needs to identify the REGION_ID of the Europe region. This is accomplished by the SQL statement:
3. The following illustration shows that the Europe region has a REGION_ID value of 1:
4. To identify which countries have 1 as their REGION_ID, you need to execute the following SQL query:
5. Manually counting the country rows with a REGION_ID of 1 in the following illustration helps answer the second question:
6. The answer to the second question is therefore: There are eight countries in the Europe region as far as the HR data model is concerned.
SQL Expressions and Operators
The general form of the SELECT statement introduced the notion that columns and expressions are selectable. An expression is usually made up of an operation being performed on one or more column values. The operators that can act upon column values to form an expression depend on the data type of the column. They are the four cardinal arithmetic operators (addition, subtraction, multiplication, and division) for numeric columns; the concatenation operator for character or string columns; and the addition and subtraction operators for date and timestamp columns. As in regular arithmetic, there is a predefined order of evaluation (operator precedence) when more than one operator occurs in an expression. Round brackets have the highest precedence. Division and multiplication operations are next in the hierarchy and are evaluated before addition and subtraction, which have lowest precedence. These precedence levels are shown in Table 2-1.
TABLE 2-1 Precedence of Arithmetic Operators
Operators with the same level of precedence are evaluated from left to right. Round brackets may therefore be used to enforce nondefault operator precedence. Using brackets generously when constructing complex expressions is good practice and is encouraged. It leads to readable code that is less prone to error. Expressions expose a large number of useful data manipulation possibilities.
Arithmetic Operators
Consider the example of the JOB_HISTORY table, which stores the start date and end date of an employee’s term in a previous job role. It may be useful for tax or pension purposes, for example, to calculate how long an employee worked in that role. This information can be obtained using an arithmetic expression. There are a few interesting elements of both the SQL statement and the results returned from Figure 2-7 that warrant further discussion.
FIGURE 2-7 Arithmetic expression to calculate number of hours worked
Seven terms have been specified in the SELECT clause. The first four are regular columns of the JOB_HISTORY table, namely: EMPLOYEE_ID, JOB_ID, START_DATE, and END_DATE. The latter two terms provide the source information required to calculate the number of days and hours that an employee filled a particular position. Consider employee number 176 on the ninth row of output. This employee started as a Sales Manager on January 1, 2007, and ended employment on December 31, 2007. Therefore, this employee worked for exactly one year, which, in 2007, consisted of 365 days or 2,920 hours.
The number of days for which an employee was employed can be calculated by using the fifth term in the SELECT clause, which is an expression. This expression demonstrates that arithmetic performed on columns containing date information returns numeric values that represent a certain number of days. The sixth term closely resembles the fifth but calculates the number of hours worked by additionally multiplying by 8 (assuming an 8-hour workday).
To enforce operator precedence of the subtraction and addition operations in the sixth term, the subexpression end_date-start_date+1 is enclosed in round brackets and then multiplied by 8 to obtain the correct number of hours worked. The seventh term is evaluated by first multiplying 1 by 8, which is added to end_date-start_date, which returns the incorrect results.
A hypothetical formula for predicting the probability of a meteor shower in a particular geographic region has been devised. The two expressions listed in Figure 2-8 are identical except for the Meteor Shower Probability % expression. However, as the results in the following table demonstrate, a different calculation is being made by each expression. Notice that the two expressions differ very slightly. Expression 2 has a pair of parentheses at the very end, enclosing (10 - 5). Consider how the expressions are evaluated for the Asia region where REGION_ID is 3 as shown in the following table:
FIGURE 2-8 Use of the concatenation and arithmetic operators
Expressions offer endless possibilities and are one of the fundamental constructs in SELECT statements. As you practice SQL on your test database environment, you may encounter two infamous Oracle errors: “ORA-00923: FROM keyword not found where expected” and “ORA-00942: table or view does not exist”. These are generally indicative of spelling or punctuation errors, such as missing enclosing quotes around character literals. Do not be perturbed by these messages. Remember, you cannot cause damage to the database if all you are doing is selecting data. It is a read-only operation, and the worst you can do is execute a nonperformant query.
Expression and Column Aliasing
Figure 2-7 introduced a new concept called column aliasing. Notice how the expression column has a meaningful heading named Days Worked. This heading is an alias. An alias is an alternate name for a column or an expression. If this expression did not make use of an alias, the column heading would be (END_DATE-START_DATE)+1, which is unattractive and not very descriptive. Aliases are especially useful with expressions or calculations and may be implemented in several ways. There are a few rules governing the use of column aliases in SELECT statements. In Figure 2-7, the alias given for the calculated expression called “Days Worked” was specified by leaving a space and entering the alias in double quotation marks. These quotation marks are necessary for two reasons. First, this alias is made up of more than one word. Second, case preservation of an alias is only possible if the alias is double quoted. As Figure 2-9 shows, an “ORA-00923: FROM keyword not found where expected” error is returned when a multi-worded alias is not double quoted.
FIGURE 2-9 Use of column and expression aliases
The ORA-00923 error is not randomly generated by the server. The Oracle interpreter tries to process the statement and finds a problem with it. As it processes this particular statement, it finds a problem with line 2, column 43 at the point of the problem: the word Worked. Line 2 was processed and the expression was aliased with the word Days. The space after Days indicates to the Oracle interpreter that, since there is no additional comma to indicate another term belonging to the SELECT clause, it is complete. Therefore, it expects to find the FROM clause next. Instead it finds the word Worked and yields this error. Error messages from the Oracle server are informative, and you should read them carefully to resolve problems. This error is avoided by enclosing an alias that contains a space or other special characters, such as # and $, in double quotation marks, as shown around the alias “Days Worked” in Figure 2-7.
The second example in Figure 2-9 illustrates another interesting characteristic of column aliasing. Double quotation marks have once again been dispensed with, and an underscore character is substituted for the space between the words to avoid an error being returned. The Oracle interpreter processes the statement, finds no problem, and executes it. Notice that, although the alias was specified as Days_Worked, with only the title letters of the alias being capitalized, the expression heading was returned as DAYS_WORKED: all letters were automatically converted to uppercase. Thus, to preserve the case of the alias, it must be enclosed in double quotation marks.
The aliases encountered so far have been specified by leaving a space after a column or expression and inserting the alias. SQL offers a more formal way of inserting aliases. The AS keyword is inserted between the column or expression and the alias. Figure 2-10 illustrates the mixed use of the different types of column aliasing. Both the EMPLOYEE_ID and JOB_ID columns are aliased using the AS keyword, while the “Days Worked” expression is aliased using a space. The AS keyword is optional since it is also possible to use a space before specifying an alias, as discussed earlier. Use of the AS keyword does, however, improve the readability of SQL statements, and the author believes it is a good SQL coding habit to form.
FIGURE 2-10 Use of the AS keyword to specify column aliases
Character and String Concatenation Operator
The double pipe symbols || represent the character concatenation operator. This operator is used to join character expressions or columns together to create a larger character expression. Columns of a table may be linked to each other or to strings of literal characters to create one resultant character expression.
Figure 2-8 shows that the concatenation operator is flexible enough to be used multiple times and almost anywhere in a character expression. Here, the character literal "The" is concatenated to the data contents of the REGION_NAME column. This new string of characters is further concatenated to the character literal "region is on Planet Earth", and the whole expression is aliased with the friendly column heading “Planetary Location”. Notice how each row in the results set is constructed by the systematic application of the expression to every row value from the table.
Consider the first data row from the “Planetary Location” expression column. It returns “The Europe region is on Planet Earth”. A legible sentence for the rows of data has been created by concatenating literal strings of characters and spaces to either side of each row’s REGION_NAME column value. The REGION_ID column has been aliased to show that regular columns as well as expressions may be aliased. Further, column headings are by default displayed in uppercase but can be overridden using an alias like “Region Id”. The data types of the columns being queried determine how SQL*Plus and SQL Developer present their default data outputs. If the data type is numeric, then the column data is formatted to be right aligned. If the data type is character or date, then the column data is formatted to be left aligned.
Literals and the DUAL Table
Literal values in expressions are a common occurrence. These values refer to numeric, character, or date and time values found in SELECT clauses that do not originate from any database object. Concatenating character literals to existing column data can be useful, as introduced in Figure 2-8. What about processing literals that have nothing to do with existing column data? To ensure relational consistency, Oracle offers a clever solution to the problem of using the database to evaluate expressions that have nothing to do with any tables or columns. To get the database to evaluate an expression, a syntactically legal SELECT statement must be submitted. What if you wanted to know the sum of two numbers or two numeric literals? These questions can only be answered by interacting with the database in a relational manner. Oracle solves the problem of relational interaction with the database operating on literal expressions by offering a special table called DUAL. Recall the DUAL table described in Figure 2-1. It contains one column called DUMMY of character data type. You can execute the query SELECT * FROM dual, and the data value “X” is returned as the contents of the DUMMY column. The DUAL table allows literal expressions to be selected from it for processing and returns the expression results in its single row. It is exceptionally useful since it enables a variety of different processing requests to be made from the database. You may want to know how many seconds there are in a year. Figure 2-11 demonstrates an arithmetic expression executed against the DUAL table. Testing complex expressions during development, by querying the DUAL table, is an effective method to evaluate whether these expressions are working correctly. Literal expressions can be queried from any table, but remember that the expression will be processed for every row in the table.
FIGURE 2-11 Using the DUAL table
The preceding statement will return four lines in the results set, since there are four rows of data in the REGIONS table.
Two Single Quotes or the Alternative Quote Operator
The literal character strings concatenated so far have been singular words prepended and appended to column expressions. These character literals are specified using single quotation marks. For example:
What about character literals that contain single quotation marks? For example, plural possessives pose a particular problem for character literal processing (the possessive case of most English plural nouns are formed by adding an apostrophe to the end if it ends with an “s”; an apostrophe and an “s” are added to singular nouns). Consider the following statement:
As the example in Figure 2-12 shows, executing this statement causes an ORA-01756 Oracle error to be generated. It might seem like an odd error, but upon closer examination, the Oracle interpreter successfully processes the SELECT statement until position 16, at which point it expects a FROM clause. Position 1 to position 16 is:
FIGURE 2-12 Error while dealing with literals with implicit quotes
The Oracle server processes this segment to mean that the character literal 'Plural' is aliased as column “s”. At this point, the interpreter expects a FROM clause, but instead finds the word “have”. It then generates an error.
So, how are words that contain single quotation marks dealt with? There are essentially two mechanisms available. The most popular of these is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string. Figure 2-13 demonstrates how the previous error is avoided by replacing the character literal 'Plural's with the literal 'Plural""s.
FIGURE 2-13 Use of two single quotes with literals with implicit quotes
The second example in Figure 2-13 shows that using two single quotes to handle each naturally occurring single quote in a character literal can become messy and error prone as the number of affected literals increases. Oracle offers a neat way to deal with this type of character literal in the form of the alternative quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as the special pair of symbols that enclose or wrap any other character literal. These character-enclosing symbols could have been anything other than single quotation marks.
Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols. The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly braces}, [square brackets], or <angle brackets>. Using the q operator, the character delimiter can effectively be changed from a single quotation mark to any other character, as shown in Figure 2-14.
FIGURE 2-14 The alternate quote (q) operator
The syntax of the alternative quote operator is as follows:
q'delimitercharacter literal which may include the single quotes delimiter'
where delimiter can be any character or bracket. The first and second examples in Figure 2-14 show the use of angle and square brackets as character delimiters, while the third example demonstrates how an uppercase “X” has been used as the special character delimiter symbol through the alternative quote operator.
The NULL Concept
The concept of a null value was introduced in the earlier discussion of the DESCRIBE command. Both the number zero and a blank space are different from null since they occupy space. Null refers to an absence of data. A row that contains a null value lacks data for that column. Null is formally defined as a value that is unavailable, unassigned, unknown, or inapplicable. In other words, the rules of engaging with null values need careful examination. Failure to heed the special treatment that null values require will almost certainly lead to an error, or worse, an inaccurate answer.
INSIDE THE EXAM
There are two certification objectives in this chapter. The capabilities of the SELECT statement introduce the three fundamental theoretical concepts of projection, selection, and joining. Practical examples that illustrate selection include building the SELECT clause and using the DISTINCT keyword to limit the rows returned. Projection is demonstrated in examples where columns and expressions are restricted for retrieval. The second objective of executing a SQL statement measures your understanding of the basic form of the SELECT statement. The exam measures two aspects. First, syntax is measured: you are required to spot syntax errors. SQL syntax errors are raised when the Oracle interpreter does not understand a statement. These errors could take the form of statements missing terminators such as a missing semicolon, not enclosing character literals in appropriate quote operators, or statements making use of invalid reserved words.
Second, the meaning of a statement is measured. You will be presented with a syntactically legitimate statement and asked to choose between accurate and inaccurate descriptions of that statement. The exam measures knowledge around the certification objectives using multiple choice format questions. Your understanding of column aliasing, arithmetic and concatenation operators, character literal quoting, the alternative quote operator, SQL statement syntax, and basic column data types will be tested.
Null values may be a tricky concept to come to grips with. The problem stems from the absence of null on a number line. It is not a real, tangible value that can be related to the physical world. Null is a placeholder in a nonmandatory column until some real data is stored in its place. Until then, beware of conducting arithmetic with null columns.
This section focuses on interacting with null column data with the SELECT statement and its impact on expressions.
Not Null and Nullable Columns
Tables store rows of data that are divided into one or more columns. These columns have names and data types associated with them. Some of them are constrained by database rules to be mandatory columns. It is compulsory for some data to be stored in the NOT NULL columns in each row. When columns of a table, however, are not compelled by the database constraints to hold data for a row, these columns run the risk of being empty.
In Figure 2-15, the EMPLOYEES table is described, and a few columns are selected from it. There are five NOT NULL columns and six NULLABLE columns. Nullable is a term sometimes used to describe a column that is allowed to contain null values. One of the nullable columns is the COMMISSION_PCT column. Figure 2-15 shows the first five rows of data from the EMPLOYEES table. This is sufficient to illustrate that all these employee records have null values in their COMMISSION_PCT columns.
FIGURE 2-15 Null values in the COMMISSION_PCT column
SQL Developer makes it simple to observe null values in columns, as displayed in Figure 2-16. Here, the word (null) is displayed in the Query Result tab when a null value is encountered, as with the COMMISSION_PCT column. SQL Developer supports customizing this default description of null column data.
FIGURE 2-16 Null arithmetic always returns a null value.
The column aliased as “Null Arithmetic” is an expression made up of COMMISSION_PCT + SALARY + 10. Instead of returning a numeric value, this column returns null. There is an important reason for this:
Any arithmetic calculation with a NULL value always returns NULL.
Oracle offers a mechanism for interacting arithmetically with NULL values using the general functions discussed in Chapter 5. As the column expression aliased as “Division by Null” illustrates, even division by a null value results in null, unlike division by zero, which results in an error. Finally, notice the impact of the null keyword when used with the character concatenation operator. Null is concatenated between the FIRST_NAME and LAST_NAME columns, yet it has no impact. The character concatenation operators ignore null, while the arithmetic operations involving null values always result in null.
Foreign Keys and Nullable Columns
Data model design sometimes leads to problematic situations when tables are related to each other via a primary and foreign key relationship, but the column that the foreign key is based on is nullable.
The DEPARTMENTS table has as its primary key the DEPARTMENT_ID column. The EMPLOYEES table has a DEPARTMENT_ID column that is constrained by its foreign key relationship to the DEPARTMENT_ID column in the DEPARTMENTS table. This means that no record in the EMPLOYEES table is allowed to have in its DEPARTMENT_ID column a value that is not in the DEPARTMENTS table. This referential integrity forms the basis for third normal form and is critical to overall data integrity.
But what about NULL values? Can the DEPARTMENT_ID column in the DEPARTMENTS table contain nulls? The answer is no. Oracle insists that any column that is a primary key is implicitly constrained to be mandatory. But what about implicit constraints on foreign key columns? This is a quandary for Oracle, since in order to remain flexible and cater to the widest audience, it cannot insist that columns related through referential integrity constraints must be mandatory. Further, not all situations demand this functionality.
| SCENARIO & SOLUTION |
| You are constructing an arithmetic expression that calculates taxable income based on an employee’s SALARY and COMMISSION_PCT columns, both of which are nullable. Is it possible to convert the null values in either column to zero to always return a numeric taxable income? |
Yes, but not with the information you have covered so far. Null values require special handling. In Chapter 5, we discuss the NVL function, which provides a mechanism to convert null values into more arithmetic-friendly data values. |
| An alias provides a mechanism to rename a column or an expression. Under what conditions should you enclose an alias in double quotes? |
If an alias contains more than one word or if the case of an alias must be preserved, then it should be enclosed in double quotation marks. Failure to double quote a multi-worded alias will raise an Oracle error. Failure to double quote a single-word alias will result in the alias being returned in uppercase. |
| When working with character literal values that include single quotation marks, how should you specify these literals in the SELECT clause without raising an error? |
There are two mechanisms available. The more common approach is to replace each naturally occurring single quote with two single quotes. The other approach is to make use of the alternate quote operator to specify an alternate pair of characters with which to enclose character literals. |
The DEPARTMENT_ID column in the EMPLOYEES table is actually nullable. Therefore, the risk exists that there are records with null DEPARTMENT_ID values present in this table. In fact, there are such records in the EMPLOYEES table. The HR data model allows employees, correctly or not, to belong to no department. When performing relational joins between tables, it is entirely possible to miss or exclude certain records that contain nulls in the join column. Chapter 7 explores ways to deal with this challenge by making use of outer joins.
EXERCISE 2-3
Experimenting with Expressions and the DUAL Table
In this step-by-step exercise, a connection is made using SQL Developer as the HR user. Use expressions and operators to answer three questions related to the SELECT statement:
Question 1: It was demonstrated earlier how the number of days in which staff worked in a job could be calculated. For how many years did staff work while fulfilling these job roles and what were their EMPLOYEE_ID, JOB_ID, START_DATE, and END_DATE values? Alias the expression column in your query with the alias Years Worked. Assume that a year consists of 365.25 days.
1. Start SQL Developer and connect to the HR schema.
2. The projection of columns required includes EMPLOYEE_ID, JOB_ID, START_DATE, END_DATE, and an expression called Years Worked from the JOB_HISTORY table.
3. The expression can be calculated by dividing 1 plus the difference between END_DATE and START_DATE by 365.25 days, as shown next:
4. Executing the preceding SELECT statement yields the results displayed in the following illustration:
Question 2: Query the JOBS table and return a single expression of the form:
The Job Id for the <job_title’s> job is: <job_id>.
Take note that the job_title should have an apostrophe and an “s” appended to it to read more naturally. A sample of this output for the organization president is: “The Job Id for the President’s job is: AD_PRES.” Alias this column expression as “Job Description” using the AS keyword.
1. There are multiple solutions to this problem. The approach chosen here is to handle the naturally occurring single quotation marks with an additional single quote.
2. A single expression aliased as Job Description is required and may be constructed by dissecting the requirement into the literal "The Job Id for the" being concatenated to the JOB_TITLE column. This string is then concatenated to the literal "'s job is: " which is further concatenated to the JOB_ID column. An additional single quotation mark is added to yield the SELECT statement that follows:
3. The results of this SQL query are shown in the following illustration:
Question 3: Using the DUAL table, calculate the area of a circle with radius 6,000 units, with pi being approximately 22/7. Use the formula: Area = pi × radius × radius. Alias the result as “Area”.
1. Working with the DUAL table may initially seem curious. You get used to it as its functionality becomes more apparent. This question involves selecting a literal arithmetic expression from the DUAL table to yield a single row calculated answer that is not based on the column values in any table.
2. The expression may be calculated using the following SQL statement; note the use of brackets for precedence.
3. The results returned show the approximate area of the circle as 113,142,857 square units.
CERTIFICATION SUMMARY
The SELECT statement construct forms the basis for the majority of interactions that occur with an Oracle database. These interactions may take the form of queries issued from SQL Developer, SQL*Plus, or any number of Oracle or other third-party client tools. At their core, these tools translate requests for information into SELECT statements, which are then executed by the database.
The structure of a table has been described. Rows of data have been retrieved and the set-oriented format of the results was revealed. The results were refined by projection. In other words, your queries can include only the columns you are interested in retrieving and exclude the remaining columns in a table.
SELECT syntax rules are basic and flexible, and language errors should be rare due to its English-like grammar. Statement termination using semicolons, regard for character literal case-sensitivity, and awareness of null values should assist with avoiding errors.
Expressions expose a vista of data manipulation possibilities through the interaction of arithmetic and character operators with column or literal data, or a combination of the two.
The general form of the SELECT statement was explored, and the foundation for the expansion of this statement was constructed.
The Self Test exercises are made up of two components. The first component is comprised of questions that give you an idea about what you may be asked during the exam. The second component enables you to practice the language skills discussed in this chapter in a lab format. The solutions to both categories of questions are discussed in detail in the solutions section.
TWO-MINUTE DRILLList the Capabilities of SQL SELECT Statements
The three fundamental operations that SELECT statements are capable of are projection, selection, and joining. Projection refers to the restriction of columns selected from a table. Using projection, you retrieve only the columns of interest and not every possible column.
Selection refers to the extraction of rows from a table. Selection includes the further restriction of the extracted rows based on various criteria or conditions. This allows you to retrieve only the rows that are of interest and not every row in the table.
Joining involves linking two or more tables based on common attributes. Joining allows data to be stored in third normal form in discrete tables, instead of in one large table.
An unlimited combination of projections, selections, and joins provides the language to extract the relational data required.
A structural definition of a table can be obtained using the DESCRIBE command.
Columns in tables store different types of data using various data types, the most common of which are NUMBER, VARCHAR2, DATE, and TIMESTAMP.
The data type NUMBER(x,y) implies that numeric information stored in this column can have at most x digits, but the whole number portion can have at most (x-y) digits.
The DESCRIBE command lists the names, data types, and nullable status of all columns in a table.
Mandatory columns are also referred to as NOT NULL columns.
Execute a Basic SELECT Statement
The syntax of the primitive SELECT clause is as follows: SELECT *|{[DISTINCT] column|expression [alias],…}
The SELECT statement is also referred to as a SELECT query and comprises at least two clauses, namely the SELECT clause and the FROM clause.
The SELECT clause determines the projection of columns. In other words, the SELECT clause specifies which columns are included in the results returned.
The asterisk (*) operator is used as a wildcard symbol to indicate all columns. So, the statement SELECT * FROM accounts returns all the columns available in the ACCOUNTS table.
The FROM clause specifies the source table or tables from which items are selected.
The DISTINCT keyword preceding items in the SELECT clause causes duplicate combinations of these items to be excluded from the returned results set.
SQL statements should be terminated with a semicolon. As an alternative, a new line can be added after a statement and a forward slash can be used to execute the statement.
SQL statements can be written and executed in lowercase, uppercase, or mixed case. Be careful when interacting with character literals since these are case-sensitive.
Arithmetic operators and the string concatenation operator acting on column and literal data form the basis of SQL expressions.
Expressions and regular columns may be aliased using the AS keyword or by leaving a space between the column or expression and the alias.
If an alias contains multiple words or the case of the alias is important, it must be enclosed in double quotation marks.
Naturally occurring single quotes in a character literal can be selected by making use of either an additional single quote per naturally occurring quote or the alternative quote operator.
The DUAL table is a single column and single row table that is often used to evaluate expressions that do not refer to specific columns or tables.
Columns which are not governed by a NOT NULL constraint have the potential to store null values and are sometimes referred to as nullable columns.
NULL values are not the same as a blank space or zero. NULL values refer to an absence of data. Null is defined as a value that is unavailable, unassigned, unknown, or inapplicable.
Caution must be exercised when working with null values since arithmetic with a null value always yields a null result.
SELF TEST
The following questions will help you measure your understanding of the material presented in this chapter. Read all the choices carefully because there might be more than one correct answer. Choose all the correct answers for each question.
The following test is typical of the questions and format of the OCA 12c examination for the topic “Retrieving Data Using the SQL SELECT Statement.” These questions often make use of the Human Resources schema.
List the Capabilities of SQL SELECT Statements
1. Which query creates a projection of the DEPARTMENT_NAME and LOCATION_ID columns from the DEPARTMENTS table? (Choose the best answer.)
A. SELECT DISTINCT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS;
B. SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS;
C. SELECT DEPT_NAME, LOC_ID FROM DEPT;
D. SELECT DEPARTMENT_NAME AS “LOCATION_ID” FROM DEPARTMENTS;
2. After describing the EMPLOYEES table, you discover that the SALARY column has a data type of NUMBER(8,2). Which SALARY value(s) will not be permitted in this column? (Choose all that apply.)
A. SALARY=12345678
B. SALARY=123456.78
C. SALARY=12345.678
D. SALARY=123456
E. SALARY=12.34
3. After describing the JOB_HISTORY table, you discover that the START_DATE and END_DATE columns have a data type of DATE. Consider the expression END_DATE-START_DATE. (Choose two correct statements.)
A. A value of DATE data type is returned.
B. A value of type NUMBER is returned.
C. A value of type VARCHAR2 is returned.
D. The expression is invalid since arithmetic cannot be performed on columns with DATE data types.
E. The expression represents the days between the END_DATE and START_DATE less one day.
4. The DEPARTMENTS table contains a DEPARTMENT_NAME column with data type VARCHAR2(30). (Choose two true statements about this column.)
A. This column can store character data up to a maximum of 30 characters.
B. This column must store character data that is at least 30 characters long.
C. The VARCHAR2 data type is replaced by the CHAR data type.
D. This column can store data in a column with data type VARCHAR2(50) provided that the contents are at most 30 characters long.
Execute a Basic SELECT Statement
5. Which statement reports on unique JOB_ID values from the EMPLOYEES table? (Choose all that apply.)
A. SELECT JOB_ID FROM EMPLOYEES;
B. SELECT UNIQUE JOB_ID FROM EMPLOYEES;
C. SELECT DISTINCT JOB_ID, EMPLOYEE_ID FROM EMPLOYEES;
D. SELECT DISTINCT JOB_ID FROM EMPLOYEES;
6. Choose the two illegal statements. The two correct statements produce identical results. The two illegal statements will cause an error to be raised:
A. SELECT DEPARTMENT_ID|| ' represents the '|| DEPARTMENT_NAME||' Department' as "Department Info" FROM DEPARTMENTS;
B. SELECT DEPARTMENT_ID|| ' represents the || DEPARTMENT_NAME||' Department' as "Department Info" FROM DEPARTMENTS;
C. select department_id|| ' represents the '||department_name|| ' Department' "Department Info" from departments;
D. SELECT DEPARTMENT_ID represents the DEPARTMENT_NAME Department as "Department Info" FROM DEPARTMENTS;
7. Which expressions do not return NULL values? (Choose all that apply.)
A. select ((10 + 20) * 50) + null from dual;
B. select 'this is a '||null||'test with nulls' from dual;
C. select null/0 from dual;
D. select null||'test'||null as “Test” from dual;
8. Choose the correct syntax to return all columns and rows of data from the EMPLOYEES table.
A. select all from employees;
B. select employee_id, first_name, last_name, first_name, department_id from employees;
C. select % from employees;
D. select * from employees;
E. select *.* from employees;
9. The following character literal expression is selected from the DUAL table: SELECT 'Coda""s favorite fetch toy is his orange ring' FROM DUAL; (Choose the result that is returned.)
A. An error would be returned due to the presence of two adjacent quotes
B. Coda's favorite fetch toy is his orange ring
C. Coda""s favorite fetch toy is his orange ring
D. Coda""s favorite fetch toy is his orange ring'
10. There are four rows of data in the REGIONS table. Consider the following SQL statement: SELECT '6 * 6' “Area” FROM REGIONS; How many rows of results are returned and what value is returned by the Area column? (Choose the best answer.)
A. 1 row returned, Area column contains value 36
B. 4 rows returned, Area column contains value 36 for all 4 rows
C. 1 row returned, Area column contains value 6 * 6
D. 4 rows returned, Area column contains value 6 * 6 for all 4 rows
E. A syntax error is returned.
LAB QUESTION
In this chapter you worked through examples in the Human Resources schema. Oracle provides a number of example schemas for you to experiment with and to learn different concepts from. For the practical exercises, you will be using the Order Entry, or OE, schema. The solutions for these exercises will be provided later using SQL Developer. Using SQL Developer or SQL*Plus, connect to the OE schema and complete the following tasks.
1. Obtain structural information for the PRODUCT_INFORMATION and ORDERS tables.
2. Select the unique SALES_REP_ID values from the ORDERS table. How many different sales representatives have been assigned to orders in the ORDERS table?
3. Create a results set based on the ORDERS table that includes the ORDER_ID, ORDER_DATE, and ORDER_TOTAL columns. Notice how the ORDER_DATE output is formatted differently from the START_DATE and END_DATE columns in the HR.JOB_HISTORY table.
4. The PRODUCT_INFORMATION table stores data regarding the products available for sale in a fictitious IT hardware store. Produce a set of results that will be useful for a salesperson. Extract product information in the format <PRODUCT_NAME> with code: <PRODUCT_ID> has status of: <PRODUCT_STATUS>. Alias the expression as “Product.” The results should provide the LIST_PRICE, the MIN_PRICE, the difference between LIST_PRICE, and MIN_PRICE aliased as “Max Actual Savings”, along with an additional expression that takes the difference between LIST_PRICE and MIN_PRICE and divides it by the LIST_PRICE and then multiplies the total by 100. This last expression should be aliased as “Max Discount %”.
5. Calculate the surface area of the earth using the DUAL table. Alias this expression as “Earth’s Area”. The formula for calculating the area of a sphere is: 4πr2. Assume, for this example, that the earth is a simple sphere with a radius of 3,958.759 miles and that π is 22/7.
SELF TEST ANSWERS
List the Capabilities of SQL SELECT Statements
1. 
B. A projection is an intentional restriction of the columns returned from a table.
A, C, and D are incorrect. A is eliminated since the question has nothing to do with duplicates, distinctiveness, or uniqueness of data. C incorrectly selects nonexistent columns called DEPT_NAME and LOC_ID from a nonexistent table called DEPT. D returns just one of the requested columns: DEPARTMENT_NAME. Instead of additionally projecting the LOCATION_ID column from the DEPARTMENTS table, it attempts to alias the DEPARTMENT_NAME column as LOCATION_ID. 2. A. Columns with NUMBER(8,2) data type can store, at most, eight digits, of which, at most, six digits are to the left of the decimal point. Although A is the correct answer, note that since the question is phrased in the negative, these values are NOT allowed to be stored in such a column. A is not allowed because it contains eight whole number digits, but the data type is constrained to store six whole number digits and two fractional digits.
B, C, D, and E are incorrect, as they can legitimately be stored in this data type. C is allowed since the fractional portion is rounded to two decimal places. D shows that numbers with no fractional part are legitimate values for this column, as long as the number of digits in the whole number portion does not exceed six digits.
3. B and E. The result of arithmetic between two date values represents a certain number of days.
A, C, and D are incorrect. It is a common mistake to expect the result of arithmetic between two date values to be a date as well, so A may seem plausible, but it is false.
4. A and D. The scale of the VARCHAR2 data type, specified in brackets, determines its maximum capacity for storing character data as mentioned by A. If a data value that is at most 30 characters long is stored in any data type, it can also be stored in this column as stated by D.
B and C are incorrect. B is incorrect because it is possible to store character data of any length up to 30 characters in this column. C is false, since the CHAR data type exists in parallel with the VARCHAR2 data type.
Execute a Basic SELECT Statement
5. D. Unique JOB_ID values are projected from the EMPLOYEES table by applying the DISTINCT keyword to just the JOB_ID column.
A, B, and C are incorrect, since A returns an unrestricted list of JOB_ID values including duplicates, B makes use of the UNIQUE keyword in the incorrect context, and C selects the distinct combination of JOB_ID and EMPLOYEE_ID values. This has the effect of returning all the rows from the EMPLOYEES table since the EMPLOYEE_ID column contains unique values for each employee record. Additionally, C returns two columns, which is not what was originally requested.
6. B and D. B and D represent the two illegal statements that will return syntax errors if they are executed. This is a tricky question because it asks for the illegal statements and not the legal statements. B is illegal because it is missing a single quote enclosing the character literal "represents the". D is illegal because it does not make use of single quotes to enclose its character literals.
A and C are incorrect, as they are the legal statements. A and C appear to be different since the case of the SQL statements are different and A uses the alias keyword AS, whereas C just leaves a space between the expression and the alias. Yet both A and C produce identical results.
7. B and D. B and D do not return null values since character expressions are not affected in the same way by null values as arithmetic expressions. B and D ignore the presence of null values in their expressions and return the remaining character literals.
A and C are incorrect. They return null values because any arithmetic expression that involves a null will return a null.
8. D. An asterisk is the SQL operator that implies that all columns must be selected from a table.
A, B, C, and E are incorrect. A uses the ALL reserved word but is missing any column specification and will, therefore, generate an error. B selects some columns but not all columns and, therefore, does not answer the question. C and E make use of illegal selection operators.
9. B. The key to identifying the correct result lies in understanding the role of the single quotation marks. The entire literal is enclosed by a pair of quotes to avoid the generation of an error. The two adjacent quotes are necessary to delimit the single quote that appears in literal B.
A, C, and D are incorrect. A is eliminated since no error is returned. C inaccurately returns two adjacent quotes in the literal expression and D returns a literal with all the quotes still present. The Oracle server removes the quotes used as character delimiters after processing the literal.
10. D. The literal expression '6 * 6' is selected once for each row of data in the REGIONS table.
A, B, C, and E are incorrect. A returns one row instead of four and calculates the product 6 * 6. The enclosing quote operators render 6 * 6 a character literal and not a numeric literal that can be calculated. B correctly returns four rows but incorrectly evaluates the character literal as a numeric literal. C incorrectly returns one row instead of four and E is incorrect, because the given SQL statement can be executed.
LAB ANSWER
The assumption is made that an Oracle database is available for you to practice on. The database administrator (DBA) in your organization may assist you with installing and setting this up. In order for any client tool such as SQL*Plus or SQL Developer to connect to the database, a listener process should be running and the database must be opened. Additionally, you may have to request that the HR and OE schema accounts be unlocked and that the passwords be reset. If these sample schemas are not present, it is a simple matter to get the DBA to run the scripts, which are installed when the database is installed, to create them. Connect to the OE schema using either SQL*Plus or SQL Developer.
1. The DESCRIBE command gives us the structural description of a table. The following illustration shows these two tables being described:
2. The request for unique values usually involves using the DISTINCT keyword as part of your SELECT statement. The two components of the statement involve the SELECT clause and the FROM clause. You were asked for unique SALES_REP_ID values FROM the ORDERS table. It is simple to translate this request into the following SELECT statement:
From the results in the illustration, you can answer the original question: There are nine different sales representatives responsible for orders listed in the ORDERS table, but there is at least one order that contains null values in their SALES_REP_ID fields.
3. When asked to create a results set, it translates to SELECT one or more columns from a table. In this case, your SELECT clause is constructed from the three columns requested. There is no request for unique values, so there is no need to consider the DISTINCT keyword. The FROM clause need only include the ORDERS table to build the following SELECT statement:
Consider the output in the following illustration, specifically the ORDER_DATE column. This column contains the day, month, year, hours, minutes, seconds, and fractional seconds up to six decimal places or accurate up to a millionth of a second. The description of the ORDERS table exposes ORDER_DATE as a TIMESTAMP(6) with LOCAL TIMEZONE column. This means that the data in this column can be stored with fractional precision up to six decimal places and that the data is time zone-aware. Basically, data may be worked on by people in different time zones. So Oracle provides a data type that normalizes the local time to the database time zone to avoid confusion. Compared to the START_DATE and END_DATE columns in the HR.JOB_HISTORY table, the ORDER_DATE column data type is far more sophisticated. Essentially, though, both these data types store date and time information but to differing degrees of precision.
4. The SELECT clause to answer this question should contain an expression aliased as “Product” made up of concatenations of character literals with the PRODUCT_NAME, PRODUCT_ID, and PRODUCT_STATUS columns. Additionally, the SELECT clause must contain the LIST_PRICE and MIN_PRICE columns and two further arithmetic expressions aliased as “Max Actual Savings” and “Max Discount %”. The FROM clause need only include the PRODUCT_INFORMATION table. Proceed by constructing each of the three expressions in turn and put them all together. The “Product” expression could be derived with the following SELECT statement:
The “Max Actual Savings” expression could be derived with the following SELECT statement:
The “Max Discount %” expression takes the calculation for “Max Actual Savings”, divides this amount by the LIST_PRICE, and multiplies it by 100. It could be derived with the following SELECT statement:
These three expressions, along with the two regular columns, form the SELECT clause executed against the PRODUCT_INFORMATION table as shown next:
5. The versatile DUAL table clearly forms the FROM clause. The SELECT clause is more interesting, since no actual columns are being selected, just an arithmetic expression. A possible SELECT statement to derive this calculation could be:
This calculation approximates that planet Earth’s surface area is 197,016,573 square miles.
