Get Latest Nov-2024 Conduct effective penetration tests using TroytecDumps 1z1-071 exam [Q144-Q158]

Rate this post

Get Latest [Nov-2024] Conduct effective penetration tests using TroytecDumps 1z1-071

Penetration testers simulate 1z1-071 exam PDF

Target Audience

The Oracle 1Z0-071 exam is designed for those professionals who have some level of experience in operating with Oracle SQL and PL/SQL technologies and want to improve their expertise to perform the job roles of a Database Administrator or a Developer.

QUESTION 144
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?

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

QUESTION 145
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.

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

(employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id));
CREATE TABLE employees

(employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(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_num_id_uk UNIQUE (employee_id, login_id));

QUESTION 147
Evaluate the following query:

What would be the outcome of the above query?

It produces an error because the data types are not matching.

It executes successfully and displays the literal ” {‘s start date was > “for each row in the output.

It executes successfully and introduces an ‘sat the end of each promo_namein the output.

It produces an error because flower braces have been used.

QUESTION 148
Examine the description of the EMPLOYEES table:

You write this failing statement:
SELECT dept_no AS department_id, MAX (salary) As max_sal
FROM employees
WHERE salary >10000
GROUP BY department_id
ORDER BY max_sal;
Which clause causes the error?

ORDER BY

WHERE

GROUP BY

SELECT

QUESTION 149
Which two are true about unused columns?

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

QUESTION 150
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

QUESTION 151
View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.

Evaluate the following SQL statement:
SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions
WHERE promo_cost < ALL
(SELECT MAX(promo_cost) FROM promotions
GROUP BY (promo_end_date-
promo_begin_date)));
What would be the outcome of the above SQL statement?

It displays prod IDs in the promo with the lowest cost.

It displays prod IDs in the promos with the lowest cost in the same time interval.

It displays prod IDs in the promos with the highest cost in the same time interval.

It displays prod IDs in the promos which cost less than the highest cost in the same time interval.

QUESTION 152
Which three are true about system and object privileges? (Choose three.)

WITH GRANT OPTION can be used when granting an object privilege to both users and roles

Adding a primary key constraint to an existing table in another schema requires a system privilege

Adding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege

Revoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect

Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect.

WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC

QUESTION 153
View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.
Evaluate the following SQL statement:
SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions
WHERE promo_cost < ALL
(SELECT MAX(promo_cost) FROM promotions
GROUP BY (promo_end_date-
promo_begin_date)));
What would be the outcome of the above SQL statement?

It displays prod IDs in the promo with the lowest cost.

It displays prod IDs in the promos with the lowest cost in the same time interval.

It displays prod IDs in the promos with the highest cost in the same time interval.

It displays prod IDs in the promos which cost less than the highest cost in the same time interval.

QUESTION 154
Examine the description of the CUSTOMERS table:

You want to display details of all customers who reside in cities starting with the letter D followed by at least two character.
Which query can be used?

SELECT * FROM customers WHERE city =’D_%’;

SELECT * FROM customers WHERE city =’%D_’;

SELECT * FROM customers WHERE city LIKE’D %’;

SELECT * FROM customers WHERE city LIKE’D_’;

QUESTION 155
Which three privileges can be restricted to a subset of columns in a table? (Choose three.)

ALTER

DELETE

UPDATE

SELECT

INDEX

REFERENCES

INSERT

QUESTION 156
Examine the description of EMPLOYEES table:
Which three queries return all rows for which SALARY+COMMISSION is greate than 20000?

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;

QUESTION 157
Which two statements are true about transactions in the Oracle Database server? (Choose two.)

If a session has an uncommitted transaction, then a DDL statement issues a COMMITbefore starting a new transaction.

An uncommitted transaction commits automatically if the user exists SQL*Plus.

Data Manipulation Language (DML) statements always start a new transaction.

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

A Data Definition Language (DDL) statement does a COMMITautomatically only for the data dictionary updates caused by the DDL.

A session can always see uncommitted updates made by itself.

QUESTION 158
Examine the structure of the ORDERS table: (Choose the best answer.)

You want to find the total value of all the orders for each year and issue this command:
SQL> SELECT TO_CHAR(order_date,’rr’), SUM(order_total) FROM orders
GROUP BY TO_CHAR(order_date, ‘yyyy’);
Which statement is true regarding the result?