INF503 All Week Quizzes

1. The exam is timed.
   A) True
2. What can DDL be used for? (Choose three.)
   A) Add privileges for a user to a database table
   Add columns to a database table
   Add comments to a database table
3. Which of the following can be used to remove data from a table? (Choose two.)
   A) DELETE
   UPDATE
4. What is one of the purposes of DDL? (Choose the best answer.)
   A) Issue privileges to users
5. When transforming an ERD into a relational database, you often use an entity to build a database’s:
   A) Table
6. Which of the following topics are not included in the SQL Fundamentals I exam but are addressed on the SQL Associate exam? (Choose all that apply.)
   A) FLASHBACK
   MERGE
   External tables
7. The 1Z0-071 exam (which is the subject of this book) has been officially validated by Oracle Corporation against which of the following versions of the Oracle database? (Choose all that apply.)
   A) 12c
   11g
8. If you focus on trying to achieve the minimum passing grade requirement for the exam, you can study more efficiently.
   A) False
9. Which of the following is not a capability of the SELECT statement?
   A) It can remove data from a table.
10. The unique identifier of a row in a database table is a(n):
    A) Primary key
11. Which of the following is true of SQL?
    A)It is the most commonly used language for interacting with a database.
12. What can a SELECT statement be used to query? (Choose the best answer.)
    A) One or more tables
13. The best exam guide you could possibly get for preparing to take and pass the 1Z0-071 certification exam, SQL Associate, is which of the following? (Choose all that apply.)
    A) This book
    don’t make me tell you again
    This here book
    The book you are holding right now
14. What can you use to submit SQL statements for execution? (Choose all that apply.)
    A) SQL*Plus
    PHP
    JAVA
    SQL Developer
15. Which one of the following is a DML statement?
    A) UPDATE
16. A table is which of the following?
    A) A schema object
17. 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));
    
    A) The statement will fail because you cannot create two primary key constraints on the table.
    
18. Review the following SQL statement:
    CREATE TABLE shipping_Order ( order_ID NUMBER, order_Year CHAR(2), customer_ID NUMBER, CONSTRAINT shipping_Order_pk 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 succeed: the table will be created, and the primary key will also be created.
    
    
    

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

20. The difference between dropping a column from a table with DROP and setting a column to be UNUSED is:
A) 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.

21. 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 CUSTOMER#ADDRESS

You attempt to execute the following SQL statement:

CREATE TABLE CUSTOMER#ADDRESS (ID NUMBER, NAME VARCHAR2(30));

Which one of the following is true?
A) The SQL statement will execute, and the TABLE will be created.

22. Which of the following is true about ROLES?
A) Roles are in the same namespace as USERS.

23. A CONSTRAINT is assigned to which of the following?

A) TABLE

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

25. Which of the following are schema objects? (Choose all that apply.)
A) SEQUENCE

INDEX

26. 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) SEQUENCE

TABLE

VIEW

27. 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) 3

28.Which of the following are valid CREATE TABLE statements? (Choose three.)

A)  CREATE TABLE workSchedule(ID NUMBER,
NAME VARCHAR2(30));

CREATE TABLE CUSTOMER_HISTORY
(ID NUMBER,
NAME VARCHAR2(30));

CREATE TABLE “Boat Inventory”
(ID NUMBER,
NAME VARCHAR2(30));

29. The purpose of the CREATE DIRECTORY statement is to create a named object in the database:
A) That points to a directory you choose somewhere within the Oracle server’s file system

30. 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 PRIMARY KEY (POSITION_ID));

31. 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 VARCHAR2.

32. Review the SQL statements that follow, and assume that there is no table called ADDRESSES already present 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;

A) The ADDRESSES table will have no rows.

33. Which of the following reserved words is/are optional in a complete DELETE statement? (Choose all that apply)

A) FROM
WHERE

34. 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) For existing rows in LAMPS, UPDATE can remove values from any column by changing its value to NULL.

For existing rows in LAMPS, UPDATE can add values to any column with a NULL value.

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

36. 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 2

37. Review the following SQL statement:
TRUNCATE personnel;
Which of the following is true of the previous statement?

A) The statement will fail.

38. 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) At least one of the statements will not execute.

39. 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 WHERE INVOICE_ID = 2;

Which of the following best describes the results of attempting to execute the DELETE statement?

A) The DELETE statement will execute, but no rows in the table will be removed.

40. The CASCADE keyword, when used with TRUNCATE:

A) Is required if the table has any dependent child tables

