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
