[Jan-2025] 1z1-071 exam torrent Oracle study guide [Q169-Q191]

4/5 - (1 vote)

[Jan-2025] 1z1-071 exam torrent Oracle study guide

Use Valid New 1z1-071 Test Notes & 1z1-071 Valid Exam Guide

Format of ORACLE 1Z0-071 Certification Exam

The ORACLE 1Z0-071 exam will include the following sections:

Section 1: Basic Concepts and Principles
Section 2: Oracle Database SQL Language and Data Structures
Section 4: Data Integrity, Security, and Backup Methods.
Section 3: Oracle Database Administration and Management

ORACLE 1Z0-071 exams questions will be in the form of Multiple choice, and the passing score for winning the certification is 63%. If you will not be able to score the required percentage, you will not be entertained with the certification. Don’t worry, Oracle 1Z0-071 Dumps will help you to get a score in the exam.

NO.169 Which three are true about privileges and roles?

A role is owned by the user who created it.

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

System privileges always set privileges for an entire database.

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

All roles are owned by the SYS schema.

PUBLICcan be revoked from a user.

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

NO.170 Examine the structure of the BOOKS_TRANSACTIONS table:
You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?

SELECT member_id AS “MEMBER ID”, due_date AS “DUE DATE”, $2 AS “LATE FEE” FROM BOOKS_TRANSACTIONS

SELECT member_id AS “MEMBER ID”, due_date AS “DUE DATE”, ‘$2’ AS “LATE FEE” FROM BOOKS_TRANSACTIONS

SELECT member_id ‘MEMBER ID’, due_date ‘DUE DATE’, ‘$2 AS LATE FEE’ FROM BOOKS_TRANSACTIONS;

SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE
FROM BOOKS_TRANSACTIONS

NO.171 View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. (Choose the best answer.)

PRODUCT_ID column is the primary key.
You create an index using this command:
SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table.
Which query would use the UPPER_NAME_IDX index?

SELECT product_id, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name) = ‘LASERPRO’ OR list_price > 1000;

SELECT UPPER(product_name)
FROM product_information;

SELECT UPPER(product_name)
FROM product_information
WHERE product_id = 2254;

SELECT product_id
FROM product_information
WHERE UPPER(product_name) IN (‘LASERPRO’, ‘CABLE’);

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

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

when a CREATE INDEX statement is executed successfully in the same session

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

when the session logs out successfully

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

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

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

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.

NO.174

Which three statements are true?

The COMMISSION column can contain negative values .

The MANAGER column is a foreign key referencing the EMPNO column.

The SALARY column must have a value .

An index is created automatically in the MANAGER column.

The DEPTNO column in the EMP table can contain the value 1.

The DEPTNO column in the EMP table can contain NULLS .

The DNAME column has a unique constraint.

NO.175 View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator:

Which two JOIN operations can be used to obtain the required output? (Choose two.)

FULL OUTER JOIN

JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

NO.176 Which two statements are true about the rules of precedence for operators? (Choose two.)

The concatenation operator | | is always evaluated before addition and subtraction in an expression

Multiple parentheses can be used to override the default precedence of operators in an expression

Arithmetic operators with equal precedence area evaluated from left to right within an expression

NULLS influence the precedence of operators in an expression

The + binary operator has the highest precedence in an expression in a SQL statement

NO.177 View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. (Choose the best answer.)

PRODUCT_ID column is the primary key.
You create an index using this command:
SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table.
Which query would use the UPPER_NAME_IDX index?

SELECT product_id, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name) = ‘LASERPRO’ OR list_price > 1000;

SELECT UPPER(product_name)
FROM product_information;

SELECT UPPER(product_name)
FROM product_information
WHERE product_id = 2254;

SELECT product_id
FROM product_information
WHERE UPPER(product_name) IN (‘LASERPRO’, ‘CABLE’);

NO.178 View the exhibit and examine the structure of the PROMOTIONStable.

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the ‘INTERNET’ category.
Which query would give you the required output?
SELECT promo_name, promo_begin_date FROM promotions

WHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)
FROM promotions) AND
promo_category= ‘INTERNET’;

SELESELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date IN (SELECT promo_begin_date
FROM promotions
WHERE promo_category= ‘INTERNET’);
SELECT promo_name, promo_begin_date FROM promotions

WHERE promo_begin_date > ALL (SELECT promo_begin_date
FROM promotions
WHERE promo_category = ‘INTERNET’);
SELECT promo_name, promo_begin_date FROM promotions

WHERE promo_begin_date> ANY (SELECT promo_begin_date
FROM promotions
WHERE promo_category= ‘INTERNET’);

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

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.

NO.180 View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index- organized table. (Choose two.)

Evaluate this SQL statement:
ALTER TABLE emp
DROP COLUMN first_name;
Which two statements are true?

The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.

The FIRST_NAME column would be dropped provided at least one column remains in the table.

The FIRST_NAME column would be dropped provided it does not contain any data.

The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.

NO.181 Examine the structure of the BOOKS_TRANSACTIONS table:

Examine the SQL statement:

Which statement is true about the outcome?

It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.

It displays details for members who have borrowed before today’s date with either RM as TRANSACTION_TYPEor MEMBER_IDas A101 and A102.

It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE.

It displays details for members who have borrowed before today with RM as TRANSACTION_TYPEand the details for members A101 or A102.

NO.182 Examine this SQL statement:

Which two are true?

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.

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

NO.183 Which three are true about the CREATE TABLE command?

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.

NO.184 The PROD_ID column is the foreign key in the SALES table.Which references the PRODUCTS table.
Similarly,the CUST_ID and TIME_ID columns are Also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales(prod_id, I cust_id, order_date DEFAULT SYSDATE)
AS SELECT I prod_id,cust_id,time_id FROM sales.
Which statement is true regarding the above command?

The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.

The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.

The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause I do not match.

The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table

NO.185 Examine this query which executes successfully;
Select job,deptno from emp
Union all
Select job,deptno from jobs_history;
What will be the result?

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.

NO.186 Which three statements are true about single-row functions?

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.

NO.187 Examine this data in the EMPLOYERS table

Which statement will execute successfully?

SELECT dept_id, MAX (Last_name), SUM (salary) FROM employees GROUP BY dept_id

SELECT dept_id, LENGTH (last_name), SUM (salary) FROM employees GROUP BY dept_id

SELECT dept_id, STDDEV (last_name), SUM (salary) FROM employees GROUP BY dept_id

SELECT dept_id, INSTR (last_name,’A’), SUM (salary) FROM employees GROUP BY dept_id

NO.188 Which two join conditions in a from clause are non-equijoins?

tablet join table2 ON (table1.column = table2.column) where table2.column LIKE ‘A’

table1 join table2 on (table1.column between table2.column] and table2.column2)

table1 natural JOIN table2

table1 join table2 using (column1, column2)

table1 join table2 ON (table1.column >= table2.column)

NO.189 Examine the structure of the BOOKS_TRANSACTIONStable.

You want to update this table such that BOOK_IDis set to ‘INVALID’for all rows where no MEMBER_ID has been entered.
Examine this partial SQL statement:

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

MEMBER_ID = ”;

MEMBER_ID = NULL;

MEMBER_ID IS NULL;

MEMBER_ID = “”;

NO.190 You executed the following CREATE TABLE statement that resulted in an error:
SQL> CREATE TABLE employees(emp_id NUMBER(10) PRIMARY KEY, ename VARCHAR2(20), email NUMBER(3) UNIQUE, address VARCHAR2(500), phone VARCHAR2(20), resume LONG, hire_date DATE, remarks LONG, dept_id NUMBER(3) CONSTRAINT emp_dept_id_fk REFERENCES departments (dept_id), CONSTRAINT ename_nn NOY NULL(ename));
Identify two reasons for the error.

The NOT NULL constraint on the ENAME column must be defined as the column level

FOREIGN KEY defined on the DEPT_ID column must be at the table level only

Only one LONG column can be used per table

The FOREIGN KEY keyword is missing in the constraint definition

The PRIMARY KEY constraint in the EMP_ID column must have a name and must be defined at the table level only

NO.191 View the Exhibit and examine the description of the ORDERS table. (Choose two.)

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