1z0-071 Oracle Database 12c SQL Questions and Answers

Questions 4

Examine the description of the EMPLOYEES table:

1z0-071 Question 4

NLS_DATE FORMAT is DD-MON-RR.

Which two queries will execute successfully?

Options:

SELECT dept_ id, AVG (MAX(salary)) FROM employees GROUP By dept_id HAVING hire_date> ' O1-JAN-19';

SELECT dept_ id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;

SELECT dept id, MAX (SUM(salary)) FROM employees GROUP BY dept_id;

SELECT dept_ iD, sum(salary) FROM employees WHERE hire_date > '01-JAN-9' GROUP BY dept_id;

SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;

Buy Now

Questions 5

Examine this partial statement:

SELECT ename, sal,comm FROM emp

Now examine this output:

1z0-071 Question 5

WHICH ORDER BY clause will generate the displayed output?

Options:

ORDER BY NVL(enam,0) DESC, ename

ORDER BY NVL(comm,0) ASC NULLS FIRST, ename

ORDER BY NVL(comm,0) ASC NULLS LAST, ename

ORDER BY comm DESC NULLS LAST, ename

Buy Now

Questions 6

Examine the data in the COLORS table:

1z0-071 Question 6

Examine the data in the BRICKS table:

1z0-071 Question 6

Which two queries return all the rows from COLORS?

Options:

1z0-071 Question 6 Option 1

Buy Now

Answer:

A, D

Explanation:

In SQL, to return all rows from one table in a join regardless of whether they match rows in the other table, a LEFT JOIN is used when we want all rows from the left table, or a RIGHT JOIN when we want all rows from the right table. Here’s an explanation for each query provided in the context of the COLORS and BRICKS tables:

A. This query uses a LEFT JOIN and will return all the rows from the COLORS table (c) because it is on the left side of the join. Additionally, it joins the BRICKS table (b) on the condition that the color_rgb_hex_value matches. The condition in the WHERE clause (b.brick_id > 0) will not exclude any COLORS rows because it only filters the rows from the right table (BRICKS) after the join.

B. This query uses a RIGHT JOIN, and while it does return all rows from the COLORS table, it doesn't place it on the left side of the join, which is not what is typically expected from a RIGHT JOIN to return all rows from the right side table.

C. This query is a plain JOIN (also known as an inner join), which only returns rows that have matching values in both tables, so it will not return all the rows from the COLORS table if there is no match in the BRICKS table.

D. This query uses a FULL JOIN, which returns all rows when there is a match in one of the tables. Hence, it will return all the rows from both the COLORS and BRICKS tables. While this does return all rows from the COLORS table, it also includes all rows from the BRICKS table, which may not be desired if the requirement was only to return all rows from COLORS.

E. This query is similar to option A and uses the USING clause to specify the join condition. The USING clause is used when both tables have a column with the same name and is meant to simplify the syntax. However, in this case, the columns do not have the same name in both tables (RGB_HEX_VALUE in COLORS and COLOR_RGB_HEX_VALUE in BRICKS), so this query will not execute successfully.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Joins"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "LEFT OUTER JOIN"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "RIGHT OUTER JOIN"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "FULL OUTER JOIN"

Based on the explanations above, the correct answers that return all rows from the COLORS table are A and D. However, D includes all rows from BRICKS as well, which may not be the intended requirement if the question specifies only rows from COLORS should be returned. Thus, A is the most accurate answer to the question as it adheres to the standard expectation of a LEFT JOIN.

Questions 7

Which three statements are true about a self join?

Options:

It must be an inner join.

It must be an equijoin.

The query must use two different aliases for the table.

The on clause can be used.

The on clause must be used.

It can be an outer join.

Buy Now

Questions 8

Examine the description of the EMPLOYEES table:

1z0-071 Question 8

Which statement will fail?

Options:

SELECT department_id, COUNT (*)

FROM employees

HAVING department_ id <> 90 AND COUNT(*) >= 3

GROUP BY department_id;

SELECT department_id, COUNT (*)

FROM employees

WHERE department_ id <> 90 AND COUNT(*) >= 3

GROUP BY department_id;

SELECT department_id, COUNT(*)

FROM employees

WHERE department_id <> 90 HAVING COUNT(*) >= 3

GROUP BY department_id;

SELECT department_id, COUNT(*)

FROM employees

WHERE department_id <> 90 GROUP BY department_id

HAVING COUNT(*) >= 3;

Buy Now

Questions 9

Which two statements are true about INTERVAL data types

Options:

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.

INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.

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 support yearly intervals.

Buy Now

Questions 10

Which two statements are true regarding the UNION ALL operators?

Options:

NULLS are not ignored during duplicate checking.

Duplicates are eliminated automatically by the UNION ALL operator

The names of columns selected in each SELECT statement must be identical.

The number of columns selected in each SELECT statement must be identical

The output is sorted by the UNION ALL operator.

Buy Now

Questions 11

Examine the description of the PRODUCTS table:

1z0-071 Question 11

Which three queries use valid expressions?

Options:

SELECT produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;

SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;

SELECT ptoduct_id, (expiry_date-delivery_date) * 2 FROM products;

SPLECT product_id, expiry_date * 2 FROM products;

SELEGT product_id, unit_price, unit_price + surcharge FROM products;

SELECT product_id,unit_price || "Discount", unit_price + surcharge-discount FROM products;

Buy Now

Questions 12

You execute these commands:

CREATE TABLE customers (customer id INTEGER, customer name VARCHAR2 (20));

INSERT INTO customers VALUES (1，‘Custmoer1 ‘);

SAVEPOINT post insert;

INSERT INTO customers VALUES (2, ’Customer2 ‘);

SELECTCOUNT (*) FROM customers;

Which two, used independently, can replace so the query retums 1?

Options:

ROLLBACK;

COMMIT;

ROLIBACK TO SAVEPOINT post_ insert;

CONOIT TO SAVEPOINT post_ insert;

ROLLEBACK TO post_ insert;

Buy Now

Questions 13

Which two are true about rollbacks?

Options:

The ROLLBACK statement does not release locks resulting from table updates.

Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back.

A transaction interrupted by a system failure is automatically rolled back.

If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted .

Data consistency is not guaranteed after a rollback.

Buy Now

Questions 14

Viev the Exhibit and examine the structure of the PRODUCT INFORMATION and INVENTORIEStables.

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 where in QUANTITY ON HAND is lessthan five.

Which two SQL statements can accomplish the task? (Choose two)

Options:

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;

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;

Buy Now

Questions 15

Which statement will execute successfully?

Options:

SELECT 1, 2 FROM DUAL

UNION

SELECT 3, 4 FROM DUAL

ORDER BY 1, 2;

SELECT 3 FROM DUAL

UNION

SELECT 4 FROM DUAL

ORDER BY 3 ;

SELECT 1, 2 FROM DUAL

UNION

SELECT 3, 4 FROM DUAL

ORDER BY 3, 4;

SELECT 1 FROM DUAL

UNION

SELECT 2 FROM DUAL

ORDER BY 1, 2;

Buy Now

Questions 16

Examine the description of the transactions table:

1z0-071 Question 16

Which two SQL statements execute successfully?

Options:

SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from transactions;

SELECT customer_id AS 'CUSTOMER-ID',transaction_date AS DATE, amount+100 'DUES' from transactions;

SELECT customer_id CUSTID, transaction_date TRANS_DATE，amount+100 DUES FROM transactions;

SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM transactions;

SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES AMOUNT" FROM transactions;

Buy Now

Questions 17

Which two statements cause changes to the data dictionary?

Options:

DELETE FROM scott. emp;

GRANT UPDATE ON scott. emp TO fin manager;

AITER SESSION set NLs. _DATE FORMAT = 'DD/MM/YYYY';

TRUNCATE TABLE emp:

SELECT * FROM user_ tab._ privs;

Buy Now

Questions 18

Examine this query:

SELECT SUBSTR (SYSDATE,1,5) ‘Result’ FROM DUAL

Which statement is true?

Options:

It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)

It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)

It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)

It executes successfully with an implicit data type conversion

Buy Now

Questions 19

The STORES table has a column START_DATE of data type DATE, containing the datethe row was inserted.

You only want to display details of rows where START_DATEis within the last 25 months.which WHERE clause can be used?

Options:

WHERE TO_NUMBER(start_date - SYSDATE)<=25

WHERE ADD_MONTHS (start date , 25)<= SYSDATE

WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25

WHERE MONTHS_BETWEEN (start_date, SYSDATE)<=25

Buy Now

Questions 20

Which two actions can you perform with object privileges?

Options:

Create roles.

Delete rows from tables in any schema except sys.

Set default and temporary tablespaces for a user.

Create FOREIGN KEY constraints that reference tables in other schemas.

Execute a procedure or function in another schema.

Buy Now

Questions 21

Examine this business rule:

Each student can work on multiple projects and each project can have multiple students.

You must design an Entity Relationship(ER) model for optimal data storage and allow for generating reports in this format:

1z0-071 Question 21

Which two statements are true?

Options:

An associative table must be created with a composite key of STUDENT_ID and PROJRCT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.

PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.

The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.

The ER must have a many to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.

STUDENT ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.

Buy Now

Questions 22

Which two statements are true about Oracle synonyms?

Options:

A synonym can have a synonym.

All private synonym names must be unique in the database.

Any user can create a PUBLIC synonym.

A synonym can be created on an object in a package.

A synonym has an object number.

Buy Now

Questions 23

Which three statements about roles are true?

Options:

Roles are assigned to roles using the ALTER ROLE Statement

A role is a named group of related privileges that can only be assigned to a user

Roles are assigned to users using the ALTER USER statement

A single role can be assigned to multiple users.

A single user can be assigned multiple roles

Privileges are assigned to a role using the ALTER ROLE statement.

Privileges are assigned to a role using the GRANT statement.

Buy Now

Questions 24

Which two statements are true about outer Joins?

Options:

The outer join operator (+) can be used on both sides of the join condition in an outer join.

An outer join is used to retrieve only the rows that do not meet the join condition.

The IN operator cannot be used in a condition that Involves an outer join.

A condition representing an outer join cannot be linked to another condition using the or logical operator.

The outer join operator (+) is used next to the column of the table without the matching rows.

Buy Now

Questions 25

Examine the description of the BRICKS table;

1z0-071 Question 25

Examine the description of the BRICKS_STAGE table;

1z0-071 Question 25

Which two queries execute successfully?

Options:

SELECT shape,color,weight from bricks

MINUS

SELECT * FROM bricks_stage;

SELECT shape,color FROM bricks

MINUS

SELECT WEIGHT,color FROM bricks_stage;

select * from bricks

MINUS

select * from bricks_stage;

SELECT shape,color FROM bricks

MINUS

SELECT color,shape FROM bricks_stage;

SELECT brick_id,shape FROM bricks

MINUS

SELECT WEIGHT,COLOR from bricks_stage;

Buy Now

Questions 26

Which three are key components of an Entity Relationship Model?

Options:

a table

an attribute

a unique identifier

an activity

a relationship

an entity

Buy Now

Questions 27

Examine this SQL statement:

1z0-071 Question 27

Which two are true?

Options:

The subquery is executed before the UPDATE statement is executed.

All existing rows in the ORDERS table are updated.

The subquery is executed for every updated row in the ORDERS table.

The UPDATE statement executes successfully even if the subquery selects multiple rows.

The subquery is not a correlated subquery.

Buy Now

Answer:

A, C

Explanation:

The provided SQL statement is an update statement that involves a subquery which is correlated to the main query.

A. The subquery is executed before the UPDATE statement is executed. (Incorrect)

This statement is not accurate in the context of correlated subqueries. A correlated subquery is one where the subquery depends on values from the outer query. In this case, the subquery is executed once for each row that is potentially updated by the outer UPDATE statement because it references a column from the outer query (o.customer_id).

B. All existing rows in the ORDERS table are updated. (Incorrect)

Without a WHERE clause in the outer UPDATE statement, this would typically be true. However, the correctness of this statement depends on the actual data and presence of matching customer_id values in both tables. If there are rows in the ORDERS table with customer_id values that do not exist in the CUSTOMERS table, those rows will not be updated.

C. The subquery is executed for every updated row in the ORDERS table. (Correct)

Because the subquery is correlated (references o.customer_id from the outer query), it must be executed for each row to be updated in the ORDERS table to get the corresponding cust_last_name from the CUSTOMERS table.

Reference: [Reference: The behavior of correlated subqueries is detailed in the Oracle Database SQL Language Reference, which explains that a correlated subquery is evaluated once for each row processed by the parent statement (Oracle Database SQL Language Reference, 12c Release 1 (12.1))., D. The UPDATE statement executes successfully even if the subquery selects multiple rows. (Incorrect), The subquery inside the SET clause must return exactly one value for each row to be updated. If the subquery returns more than one row for any outer row, the UPDATE statement will result in an error (specifically, an "ORA-01427: single-row subquery returns more than one row" error)., E. The subquery is not a correlated subquery. (Incorrect), This is incorrect because the subquery references the o.customer_id column from the ORDERS table, which makes it a correlated subquery., The correct answers are A and C. The subquery is a correlated subquery because it references the ORDERS table's customer_id in its WHERE clause. It is executed for each row to be updated since it depends on values from the outer query (the o.customer_id). It's important to note that although the statement A is marked incorrect based on the typical behavior of correlated subqueries, in some cases, Oracle's optimizer may unnest the subquery and execute it beforehand if it determines that it's more efficient and the result is the same. However, this doesn't change the nature of the query being a correlated subquery., , ]

Questions 28

You execute these commands:

SQL> DEFINE hiredate = ’01-APR -2011’;

SQL> SELECT employee_id, first_name, salary FROM employees WHERE hire date > &hiredate AND manager_id >&mgr_id;

For which substitution variables will you be prompted?

Options:

none

&hiredate and &mgr_id

only &hiredate

only &mgr_id

Buy Now

Questions 29

Examine the description of the BOOKS_TRANSACTIONS table:

1z0-071 Question 29

Which two WHERE conditions give the same result?

Options:

WHERE borrowed_date = SYSDATE AND (transaction_type ='RM' OR member_id IN ('A101','A102'));

WHERE borrowed_date = SYSDATE AND transaction_type ='RM' OR member_id IN ('A101','A102');

WHERE borrowed_date = SYSDATE AND (transaction_type ='RM' AND member_id='A101' OR member_id ='A102'));

WHERE (borrowed_date = SYSDATE AND transaction_type ='RM') OR member_id IN ('A101','A102');

WHERE borrowed_date = SYSDATE AND (transaction_type ='RM' AND (member_id ='A101' OR member_id ='A102') );

Buy Now

Questions 30

Examine the description of the CUSTOMERS table:

1z0-071 Question 30

Which three statements will do an implicit conversion?

Options:

SELECT * FROM customers WHERE TO_ CHAR (customer_ id) = '0001';

SELECT * FROM customers WHERE customer id = '0001';

SELECT * FROM customers WHERE customer_ id = 0001;

SELECT FROM customers WHERE insert date = '01-JAN-19';

SELECT. FROM customers WHERE insert_ date = DATE *2019-01-01';

SELECT. FRON customers WE TO DATE (Insert _ date) = DATE ‘2019-01-01’;

Buy Now

Questions 31

Which three are true about the CREATE TABLE command?

Options:

It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.

The owner of the table should have space quota available on the tablespace where the table is defined.

It implicitly executes a commit.

It implicitly rolls back any pending transactions.

A user must have the CREATE ANY TABLE privilege to create tables.

The owner of the table must have the UNLIMITED TABLESPACE system privilege.

Buy Now

Questions 32

Which two statements are true about conditional INSERT ALL?

Options:

Each row returned by the subquery can be inserted into only a single target table.

It cannot have an ELSE clause.

The total number of rows inserted is always equal to the number of rows returned by the subquery

A single WHEN condition can be used for multiple INTO clauses.

Each WHEN condition is tested for each row returned by the subquery.

Buy Now

Questions 33

The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully?

Options:

SELECT COUNT(prod_id) FROM sales WHERE quantity_sold>55000 GROUP BY prod_id;

SELECT prod_id FROM sales WHERE quantity_sold> 55000 GROUP BY prod_id HAVING COUNT(*)> 10;

SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold> 55000;

SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*)> 10 GROUP BY COUNT(*)> 10;

SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*)> 10 GROUP BY prod_id HAVING COUNT(*)> 10;

Buy Now

Questions 34

The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number.

Which two queries execute successfully?

Options:

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

SELECT NVL2(cust_credit_limit * .15,'Not Available') FROM customers;

SELECT NVL(cust_credit_limit * .15,'Not Available') FROM customers;

SLECT NVL(TO_CHAR(cust_credit_limit * .15),'Not available') from customers;

SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),'NOT Available') FROM customers;

Buy Now

Questions 35

You need to allow user ANDREW to:

1. Modify the TITLE and ADDRESS columns of your CUSTOMERS table.

2. GRANT tha permission to other users.

Which statement will do this?

Options:

GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;

GRANT UPDATE ON customers. title, customers.address TO andrew WITH GRANT OPTION;

GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;

GRANT UPDATE (title, address) ON customers TO andrew;

GRANT UPDATE ON customers. title, customers.address TO andrew;

GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION:

Buy Now

Questions 36

Examine this list of requirements for a sequence:

1. Name:EMP_SEQ

2. First value returned:1

3. Duplicates are never permitted.

4. Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID COLUMN.

5. Reduce the chances of gaps in the values.

Which two statements will satisfy these requirements?

Options:

CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;

CREATE SEQUENCE emp_seq NOCACHE;

CREATE SEQUENCE emp_seq START WITH 1 CACHE;

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;

CREATE SEQUENCE emp_seq;

Buy Now

Questions 37

Which two are true about scalar subquery expressions?

Options:

You cannot correlate them with a table in the parent statement

You can use them as a default value for a column.

.You must enclose them in parentheses.

They can return at most one row.

They can return two columns.

Buy Now

Questions 38

Which two queries execute successfully?

Options:

SELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;

SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;

SELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL;

select INTERVAL '1' DAY +INTERVAL '1' MONTH FROM DUAL;

SELECT SYSDATE “INTERRVAL '1' DAY FROM DUAL;

Buy Now

Questions 39

Which two are SQL features?

Options:

providing graphical capabilities

providing variable definition capabilities.

providing database transaction control

processing sets of data

providing update capabilities for data in external files

Buy Now

Questions 40

Examine the description of the ENPLOYES table:

1z0-071 Question 40

Which query requires explicit data type conversion?

Options:

SELECT SUBSTR(join date, 1, 2) - 10 FROM employees;

SELECT join_ date + '20' EROM employees;

SELECT join_ date丨丨‘’丨丨 salary FROM employees;

SELECT join _ date FROM employees WHERE join date > *10-02-2018';

SELECT salary + '120.50' FROM employees;

Buy Now

Questions 41

Which three statements are true about dropping and unused columns in an Oracle database?

Options:

A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.

A DROP COLUMN command can be rolled back.

An UNUSED column's space is remained automatically when the block containing that column is next queried.

An UNUSED column's space is remained automatically when the row containing that column is next queried.

Partition key columns cannot be dropped.

A column that is set to NNUSED still counts towards the limit of 1000 columns per table.

Buy Now

Answer:

B, E, F

Explanation:

A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option. (Incorrect)

Primary key columns that are referenced by foreign keys cannot be dropped as it would violate referential integrity. The CASCADE option does not apply in this context.

B. A DROP COLUMN command can be rolled back. (Correct)

Dropping a column from a table is a transactional operation. If it's not committed, it can be rolled back.

Reference: [Reference: Oracle Database SQL Language Reference confirms that the DROP COLUMN operation is transactional and can be rolled back if it is not committed (Oracle Database SQL Language Reference, 12c Release 1 (12.1))., C. An UNUSED column's space is remained automatically when the block containing that column is next queried. (Incorrect), The space occupied by an UNUSED column is not automatically reclaimed in this way., D. An UNUSED column's space is remained automatically when the row containing that column is next queried. (Incorrect), Similar to C, the space for an UNUSED column is not reclaimed automatically upon querying the row., E. Partition key columns cannot be dropped. (Correct), Partition key columns are integral to the partitioning strategy of a table and cannot be dropped., Reference: Oracle Database VLDB and Partitioning Guide details the restrictions on dropping partition key columns (Oracle Database VLDB and Partitioning Guide, 12c Release 1 (12.1))., F. A column that is set to UNUSED still counts towards the limit of 1000 columns per table. (Correct), Marking a column as UNUSED is a logical operation that prevents it from being used in future DML operations, but it still counts towards the column limit of the table until it is actually dropped from the table., Reference: Oracle Database SQL Language Reference indicates that UNUSED columns count toward the column limit (Oracle Database SQL Language Reference, 12c Release 1 (12.1))., ]

Questions 42

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

Options:

A table can have only one primary key but multiple foreign keys.

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.

Primary key and foreign key constraints can be defined at both the column and table level.

Only the primary key can be defined 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.

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.

Buy Now

Questions 43

Which three statements are true about an ORDER BY clause?

Options:

An ORDER BY clause always sorts NULL values last.

An ORDER BY clause can perform a binary sort

An ORDER BY clause can perform a linguistic sort

By default an ORDERBY clause sorts rows in ascending order

An ORDR BY clause will always precede a HAVI NG clause if both are used in the same top-level

Buy Now

Questions 44

Examine this query:

SELECT employee_id, first_name, salary

FROM employees

WHERE hiredate > 61*

Which two methods should yours to prevent prompting for hire date value when this queries executed?

Options:

Execute the SET VERIFY ON command before executing the query.

Execute the SET VERIFY OFF command before executing the query.

Store the query in a script and pass the substitution value to the script when executing it.

Replace 's1' with &1'in the query:

Use the UNDEFINE command before executing the query.

Use the DEFINE command before executing the query

Buy Now

Questions 45

The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8, 2).

Evaluate this SQL statement:

SELECT TO_CHAR(unit_price,'$9,999') FROM PRODUCT_INFORMATION;

Which two statements are true about the output?

Options:

A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024.

A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.

A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236.

A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023.

A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #####

Buy Now

Questions 46

Examine the description of the EMPLOYEES table:

1z0-071 Question 46

Which statement will execute successfully, returning distinct employees with non-null first names?

Options:

SELECT DISTINCT * FROM employees WHERE first_ name IS NOT NULL;

SELECT first_ name, DISTNCT last_ name FROM employees WHERE first_ name IS NOT NULL;

SELECT Distinct * FROM employees WHERE first_ name < > NULL;

SELECT first_ name, DISTINCT last_ name FROM employees WHERE first_ name < > NULL;

Buy Now

Questions 47

Examine the description of the PRODUCT_ STATUS table:

1z0-071 Question 47

The STATUS column contains the values IN STOCK or OUT OF STocK for each row.

Which two queries will execute successfully?

Options:

SELECT prod_id ||q’(‘ s not available)’ ‘CURRENT AVAILABILITY’ FROM

product_ status WHERE status = ‘OUT OF STOCK’

SELECT prod_id ||q”‘ s not available” FROM

product_ status WHERE status = ‘OUT OF STOCK’;

SELECT PROD_ID||q’(‘s not available)’ FROM

product_ status WHERE status = ‘OUT OF STOCK’;

SELECT PROD_ID||q’(‘s not available)’ “CURRENT AVAILABILITY”

FROM product_ status WHERE status = ‘OUT OF STOCK’;

SELECT prod_id q’s not available” from product_ status WHERE status = ‘OUT OF STOCK’;

SELECT prod_id “CURRENT AVAILABILITY”||q’ (‘s not available)’ from product_ status WHERE status

= ‘OUT OF STOCK’;

Buy Now

Questions 48

Examine this query which executes successfully;

Select job,deptno from emp

Union all

Select job,deptno from jobs_history;

What will be the result?

Options:

It will return rows from both select statements after eliminating duplicate rows.

It will return rows common to both select statements.

It will return rows both select statements including duplicate rows.

It will return rows that are not common to both select statements.

Buy Now

Questions 49

You execute this command:

TRUNCATE TABIE depts;

Which two are true?

Options:

A ROLLBACK statement can be used to retrieve the deleted data.

It drops any triggers defined on the table.

It retains the indexes defined on the table.

It retains the integrity constraints defined on the table,

It always retains the space used by the removed rows.

A FLASHBACK TABLE statement can be used to retrieve the deleted data.

Buy Now

Questions 50

Examine this partial command:

1z0-071 Question 50

Which two clauses are required for this command to execute successfully?

Options:

the DEFAULT DIRECTORY clause

the REJECT LIMIT clause

the LOCATION clause

the ACCESS PARAMETERS clause

the access driver TYPE clause

Buy Now

Questions 51

The ORDERS table has a column ORDER_DATE of date type DATE The default display format for a date is DD-MON-RR

Which two WHERE conditions demonstrate the correct usage of conversion functions?

Options:

WHERE ordet_date> TO_CHAR(ADD_MONTHS(SYSDATE, 6),'MON DD YYYY')

WHERE TO_CHAR(order_date,'MON DD YYYY') ='JAN 20 2019';

WHERE order_date> TO_DATE('JUL 10 2018','MON DD YYYY');

WHERE order_date IN (TO_DATE ('Oct 21 2018','MON DD YYYY'), TO_CHAR('Nov 21 2018','MON DD YYYY'));

WHERE order_date> TO_DATE(ADD_MONTHS(SYSDATE,6),'MON DD YYYY');

Buy Now

Questions 52

A session's NLS_DATE_FORMAT is set to DD Mon YYYY .

Which two queries return the value 1 Jan 2019?

Options:

SELECT to_date(' 2019-01-01 ', 'YYYY -MM-DD' ) FROM DUAL;

SELECT DATE '2019-01-01' FROM DUAL ;

SELECT TO_CHAR('2019-01-01') FROM DUAL; 2019-01-01

SELECT '2019-01-01' FROM DUAL ; 2019-01-01

SELECT TO_ DATE('2019-01-01') FROM DUAL;

Buy Now

Questions 53

You create a table by using this command:

CREATE TABLE rate_list (rate NUMBER(6,2));

Which two are true about executing statements?

Options:

INSERT INTO rate_list VALUES (-.9) inserts the value as -.9.

INSERT INTO rate_list VALUES (0.999) produces an error.

INSERT INTO rate_list VALUES (-10) produces an error.

INSERT INTO rate_list VALUES (87654. 556) inserts the value as 87654.6.

INSERT INTO rate_list VALUES (0.551) inserts the value as .55.

INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99.

Buy Now

Questions 54

Which three statements are true about single-row functions?

Options:

The data type returned can be different from the data type of the argument.

They can be nested to any level.

They return a single result row per table.

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.

Buy Now

Questions 55

You execute this command:

TRUNCATE TABLE depts;

Which two are true?

Options:

It retains the indexes defined on the table.

It drops any triggers defined on the table.

A Flashback TABLE statement can be used to retrieve the deleted data.

It retains the integrity constraints defined on the table.

A ROLLBACK statement can be used to retrieve the deleted data.

It always retains the space used by the removed rows

Buy Now

Questions 56

Which two statements are true about a full outer join?

Options:

It includes rows that are returned by an inner join.

The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.

It includes rows that are returned by a Cartesian product.

It returns matched and unmatched rows from both tables being joined.

It returns only unmatched rows from both tables being joined.

Buy Now

Questions 57

Examine the description of the PROMTIONS table:

1z0-071 Question 57

You want to display the unique promotion costs in each promotion category.

Which two queries can be used?

Options:

SELECT promo_cost, | pxomo_category FROM promotions ORDER BY 1;

SELECT promo_category, DISTINCT promo_cost PROM promotions ORDER BY 2:

SELECT DISTINCT promo_category ||'has’|| promo_cost AS COSTS FROM promotions ORDER BY 1;

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

SELECT DISTINCT promo_cost ||' in' II DISTINCT promo_category FROM promotions ORDER BY 1;

Buy Now

Questions 58

Which two statements are true about the ORDER BY clause?

Options:

Numeric values are displayed in descending order if they have decimal positions.

Only columns that are specified in the SELECT list can be used in the ORDER BY clause.

In a character sort, the values are case-sensitive.

Column aliases can be used in the ORDER BY clause.

NULLS are not included in the sort operation.

Buy Now

Questions 59

Examine this partial query:

SELECT ch.channel_type, t.month, co.country_code, SUM(s.amount_sold) SALES

FROM sales s, times t, channels ch, countries co

WHERE s.time_ id = t.time id

AND s.country_ id = co. country id

AND s. channel id = ch.channel id

AND ch.channel type IN ('Direct Sales', 'Internet')

AND t.month IN ('2000-09', '2000-10')

AND co.country code IN ('GB', 'US')

Examine this output:

1z0-071 Question 59

Which GROUP BY clause must be added so the query returns the results shown?

Options:

GROUP BY ch.channel_type, t.month, co.country code;

GROUP BY ch.channel_type,ROLLUP (t month, co. country_ code) ;

GROUP BY CUBE (ch. channel_ type, t .month, co. country code);

GROUP BYch. channel_ type, t.month,ROLIUP (co. country_ code) ;

Buy Now

Questions 60

Which three actions can you perform on an existing table containing date?

Options:

Add a new column as the table's first column.

Define a default value that is automatically inserted into a column containing nulls.

Add a new NOT NULL Column with a DEFAULT value.

Change a DATE Column containing data to a NUMBER data type.

Increase the width of a numeric column.

Change the default value of a column.

Buy Now

Questions 61

Which two statements are true regarding a SAVEPOINT?

Options:

Rolling back to a SAVEPOINT can undo a CREATE INDEX statement.

Only one SAVEPOINT may be issued in a transaction.

A SAVEPOINT does not issue a COMMIT

Rolling back to a SAVEPOINT can undo a TRUNCATE statement.

Rolling back to a SAVEPOINT can undo a DELETE statement

Buy Now

Questions 62

Which three statements are true about views in an Oracle database?

Options:

A SELECT statement cannot contain a where clause when querying a view containing a WHERE clause in its defining query

Rows inserted into a table using a view are retained in the table if the view is dropped

Views can join tables only if they belong to the same schema.

Views have no segment.

Views have no object number.

A view can be created that refers to a non-existent table in its defining query.

Buy Now

Questions 63

Examine the BRICKS table:

1z0-071 Question 63

You write this query:

SELECT

FROM bricks b1 CROSS JOIN bricks b2

WHERE b1. Weight < b2. Weight:

How many rows will the query return?

Options:

Buy Now

Questions 64

Which two are true about the MERGE statement?

Options:

The WHEN NOT MATCHED clause can be used to specify the deletions to be performed.

The WHEN NOT MATCHED clause can be used to specify the inserts to be performed.

The WHEN MATCHED clause can be used to specify the inserts to be performed.

The WHEN NOT MATCHED clause can be used to specify the updates to be performed.

The WHEN MATCHED clause can be used to specify the updates to be performed.

Buy Now

Questions 65

Which two statements are true about * TABLES views?

Options:

You must have SELECT privileges on a table to view it in ALL TABLES.

You must have SELECT privileges on a table to view it in DBA TABLES.

USER TABLES displays all tables owned by the current user.

All TABLES displays all tables owned by the current user.

You must have SELECT privileges on a table to view it in USER TABLES.

All users can query DBA TABLES successfully.

Buy Now

Questions 66

Examine the description of the ORDERS table:

1z0-071 Question 66

Which three statements execute successfully?

Options:

(SELECT * FROM orders

UNION ALL

SELECT* FROM invoices) ORDER BY order _id;

SELECE order _id, order _ date FRON orders

LNTERSECT

SELECT invoice_ id, invoice_ id, order_ date FROM orders

SELECT order_ id, invoice_ data order_ date FROM orders

MINUS

SELECT invoice_ id, invoice_ data FROM invoices ORDER BY invoice_ id;

SELECT * FROM orders ORDER BY order_ id

INTERSEOT

SELECT * FROM invoices ORDER BY invoice_ id;

SELECT order_ id, order_ data FROM orders

UNION ALL

SELECT invoice_ id, invoice_ data FROM invoices ORDER BY order_ id;

SELECT * FROM orders

MINUS

SELECT * FROM INVOICES ORDER BY 1

SELECT * FROM orders ORDER BY order_ id

UNION

SELECT * FROM invoices;

Buy Now

Questions 67

Examine the description of the PRODUCT_INFORMATION table:

1z0-071 Question 67

Options:

SELECT (COUNT(list_price) FROM Product_intormation WHERE list_price=NULL;

SELECT count(nvl( list_price,0)) FROM product_information WHERE list_price is null;

SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price is null.

BELECT COUNT(list_price) FROM product_information where list_price is NULL;

Buy Now

Questions 68

Choose two

Examine the description of the PRODUCT DETALS table:

1z0-071 Question 68

Options:

PRODUCT_ID can be assigned the PEIMARY KEY constraint.

EXPIRY_DATE cannot be used in arithmetic expressions.

EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it

PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it

PRODUCT_PRICE contains the value zero by default if no value is assigned to it.

PRODUCT_NAME cannot contain duplicate values.

Buy Now

Answer:

A, D

Explanation:

A. PRODUCT_ID can be assigned the PRIMARY KEY constraint.

In Oracle Database 12c, a PRIMARY KEY constraint is a combination of a NOT NULL constraint and a unique constraint. It ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table and not null. Given the PRODUCT_ID is marked as NOT NULL, it is a candidate for being a primary key because we can assume that it is intended to uniquely identify each product in the table.

Reference: [Reference: Oracle 12c documentation states that a column defined with the NOT NULL constraint can be used as a primary key, provided the values in the column are also unique (Oracle Database SQL Language Reference, 12c Release 1 (12.1))., B. EXPIRY_DATE cannot be used in arithmetic expressions. (Incorrect), This statement is not necessarily true. Dates in Oracle can be used in arithmetic expressions, typically to add or subtract days from a date., C. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it. (Incorrect), Unless explicitly specified, a date column does not default to SYSDATE. A default value must be set using the DEFAULT clause during the table creation or altered later., D. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it., This is correct. In Oracle, if a numeric column like PRODUCT_PRICE has a NULL value (meaning no value stored in it), it can still be used in an arithmetic expression. In such expressions, NULL is typically treated as a zero, but the result of any arithmetic with NULL is also NULL., Reference: Oracle 12c SQL Language Reference indicates that if you include a numeric column with NULL in an arithmetic expression, the outcome will be NULL, meaning that the operation considers the NULL but does not necessarily treat it as zero (Oracle Database SQL Language Reference, 12c Release 1 (12.1))., E. PRODUCT_PRICE contains the value zero by default if no value is assigned to it. (Incorrect), Unless a default value is explicitly specified during the table creation or altered later, a numeric column like PRODUCT_PRICE does not automatically have a default value of zero., F. PRODUCT_NAME cannot contain duplicate values. (Incorrect), There is no constraint indicated that would prevent PRODUCT_NAME from containing duplicate values. Without a UNIQUE or PRIMARY KEY constraint, a column can contain duplicates., The correct answers are A and D. PRODUCT_ID can be the primary key because it's specified as NOT NULL, thus it can uniquely identify each row in the table. PRODUCT_PRICE can be used in an arithmetic expression with the understanding that if it's NULL, the result of the expression would be NULL as well., ]

Questions 69

Which two queries execute successfully?

Options:

SELECT prod_id, exp_date FROM products

UNION ALL

SELECT prod_id, NULL FROM new_products;

SELECT prod_id, prod_name FROM products

INTERSECT

SELECT 100, prod_name FROM newproducts;

SELECT * FROM products

UNION

SELECT * FROM new_products;

SELECT k FROM products

MINUS

SELECT prod_id FROM new_products;

SELECT prod_id FROM products

UNION ALL

SELECT prod_id, prod_name FROM new_products;

Buy Now

Questions 70

BOOK_SEQ is an existing sequence in your schema.

Which two CREATE TABLE commands are valid?

Options:

CREATE TABLE bookings (

bk_id NUMBER(4) NOT NULL PRIMARY KEY,

start_date DATE NOT NULL,

end_date DATE DEFAULT SYSDATE);

CREATE TABLE bookings (

bk_id NUMBER(4) NOT NULL DEFAULT book_seq.CURRVAL,

start_date DATE NOT NULL,

end_date DATE DEFAULT SYSDATE);

CREATE TABLE bookings (

bk_id NUMBER(4) DEFAULT book_seq.CURRVAL,

start_date DATE DEFAULT SYSDATE,

end_date DATE DEFAULT start date);

CREATE TABLE bookings ( bk_id NUMBER(4),

start_date DATE DEFAULT SYSDATE,

end_date DATE DEFAULT (end_date >= start_date));

CREATE TABLE bookings (

bk_id NUMBER(4) DEFAULT book_seq.NEXTVAL PRIMARY KEY,

start_date DATE DEFAULT SYSDATE,

end_date DATE DEFAULT SYSDATE NOT NULL);

Buy Now

Questions 71

Examine the description of EMPLOYEES table:

Which three queries return all rows for which SALARY+COMMISSION is greate than 20000?

Options:

SELECT * FROM employees WHERE salary+NULLF(commission,0)>=20000;

SELECT * FROM employees WHERE salary+NVL2(commission,commission,0)>=20000;

SELECT * FROM employees WHERE NVL2(salary)+commission,salary+commission,

SELECT * FROM employees WHERE salary+NVL(commission,0)>=20000;

SELECT * FROM employees WHERE NVL(salary+commission,0)>=20000;

SELECT * FROM employees WHERE NVL(salary+commission,0)>==20000;

Buy Now

Questions 72

Which two are true about constraints?

Options:

Constraints are enforced only during INSERT operations.

A column with a foreign key constraint can never contain a null value.

All constraints can be defined at the table or column level.

A constraint can be disabled even if the constrained column contains data.

A column with a UNIQUE constraint can contain a NULL value.

Buy Now

Questions 73

Examine the data in the COLORS table:

1z0-071 Question 73

Examine the data in the BRICKS table:

1z0-071 Question 73

Which two queries return all the rows from COLORS?

Options:

SELECT.

FROM bricks b

RIGHT JOIN colors c

ON b. color _rgb_ hex_ value = c. rgb hex_ value;

SELECT

EROM colors C

LEFT JOIN bricks 上

USING (rgb _ hex_ value) ;

SELECT

FROM bricks b

FULL JOIN colors C

ON b. color rgb _ hex_ value = c. rgb _hex_ value;

SELECT *

EROM bricks | b

JOIN colors C

ON b. color_ rgb_ hex_ value =c. rgb _hex value;

SELECT

EROM colors C

LEET JOIN bricks b

ON b. color_ rgb_ hex value = c. rgb. hex.

value

WHERE b. brick_ id > 0;

Buy Now

Questions 74

You have been asked 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 loan 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?

Options:

TIMESTAMP WITH TIMEZONE

TIMESTAMP

TIMESTAMP WITH LOCAL TIMEZONE

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

Buy Now

Questions 75

You want to return the current date and time from the user session, with a data type of TIMESTAMP WITH TIME ZONE.

Which function will do this?

Options:

CURRENT DATE

CURRENT_ TIMESTAMP

SYSDATE

LOCALTIMESTAMP

Buy Now

Questions 76

Which three statements are true about multiple row subqueries?

Options:

They can contain HAVING clauses.

Two or more values are always returned from the subquery.

They cannot contain subquery.

They can return multiple columns.

They can contain GROUP BY clauses.

Buy Now

Questions 77

Examine these SQL statements which execute successfully:

1z0-071 Question 77

Which two statements are true after execution?

Options:

The primary key constraint will be enabled and DEFERRED.

The primary key constraint will be enabled and IMMEDIATE.

The foreign key constraint will be disabled.

The foreign key constraint will be enabled and DEFERRED.

The foreign key constraint will be enabled and IMMEDIATE.

Buy Now

Questions 78

Examine these statements and results：

SQL> SELECT COUNT(*) FROM emp

COUNT(*)

---------------------

sQL> CREATE GLOBAL TEMPORARY TABLE t emp As SELECT * FROM emp;

Table created

SQL> INSERT INTo temp SELECT * FROM emp;

14 rows created

SQL> COMMIT:

Commit complete*

SQL> INSERT INTo temp SELECT * EROM emp;

14. rows created

SQL> SELECT COUNT(*) FROM t emp

How many rows are retrieved by the last query?

Options:

Buy Now

Questions 79

Examine the description of the EMPLOYEES table:

1z0-071 Question 79

The session time zone is the same as the database server

Which two statements will list only the employees who have been working with the company for more than five years?

Options:

SELECT employee_ name FROM employees WHERE (SYSDATE – hire_ data) / 365>5

SELECT employee_ name FROM employees WHERE (SYSTIMESTAMP – hire_ data) / 365>

SELECT employee_ name FROM employees WHERE (CUARENT_ DATE – hire_ data / 365>5

SELECT employee_ name FROM employees WHERE (SYSNAYW – hire_ data / 12> 3

SELECT employee_ name FROM employees WHERE (CUNACV_ DATE – hire_ data / 12> 3

Buy Now

Questions 80

Examine the description of the BOOKS_TRANSACTIONS table:

1z0-071 Question 80

Examine this partial SQL statement:

SELECT * FROM books_transactions

Which two WHERE conditions give the same result?

Options:

WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101','A102');

WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102');

WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));

WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');

Buy Now

Answer:

A, D

Explanation:

When writing SQL statements with multiple conditions in the WHERE clause, it's important to understand how logical operators like AND and OR work. These operators are used to filter records based on more than one condition:

The AND operator displays a record if all the conditions separated by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.

The precedence of these operators is also important: AND operations are always evaluated before OR operations unless parentheses are used to explicitly define the order of operations. Therefore, conditions enclosed in parentheses are evaluated first as per the standard SQL operator precedence.

Given this understanding, let's evaluate the options:

Option A uses parentheses to group the borrowed_date and transaction_type conditions together, ensuring these are evaluated first before the OR operator. This means the query will return records that meet both of these conditions or records with member_id 'A101' or 'A102', regardless of other conditions.
Option D does not use parentheses around the borrowed_date and transaction_type conditions but does use them to group the member_id conditions. Since the AND operator has higher precedence than the OR, this query will first evaluate the borrowed_date and transaction_type conditions, then evaluate the grouped member_id conditions. The outcome is records that have a borrowed_date of SYSDATE, a transaction_type of 'RM', and a member_id of either 'A101' or 'A102'.

The results of Options A and D are effectively the same, even though the use of parentheses is different. This is because in both cases, the evaluation of the borrowed_date and transaction_type conditions will be performed first due to their grouping by parentheses in Option A and the precedence of AND over OR in Option D. Therefore, they will both return all records where borrowed_date equals SYSDATE and transaction_type equals 'RM', plus any records where member_id is either 'A101' or 'A102'.

For further details on SQL operator precedence and logical operators, you can refer to Oracle Database SQL Language Reference 12c documentation, specifically the sections on conditional expressions.

Questions 81

Which two are true about the NVL, NVL2, and COALESCE functions?

Options:

The first expression in NVL2 is never returned.

NVL2 can have any number of expressions in the list.

COALESCE stops evaluating the list of expressions when it finds the first null value.

COALESCE stops evaluating the list of expressions when it finds the first non-null value.

NVL must have expressions of the same data type.

NVL can have any number of expressions in the list.

Buy Now

Questions 82

Which three are true about scalar subquery expressions?

Options:

A scalar subquery expression that returns zero rows evaluates to zoro

They cannot be used in the values clause of an insert statement*

They can be nested.

A scalar subquery expression that returns zero rows evaluates to null.

They cannot be used in group by clauses.

They can be used as default values for columns in a create table statement.

Buy Now

Questions 83

An Oracle database server session has an uncommitted transaction in progress which updated 5000 rows

in a table.

In which three situations does the transact ion complete thereby committing the updates?

Options:

When the session logs out is successfully

When a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT

When a CREATE INDEX statement is executed successfully in same session

When a COMMIT statement is issued by the same user from another session in the same database instance

When a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

When a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user, then issues a COMMIT

Buy Now

Questions 84

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

Options:

Create a directory object for an external table.

Read data from an external table and load it into a table in the database.

Query data from an external table.

Create a directory object for a flat file.

Execute DML statements on an external table.

Read data from a table in the database and insert it into an external table.

Buy Now

Questions 85

You own table DEPARTMENTS, referenced by views, indexes, and synonyms.

Examine this command which executes successfully:

DROP TABLE departments PURGE;

Which three statements are true?

Options:

Neither can it be rolled back nor can the DEPARTMENTS table be recovered.

It will remove all views that are based on the DEPARTMENTS table.

It will delete all rows from the DEPARTMENTS table, but retain the empty table.

It will remove the DE PARTMENTS table from the database.

It will remove all synonyms for the DEPARTMENTS table.

It will drop all indexes on the DEPARTMENTS table.

Buy Now

Questions 86

Which two statements are true about the data dictionary?

Options:

Views with the prefix dba_ display only metadata for objects in the SYS schema.

Views with the prefix all_ display metadata for objects to which the current user has access.

The data dictionary is accessible when the database is closed.

Views with the prefix all_, dba_ and useb_ are not all available for every type of metadata.

The data dictionary does not store metadata in tables.

Buy Now

Questions 87

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

The columns ORDER_MODE and ORDER TOTAL have the default values'direct “and respectively.

Which two INSERT statements are valid? (Choose two.)

Options:

INSERT INTO (SELECT order_id, order date, customer_id FROM orders) VALUES (1, ‘09-mar-2007“，101);

INSERT INTO orders (order_id, order_date, order mode,customer_id, order_total) VALUES (1, TO_DATE (NULL),‘online‘,101, NULL) ;

INSERT INTO orders VALUES (1, ‘09-mar-2007’, ‘online’,’ ’,1000);

INSERT INTO orders (order id, order_date, order mode, order_total)VALUES (1，‘10-mar-2007’,’online’, 1000)

INSERT INTO orders VALUES(‘09-mar-2007’，DEFAULT,101, DEFALLT);

Buy Now

Questions 88

Examine the data in the CUST_NAME column of the CUSTOMERS table:

CUST_NAME

---------------------

Renske Ladwig

Jason Mallin

Samuel McCain

Allan MCEwen

Irene Mikkilineni

Julia Nayer

You want to display the CUST_NAME values where the last name starts with Mc or MC.

Which two WHERE clauses give the required result?

Options:

WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1)) LIKE UPPER('MC%')

WHERE SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1) LIKE 'Mc%’ OR 'MC%’

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1)) IN (‘MC%’,’Mc%’)

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ') + 1)) LIKE ‘Mc%’

WHERE SUBSTR(cust_name, INSTR(cust_name,’ ‘) + 1) LIKE ‘Mc%’

Buy Now

Questions 89

1z0-071 Question 89

Which two queries only return CUBE?

Options:

SELECT shape FROM bricks JOIN boxes ON weight >= min_weight AND weight < max_weight;

SELECT shape FROM bricks JOIN boxes ON weight > min_weight;

SELECT shape FROM bricks JOIN boxes ON weight BETWEEN min_weight AND max_weight;

SELECT shape FROM bricks JOIN boxes ON weight < max_weight;

SELECT shape FROM bricks JOIN boxes ON NOT (weight > max_weight);

Buy Now

Questions 90

Which two statements are true about external tables?

Options:

Indexes can be created on them.

You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.

DML statements cannot be used on them.

Their data can be retrieved by using only SQL or PL/SQL.

Their metadata and actual data are both stored outside the database.

Buy Now

Questions 91

Which three statements are true about indexes and their administration in an Oracle database?

Options:

An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.

An index can be created as part of a CREATE TABLE statement.

A DROP INDEX statement always prevents updates to the table during the drop operation

A UNIQUE and non-unique index can be created on the same table column

A descending index is a type of function-based index

If a query filters on an indexed column then it will always be used during execution of the query

Buy Now

Questions 92

Which three statements are true about performing DML operations on a view with no INSTEAD OF triggers defined?

Options:

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

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

Views cannot be used to add or modify rows in an underlying table If the defining query of the view contains the DISTINCT keyword.

Buy Now

Questions 93

Examine this query:

SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL; What is the result?

Options:

160

150

200

100

Buy Now

Questions 94

Examine the description of the CUSTOMERS table:

1z0-071 Question 94

You need to display last names and credit limits of all customers whose last name starts with A or B In lower or upper case, and whose credit limit is below 1000.

Examine this partial query:

SELECT cust_last_nare, cust_credit_limit FROM customers

Which two WHERE conditions give the required result?

Options:

WHERE UPPER(cust_last_name) IN ('A%', 'B%') AND cust_credit_limit < 1000:

WHERE (INITCAP(cust_last_name) LIKE ‘A%' OR ITITCAP(cust_last_name) LIKE ‘B%') AND cust_credit_limit < 1000

WHERE UPPER(cust_last_name) BETWEEN UPPER('A%' AND 'B%’) AND ROUND(cust_credit_limit) < 1000;

WHERE (UPPER(cust_last_name) LIKE 'A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND ROUND(cust_credit_limit) < 1000;

WHERE (UPPER(cust_last_name) like INITCAP ('A') OR UPPER(cust_last_name) like INITCAP('B')) AND ROUND(cust_credit_limit) < ROUND(1000) ;

Buy Now

Questions 95

Examine this query which executes successfully:

SELECT job, deptno FROM emp

UNION ALL

SELECT job, deptno FROM jobs_ history;

What will be the result?

Options:

It will return rows common to both SELECT statements.

It will return rows from both SELECT statements after eliminating duplicate rows.

It will return rows that are not common to both SELECT statements.

It will return rows from both SELECT statements including duplicate rows.

Buy Now

Questions 96

Examine the description of the PROMOTIONS TABLE:

You want to display the unique is promotion costs in each promotion category.

Which two queries can be used?

Options:

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

SELECT promo_cost, promo_category FROM promotions ORDER BY 1

SELECT promo_category, DISTINCT promo_cost FROM promotiong ORDER BY 2:

select DISTINCT promo_categoryIl ‘has’||promol_cost as COSTS FROM promotions ORDER BY 1:

SELECT DISTINCT promo_cost ||’in’IIDISTINCT promo_category promotions ORDER BY1:

Buy Now

Questions 97

Which two statements will return the names of the three employees with the lowest salaries?

Options:

SELECT last_ name, salary

FROM employees

FETCH FIRST 3 ROWS ONLY

ORDER BY salary;

SELECT last name, salary

FROM employees

ORDER BY salary

FETCE FIRST 3 RONS ONLY;

SELECT last_ name, salary

FBOM employees

WEERE

ORDER BY SELECT

ROINUM <= 3

salary FROM

employees);

SELECT last_ name, salary

FROM

(SELECT” FROM employees ORDER BY salary)

WHERE ROWNUM <=3

SELECT last_ name, salary

FROM employees

WHERE ROWNUM <=3

ORDER BY salary

Buy Now

Exam Code: 1z0-071

Exam Name: Oracle Database 12c SQL

Last Update: May 1, 2024

Questions: 324

PDF + Testing Engine

$56 ~~$159.99~~

Testing Engine

$42 ~~$119.99~~

PDF (Q&A)

$35 ~~$99.99~~

Labour Day - Special Limited Time 65% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: dpm65

dumpsmate logo

Contact Email:

Hot Vendors

1z0-071 Oracle Database 12c SQL Questions and Answers

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation:

Options:

Answer:

Explanation: