Name:     ID: 
 
Email: 

ITSE1345 Ch 8 Review

True/False
Indicate whether the statement is true or false.
 

 1. 
Although Oracle10g 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.
 

 2. 
Search conditions for data contained in numeric columns must be enclosed in single quotation marks.
 

 3. 
The BETWEEN...AND comparison operator can be used to specify a range of values as a search condition.
 

 4. 
The underscore symbol (_) is used in a search pattern to indicate "exactly one character in this position".
 

 5. 
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.
 

 6. 
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.
 

 7. 
Logical operators are evaluated in the order of NOT, AND, and OR.
 

 8. 
A NULL value is the same as a blank space.
 

 9. 
When sorted in descending order, NULL values will be listed first in the results, unless the user specifies otherwise.
 

 10. 
A column alias that has been defined in the SELECT clause of a SELECT statement cannot be referenced in an ORDER BY clause.
 

 11. 
In a WHERE clause, logical operators are evaluated before comparison operators.
 

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

Modified True/False
Indicate whether the statement is true or false.  If false, change the identified word or phrase to make the statement true.
 

 13. 
Oracle10g is case sensitive when comparing data to a search condition. _________________________

 

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

 

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

 

 16. 
When sorting data, the values will be sorted in ascending order by default. _________________________

 

 17. 
The maximum number of columns that can be used to sort data is 25. _________________________

 

Completion
Complete each statement.
 

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

 

 19. 
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.
 

 

 20. 
The ____________________ logical operator is used to combine search conditions that must both be TRUE to include a row in the results of a query.
 

 

 21. 
The ____________________ logical operator is used to reverse the meaning of a search condition.
 

 

Multiple Choice
Identify the choice that best completes the statement or answers the question.
 

 22. 
The process of retrieving only certain rows based upon a specified condition is known as ____.
a.
selection
c.
retrieval
b.
projection
d.
derivation
 

 23. 
Which of the following clauses is used to restrict the rows returned by a query?
a.
SELECT
c.
WHERE
b.
FROM
d.
ORDER BY
 

 24. 
If a user performs a query that restricts the rows returned based upon a specified date, the date must be enclosed in ____.
a.
double quotation marks (" ")
c.
parentheses ( )
b.
single quotation marks (' ')
d.
none of the above
 

 25. 
Which of the following operators is used when the criteria is based upon a search pattern?
a.
AND
c.
IS NULL
b.
LIKE
d.
BETWEEN...AND
 

 26. 
Which of the following search patterns could be used to find the word HELLO in Oracle10g?
a.
%H%
c.
_EL*
b.
HEL*
d.
%HEL?O
 

 27. 
When the WHERE clause contains multiple types of operators, which of the following is resolved first?
a.
arithmetic operations
c.
logical operators
b.
comparison operators
d.
union operators
 

 28. 
When the WHERE clause contains multiple types of operators, which of the following is resolved last?
a.
arithmetic operations
c.
logical operators
b.
comparison operators
d.
search patterns
 

 29. 
Which of the following search conditions can be used to identify records that do not have data stored in a column named ColB?
a.
ColB = NULL
c.
ColB IS NULL
b.
ColB IS NOT NULL
d.
ColB = ' '
 

 30. 
When sorting the results in ascending order, which of the following values will be presented first in the output?
a.
numeric
c.
NULL
b.
character
d.
date
 

 31. 
When sorting the results in descending order, which of the following values will be presented first in the output?
a.
numeric
c.
NULL
b.
character
d.
date
 

 32. 
To instruct Oracle10g to sort data in descending order, enter ____ after the column name in the WHERE clause.
a.
d
c.
desc
b.
des
d.
none of the above
 

 33. 
To instruct Oracle10g to sort data in ascending order, enter ____ after the column name in the ORDER BY clause.
a.
asc
c.
ascending
b.
a
d.
either a or c
 

 34. 
If there is an exact match between the data used for a primary sort, a(n) ____ can be used to determine the sequence used to display the data.
a.
secondary sort
c.
alternate sort
b.
alternative sort
d.
supplemental sort
 

 35. 
In regards to sorting, if a DISTINCT option is used in the SELECT clause of a query then ____.
a.
an ORDER BY clause can not be added
c.
only a primary sort is allowed
b.
only columns in the SELECT can be used for sorting
d.
None of the above
 
 
Contents of BOOK table
nar001-1.jpg
 

 36. 
Based upon the contents of the BOOKS table, which of the following SQL statements will retrieve all books published by the publisher assigned Pubid 1?
a.
SELECT * FROM books WHERE pubid LIKE "1";
b.
SELECT * FROM books WHERE pubid = 1;
c.
SELECT * FROM books WHERE pubid > 1;
d.
both a and b
 

 37. 
Based upon the contents of the BOOKS table, which of the following queries will retrieve all books published in 2001?
a.
SELECT * FROM books
WHERE pubdate BETWEEN '01-JAN-01' AND '31-DEC-01';
b.
SELECT * FROM books
WHERE pubdate LIKE '%01';
c.
SELECT * FROM books
WHERE pubdate >= '01-JAN-01' AND pubdate <= '31-DEC-01';
d.
all of the above
 

 38. 
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?
a.
SELECT title FROM books
WHERE category = 'BUSINESS' OR 'COMPUTER' AND retail >35;
b.
SELECT title FROM books
WHERE category = 'BUSINESS' OR category ='COMPUTER' AND retail >35;
c.
SELECT title FROM books
WHERE (category = 'BUSINESS' OR category ='COMPUTER' )AND retail >35;
d.
SELECT title FROM books
WHERE category = 'BUSINESS' OR category ='COMPUTER' AND (retail >35);
 

 39. 