41. onsider 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 execute and create a new row in the table.

42) 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 execute—the table will contain one row of data.

43) Consider the following data in a table called PARTS:

PNO PART_TITLE STATUS --- ---------------- ------- 1 PROCESSOR V1.0 VALID 2 ENCASEMENT X770 PENDING 3 BOARD CPU XER A7 PENDING

Which of the following SQL statements 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) None of these

44) 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 the two INSERT statements?
A) Both will execute successfully.

45) Which of the following reserved words is not required in order to form a syntactically correct UPDATE statement?
A) WHERE

46. RUNCATE TABLE:
A) Is a valid set of keywords to be used within a DDL statement

47. Review the following data listing for a table SHIPS:

Copy 

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

Copy 

SELECT SHIP_ID FROM SHIPS WHERE CAPACITY BETWEEN 2052 AND 3000 AND LENGTH IN ('100','855') AND SHIP_NAME LIKE 'Codd_%';

How many rows will the SELECT statement return?

A) 1

48) You can use a substitution variable to replace:
A) Both

49) To permanently delete a substitution variable named THE_NAME so that it can no longer be used, use:

A) UNDEFINE THE_NAME

50) Review this SELECT statement:

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 in ascending order and then by CAPACITY in descending order.

51) 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.

52) To list all the currently defined variables, use:
A) DEFINE

53) Which if the following is true of the ORDER BY clause? (Choose two.)
A) It can sort rows based on data that isn’t displayed as part of the SELECT statement.

It is optional.

54) Assume you have a table ITEMS that includes a column STATUS. Which of the following statements is syntactically correct? (Choose all that apply.)

SELECT * FROM ITEMS FETCH NEXT 20 % ROWS ONLY;(This is incorrect)

55) 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) There is nothing wrong with the syntax.

56) Review this SELECT statement:

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 fail to execute because the ORDER BY clause includes the word LIKE.

57) 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):

SELECT SHIP_ID FROM SHIPS WHERE SHIP_NAME IN ('Codd Elegance','Codd Victorious') OR (LIFEBOATS >= 80 OR LIFEBOATS <= 100) AND CAPACITY / LIFEBOATS > 25;

Which of the following statements is true about this SELECT statement?

A) The syntax is correct.

58) If you are using an ORDER BY to sort values in descending order, in which order will they appear?
A) f the data type is character, the value ‘Michael’ will appear first before the value ‘Jackson’.

59) 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.

60) 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) 1

61) 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):

SELECT ROOM_NUMBER FROM SHIP_CABINS WHERE (STYLE = NULL) OR (WINDOW = NULL);

How many rows will the SQL statement retrieve?
A) 0

62) The PERCENTILE_CONT function:
A) Can be used with PARTITION BY to specify groups of data

63) Consider the following:

SELECT MOD(5,3), REMAINDER(5,3) FROM DUAL;

Which of the following will be the result?

A) 2, -1

64) Analytic functions are processed:

A) As the last set of operations before processing the ORDER BY clause

65) Built-in SQL functions: (Choose three.)
A) Are written by SQL developers and also known as “user-defined” functions. (THIS IS INCORRECT)

66) The output of a function may be used: (Choose three.)
A) As an alternative to the keyword SET in an UPDATE statement. (THIS IS INCORRECT)

67) Consider the following SQL statement:

SELECT SOUNDEX('Donald') FROM DUAL;

Which of the following is most likely to be the output of this SELECT statement? (Choose the best answer.)
A) D543

68) Which of the following is true of character functions?
A)They are generally used to process text data.

69) Which of the following is true of functions?
A) They always return a value.

70) The LEAD function returns data from:
A) The row specified by the LEAD function’s offset

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

72) Review this SQL statement:

SELECT LASTNAME FROM CUSTOMERS WHERE LASTNAME = SOUNDEX('Franklin');

What is a possible result for the query?
A) None of these

73) 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) None of these

74) Review this SQL statement:

SELECT TRUNC(ROUND(ABS(-1.7),2)) FROM DUAL;

What will be the result of the SQL statement?
A) 1

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

76) The ORDER BY in an OVER clause:
A) Operates independently of the ORDER BY in the SELECT statement

77) 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_CHAR(SYSDATE, ‘HH:MI’)

78) 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, ‘HH:MI:SS’) FROM DUAL;

79) Which format mask returns the local currency symbol?
A) L

80)The DECODE expression always ends with:

A) Neither of these

