Chapter 8 - Restricting Rows and Sorting Data
Which of the following symbols represents any number of characters in a pattern search? * ? % -
%
Which of the following displays all books published by Publisher 1 with a retail price of at least $25.00? SELECT * FROM books WHERE pubid = 1 AND retail >= 25; SELECT * FROM books WHERE pubid = 1 OR retail >= 25; SELECT * FROM books WHERE pubid = 1 AND WHERE retail > 25; SELECT * FROM books WHERE pubid = 1, retail >= 25; SELECT * FROM books WHERE pubid = 1, retail >= $25.00;
SELECT * FROM books WHERE pubid = 1 AND retail >= 25;
Which of the following doesn't include the display of books published by Publisher 2 and having a retail price of at least $35.00? SELECT * FROM books WHERE pubid = 2, retail >= $35.00; SELECT * FROM books WHERE pubid = 2 AND NOT retail < 35; SELECT * FROM books WHERE pubid IN (1, 2, 5) AND retail NOT BETWEEN 1 AND 29.99; All the above statements display the specified books. None of the above statements display the specified books.
SELECT * FROM books WHERE pubid = 2 AND NOT retail < 35;
Which of the following includes the book HOW TO GET FASTER PIZZA in the query results? SELECT * FROM books WHERE title LIKE '%AS_E%'; SELECT * FROM books WHERE title LIKE 'AS_E%'; SELECT * FROM books WHERE title = '%AS_E%'; SELECT * FROM books WHERE title = 'AS_E%';
SELECT * FROM books WHERE title LIKE '%AS_E%';
Which of the following returns the book HANDCRANKED COMPUTERS in the results? SELECT * FROM books WHERE title = 'H_N_%'; SELECT * FROM books WHERE title LIKE "H_N_C%"; SELECT * FROM books WHERE title LIKE 'H_N_C%'; SELECT * FROM books WHERE title LIKE '_H%';
SELECT * FROM books WHERE title LIKE 'H_N_C%';
Which of the following includes a customer with the first name BONITA in the results? SELECT * FROM customers WHERE firstname = 'B%'; SELECT * FROM customers WHERE firstname LIKE '%N%'; SELECT * FROM customers WHERE firstname = '%N%'; SELECT * FROM customers WHERE firstname LIKE '_B%';
SELECT * FROM customers WHERE firstname LIKE '%N%';
Which of the following finds all orders placed before April 5, 2009 that haven't yet shipped? SELECT * FROM orders WHERE orderdate < '04-05-09' AND shipdate = NULL; SELECT * FROM orders WHERE orderdate < '05-04-09' AND shipdate IS NULL; SELECT * FROM orders WHERE orderdate < 'APR-05-09' AND shipdate IS NULL; SELECT * FROM orders WHERE orderdate < '05-APR-09' AND shipdate IS NULL; none of the above
SELECT * FROM orders WHERE orderdate < '05-APR-09' AND shipdate IS NULL;
Which of the following SQL statements isn't valid? SELECT address || city || state || zip "Address" FROM customers WHERE lastname = 'SMITH'; SELECT * FROM publisher ORDER BY contact; SELECT address, city, state, zip FROM customers WHERE lastname = "SMITH"; All the above statements are valid and return the expected results.
SELECT address, city, state, zip FROM customers WHERE lastname = "SMITH";
When should a percent sign (%) be used with the LIKE operator?
The percent sign represents any number of characters (zero, one, or more). It should be used when you want a certain letter but don't care what comes after (number, special character, etc.) (or before depending on the placement of the % sign)
When should an underscore symbol ( _ ) be used with the LIKE operator?
The underscore symbol represents exactly one character. It should be used when you want only one letter (number, special character, etc.) after (or before depending on the placement of the % sign) the letter you did enter.
Which clause is used to restrict rows or perform selection? SELECT FROM WHERE ORDER BY
WHERE
Which clause of an SQL query is used to restrict the number of rows returned?
WHERE
Which operator should you use to find NULL values?
WHERE columntitle IS NULL;
When should single quotation marks be used in a WHERE clause?
When nonnumeric data is specified in the condition. (WHERE city = 'London';)
What's the effect of using the NOT operator in a WHERE clause?
It's used to reverse the meaning of search conditions
Which of the following SQL statements is valid? SELECT order# FROM orders WHERE shipdate = NULL; SELECT order# FROM orders WHERE shipdate = 'NULL'; SELECT order# FROM orders WHERE shipdate = "NULL"; None of the statements are valid.
None of the statements are valid. (You can't use an = sign when looking for a NULL value, instead use IS NULL)
The IN comparison operator is similar to which logical operator?
OR
Which clause of an SQL query displays the results in a specific sequence?
ORDER BY
Which of the following SQL statements returns all books published after March 20, 2005? SELECT * FROM books WHERE pubdate > 03-20-2005; SELECT * FROM books WHERE pubdate > '03-20-2005'; SELECT * FROM books WHERE pubdate > '20-MAR-05'; SELECT * FROM books WHERE pubdate > 'MAR-20-05';
SELECT * FROM books WHERE pubdate > '20-MAR-05';
Which operator can you use to find any books with a retail price of at least $24.00?
>=
Which of the following returns a list of all customers' names sorted in descending order by city within state? SELECT name FROM customers ORDER BY desc state, city; SELECT firstname, lastname FROM customers SORT BY desc state, city; SELECT firstname, lastname FROM customers ORDER BY state desc, city; SELECT firstname, lastname FROM customers ORDER BY state desc, city desc; SELECT firstname, lastname FROM customers ORDER BY 5 desc, 6 desc;
SELECT firstname, lastname FROM customers ORDER BY state desc, city desc;
Which of the following doesn't return a customer with the last name THOMPSON in the query results? SELECT lastname FROM customers WHERE lastname = "THOMPSON"; SELECT * FROM customers; SELECT lastname FROM customers WHERE lastname > 'R'; SELECT * FROM customers WHERE lastname < 'V';
SELECT lastname FROM customers WHERE lastname = "THOMPSON";
Which of the following lists each book having a profit of at least $10.00 in descending order by profit? SELECT * FROM books WHERE profit => 10.00 ORDER BY "Profit" desc; SELECT title, retail-cost "Profit" FROM books WHERE profit => 10.00ORDER BY "Profit" desc; SELECT title, retail-cost "Profit" FROM books WHERE "Profit" => 10.00ORDER BY "Profit" desc; SELECT title, retail-cost profit FROM books WHERE retail-cost >= 10.00 ORDER BY "PROFIT" desc; SELECT title, retail-cost "Profit" FROM books WHERE profit => 10.00 ORDER BY 3 desc;
SELECT title, retail-cost profit FROM books WHERE retail-cost >= 10.00 ORDER BY "PROFIT" desc;
Because % is a wildcard character, how can the LIKE operator search for a literal percent sign (%) in a character string?
The LIKE operator includes the ESCAPE option for indicating when wildcard symbols should be used as literals rather than translated as wildcard characters. This option allows the user to select the escape character. The escape character must precede any wildcard characters in the search pattern that should be interpreted literally, not as wildcard characters. The ESCAPE keyword followed by the escape character \, which must be enclosed in single quotation marks (' \ '). (WHERE tvalue LIKE '\%A&T' ESCAPE '\';)
What's the default sort sequence for the ORDER BY clause? ascending descending the order in which records are stored in the table There's no default sort sequence.
ascending
Which of the following lists books generating at least $12.00 in profit? SELECT * FROM books WHERE retail-cost > 12; SELECT * FROM books WHERE retail-cost <= 12; SELECT * FROM books WHERE profit >= 12; SELECT * FROM books WHERE retail-cost => 12.00; none of the above
none of the above
Which of the following represents exactly one character in a pattern search? ESCAPE ? - % none of the above
none of the above
Which of the following lists all books published before June 2, 2004 and all books published by Publisher 4 or in the Fitness category? SELECT * FROM books WHERE category = 'FITNESS' OR pubid = 4 AND pubdate < '06-02-2004'; SELECT * FROM books WHERE category = 'FITNESS' AND pubid = 4 OR pubdate < '06-02-2004'; SELECT * FROM books WHERE category = 'FITNESS' OR (pubid = 4 AND pubdate < '06-02-2004'); SELECT * FROM books WHERE category = 'FITNESS' OR pubid = 4, pubdate < '06-02-04'; none of the above
none of the above (all of these dates are invalid)
Which of the following clauses is used to display query results in a sorted order? WHERE SELECT SORT ORDER none of the above
none of the above (has to be ORDER BY)
Which of the following returns all books published after March 20, 2005? SELECT * FROM books WHERE pubdate > 03-20-2005; SELECT * FROM books WHERE pubdate > '03-20-2005'; SELECT * FROM books WHERE pubdate NOT < '20-MAR-05'; SELECT * FROM books WHERE pubdate NOT < 'MAR-20-05'; none of the above
none of the above (the NOT has to be in front of the pubdate column)
