Database Final

An inline view is a form of a subquery. A. True B. False

A

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

FURNISHING CAT# NUMBER ITEM_NAME VARCHAR2(15 BYTE) ADDED DATE SECTION VARCHAR2(10 BYTE) PK_CAT# STORE_INVENTORY NUM NUMBER AISLE VARCHAR2(7 BYTE) PRODUCT VARCHAR2(15 BYTE) LAST_ORDER DATE PK_NUM SELECT NUM, PRODUCT FROM STORE_INVENTORY INTERSECT SELECT CAT#, ITEM_NAME FROM FURNISHINGS; How many rows will result from this query? A. 0 B. 1 C. 3 D. 6

A

Review the PORTS and SHIPS tables shown in question 7. Then review the following SQL code: 01 SELECT PORT_NAME 02 FROM PORTS P 03 WHERE PORT_ID IN (SELECT HOME_PORT_ID, SHIP_NAME 04 FROM SHIPS 05 WHERE SHIP_ID IN (1,2,3)); Which of the following is true of this statement? A. The statement will fail with a syntax error because of line 3. B. The statement will fail with a syntax error because of line 5. C. Whether the statement fails depends on how many rows are returned by the subquery in lines 3 through 5. D. None of the above.

A

Review the PORTS and SHIPS tables: Next, review the following SQL code: 01 SELECT P.COUNTRY, P.CAPACITY 02 FROM PORTS P 03 WHERE P.PORT_ID > (SELECT S.HOME_PORT_ID 04 FROM SHIPS S WHERE S.LENGTH > 900); You know that there are five rows in the SHIPS table with a length greater than 900. What will result from an attempt to execute this SQL statement? A. An execution error will result because the subquery will return more than one row and the parent query is expecting only one row from the subquery. B. A syntax error will result because PORT_ID and HOME_PORT_ID in line 3 have different column names. C. The statement will execute and produce output as intended. D. None of the above.

A

Review the following SQL statements: CREATE TABLE BOUNCERS (NIGHTCLUB_CODE NUMBER, STRENGTH_INDEX NUMBER); INSERT INTO BOUNCERS VALUES (1, NULL); UPDATE BOUNCERS SET STRENGTH_INDEX = 10; What is the end result of the sql statements listed here? A. The BOUNCERS table will contain one row. B. The BOUNCERS table will contain two rows. C. The UPDATE will fail because there is no where clause D. None of the above.

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

Review the illustration from question 8 and the following SQL code: 01 DELETE FROM PORTS P 02 WHERE PORT_ID NOT EXISTS (SELECT PORT_ID 03 FROM SHIPS 04 WHERE HOME_PORT_ID = P.PORT_ID); The code is attempting to delete any row in the PORTS table that is not a home port for any ship in the SHIPS table, as indicated by the HOME_PORT_ID column. In other words, only keep the PORTS rows that are currently the HOME_PORT_ID value for a ship in the SHIPS table; get rid of all other PORT rows. That's the intent of the SQL statement. What will result from an attempt to execute the preceding SQL statement? A. It will fail because of a syntax error on line 2. B. It will fail because of a syntax error on line 4. C. It will fail because of an execution error in the subquery. D. It will execute successfully and perform as intended.

A

Review the illustration from question 8 and the following SQL code: 01 UPDATE PORTS P 02 SET CAPACITY = CAPACITY + 1 03 WHERE EXISTS (SELECT * 04 FROM SHIPS 05 WHERE HOME_PORT_ID = P.PORT_ID); The PORTS table has 15 rows. The SHIPS table has 20 rows. Each row in PORTS has a unique value for PORT_ID. Each PORT_ID value is represented in the HOME_PORT_ID column of at least one row of the SHIPS table. What can be said of this UPDATE statement? A. The value for CAPACITY will increase once for each of the 15 rows in the PORTS table. B. The value for CAPACITY will increase by 20 for each of the 15 rows in the PORTS table. C. The value for CAPACITY will not increase. D. The statement will fail to execute because of an error in the syntax.

A

SELECT * FROM FURNISHING; CAT# ITEM_NAME ADDED SECTION 1 side table 23-DEC-09 LR 2 desk 12-SEP-09 BR 3 towel 10-OCT-09 BA SELECT * FROM STORE_INVENTORY; NUM AISLE PRODUCT LAST_ORDER 77 F02 jacket 2009-09-09 78 B11 towel 2009-11-11 79 SP01 lava lamp 2009-12-21 ( SELECT PRODUCT FROM STORE_INVENTORY UNION ALL SELECT ITEM_NAME FROM FURNISHINGS ) INTERSECT ( SELECT ITEM_NAME FROM FURNISHINGS WHERE ITEM_NAME = 'Towel' UNION ALL SELECT ITEM_NAME FROM FURNISHINGS WHERE ITEM_NAME = 'Towel' ); How many rows will result from this code? A. 1 B. 2 C. 4 D. 6

A

