OCA 1Z0-071 12C - Parte 2

Este test está cronometrado.

Tienes 2 horas para completar 78 preguntas de este test.

Iniciar Test

Por favor espera - en proceso…

Pregunta 1 de 78

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

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 2 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 3 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 4 de 78

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

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 5 de 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?

Selecciona una de las siguientes respuestas posibles:

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;

Explicación

Pregunta 6 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 7 de 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?

Selecciona una de las siguientes respuestas posibles:

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;

Explicación

Pregunta 8 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 9 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 10 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 11 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 12 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 13 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 14 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 15 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 16 de 78

Examine the structure of the PROGRAMS table:

Which two SQL statements would execute successfully?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 17 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 18 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 19 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 20 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 21 de 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.)

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 22 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 23 de 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.

Selecciona una de las siguientes respuestas posibles:

UNION
MINUS
INTERSECT
UNION ALL

Explicación

Pregunta 24 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 25 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 26 de 78

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

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 27 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 28 de 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?

Selecciona una de las siguientes respuestas posibles:

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)

Explicación

Pregunta 29 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 30 de 78

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

Which two SQL statements would execute successfully?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 31 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 32 de 78

Which three statements are true regarding subqueries?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 33 de 78

Which two statements are true regarding single row functions?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 34 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 35 de 78

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

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 36 de 78

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

Which query generates the required output?

Selecciona una de las siguientes respuestas posibles:

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 '\';

Explicación

Pregunta 37 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 38 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 39 de 78

Which two statements are true regarding subqueries?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 40 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 41 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 42 de 78

You issued this command:

SQL > DROP TABLE employees;

Which three statements are true?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 43 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 44 de 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?

Selecciona una o más de las siguientes respuestas posibles:

FULL OUTER JOIN
JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN

Explicación

Pregunta 45 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 46 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 47 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 48 de 78

Which two statements are true regarding constraints?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 49 de 78

Which two statements are true regarding working with dates?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 50 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 51 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 52 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 53 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 54 de 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.

Selecciona una de las siguientes respuestas posibles:

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;

Explicación

Pregunta 55 de 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?

Selecciona una de las siguientes respuestas posibles:

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;

Explicación

Pregunta 56 de 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?

Selecciona una de las siguientes respuestas posibles:

Date
Number
Timestamp
Interval day to second
Interval year to month

Explicación

Pregunta 57 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 58 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 59 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 60 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 61 de 78

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

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 62 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 63 de 78

Which three statements are true regarding single-row functions?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 64 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 65 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 66 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 67 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 68 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 69 de 78

A non-correlated subquery can be defined as __________.

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 70 de 78

Which three statements are true reading subqueries?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 71 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 72 de 78

Which two statements are true regarding multiple-row subqueries?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 73 de 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?

Selecciona una de las siguientes respuestas posibles:

INTERSECT
UNION ALL
MINUS
UNION

Explicación

Pregunta 74 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 75 de 78

Which statement is true regarding the UNION operator?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 76 de 78

You issued the following command:

SQL> DROP TABLE employees;

Which three statements are true?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 77 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 78 de 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?

Selecciona una de las siguientes respuestas posibles:

Abigail PA
Abigail Pa
Abigail IS
An error message

Explicación

Pregunta 79 de 78

Which two statements are true regarding constraints?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 80 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 81 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 82 de 78

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

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 83 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 84 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 85 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 86 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 87 de 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?

Selecciona una de las siguientes respuestas posibles:

+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

Explicación

Pregunta 88 de 78

Which two statements are true regarding savepoints?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 89 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 90 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 91 de 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?

Selecciona una de las siguientes respuestas posibles:

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;

Explicación

Pregunta 92 de 78

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

Which UPDATE statement is valid?

Selecciona una de las siguientes respuestas posibles:

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;

Explicación

Pregunta 93 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 94 de 78

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

Which two tasks would require subqueries?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 95 de 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?

Selecciona una de las siguientes respuestas posibles:

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;

Explicación

Pregunta 96 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 97 de 78

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

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 98 de 78

Examine the structure proposed for the TRANSACTIONS table:

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 99 de 78

Examine the structure of the MARKS table:

Which two statements would execute successfully?

Selecciona una o más de las siguientes respuestas posibles:

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%';

Explicación

Pregunta 100 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 101 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 102 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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;

Explicación

Pregunta 103 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 104 de 78

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

Selecciona una de las siguientes respuestas posibles:

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;

Explicación

Pregunta 105 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 106 de 78

Which two statements are true regarding views?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 107 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 108 de 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?

Selecciona una de las siguientes respuestas posibles:

100
an error
10
1

Explicación

Pregunta 109 de 78

Which statement is true regarding the SESSION_PRIVS dictionary view?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 110 de 78

Which three statements indicate the end of a transaction?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 111 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 112 de 78

Evaluate the following SQL commands:

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

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 113 de 78

Evaluate the following query:

What would be the outcome of the above query?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 114 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 115 de 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?

Selecciona una de las siguientes respuestas posibles:

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.

Explicación

Pregunta 116 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 117 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 118 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 119 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 120 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 121 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 122 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 123 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 124 de 78

Which three are true about system and object privileges?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 125 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 126 de 78

Which two statements are true about substitution variables?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 127 de 78

Which three statements are true about inner and outer joins?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 128 de 78

Which three statements are true about the DESCRIBE command?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 129 de 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?

Selecciona una de las siguientes respuestas posibles:

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;

Explicación

Pregunta 130 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 131 de 78

Evaluate these commands which execute successfully:

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 132 de 78

Which two statements are true about INTERVAL data types

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 133 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 134 de 78

Which three statements are true regarding indexes?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 135 de 78

Which three are true about the CREATE TABLE command?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 136 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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;

Explicación

Pregunta 137 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 138 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

ALTER
DELETE
UPDATE
SELECT
INDEX
REFERENCES
INSERT

Explicación

Pregunta 139 de 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?

Selecciona una de las siguientes respuestas posibles:

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

Explicación

Pregunta 140 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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;

Explicación

Pregunta 141 de 78

Which three statements are true about single-row functions?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 142 de 78

Which two statements are true regarding a SAVEPOINT?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 143 de 78

Which two are true about savepoints?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 144 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 145 de 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?

Selecciona una o más de las siguientes respuestas posibles:

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;

Explicación

Pregunta 146 de 78

Which two are SQL features?

Selecciona una o más de las siguientes respuestas posibles:

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

Explicación

Pregunta 147 de 78

Which two are true about scalar subquery expressions?

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Pregunta 148 de 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?

Selecciona una de las siguientes respuestas posibles:

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;

Explicación

Pregunta 149 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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;

Explicación

Pregunta 150 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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.

Explicación

Revisar respuesta

["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"]

	Creado por Juan Taborda hace más de 4 años

examen corregido

OCA 1Z0-071 12C - Parte 2

Pregunta 1 de 78

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

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 2 de 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?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 3 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 4 de 78

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

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 5 de 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?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 6 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 7 de 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?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 8 de 78

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 9 de 78

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 10 de 78

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 11 de 78

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 12 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 13 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 14 de 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?

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 15 de 78

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 16 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 17 de 78

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 18 de 78

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 19 de 78

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 20 de 78

Selecciona una de las siguientes respuestas posibles:

Explicación

Pregunta 21 de 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.)

Selecciona una o más de las siguientes respuestas posibles:

Explicación

Pregunta 22 de 78

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

Selecciona una o más de las siguientes respuestas posibles:

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?