Oracle 1z1-071 Premium Exam Engine pdf – Download Free Updated 308 Questions [Q158-Q181]

20Jan 2024 by admin No Comments

4.3/5 - (3 votes)

Oracle 1z1-071 Premium Exam Engine pdf – Download Free Updated 308 Questions

Verified 1z1-071 Bundle Real Exam Dumps PDF

Oracle 1z1-071 exam is a multiple-choice exam that comprises 73 questions. Candidates are given two hours to complete the exam, and the passing score is 63%. To pass the exam, candidates need to have a thorough understanding of SQL syntax and database implementation techniques. 1z1-071 exam is designed to validate one’s knowledge of SQL concepts such as queries, data manipulation, data control language, and table creation.

NEW QUESTION 158
The customers table has the following structure:
You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?

SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE cust_income_level IS NOT NULL ANDtax_amount IS NOT NULL;

SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE cust_income_level IS NOT NULL ANDcust_credit_limit IS NOT NULL;

SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE cust_income_level <> NULL ANDtax_amount <> NULL;

SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE (cust_income_level, tax_amount) IS NOT NULL;

NEW QUESTION 159
View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

The CUSTOMERS table contains the current location of all currently active customers.
The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?

INTERSECT

UNION ALL

MINUS

UNION

NEW QUESTION 160
Examine this query:
SELECT TRUNC(ROUND(156.00, -2), -1) FROM DUAL;
What is the result?

200

100

160

150

NEW QUESTION 161
View the Exhibits and examine the structure of the COSTSand PROMOTIONStables.
You want to display PROD_IDSwhose promotion cost is less than the highest cost PROD_IDin a promotion time interval.
Examine this SQL statements:

What will be the result?
Exhibit 1.

Exhibit 2.

It gives an error because the GROUP BYclause is not valid

It executes successfully but does not give the required result

It executes successfully and gives the required result

It gives an error because the ALLkeyword is not valid

NEW QUESTION 162
You want to display the date for the first Monday of the next month and issue the following command:

What is the outcome?

In generates an error because rrrrshould be replaced by rrin the format string.

It executes successfully but does not return the correct result.

It executes successfully and returns the correct result.

In generates an error because TO_CHARshould be replaced with TO_DATE.

In generates an error because fmand double quotation marks should not be used in the format string.

NEW QUESTION 163
View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?

The statement would execute successfully to produce the required output.

The statement would not execute because inline views and outer joins cannot be used together.

The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

The statement would not execute because the GROUP BY clause cannot be used in the inline view.

NEW QUESTION 164
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.

NEW QUESTION 165
SCOTT is a user in the database.
Evaluate the commands issued by the DBA:

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

Statement 1 would not execute because the WITH GRANT option is missing.

Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.

Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.

Statement 1 would not execute because the IDENTIFIED BY <password> clause is missing.

NEW QUESTION 166
Examine the structure proposed for the TRANSACTIONStable:

Which two statements are true regarding the creation and storage of data in the above table structure?
(Choose two.)

The CUST_STATUScolumn would store exactly one character.

The TRANS_VALIDITYcolumn would have a maximum size of one character.

The CUST_CREDIT_LIMITcolumn would be able to store decimal values.

The CUST_STATUScolumn would give an error.

The TRANS_DATEcolumn would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.

The TRANS_VALIDITYcolumn would give an error.

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

What will be the status of the foreign key EMP_MGR_FK?

It will be enabled and immediate.

It will be enabled and deferred.

It will remain disabled and can be re-enabled manually.

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

NEW QUESTION 168
Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, which are not present in the SALES2 table.
SALES1 table
NameNullType
———————————————–
SALES_IDNUMBER
STORE_IDNUMBER
ITEMS_IDNUMBER
QUANTITYNUMBER
SALES_DATEDATE
SALES2 table
NameNullType
———————————————-
SALES_IDNUMBER
STORE_IDNUMBER
ITEMS_IDNUMBER
QUANTITYNUMBER
SALES_DATEDATE
Which set operator generates the required output?

INTERSECT

UNION

PLUS

MINUS

SUBTRACT

NEW QUESTION 169
Evaluate the following SQL statement:
SQL> select cust_id, cust_last_name “Last name”
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses either one of which can complete the query. (Choose three.)

ORDER BY “Last name”

ORDER BY 2, cust_id

ORDER BY CUST_NO

ORDER BY 2,1

ORDER BY “CUST_NO”

NEW QUESTION 170
Which two statements are true regarding roles? (Choose two.)

A role can be granted to itself.

A role can be granted to PUBLIC.

A user can be granted only one role at any point of time.

The REVOKE command can be used to remove privileges but not roles from other users.

Roles are named groups of related privileges that can be granted to users or other roles.

