OCA 1Z0-071 12C - Parte 2

This is a timed quiz.

You have 2 hours to complete the 78 questions in this quiz.

Begin Quiz

Please wait - loading…

Question 1 of 78

Which statement is true about Data Manipulation Language (DML)?

Select one of the following:

DML automatically disables foreign ley constraints when modifying primary key values in the parent table
Each DML statement forms a transaction by default.
A transaction can consist of one or more DML statements.
DML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreigr constraint.

Explanation

Question 2 of 78

See the Exhibit and examine the structure of the PROMOTIONS table:

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the INTERNET category.

Which query would give you the required output?

Select one of the following:

SELECT promo_name, promo_begin_date
FROM promotions
WHERE promo_begin_date> ALL
(SELECT MAX (promo_begin_date)
FROM promotions)
AND promo_category= 'INTERNET';
SELECT promo_name, promo_begin_date
FROM promotions
WHERE promo_begin_date IN (SELECT promo_begin_date
FROM promotions
WHERE promo_category= 'INTERNET');
SELECT promo_name, promo_begin_date
FROM promotions
WHERE promo_begin_date > ALL
(SELECT promo_begin_date
FROM promotions
WHERE promo_category = 'INTERNET');
SELECT promo_name, promo_begin_date
FROM promotions
WHERE promo_begin_date> ANY (SELECT promo_begin_date
FROM promotions
WHERE promo_category= 'INTERNET');

Explanation

Question 3 of 78

Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)

Select one or more of the following:

When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.
DELETE <sequencename> would remove a sequence from the database.
The numbers generated by an explicitly defined sequence can only be used to insert data in one table.
CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.
When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

Explanation

Question 4 of 78

Evaluate the following CREATE TABLE command:
Which statement is true regarding the above SQL statement?

Select one of the following:

It would execute successfully and only ORD_ITM_IDX index would be created.
It would give an error because the USING INDEX clause cannot be used on a composite primary.
It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID PK would be created.
It would give an error because the USING INDEX is not permitted in the CREATE TABLE command.

Explanation

Question 5 of 78

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount
in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?

Select one of the following:

SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
SELECT DISTINCT cust_income_level ||‘ ‘|| cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
SELECT cust_income_level ||‘ ‘ ||cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;

Explanation

Question 6 of 78

Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement?

Select one or more of the following:

WHERE and HAVING clauses cannot be used together in a SQL statement.
The HAVING clause conditions can have aggregate functions.
The HAVING clause conditions can use aliases for the columns.
The WHERE clause is used to exclude rows before the grouping of data.
The HAVING clause is used to exclude one or more aggregated results after grouping data.

Explanation

Question 7 of 78

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

Select one of the following:

SELECT TO_CHAR(’11-oct-2007′, ‘fmDdspth "of" Month, Year’) FROM DUAL;
SELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdspth of month, year’) FROM DUAL;
SELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdthsp "of" Month, Year’) FROM DUAL;
SELECT TO_DATE(TO_CHAR(’11-oct-2007′,’fmDdspth ”of” Month, Year’)) FROM DUAL;

Explanation

Question 8 of 78

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS

SQL>CREATE TABLE DEPARTMENT_DETAILS
(DEPARTMENT_ID NUMBER PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(50),
HOD VARCHAR2(50));

SQL>CREATE TABLE COURSE_DETAILS
(COURSE_ID NUMBER PRIMARY KEY,
COURSE_NAME VARCHAR2(50),
DEPARTMENT_ID NUMBER REFERENCES DEPARTMENT_DETAILS(DEPARTMENT_ID));

You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses.
Which SQL statement must you use?

Select one of the following:

SELECT course_id, department_id
FROM department_details d
RIGHT OUTER JOIN course_details c
USING(department_id)
SELECT c.course_id, d.department_id
FROM course_details c
RIGHT OUTER JOIN department_details d
ON (c.department_id=d. department_id);
SELECT c.course_id, d.department_id
FROM course_details c
FULL OUTER JOIN department_details d
ON (c.department_id=d. department_id);
SELECT c.course_id, d.department_id
FROM course_details c
FULL OUTER JOIN department_details d
ON (c.department_id <> d. department_id);

Explanation

Question 9 of 78

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.
The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS table.

The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

Examine this command:

CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE) AS SELECT prod_id, cust_id, time_id FROM sales;

Which statement is true?

Select one of the following:

The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
The NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

Explanation

Question 10 of 78

View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.

Evaluate the following CREATE TABLE command:

CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) AS SELECT order_id.order_date,customer_id FROM orders;

Which statement is true regarding the above command?

Select one of the following:

The NEW_ORDERS table would not get created because the DEFAULT value cannot be specified in the column definition.
The NEW_ORDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
The NEW_ORDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
The NEW_ORDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

Explanation

Question 11 of 78

Evaluate 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 > 2000000 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?

Select one of the following:

They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
The INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true.

Explanation

Question 12 of 78

Which two statements are true regarding the SQL GROUP BY clause?

Select one or more of the following:

You can use a column alias in the GROUP BY clause.
Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.
The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.
Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.
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.

Explanation

Question 13 of 78

You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true?

Select one or more of the following:

All existing views and synonyms that refer to the table are invalidated but retained.
Any uncommitted transaction in the session is committed.
Table data and the table structure are deleted.
All the table-s indexes if any exist, are invalidated but retained.
Table data is deleted but the table structure is retained.

Explanation

Question 14 of 78

You 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) NOT NULL DEFAULT 'SEATTLE',
CONSTRAINT cnames CHECK (first_name<>last_name) );
What is the outcome?

Select one of the following:

It succeeds and CITY can contain only 'SEATTLE' or null for all rows.
It fails because the condition for the CANAMES constraint is not valid.
It fails because the DEFAULT options cannot be after the sentence NOT NULL.
It succeeds and an index is crated for CITIZEN_ID.

Explanation

Question 15 of 78

Evaluate the following CREATE 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 BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The above command fails when executed. What could be the reason?

Select one of the following:

SYSDATE cannot be used with the CHECK constraint.
The BETWEEN clause cannot be used for the CHECK constraint.
The CHECK constraint cannot be placed on columns having the DATE data type.
ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Explanation

Question 16 of 78

Examine the structure of the PROGRAMS table:

Which two SQL statements would execute successfully?

Select one or more of the following:

SELECT NVL (ADD_MONTHS (END_DATE,1), SYSDATE) FROM programs;
SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
SELECT NVL (MONTHS_BETWEEN (start_date, end_date), 'Ongoing') FROM programs;
SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), 'Ongoing') FROM programs

Explanation

Question 17 of 78

View the Exhibit and examine the structure of the CUSTOMERS table.

Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.

Customers with no credit limit should have 'Not Available' displayed.

Which SQL statement would produce the required result?

Select one of the following:

SELECT NVL (TO_CHAR(cust_credit_limit*.15), 'Not Available') 'NEW CREDIT' FROM customers
SELECT TO_CHAR(NVL(cust_credit_limit*.15), 'Not Available')) 'NEW CREDIT' FROM customers
SELECT NVL (cust_credit_limit*.15, 'Not Available') 'NEW CREDIT' FROM customers
SELECT NVL (cust_credit_limit, 'Not Available')*.15 'NEW CREDIT' FROM customers

Explanation

Question 18 of 78

Examine these SQL statements that are executed in the given order:

CREATE TABLE emp
(emp_no NUMBER (2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR 2 (15),
salary NUMBER (8, 2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp (emp_no));

ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;

What will be the status of the foreign key EMP_MGR_FK?

Select one of the following:

It will be enabled and immediate.
It will be enabled and deferred.
It will remain disabled and can be re-enabled manually.
It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

Explanation

Question 19 of 78

View the Exhibit and examine the structure in the EMPLOYEES tables.

Evaluate the following SQL statement:

SELECT employee_id, department_id FROM employees
WHERE department_id= 50
ORDER BY department_id
UNION
SELECT employee_id, department_id FROM employees
WHERE department_id=90
UNION
SELECT employee_id, department_id FROM employees
WHERE department_id=10;

What would be the outcome of the above SQL statement?

Select one of the following:

The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

Explanation

Question 20 of 78

View the Exhibit and examine, the description for the SALES and CHANNELS tables. (Choose the best answer.)

You issued this SQL statement:

INSERT INTO SALES VALUES (23, 2300, SYSDATE,
(SELECT CAHNNEL_ID FROM CHANNELS WHERE CHANNEL_DESC='DIRECT SALES'), 12, 500);
Which statement is true regarding the result?

Select one of the following:

The statement will fail because the sub-query in the VALUES clause is not enclosed within single quotation marks.
The statement will fail because a subquery cannot be used in a VALUES clause.
The statement will execute and a new row will be inserted in the SALES table.
The statement will fail because the VALUES clause is not required with the subquery.

Explanation

Question 21 of 78

View the Exhibit and examine the description of the ORDERS table.

Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

Select one or more of the following:

WHERE order_date > TO_DATE(‘JUL 10 2006′,’MON DD YYYY’)
WHERE TO_CHAR(order_date,’MON DD YYYY’) = ‘JAN 20 2003’
WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),’MON DD YYYY’)
WHERE order_date IN ( TO_DATE(‘Oct 21 2003′,’Mon DD YYYY’), TO_CHAR(‘NOV 21 2003′,’Mon DD YYYY’) )

