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

Questions 4

Which two statements are true about single row functions?

Options:

CONCAT: can be used to combine any number of values

MOD: returns the quotient of a division operation

CEIL: can be used for positive and negative numbers

FLOOR: returns the smallest integer greater than or equal to a specified number

TRUNC: can be used with NUMBER and DATE values

Buy Now

Questions 5

Which three statements are true about performing Data Manipulation Language (DML) operations on a view In an Oracle Database?

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.

Views cannot be used to query rows from an underlying table if the table has a PRIPOARY 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.

Views cannot be used to add on modify rows in an underlying table if the defining query of the view contains aggregating functions.

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.

Buy Now

Questions 6

Examine the BRICKS table:

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 7

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 8

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 9

which is true about the round,truncate and mod functions>?

Options:

ROUND(MOD(25,3),-1) IS INVALID

ROUND(MOD(25,3),-1) AND TRUNC(MOD(25,3),-1) ARE BOTH VALID AND GIVE THE SAME RESULT.

ROUND(MOD(25,3),-1) AND TRUNC(MOD(25,3),-1) ARE BOTH VALID AND GIVE THE DIFFERENT RESULTS.

TRUNC(MOD(25,3),-1) IS INVALID.

Buy Now

Questions 10

Which three queries use valid expressions?

Options:

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

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

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

SELECT product_id, expiry_date * 2 from products;

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

SELECT product_id, unit_price, unit_price + surcharge FROM products;

Buy Now

Questions 11

Examine these statements:

CREATE TABLE dept (

deptno NUMBER PRIMARY KEY,

diname VARCHAR2(10) ,

mgr NUMBER ,

CONSTRAINT dept_fkey FOREIGN KEY(mgr) REFERENCES emp (empno));

CREATE TABLE emp (

Empno NUMBER PRIMARY KEY,

Ename VARCHAR2 (10) ,

deptno NUMBER,

CONSTRAINT emp_fkey FOREIGN KEY (deptno) REFERENCES dept (deptno) DISABLE);

ALTER TABLE emp MODIFY CONSTRAINT emp_fkey ENABLE;

Which two are true?

Options:

The MGR column in the DEPT table will not be able to contain NULL values.

The CREATE TABLE EMP statement must precede the CREATE TABLE DEPT statement for all threestatements to execute successfully.

Both foreign key constraint definitions must be removed from the CREATE TABLE statements, andbe added with ALTER TABLE statements once both tables are created, for the two CREATE TABLEstatements to

execute successfully in the order shown.

The DEFT FKEY constraint definition must be removed from the CREATE TABLE DEF statement.and be added with an AITER TABLE statement once both tables are created, for the two CREATE TABLE statements

to execute successfully in the order shown.

The Deptno column in the emp table will be able to contain nulls values.

All three statements execute successfully in the order shown

Buy Now

Questions 12

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 13

Examine the description of the CUSTOMERS table:

Which two SELECT statements will return these results:

CUSTOMER_ NAME

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

Mandy

Mary

Options:

SELECT customer_ name FROM customers WHERE customer_ name LIKE ' % a % ’ ;

SELECT customer_ name FROM customers WHERE customer name LIKE 'Ma%' ;

SELECT customer_ name FROM customers WHERE customer_ name='*Ma*';

SELECT customer_ name FROM customers WHERE UPPER (customer_ name ) LIKE 'MA*. ;

SELECT customer_ name FROM customers WHERE customer name LIKE 'Ma*';

SELECT customer_ name FROM customers WHERE UPPER (customer name) LIKE 'MA&';

SELECT customer_ name FROM customers WHERE customer_ name KIKE .*Ma*';

Buy Now

Questions 14

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 15

Which statement fails to execute successfully?

Options:

Option A

Option B

Option C

Option D

Buy Now

Questions 16

Examine this description of the PRODUCTS table:

Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode. Which three commands execute successfully on PRODUCTS?

Options:

ALTER TAELE products DROP COLUMN expiry_date;

CREATE INDEX price_idx on products (price);

ALTER TABLE products SET UNUSED(expiry_date);

TRUNCATE TABLE products;

ALTER TABLE products DROP UNUSED COLUMNS

DROP TABLE products

Buy Now

Questions 17

Which two are true about the data dictionary?

Options:

Base tables in the data dictionary have the prefix DBA_.

All user actions are recorded in the data dictionary.

The data dictionary is constantly updated to reflect changes to database objects, permissions, and data.

All users have permissions to access all information in the data dictionary by default

The SYS user owns all base tables and user-accessible views in the data dictionary.

Buy Now

Questions 18

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 19

Examine the description of the PRODUCT_INFORMATION table:

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 20

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 21

.No user-defined locks are used in your database.

Which three are true about Transaction Control Language (TCL)?

Options:

COMMIT erases all the transaction’s savepoints and releases its locks.

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 release its locks.

ROLLBACK to SAVEPOTNT undoes the transaction's changes made since the named savepoint and then ends the transaction.

ROLLBACK without the TO SAVEPOINT clause undoes alt the transaction's changes, releases its locks, and erases all its savepoints.

ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.

Buy Now

Questions 22

Which two statements are true about the order by clause when used with a sql statement containing a set operator such as union?

Options:

column positions must be used in the order by clause.

The first column in the first select of the compound query with the union operator is used by default to sort output in the absence of an order by clause.

Each select statement in the compound query must have its own order by clause.

only column names from the first select statement in the compound query are recognized.

Each select statement in the compound query can have its own order by clause.

Buy Now

Questions 23

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 24

Which statement is true regarding the SESSION_PRIVS dictionary view?

Options:

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 system privileges available in the user session.

It contains the current object privileges available in the user session.

Buy Now

Questions 25

Which three statements are true about built-in data types?

Options:

A VARCHAR2 blank-pads column values only if the data stored is non-numeric and contains no special characters.

The default length for a CHAR column is always one character.

A VARCHAR2 column definition does not require the length to be specified.

A BLOB stores unstructured binary data within the database.

A CHAR column definition does not require the length to be specified.

A BFILE stores unstructured binary data in operating system files.

Buy Now

Questions 26

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 27

Which two statements are true about a self join?

Options:

The join key column must have an index.

It can be a left outer join.

It must be a full outer join.

It can be an inner join.

It must be an equijoin.

Buy Now

Questions 28

Examine this statement:

SELECT1 AS id,‘ John’ AS first_name, NULL AS commission FROM dual

INTERSECT

SELECT 1,’John’ null FROM dual ORDER BY 3;

What is returned upon execution?[

Options:

2 rows

0 rows

An error

1 ROW

Buy Now

Questions 29

Which three statements are true about single row functions?

Options:

They can be used only in the where clause of a select statement.

They can accept only one argument.

They return a single result row per table.

The argument can be a column name, variable, literal or an expression.

They can be nested to any level.

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

Buy Now

Questions 30

Examine this description of the EMP table:

You execute this query:

SELECT deptno AS "departments", SUM (sal) AS "salary"

FROM emp

GROUP | BY 1

HAVING SUM (sal)> 3 000;

What is the result?

Options:

only departments where the total salary is greater than 3000, returned in no particular order

all departments and a sum of the salaries of employees with a salary greater than 3000

an error

only departments where the total salary is greater than 3000, ordered by department

Buy Now

Questions 31

Which two queries return the string Hello! we're ready?

Options:

SELECT q'! Hello! We're ready! 'FROM DUAL;

SELECT "Hello! We're ready "FROM |DUAL;

SELECT q'[Hello! We're ready]'FROM DUAL;

SELECT 'Hello! we\ re ready' ESCAPE'N'FROMDUAL:

SELECT 'Hello! We're ready' FROM DUAL;

Buy Now

Questions 32

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

Options:

ALTER

REFERENCES

UPDATE

SELECT

INDEX

INSERT

DELETE

Buy Now

Questions 33

Examine the description of the PROMTIONS table:

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 34

In the PROMOTIONS table, the PROMO_BEGTN_DATE column is of data type DATE and the default date format is DD-MON-RR.

Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query?

Options:

TO_NUMBER(PROMO_BEGIN_DATE)-5 will return number

TO_DATE(PROMO_BEGIN_DATE * 5) will return a date

PROMO_BEGIN_DATE-SYSDATE will return a number.

PROMO_BEGIN_DATE-5 will return a date.

PROMO_BEGIN_DATE-SYSDATE will return an error.

Buy Now

Questions 35

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 36

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 37

Examine the description of the CUSTOMERS table:

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 38

Examine this SQL statement:

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

Which three are true about privileges and roles?

Options:

A role is owned by the user who created it.

System privileges always set privileges for an entire database.

All roles are owned by the SYS schema.

A role can contain a combination of several privileges and roles.

A user has all object privileges for every object in their schema by default.

PUBLIC can be revoked from a user.