SELECT * FROM FURNISHING; CAT# ITEM_NAME ADDED SECTION 1 side table 23-DEC-09 LR 2 desk 12-SEP-09 BR 3 towel 10-OCT-09 BA SELECT * FROM STORE_INVENTORY; NUM AISLE PRODUCT LAST_ORDER 77 F02 jacket 2009-09-09 78 B11 towel 2009-11-11 79 SP01 lava lamp 2009-12-21 01 SELECT (SELECT LAST_ORDER FROM STORE_INVENTORY 02 UNION 03 SELECT ADDED "Date Added" FROM FURNISHINGS) 04 FROM ONLINE_SUBSCRIBERS 05 ORDER BY 1; What will happen when this SQL statement is executed? A. It will fail with an execution error on line 1. B. It will execute, but the UNION will not work as expected. C. It will execute and display one column under the "Date Added" heading. D. It will execute and display one column under the "LAST_ORDER" heading.

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

When combining two SELECT statements, which of the following set operators will produce a different result, depending on which SELECT statement precedes or follows the operator? A. MINUS B. UNION ALL C. INTERSECT D. UNION 5. Which of the following

A

When is a query considered a multirow subquery? (Choose the best answer.) A. If it returns multiple rows at the time of execution B. If it may or may not return multiple rows, as determined by its WHERE clause C. If it returns numeric data, regardless of the number of rows of data it returns D. All of the above

A

Which of the following forms of subquery never returns more than one row? A. Scalar B. Correlated C. Multiple-column D. None of the above

A

Which of the following statements are true? (Choose two.) A. You can use a data type conversion function to format numeric data to display with dollar signs and commas. B. The presence of an explicit data type conversion documents your intent in the code. C. Depending on the values, you can successfully use an explicit data type conversion to transform numeric values to text but not the other way around; you can't explicitly convert text to numeric. D. An implicit data type conversion performs faster than an explicit data type conversion.

A and B. One of the most common uses of conversion functions is to format data, such as numeric and date formatting. The use of an explicit data type conversion function clarifies your purpose in the code and your expectation about what sort of values are contained within the various elements of your code.

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

The WITH clause can be used to name a subquery. Which of the following is also true? (Choose two.) A. The name of the subquery can be used in the SELECT statement following the WITH clause. B. The name of the subquery can be joined to other tables in the SELECT statement following the WITH clause. C. The name of the subquery is stored in the database by the WITH statement and can be referenced by other SQL statements in later sessions. D. The name of the subquery can be invoked from within the subquery that is named.

A, B

The output of a function may be used: (Choose three.) A. As an input parameter value to an outer function. B. As a column of output in a SELECT statement. C. As an input value within the VALUES list of an INSERT statement. D. As an alternative to the keyword SET in an UPDATE statement.

A, B, C

Which of the following can a correlated subquery be used in? (Choose three.) A. The SET clause of an UPDATE statement B. The WHERE clause of an UPDATE statement C. The WHERE clause of a DELETE statement D. The FROM clause of a DELETE statement

A, B, C

What can be granted to a role? (Choose all that apply.) A. System privileges B. Object privileges C. Roles D. None of the above

A, B, and C. Both system and object privileges, as well as other roles, can be granted to any given role.

User account MUSKIE owns a table called CBAY. Which of the following statements can be executed by MUSKIE and enable user ONEILL to execute UPDATE statements on the CBAY table? (Choose three.) A. GRANT ALL ON CBAY TO ONEILL; B. GRANT ALL PRIVILEGES TO ONEILL; C. GRANT ALL TO ONEILL; D. GRANT INSERT, UPDATE ON CBAY TO ONEILL;

A, B, and D. All three forms result in the UPDATE privilege being granted to user ONEILL for the CBAY table.

Review this WORK_HISTORY table. Your task is to create a query that will list—for each ship—all of the EMPLOYEE_ID values for all the employees who have the shortest work history for their ship. In other words, if there are two ships, you want to list all the employees assigned to the first ship who have the shortest work history, all the employees assigned to the second ship who have the shortest work history, and so on. Which of the following queries will accomplish this task? (Choose two.) A. SELECT EMPLOYEE_ID FROM WORK_HISTORY W1 WHERE ABS(START_DATE - END_DATE) = (SELECT MIN(ABS(START_DATE - END_DATE)) FROM WORK_HISTORY WHERE SHIP_ID = W1.SHIP_ID); B. SELECT EMPLOYEE_ID FROM WORK_HISTORY W1 WHERE ABS(START_DATE - END_DATE) = (SELECT MIN(ABS(START_DATE - END_DATE)) FROM WORK_HISTORY); C. SELECT EMPLOYEE_ID FROM WORK_HISTORY W1 WHERE ABS(START_DATE - END_DATE) <= ALL (SELECT ABS(START_DATE - END_DATE) FROM WORK_HISTORY WHERE SHIP_ID = W1.SHIP_ID); D. SELECT EMPLOYEE_ID FROM WORK_HISTORY W1 WHERE ABS(START_DATE - END_DATE) < (SELECT MIN(ABS(START_DATE - END_DATE)) FROM WORK_HISTORY WHERE SHIP_ID = W1.SHIP_ID);

A, C

SELECT * FROM FURNISHING; CAT# ITEM_NAME ADDED SECTION 1 side table 23-DEC-09 LR 2 desk 12-SEP-09 BR 3 towel 10-OCT-09 BA SELECT * FROM STORE_INVENTORY; NUM AISLE PRODUCT LAST_ORDER 77 F02 jacket 2009-09-09 78 B11 towel 2009-11-11 79 SP01 lava lamp 2009-12-21 01 SELECT A.SUB_DATE, COUNT(*) 02 FROM ONLINE_SUBSCRIBERS A JOIN 03 (SELECT LAST_ORDER, PRODUCT FROM STORE_INVENTORY 04 UNION 05 SELECT ADDED, ITEM_NAME FROM FURNISHINGS) B 06 ON A.SUB_DATE = B.LAST_ORDER 07 GROUP BY A.SUB_DATE; Where can you add an ORDER BY to this code? (Choose two.) A. At the end of line 5 before the right parenthesis B. Between lines 5 and 6 C. After line 7 D. Nowhere

