314 creating complex conditions

__________________________ operations are solved after arithmetic operations

comparison

A(n) ____ is used to indicate how data should relate to a given search condition.

comparison operator

Only one search condition may be listed in a WHERE clause.

f

The "equal to" comparison operator is used to search for NULL values.

f

The LIKE operator is used to indicate a range of values.

f

The SORT BY clause is used for displaying the results of a query in a sorted order.

f

The percent sign (%) is used in a search pattern to indicate "exactly one character in this position".

f

To find rows containing a NULL value in a specified column, you must use the search condition of = NULL.

f

When two conditions are joined by the AND logical operator, both of the conditions must be evaluated as FALSE to be included in the query results.

f

condition

identifies what must exist or a requirment that must be met for a record to be included in the results

comparison operator

indicates how data should relate to the search value (greater than, less than)

ESCAPE

indicating when wildcard symbols should be used as literals rather than translated as wildcard characters

Which of the following operators is used when the criteria is based upon a search pattern?

like

______________________ operators have a lower precedence and are evaluated last

logical

NULL VALUE

means no vlaue has been stored in that field.

When performing a search on a(n) ____________________ column, the search condition does not need to be enclosed in single quotation marks.

numeric

primary sort

only one column specified in ORDER BY clause

selection

only see records meeting certain condition. reduces number of records a query returns

%

represents any number of characters (0 or more)

_

represents exactly 1 character

IS NOT NULL

returns records with data available in the field

logical operators (NOT)

reverse the meaning of a search condition

secondary sort

specifies second field to sort by if an exact match occurs between 2 or more rows

Although Oracle11 g is not case sensitive in regards to keywords, table names, and column names, it is case sensitive when comparing search conditions to the data contained in a database table.

t

An ORDER BY clause can reference a column to be used for sorting based upon its position in the SELECT clause.

t

not equal to

!= ^= <>

To sort query output, use the ____________________ clause.

ORDER BY

Which of the following clauses is used to indicate a particular sort sequence for presenting query results?

ORDER BY

the _____________ _________________ clause used to display query results in a sorted order, is listed at the end of SELECT staremetn

ORDER BY

A(n) ____________________ sort can be specified in the ORDER BY clause to indicate that if an exact match occurs during the primary sort, the alternate column listed should be used to determine the presentation sequence.

secondary

logical operators (AND, OR)

used to join multiple search conditions

When sorting the results in ascending order, which of the following values will be presented last in the output?

NULL

When sorting the results in descending order, which of the following values will be presented first in the output?

NULL

The ____________________ logical operator is similar to the IN comparison operator.

OR

When you use the ____ logical operator to create a complex search condition, only one of the conditions must be true for the complex search condition to be true.

OR

Based upon the contents of the ORDERS table in the accompanying figure, which of the following queries will display all orders shipped between April 4, 2003 and April 5, 2003?

both b and c

NULLS FIRST/NULLS LAST

change the order for listing NULL values

_____________ opeations must be solved first

arithmetic

by default results are ordered in ________________ order

ascending

The maximum number of columns that can be specified for sorting data is ____.

255

The operator ^= indicates not equal

T

The ____________________ symbol is used for determining whether or not a value is greater than a specified search condition

>

Which of the following symbols is a wildcard character that can represent any number of characters in a WHERE clause?

%

Which of the following search patterns would find dates occurring only in the year 2003?

%03

Which of the following search patterns could be used to find the word HELLO in Oracle11 g?

%H%

find terms that start with P

'%P'

equality operator

= to evalluate search conditions to return the resutls containing the exact value you provide

Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will retrieve all books published in 2001?

ALL OF THE ABOVE

Which of the following is a valid logical operator?

ALL OF THE ABOVE

Which of the following operators can be used to combine search conditions?

AND

The ____________________ comparison operator is used to indicate that a data value must fall within a range of values to be included in the query results.

BETWEEN AND

The order in which NULL values appear in the results can be overridden by which of the following keywords?