NEW QUESTION 171
Evaluate the following SQL statement
SQL>SELECT promo_id, prom _category FROM promotions
WHERE promo_category=’Internet’ ORDER BY promo_id
UNION
SELECT promo_id, promo_category FROM Pomotions
WHERE promo_category = ‘TV’
UNION
SELECT promoid, promocategory FROM promotions WHERE promo category=’Radio’
Which statement is true regarding the outcome of the above query?

It executes successfully and displays rows in the descend ignore of PROMO CATEGORY.

It produces an error because positional, notation cannot be used in the ORDER BY clause with SBT operators.

It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.

It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.

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

CREATE TABLE employees(employee_id NUMBER,Login_id
NUMBER,Employee_name VARCHAR2(100),Hire_date DATE,CONSTRAINT
emp_id_ukUNIQUE (employee_id, login_id));

CREATE TABLE employees(employee_id NUMBER,login_id
NUMBER,employee_name VARCHAR2(25),hire_date DATE,CONSTRAINT emp_id_pk
PRIMARY KEY (employee_id, login_id));

CREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_pk
PRIMARY KEY, Login_id NUMBER UNIQUE, Employee_name VARCHAR2(25),Hire_date DATE);

CREATE TABLE employees(employee_id NUMBER,Login_id
NUMBER,Employee_name VARCHAR2(100),Hire_date DATE,CONSTRAINT emp_id_uk
UNIQUE (employee_id, login_id);CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));

CREATE TABLE employees(employee_id NUMBER CONSTRAINT emp_id_nn NOT
NULL, Login_id NUMBER CONSTRAINT login_id_nn NOT NULL,Employee_name
VARCHAR2(100),Hire_date DATE,CONSTRAINT emp_id_ukUNIQUE (employee_id,
login_id));

NEW QUESTION 173
Which three actions can you perform only with system privileges?

Truncate a table in another schema.

Access flat files via a database, which are stored in an operating system directory.

Query any table in a database.

Use the WITH GRANT OPTION clause.

Execute a procedure in another schema.

NEW QUESTION 174
In which three situations does a transaction complete? (Choose three.)

when a PL/SQL anonymous block is executed

when a DELETE statement is executed

when a ROLLBACK command is executed

when a data definition language (DDL) statement is executed

when a TRUNCATE statement is executed after the pending transaction

NEW QUESTION 175
In which three situations does a transaction complete?

when a PL/SQL anonymous block is executed

when a DELETEstatement is executed

when a ROLLBACKcommand is executed

when a data definition language (DDL) statement is executed

when a TRUNCATEstatement is executed after the pending transaction

NEW QUESTION 176
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:
SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p ______________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN operations can be used to obtain the required output?

FULL OUTER JOIN

JOIN

LEFT OUETR JOIN

RIGHT OUTER JOIN

NEW QUESTION 177
View the exhibit and examine the structure and data in the INVOICE table.

Which two SQL statements would execute successfully? (Choose two.)

SELECT MAX(AVG(SYSDATE -inv_date))
FROM invoice

SELECT AVG(inv_date)
FROM invoice

SELECT MAX(inv_date), MIN(cust_id)
FROM invoice

SELECT AVG( inv_date -SYSDATE), AVG(inv_amt)
FROM invoice

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

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

NEW QUESTION 179
Examine the description of the SALES1 table:

SALES2 is a table with the same description as SALES1.
Some sales data is duplicated in both tables.
You want to display the rows from the SALES1 table which are not present in the SALES2 table.
Which set operator generates the required output?

SUBTRACT

INTERSECT

UNION ALL

UNION

MINUS

NEW QUESTION 180
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_TYPE or MEMBER_ID as A101 and A102.

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

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

NEW QUESTION 181
Examine the structure of the EMPLOYEES table:

There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the name, joining date, and manager for all employees. Newly hired employees are yet to be assigned a department or a manager. For them, ‘No Manager’ should be displayed in the MANAGER column.
Which SQL query gets the required output?

SELECT e.last_name, e.hire_date, NVL(m.last_name, ‘No Manager’) ManagerFROM employees e JOIN employees mON (e.manager_id = m.employee_id);

SELECT e.last_name, e.hire_date, NVL(m.last_name, ‘No Manager’) ManagerFROM employees e LEFT OUTER JOIN employees mON (e.manager_id = m.employee_id);

SELECT e.last_name, e.hire_date, NVL(m.last_name, ‘No Manager’) ManagerFROM employees e RIGHT OUTER JOIN employees mON (e.manager_id = m.employee_id);

SELECT e.last_name, e.hire_date, NVL(m.last_name, ‘No Manager’) ManagerFROM employees e NATURAL JOIN employees mON (e.manager_id = m.employee_id).

Pass Your Oracle Exam with 1z1-071 Exam Dumps: https://www.practicematerial.com/1z1-071-exam-materials.html

Leave a Reply Cancel reply