A, C

Built-in SQL functions: (Choose three.) A. Can be invoked from a DELETE statement's WHERE clause. B. Are written by SQL developers and also known as "user-defined" functions. C. Are available for use from the UPDATE statement. D. Are available for use within a SELECT statement's WHERE clause, as well as the SELECT statement's expression list.

A, C, D

Which of the following can a subquery be used in? (Choose all that apply.) A. An INSERT statement's SELECT B. A GRANT statement C. A WHERE clause in a SELECT statement D. An inline view

A, C, D

Review the illustration from questions 8. Which of the following statements, when executed, will result in an error? A. WITH (SELECT SHIP_ID FROM SHIPS) SELECT PORT_ID FROM PORTS; B. WITH SHIPPER_INFO AS (SELECT SHIP_ID FROM SHIPS) SELECT PORT_ID FROM PORTS; C. WITH SHIPPER_INFO AS (SELECT SHIP_ID FROM SHIPS) SELECT PORT_ID FROM PORTS, SHIPPER_INFO; D. SELECT WITH SHIPPER_INFO AS (SELECT SHIP_ID FROM SHIPS) SELECT PORT_ID, SHIPPER_INFO.SHIP_ID FROM PORTS, SHIPPER_INFO;

A, D

Review this SQL statement: SELECT SUBSTR('2009',1,2) || LTRIM('1124','1') FROM DUAL; What will be the result of the SQL statement? A. 2024 B. 221 C. 20124 D. A syntax error

A.

Review this SQL statement: SELECT TRUNC(ROUND(ABS(-1.7),2)) FROM DUAL; What will be the result of the SQL statement? A. 1 B. 2 C. −1 D. −2

A.

Your user account owns a table BACK_ORDERS, and you want to grant privileges on the table to a user account named CARUSO, which already has the system privileges CREATE SESSION and UNLIMITED TABLESPACE. Examine the following SQL statement: GRANT SELECT ON BACK_ORDERS TO CARUSO; Once this statement has been executed, which of the following statements will be true for user CARUSO? A. CARUSO will have SELECT privileges on BACK_ORDERS but not the ability to give other users SELECT privileges on BACK_ORDERS. B. CARUSO will have SELECT privileges on BACK_ORDERS, as well as the ability to give other users SELECT privileges on BACK_ORDERS. C. CARUSO will have SELECT, INSERT, UPDATE, and DELETE privileges on BACK_ ORDERS but not the ability to give other users those same privileges on BACK_ORDERS. D. CARUSO will have SELECT and ALTER TABLE privileges on BACK_ORDERS but not the ability to give other users those same privileges on BACK_ORDERS.

A. GRANT SELECT ON table TO user gives the user the ability to SELECT on the table and nothing more.

You need to determine the day of the week for a particular date in the future. Which function will reveal this information? A. TO_CHAR B. DAY_OF_WEEK C. TO_DATE D. None of the above

A. Here's an example: SELECT TO_CHAR(SYSDATE,'Day') FROM DUAL;.

Which format mask returns the local currency symbol? A. L B. C C. $ D. None of the above

A. L is the local currency symbol format mask.

Which of the following is the system privilege that empowers the grantee to create an index in his or her own user account but not in the accounts of others? A. CREATE TABLE B. CREATE ANY TABLE C. CREATE INDEX D. CREATE ANY INDEX

A. The CREATE TABLE privilege also includes the ability to create an index. Remember that a CREATE TABLE statement may include the PRIMARY KEY or UNIQUE constraint, which—if created—will automatically cause the creation of an index to support each constraint.

You are logged in to user account FRED and have been tasked with granting privileges to the user account ETHEL. You execute the following SQL statements: GRANT CREATE ANY TABLE TO ETHEL WITH ADMIN OPTION; REVOKE CREATE ANY TABLE FROM ETHEL; Assuming both statements execute successfully, what is the result? A. ETHEL does not have the system privilege CREATE ANY TABLE or the right to grant the CREATE ANY TABLE system privilege to any other user. B. ETHEL has the system privilege CREATE ANY TABLE because the WITH ADMIN OPTION clause wasn't included in the REVOKE statement. C. ETHEL no longer has the system privilege CREATE ANY TABLE but still has the right to grant the CREATE ANY TABLE system privilege to any other user, since the WITH ADMIN OPTION clause was omitted from the REVOKE statement. However, ETHEL may not grant the CREATE ANY TABLE privilege to herself. D. ETHEL no longer has the system privilege CREATE ANY TABLE but still has the right to grant the CREATE ANY TABLE system privilege to any other user since the WITH ADMIN OPTION clause was omitted. Furthermore, ETHEL may grant the CREATE ANY TABLE privilege to herself because of the WITH ADMIN OPTION clause.

A. The WITH ADMIN OPTION clause is not allowed nor needed in the REVOKE statement.

Which of the following statements will grant the role OMBUDSMAN to user JOSHUA in such a way that JOSHUA may grant the role to another user? A. GRANT OMBUDSMAN TO JOSHUA WITH ADMIN OPTION; B. GRANT OMBUDSMAN TO JOSHUA WITH GRANT OPTION; C. GRANT OMBUDSMAN TO JOSHUA WITH ROLE OPTION; D. GRANT OMBUDSMAN TO JOSHUA CASCADE;

A. WITH ADMIN OPTION is what is used for roles.

A correlated subquery: A. May be used in a SELECT but not an UPDATE B. Cannot be executed as a standalone query C. Must use a table alias when referencing a column in the outer query D. All of the above

B

Assume a schema with only two tables: one named PRODUCTS and one named ENGINEERING. Review the following SQL statements: SELECT PRODUCT_ID FROM PRODUCTS; DROP TABLE SHIP_STAFF; INSERT INTO ENGINEERING (PROJECT_ID, MGR) VALUES (27, 21); COMMIT INSERT INTO ENGINEERING (PROJECT_ID, MGR) VALUES (400, 17); ROLLBACK; In this series of SQL statements, which line represents the first commit event? A. LINE 1 B. LINE 2 C. LINE 3 D. LINE 6

B

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 SQL STATEMENTS: CREATE TABLE AB_INVOICES (INVOICE_ID NUMBER, VENDOR_ID NUMBER); ALTER TABLE AB_INVOICES ADD PRIMARY KEY (INVOICE_ID); INSERT INTO AB_INVOICES VALUES (1,1); DELETE AB_INVOICES WHRE INVOICE_ID = 2; Which of the following best describes the results of attempting to execute the DELETE statement? A. The DELETE statement will fail because it is missing a column list between the word DELETE and the name of the table AB_INVOICES. B. The DELETE statement will execute, but no rows in the table will be removed. C. The DELETE statement will produce a syntax error because it is referencing a row that does not exist in the database. D. None of the above.

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 * FROM FURNISHING; CAT# ITEM_NAME ADDED SECTION 1 side table 23-DEC-09 LR 2 desk 12-SEP-09 BR 3 towel 10-OCT-09 BA SELECT * FROM STORE_INVENTORY; NUM AISLE PRODUCT LAST_ORDER 77 F02 jacket 2009-09-09 78 B11 towel 2009-11-11 79 SP01 lava lamp 2009-12-21 01 SELECT '--', SECTION 02 FROM FURNISHINGS 03 WHERE CAT# NOT IN (1,2) 04 UNION ALL 05 SELECT TO_CHAR(LAST_ORDER,'Month'), AISLE 06 FROM STORE_INVENTORY; How many rows will result from this query? A. 0 B. 4 C. 6 D. It will not execute because it will fail with 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

TRUNCATE TABLE: A. Cannot be used within a valid SQL statement B. Is a valid set of keywords to be used within a DDL statement C. Does not require the DROP_ANY_TABLE privilege D. Is a valid statement that will truncate a table called TABLE

B

The ORDER BY clause can be included in a SELECT with set operators if: A. It follows the first SELECT statement. B. It follows the final SELECT statement. C. It is used in each SELECT statement and its ORDER BY expressions match in data type. D. The ORDER BY clause cannot be used in a SELECT with set operators.

B

The set operators do NOT include which one of the following keywords? A. ALL B. SET C. MINUS D. UNION

B

Which subquery includes references to the parent query and thus cannot execute as a standalone query? (Choose the best answer.) A. A scalar subquery B. A correlated subquery C. A multiple-column subquery D. A referential subquery

B

Conversion functions cannot be used to: A. Format date values B. Convert columns to new data types C. Transform data D. Create user-defined data types

B and D. Conversion functions cannot change the data type of a column to something else. They cannot create user-defined data types.

Which of the following problems can be solved with a subquery? (Choose the two best answers.) A. You are tasked with determining the minimum sales for every division in a multinational corporation. B. You are tasked with determining which divisions in a corporation earned sales last year that were less than the average sales for all divisions in the prior year. C. You are tasked with creating a view. D. You are tasked with creating a sequence.

B, C

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 * FROM FURNISHING; CAT# ITEM_NAME ADDED SECTION 1 side table 23-DEC-09 LR 2 desk 12-SEP-09 BR 3 towel 10-OCT-09 BA SELECT * FROM STORE_INVENTORY; NUM AISLE PRODUCT LAST_ORDER 77 F02 jacket 2009-09-09 78 B11 towel 2009-11-11 79 SP01 lava lamp 2009-12-21 01 SELECT A.SUB_DATE, COUNT(*) 02 FROM ONLINE_SUBSCRIBERS A JOIN 03 (SELECT LAST_ORDER, PRODUCT FROM STORE_INVENTORY 04 UNION 05 SELECT ADDED, ITEM_NAME FROM FURNISHINGS) B 06 ON A.SUB_DATE = B.LAST_ORDER 07 GROUP BY A.SUB_DATE; Which of the following are true about this SQL statement? (Choose two.) A. The GROUP BY clause on line 7 is not allowed here. B. The B.LAST_ORDER reference at the end of line 6 refers to data included in the ADDED column referred to in line 5. C. The JOIN at the end of line 2 is not allowed in this context. D. The statement is syntactically correct and will execute successfully.

B, D

Which of the following comparison operators can be used with a multiple-row subquery? (Choose two.) A. = B. >= ALL C. LIKE D. IN