BOTH A AND B

Which of the following search conditions can be used to identify records that have data stored in a column named ColB?

ColB IS NOT NULL

Which of the following search conditions can be used to identify records that do not have data stored in a column named ColB

ColB IS NULL

tp voew [in;osjers om descending order use

DESC

Comparison operators are used to combine search conditions.

F

The "equal to" comparison operator is used to search for NULL values.

F

The maximum number of columns that can be used to sort the results of a query is 52.

F

The percent sign (%) and asterisk (*) symbols can be used with the LIKE comparison operator to create a search pattern

F

When two conditions are joined by the AND logical operator, only one of the conditions must be TRUE to be included in the query results

F

Based upon the contents of the ORDERS table in the accompanying figure, which of the following SELECT statements will retrieve all orders contained in the ORDERS table that have not yet been shipped to the customer?

NONE OF THE ABOVE

Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will list all books stored in the BOOKS table with a retail price of more than $38 sorted by retail price?

SELECT * FROM books WHERE retail >38 ORDER BY retail;

Which of the following queries will display all orders placed in the month of March?

SELECT * FROM orders WHERE orderdate LIKE '%Mar%';

Based upon the contents of the ORDERS table in the accompanying figure, which of the following queries will not list the orders that have been shipped to Atlanta or Seattle?

SELECT * FROM orders WHERE shipcity = 'Atlanta' OR shipcity = 'Seattle';

Based upon the contents of the ORDERS table in the accompanying figure, which of the following queries will list all orders contained in the ORDERS table that have been shipped based upon the customer# and order#?

SELECT * FROM ordersWHERE shipdate IS NOT NULLORDER BY customer#, order#;

Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will return the category and title of all books stored in the BOOKS table presented in order of their category and, for the books in the same category, sort the title of the books in descending order?

SELECT category, title FROM booksORDER BY 1 ASC, 2 DESC;

Based upon the contents of the ORDERS table in the accompanying figure, which of the following SQL statements will display how long it took to ship order # 1007 (based upon when the order was originally placed)?

SELECT order#, shipdate-orderdate FROM orders WHERE order# = 1007

Based upon the contents of the ORDERS table in the accompanying figure, which of the following SQL statements will display how long it took to ship order # 1007 (based upon when the order was originally placed)?

SELECT order#, shipdate-orderdate FROM orders WHERE order# = 1007;

Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will retrieve all book titles that are in the Business or Computer category and have a retail price of more than $35.00?

SELECT title FROM booksWHERE (category = 'BUSINESS' OR category ='COMPUTER' ) AND retail >35;

Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will display the title and retail price of each book stored in the BOOKS table in order of category and retail price?

SELECT title, retail FROM booksORDER BY category, retail;

The first column listed in an ORDER BY clause is considered the primary sort.

T

The numeric order of columns in a SELECT clause can be referenced in the ORDER BY clause.

T

The percent sign (%) and underscore (_) symbols can be used with the LIKE comparison operator to create a search pattern.

T

To indicate that data should be sorted in descending order, use the DESC keyword.

T

A search condition begins with the keyword ____.

WHERE

The ____________________ clause is used to restrict the rows returned by a query.

WHERE

When two conditions are joined by the AND logical operator, both of the conditions must be evaluated as TRUE to be included in the query results.

true

LIKE

unique - used with wildcard characters to search for patterns

IS NULL

used if you need to identify records that have a NULL value

Other comparison operators (BETWEEN..AND, IN, LIKE, IS NULL)

used in conditions with search values that include patterns, ranges or NULL values

Arithmetic operations

used to indicate how a record should relate to a specific search value

wildcard characters

used to represent 1 or more alphanumeric characters available for pattern searches % _

WHERE clause

used to specify conditions that must be true for a record to be included in query results

ORDER BY clause

used to specify the sorted order for displaying query results

BETWEEN... AND

used when searching a field for values falling wihtin a specific range

NOT

used with all comparison operators to reverse the operation