81) The purpose of NULLIF is to:
A)None of these

82)Consider the following statement:

SELECT NVL(SHIP_NAME,'None'), CASE CAPACITY WHERE 234 THEN 'OK' WHERE 999 THEN 'OK' END FROM SHIPS;

Which of the following statements is true of the previous SELECT statement?
A) The statement will fail because of syntax errors on lines 2 and 3.

83) Conversion functions cannot be used to:
A) Convert columns to new data types
Create user-defined data types

84) Which of the following can be said of the CASE statement?
A) It uses the keyword THEN.

85) 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 three rows, but it will not change the price for line items 100 and 184.

86) 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;

87)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.
The presence of an explicit data type conversion documents your intent in the code.

88)Conversion functions:
A) Change a value’s data type in an equation to tell SQL to treat the value as that specified data type.

89) 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_CHAR function with a format model

90) 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,0)) FROM ALARM_HISTORY;

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

92) Review the following illustration:

Figure A
Now review this SQL statement:

Copy 

SELECT CRUISE_ORDER_ID, COUNT(ORDER_DATE) FROM CRUISE_ORDERS;

What can be said of this statement?
A) It will fail to execute because it mixes scalar and aggregate data in the select list.

93) Which of the following is true about aggregate functions? (Choose two.)
A) Return one value for each group of rows specified in a SELECT statement.

Are also called group functions.

94)Which of the following aggregate functions ignores NULL values in its calculations? (Choose all that apply.)
A) Choose all that apply
SUM
AVG
MEDIAN
MAX

95) Review the following illustration:

Figure A
Your assignment: create a SELECT statement that queries the PROJECTS table to show the average project cost for each PURPOSE. You know there are only two values for PURPOSE in the table: ‘Upgrade’ and ‘Maintenance’. You want to restrict rows where DAYS is greater than 3. Which of the following SELECT statements will perform this task?
A) SELECT PURPOSE, AVG(PROJECT_COST)
FROM PROJECTS
WHERE DAYS > 3
GROUP BY PURPOSE;

96) Which of the following aggregate functions can be used on character data? (Choose two.)
A) MIN
COUNT

97) Review the following illustration:

Figure A
and then look at the SQL code that follows:

SELECT TO_CHAR(ORDER_DATE,'Q') "Quarter", COUNT(*) FROM CRUISE_ORDERS WHERE TO_CHAR(ORDER_DATE,'YYYY') = '2009' GROUP BY TO_CHAR(ORDER_DATE,'Q');

Recall that the ‘Q’ format model is for quarter, so TO_CHAR using a DATE data type with the ‘Q’ format mask is translating the date into the quarter in which it falls—1, 2, 3, or 4. Given that, which of the following statements is true of the SQL statement?
A) It will execute and show the number of orders in the CRUISE_ORDERS table for each quarter in the year 2009.

98) Review the following illustration:

Figure A
and then review the following SQL statement:

SELECT AVG(CRUISE_ORDER_ID), MIN(ORDER_DATE) FROM CRUISE_ORDERS;

What will result from an attempt to execute this SQL statement on the CRUISE_ORDERS table?
A) It will execute and perform as intended.

99) An aggregate function can be called from within: (Choose two.)
A) The ORDER BY clause of a SELECT statement
The select list of a SELECT statement

100) Review the following data listing from a table SCORES:

SCORE_ID TEST_SCORE -------- ---------- 1 95 2 3 85

Now consider the following query:

SELECT TO_CHAR(AVG(TEST_SCORE),'999,999.99') FROM SCORES;

What will be the result of this query?
A) 90.00.

101) Examine the following data listing of a table called PERMITS:

PERMIT_ID FILED_DATE VENDOR_ID --------- ---------- --------- 1 05-DEC-09 101 2 12-DEC-09 310903 3 14-DEC-09 101

Which one of the following aggregate functions could be used to determine how many permits have been filed by VENDOR_ID 101?
A) COUNT

102) Review the following illustration:

Figure A
and then look at the SQL code that follows:

SELECT COUNT(COUNT(PROJECT_COST)) FROM PROJECTS GROUP BY PURPOSE;

What will happen if you try to execute this query on the PROJECTS table?
A) It will succeed and display one row.

103) Which of the following statements is true about HAVING? (Choose two.)
A) Which of the following statements is true about HAVING? (Choose two.)
It can be used only in the SELECT statement

104) Review the following illustration:

Figure A
Your task is to define a SELECT statement that groups rows according to their value for PURPOSE and, for each purpose, adds up the values stored in DAYS. Which one of the following queries will perform this task?
A) SELECT SUM(DAYS), PURPOSE
FROM PROJECTS
GROUP BY PURPOSE;

105) Review the following illustration:

Figure A
Which of the following SQL statements will execute correctly?
A) SELECT RANK(100000) WITHIN GROUP (ORDER BY PROJECT_COST) FROM PROJECTS;

106) Review the following illustration:

Figure A
and review the SQL statement that follows:

SELECT SHIP_ID, MAX(DAYS) FROM PROJECTS GROUP BY SHIP_ID HAVING AVG(PROJECT_COST) < 500000;

Which of the following statements is true for this SQL statement?
A) It will include only those groups of rows for a given SHIP_ID with an average value of PROJECT_COST less than 500000.

107) Review the following illustration:

Figure A
Which of the following is a syntactically correct outer join query? (Choose two.)
A) SELECT VENDOR_NAME, INVOICE_DATE
FROM VENDORS RIGHT OUTER JOIN INVOICES
ON VENDORS.VENDOR_ID = INVOICES.VENDOR_ID;

SELECT VENDOR_NAME, INVOICE_DATE
FROM VENDORS LEFT JOIN INVOICES
ON VENDORS.VENDOR_ID = INVOICES.VENDOR_ID;

108) You have two tables. One table is called CUSTOMERS. Another is called PURCHASES, and it records a list of customer transactions. Your goal is to create a SELECT statement that will show all customers by last name in alphabetical order, along with any purchases they may have made in the past two weeks, as recorded in the PURCHASES table. It’s possible that many customers have made no purchases in the past two weeks, but you still want them included in the output. Both tables contain a column called CUSTOMER_ID. Which of the following will be true of the SELECT statement you’ll need to create? (Choose two.)
A) It will be an outer join.
It will be an equijoin.

109) Review the following illustration:

Figure A
Which of the following is a valid self-join statement? (Choose all that apply.)
A) SELECT P1.POSITION_ID, P1.MIN_SALARY, P1.MAX_SALARY
FROM POSITIONS P1 SELF JOIN POSITIONS P2
ON P1.REPORTS_TO = P2.POSITION_ID;

110)

Review the following illustration:

Figure A
and then review the following SQL statement:

SELECT VENDOR_ID, INVOICE_DATE, TOTAL_PRICE FROM VENDORS JOIN INVOICES USING (VENDOR_ID);

What kind of join is this? (Choose two.)
A) INNER
Equijoin

111) Review the following illustration:

Figure A

and then review the following SQL statement:

SELECT A.EMPLOYEE_ID, B.POSITION FROM PAY_HISTORY A JOIN POSITIONS B ON A.SALARY < B.MAX_SALARY AND A.SALARY > B.MIN_SALARY;

Which of the following statements accurately describe the SQL statement? (Choose two.)
A) It is an inner join.
It is a non-equijoin.

112) Review the INVOICES and VENDORS tables.

Figure A

Next review the following SQL statement:

SELECT VENDOR_ID, INVOICE_DATE, TOTAL_PRICE FROM VENDORS JOIN INVOICES USING (VENDOR_ID);

Which of the following statements is true for the SQL statement?
A) It will execute successfully.

113) How many tables can be joined in a query?
A) One, two, three, or more

114) Review this SQL statement:

SELECT V.VENDOR_ID, INV.INVOICE_DATE FROM VENDORS V INNER JOIN INVOICES INV ON V.VENDOR_ID = INV.VENDOR_ID;

Which one of the following keywords in this statement is optional?
A) INNER

115) Equijoins look for:
A) Exact data matches

116) Which of the following symbols is most likely to be used in a SELECT statement using a non-equijoin?
A) <=

117) The difference between an INNER and an OUTER join is:
A) The INNER join displays rows that match in all joined tables; the OUTER join shows data that doesn’t necessarily match.

118) A self-join is: (Choose two.)

A) A SELECT statement that joins a table to itself by connecting a column in the table to a different column in the same table

A SELECT statement that specifies one table twice in the FROM clause

119) Review the POSITIONS, EMPLOYEES, and PAY_HISTORY tables.

Figure A
Review the following SQL statement:

SELECT LAST_NAME, POSITION, SALARY FROM POSITIONS P JOIN EMPLOYEES E ON P.POSITION_ID = E.POSITION_ID JOIN PAY_HISTORY PH ON E.EMPLOYEE_ID = PH.EMPLOYEE_ID;

