Chapter 4 DB

Consider the following statement: SELECT * FROM ITEMS ORDER BY LIST_DATE OFFSET -5 ROWS FETCH FIRST 4 ROWS ONLY; Assume you have a table ITEMS with a column LIST_DATE. What is the result of an attempt to execute the statement? A. It will sort the rows by LIST_DATE and return only the first four rows. B. It will sort the rows by LIST_DATE and return only the last four rows. C. It will fail with a syntax error because of the use of a negative number with OFFSET. D. It will fail with a syntax error because of the use of FIRST and OFFSET together.

A

Consider the following text: DEFINE vRoomNumber PROMPT "Enter a room number: " SELECT ROOM_NUMBER, STYLE, WINDOW FROM SHIP_CABINS WHERE ROOM_NUMBER = &RNBR; What will happen when this script is executed? A. The end user will be prompted to enter a number. B. The script will fail because vRoomNumber in the first line does not have an ampersand prefix. C. The SELECT statement will fail because the substitution variable should not be prefixed by an ampersand since it is already defined with the DEFINE statement. D. The DEFINE statement in line 1 should be preceded by the keyword SET.

A

Review the following data listing for a table called SHIP_CABINS: ROOM_NUMBER STYLE WINDOW ----------- --------- --------- 102 Suite Ocean 103 Ocean 104 The blank values are NULL. Now review the following SQL statement (line numbers are added for readability): 01 SELECT ROOM_NUMBER 02 FROM SHIP_CABINS 03 WHERE (STYLE = NULL) OR (WINDOW = NULL); How many rows will the SQL statement retrieve? A. 0 B. 1 C. 2 D. None because you cannot use parentheses in line 3 to surround the expressions

A

Review the following data listing for the SHIPS table: SHIP_ID SHIP_NAME CAPACITY LENGTH LIFEBOATS ------- ------------- -------- ------ --------- 1 Codd Crystal 2052 855 80 2 Codd Elegance 2974 952 95 Now review the following SQL statement (line numbers are added for readability): 01 SELECT SHIP_ID FROM SHIPS 02 WHERE SHIP_NAME IN ('Codd Elegance','Codd Victorious') 03 OR (LIFEBOATS >= 80 04 OR LIFEBOATS <= 100) 05 AND CAPACITY / LIFEBOATS > 25; Which of the following statements is true about this SELECT statement? A. The syntax is correct. B. The syntax on lines 3 and 4 is incorrect. C. Lines 3 and 4 have correct syntax but could be replaced with OR LIFEBOATS BETWEEN 80 AND 100. D. Line 5 is missing parentheses.

A

To permanently delete a substitution variable named THE_NAME so that it can no longer be used, use: A. UNDEFINE THE_NAME B. SET DEFINE OFF C. REMOVE THE_NAME D. You cannot delete a substitution variable.

A

Which if the following is true of the ORDER BY clause? (Choose two.) A. It is optional. B. It can be used in the UPDATE statement as well as SELECT and DELETE. C. It can sort rows based on data that isn't displayed as part of the SELECT statement. D. If the list of ORDER BY expressions uses the "by position" form, then all expressions in the ORDER BY must use the "by position" form.

A and C

If you are using an ORDER BY to sort values in descending order, in which order will they appear? A. If the data type is numeric, the value 400 will appear first before the value 800. B. If the data type is character, the value 'Michael' will appear first before the value 'Jackson'. C. If the data type is date, the value for June 25, 2010, will appear before the value for August 29, 2010. D. If the data type is character, the value '130' will appear first before '75'.

B

Review the following data listing for a table VENDORS: VENDOR_ID CATEGORY --------- --------------- 1 Supplier 2 Teaming Partner Now review the following SQL statement: SELECT VENDOR_ID FROM VENDORS WHERE CATEGORY IN ('Supplier','Subcontractor','%Partner'); How many rows will the SELECT statement return? A. 2 B. 1 C. 0 D. None because it will fail due to a syntax error

B

SELECT SHIP_NAME FROM SHIPS ORDER BY SHIP_ID, CAPACITY DESC; Assume that all table and column references exist within the database. What can be said of this SELECT statement? A. The rows will sort in order by SHIP_ID and then by CAPACITY. All rows will sort in descending order. B. The rows will sort in order by SHIP_ID in ascending order and then by CAPACITY in descending order. C. The statement will fail to execute because the ORDER BY list includes a column that is not in the select list. D. The statement will fail to execute because there is no WHERE clause.

B

Assume you have a table ITEMS that includes a column STATUS. Which of the following statements is syntactically correct? (Choose all that apply.) A. SELECT * FROM ITEMS FETCH NEXT 20 % ROWS ONLY; B. SELECT * FROM ITEMS FETCH NEXT 20 PERCENT ROWS ONLY; C. SELECT * FROM ITEMS FETCH NEXT 20 ROWS WITH TIES; D. SELECT * FROM ITEMS ORDER BY STATUS FETCH NEXT 20 ROWS WITH TIES;

B, C, D

SELECT PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE, SHIPPING FROM PRODUCTS WHERE (UNIT_PRICE + SHIPPING) * TAX_RATE > 5 ORDER BY LIKE PRODUCT_NAME; Assume all table and column references exist in the database. What can be said of this SELECT statement? A. The statement will execute successfully and as intended. B. The statement will execute but not sort because the ORDER BY clause is wrong. C. The statement will fail to execute because the ORDER BY clause includes the word LIKE. D. None of the above.

C

To list all the currently defined variables, use: A. SHOW ALL B. SHOW DEFINE C. DEFINE D. DEFINE ALL

C

Assume all table name and column name references in the SQL statement that follows are valid. That being said, what is wrong with the syntax of the following SQL statement? SELECT SHIP_ID FROM SHIPS WHERE ((2*LIFEBOATS)+57) - CAPACITY IN (LIFEBOATS*20, LIFEBOATS+LENGTH); A. In the WHERE clause there is a syntax error before the word CAPACITY. B. It needs to have either an equal sign or a not-equal sign. C. In the WHERE clause there is a syntax error after the word IN. D. There is nothing wrong with the syntax.

D

Review the following data listing for a table SHIPS: SHIP_ID SHIP_NAME CAPACITY LENGTH LIFEBOATS ------- ------------- -------- ------ --------- 1 Codd Crystal 2052 855 80 2 Codd Elegance 2974 952 95 In the SHIPS table, SHIP_NAME has a data type of VARCHAR2(20). All other columns are NUMBER. Now consider the following query (note that line numbers have been added for readability): 01 SELECT SHIP_ID 02 FROM SHIPS 03 WHERE CAPACITY BETWEEN 2052 AND 3000 04 AND LENGTH IN ('100','855') 05 AND SHIP_NAME LIKE 'Codd_%'; How many rows will the SELECT statement return? A. None because of a syntax error resulting from a data type conflict in line 4 B. None because line 5 is asking for SHIP names that contain an underscore after the string 'Codd', and none do C. 2 D. 1

D

You can use a substitution variable to replace: A. A floating-point value in a WHERE clause B. The name of a table in a SELECT statement C. Neither D. Both

D