Which of the following queries will return all books that cost at least $25.00?
a.
SELECT * FROM books
WHERE cost > $25.00;
b.
SELECT * FROM books
WHERE cost >= 25.00;
c.
SELECT * FROM books
WHERE cost >= $25.00;
d.
none of the above
 

 40. 
Which of the following queries will display all books stored in the BOOKS table that generate more than 60 percent profit?
a.
SELECT * FROM books
WHERE profit > .6;
b.
SELECT * FROM books
WHERE (retail-cost)/cost > .60;
c.
SELECT * FROM books
WHERE (retail-cost)/cost > 60%;
d.
SELECT * FROM books
WHERE (retail-cost)/cost > '60';
 

 41. 
Which of the following queries will retrieve all books stored in the BOOKS table in the Children and Business categories or that have a retail price of at least $42.00?
a.
SELECT * FROM books
WHERE category = 'CHILDREN' OR category = 'BUSINESS' OR retail >= 42;
b.
SELECT * FROM books
WHERE category IN ('CHILDREN', 'BUSINESS') OR retail => 42;
c.
SELECT * FROM books
WHERE category = 'CHILDREN' AND category='BUSINESS' OR retail >=42;
d.
both a and b
 

 42. 
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?
a.
SELECT * FROM books WHERE retail >38 SORT BY retail;
b.
SELECT * FROM books WHERE retail >38 SORTED BY retail;
c.
SELECT * FROM books WHERE retail >38 ORDERED BY retail;
d.
SELECT * FROM books WHERE retail >38 ORDER BY retail;
 

 43. 
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?
a.
SELECT title, retail price FROM books
ORDER BY category, retail;
b.
SELECT title, retail price FROM books
ORDERED BY category, retail;
c.
SELECT title, retail FROM books
SORT BY category, retail;
d.
none of the above
 

 44. 
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?
a.
SELECT category, title FROM books
ORDERED BY 1, 2 desc;
b.
SELECT category, title FROM books
ORDER BY 1 asc, 2 desc;
c.
SELECT category, title FROM books
SORTED BY 1 asc, 2 desc;
d.
SELECT category, title FROM books
SORT BY 1, 2 desc;
 
 
Contents of the ORDERS table
nar002-1.jpg
 

 45. 
Which of the following SQL statements will list all orders placed by customer# 1020 that have not yet been shipped?
a.
SELECT * FROM orders WHERE customer# = 1020 OR shipdate = NULL;
b.
SELECT * FROM orders WHERE customer# = 1020 AND shipdate IS NULL;
c.
SELECT * FROM orders WHERE customer# = 1020 OR shipdate IS NULL;
d.
SELECT * FROM orders WHERE customer# = 1020 AND shipdate = NULL;
 

 46. 
Which of the following queries will not list the orders that have been shipped to Atlanta or Seattle?
a.
SELECT * FROM orders WHERE shipcity = 'ATLANTA' OR shipcity = 'SEATTLE';
b.
SELECT * FROM orders WHERE shipcity IN ( 'ATLANTA' , 'SEATTLE');
c.
SELECT * FROM orders WHERE shipcity = 'Atlanta' OR shipcity = 'Seattle';
d.
SELECT * FROM orders WHERE shipcity LIKE 'AT%' OR shipcity LIKE 'SEA%';
 

 47. 
Which of the following queries will return all orders shipped within three days after the order was received?
a.
SELECT * FROM orders WHERE shipdate-orderdate >= 3;
b.
SELECT * FROM orders WHERE shipdate-orderdate =< 3;
c.
SELECT * FROM orders WHERE NOT shipdate-orderdate => 3;
d.
SELECT * FROM orders WHERE shipdate-orderdate <= 3;
 

 48. 
Which of the following queries will display all orders placed in the month of March?
a.
SELECT * FROM orders WHERE orderdate LIKE '%Mar%';
b.
SELECT * FROM orders WHERE orderdate LIKE 'Mar%';
c.
SELECT * FROM orders WHERE orderdate LIKE "%Mar%";
d.
none of the above
 

 49. 
Which of the following queries will display all orders shipped between April 4, 2003 and April 5, 2003?
a.
SELECT * FROM orders
WHERE shipdate <= '04-APR-03' AND shipdate >= '05-APR-03';
b.
SELECT * FROM orders
WHERE shipdate BETWEEN '04-APR-03' AND '05-APR-03';
c.
SELECT * FROM orders
WHERE shipdate >= ‘04-APR-03’ AND shipdate <= ‘05-APR-03’;
d.
both b and c
 

 50. 
Which of the following queries will list all orders contained in the ORDERS table that have been shipped based upon the customer# and order#?
a.
SELECT * FROM orders
WHERE shipdate IS NOT NULL
ORDER BY customer#, order#;
b.
SELECT * FROM orders
WHERE shipdate IS NULL
ORDER BY customer#, order#;
c.
SELECT * FROM orders
WHERE shipdate IS NOT NULL
ORDERED BY customer#, order#;
d.
SELECT * FROM orders
WHERE shipdate IS  NOT NULL
SORTED BY customer#, order#;
 



 
         Start Over