Explanation

Question 22 of 78

Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

Select one or more of the following:

Finding the lowest value
Finding the quotient
Raising to a power
Subtraction
Addition

Explanation

Question 23 of 78

View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.

Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.

SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE JOB_ID = SA_MAN
————————————-
SELECT EMPLOYEE_ID
FROM JOB_HISTORY
WHERE JOB_ID = SA_MAN;

Choose correct SET operator which would cause the query to return the desired result.

Select one of the following:

UNION
MINUS
INTERSECT
UNION ALL

Explanation

Question 24 of 78

You must create a SALES table with these column specifications and data types: (Choose the best answer.)

SALESID: Number
STOREID: Number
ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified
PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified

Which statement would create the table?

Select one of the following:

CREATE TABLE Sales
(SALESID NUMBER (4),
STOREID NUMBER (4),
ITEMID NUMBER (4),
QTY NUMBER DEFAULT = 1,
SLSDATE DATE DEFAULT SYSDATE,
PAYMENT VARCHAR2(30) DEFAULT = "CASH");
CREATE TABLE Sales
(SALESID NUMBER (4),
STOREID NUMBER (4),
ITEMID NUMBER (4),
QTY NUMBER DEFAULT = 1,
SLSDATE DATE DEFAULT 'SYSDATE',
PAYMENT VARCHAR2(30) DEFAULT CASH);
CREATE TABLE Sales
(SALESID NUMBER (4),
STOREID NUMBER (4),
ITEMID NUMBER (4),
qty NUMBER DEFAULT = 1,
SLSDATE DATE DEFAULT SYSDATE,
PAYMENT VARCHAR2(30) DEFAULT = 'CASH');
Create Table sales
(salesid NUMBER(4),
Storeid NUMBER (4),
Itemid NUMBER (4),
QTY NUMBER DEFAULT 1,
Slsdate DATE DEFAULT SYSDATE,
payment VARCHAR2(30) DEFAULT CASH);

Explanation

Question 25 of 78

View the Exhibit and examine the details of the PRODUCT_INFORMATION table. (Choose two.)

Evaluate this SQL statement:

SELECT TO_CHAR (list_price, $9,999)
From product_information;

Which two statements are true regarding the output?

Select one or more of the following:

A row whose LIST_PRICE column contains value 11235.90 would be displayed as #######.
A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123.
A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124.
A row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123.

Explanation

Question 26 of 78

Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

Select one of the following:

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.
During executing, the oracle server may read data from storage if the required data is not already in memory.
During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Explanation

Question 27 of 78

Examine the structure of the ORDERS table:

You want to find the total value of all the orders for each year and issue this command:

SQL> SELECT TO_CHAR(order_date,rr), SUM(order_total) FROM orders GROUP BY TO_CHAR(order_date, yyyy);

Which statement is true regarding the result?

Select one of the following:

It executes successfully but does not give the correct output.
It executes successfully but gives the correct output.
It returns an error because the TO_CHAR function is not valid.
It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Explanation

Question 28 of 78

View the Exhibit and examine the structure of the ORDER_ITEMS table. (Choose the best answer.)

You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.

Which query would produce the desired result?

Select one of the following:

SELECT order_id FROM order_items GROUP BY order_id HAVING SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)) FROM order_items GROUP BY order_id);
SELECT order_id FROM order_items WHERE(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity) FROM order_items) GROUP BY order_id);
SELECT order_id FROM order_items WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id);
SELECT order_id FROM order_items WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP BY order_id)

Explanation

Question 29 of 78

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table.

Evaluate this SQL statement:

ALTER TABLE emp DROP COLUMN first_name;

Which two statements are true?

Select one or more of the following:

The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.
The FIRST_NAME column would be dropped provided at least one column remains in the table.
The FIRST_NAME column would be dropped provided it does not contain any data
The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement

Explanation

Question 30 of 78

View the exhibit and examine the structure and data in the invoice table

Which two SQL statements would execute successfully?

Select one or more of the following:

SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice
SELECT AVG(inv_date) FROM invoice
SELECT MAX(inv_date), MIN(cust_id) FROM invoice
SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice

Explanation

Question 31 of 78

Which two statements best describe the benefits of using the WITH clause?

Select one or more of the following:

It can improve the performance of a large query by storing the result of a query block having the WITH clause in the sessions temporary tablespace.
It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
It enables sessions to store a query block permanently in memory and use it to create complex queries
It enables sessions to store the results of a query permanently.

Explanation

Question 32 of 78

Which three statements are true regarding subqueries?

Select one or more of the following:

The ORDER BY Clause can be used in a subquery.
A subquery can be used in the FROM clause of a SELECT statement
If a subquery returns NULL, the main query may still return rows.
A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

Explanation

Question 33 of 78

Which two statements are true regarding single row functions?

Select one or more of the following:

MOD : returns the quotient of a division.
TRUNC : can be used with NUMBER and DATE values.
CONCAT : can be used to combine any number of values.
SYSDATE : returns the database server current date and time.
INSTR : can be used to find only the first occurrence of a character in a string.
TRIM : can be used to remove all the occurrences of a character from a string.

Explanation

Question 34 of 78

View the Exhibit and examine the structure of the ORDERS table.

You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.

Which query would give you the desired result?

Select one or more of the following:

SELECT order_id, order_date FROM orders
WHERE order_date > ANY
(SELECT order_date FROM orders WHERE customer_id = 101);
SELECT order_id, order_date FROM orders
WHERE order_date > ALL
(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
SELECT order_id, order_date FROM orders
WHERE order_date > ALL
(SELECT order_date FROM orders WHERE customer_id = 101)
SELECT order_id, order_date FROM orders
WHERE order_date > IN
(SELECT order_date FROM orders WHERE customer_id = 101);

Explanation

Question 35 of 78

Which task can be performed by using a single Data Manipulation Language (DML) statement?

Select one of the following:

Removing all data only from a single column on which a primary key constraint is defined.
Removing all data from a single column on which a unique constraint is defined.
Adding a column with a default value while inserting a row into a table.
Adding a column constraint while inserting a row into a table.

Explanation

Question 36 of 78

You must display details of all users whose username contains the string ch_. (Choose the best answer.)

Which query generates the required output?

Select one of the following:

SELECT * FROM users
Where user_name LIKE '%ch_';
SELECT * FROM users
Where user_name LIKE '%ch_%'ESCAPE'%';
SELECT * FROM users
Where user_name LIKE 'ch\_%' ESCAPE '_';
SELECT * FROM users
Where user_name LIKE '%ch\_%' ESCAPE '\';

Explanation

Question 37 of 78

View the Exhibit and examine the data in the PRODUCTS table.

You must display product names from the PRODUCTS table that belong to the Software/other category with minimum prices as either $2000 or $4000 and with no unit of measure.

You issue this query:

SQL > SELECT prod_name, prod_category, prod_min_price FROM products
Where prod_category LIKE '%Other%' AND (prod_min_price = '2000' OR prod_min_price = '4000') AND prod_unit_of_measure <> '';

Which statement is true?

Select one or more of the following:

It executes successfully but returns no result
It executes successfully and returns the required result.
It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
It generates an error because the condition specified for the PROD_CATEGORY column is not valid.

Explanation

Question 38 of 78

Examine the structure of the EMPLOYEES table.

You must display the maximum and minimum salaries of employees hired 1 year ago.

Which two statements would provide the correct output?

Select one or more of the following:

SELECT MIN(Salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365 GROUP BY MIN(salary), MAX(salary);
SELECT minsal, maxsal FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365) GROUP BY maxsal, minsal;
SELECT minsal, maxsal FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365 GROUP BY MIN(salary), MAX(salary);
SELECT MIN(Salary), MAX(salary) FROM (SELECT salary FROM employees WHERE hire_date < SYSDATE-365);

Explanation

Question 39 of 78

Which two statements are true regarding subqueries?

Select one or more of the following:

A subquery can appear on either side of a comparison operator.
Only two subqueries can be placed at one level.
A subquery can retrieve zero or more rows.
There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Explanation

Question 40 of 78

Which two statements are true regarding the execution of the correlated subqueries?

Select one or more of the following:

The nested query executes after the outer query returns the row.
The nested query executes first and then the outer query executes.
The outer query executes only once for the result returned by the inner query.
Each row returned by the outer query is evaluated for the results returned by the inner query.

Explanation

Question 41 of 78

Which two statement are true regarding table joins available in the Oracle Database server?

Select one or more of the following:

You can use the ON clause to specify multiple conditions while joining tables.
You can explicitly provide the join condition with a NATURAL JOIN.
You can use the JOIN clause to join only two tables.
You can use the USING clause to join tables on more than one column.

Explanation

Question 42 of 78

You issued this command:

SQL > DROP TABLE employees;

Which three statements are true?

Select one or more of the following:

Sequences used in the EMPLOYEES table become invalid.
If there is an uncommitted transaction in the session, it is committed.
All indexes and constraints defined on the table being dropped are also dropped.
The space used by the EMPLOYEES table is always reclaimed immediately.
The EMPLOYEES table can be recovered using the ROLLBACK command.
The EMPLOYEES table may be moved to the recycle bin.

Explanation

Question 43 of 78

View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.)
The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them.