Which of the following is true for the SQL statement? (Choose two.)
A) It will execute successfully
It connects three tables.

120) A table alias: (Choose two.)
A) Can be used to clear up ambiguity in the query.
Exists only for the SQL statement that declared it.

121) An inner join queries from two tables (looking at values in columns and optionally using expressions that reference columns) and compares the resulting values in one set of rows with the resulting values in another set of rows, looking for:
A) Values that match

122) Which of the following can a correlated subquery be used in? (Choose three.)
A) The FROM clause of a DELETE statement

123) Which of the following is a true statement?
A) A SELECT statement with a GROUP BY may use a subquery to return a value to the outermost WHERE clause.

124) Which of the following can a subquery be used in? (Choose all that apply.)
A) A GRANT statement

125) An inline view is a form of a subquery.
A) True

126) Which of the following forms of subquery never returns more than one row?
A) Scalar

127) Review the following illustration and the SQL code:

Figure A

DELETE FROM PORTS P WHERE PORT_ID NOT EXISTS (SELECT HOME_PORT_ID FROM SHIPS 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.

128) Another name for an EXISTS query is:
A) Semijoin

129) Review the following illustration:

Figure A
Which of the following statements, when executed, will result in an error?
A) SELECT WITH SHIPPER_INFO AS
(SELECT SHIP_ID FROM SHIPS)
SELECT PORT_ID, SHIPPER_INFO.SHIP_ID
FROM PORTS, SHIPPER_INFO;
WITH (SELECT SHIP_ID FROM SHIPS)
SELECT PORT_ID
FROM PORTS;

130) When is a query considered a multirow subquery? (Choose the best answer.)
A) If it returns multiple rows at the time of execution

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

132) Which subquery includes references to the parent query and thus cannot execute as a standalone query? (Choose the best answer.)
A) A correlated subquery

133) 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.
The name of the subquery can be joined to other tables in the SELECT statement following the WITH clause.

134) Review this WORK_HISTORY table:

Figure A

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) <= ALL
(SELECT ABS(START_DATE – END_DATE)
FROM WORK_HISTORY
WHERE SHIP_ID = W1.SHIP_ID);

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

135) A correlated subquery:
A) Cannot be executed as a standalone query

136) Review the given PORTS and SHIPS tables:

Figure A
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:

SELECT S1.SHIP_NAME, (SELECT PORT_NAME FROM PORTS WHERE PORT_ID = S1.HOME_PORT_ID) HOME_PORT FROM SHIPS S1 WHERE S1.CAPACITY = (SELECT MIN(CAPACITY) FROM SHIPS S2 WHERE S2.HOME_PORT_ID = S1.HOME_PORT_ID);

Which of the following statements is true about this SQL statement?

A) The statement will execute successfully as intended.

137) Review the given PORTS and SHIPS tables and the SQL code:

Figure A

SELECT PORT_NAME FROM PORTS P WHERE PORT_ID IN (SELECT HOME_PORT_ID, SHIP_NAME FROM SHIPS 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.

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

139) Review the PORTS and SHIPS tables:

Figure A
Next, review the following SQL code:

SELECT P.COUNTRY, P.CAPACITY FROM PORTS P WHERE P.PORT_ID > (SELECT S.HOME_PORT_ID 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.

140) Which of the following problems can be solved with a subquery? (Choose the two best answers.)
A) 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.
You are tasked with creating a view.

141) Review the following illustration and SQL code:

Figure A

UPDATE PORTS P SET CAPACITY = CAPACITY + 1 WHERE EXISTS (SELECT * FROM SHIPS 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.

142) Review this code:

Copy 

DROP SEQUENCE PROJ_ID_SEQ#; CREATE SEQUENCE PROJ_ID_SEQ# START WITH 1 INCREMENT BY 2; SELECT PROJ_ID_SEQ#.CURRVAL FROM DUAL;

What will result from these SQL statements?
A) The SELECT statement will fail because you cannot reference the CURRVAL pseudocolumn of a sequence until after you have referenced NEXTVAL for the sequence in a session.

143) Review the following illustration:

Figure A
Now review the following SQL code:

CREATE OR REPLACE VIEW PROJECTS_ROLLUP AS SELECT SHIP_NAME, CAPACITY, COUNT(PROJECT_ID) NUM_PROJECTS, ROUND(SUM(DAYS)) TOTAL_DAYS FROM SHIPS A JOIN PROJECTS B ON A.SHIP_ID = B.SHIP_ID GROUP BY SHIP_NAME, CAPACITY;

What can be said of this code?
A) After the view is created, a valid SELECT statement will work on the PROJECTS_ROLLUP view, but an INSERT will not.

144) Review the following series of SQL statements:

CREATE TABLE SUPPLIES_01 ( SUPPLY_ID NUMBER(7), SUPPLIER VARCHAR2(30), ACCT_NO VARCHAR2(50)); CREATE INDEX IX_SU_01 ON SUPPLIES_01(ACCT_NO); DROP TABLE SUPPLIES_01; CREATE TABLE SUPPLIES_02 ( SUPPLY_ID NUMBER(7), SUPPLIER VARCHAR2(30), ACCT_NO VARCHAR2(50)); CREATE INDEX IX_SU_02 ON SUPPLIES_02(ACCT_NO,SUPPLIER);

Assuming there are no objects already in existence named SUPPLIES_01 or SUPPLIES_02 prior to the execution of the preceding statements, what database objects will result from these statements?
A) A table called SUPPLIES_02 and an index called IX_SU_02

145) The database object that stores lookup information to speed up querying in tables is:
A) INDEX

146) An invisible index is an index on one or more columns in a table:
A) And is updated for any DELETE statements performed on the table

147) Which of the following keywords cannot be used with the CREATE SEQUENCE statement?
A) JOIN

148) Choose the best answer from the choices below. An index:

A) May improve the performance of an UPDATE statement that uses a WHERE clause, if the WHERE clause performs an equality comparison on an indexed column in a table

149) A SEQUENCE is
A) None of these.

150) Review the following illustration:

Figure A
Now review the following SQL code:

CREATE OR REPLACE VIEW SHIP_CAP_PROJ AS SELECT SHIP_ID, TO_CHAR(CAPACITY,'999,999'), PROJECT_COST FROM SHIPS JOIN PROJECTS USING (SHIP_ID) WHERE (PROJECT_COST * 2) < 100000;

What will result from an attempt to execute this SQL code?
A) The statement will fail because of an error in line 3.

151) Review the following SQL code:

CREATE TABLE PO_BOXES (PO_BOX_ID NUMBER(3), PO_BOX_NUMBER VARCHAR2(10)) ENABLE ROW MOVEMENT; INSERT INTO PO_BOXES VALUES (1, 'A100'); INSERT INTO PO_BOXES VALUES (2, 'B100'); COMMIT; DROP TABLE PO_BOXES; COMMIT; PURGE TABLE PO_BOXES; COMMIT;

What statement will recover the PO_BOXES table after these statements are executed?
A) None of these—the table cannot be recovered.

152) Review this code:

DROP TABLE SHIPS CASCADE CONSTRAINTS; DROP SEQUENCE PROJ_ID_SEQ#; CREATE TABLE SHIPS (SHIP_ID NUMBER PRIMARY KEY, LENGTH NUMBER); CREATE SEQUENCE PROJ_ID_SEQ# START WITH 1 INCREMENT BY 4; INSERT INTO SHIPS (SHIP_ID, LENGTH) VALUES (PROJ_ID_SEQ#.NEXTVAL, 'NOT A NUMBER'); INSERT INTO SHIPS (SHIP_ID, LENGTH) VALUES (PROJ_ID_SEQ#.NEXTVAL, 750); COMMIT;

Note that the first INSERT statement is attempting to enter a string literal of ‘NOT A NUMBER’ into a column declared with a numeric data type. Given that, what will be the result of these SQL statements?
A) One row added to the SHIPS table, with a SHIP_ID value of 5.

153) Review the following illustration:

Figure A
Now review the following SQL code:

CREATE OR REPLACE VIEW MAJOR_PROJECTS AS SELECT PROJECT_ID, SHIP_ID, PROJECT_NAME, PROJECT_COST FROM PROJECTS WHERE PROJECT_COST > 10000; INSERT INTO MAJOR_PROJECTS (PROJECT_ID, SHIP_ID, PROJECT_NAME, PROJECT_COST) VALUES ((SELECT MAX(PROJECT_ID)+1 FROM PROJECTS), (SELECT MAX(SHIP_ID) FROM SHIPS), 'Small Project', 500);

What will result from an attempt to execute these two SQL statements?
A) The CREATE and INSERT statements will successfully execute.

154) Which of the following SQL statements can always be executed on any VIEW object?
A) SELECT

155) All database data is stored in: