DB CUMULATIVE

Ace your homework & exams now with Quizwiz!

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.

Which of the following is true of SQL? A. It is most commonly used language for interacting with a database. B. It is the only language you can use to create a database. C. It is the only language you can use to interact with a database. D. None of the above.

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 CONSTRAINT is assigned to which of the following? (Choose all that apply.) A. TABLE B. SYNONYM C. SEQUENCE D. INDEX

You are tasked with querying the data dictionary view that lists only those sequences to which you currently have privileges but don't necessarily own. To do this, you log in to your own user account and query the data dictionary view called: A. ALL_SEQUENCES B. DBA_SEQUENCES C. USER_SEQUENCES D. USER_PRIV_SEQUENCES

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;

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

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

If an ALTER TABLE . . . DROP COLUMN statement is executed against an underlying table upon which a view is based, the status of that view in the data dictionary changes to: A. COMPILE B. INVALID C. ALTERED D. FLAG

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

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

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')

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

Now you have changed the purpose of the PIER column in the MARINA table and want to remove the comment you just created in the previous question. Which of the following statements will remove the comment? A. COMMENT ON COLUMN MARINA.PIER DROP; B. COMMENT ON COLUMN MARINA.PIER IS NULL; C. COMMENT ON COLUMN MARINA.PIER SET UNUSED; D. COMMENT ON COLUMN MARINA.PIER IS '';

A table is which of the following? (Choose all that apply.) A. A schema object B. A nonschema object C. A role D. All of the above

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

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.

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.

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.

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

One place to get a master list of all the views that form the data dictionary is: A. DICTIONARY B. DATA_DICTIONARY C. CATALOG D. USER_CATALOG

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.

Review the following SQL statement: CREATE TABLE personnel (personnel_ID NUMBER(6), division_ID NUMBER(6), CONSTRAINT personnel_ID_PK PRIMARY KEY (personnel_ID), CONSTRAINT division_ID_PK PRIMARY KEY (division_ID)); Assume there is no table already called PERSONNEL in the database. What will be the result of an attempt to execute the preceding SQL statement? A. The statement will fail because you cannot create two primary key constraints on the table. B. The statement will successfully create the table and the first primary key but not the second. C. The statement will successfully create a single table and one composite primary key consisting of two columns. D. The statement will successfully create the table and two primary keys.

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.

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

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.

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.

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.

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.

The DESC command can be used to do which of the following? A. Show a table's columns and the data types of those columns B. Show a brief paragraph describing what the table does C. Show a table's name and who created it D. Show the data that is contained within a table

The term metadata means: A. Data about data B. Global data that is accessible throughout the database C. Data that is automatically updated and maintained by the database system D. Distributed data

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

When transforming an ERD into a relational database, you often use an entity to build a database's: A. Table B. Column C. Attribute D. Relationship

Which of the following data dictionary views does not have an OWNER column? A. USER_TABLES B. ALL_INDEXES C. DBA_CONS_COLUMNS D. All of the above

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

You need to get information about columns in a table you do not own, nor do you have privileges to it. Which view can you query to get this information? A. DBA_TAB_COLUMNS B. ALL_TAB_COLUMNS C. ALL_COLUMNS D. Can't be done

What can an INSERT statement do? (Choose two.) A. Add rows into more than one table B. Add data into more than one column in a table C. Delete rows by overwriting them D. Join tables together

A and B.

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.

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

Which of the following can be used to remove data from a table? (Choose two.) A. DELETE B. UPDATE C. MODIFY D. ALTER

A, B

What can DDL be used for? (Choose three.) A. Add comments to a database table B. Add columns to a database table C. Add data to a database table D. Add privileges for a user to a database table

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 you use to submit SQL statements for execution? (Choose all that apply) A. PHP B. Java C. SQL Developer D. SQL*Plus

A, B, C, D

You are tasked to work with a view. The view's underlying table has been altered. What information can the data dictionary provide at this point? (Choose all correct answers.) A. The status of the view so that you can determine whether the view requires recompilation B. The current state of the table C. The query that was used to create the view D. The names of columns in the underlying table

A, B, C, D

Which of the following options can be used with the reserved word CREATE to form the beginning of a complete SQL statement? (Choose three.) A. TABLE B. VIEW C. CONSTRAINT D. SEQUENCE

A, B, D

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, and 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.

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.

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

Which of the following are schema objects? A. SEQUENCE B. PASSWORD C. INDEX D. ROLE

A, C

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

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, and 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

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

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

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

Which of the following statements is false? A. It is possible to merge into two or more tables. B. It is possible to merge into a view. C. The USING clause can reference two or more tables. D. You cannot perform an update to a column that is referenced in the ON clause.

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.

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.

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

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.

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

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.

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

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.

The USER_CONSTRAINTS view in the data dictionary lists FOREIGN KEY constraints in the CONSTRAINT_TYPE column with which of the following single-letter abbreviations? A. K B. R C. F D. G

The data dictionary is owned by: A. PUBLIC B. SYS C. SYSTEM D. Each individual user

The difference between dropping a column from a table with DROP and setting a column to be UNUSED is: A. An UNUSED column can be recovered. B. The UNUSED column and its data are retained within the table's storage allocation and counts against the total limit on the number of columns the table is allowed to have. C. A column that is dropped with DROP no longer appears within the table's description as shown with the DESC or DESCRIBE statement, whereas a column that is set to UNUSED still appears in the table's structure as shown in the output of the DESC statement. D. Nothing

The following SQL statements create a table with a column named A, then add a row to that table, then query the table: CREATE TABLE NUMBER_TEST (A NUMBER(5,3)); INSERT INTO NUMBER_TEST (A) VALUES (3.1415); SELECT A FROM NUMBER TEST; What is the displayed output of the SELECT statement? A. 3.1415 B. 3.142 C. 3.141 D. None of the above.

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

The unique identifier of a row in a database table is a(n): A. ID B. Primary key C. Primary column D. Column

What is one of the purposes of DDL? (Choose the best answer.) A. Query data from a given table B. Issue privileges to users C. Remove existing data from a database table D. None of the above

Which of the following SQL statements creates a table that will reject attempts to INSERT a row with NULL values entered into the POSITION_ID column? A. CREATE TABLE POSITIONS (POSITION_ID NUMBER(3), CONSTRAINT POSITION_CON UNIQUE (POSITION_ID)); B. CREATE TABLE POSITIONS (POSITION_ID NUMBER(3), CONSTRAINT POSITION_CON PRIMARY KEY (POSITION_ID)); C. CREATE TABLE POSITIONS (POSITION_ID NUMBER(3), CONSTRAINT POSITION_CON REQUIRED (POSITION_ID)); D. None of the above.

Which of the following is NOT a capability of the SELECT statement? A. It can transform queried data and display the results B. It can remove data from a table C. It can join data from multiple tables D. It can aggregate database data

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

You are tasked with the job of adding a comment to the data dictionary to accompany the column PIER in the table MARINA. Which of the following will execute successfully? A. COMMENT ON COLUMN (MARINA.PIER) IS 'Number of piers'; B. COMMENT ON COLUMN MARINA.PIER IS 'Number of piers'; C. COMMENT ON COLUMN MARINA(PIER) IS 'Number of piers'; D. COMMENT ON TABLE COLUMN MARINA.PIER IS 'Number of piers';

You attempt to execute the following SQL statement: CREATE TABLE VENDORS (VENDOR_ID NUMBER, VENDOR_NAME VARCHAR2, CATEGORY CHAR); Which one of the following is true? A. The execution fails because there is no precision indicated for NUMBER. B. The execution fails because there is no precision indicated for VARCHAR2. C. The execution fails because there is no precision indicated for CHAR. D. The execution succeeds, and the table is created.

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.

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

Which of the following are valid CREATE TABLE statements? (Choose three.) A. CREATE TABLE $ORDERS (ID NUMBER, NAME VARCHAR2(30)); B. CREATE TABLE CUSTOMER_HISTORY (ID NUMBER, NAME VARCHAR2(30)); C. CREATE TABLE "Boat Inventory" (ID NUMBER, NAME VARCHAR2(30)); D. CREATE TABLE workSchedule (ID NUMBER, NAME VARCHAR2(30));

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

You can add your own comments to the data dictionary with the COMMENT statement using which of the following? (Choose two.) A. INDEX B. COLUMN C. SEQUENCE D. TABLE

B, D

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

Consider the following statement: 01 SELECT NVL(SHIP_NAME,'None'), 02 CASE CAPACITY WHERE 234 THEN 'OK' 03 WHERE 999 THEN 'OK' 04 END 05 FROM SHIPS; Which of the following statements is true of the previous SELECT statement? A. The statement will fail with a compilation error because there is no column alias on the NVL expression (line 1). B. The statement will fail because of syntax errors on lines 2 and 3. C. The statement will fail because of the keyword END on the fourth line. D. The statement will execute successfully.

Consider the following table listing from the table ALARM_HISTORY: TRACKING_DATE INCIDENTS ------------- --------- 17-OCT-2018 12 18-OCT-2018 3 19-OCT-2018 20-OCT-2018 21-OCT-2018 4 You are tasked to calculate the average number of alarm incidents per day in ALARM_ HISTORY. You know the following query is syntactically correct: SELECT AVG(INCIDENTS) FROM ALARM_HISTORY; However, you are aware that the value for INCIDENTS might be NULL, and you want the AVG returned to be calculated across every day in ALARM_HISTORY, not just the non-NULL days. Which of the following queries will achieve this goal? A. SELECT AVG(NVL(INCIDENTS)) FROM ALARM_HISTORY; B. SELECT AVG(NVL(INCIDENTS,0)) FROM ALARM_HISTORY; C. SELECT NVL(AVG(INCIDENTS)) FROM ALARM_HISTORY; D. SELECT NVL(AVG(INCIDENTS,0)) FROM ALARM_HISTORY;

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.

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

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

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;

[GO TO PG 521 FOR TABLE AND PG 524 FOR CODE] What will this SQL statement do? 01 MERGE INTO SHIP_INVENTORY A 02 USING PORT_INVENTORY B 03 ON (A.NUM = B.NUM) 04 WHEN NOT MATCHED THEN INSERT 05 (A.NUM, A.AISLE, A.PRODUCT, A.LAST_ORDER) 06 VALUES 07 (B.NUM, B.AISLE, B.PRODUCT, B.LAST_ORDER) 08 WHERE TO_CHAR(A.LAST_ORDER,'RRRR') = '2019'; A. It will fail with a syntax error because you must have an ELSE clause. B. It will fail with a syntax error because you cannot reference the target table (SHIP_INVENTORY) in the WHERE clause in line 8. C. It will add rows from PORT_INVENTORY to SHIP_INVENTORY that do not already exist in SHIP_INVENTORY, limited to LAST_ORDER values from the year 2019. D. It will add rows from PORT_INVENTORY to SHIP_INVENTORY that do not already exist in SHIP_INVENTORY, regardless of the value for LAST_ORDER.

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.

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.

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

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.

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.

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

When you're looking for a particular bit of data and you're not sure where in the data dictionary it might be, a good starting point is: (Choose the best answer.) A. SELECT * FROM V$DATABASE; B. SELECT * FROM GV_$START_HERE; C. SELECT * FROM DICTIONARY; D. SELECT * FROM V$RESERVED_WORDS;

Which among the following is considered an acceptable query with V$DATAFILE? A. A join with two other objects in the data dictionary B. A complex GROUP BY with multiple levels of aggregation C. A query that displays rows from the table with no joins D. All of the above

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.

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.

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

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.

Which one of the following is a DML statement? A. ADD B. ALTER C. UPDATE D. MODIFY

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

You have a single database, with only one schema. The following four objects exist in the database: - A TABLE named PRODUCT_CATALOG - A TABLE named ADS - A USER named PRODUCT_CATALOG - A VIEW named CONFERENCE_SCHEDULE How many of the four objects are owned by the schema? A. 0 B. 2 C. 3 D. 4

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

A multitable INSERT statement: A. Can accomplish tasks that cannot otherwise be done in any combination of SQL statements B. Will create any tables in which it attempts to INSERT but that do not yet exist C. Can use conditional logic D. Is capable of inserting rows into nonupdatable views

Examine the SQL syntax in question 8. Which of the following two alternatives for line 3 are syntactically correct? OPTION 1: ON (A.NUM = B.NUM AND A.AISLE = B.AISLE) OPTION 2: ON (A.LAST_ORDER < B.LAST_ORDER) A. Only option 1 B. Only option 2 C. Both option 1 and option 2 D. Neither option 1 nor option 2

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

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)

The MERGE statement includes a USING clause. Which of the following statements is not true of the USING clause? A. It can be used to specify a subquery. B. The data it identifies remains unchanged after the MERGE statement executes. C. The USING clause is optional. D. It can be used to specify an inline view.

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

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;

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;

Which of the following can be said of the CASE statement? A. It converts text to uppercase. B. It uses the keyword IF. C. It uses the keyword THEN. D. Its END keyword is optional.

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

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.

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.

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

[GO TO PG 521 & 522 FOR TABLES AND CODE] Regarding this SQL statement, which of the following statements is true? 01 INSERT ALL 02 WHEN (SUBSTR(PART_NAME,1,4) = 'MED-') THEN 03 INTO STORE_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER) 04 VALUES (SPARE_ID, 'Back', PART_NAME, SYSDATE) 05 INTO SHIP_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER) 06 VALUES (SPARE_ID, 'Back', PART_NAME, SYSDATE) 07 WHEN (SUBSTR(PART_NAME,1,4) = 'ARR-') THEN 08 INTO PORT_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER) 09 VALUES (SPARE_ID, 'Back', PART_NAME, SYSDATE) 10 SELECT SPARE_ID, PART_NO, PART_NAME 11 FROM SPARES; A. The statement will fail because there is no ELSE clause. B. The statement will fail because it is missing a WHEN condition. C. The statement will add a row returned from the SPARES table to the SHIP_INVENTORY table only if the WHEN condition on line 2 evaluates to true. D. The statement will add every row returned from the SPARES table to the SHIP_INVENTORY table.

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.

[GO TO PG 521 & 522 FOR TABLES AND CODE] Review the SQL statement in the preceding question (typed out below). If one of the INTO clauses executed on a table and resulted in a constraint violation on that table, what would result? 01 INSERT ALL 02 WHEN (SUBSTR(PART_NAME,1,4) = 'MED-') THEN 03 INTO STORE_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER) 04 VALUES (SPARE_ID, 'Back', PART_NAME, SYSDATE) 05 INTO SHIP_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER) 06 VALUES (SPARE_ID, 'Back', PART_NAME, SYSDATE) 07 WHEN (SUBSTR(PART_NAME,1,4) = 'ARR-') THEN 08 INTO PORT_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER) 09 VALUES (SPARE_ID, 'Back', PART_NAME, SYSDATE) 10 SELECT SPARE_ID, PART_NO, PART_NAME 11 FROM SPARES; A. The row would not be inserted, and the INSERT statement would skip to the next row returned by the subquery and perform another pass through the WHEN conditions. B. The row would not be inserted, and the INSERT statement would stop. No additional rows would be returned by the subquery or processed, but rows that have already been processed are unaffected. C. The row would not be inserted, the INSERT statement would stop, and all rows affected by the INSERT statement would be rolled back, as if the INSERT statement had never been executed. D. None of the above.

[GO TO PG 521 FOR TABLE AND PG 523 FOR CODE] 01 INSERT FIRST 02 WHEN (SUBSTR(PART_NAME,5,3) = 'OPS') THEN 03 INTO STORE_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER) 04 VALUES (SEQ_NUM.NEXTVAL, 'Back', PART_NAME, SYSDATE) 05 WHEN (SUBSTR(PART_NAME,1,4) = 'PAN-') THEN 06 INTO SHIP_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER) 07 VALUES (SEQ_SHIP_NUM.NEXTVAL, 'Back', PART_NAME, SYSDATE) 08 ELSE 09 INTO PORT_INVENTORY (NUM, AISLE, PRODUCT, LAST_ORDER) 10 VALUES (SEQ_PORT_NUM.NEXTVAL, 'Back', PART_NAME, SYSDATE) 11 SELECT SPARE_ID, PART_NO, PART_NAME 12 FROM SPARES 13 WHERE LENGTH(PART_NO) > 2; Which one of the following answers correctly identifies data that, if present in the SPARES table, will be inserted by this conditional INSERT statement into the table—or tables— identified by the answer? A. PART_NO = 123; PART_NAME = 'BAH-OPS,' in both STORE_INVENTORY and PORT_INVENTORY B. PART_NO = 401; PART_NAME = 'PAN-OPS,' in both SHIP_INVENTORY and PORT_INVENTORY C. PART_NO = 170; PART_NAME = 'TRA-OPS,' in STORE_INVENTORY D. PART_NO = 4; PART_NAME = 'PAN-OPS,' in both STORE_INVENTORY and SHIP_INVENTORY

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.

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

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

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.

Review the following SQL statement: CREATE TABLE shipping_Order (order_ID NUMBER, order_YEAR CHAR(2), customer_ID NUMBER, CONSTRAINT shipping_Order PRIMARY KEY (order_ID, order_Year)); Assume there is no table already called SHIPPING_ORDER in the database. What will be the result of an attempt to execute the preceding SQL statement? A. The statement will fail because the data type for ORDER_YEAR is CHAR, and CHAR data types aren't allowed in PRIMARY KEY constraint. B. The statement will fail because there is no precision for the ORDER_ID column's data type. C. The table will be created, but the primary key constraint will not be created because the name does not include the _PK suffix. D. The statement will succeed: the table will be created, and the primary key will also be created.

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.

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

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.

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.

The purpose of the CREATE DICTIONARY statement is to create a named object in the database: A. That lists names of user accounts that have external privileges B. That contains lookup reference material for queries C. That identifies that root directory of the Oracle server installation D. That points to a dictionary you choose somewhere within the Oracle server's file system.

What can a SELECT statement be used to query? (Choose the best answer.) A. Only one report B. Only one table C. One or more reports D. One or more tables

Which of the following actions will not cause the contents of the data dictionary to be changed in some way? A. Create a new table B. Modify the data type of an existing column C. Execute a valid COMMENT statement D. None of the above

Which of the following is true about ROLES? A. Roles are schema objects but only when created from within a user account. B. Roles are in the same namespace as CONSTRAINTS. C. Roles are in the same namespace as TABLES. D. Roles are in the same namespace as USERS.

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

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

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

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.

The DECODE expression always ends with: A. The keyword END B. A default expression to return if no other value matched the source expression C. Both of the above D. Neither of the above

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

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.

[GO TO PG 521 FOR TABLE AND PG 524 FOR CODE] Which of the following statements is true for this SQL statement? 01 INSERT 02 WHEN (PART_NO < 500) THEN 03 INTO STORE_INVENTORY (NUM, PRODUCT) 04 VALUES (SPARE_ID, PART_NAME) 05 INTO PORT_INVENTORY (NUM, PRODUCT) 06 VALUES (SPARE_ID, PART_NAME) 07 WHEN (PART_NO >= 500) THEN 08 INTO SHIP_INVENTORY (NUM, PRODUCT) 09 VALUES (SPARE_ID, PART_NAME) 10 SELECT SPARE_ID, PART_NO, PART_NAME 11 FROM SPARES; A. If the first WHEN condition in line 2 is true, the INTO clause in line 3 and line 4 will be executed, after which processing will skip to the next row returned by the subquery. B. If the first WHEN condition in line 2 is true, the WHEN condition in line 7 will not be evaluated. C. No matter which WHEN condition is true, the INTO clause in line 5 will be executed regardless. D. Regardless of whether the first WHEN condition is true, the second WHEN condition will be evaluated.

You are logged in to user FINANCE. It is currently the only schema in the entire database. The following exist in the database: - A VIEW named VENDORS - A CONSTRAINT named VENDORS - An INDEX named CUSTOM#ADDRESS You attempt to execute the following SQL statement: CREATE TABLE CUSTOM#ADDRESS (ID NUMBER, NAME VARCHAR2(30)); Which one of the following is true? A. The question is flawed because you cannot have an INDEX named CUSTOM#ADDRESS. B. The question is flawed because you cannot have a VIEW and a CONSTRAINT with identical names in the same schema. C. The SQL statement will fail to execute and result an error message because you cannot create a TABLE name with the # character. D. The SQL statement will fail to execute and result in error message because you cannot create a TABLE that has the same name as an INDEX in the same schema. E. The SQL statement will execute, and the TABLE will be created.

DB CUMULATIVE

Related study sets

Flag Education and Detail

Mastering Biology: Chapter 20 Biotechnology

Comm 398 Test 2

Pharmacology Assessment 4

Permit test practice

Life Insurance

BIO101 Chapter 9 Homework

Ch. 8 Configuring Ethernet Switching

SOC EXAM 1

Test 3: Sections 7.3-7.6 and 4.1

MRI, Milan, and Strategic

Oxygenation and Perfusion chapter 39

Dohrn Insurance Simulated Life Exam Flash Cards

Chapter 4

Personal Finance Chapter 9

Bio 1107 - Chapter 12

Beckwith ELA Idiom Worksheet Practice

EAPS 100 Exam 2

S66 Review Questions (Updated)

Chapter 10: Measuring a Nation's Income