Quiz
1ZO-071
Create a free account to finish this quiz
Save your progress and see your final score.
Already have an account? Sign in
Questions in this quiz (87)
1Which two statements execute successfully?
- AA. SELECT TO_ DATE('2019-DEC-25 15:30' 'YYYY-MON-DD HH24:MI', 'NLS_ DATE_
LANGUAGE
=AMERICAN' ) FROM DUAL; - BB. SELECT TO_CHAR('2019-DEC-25 15:30" YY-MON-D HH24:M2', 'NLS_DATE LANGUAGE =
AMERICAN') FROM DUAL; - CC. SELECT TO _DATE (TO_ CHAR ('2019-DEC-25 03:30', 'YYYY-MON-DD HH12:MI')) FROM
DUAL; - DD. SELECT TO _CHAR(TO_DATE('2019-DEC-25 03:30','YYYY-MON-DD HH12:MI')) FROM
DUAL - EE. SELECT TO _ CHAR('2019-DEC-25 15:30'.'YYYY-MON-DD HH24:MI')
FROMDUAL
- AA. SELECT TO_ DATE('2019-DEC-25 15:30' 'YYYY-MON-DD HH24:MI', 'NLS_ DATE_
2Which two statements are true about the results of using the intersect operator in compound queries?
- AA. intersect ignores nulls.
- BB. Reversing the order of the intersected tables can sometimes affect the output.
- CC. Column names in each select in the compound query can be different.
- DD. intersect returns rows common to both sides of the compound query.
- EE. The number of columns in each select in the compound query can be different.
3You issue this command which succeeds: SQL> DROP TABLE products;
Which three statements are true?- AA. All existing views and synonyms that refer to the table are invalidated but retained.
- BB. Any uncommitted transaction in the session is committed.
- CC. Table data and the table structure are deleted.
- DD. All the table’s indexes if any exist, are invalidated but retained.
- EE. Table data is deleted but the table structure is retained.
4Which two statements are true regarding the COUNT function?
- AA. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
- BB. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
- CC. COUNT (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
- DD. COUNT (*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.
- EE. The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.
5Evaluate this ALTER TABLE statement: (Choose the best answer.) ALTER TABLE orders
SET UNUSED (order_date); Which statement is true?- AA. After executing the ALTER TABLE command, a new column called ORDER_DATE can be added to the ORDERS table.
- BB. The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.
- CC. ROLLBACK can be used to restore the ORDER_DATE column.
- DD. The DESCRIBE command would still display the ORDER_DATE column.
6Which task can be performed by using a single Data Manipulation Language (DML) statement?
- AA. Removing all data only from a single column on which a primary key constraint is defined.
- BB. Removing all data from a single column on which a unique constraint is defined.
- CC. Adding a column with a default value while inserting a row into a table.
- DD. Adding a column constraint while inserting a row into a table.
7Which two statements are true regarding constraints?
- AA. A foreign key column cannot contain null values.
- BB. A column with the UNIQUE constraint can contain null values.
- CC. A constraint is enforced only for INSERT operation on the table.
- DD. A constraint can be disabled even if the constraint column contains data.
- EE. All constraints can be defined at the column level and at the table level.
8Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME------------------
Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikilineni Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC". Which query gives the required output?- AA. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE SUBSTR (cust_name, INSTR (cust_name, ' ')+1)LIKE INITCAP ('MC%');
- BB. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) ='Mc';
- CC. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))LIKE 'Mc%';
- DD. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) =INITCAP
'MC%'
9View the exhibit for the structure of the STUDENT and FACULTY tables. STUDENT
NameNull?Type-------------------------------------------------- STUDENT_IDNOT NULLNUMBER(2) STUDENT_NAMEVARCHAR2(20) FACULTY_IDVARCHAR2(2)
LOCATION_IDNUMBER(2) FACULTY
NameNull?Type-------------------------------------------------- FACULTY_IDNOT NULLNUMBER(2) FACULTY_NAMEVARCHAR2(20) LOCATION_IDNUMBER(2)
You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements: Statement 1
SQL>SELECT faculty_name, COUNT(student_id) FROM student JOIN faculty
USING (faculty_id, location_id) GROUP BY faculty_name; Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty GROUP BY faculty_name;
Which statement is true regarding the outcome?- AA. Only statement 2 executes successfully and gives the required result.
- BB. Only statement 1 executes successfully and gives the required result.
- CC. Both statements 1 and 2 execute successfully and give different results.
- DD. Both statements 1 and 2 execute successfully and give the same required result
10Which statement is true regarding the USING clause in table joins? (Choose two.)
A. It can be used to join a maximum of three tables.
B. It can be used to access data from tables through equijoins as well as nonequijoins.
C. It can be used to join tables that have columns with the same name and compatible data types.
D. It can be used to restrict the number of columns used in a NATURAL join.- AA. It can be used to join a maximum of three tables.
- BB. It can be used to access data from tables through equijoins as well as nonequijoins.
- CC. It can be used to join tables that have columns with the same name and compatible data types.
- DD. It can be used to restrict the number of columns used in a NATURAL join.
11You issued the following command: SQL> DROP TABLE employees; Which three statements are true?
- AA. All uncommitted transactions are committed.
- BB. All indexes and constraints defined on the table being dropped are also dropped.
- CC. Sequences used in the employees table become invalid.
- DD. The space used by the employees table is reclaimed immediately.
- EE. The employees table can be recovered using the rollback command.
- FF. The employees table is moved to the recycle bin
12On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command:
$> sqlldr hr/hr@pdb table=employees
Which two statements are true regarding the command?- AA. It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database.
- BB. It fails because no SQL *Loader data file location is specified.
- CC. It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
- DD. It fails because no SQL *Loader control file location is specified.
13Which two statements are true regarding savepoints? (Choose two.)
- AA. Savepoints may be used to ROLLBACK.
- BB. Savepoints can be used for only DML statements.
- CC. Savepoints are effective only for COMMIT.
- DD. Savepoints are effective for both COMMIT and ROLLBACK.
- EE. Savepoints can be used for both DML and DDL statements.
14Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?
- AA. By default, EM express is available for a database after database creation.
- BB. You can use EM express to manage multiple databases running on the same server.
- CC. You can perform basic administrative tasks for pluggable databases by using the EM express interface.
- DD. You cannot start up or shut down a database Instance by using EM express.
- EE. You can create and configure pluggable databases by using EM express.
15Which two statement are true regarding table joins available in the Oracle Database server? (Choose two.)
- AA. You can use the ON clause to specify multiple conditions while joining tables.
- BB. You can explicitly provide the join condition with a NATURAL JOIN.
- CC. You can use the JOIN clause to join only two tables.
- DD. You can use the USING clause to join tables on more than one column.
16You need to display the date 11-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?
- AA. SELECT TO_CHAR (TO_DATE (’11-oct-2007’), ‘fmDdthsp “of” Month, Year’)FROM DUAL
- BB. SELECT TO_CHAR (‘11-oct-2007’, ‘fmDdspth “of” Month, Year’)FROM DUAL
- CC. SELECT TO_CHAR (TO_DATE (‘11-oct-2007’), ‘fmDdspth of month, year’)FROM DUAL
- DD. SELECT TO_DATE (TO_CHAR (’11-oct-2007’), ‘fmDdspth “of” Month, Year’))FROM DUAL
17Evaluate the following CRTEATE TABLE commands:
CREATE_TABLE orders
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE, cust_id NUMBER (4) );
CREATE TABLE ord_items (ord _no NUMBER (2),
item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) ); Why would the ORD_ITEMS table not get created?- AA. SYSDATE cannot be used with the CHECK constraint.
- BB. The BETWEEN clause cannot be used for the CHECK constraint.
- CC. The CHECK constraint cannot be placed on columns having the DATE data type.
- DD. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.
18Which three statements are true regarding the SQL WHERE and HAVING clauses?
- AA. The HAVING clause conditions can have aggregating functions.
- BB. The HAVING clause conditions can use aliases for the columns.
- CC. The WHERE and HAVING clauses cannot be used together in a SQL statement.
- DD. The WHERE clause is used to exclude rows before grouping data.
- EE. The HAVING clause is used to exclude one or more aggregated results after grouping data.
19Evaluate the following statement. INSERT ALL
WHEN order_total < 10000 THEN INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders
WHEN order_total > 200000 AND order_total < 20000 THEN INTO large_orders
SELECT order_id, order_total, customer_id FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?- AA. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
- BB. They are evaluated by the first WHEN claus
- CC. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
- DD. They are evaluated by the first WHEN claus
- EE. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
- FF. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.
20Which three statements are true reading subquenes?
- AA. A Main query can have many subqueries.
- BB. A subquery can have more than one main query.
- CC. The subquery and main query must retrieve date from the same table.
D. The subquery and main query can retrieve data from different tables. - DD. The subquery and main query can retrieve data from different tables.
- EE. Only one column or expression can be compared between the subquery and main query.
- FF. Multiple columns or expressions can be compared between the subquery and main query
21Which statements are true? (Choose all that apply.)
- AA. The data dictionary is created and maintained by the database administrator.
- BB. The data dictionary views consists of joins of dictionary base tables and user-defined tables.
- CC. The usernames of all the users including the database administrators are stored in the data dictionary.
- DD. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
- EE. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
- FF. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary
22Which two statements are true regarding constraints?
- AA. A table can have only one primary key and one foreign key.
- BB. A table can have only one primary key but multiple foreign keys.
- CC. Only the primary key can be defined at the column and table levels.
- DD. The foreign key and parent table primary key must have the same name.
- EE. Both primary key and foreign key constraints can be defined at both column and table levels.
23Which two statements best describe the benefits of using the WITH clause? (Choose two.)
- AA. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
- BB. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
- CC. It enables sessions to store a query block permanently in memory and use it to create complex queries.
- DD. It enables sessions to store the results of a query permanently.
24Which two statements are true regarding multiple-row subqueries? (Choose two.)
- AA. They can contain group functions.
- BB. They always contain a subquery within a subquery.
- CC. They use the < ALL operator to imply less than the maximum.
- DD. They can be used to retrieve multiple rows from a single table only.
- EE. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.
25Which three statements are true about multiple-row subqueries?
- AA. They can contain a subquery within a subquery.
- BB. They can return multiple columns as well as rows.
- CC. They cannot contain a subquery within a subquery.
- DD. They can return only one column but multiple rows.
- EE. They can contain group functions and GROUP BY and HAVING clauses.
- FF. They can contain group functions and the GROUP BY clause, but not the HAVING clause.
26You need to produce a report where each customer’s credit limit has been incremented by $1000. In the output, the customer’s last name should have the
heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in
uppercase.
Which statement would accomplish this requirement?- AA. SELECT cust_last_name AS “Name”, cust_credit_limit + 1000AS “New Credit Limit”FROM customers;
- BB. SELECT cust_last_name AS Name, cust_credit_limit + 1000AS New Credit LimitFROM customers;
- CC. SELECT cust_last_name AS Name, cust_credit_limit + 1000“New Credit Limit”FROM customers;
- DD. SELECT INITCAP (cust_last_name) “Name”, cust_credit_limit + 1000INITCAP (“NEW CREDIT LIMIT”)FROM customers;
27Which two statements are true regarding constraints? (Choose two.)
- AA. A constraint is enforced only for an INSERT operation on a table.
- BB. A foreign key cannot contain NULL values.
- CC. The column with a UNIQUE constraint can store NULLS.
- DD. You can have more than one column in a table as part of a primary key.
28Examine the commands used to create the DEPARTMENT_DETAILS and the COURSE-DETAILS tables: SQL> CREATE TABLE DEPARTMfiNT_D£TAILS
DEPARTMENT_ID NUMBER PRIMARY KEY , DEPARTMEHT_NAME VARCHAR2(50) ,
HOD VARCHAP2(50));
SQL> CREATE TABLE COURSE-DETAILS (COURSE ID NUMBER PRIMARY KEY , COURS_NAME VARCHAR2 (50) ,
DEPARTMEHT_ID NUMBER REFERENCES DEPARTMENT_DETAIL
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?- AA. SELECT d.departranc_id, c.cours«_id FROM cource_deatils c LEFT OUTER JOIN departmnt_details d ON (c.dapartmsnt_id=d.departtnent_id);
- BB. SELECT d.department_id,
- CC. course_id FROM dapartment_details d RIGHT OUTER JOIN course_dotails c ON (c.depattnient_id=d.department_id) ;
- DD. SELECT d.department i
- EE. ccours_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id);
- FF. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department id).- (DEPARTMENT_ID) ;
29Examine the structure of the MEMBERS table. NameNull?Type------------------------------------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
CITYVARCHAR2 (25)
STATENOT NULL VARCHAR2 (3)
Which query can be used to display the last names and city names only for members from the states MO and MI?- AA. SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
- BB. SELECT last_name, city FROM members WHERE state LIKE 'M%';
- CC. SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');
- DD. SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';
30Which two statements are true regarding constraints? (Choose two.)
- AA. All constraints can be defined at the column level and at the table level.
- BB. A constraint can be disabled even if the constraint column contains data.
- CC. A column with the UNIQUE constraint can contain NULLS.
- DD. A foreign key column cannot contain NULLS.
- EE. A constraint is enforced only for INSERT operations.
31Examine the command:
SQL> ALTER TABLE books_transactions
ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id) REFERENCES books (book_id) ON DELETE CASCADE; What does ON DELETE CASCADE imply?- AA. When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.
- BB. When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted butthe table structure is retained.
- CC. When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS
table are also deleted. - DD. When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.
32The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command:
GRANT ALL
ON orders, order_items TO PUBLIC;
What correction needs to be done to the above statement?- AA. PUBLIC should be replaced with specific usernames.
- BB. ALL should be replaced with a list of specific privileges.
- CC. WITH GRANT OPTION should be added to the statement.
- DD. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.
33Which three statements are true regarding single-row functions? (Choose three.)
- AA. The data type returned, can be different from the data type of the argument that is referenced.
- BB. They can return multiple values of more than one data type.
- CC. They can accept only one argument.
- DD. They can be nested up to only two levels.
- EE. They can be used in SELECT, WHERE, and ORDER BY clauses.
- FF. They can accept column names, expressions, variable names, or a user-supplied constants as arguments.
34Which two statements are true about Data Manipulation Language (DML) statements?
- AA. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
- BB. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
- CC. ADELETE FROM..... statement can remove rows based on only a single condition on a table.
- DD. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
- EE. ADELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
- FF. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table
35In which three situations does a transaction complete?
- AA. when a PL/SQL anonymous block is executed
- BB. when a DELETE statement is executed
- CC. when a ROLLBACK command is executed
- DD. when a data definition language (DDL) statement is executed
- EE. when a TRUNCATE statement is executed after the pending transaction
36View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. EMPLOYEES
NameNull?Type---------------------- ------------- EMPLOYEE_IDNOT NULLNUMBER(6) FIRST_NAMEVARCHAR2(20) LAST_NAMENOT NULLVARCHAR2(25) HIRE_DATENOT
NULLDATE JOB_IDNOT NULLVARCHAR2(10) SALARYNUMBER(10,2) COMMISSIONNUMBER(6,2) MANAGER_IDNUMBER(6)
DEPARTMENT_IDNUMBER(4) DEPARTMENTS
NameNull?Type---------------------- ------------
DEPARTMENT_IDNOT NULLNUMBER(4) DEPARTMENT_NAMENOT NULLVARCHAR2(30) MANAGER_IDNUMBER(6) LOCATION_IDNUMBER(4)
You want to update EMPLOYEES table as follows: You issue the following command:
SQL> UPDATE employees SET department_id = (SELECT department_id FROM departments
WHERE location_id = 2100), (salary, commission) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission) FROM employees, departments
WHERE departments.location_id IN(2900, 2700, 2100))
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700; What is outcome?- AA. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.
- BB. It generates an error because a subquery cannot have a join condition in a UPDATE statement.
- CC. It executes successfully and gives the desired update
- DD. It executes successfully but does not give the desired update
37View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
ORDERS_MASTER ORDER_ID ORDER_TOTAL
1
1000
2
2000
3
3000
4
MONTHLY_ORDERS ORDER_ID ORDER_TOTAL
2
2500
3
Evaluate the following MERGE statement: MERGE_INTO orders_master o
USING monthly_orders m ON (o.order_id = m.order_id) WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total DELETE WHERE (m.order_total IS NULL) WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?- AA. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.
- BB. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.
- CC. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.
- DD. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.
38Which two statements are true regarding working with dates? (Choose two.)
- AA. The RR date format automatically calculates the century from the SYSDATE function but allows the session user to enter the century.
- BB. The RR date format automatically calculates the century from the SYSDATE function and does not allow a session user to enter the century
- CC. The default internal storage of dates is in character format.
- DD. The default internal storage of dates is in numeric format.
39You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference.
Which method or feature should you use?- AA. Compare Period ADDM report.
- BB. AWR Compare Period report.
- CC. Active Session History (ASH) report.
- DD. Taking a new snapshot and comparing it with a preserved snapshot.
40Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)
- AA. WHERE and HAVING clauses cannot be used together in a SQL statement.
- BB. The HAVING clause conditions can have aggregate functions.
- CC. The HAVING clause conditions can use aliases for the columns.
- DD. The WHERE clause is used to exclude rows before the grouping of data.
- EE. The HAVING clause is used to exclude one or more aggregated results after grouping data.
41You issued this command:
CHOOSE THREE
SQL > DROP TABLE employees; Which three statements are true?- AA. Sequences used in the EMPLOYEES table become invalid.
- BB. If there is an uncommitted transaction in the session, it is committed.
- CC. All indexes and constraints defined on the table being dropped are also dropped.
- DD. The space used by the EMPLOYEES table is always reclaimed immediately.
- EE. The EMPLOYEES table can be recovered using the ROLLBACK command.
- FF. The EMPLOYEES table may be moved to the recycle bin.
42Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.)
- AA. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
- BB. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
- CC. The WHERE clause can be used to exclude rows after dividing them into groups.
- DD. The HAVING clause can be used with aggregate functions in subqueries.
- EE. The WHERE clause can be used to exclude rows before dividing them into groups.
43Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)
- AA. The nested query executes after the outer query returns the row.
- BB. The nested query executes first and then the outer query executes.
- CC. The outer query executes only once for the result returned by the inner query.
- DD. Each row returned by the outer query is evaluated for the results returned by the inner query.
44In which normal form is a table, if it has no multi-valued attributes and no partial dependencies?
- AA. second normal form
- BB. first normal form
- CC. third normal form
- DD. fourth normal form
45You must write a query that prompts users for column names and conditions every time it is executed. (Choose the best answer.)
The user must be prompted only once for the table name. Which statement achieves those objectives?- AA. SELECT &col1, '&col2'FROM &tableWHERE &&condition = '&cond';
- BB. SELECT &col1, &col2 FROM "&table"WHERE &condition =&cond;
- CC. SELECT &col1, &col2 FROM &&tableWHERE &condition = &cond;
- DD. SELECT &col1, &col2 FROM &&tableWHERE &condition = &&cond
46You issue this command which succeeds: SQL> DROP TABLE products;
Which three statements are true?- AA. All existing views and synonyms that refer to the table are invalidated but retained.
- BB. Any uncommitted transaction in the session is committed.
- CC. Table data and the table structure are deleted.
- DD. All the table’s indexes if any exist, are invalidated but retained.
- EE. Table data is deleted but the table structure is retained.
47Which three statements are true regarding subqueries?
- AA. Multiple columns or expressions can be compared between the main query and subquery.
- BB. Subqueries can contain ORDER BY but not the GROUP BY clause.
- CC. Main query and subquery can get data from different tables.
- DD. Subqueries can contain GROUP BY and ORDER BY clauses.
- EE. Main query and subquery must get data from the same tables.
- FF. Only one column or expression can be compared between the main query and subquery.
48Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED (order_date); Which statement is true?- AA. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
- BB. The ORDER_DATE column should be empty for the ALTER TABLE command to execute succsessfully.
- CC. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
- DD. The DESCRIBE command would still display the ORDER_DATE column.
49You must display details of all users whose username contains the string 'ch_'. (Choose the best answer.) Which query generates the required output?
- AA. SELECT * FROM users Where user_name LIKE '%ch_';
- BB. SELECT * FROM usersWhere user_name LIKE '%ch_%'ESCAPE'%';
- CC. SELECT * FROM users Where user_name LIKE 'ch\_%' ESCAPE '_';
- DD. SELECT * FROM users Where user_name LIKE '%ch\_%' ESCAPE '\';
50Which statement is true regarding the UNION operator?
- AA. By default, the output is not sorted.
- BB. Null values are not ignored during duplicate checking.
- CC. Names of all columns must be identical across all select statements.
- DD. The number of columns selected in all select statements need not be the same.
51Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME------------------
Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikilineni Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC". Which query gives the required output?- AA. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE SUBSTR (cust_name, INSTR (cust_name, ' ')+1)LIKE INITCAP ('MC%');
- BB. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) ='Mc';
- CC. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))LIKE 'Mc%';
- DD. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) =INITCAP
'MC%';
52Which two tasks can be performed by using Oracle SQL statements?
- AA. changing the password for an existing database user
- BB. connecting to a database instance
- CC. querying data from tables across databases
- DD. starting up a database instance
- EE. executing operating system (OS) commands in a session
53You issued the following command: SQL> DROP TABLE employees; Which three statements are true?
- AA. All uncommitted transactions are committed.
- BB. All indexes and constraints defined on the table being dropped are also dropped.
- CC. Sequences used in the employees table become invalid.
- DD. The space used by the employees table is reclaimed immediately.
- EE. The employees table can be recovered using the rollback command.
- FF. The employees table is moved to the recycle bin
54A subquery is called a single-row subquery when .
- AA. There is only one subquery in the outer query and the inner query returns one or more values
- BB. The inner query returns a single value to the outer query.
- CC. The inner query uses an aggregating function and returns one or more values.
- DD. The inner query returns one or more values and the outer query returns a single value
55Examine the structure of the MEMBERS table: NameNull?Type------------------------------------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
CITYVARCHAR2 (25)
STATEVARCHAR2 (3)
You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?- AA. SELECT * FROM MEMBERS WHERE state LIKE '%A_*;
- BB. SELECT * FROM MEMBERS WHERE state LIKE 'A_*;
- CC. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
- DD. SELECT * FROM MEMBERS WHERE state LIKE 'A%';
56Which three statements are true regarding the data types?
- AA. The minimum column width that can be specified for a VARCHAR2 data type column is one.
. - BB. Only one LONG column can be used per table
- CC. A TIMESTAMP data type column stores only time values with fractional seconds.
- DD. The BLOB data type column is used to store binary data in an operating system file.
- EE. The value for a CHAR data type column is blank-padded to the maximum defined column width
- AA. The minimum column width that can be specified for a VARCHAR2 data type column is one.
57Which three tasks can be performed using SQL functions built into Oracle Database?
- AA. displaying a date in a nondefault format
- BB. finding the number of characters in an expression
- CC. substituting a character string in a text expression with a specified string
- DD. combining more than two columns or expressions into a single column in the outpu
58Examine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario?- AA. The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities.
- BB. The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships
- CC. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
- DD. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
- EE. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and
PROJECTS entities.
59Which two statement are true regarding table joins available in the Oracle Database server? (Choose two.)
- AA. You can use the ON clause to specify multiple conditions while joining tables.
. - BB. You can explicitly provide the join condition with a NATURAL JOIN
- CC. You can use the JOIN clause to join only two tables.
- DD. You can use the USING clause to join tables on more than one column.
- AA. You can use the ON clause to specify multiple conditions while joining tables.
60Evaluate the following CRTEATE TABLE commands:
CREATE_TABLE orders
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE, cust_id NUMBER (4) );
CREATE TABLE ord_items (ord _no NUMBER (2),
item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) ); Why would the ORD_ITEMS table not get created?- AA. SYSDATE cannot be used with the CHECK constraint.
- BB. The BETWEEN clause cannot be used for the CHECK constraint.
- CC. The CHECK constraint cannot be placed on columns having the DATE data type.
- DD. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY
61You execute the following commands: SQL > DEFINE hiredate = '01-APR-2011'
SQL >SELECT employee_id, first_name, salary FROM employees
WHERE hire_date > '&hiredate' AND manager_id >&mgr_id;
For which substitution variables are you prompted for the input?- AA. none, because no input required
- BB. both the substitution variables ''hiredate' and 'mgr_id'
- CC. only hiredate'
- DD. only 'mgr_id'
62Which two statements are true regarding the SQL GROUP BY clause?
- AA. You can use a column alias in the GROUP BY clause.
- BB. Using the WHERE clause after the GROUP BY clause excludes rows after creating groups
- CC. The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.
- DD. Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.
- EE. If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY
clause.
63You execute the SQL statement: SQL> CREATE TABLE citizens
(citizen_id CHAR (10) PRIMARY KEY, last_name VARCHAR2 (50) NOT NULL, first_name VARCHAR2 (50),
address VARCHAR2 (100),
city VARCHAR2 (30) DEFAULT ‘SEATTLE’ NOT NULL,
CONSTRAINT cnames CHECK (first_name<>last_name) ); What is the outcome?- AA. It fails because the NOT NULL and DEFAULT options cannot be combined for the same column.
- BB. It succeeds and CITY can contain only ‘SEATTLE’ or null for all rows.
- CC. It fails because the condition for the CANAMES constraint is not valid.
- DD. It succeeds and an index is crated for CITIZEN_ID.
64When does a transaction complete? (Choose all that apply.)
- AA. When a PL/SQL anonymous block is executed
- BB. When a DELETE statement is executed
- CC. When a data definition language statement is executed
- DD. When a TRUNCATE statement is executed after the pending transaction
- EE. When a ROLLBACK command is executed
65Which statements are true? (Choose all that apply.)
- AA. The data dictionary is created and maintained by the database administrator.
- BB. The data dictionary views consists of joins of dictionary base tables and user-defined tables.
- CC. The usernames of all the users including the database administrators are stored in the data dictionary.
- DD. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
- EE. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
- FF. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary.
66Which statement is true regarding the INTERSECT operator?
- AA. The names of columns in all SELECT statements must be identical.
- BB. It ignores NULL values.
- CC. Reversing the order of the intersected tables alters the result.
- DD. The number of columns and data types must be identical for all SELECT statements in the query.
67Which task can be performed by using a single Data Manipulation Language (DML) statement?
- AA. adding a column constraint when inserting a row into a table
- BB. adding a column with a default value when inserting a row into a table
- CC. removing all data only from one single column on which a unique constraint is defined
- DD. removing all data only from one single column on which a primary key constraint is defined
68Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c?
- AA. ALTER TABLE SPLIT PARTITION
- BB. ALTER TABLE MERGE PARTITION
- CC. ALTER TABLE TRUNCATE PARTITION
- DD. ALTER TABLE ADD PARTITION
- EE. ALTER TABLE DROP PARTITION
- FF. ALTER TABLE MOVE PARTITION
69Which two statements best describe the benefits of using the WITH clause? (Choose two.)
- AA. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
- BB. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
- CC. It enables sessions to store a query block permanently in memory and use it to create complex queries..
- DD. It enables sessions to store the results of a query permanently
70You need to produce a report where each customer’s credit limit has been incremented by $1000. In the output, the customer’s last name should have the
heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in
uppercase.
Which statement would accomplish this requirement?- AA. SELECT cust_last_name AS “Name”, cust_credit_limit + 1000AS “New Credit Limit”FROM customers;
- BB. SELECT cust_last_name AS Name, cust_credit_limit + 1000AS New Credit LimitFROM customers;
- CC. SELECT cust_last_name AS Name, cust_credit_limit + 1000“New Credit Limit”FROM customers;
- DD. SELECT INITCAP (cust_last_name) “Name”, cust_credit_limit + 1000INITCAP (“NEW CREDIT LIMIT”)FROM customers;
71Which three statements are true regarding the usage of the WITH clause in complex correlated subqueries: (Choose three.)
- AA. It can be used only with the SELECT clause.
- BB. The WITH clause can hold more than one query.
- CC. If the query block name and the table name are the same, then the table name takes precedence.
- DD. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block
72mine the structure of the MEMBERS table. NameNull?Type------------------------------------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
CITYVARCHAR2 (25)
STATENOT NULL VARCHAR2 (3)
Which query can be used to display the last names and city names only for members from the states MO and MI?- AA. SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
%'; - BB. SELECT last_name, city FROM members WHERE state LIKE 'M
- CC. SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');
- DD. SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';
- AA. SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
73A non-correlated subquery can be defined as . (Choose the best answer.)
- AA. A set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query.
- BB. A set of sequential queries, all of which must return values from the same table.
- CC. A set of sequential queries, all of which must always return a single value.
- DD. A SELECT statement that can be embedded in a clause of another SELECT statement only.
74Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)
- AA. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always
reused for the current execution. - BB. During executing, the oracle server may read data from storage if the required data is not already in memory.
- CC. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
- DD. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for
execution.
- AA. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always
75Evaluate the following query:
SQL> SELECT TRUNC (ROUND (156.00, -1),-1) FROM DUAL;
What would be the outcome?- AA. 150
- BB. 200
- CC. 160
- DD. 16
- EE. 100
76Which three statements are true regarding single-row functions? (Choose three.)
- AA. The data type returned, can be different from the data type of the argument that is referenced.
- BB. They can return multiple values of more than one data type.
- CC. They can accept only one argument.
- DD. They can be nested up to only two levels.
- EE. They can be used in SELECT, WHERE, and ORDER BY clauses.
- FF. They can accept column names, expressions, variable names, or a user-supplied constants as arguments.
77In which three situations does a transaction complete?
- AA. when a PL/SQL anonymous block is executed
- BB. when a DELETE statement is executed
- CC. when a ROLLBACK command is executed
- DD. when a data definition language (DDL) statement is executed
- EE. when a TRUNCATE statement is executed after the pending transaction
78Which two statements are true about sequences crated in a single instance Oracle database?
- AA. The numbers generated by an explicitly defined sequence can only be used to insert data in one table.
- BB. DELETE <sequencename> would remove a sequence from the database.
- CC. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.
- DD. When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.
- EE. When the database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is
restarted.
79Evaluate the following statement. INSERT ALL
WHEN order_total < 10000 THEN INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders
WHEN order_total > 200000 THEN INTO large_orders
SELECT order_id, order_total, customer_id FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?- AA. Each row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent when clauses.
- BB. All rows are evaluated by all the three WHEN clauses.
- CC. Each row is evaluated by the first WHEN clause and if the condition is true, then the row would be evaluated by the subsequent when clauses.
- DD. The INSERT statement will return an error because the ELSE clause is missing.
80Which statement is true about an inner join specified in the WHERE clause of a query?
- AA. It must have primary-key and foreign-key constraints defined on the columns used in the join condition.
- BB. It requires the column names to be the same in all tables used for the join conditions.
- CC. It is applicable for equijoin and nonequijoin conditions.
- DD. It is applicable for only equijoin conditions.
81Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)
- AA. You can use column alias in the GROUP BY clause.
- BB. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
- CC. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
- DD. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
- EE. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the
GROUP BY cause.
82Which statement is true regarding the default behaviour of the ORDER by clause?
- AA. Numeric values are displayed in descending order if they have decimal positions.
- BB. Only columns that are specified in the SELECT list can be used in the ORDER by clause
- CC. In a character sort, the values are case-sensitive.
- DD. NULLs are not including in the sort operation
83Which statement correctly grants a system privilege?
- AA. GRANT CREATE VIEWON table1 TOuser1;
- BB. GRANT ALTER TABLETO PUBLIC;
- CC. GRANT CREATE TABLETO user1, user2;
- DD. GRANT CREATE SESSIONTO ALL;
84You want to display the date for the first Monday of the next month and issue the following command: SQL>SELECT
TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'),
'dd "is the first Monday for" fmmonth rrrr') FROM DUAL;
What is the outcome?- AA. In generates an error because rrrr should be replaced by rr in the format string.
- BB. It executes successfully but does not return the correct result.
- CC. It executes successfully and returns the correct result.
- DD. In generates an error because TO_CHAR should be replaced with TO_DATE.
- EE. In generates an error because fm and double quotation marks should not be used in the format string.
85Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:
SQL>CREATE TABLE DEPARTMENT_DETAILS (DEPARTMENT_ID NUMBER PRIMARY KEY, DEPARTMENT_NAMEVARCHAR2(50), HODVARCHAR2(50));
SQL>CREATE TABLE COURSE_DETAILS (COURSE_IDNUMBER PRIMARY KEY, COURSE_NAMEVARCHAR2(50), DEPARTMENT_IDVARCHAR2(50));
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?- AA. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=department_id);
- BB. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=department_id);
- CC. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=department_id);
- DD. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=department_id);
86Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose two.)
- AA. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
- BB. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
- CC. The WHERE clause can be used to exclude rows after dividing them into groups.
- DD. The HAVING clause can be used with aggregate functions in subqueries.
- EE. The WHERE clause can be used to exclude rows before dividing them into groups
87Examine the structure of the INVOICE table. NameNull?
Type-------------------------------------------------- INV_NONOT NULLNUMBER(3) INV_DATEDATE INV_AMTNUMBER(10,2)
Which two SQL statements would execute successfully?- AA. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')FROM invoice;
- BB. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')FROM invoice;
- CC. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;
- DD. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')FROM invoice;
Information
- Description
- Quiz on 1ZO-071, created by Joshua Nkongho on 08/02/2026.
- Author
- Joshua Nkongho
- Language
- English
- Views
- 0
- Tags
- Rating
- No ratings yet Rate this quiz