The BASED_ON column indicates dependencies between tasks.

Some tasks do not depend on the completion of other tasks.

You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends.

Which query would give the required result?

Select one of the following:

SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p
JOIN proj_task_details d
ON (p.task_id = d.task_id);
SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p
FULL OUTER JOIN proj_task_details d
ON (p.based_on = d.task_id);
SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p
JOIN proj_task_details d
ON (p.based_on = d.task_id);
SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p
LEFT OUTER JOIN proj_task_details d
ON (p.based_on = d.task_id);

Explanation

Question 44 of 78

View the Exhibit and examine the structure of the SALES and PRODUCTS tables

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.

Examine this query which is missing a JOIN operator:

SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p ______________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;

Which two JOIN operations can be used to obtain the required output?

Select one or more of the following:

FULL OUTER JOIN
JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN

Explanation

Question 45 of 78

View the exhibit and examine the description of the EMPLOYEES table. You executed this SQL statement:

SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;

Which two statements are true regarding the result?

Select one or more of the following:

The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
The values in the all columns would be returned in descending order.
The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

Explanation

Question 46 of 78

Examine the structure of the SALES table.
Examine this statement:

SQL > CREATE TABLE sales1 (prod_id, cust_id, quantity_sold, price) AS SELECT product_id, customer_id, quantity_sold, price FROM sales WHERE 1 = 2;

Which two statements are true about the SALES1 table?

Select one or more of the following:

It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
It will not be created because of the invalid WHERE clause.
It is created with no rows.
It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.

Explanation

Question 47 of 78

Examine this SELECT statement and view the Exhibit to see its output:

SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status, FROM user_constraints WHERE table_name = 'ORDERS';

Which two statements are true about the output?

Select one or more of the following:

The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.
The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
In the second column, c indicates a check constraint.
The STATUS column indicates whether the table is currently in use.

Explanation

Question 48 of 78

Which two statements are true regarding constraints?

Select one or more of the following:

A constraint is enforced only for an INSERT operation on a table.
A foreign key cannot contain NULL values.
The column with a UNIQUE constraint can store NULLS
All constraints can be defined at the column level and at the table level
A constraint can be disabled even if the constraint column contains data

Explanation

Question 49 of 78

Which two statements are true regarding working with dates?

Select one or more of the following:

The RR date format automatically calculates the century from the SYSDATE function but allows the session user to enter the century.
The RR date format automatically calculates the century from the SYSDATE function and does not allow a session user to enter the century.
The default internal storage of dates is in character format.
The default internal storage of dates is in numeric format.

Explanation

Question 50 of 78

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables

You executed this UPDATE statement:

UPDATE ( SELECT order_date, order_total, customer_id FROM orders) Set order_date = '22-mar-2007' WHERE customer_id IN (SELECT customer_id FROM customers WHERE cust_last_name = 'Roberts' AND credit_limit = 600);

Which statement is true regarding the execution?

Select one or more of the following:

It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
It would not execute because two tables cannot be referenced in a single UPDATE statement.
It would execute and restrict modifications to the columns specified in the SELECT statement.
It would not execute because a SELECT statement cannot be used in place of a table name.

Explanation

Question 51 of 78

View the Exhibit and examine the structure of the PRODUCTS table

You must display the category with the maximum number of items.

You issue this query:

SQL > SELECT COUNT(), prod_category_id FROM products GROUP BY prod_category_id HAVING COUNT() = (SELECT MAX(COUNT(*)) FROM products);

What is the result?

Select one of the following:

It generates an error because = is not valid and should be replaced by the IN operator.
It executes successfully but does not give the correct output.
It executes successfully and gives the correct output.
It generate an error because the subquery does not have a GROUP BY clause.

Explanation

Question 52 of 78

Examine the structure of the MEMBERS table: (Choose the best answer.)

Examine the SQL statement:

SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC;

What would be the result execution?

Select one of the following:

It displays all cities in descending order, within which the last names are further sorted in descending order.
It fails because a column alias cannot be used in the ORDER BY clause.
It fails because a column number and a column alias cannot be used together in the ORDER BY clause
It displays all cities in ascending order, within which the last names are further sorted in descending order

Explanation

Question 53 of 78

View and Exhibit and examine the structure and data in the INVOICE table.

Which two statements are true regarding data type conversion in query expressions?

Select one or more of the following:

inv_date = '15-february-2008' :uses implicit conversion
inv_amt = '0255982' : requires explicit conversion
inv_date > '01-02-2008' : uses implicit conversion
CONCAT(inv_amt, inv_date) : requires explicit conversion
inv_no BETWEEN '101' AND '110' : uses implicit conversion

Explanation

Question 54 of 78

Examine the structure of the EMPLOYEES table.
You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.

Select one of the following:

SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000
UNION ALL
SELECT last_name, hire_date, salary FROM employees WHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)
INTERSECT
SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE- 180;
SELECT last_name, hire_date, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)
UNION ALL
(SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180
INTERSECT
SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000);
SELECT last_name, hire_date, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)
UNION
SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180
INTERSECT
SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000;
(SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000 UNION ALL SELECT last_name, hire_date, salary FROM employees WHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)) UNION SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180;

Explanation

Question 55 of 78

Examine the structure of the PROMOTIONS table:

Management requires a report of unique promotion costs in each promotion category.

Which query would satisfy this requirement?

Select one of the following:

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1
SELECT promo_category, DISTINCT promo_cost FROM promotions
SELECT DISTINCT promo_cost, promo_category FROM promotions
SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

Explanation

Question 56 of 78

You must create a table for a banking application. (Choose the best answer.)
One of the columns in the table has these requirements:

1: A column to store the duration of a short team loan

2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.

3: The maximum loan period is 30 days.

4: Interest must be calculated based on the number of days for which the loan remains unpaid.

Which data type would you use?

Select one of the following:

Date
Number
Timestamp
Interval day to second
Interval year to month

Explanation

Question 57 of 78

Examine the structure of the CUSTOMERS table:

CUSTNO is the PRIMARY KEY.

You must determine if any customers details have been entered more than once using a different CUSTNO, by listing all duplicate names.

Which two methods can you use to get the required result?

Select one or more of the following:

Subquery
Self-join
Full outer-join with self-join
Left outer-join with self-join
Right outer-join with self-join

Explanation

Question 58 of 78

Which two are the minimal requirements for a self-join?

Select one or more of the following:

Only equijoin conditions may be used in the query.
Outer joins must not be used in the query.
There must be a condition on which the self-join is performed.
No other condition except the self-join may be specified.
The table used for the self-join must have two different alias names in the query.

Explanation

Question 59 of 78

Examine the SQL statement used to create the TRANSACTION table

SQL > CREATE TABLE transaction
(trn_id char(2) primary key,
Start_date date DEFAULT SYSDATE,
End_date date NOT NULL);

The value A1 does not exist for trn_id in this table.

Which SQL statement successfully inserts a row into the table with the default value for START_DATE?

Select one of the following:

INSERT INTO transaction VALUES ('A1',DEFAULT, TO_DATE(DEFAULT+10))
INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))
INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')
INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1',, '10-DEC-2014')

Explanation

Question 60 of 78

Which three SQL statements would display the value 1890.55 as $1,890.55?

Select one or more of the following:

SELECT TO_CHAR (1890.55, $99G999D00) FROM DUAL
SELECT TO_CHAR (1890.55, $9,999V99) FROM DUAL
SELECT TO_CHAR (1890.55, $0G000D00) FROM DUAL
SELECT TO_CHAR (1890.55, $99,999D99) FROM DUAL
SELECT TO_CHAR (1890.55, $99G999D99) FROM DUAL

Explanation

Question 61 of 78

A subquery is called a single-row subquery when _______.

Select one of the following:

There is only one subquery in the outer query and the inner query returns one or more values
The inner query returns a single value to the outer query.
The inner query uses an aggregating function and returns one or more values.
The inner query returns one or more values and the outer query returns a single value.

Explanation

Question 62 of 78

You must write a query that prompts users for column names and conditions every time it is executed. The user must be prompted only once for the table name.

Which statement achieves those objectives?

Select one of the following:

SELECT &col1, '&col2'
FROM &table
WHERE &&condition = '&cond';
SELECT &col1, &col2
FROM "&table"
WHERE &condition = &cond;
SELECT &col1, &col2
FROM &&table
WHERE &condition = &cond;
SELECT &col1, &col2
FROM &&table
WHERE &condition = &&cond

Explanation

Question 63 of 78

Which three statements are true regarding single-row functions?

Select one or more of the following:

The data type returned, can be different from the data type of the argument that is referenced.
They can return multiple values of more than one data type.
They can accept only one argument.
They can be nested up to only two levels.
They can be used in SELECT, WHERE, and ORDER BY clauses.
They can accept column names, expressions, variable names, or a user-supplied constants as arguments.

Explanation

Question 64 of 78

View the Exhibit and examine the structure in the DEPARTMENTS tables.

Examine this SQL statement:

SELECT department_id "DEPT_ID", department_name, 'b' FROM departments WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10

Which two ORDER BY clauses can be used to sort output?

Select one or more of the following:

ORDER BY DEPT_NAME;
ORDER BY DEPT_ID;
ORDER BY b;
ORDER BY 3;

Explanation

Question 65 of 78

Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement?

Select one or more of the following:

The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
The WHERE clause can be used to exclude rows after dividing them into groups.
The HAVING clause can be used with aggregate functions in subqueries.
The WHERE clause can be used to exclude rows before dividing them into groups.

Explanation

Question 66 of 78

You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique and not null.
Which two SQL statements would create the required table?

Select one or more of the following:

CREATE TABLE employees
( employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL,
login_id NUMBER CONSTRAINT login_id_nn NOT NULL,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_num_id_uk UNIQUE (employee_id, login_id));
CREATE TABLE employees
( employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,
login_id NUMBER UNIQUE,
employee_name VARCHAR2(25),
hire_date DATE);
CREATE TABLE employees
( employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(25),
hire_date DATE,
CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));
CREATE TABLE employees
( employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id));
CREATE TABLE employees
( employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id);
CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));

Explanation

Question 67 of 78

View the Exhibit and examine the structure of the PORDUCT_INFORMATION table.
PRODUCT_ID column is the primary key. You create an index using this command:

SQL > CREATE INDEX upper_name_idx ON product_information(UPPER(product_name));

No other indexes exist on the PRODUCT_INFORMATION table. Which query would use the UPPER_NAME_IDX index?

Select one of the following:

SELECT product_id, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name) = 'LASERPRO'
OR list_price > 1000;
SELECT UPPER(product_name)
FROM product_information;
SELECT UPPER(product_name)
FROM product_information
WHERE product_id = 2254;
SELECT product_id
FROM product_information
WHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');

Explanation

Question 68 of 78

Examine the types and examples of relationship that follows:

1 One-to-one - a) teacher to Student
2 One-to-many - b) Employees to Manager
3 Many-to-one - c) Person to SSN
4 Many-to-many - d) Customers to Products

Which option indicates correctly matched relationships?

Select one of the following:

1-d, 2-b, 3-a, and 4-c
1-c, 2-d, 3-a, and 4-b
1-a, 2-b, 3-c, and 4-d
1-c, 2-a, 3-b, and 4-d

Explanation

Question 69 of 78

A non-correlated subquery can be defined as __________.

Select one of the following:

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.
A set of sequential queries, all of which must return values from the same table.
A set of sequential queries, all of which must always return a single value.
A SELECT statement that can be embedded in a clause of another SELECT statement only.

Explanation

Question 70 of 78

Which three statements are true reading subqueries?

Select one or more of the following:

A Main query can have many subqueries.
A subquery can have more than one main query.
The subquery and main query must retrieve date from the same table.
The subquery and main query can retrieve data from different tables.
Only one column or expression can be compared between the subquery and main query.
Multiple columns or expressions can be compared between the subquery and main query.

Explanation

Question 71 of 78

See the Exhibit and examine the structure of the PROMOTIONS table:

Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.

You issue the following SQL statements:

SELECT AVG(CASE WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category='A' THEN promo_cost ELSE null END) "CAT_2000A",
AVG(CASE WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category='A' THEN promo_cost ELSE null END) "CAT_5000A" FROM PROMOTIONS;

What would be the outcome?

Select one of the following:

It generates an error because multiple conditions cannot be specified for the WHEN clause.
It executes successfully and gives the required result.
It generates an error because CASE cannot be used with group functions.
It generates an error because NULL cannot be specified as a return value.

Explanation

Question 72 of 78

Which two statements are true regarding multiple-row subqueries?

Select one or more of the following:

They can contain group functions.
They always contain a subquery within a subquery.
They use the < ALL operator to imply less than the maximum.
They can be used to retrieve multiple rows from a single table only.
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.

Explanation

Question 73 of 78

View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

The CUSTOMERS table contains the current location of all currently active customers.

The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.

You need to find those customers who have never changed their address.

Which SET operator would you use to get the required output?

Select one of the following:

INTERSECT
UNION ALL
MINUS
UNION

Explanation

Question 74 of 78

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:

SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;

What would happen when the above statement is executed?

Select one of the following:

The statement would execute successfully to produce the required output.
The statement would not execute because inline views and outer joins cannot be used together.
The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
The statement would not execute because the GROUP BY clause cannot be used in the inline.

Explanation

Question 75 of 78

Which statement is true regarding the UNION operator?

Select one of the following:

By default, the output is not sorted.
Null values are not ignored during duplicate checking.
Names of all columns must be identical across all select statements.
The number of columns selected in all select statements need not be the same.

Explanation

Question 76 of 78

You issued the following command:

SQL> DROP TABLE employees;

Which three statements are true?

Select one or more of the following:

All uncommitted transactions are committed.
All indexes and constraints defined on the table being dropped are also dropped.
Sequences used in the employees table become invalid.
The space used by the employees table is reclaimed immediately.
The employees table can be recovered using the rollback command.
The employees table is moved to the recycle bin

Explanation

Question 77 of 78

Examine the create table statements for the stores and sales tables.

SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);

SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));

You executed the following statement:

SQL> DELETE from stores WHERE store_id=900;

The statement fails due to the integrity constraint error:

ORA-02292: integrity constraint (HR.STORE_ID_FK) violated

Which three options ensure that the statement will execute successfully?

Select one or more of the following:

Disable the primary key in the STORES table.
Use CASCADE keyword with DELETE statement.
DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
Disable the FOREIGN KEY in SALES table and then delete the rows.
Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

Explanation

Question 78 of 78

In the customers table, the CUST_CITY column contains the value Paris for the CUST_FIRST_NAME Abigail. Evaluate the following query

What would be the outcome?

Select one of the following:

Abigail PA
Abigail Pa
Abigail IS
An error message

Explanation

Question 79 of 78

Which two statements are true regarding constraints?

Select one or more of the following:

A foreign key column cannot contain null values.
A column with the UNIQUE constraint can contain null values.
A constraint is enforced only for INSERT operation on the table.
A constraint can be disabled even if the constraint column contains data.
All constraints can be defined at the column level and at the table level.

Explanation

Question 80 of 78

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

Select one or more of the following:

It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database
It fails because no SQL *Loader data file location is specified.
It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
It fails because no SQL *Loader control file location is specified.

Explanation

Question 81 of 78

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

Select one of the following:

Compare Period ADDM report.
AWR Compare Period report.
Active Session History (ASH) report.
Taking a new snapshot and comparing it with a preserved snapshot.

Explanation

Question 82 of 78

Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?

Select one of the following:

By default, EM express is available for a database after database creation.
You can use EM express to manage multiple databases running on the same server.
You can perform basic administrative tasks for pluggable databases by using the EM express interface.
You cannot start up or shut down a database Instance by using EM express.
You can create and configure pluggable databases by using EM express.

Explanation

Question 83 of 78

Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c?

Select one or more of the following:

ALTER TABLE DROP PARTITION
ALTER TABLE ADD PARTITION
ALTER TABLE MOVE PARTITION
ALTER TABLE MERGE PARTITION
ALTER TABLE SPLIT PARTITION
ALTER TABLE TRUNCATE PARTITION

Explanation

Question 84 of 78

View the Exhibits and examine PRODUCTS and SALES tables.

You issue the following query to display product name and the number of times the product has been sold:
SQL>SELECT p.prod_name, i.item_cnt
FROM (SELECT prod_id, COUNT(*) item_cnt
FROM sales
GROUP BY prod_id) i RIGHT OUTER JOIN products p
ON i.prod_id = p.prod_id;

What happens when the above statement is executed?

Select one of the following:

The statement executes successfully and produces the required output.
The statement produces an error because ITEM_CNT cannot be displayed in the outer query.
The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.

Explanation

Question 85 of 78

Examine the structure of the BOOKS_TRANSACTIONS table:

Examine the SQL statement:

SELECT * FROM books_transactions WHERE borrowed_date < SYSDATE AND transaction_type = 'RM' OR MEMBER_ID IN ('A101', 'A102'):

Which statement is true about the outcome?

Select one of the following:

It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
It displays details for members who have borrowed before today's date with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102
It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE
It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102

Explanation

Question 86 of 78

View the Exhibit and examine the data in the EMPLOYEES table.

You want to generate a report showing the total compensation paid to each employee to date.

You issue the following query:

SELECT ename || 'joined on ' || hiredate || ', the total compesation paid is' || TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365* sal+comm ) "COMPENSATION UNTIL DATE" FROM employees;

What is the outcome?

Select one of the following:

It generates an error because the alias is not valid.
It executes successfully and gives the correct output
It executes successfully but does not give the correct output
It generates an error because the usage of the ROUND function in the expression is not valid.
It generates an error because the concatenation operator can be used to combine only two items.

Explanation

Question 87 of 78

Evaluate the following query:

SELECT INTERVAL ‘300’ MONTH,
INTERVAL ’54-2′ YEAR TO MONTH,
INTERVAL ’11:12:10.1234567′ HOUR TO SECOND
FROM dual;

What is the correct output of the above query?

Select one of the following:

+25-00 , +54-02, +00 11:12:10.123457
+00-300, +54-02, +00 11:12:10.123457
+25-00 , +00-650, +00 11:12:10.123457
+00-300 , +00-650, +00 11:12:10.123457

Explanation

Question 88 of 78

Which two statements are true regarding savepoints?

Select one or more of the following:

Savepoints are effective only for COMMIT
Savepoints may be used to ROLLBACK
Savepoints can be used for only DML statements.
Savepoints are effective for both COMMIT and ROLLBACK.
Savepoints can be used for both DML and DDL statements.

Explanation

Question 89 of 78

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:

You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table.
You execute the SQL statement:

SELECT d.department_id FROM course_details c INNER JOIN department_details d ON c.department_id <> d.department_id;

What is the outcome?

Select one of the following:

It fails because the join type used is incorrect
It executes successfully and displays the required list
It executes successfully but displays an incorrect list
It fails because the ON clause condition is not valid

Explanation

Question 90 of 78

View the Exhibit and examine the details of the PRODUCT_INFORMATION table.

You must display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088.
You executed this SQL statement:

SELECT product_name
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;

Which statement is true regarding the execution?

Select one of the following:

It would not execute because the entire WHERE clause is not enclosed within parentheses
It would execute but would return no rows
It would not execute because the same column has been used twice with the AND logical operator
It would execute and return the desired result.

Explanation

Question 91 of 78

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

Select one of the following:

SELECT cust_last_name AS "Name", cust_credit_limit + 1000 AS "New Credit Limit" FROM customers;
SELECT cust_last_name AS Name, cust_credit_limit + 1000 AS New Credit Limit FROM customers;
SELECT cust_last_name AS Name, cust_credit_limit + 1000 "New Credit Limit" FROM customers;
SELECT INITCAP (cust_last_name) "Name", cust_credit_limit + 1000 INITCAP ("NEW CREDIT LIMIT") FROM customers;

Explanation

Question 92 of 78

View the Exhibit and examine the structure of the ORDERS table.

Which UPDATE statement is valid?

Select one of the following:

UPDATE orders SET order_date = '12-mar-2007', order_total IS NULL WHERE order_id = 2455;
UPDATE orders SET order_date = '12-mar-2007', AND order_total = TO_NUMBER(NULL) WHERE order_id = 2455;
UPDATE orders SET order_date = '12-mar-2007', order_total = NULL WHERE order_id = 2455;
UPDATE orders SET order_date = TO_DATE('12-mar-2007','dd-mon-yyyy'), SET order_total = TO_NUMBER (NULL) WHERE order_id = 2455;

Explanation

Question 93 of 78

SCOTT is a user in the database.

Evaluate the commands issued by the DBA:

Which statement is true regarding the execution of the above commands?

Select one of the following:

Statement 1 would not execute because the WITH GRANT option is missing.
Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.
Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.
Statement 1 would not execute because the IDENTIFIED BY <password> clause is missing.

Explanation

Question 94 of 78

View the Exhibit and examine the data in the PRODUCT_INFORMATION table.

Which two tasks would require subqueries?

Select one or more of the following:

displaying all the products whose minimum list prices are more than average list price of products having the status orderable
displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE
displaying the number of products whose list prices are more than the average list price
displaying all supplier IDs whose average list price is more than 500
displaying the minimum list price for each product status

Explanation

Question 95 of 78

View the Exhibit and examine the description of the EMPLOYEES table.

You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.

Which SQL statement would you execute to get the desired output?

Select one of the following:

SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct, salary,salary+commission_pct))"Total" FROM EMPLOYEES;
SELECT first_name, salary, salary*12+salary*commission_pct "Total" FROM EMPLOYEES;
SELECT first_name, salary, (salary + NVL (commission_pct, 0)*salary)*12 "Total" FROM EMPLOYEES;
SELECT first_name, salary, salary*12 + NVL(salary,0)*commission_pct, "Total" FROM EMPLOYEES;

Explanation

Question 96 of 78

View the Exhibit and examine the structure of the PROMOTIONS table.

Evaluate the following SQL statement:

SELECT promo_name, CASE WHEN promo_cost >= (SELECT AVG (promo_cost) FROM promotions WHERE promo_category = 'TV') THEN 'HIGH' ELSE 'LOW' END COST_REMARK FROM promotions

Which statement is true regarding the outcome of the above query?

Select one of the following:

It produces an error because subqueries cannot be used with the CASE expression.
It shows COST_REMARK for all the promos in the promo category "˜TV'.
It shows COST_REMARK for all the promos in the table.
It produces an error because the subquery gives an error.

Explanation

Question 97 of 78

Which statement is true regarding the USING clause in table joins?

Select one of the following:

It can be used to join a maximum of three tables
It can be used to access data from tables through equijoins as well as nonequijoins
It can be used to join tables that have columns with the same name and compatible data types
It can be used to restrict the number of columns used in a NATURAL join

Explanation

Question 98 of 78

Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the storage of data in the above table structure?

Select one or more of the following:

The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.

Explanation

Question 99 of 78

Examine the structure of the MARKS table:

Which two statements would execute successfully?

Select one or more of the following:

SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks WHERE subject1 > subject2;
SELECT student_name subject1 FROM marks WHERE subject1 > AVG(subject1);
SELECT SUM(subject1+subject2+subject3) FROM marks WHERE student_name IS NULL;
SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE "˜R%';

Explanation

Question 100 of 78

Examine the data in the CUSTOMERS table:

You want to list all cities that have more than one customer along with the customer details.
Evaluate the following query:

SELECT c1.custname, c1.city FROM Customers C1 ____________ Customers C2 ON (c1.city = c2.city AND c1.custname <> c2.custname);

Which two JOIN options can be used in the blank in the above query to give the correct output?

Select one or more of the following:

LEFT OUTER JOIN
JOIN
NATURAL JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN

Explanation

Question 101 of 78

Examine the structure proposed for the TRANSACTIONS table:

Which statements are true regarding the creation and storage of data in the above table structure?

Select one or more of the following:

The CUST_STATUS column would store exactly one character.
The TRANS_VALIDITY column would have a maximum size of one character.
The CUST_CREDIT_LIMIT column would be able to store decimal values.
The CUST_STATUS column would give an error.
The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
The TRANS_VALIDITY column would give an error.

Explanation

Question 102 of 78

View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.

You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task?

Select one or more of the following:

SELECT i.product_id, i.quantity_on_hand, pi.supplier_id FROM product_information pi JOIN inventories i ON (pi.product_id=i.product_id) WHERE quantity_on_hand < 5;
SELECT product_id, quantity_on_hand, supplier_id FROM product_information NATURAL JOIN inventories AND quantity_on_hand < 5;
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id FROM product_information pi JOIN inventories i ON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id FROM product_information pi JOIN inventories i USING (product_id) AND quantity_on_hand < 5;

Explanation

Question 103 of 78

In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than 10 years.
Evaluate the following SQL statement:
What would be the result?

Select one of the following:

It executes successfully but no rows updated.
It executes successfully and updates the records of those employees who have been working in the company for more than 600 days.
It gives an error because multiple NVL functions are used in an expression.
It gives an error because NVL function cannot be used with UPDATE.

Explanation

Question 104 of 78

Which statement adds a column called SALARY to the EMPLOYEES table having 100 rows, which cannot contain null?

Select one of the following:

ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL;
ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT NOT NULL;
ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) NOT NULL;

Explanation

Question 105 of 78

View the Exhibit and examine the data in the PROMOTIONS table.

PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?

Select one of the following:

SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00';
SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';
SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';

Explanation

Question 106 of 78

Which two statements are true regarding views?

Select one or more of the following:

The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
The OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.
Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
Rows added through a view are deleted from the table automatically when the view is dropped.
A simple view in which column aliases have been used cannot be updated.
A subquery used in a complex view definition cannot contain group functions or joins.

Explanation

Question 107 of 78

View the Exhibit and examine the structure of CUSTOMERS table.

Evaluate the following query:

SELECT cust_id, cust_city
FROM customers
WHERE cust_first_name NOT LIKE 'A_%g_%'
AND cust_credit_limit BETWEEN 5000 AND 15000
AND cust_credit_limit NOT IN (7000,11000)
AND cust_city NOT BETWEEN 'A' AND 'B'

Which statement is true regarding the above query?

Select one of the following:

It produces an error because the condition on the CUST_CITY column is not valid.
It produces an error because the condition on the CUST_FIRST_NAME column is not valid.
It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.
It executes successfully.

Explanation

Question 108 of 78

Evaluate the following CREATE SEQUENCE statement:

CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;

The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?

Select one of the following:

100
an error
10
1

Explanation

Question 109 of 78

Which statement is true regarding the SESSION_PRIVS dictionary view?

Select one of the following:

It contains the object privileges granted to other users by the current user session.
It contains the system privileges granted to other users by the current user session.
It contains the current object privileges available in the user session.
It contains the current system privileges available in the user session.

Explanation

Question 110 of 78

Which three statements indicate the end of a transaction?

Select one or more of the following:

after a CREATE statement is issued
after a SAVEPOINT is issued
after a SELECT statement is issued
after a ROLLBACK is issued
after a COMMIT is issued

Explanation

Question 111 of 78

Examine the structure of the BOOKS_TRANSACTIONS table.
You want to update this table such that BOOK_ID is set to 'INVALID' for all rows where no MEMBER_ID has been entered.
Examine this partial SQL statement:

UPDATE books_transactions
SET book_id = 'INVALID'
WHERE ______________

Which condition must be used in the WHERE clause to perform the required update?

Select one of the following:

MEMBER_ID = '';
MEMBER_ID = NULL;
MEMBER_ID IS NULL;
MEMBER_ID = "";

Explanation

Question 112 of 78

Evaluate the following SQL commands:

The command to create a table fails. Identify the reason for the SQL statement failure.

Select one of the following:

You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.
You cannot use the BETWEEN clause in the condition of a CHECK constraint.
You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
You cannot use SYSDATE in the condition of a CHECK constraint.

Explanation

Question 113 of 78

Evaluate the following query:

What would be the outcome of the above query?

Select one of the following:

It produces an error because the data types are not matching.
It executes successfully and displays the literal " {'s start date was \> " for each row in the output.
It executes successfully and introduces an 's at the end of each promo_name in the output.
It produces an error because flower braces have been used.

Explanation

Question 114 of 78

View the exhibit and examine the description for the SALES and CHANNELS tables.

You issued this SQL statement:

INSERT INTO sales VALUES (23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel_desc = 'Direct Sales'), 12, 1, 500);

Which statement is true regarding the result?

Select one of the following:

The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.
The statement will fail because a subquery cannot be used in a VALUES clause.
The statement will fail because the VALUES clause is not required with a subquery.
The statement will execute and a new row will be inserted in the SALES table.

Explanation

Question 115 of 78

View the Exhibit and examine the structure of the CUSTOMERS table.

Evaluate the following SQL statement:

SELECT cust_city, COUNT(cust_last_name)
FROM customers
WHERE cust_credit_limt > 1000
GROUP BY cust_city
HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;

Which statement is true regarding the outcome of the above query?

Select one of the following:

It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
It executes successfully.

Explanation

Question 116 of 78

You notice that the elapsed time for an important database scheduler Job is unacceptably long. The job belongs to a scheduler job class and window. Which two actions would reduce the job’s elapsed time?

Select one or more of the following:

Increasing the job’s relative priority within the Job class to which it belongs
Increasing the priority of the scheduler window to which the job belongs
Increasing the value of the JOB_QUEUE_PROCESSES parameter
Increasing the resource allocation for the consumer group mapped to the scheduler job’s
job class within the plan mapped to the scheduler window
Increasing the priority of the job class to which the job belongs
Moving the job to an existing higher priority scheduler window with the same schedule
and duration

Explanation

Question 117 of 78

You run a script that completes successfully using SQL*Plus that performs these actions: 1.Creates a multitenant container database (CDB) 2. Plugs in three pluggable databases (PDBs) 3. Shuts down the CDB instance 4. Starts up the CDB instance using STARTUP OPEN READ WRITE Which two statements are true about the outcome after running the script?

Select one or more of the following:

The PDBs will be opened read/write.
The seed will be in mount state.
The seed will be opened read-only
The seed will be opened read/write.
The other PDBs will be opened read-only.
The other PDBs will be in mount state.

Explanation

Question 118 of 78

Identify three valid methods of opening, pluggable databases (PDBs).

Select one or more of the following:

ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from the root
ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from a PDB
ALTER PLUGGABLE DATABASE PDB OPEN issued from the seed
ALTER DATABASE PDB OPEN issued from the root
ALTER DATABASE OPEN issued from that PDB
ALTER PLUGGABLE DATABASE PDB OPEN issued from another PDB
ALTER PLUGGABLE DATABASE OPEN issued from that PDB

Explanation

Question 119 of 78

Which two are false about the large pool for an Oracle database instance that supports shared server connections?

Select one or more of the following:

Allocates memory for RMAN backup and restore operations
Allocates memory for shared and private SQL areas
Contains a cursor area for storing runtime information about cursors
Contains stack space
Contains a hash area performing hash joins of tables

Explanation

Question 120 of 78

Which two action are impossible during an Online Datafile Move operation?

Select one or more of the following:

Creating and dropping tables in the datafile being moved
Performing file shrink of the data file being moved
Querying tables in the datafile being moved
Performing Block Media Recovery for a data block in the datafile being moved
Flashing back the database
Executing DML statements on objects stored in the datafile being moved

Explanation

Question 121 of 78

Which two statements are true about the SET VERIFY ON command?

Select one or more of the following:

It can be used in SQL Developer and SQL*Plus
It displays values for variables used only in the WHERE clause of a query
It can be used only in SQL*Plus
It displays values for variables prefixed with &&
It displays values for variables created by the DEFINE command

Explanation

Question 122 of 78

Which two statements are true about the rules of precedence for operators?

Select one or more of the following:

The concatenation operator | | is always evaluated before addition and subtraction in an expression
Multiple parentheses can be used to override the default precedence of operators in an expression
Arithmetic operators with equal precedence area evaluated from left to right within an expression
NULLS influence the precedence of operators in an expression
The + binary operator has the highest precedence in an expression in a SQL statement

Explanation

Question 123 of 78

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?

Select one or more of the following:

It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted
Only the primary key can be defined at the column and table level
The foreign key columns and parent table primary key columns must have the same names
A table can have only one primary key and one foreign key
A table can have only one primary key but multiple foreign keys
Primary key and foreign key constraints can be defined at both the column and table level
It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted

Explanation

Question 124 of 78

Which three are true about system and object privileges?

Select one or more of the following:

WITH GRANT OPTION can be used when granting an object privilege to both users and roles
Adding a primary key constraint to an existing table in another schema requires a system privilege
Adding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege
Revoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect
Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect.
WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC

Explanation

Question 125 of 78

Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)?

Select one or more of the following:

Implementing a relationship between two tables might require joining additional tables
Relating data from a table with data from the same table is implemented with a self join
Rows from unrelated tables cannot be joined
Every relationship between the two tables must be implemented in a join condition
An inner join relates rows within the same table

Explanation

Question 126 of 78

Which two statements are true about substitution variables?

Select one or more of the following:

A substitution variable can be used with any clause in a SELECT statement
A substitution variable used to prompt for a column name must be enclosed in a single quotation marks
A substitution variable prefixed with & always prompts only once for a value in a session
A substitution variable can be used only in a SELECT statement
A substitution variable used to prompt for a column name must be enclosed in double quotation marks
A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session

Explanation

Question 127 of 78

Which three statements are true about inner and outer joins?

Select one or more of the following:

A full outer join must use Oracle syntax
An inner join returns matched rows
A left or right outer join returns only unmatched rows
A full outer join returns matched and unmatched rows
Outer joins can only be used between two per query
Outer joins can be used when there are multiple join conditions on two tables

Explanation

Question 128 of 78

Which three statements are true about the DESCRIBE command?

Select one or more of the following:

It can be used to display the structure of an existing view
It can be used only from SQL*Plus
It displays the PRIMARY KEY constraint for any column or columns that have that constraint
It can be used from SQL Developer
It displays all constraints that are defined for each column
It displays the NOT NULL constraint for any columns that have that constraint

Explanation

Question 129 of 78

The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.
The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson.
Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?

Select one of the following:

SELECT REPLACE (TRIM(TRAILING 'son' FROM name1), 'An', 'O') FROM names;
SELECT INITCAP (REPLACE(TRIM('son' FROM name1), 'An', 'O')) FROM names;
SELECT REPLACE (SUBSTR(name1, -3), 'An', 'O') FROM names;
SELECT REPLACE (REPLACE(name1, 'son', ''), 'An', 'O') FROM names;

Explanation

Question 130 of 78

Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined?

Select one or more of the following:

Insert statements can always be done on a table through a view.
Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
Delete statements can always be done on a table through a view.
The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.

Explanation

Question 131 of 78

Evaluate these commands which execute successfully:

Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?

Select one or more of the following:

Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.
Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.
If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.
Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.

Explanation

Question 132 of 78

Which two statements are true about INTERVAL data types

Select one or more of the following:

The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.
INTERVAL DAY TO SECOND columns support fractions of seconds.
INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
INTERVAL YEAR TO MONTH columns support yearly intervals.
INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.

Explanation

Question 133 of 78

An Oracle database server session has an uncommitted transaction in progress which updated 5000 rows in a table.
In which three situations does the transactions complete thereby committing the updates?

Select one or more of the following:

when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT
when a CREATE INDEX statement is executed successfully in the same session
when a COMMIT statement is issued by the same user from another session in the same database instance
when the session logs out successfully
when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

Explanation

Question 134 of 78

Which three statements are true regarding indexes?

Select one or more of the following:

A SELECT statement can access one or more indices without accessing any tables
An update to a table can result in no updates to any of the table's indexes
A table belonging to one user can have an index that belongs to a different user
A UNIQUE index can be altered to be non-unique
An update to a table can result in updates to any or all of the table's indexes
When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped

Explanation

Question 135 of 78

Which three are true about the CREATE TABLE command?

Select one or more of the following:

It can include the CREATE..INDEX statement for creating an index to enforce the primary key constraint
It implicitly executes a commit
A user must have the CREATE ANY TABLE privilege to create tables
It implicitly rolls back any pending transactions
The owner of the table should have space quota available on the tablespace where the table is defined
The owner of the table must have the UNLIMITED TABLESPACE system privilege

Explanation

Question 136 of 78

You need to calculate the number of days from 1
January 2019 until today.
Dates are stored in the default format of DD-MON-RR.
Which two queries give the required output?

Select one or more of the following:

SELECT to_char(sysdate, 'DD-MON-YYYY') - '01-JAN-2019' FROM DUAL;
SELECT sysdate-to_date('01-JANUARY-2019') FROM DUAL;
SELECT ROUND(sysdate-'01-JAN-2019') FROM DUAL;
SELECT ROUND(sysdate-TO_DATE('01/JANUARY/2019')) FROM DUAL;
SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;

Explanation

Question 137 of 78

Which three actions can you perform by using the ORACLE_DATAPUMP access driver?

Select one or more of the following:

Read data from an external table and load it into a table in the database
Create a directory object for an external table
Execute DML statements on an external table
Query data from an external table
Read data from a table in the database and insert it into an external table
Create a directory object for a flat file

Explanation

Question 138 of 78

Which three privileges can be restricted to a subset of columns in a table?

Select one or more of the following:

ALTER
DELETE
UPDATE
SELECT
INDEX
REFERENCES
INSERT

Explanation

Question 139 of 78

You have been tasked to create a table for a banking application.
One of the columns must meet three requirements:
1) Be stored in a format supporting date arithmetic without using conversion functions
2) Store a load period of up to 10 years
3) Be used for calculating interest for the number of days the loan remains unpaid
Which data type should you use?

Select one of the following:

TIMESTAMP WITH LOCAL TIMEZONE
TIMESTAMP WITH TIMEZONE
INTERVAL DAY TO SECOND
TIMESTAMP
INTERVAL YEAR TO MONTH

Explanation

Question 140 of 78

The ORDERS table has a primary key constraint on the ORDER_ID column.
The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.
The constraint is defined with ON DELETE CASCADE.
There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000.
Which three DELETE statements execute successfully?

Select one or more of the following:

DELETE order_id FROM orders WHERE order_total < 1000;
DELETE FROM orders WHERE order_total < 1000;
DELETE orders WHERE order_total < 1000;
DELETE * FROM orders WHERE order_total < 1000;
DELETE FROM orders;

Explanation

Question 141 of 78

Which three statements are true about single-row functions?

Select one or more of the following:

They can be nested to any level
he data type returned can be different from the data type of the argument
They can accept only one argument
The argument can be a column name, variable, literal or an expression
They can be used only in the WHERE clause of a SELECT statement
They return a single result row per table

Explanation

Question 142 of 78

Which two statements are true regarding a SAVEPOINT?

Select one or more of the following:

A SAVEPOINT does not issue a COMMIT
Only one SAVEPOINT may be issued in a transaction
Rolling back to a SAVEPOINT can undo a TRUNCATE statement
Rolling back to a SAVEPOINT can undo a CREATE INDEX statement
Rolling back to a SAVEPOINT can undo a DELETE statement

Explanation

Question 143 of 78

Which two are true about savepoints?

Select one or more of the following:

After issuing a savepoints, you can roll back to the savepoint name within the current transaction.
A ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error.
They make uncommitted updates visible to other sessions owned by the same user.
After issuing a savepoint, you cannot roll back the complete transaction.
You can commit updates done between two savepoints without committing other updates in the current transaction
They make uncommitted updates visible to sessions owned by other users

Explanation

Question 144 of 78

ALTER TABLE employees SET UNUSED (department_id)
Which two are true?

Select one or more of the following:

No updates can be made to the data in the DEPARTMENT_ID column.
A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.
A query can be display data from the DEPARTMENT_ID column.
The DEPARTMENT_ID column is set to null for all rows in the table.
The DEPARTMENT_ID column can be recovered from the recycle bin.
The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.

Explanation

Question 145 of 78

The SYSDATE function displays the current Oracle Server date as:
21-MAY-19
You wish to display the date as:

MONDAY, 21 MAY, 2019 -
Which statement will do this?

Select one or more of the following:

SELECT TO_DATE(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'FMDD, DAY MONTH, YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'FMDAY, DDTH MONTH, YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;

Explanation

Question 146 of 78

Which two are SQL features?

Select one or more of the following:

processing sets of data
providing update capabilities for data in external files
providing graphical capabilities
providing variable definition capabilities
providing database transaction control

Explanation

Question 147 of 78

Which two are true about scalar subquery expressions?

Select one or more of the following:

They can return at most one row.
You can use them as a default value for a column
You cannot correlate them with a table in the parent statement.
You must enclose them in parentheses.
They can return two columns.

Explanation

Question 148 of 78

You have the privileges to create any type of synonym.
Which statement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accessible to all users?

Select one of the following:

CREATE PUBLIC SYNONIM emp FOR hcm.employee_records;
CREATE GLOBAL SYNONIM emp FOR hcm.employee_records;
CREATE SYNONIM emp FOR hcm.employee_records;
CREATE SYNONIM PUBLIC.emp FOR hcm.employee_records;
CREATE SYNONIM SYS.emp FOR hcm.employee_records;

Explanation

Question 149 of 78

A session's NLS_DATE_FORMAT is set to DD Mon YYYY.
Which two queries return the value 1 Jan 2019?

Select one or more of the following:

SELECT TO_DATE('2019-01-01') FROM DUAL;
SELECT DATE '2019-01-01' FROM DUAL;
SELECT '2019-01-01' FROM DUAL;
SELECT TO_CHAR('2019-01-01') FROM DUAL;
SELECT TO_DATE('2019-01-01', 'YYYY-MM-DD') FROM DUAL;

Explanation

Question 150 of 78

No-user-defined locks are used in your database.
Which three are true about Transaction Control Language (TCL)?

Select one or more of the following:

COMMIT erases all the transaction's savepoints and releases its locks.
ROLLBACK TO SAVEPOINT undoes the transaction's changes made since the named savepoint and then ends the transaction.
COMMIT ends the transaction and makes all its changes permanent.
ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.
ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks.
ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes, releases its locks and erases its savepoints.

Explanation

Check answer

["https://cdn.goconqr.com/uploads/media/image/24161648/desktop_b61a7b0f-57ff-45df-9657-ac478152add0.jpg","https://cdn.goconqr.com/uploads/media/image/24205966/desktop_98645a5a-46dd-4563-b0f1-30465d638587.jpg","https://cdn.goconqr.com/uploads/media/image/24208464/desktop_a25d1420-2204-4412-8286-fcd8c4b36596.jpg","https://cdn.goconqr.com/uploads/media/image/25491867/desktop_7f6ea28e-874f-4787-9c10-2e75e29ded60.jpg","https://cdn.goconqr.com/uploads/media/image/24058972/desktop_7d3d9596-c9fd-43b0-8873-59fa3e2658a3.jpg","https://cdn.goconqr.com/uploads/media/image/24059116/desktop_906b78c0-3bee-43bb-98c9-82feb413aab8.jpg","https://cdn.goconqr.com/uploads/media/image/24205966/desktop_98645a5a-46dd-4563-b0f1-30465d638587.jpg","https://cdn.goconqr.com/uploads/media/image/24208464/desktop_a25d1420-2204-4412-8286-fcd8c4b36596.jpg","https://cdn.goconqr.com/uploads/media/image/24058157/desktop_a8706ef8-f37c-4510-8fb8-db78da4a061f.jpg","https://cdn.goconqr.com/uploads/media/image/24059116/desktop_906b78c0-3bee-43bb-98c9-82feb413aab8.jpg","https://cdn.goconqr.com/uploads/media/image/25830707/desktop_375aa0b4-243e-4389-8737-269151e77d7e.jpg","https://cdn.goconqr.com/uploads/media/image/25830896/desktop_096b6130-0586-4177-8067-091dc33b233a.jpg","https://cdn.goconqr.com/uploads/media/image/25831148/desktop_b9edb8b4-bc35-45ce-a241-4eaa1edc53b9.jpg","https://cdn.goconqr.com/uploads/media/image/25831416/desktop_e0f52ae4-7eb2-4ca1-afbb-1015ed71757e.jpg","https://cdn.goconqr.com/uploads/media/image/24059116/desktop_906b78c0-3bee-43bb-98c9-82feb413aab8.jpg","https://cdn.goconqr.com/uploads/media/image/25868896/desktop_dd2c9d46-9e24-4f83-9bf9-2015375d2208.jpg","https://cdn.goconqr.com/uploads/media/image/25870412/desktop_c0ea1e6a-6324-4264-b58d-0320c3a52985.jpg","https://cdn.goconqr.com/uploads/media/image/25912080/desktop_476f8ea2-02b2-4627-a9cb-c767cb7b3ff4.jpg","https://cdn.goconqr.com/uploads/media/image/25912227/desktop_563f7115-0888-4ab7-8edb-c3e3918677ea.jpg","https://cdn.goconqr.com/uploads/media/image/24058157/desktop_a8706ef8-f37c-4510-8fb8-db78da4a061f.jpg","https://cdn.goconqr.com/uploads/media/image/25914431/desktop_2b3cae7a-6a34-4d07-b77d-a27ac6bbf577.jpg","https://cdn.goconqr.com/uploads/media/image/25914910/desktop_893b1105-ae3b-471b-b71c-6bc22947362d.jpg","https://cdn.goconqr.com/uploads/media/image/25936048/desktop_dfa1c1a9-9c02-44f8-81e0-f728d98be00c.jpg","https://cdn.goconqr.com/uploads/media/image/25944862/desktop_20e3f1d3-df9e-4127-92bb-49eb79c91d63.jpg","https://cdn.goconqr.com/uploads/media/image/24055824/desktop_8762746d-3fe4-4e99-86b3-db79babcfb32.jpg","https://cdn.goconqr.com/uploads/media/image/25951468/desktop_f5e67931-e54c-423a-971a-71664a51277c.jpg","https://cdn.goconqr.com/uploads/media/image/25951619/desktop_a954a90b-c74c-4092-8370-60aaeb925746.jpg","https://cdn.goconqr.com/uploads/media/image/25952135/desktop_fa89427c-a3ba-44b8-8a61-753b0107d710.jpg","https://cdn.goconqr.com/uploads/media/image/24058157/desktop_a8706ef8-f37c-4510-8fb8-db78da4a061f.jpg","https://cdn.goconqr.com/uploads/media/image/24059116/desktop_906b78c0-3bee-43bb-98c9-82feb413aab8.jpg","https://cdn.goconqr.com/uploads/media/image/26195088/desktop_5be84845-20eb-4f88-a27a-db5c645aedf8.jpg","https://cdn.goconqr.com/uploads/media/image/26196155/desktop_321ebb54-c40c-4c16-af38-3c4d17e2289e.jpeg","https://cdn.goconqr.com/uploads/media/image/26201274/desktop_83102359-fc9f-469c-9cc2-f18627f9764a.jpeg","https://cdn.goconqr.com/uploads/media/image/26736588/desktop_2313291a-3f3a-4e99-91b5-3f819e52144d.jpg","https://cdn.goconqr.com/uploads/media/image/26738063/desktop_80364b1b-b4c5-4bfc-89bb-93360f37add4.jpg","https://cdn.goconqr.com/uploads/media/image/24056685/desktop_cb922957-8175-45f8-b0e7-7151c159a5f7.jpg","https://cdn.goconqr.com/uploads/media/image/24021127/desktop_7694b8d9-abac-4bcb-9fba-d21ff483803d.jpg","https://cdn.goconqr.com/uploads/media/image/24208464/desktop_a25d1420-2204-4412-8286-fcd8c4b36596.jpg","https://cdn.goconqr.com/uploads/media/image/26745294/desktop_ce9c98c4-9cd3-4245-ae23-7d965f087442.jpg","https://cdn.goconqr.com/uploads/media/image/26745753/desktop_46a74c70-9411-4c67-ae4a-c9091f350762.jpg","https://cdn.goconqr.com/uploads/media/image/24058157/desktop_a8706ef8-f37c-4510-8fb8-db78da4a061f.jpg","https://cdn.goconqr.com/uploads/media/image/26195088/desktop_5be84845-20eb-4f88-a27a-db5c645aedf8.jpg","https://cdn.goconqr.com/uploads/media/image/26747877/desktop_f4003e0e-974a-4cbd-a1e5-8f23aa98713c.jpg","https://cdn.goconqr.com/uploads/media/image/26748205/desktop_2f489362-6be9-4db6-a252-60b4439ff53e.jpg","https://cdn.goconqr.com/uploads/media/image/26748509/desktop_6acc2fa1-536c-4fec-a8ab-1c542d325609.jpg","https://cdn.goconqr.com/uploads/media/image/26750316/desktop_4c39b544-fa6c-4720-ac5b-3b5b681a1627.jpg","https://cdn.goconqr.com/uploads/media/image/24059116/desktop_906b78c0-3bee-43bb-98c9-82feb413aab8.jpg","https://cdn.goconqr.com/uploads/media/image/26751257/desktop_4d341b7c-8ab2-4630-b90a-9dac6ae42496.jpg","https://cdn.goconqr.com/uploads/media/image/26752072/desktop_6cb619ea-e8e7-42a2-86e8-8a0c69abf976.jpg","https://cdn.goconqr.com/uploads/media/image/24058972/desktop_7d3d9596-c9fd-43b0-8873-59fa3e2658a3.jpg","https://cdn.goconqr.com/uploads/media/image/26833534/desktop_a053ec55-be14-4a6a-974f-b51e24d82340.jpg","https://cdn.goconqr.com/uploads/media/image/26833647/desktop_a1f5b3ce-13e5-46b0-bcb4-664471c5655b.jpg","https://cdn.goconqr.com/uploads/media/image/26833778/desktop_ca95a196-951a-4104-a0e8-d4de7d2da99f.jpg","https://cdn.goconqr.com/uploads/media/image/26834041/desktop_7adcf6bd-33b4-4768-976c-67df73b004dd.jpg","https://cdn.goconqr.com/uploads/media/image/24058972/desktop_7d3d9596-c9fd-43b0-8873-59fa3e2658a3.jpg","https://cdn.goconqr.com/uploads/media/image/29380606/desktop_bf2bb0b7-57f0-4a50-b2d8-3a5b9fd3d87b.jpg"]

	Created by Juan Taborda over 4 years ago

examen corregido

OCA 1Z0-071 12C - Parte 2

Question 1 of 78

Which statement is true about Data Manipulation Language (DML)?

Select one of the following:

Explanation

Question 2 of 78

See the Exhibit and examine the structure of the PROMOTIONS table: You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the INTERNET category. Which query would give you the required output?

Select one of the following:

Explanation

Question 3 of 78

Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)

Select one or more of the following:

Explanation

Question 4 of 78

Evaluate the following CREATE TABLE command: Which statement is true regarding the above SQL statement?

Select one of the following:

Explanation

Question 5 of 78

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?

Select one of the following:

Explanation

Question 6 of 78

Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement?

Select one or more of the following:

Explanation

Question 7 of 78

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

Select one of the following:

Explanation

Question 8 of 78

Select one of the following:

Explanation

Question 9 of 78

Select one of the following:

Explanation

Question 10 of 78

Select one of the following:

Explanation

Question 11 of 78

Select one of the following:

Explanation

Question 12 of 78

Which two statements are true regarding the SQL GROUP BY clause?

Select one or more of the following:

Explanation

Question 13 of 78

You issue this command which succeeds: SQL> DROP TABLE products; Which three statements are true?

Select one or more of the following:

Explanation

Question 14 of 78

You 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) NOT NULL DEFAULT 'SEATTLE', CONSTRAINT cnames CHECK (first_name<>last_name) ); What is the outcome?

Select one of the following:

Explanation

Question 15 of 78

Select one of the following:

Explanation

Question 16 of 78

Examine the structure of the PROGRAMS table: Which two SQL statements would execute successfully?

Select one or more of the following:

Explanation

Question 17 of 78

Select one of the following:

Explanation

Question 18 of 78

Select one of the following:

Explanation

Question 19 of 78

Select one of the following:

Explanation

Question 20 of 78

Select one of the following:

Explanation

Question 21 of 78

View the Exhibit and examine the description of the ORDERS table. Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

Select one or more of the following:

Explanation

Question 22 of 78

Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

Select one or more of the following:

See the Exhibit and examine the structure of the PROMOTIONS table:

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the INTERNET category.

Which query would give you the required output?

Evaluate the following CREATE TABLE command:
Which statement is true regarding the above SQL statement?

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount
in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?

You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true?

You 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) NOT NULL DEFAULT 'SEATTLE',
CONSTRAINT cnames CHECK (first_name<>last_name) );
What is the outcome?

Examine the structure of the PROGRAMS table:

Which two SQL statements would execute successfully?

View the Exhibit and examine the description of the ORDERS table.

Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

View the Exhibit and examine the details of the PRODUCT_INFORMATION table. (Choose two.)

Evaluate this SQL statement:

SELECT TO_CHAR (list_price, $9,999)
From product_information;

Which two statements are true regarding the output?

Examine the structure of the ORDERS table:

You want to find the total value of all the orders for each year and issue this command:

SQL> SELECT TO_CHAR(order_date,rr), SUM(order_total) FROM orders GROUP BY TO_CHAR(order_date, yyyy);

Which statement is true regarding the result?

View the Exhibit and examine the structure of the ORDER_ITEMS table. (Choose the best answer.)

You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.

Which query would produce the desired result?

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table.

Evaluate this SQL statement:

ALTER TABLE emp DROP COLUMN first_name;

Which two statements are true?

View the exhibit and examine the structure and data in the invoice table

Which two SQL statements would execute successfully?

View the Exhibit and examine the structure of the ORDERS table.

You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.

Which query would give you the desired result?

You must display details of all users whose username contains the string ch_. (Choose the best answer.)

Which query generates the required output?