B, D

Which of the following statements are true? (Choose two.) A. A single-row subquery can also be a multiple-row subquery. B. A single-row subquery can also be a multiple-column subquery. C. A scalar subquery can also be a multiple-column subquery. D. A correlated subquery can also be a single-row subquery.

B, D

Conversion functions: A. Change a column's data type so that future data stored in the table will be preserved in the converted data type. B. Change a value's data type in an equation to tell SQL to treat the value as that specified data type. C. Are similar to ALTER TABLE ... MODIFY statements. D. Are not required because SQL performs automatic data type conversion where necessary.

B. A conversion function tells SQL to treat a specified value as a specified data type for any subsequent use within a particular expression.

Assume a database with three valid users: NEIL, BUZZ, and MICHAEL. Assume all users have the appropriate privileges they require to perform the tasks shown here. Assume NEIL owns a table called PROVISIONS. Examine the following code (assume all password references are valid): 01 CONNECT NEIL/neilPassword 02 GRANT SELECT ON PROVISIONS TO BUZZ, MICHAEL; 03 04 CONNECT BUZZ/buzzPassword 05 CREATE VIEW PROVISIONS AS SELECT * FROM NEIL.PROVISIONS; 06 GRANT SELECT ON PROVISIONS TO MICHAEL; 07 CREATE PUBLIC SYNONYM PROVISIONS FOR BUZZ.PROVISIONS; 08 09 CONNECT MICHAEL/michaelPassword 10 CREATE SYNONYM PROVISIONS FOR NEIL.PROVISIONS; 11 SELECT * FROM PROVISIONS; What object is identified in line 11 by the name PROVISIONS? A. The public synonym created in line 7 B. The synonym created in line 10 C. Nothing, because user NEIL did not include WITH GRANT OPTIONS in the GRANT SELECT ON PROVISIONS TO BUZZ statement D. Something else not listed above

B. From within the MICHAEL user account, SQL first searches the local namespace and then searches the database namespace. The local namespace contains the private synonym, and that will be found first, before SQL looks in the database namespace.

If you want to display a numeric value with dollar signs and commas, which of the following is the best approach to take? A. The TO_NUMBER function with a format model B. The TO_CHAR function with a format model C. A combination of string literals that contain commas and dollar signs, along with the CONCAT function D. The MONEY data type

B. The TO_CHAR function would work, along with a format model, such as TO_CHAR(rawNumber, '$999,999.99').

Which query returns an expression of the data type INTERVAL YEAR TO MONTHS representing an interval of 1 year and 3 months? A. SELECT TO_YMINTERVAL('01:03') FROM DUAL; B. SELECT TO_YMINTERVAL('01-03') FROM DUAL; C. SELECT TO_INTERVALYM('01:03') FROM DUAL; D. SELECT TO_INTERVALYM('01-03') FROM DUAL;

B. The TO_YMINTERVAL function is correct, with the single parameter of a string containing two numbers, separated by a dash, where the first represents years in the interval, and the second represents the number of months in the interval.

Which of the following data dictionary views contains information about grants on tables that have been made by other users to your user account, as well as grants on tables that have been made by your user account to other user accounts? A. USER_TAB_COLUMNS B. USER_TAB_PRIVS C. USER_TABLES D. ALL_TAB_PRIVS_RECD

B. USER_TAB_PRIVS is the correct answer.

Consider the following set of SQL statements: CREATE TABLE MAILING_LIST (FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(30)); INSERT INTO MAILING_LIST VALUES ('Smith', 'Mary'); What will be the result of the INSERT statement? A. It will fail because there is no column list in the insert statement B. it will fail because there is no primary key in the table C. it will execute and create a new row in the table D. It will fail because the last name and first name values are reversed

C

Next, review the following SQL code: 01 SELECT TO_CHAR(A.LAST_ORDER,'RRRR-MM-DD') 02 FROM STORE_INVENTORY A 03 ORDER BY 1 04 UNION 05 SELECT ADDED 06 FROM FURNISHINGS; What will result from an attempt to execute this SQL statement? A. It will fail with a syntax error because of the TO_CHAR conversion function on line 1. B. It will fail because of the table alias in lines 1 and 2, which cannot be used in this context. C. It will fail with a syntax error on line 3 because you cannot use an ORDER BY in this context. D. It will execute successfully.

C

Review the SQL statements that follow, and assume that there is no table called ADDRESSES already presents in the database: CREATE TABLE ADDRESSES (ID NUMBER, ZONE NUMBER, ZIP_CODE VARCHAR2(5)); INSERT INTO ADDRESSES (ID, ZONE, ZIP_CODE) VALUES (1, 1, '94065'); SAVEPOINT ZONE_CHANGE_01; UPDATE ADDRESSES SET ZONE = 2 WHERE ZIP_CODE = 94065; ROLLBACK; What will be the result of the execution of the SQL statement shown here? A. The ADDRESSES table will have one row with a value of 1 for ZONE. B. The ADDRESSES table will have one row with a value of 2 for ZONE. C. The ADDRESSES table will have no rows. D. None of the above.

C

Review the following SQL statements: CREATE TABLE INSTRUCTORS (INSTRUCTOR_ID NUMBER, EXEMPT VARCHAR2(5), VACATION NUMBER, PAY_RATE NUMBER); INSERT INTO INSTRUCTORS VALUES (1, 'YES', NULL, 25); INSERT INTO INSTRUCTORS VALUES (2, NULL, NULL, NULL); UPDATE INSTRUCTORS SET EXEMPT = 'YES' SET VACATION = 15 WHERE PAY_RATE < 50; What can be said of the statements listed here? A. ONE ROW WILL BE UPDATED B. TWO ROWS WILL BE UPDATED C. AT LEAST ONE OF THE STATEMENTS WILL NOT EXECUTE D. NONE OF THE ABOVE

C

Review the following statement: CREATE TABLE STUDENT LIST (STUDENT_ID NUMBER, NAME VARCHAR2(30), PHONE VARCHAR2(30)); INSERT INTO STUDENT_LIST VALUES (1, 'Joe Wookie', 5551212); The table will create successfully. What will result from the INSERT statement? A. the INSERT will fail because there is no list of columns after STUDENT_LIST B. the INSERT will fail because the literal value for the PHONE is numeric and PHONE is a character data type C. the INSERT will execute and the table will contain one row of data D. None of the above.

C

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

Which of the following is a true statement? A. If a SELECT includes a GROUP BY clause, then any subquery used within the SELECT must also have a GROUP BY clause. B. If a query returns multiple rows, it may not be used as a subquery for a SELECT statement that uses a GROUP BY clause. C. A SELECT statement with a GROUP BY may use a subquery to return a value to the outermost WHERE clause. D. The only form of subquery permitted with a GROUP BY clause is a correlated subquery.

C

Which of the following reserved words is not required in order to form a syntactically correct UPDATE statement? A. UPDATE B. SET C. WHERE D. None of the above.

C

Which of the following reserved words is required in a complete DELETE statement (Choose all that apply). A. FROM B. WHERE C. DELETE D. NONE OF THE ABOVE

C

Which of the following statements about set operators is true? Choose the best answer. A. If you add the reserved word ALL to the end of any set operator, it will change the behavior of the set operator by removing duplicate rows. B. Set operators can be used to combine INSERT statements. C. You can connect two SELECT statements with one set operator. D. The UNION set operator has precedence over the others.

C

You are tasked with cleaning up a database application. There are two tables in the database: ORDERS contains completed ORDERS, and ORDER_RETURNS contains duplicate information for all ORDERS that were later returned. Your goal is to find out whether any rows in ORDER_RETURNS exist that were never in the ORDERS table to begin with. Which of the following operators should you use? A. ALL B. SET C. MINUS D. UNION

C

Assume a table LAMPS that has no constraints. Which of the following is true about the UPDATE statement and the LAMPS table? (Choose all that apply) A. UPDATE can be used to add rows to LAMPS by setting values to all the columns. B. UPDATE can be used to remove a row from LAMPS by setting all of the row's columns to a value of NULL. C. For existing rows in LAMPS, UPDATE can add values to any column with a NULL value. D. For existing rows in LAMPS, UPDATE can remove values from any column by changing its value to NULL.

C, D

SELECT * FROM FURNISHING; CAT# ITEM_NAME ADDED SECTION 1 side table 23-DEC-09 LR 2 desk 12-SEP-09 BR 3 towel 10-OCT-09 BA SELECT * FROM STORE_INVENTORY; NUM AISLE PRODUCT LAST_ORDER 77 F02 jacket 2009-09-09 78 B11 towel 2009-11-11 79 SP01 lava lamp 2009-12-21 01 SELECT '--' "Order Date", SECTION 02 FROM FURNISHINGS 03 WHERE CAT# NOT IN (1,2) 04 UNION ALL 05 SELECT TO_CHAR(LAST_ORDER,'Month') "Last Order", AISLE 06 FROM STORE_INVENTORY; Which of the following are valid ORDER BY clauses for this query? (Choose two.) A. ORDER BY AISLE B. ORDER BY "Last Order" C. ORDER BY SECTION D. ORDER BY 1

C, D

Review this SQL statement: SELECT MONTHS_BETWEEN(LAST_DAY('15-JAN-12')+1,'01-APR-12')FROM DUAL; What will result from this query? A. > 2 (some number greater than 2) B. 2 C. -2 D. < −2 (some number less than negative 2)

C.

Which of the following is true of character functions? A. They always accept characters as parameters and nothing else. B. They always return a character value. C. They are generally used to process text data. D. They generally have the letters CHAR somewhere in the function name.

C.

Which of the following is true of functions? A. They never return a value. B. They often return a value. C. They always return a value. D. There is no consistent answer to whether they return a value or not.

C.

Which of the following is the system privilege that is required as a minimum to allow a user account to log in to the database? A. CREATE ANY LOGIN B. CREATE ANY SESSION C. CREATE SESSION D. CREATE TABLE

C. The CREATE SESSION system privilege is the minimum requirement.

Which of the following SQL statements will display the current time, in hours, minutes, and seconds, as determined by the operating system on which the database server resides? A. SELECT TO_CHAR(SYSDATE) FROM DUAL; B. SELECT TO_CHAR(SYSDATE, 'HR:MI:SE') FROM DUAL; C. SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL; D. SELECT TO_CHAR(SYSDATE, 'HH:MM:SS') FROM DUAL;

C. The correct format mask is 'HH:MI:SS'.

Examine the following two claims: [1] The DBA_TAB_PRIVS data dictionary view allows a user account to see object privileges it has granted to other user accounts. [2] The DBA_TAB_PRIVS data dictionary view allows a user account to see object privileges granted by other user accounts to itself. Which of these claims is true? A. Only 1 B. Only 2 C. Both 1 and 2 D. Neither 1 nor 2

C. The data dictionary view DBA_TAB_PRIVS allows a user to see privileges that have been granted to itself or by itself to others.

User HARDING owns a table TEAPOT. User HARDING then executes the following SQL statements to give access to the table to user ALBERT: CREATE PUBLIC SYNONYM TEAPOT FOR HARDING.TEAPOT; CREATE ROLE DOME; GRANT DOME TO ALBERT; GRANT SELECT ON TEAPOT TO DOME; Which of the following statements can user ALBERT now execute on the TEAPOT table? A. SELECT * FROM DOME.HARDING.TEAPOT; B. SELECT * FROM HARDING.DOME.TEAPOT; C. SELECT * FROM HARDING.TEAPOT; D. None of the above

C. The schema name prefix correctly identifies the table. In addition, since the public synonym TEAPOT references the table, then DESC TEAPOT would also have worked—but that was not one of the options listed.

Which of the following SQL statements will authorize the user account JESSE to create tables in each and every user account in the database? A. GRANT CREATE ALL TABLE TO JESSE; B. GRANT CREATE PUBLIC TABLE TO JESSE; C. GRANT CREATE ANY TABLE TO JESSE; D. GRANT CREATE TABLE TO JESSE WITH PUBLIC OPTION;

C. The system privilege CREATE ANY TABLE is the system privilege that you're looking for in this question. The keyword ANY is found in many system privileges to indicate that the user authorized with the system privilege may perform the task as though it were any user account in the database.

You have a table FURNISHINGS and are told to grant DELETE privileges on the table to user HEARST. Examine the following SQL statements: GRANT DELETE ON FURNISHINGS TO HEARST; CREATE ROLE MGR; GRANT DELETE ON FURNISHINGS TO MGR; GRANT MGR TO HEARST; Now you are told to change the privileges given to HEARST so that HEARST can no longer execute DELETE statements on the FURNISHINGS table. Which of the following will accomplish the goal? (Choose the best answer.) A. REVOKE DELETE ON FURNISHINGS FROM HEARST; B. REVOKE DELETE ON FURNISHINGS FROM MGR; C. REVOKE DELETE ON FURNISHINGS FROM HEARST, MGR; D. None of the above

C. This SQL statement accomplishes the goal in one statement.

Another name for an EXISTS query is: A. Demijoin B. Multiple-column subquery C. Cross-join D. Semijoin

D

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

CONSIDER THE FOLLOWING DATE IN A TABLE CALLED PARTS: (the values of the columns are listed in order below) PNO: 1, 2, 3 PART_TITLE: processor V1.0 , encasement X770 , board cpu xer A7 STATUS: VALID, PENDING, PENDING Which of the following SQL statement will remove the word VALID from row 1, resulting in one row with a status of NULL and two rows with a status of PENDING? A. DELETE FROM PARTS WHERE STATUS = 'VALID'; B. DELETE PARTS WHERE PNO = 1; C. DELETE FROM PARTS SET STATUS = NULL WHERE PNO = 1; D. NONE OF THE ABOVE

D

Consider the following set of SQL statements: CREATE TABLE INSTRUCTORS (INSTRUCTOR_ID NUMBER, NAME VARCHAR2(20), CONSTRAINT ID_PK PRIMARY KEY (INSTRUCTOR_ID), CONSTRAINT NAME_UN UNIQUE (NAME)); INSERT INTO INSTRUCTORS (INSTRUCTOR_ID, NAME) VALUES (1, 'Howard Jackson'); INSERT INTO INSTRUCTORS (INSTRUCTOR_ID, NAME) VALUES (2, 'Trish Mars'); The table will create successfully. What will be the result of two INSERT statement? A. Neither will execute. B. The first will execute, but the second will fail. C. The first will fail, but the second will execute D. both will execute successfully

D

Review the PORTS and SHIPS tables. Your team is tasked with the job of creating a list of the ships with the least capacity in each port. In other words, each ship has a home port. For each port that is a home port to ships, which of each port's ships has the least capacity? Your team produces the following query in answer to this task: 01 SELECT S1.SHIP_NAME, (SELECT PORT_NAME 02 FROM PORTS 03 WHERE PORT_ID = S1.HOME_PORT_ID) HOME_PORT 04 FROM SHIPS S1 05 WHERE S1.CAPACITY = (SELECT MIN(CAPACITY) 06 FROM SHIPS S2 07 WHERE S2.HOME_PORT_ID = S1.HOME_PORT_ID); Which of the following statements is true about this SQL statement? A. The statement will fail with a syntax error because of the subquery on lines 1 through 3. B. The statement will fail with an execution error because of the subquery on lines 1 through 3. C. The statement will execute but will return meaningless information. D. The statement will execute successfully as intended.

D

Review the following SQL statement: TRUNCATE personnel; Which of the following is true of the previous statement? (Choose all that apply) A. The statement will result in an implicit comment. B. The statement will remove all data from any INDEX objects associated with that table. C. The statement will not fire any DML triggers on the table. D. The statement will fail.

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

SELECT * FROM FURNISHING; CAT# ITEM_NAME ADDED SECTION 1 side table 23-DEC-09 LR 2 desk 12-SEP-09 BR 3 towel 10-OCT-09 BA SELECT * FROM STORE_INVENTORY; NUM AISLE PRODUCT LAST_ORDER 77 F02 jacket 2009-09-09 78 B11 towel 2009-11-11 79 SP01 lava lamp 2009-12-21 01 SELECT (SELECT PRODUCT FROM STORE_INVENTORY 02 INTERSECT 03 SELECT ITEM_NAME FROM FURNISHINGS) 04 FROM ONLINE_SUBSCRIBERS; What will happen when this SQL statement is executed? A. It will fail with a general syntax error. B. It will fail with an execution error. C. It will execute, but the INTERSECT will not work correctly. D. It will execute and repeat the value 'Towel' for each row of the ONLINE_SUBSCRIBERS table.

D

SELECT * FROM FURNISHING; CAT# ITEM_NAME ADDED SECTION 1 side table 23-DEC-09 LR 2 desk 12-SEP-09 BR 3 towel 10-OCT-09 BA SELECT * FROM STORE_INVENTORY; NUM AISLE PRODUCT LAST_ORDER 77 F02 jacket 2009-09-09 78 B11 towel 2009-11-11 79 SP01 lava lamp 2009-12-21 01 SELECT COUNT(*) 02 FROM ONLINE_SUBSCRIBERS 03 WHERE SUB_DATE IN 04 (SELECT LAST_ORDER FROM STORE_INVENTORY 05 UNION 06 SELECT ADDED FROM FURNISHINGS); What will happen when this SQL statement is executed? A. It will fail with a syntax error because you cannot use an aggregate function like COUNT(*) in line 1 in this context. B. It will fail with a syntax error starting at line 4. C. It will execute, but it will not perform as intended because the second SELECT statement within the subquery on line 6 will not execute; only the first SELECT in the subquery on line 4 will execute. D. It will execute successfully.

D

The CASCADE keyword, when used with TRUNCATE: A. is required if the table has any depended child tables B. will ensure that future attempts to insert rows to the table will be rejected if they satisfy the TRUNCATE table's WHERE clause. C. Can be used with the optional DEPENDENCY keyword D. None of the above.

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

Analytic functions are processed: A. As the first set of operations prior to the SELECT column list processing B. As the first set of operations before processing the WHERE clause C. As the last set of operations before processing the WHERE clause D. As the last set of operations before processing the ORDER BY clause

D.

You are tasked to create a SELECT statement to subtract five months from the hired date of each employee in the EMPLOYEES table. Which function will you use? A. LAST_DAY B. SUBTRACT_MONTHS C. LAG D. None of the above

D.

A role: A. Takes the place of privileges automatically so that any privilege granted to a role supersedes any grants that have already been granted directly to a user B. Cannot be given the same name as a table C. Can be granted to a user, who can be granted only one role at a time D. Can be created by a user only if that user has the CREATE ROLE system privilege

D. The CREATE ROLE privilege is required to create a role.

Consider the following query, its output, and a subsequent query: SQL> SELECT * FROM LINE_ITEMS; LINE_ITEM PRICE --------- ----- 100 4.12 210 184 7.07 SQL> SELECT NVL(PRICE,10) FROM LINE_ITEMS; What is true of the final query shown previously? A. It will return "no rows found" because there is no PRICE of 10. B. It will return only the row where LINE_ITEM is 210. C. It will return no rows because there is no PRICE of 10. D. It will return three rows, but it will not change the price for line items 100 and 184.

D. The SELECT will return all three rows, changing only the price for line items 210 to 10, and nothing else.

You are tasked to create a report that displays the hours and minutes of the current date in a report. Which of the following will satisfy this requirement? A. TO_DATE(SYSDATE, 'HH:MM') B. TO_DATE(SYSDATE, 'HH:MI') C. TO_CHAR(SYSDATE, 'HH:MM') D. TO_CHAR(SYSDATE, 'HH:MI')

D. The TO_CHAR function formats dates. The HH and MI format masks display hours and minutes, respectively.

The purpose of NULLIF is to: A. Return a NULL if a single column is NULL B. Return a NULL if a single expression is NULL C. Both of the above D. None of the above

D. The purpose of NULLIF is to take two parameters, both of which are expressions, and return a NULL value if the two expressions evaluate to NULL. One common use is to analyze two sets of data and look for discrepancies; the inclusion of NULLIF can omit those expressions that are identical in order to highlight the discrepancies.

Your user account owns an updatable view, BACKLOG, which is based on the table PROJECTS. You are tasked to give SELECT and UPDATE capabilities to another user account named MARINO. Currently, MARINO has no privileges on either the table or the view. You want for MARINO to have the ability to grant SELECT on the view to other users as well. Examine the following SQL code: GRANT SELECT ON BACKLOG TO MARINO WITH GRANT OPTION; GRANT UPDATE ON BACKLOG TO MARINO; Which of the following statements is true? A. The statements will fail, and MARINO will not be able to use the view. B. The statements will execute successfully, but MARINO will not be able to SELECT from the view because the PROJECTS table has not been granted to MARINO. C. The statements will execute successfully, and MARINO will be able to SELECT from the view but not UPDATE the view. D. The statements will execute successfully and perform as intended.

D. The statements are syntactically correct and will perform as intended.