PUBLIC acts as a default role granted to every user in a database

Buy Now

Questions 40

Which statements is true about using functions in WHERE and HAVING?

Options:

using single-row functions in the WHERE clause requires a subquery

using single-row functions in the HAVING clause requires a subquery

using aggregate functions in the WHERE clause requires a subquery

using aggregate functions in the HAVING clause requires a subquery

Buy Now

Questions 41

Examine the description of the PRODUCTS table:

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 42

Which two true about a sql statement using SET operations such as UNION?

Options:

The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query

The data type of each column retuned by the second query must exactly match the data type of the corresponding column returned by the first query

The number, but not names, of columns must be identical for all SELECT statements in the query

The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query

The names and number of columns must be identical for all SELECT statements in the query.

Buy Now

Questions 43

Examine the data in the ENPLOYEES table:

Which statement will compute the total annual compensation tor each employee？

Options:

SECECT last_namo, (menthy_salary + monthly_commission_pct) * 12 AS annual_comp

FROM employees;

SELCECT last_namo, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp

FROM employees

SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * NVL

(monthly_commission_pct, 0)) AS annual_comp FROM employees

SELCECT last_namo, (monthly_salary * 12) + (menthy_salary * 12 * monthly_commission_pct)

AS annual_comp FROM employees

Buy Now

Questions 44

Which two are true about unused columns?

Options:

The DESCRIBE command displays unused columns

A primary key column cannot be set to unused.

A query can return data from unused columns, but no DML is possible on those columns.

Once a column has been set to unused, a new column with the same name can be added to the table.

A foreign key column cannot be set to unused.

Unused columns retain their data until they are dropped

Buy Now

Questions 45

Which two are true about creating tables in an Oracle database?

Options:

A create table statement can specify the maximum number of rows the table will contain.

The same table name can be used for tables in different schemas.

A system privilege is required.

Creating an external table will automatically create a file using the specified directory and file name.

A primary key constraint is manadatory.

Buy Now

Questions 46

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 47

Which three statements about roles are true?

Options:

Roles are assigned to roles using the ALTER ROLE statement.

A single user can be assigned multiple roles.

Roles are assigned to users using the ALTER USER statement.

A single role can be assigned to multiple users.

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

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

Privileges are assigned to a role using the GRANT statement.

Buy Now

Questions 48

Examine the description of the EMPLOYEES table:

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

The USER SYNONYMS view can provide information about private synonyms.

The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.

All the dynamic performance views prefixed with V$ are accessible to all the database users.

The USER OBJECTS view can provide information about the tables and views created by the user only.

DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

Buy Now

Questions 54

Examine the description of the PRODUCT_ STATUS table:

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 55

Examine the data in the EMP table:

You execute this query:

SELECT deptno AS "Department", AVG(sal) AS AverageSalary, MAX(sal) AS "Max Salary"

FROM emp

WHERE sal >= 12000

GROUP BY "Department "

ORDER BY AverageSalary;

Why does an error occur?

Options:

An alias name must not be used in an ORDER BY clause.

An allas name must not contain space characters.

An alias name must not be used in a GROUP BY clause.

An alias name must always be specified in quotes.

Buy Now

Questions 56

Examine this SQL statement

DELETE FROM employees e

WHERE EXISTS

(SELECT' dummy'

FROM emp history

WHERE employee_ id= e. employee id);

Which two are true?

Options:

The subquery is not a correlated subquery.

The subquery is executed before the DELETE statement is executed.

All existing rows in the EMPLOYEES table are deleted,

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

The subquery is executed for every row in the EMPLOYEES table.

Buy Now

Questions 57

Which three statements are true about defining relations between tables in a relational database?

Regardless of salary,only if the employee id is less than 125,insert EMPLOYEE_ID,NANAGER_ID,SALARY into the MGR_HISTORY table.

If the salary is more than 20000 and the employee is less than 125,insert EMPLOYEE_ID and SALARY into the SPECIAL_SAL table.

Only if the salary is 20000 or less and the employee id is less than 125,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the MGR_HISTORY table.

Regardless of salary and employee id,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the MGR_HISTORY table.

If the salary is 20000 or less and the employee id is less than 125,insert EMPLOYEE_ID,HIRE_DATE,and SALARY into the SAL_HISTORY table.

Only if the salary is 20000 or less and the employee id is 125 or higher,insert EMPLOYEE_ID,MANAGER_ID,and SALARY into the MDR_HISTORY table.

Buy Now

Questions 61

Examine this query:

SELECT employee_id,first_name,salary

FROM employees

WHERE hire_date>'&1';

Which two methods should you use to prevent prompting for a hire date value when this query is executed?

Options:

Use the DEFINE command before executing the query.

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

Replace'&1' with'&&1' in the query.

Execute the SET VERIFY OFF command before executing the query.

Use the UNDEFINE command before executing the query.

Execute the SET VERIFY ON command before executing the query.

Buy Now

Questions 62

Which two are true about using constraints?

Options:

A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parenttable must have the same names.

A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.

A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.

PRIMARY KEY and FOREIGNY constraints can be specified at the column and at the table level

A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

NOT NULL can be specified at the column and at the table level.

Buy Now

Questions 63

Examine the description products table:

Examine the description of the new_projects table;

Which two queries execute successfully?

Options:

Option A

Option B

Option C

Option D

Option E

Buy Now

Answer:

A, D

Explanation:

To determine which queries will execute successfully, we need to consider the compatibility of the column definitions and the structure of the SELECT statements:

Option A uses the MINUS set operator, which subtracts rows returned by the second SELECT statement from the rows returned by the first. For MINUS to work, the number and the order of columns and their data types must be the same in both queries. This query will not execute successfully because the second SELECT statement does not include all columns from the first SELECT statement, and the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).

Option B uses the UNION ALL set operator, which appends the results of the second SELECT statement to the results of the first. Unlike UNION, UNION ALL does not eliminate duplicate rows. This query will execute successfully because UNION ALL does not require the same data types or sizes, and the result will contain all columns from the first SELECT statement filled with NULL for non-matching columns from the second SELECT statement.

Option C uses the UNION set operator, which requires the same number of columns and compatible data types. This query will not execute successfully because PROD_NAME has different data types (CHAR(4) vs VARCHAR2(10)), and the result of a UNION must have the same number of columns with compatible data types in the two SELECT statements.

Option D uses the UNION set operator as well, but unlike Option C, it does not require a specific data type match because both SELECT statements include all columns and UNION is used (which will automatically handle type conversion where necessary). This query will execute successfully.

Option E uses the INTERSECT set operator, which requires the same number and order of columns and their data types to be identical or compatible. This query will not execute successfully because the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).

References:

Oracle Documentation on Set Operators: SQL Language Reference - Set Operators

Oracle Documentation on Data Type Precedence: SQL Language Reference - Data Type Precedence

In conclusion, only Option B and Option D will execute successfully because they adhere to the rules of the UNION ALL and UNION operators respectively, regarding column count and data type compatibility.

Questions 64

Which three are true?

Options:

LAST_DAY returns the date of the last day of the current ,month onlyu.

CEIL requires an argument which is a numeric data type.

ADD_MONTHS adds a number of calendar months to a date.

ADD_MONTHS works with a character string that can be implicitlyt converted to a DATE data type.

LAST_DAY return the date of the last day the previous month only.

CEIL returns the largest integer less than or equal to a specified number.

LAST_DAY returns the date of the last day of the month for the date argument passed to the function.

Buy Now

Questions 65

Which two statements are true about * _TABLES views?

Options:

You must have ANY TABLE system privileges, or be granted object privilges on the table, to viewa tabl e in DBA TABLES.

USER TABLES displays all tables owned by the current user.

You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in USER_TABLES.

ALL TABLES displays all tables owned by the current user.

You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in ALL_TABLES.

All users can query DBA_TABLES successfully.

Buy Now

Questions 66

Which three are true about granting object privileges on tables, views, and sequences?

Options:

UPDATE can be granted only on tables and views.

DELETE can be granted on tables, views, and sequences.

REFERENCES can be granted only on tables and views.

INSERT can be granted on tables, views, and sequences.

SELECT can be granted only on tables and views.

ALTER can be granted only on tables and sequences.

Buy Now

Questions 67

Examine this query:

SELECT INTERVAL '100' MONTH DURATION FROM DUAL;

What will be the output?

Options:

DURATION

+08-04

DUFATION

+100

DURATION

+08

an error

Buy Now

Questions 68

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 69

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 INITCAP (SUBSTR(cust_name, INSTR(cust_name,'') +1)) IN ('MC%','Mc%)

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

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

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

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

Buy Now

Questions 70

Examine the description of the ORDERS table:

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 71

Which two are true about queries using set operators such as UNION?

Options:

An expression in the first SELECT list must have a column alias for the expression

CHAR columns of different lengths used with a set operator retum a vAacsua mhtoe e equals the longest CHAR value.

Queries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric)

In a query containing multiple set operators INTERSECT always takes precedence over UNION and UNION ALL

All set operators are valid on columns all data types.

Buy Now

Questions 72

Which three statements are true about Oracle synonyms?

Options:

A synonym cannot be created for a PL /SQL package.

A SEQUENCE can have a synonym.

A synonym can be available to all users .

A synonym created by one user can refer to an object belonging to another user.

Any user can drop a PUBLIC synonym.

Buy Now

Questions 73

Which three are true about subqueries?

Options:

A subquery can be used in a WHERE clause.

A subquery can be used in a HAVING clause.

=ANY can only evaluate the argument against a subcjuery if it returns two or more values.

A subquery cannot be used in a FROM clause.

< any returns true if the argument is less than the lowest value returned by the subquery.

A subquery cannot be used in the select list.

Buy Now

Questions 74

Examine the description of the EMPLOYEES table：

Which two queries return the highest salary in the table?

Options:

SELECT department_id, MAX(salary)

FROM employees

GROUP BY department_id;

SELECT MAX (salary)

FROM employees;

SELECT MAX (salary)

FROM employees

GROUP BY department_id;

SELECT MAX (salary)

FROM employees

GROUP BY department_id

HAVING MAX (salary) = MAX (MAX (salary));

SELECT MAX (MAX (salary))

FROM employees

GROUP BY department_id;

Buy Now

Questions 75

Which two are true?

Options:

CONCAT joins two or more character strings together.

FLOOR returns the largest integer less than or equal to a specified number.

CONCAT joins two character strings together.

INSTR finds the offset within a string of a single character only.

INSTR finds the offset within a character string, starting from position 0.

FLOOR returns the largest positive integer less than or equal to a specified number.

Buy Now

Questions 76

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 77

You execute this command:

TRUNCATE TABLE dept;

Which two are true?

Options:

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.

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

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 78

Examine the description of the countries table:

Examine the description of the departments table:

Examine the description of the locations table:

Which two queries will return a list of countries with no departments?

Options:

Option A

Option B

Option C

Option D

Buy Now

Questions 79

Examine the description of the MEMBERS table;

SELECT city,last_name LNAME FROM members …

You want to display all cities that contain the string AN. The cities must be returned in ascending order, with the last names further sorted in descending order.

Which two clauses must you add to the query?

Options:

ORDER BY 1,2.

ORDER BY last_name DESC,city ASC

CORADER BY 1, LNAME DESC

WHERE city=’%AN%;

WHERE city LIKE ’%AN%;

WHERE city IN (’%AN%’)

Buy Now

Questions 80

Examine the command to create the BOOKS table.

SQL> create table books(book id CHAR(6) PRIMARY KEY,

title VARCHAR2(100) NOT NULL,

publisher_id VARCHAR2(4)，

author_id VARCHAR2 (50));

The BOOK ID value 101 does not exist in the table.

Examine the SQL statement.

insert into books (book id title, author_id values

(‘101’，’LEARNING SQL’,’Tim Jones’)

Options:

It executes successfully and the row is inserted with a null PLBLISHER_ID.

It executes successfully only if NULL is explicitly specified in the INSERT statement.

It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement.

It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.

Buy Now

Questions 81

In which three situations does a new transaction always start?

Options:

When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session

When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session

When issuing a TRUNCATE statement after a SELECT statement was issued in the same session

When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session

When issuing the first Data Manipulation Language (OML) statement after a COMMIT or ROLLBACK statement was issued in the same session

When issuing a DML statement after a DML statement filed in the same session.

Buy Now

Questions 82

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 83

Which two are true about transactions in the Oracle Database?

Options:

A session can see uncommitted updates made by the same user in a different session.

A DDL statement issued by a session with an uncommitted transaction automatically Commits that transaction.

DML statements always start new transactions.

DDL statements automatically commit only data dictionary updates caused by executing the DDL.

An uncommitted transaction is automatically committed when the user exits SQL*Plus.

Buy Now

Questions 84

Which three statements are true about Data Manipulation Language (DML)?

Options:

delete statements can remove multiple rows based on multiple conditions.

insert statements can insert nulls explicitly into a column.

insert into. . .select. . .from statements automatically commit.

DML statements require a primary key be defined on a table.

update statements can have different subqueries to specify the values for each updated column.

Buy Now

Questions 85

Examine this partial statement:

SELECT ename, sal,comm FROM emp

Now examine this output:

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 86

Which three statements are true about Structured Query Language (SQL)?

Options:

It guarantees atomicity, consistency, isolation, and durability (ACID) features

It best supports relational databases

It is used to define encapsulation and polymorphism for a relational table

It requires that data be contained in hierarchical data storage

It is the only language that can be used for both relational and object-oriented databases

It provides independence for logical data structures being manipulated from the underlying physical data storage

Buy Now

Questions 87

Examine this partial command:

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 88

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 89

Which statement is true about aggregate functions?

Options:

The AVG function implicitly converts NULLS to zero

The MAX and MIN functions can be used on columns with character data types

Aggregate functions can be used in any clause of a SELECT statement

Aggregate functions can be nested to any number of levels

Buy Now

Questions 90

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 91

Which two are true about creating tables in an Oracle database?

Options:

A create table statement can specify the maximum number of rows the table will contain.

The same table name can be used for tables in different schemas.

A system privilege is required.

Creating an external table will automatically create a file using the specified directory and file name.

A primary key constraint is manadatory.

Buy Now

Questions 92

Which two statements are true about substitution variables?

Options:

A substitution variable used to prompt for a column name must be endorsed in single quotation marks.

A substitution variable used to prompt for a column name must be endorsed in double quotation marks.

A substitution variable prefixed with & always prompts only once for a value in a session.

A substitution variable can be used with any clause in a SELECT statement.

A substitution variable can be used only in a SELECT statement.

A substitution variable prefixed with 6 prompts only once for a value in a session unless is set to undefined in the session.

Buy Now

Questions 93

Examine the description of the PRODUCTS table:

Which two statements execute without errors?

Options:

MERGE INTO new_prices n

USING (SELECT * FROM products) p

WHEN MATCHED THEN

UPDATE SET n.price= p.cost* 01

WHEN NOT MATCHED THEN

INSERT(n.prod_id, n.price) VALUES(p.prod_id, cost*.01)

WHERE(p.cost<200);

MERGE INTO new_prices n

USING (SELECT * FROM products WHERE cost>150) p

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

UPDATE SET n.price= p.cost*.01

DELETE WHERE (p.cost<200);

MERGE INTO new_prices n

USING products p

ON (p.prod_id =n.prod_id)

WHEN NOT MATCHED THEN

INSERT (n.prod _id, n.price) VALUES (p.prod_id, cost*.01)

WHERE (p.cost<200);

MERGE INTO new_prices n

USING (SELECT * FROM products WHERE cost>150) p

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

DELETE WHERE (p.cost<200)

Buy Now

Questions 94

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 less than 1000.

Which three DELETE statements execute successfully?

Options:

DELETE FROM orders WHERE order_total<1000;

DELETE * FROM orders WHERE order_total<1000;

DELETE orders WHERE order_total<1000;

DELETE FROM orders;

DELETE order_id FROM orders WHERE order_total<1000;

Buy Now

Questions 95

Whith three statements are true about built in data types?

Options:

A VARCHAR2 blank pads column values only if the data stored is non numeric and contains no special characlers

A BFILE stores unstructured binary data in operating systerm files

A CHAR column definition does not require the length to be specified

The default length for a CHAR column is always one character

A VARCHAR2 column definition does not require the length to be specified

A BLOB stores unstructured binary data within the database

Buy Now

Questions 96

Which two are true about granting object privileges on tables, views, and sequences?

Options:

DELETE can be granted on tables, views, and sequences.

REFERENCES can be grantrd only on tables.

INSERT can be granted only on tables and sequences.

SELECT can be granted on tables, views, and sequences.

ALTER can be granted only on tables and sequences.

Buy Now

Questions 97

Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?

Options:

The first column in the last SELECT of the compound query

The first NUMBER column in the first SELECT of the compound query

The first VARCHAR2 column in the first SELECT of the compound query

The first column in the first SELECT of the compound query

The first NUMBER or VARCHAR2 column in the last SELECTof the compound query

Buy Now

Exam Code: 1z0-071

Exam Name: Oracle Database 12c SQL

Last Update: Dec 3, 2024

Questions: 326

PDF + Testing Engine

$140

Testing Engine

$105

PDF (Q&A)

$90

Winter Special Limited Time 65% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: netbudy65

Dumpsbuddy logo

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:

Options:

Answer: