Free 1z0-071 Exam Braindumps certification guide Q&A [Q155-Q173]

4.7/5 - (3 votes)

Free 1z0-071 Exam Braindumps certification guide Q&A

1z0-071 Certification Overview Latest 1z0-071 PDF Dumps

The Oracle 1z0-071 certification exam is an essential step for individuals who want to demonstrate their proficiency in SQL programming for Oracle databases. Passing this exam is a requirement for obtaining the Oracle Database SQL certification, which is highly valued in the database management industry. With the right preparation and dedication, candidates can successfully pass this exam and advance their careers in database management.

NEW QUESTION 155
View the Exhibit and examine the structure of the PRODUCT table.

Which two tasks would require subqueries? (Choose two.)

display all products whose minimum list price is more than the average list price of products having the status ‘orderable’

display the total number of products supplied by supplier 102 and have product status as ‘OBSOLETE’

display the number of products whose list prices are more than the average list price

display all suppliers whose list price is more than 1000

display the minimum list price for each product status

NEW QUESTION 156
You execute this query:

What is the result?

It returns the date for the first Monday of the next month.

It returns the date for the last Monday of the current month.

It executes successfully but does not return any result.

It generates an error.

NEW QUESTION 157
Examine the structure of the PROMOTIONS table: (Choose the best answer.)

Management requires a report of unique promotion costs in each promotion category.
Which query would satisfy this requirement?

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1

SELECT promo_category, DISTINCT promo_cost FROM promotions

SELECT DISTINCT promo_cost, promo_category FROM promotions

SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

NEW QUESTION 158
View the exhibit and examine the description of SALESand PROMOTIONStables.

You want to delete rows from the SALES table, where the PROMO_NAMEcolumn in the PROMOTIONStable has either blowout saleor everyday low priceas values.
Which three DELETEstatements are valid? (Choose three.)
DELETE

FROM sales
WHERE promo_id = (SELECT promo_id
FROM promo_name = ‘blowout sale’)
AND promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘everyday low price’)
FROM promotions
WHERE promo_name = ‘everyday low price’);
DELETE

FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’)
OR promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘everyday low price’)
DELETE

FROM sales
WHERE promo_id = (SELECT promo_id
FROM promotions
WHERE promo_name = ‘blowout sale’)
OR promo_name = ‘everyday low price’);
DELETE

FROM sales
WHERE promo_id IN (SELECT promo_id
FROM promotions
WHERE promo_name IN = ‘blowout sale’,’everyday low price’));

NEW QUESTION 159
View the Exhibit and examine the structure of ORDERSand ORDER_ITEMStables.
ORDER_IDis the primary key in the ORDERStable. It is also the foreign key in the ORDER_ITEMStable wherein it is created with the ON DELETE CASCADEoption.
Which DELETEstatement would execute successfully?

DELETE orders o, order_items I
WHERE o.order_id = i.order_id;

DELETE
FROM orders
WHERE (SELECT order_id
FROM order_items);

DELETE orders
WHERE order_total < 1000;

DELETE order_id
FROM orders
WHERE order_total < 1000;

NEW QUESTION 160
Evaluate the following SQL statement:
SELECT product_name || ‘it’s not available for order’
FROM product_information
WHERE product_status = ‘obsolete’;
You received the following error while executing the above query:
ERROR:
ORA-01756: quoted string not properly terminated
What would you do to execute the query successfully?

Enclose the character literal string in the SELECT clause within the double quotation marks.

Do not enclose the character literal string in the SELECT clause within the single quotation marks.

Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.

Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.

NEW QUESTION 161
You issued this command:

Which three statements are true? (Choose three.)

Views referencing HR.EMPLOYEESare dropped.

All constraints defined on HR.EMPLOYEESare dropped.

Sequences used to populate columns in the HR.EMPLOYEEStable are dropped.

The HR.EMPLOYEEStable may be moved to the recycle bin.

All indexes defined on HR.EMPLOYEESare dropped.

Synonyms for HR.EMPLOYEESare dropped.

NEW QUESTION 162
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 163
View the Exhibit and examine the description of the tables.

You execute this SQL statement:

Which three statements are true?

The statement will execute successfully and a new row will be inserted into the SALES table.

A product can have a different unit price at different times.

The statement will fail because a subquery may not be contained in a VALUES clause.

The statement will fail if a row already exists in the SALES table for product 23.

A customer can exist in many countries.

The SALES table has five foreign keys.

NEW QUESTION 164
These are the steps for a correlated subquery, listed in random order:
The WHERE clause of the outer query is evaluated.
A candidate row is fetched from the table specified in the outer query.
This is repeated for the subsequent rows of the table, until all the rows are processed.
Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?

2, 1, 4, 3

4, 1, 2, 3

4, 2, 1, 3

2, 4, 1, 3

NEW QUESTION 165
View the Exhibit and examine the structure of the ORDER_ITEMS table.

You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.
Which query would produce the desired result?

SELECT order_idFROM order_itemsGROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity))FROM order_items GROUP BY order_id);

SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_items)GROUP BY order_id;

SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = MAX(unit_price*quantity)GROUP BY order_id;

SELECT order_idFROM order_itemsWHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_itemsGROUP BY order_id)

NEW QUESTION 166
Evaluate the following SELECTstatement and view the exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status, FROM user_constraints
WHERE table_name = ‘ORDERS’;

Which two statements are true about the output? (Choose two.)

The R_CONSTRAINT_NAMEcolumn gives the alternative name for the constraint.

In the second column, ‘c’indicates a check constraint.

The STATUScolumn indicates whether the table is currently in use.

The column DELETE_RULEdecides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

NEW QUESTION 167
Evaluate the following query:
SQL> SELECT TRUNC (ROUND (156.00, -1),-1)
FROM DUAL;
What would be the outcome?

150

200

160

100

NEW QUESTION 168
Examine the following query:
SQL> SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;
What is the output of this query?

It displays 5 percent of the products with the highest amount sold.

It displays the first 5 percent of the rows from the SALES table.

It displays 5 percent of the products with the lowest amount sold.

It results in an error because the ORDER BY clause should be the last clause.

NEW QUESTION 169
Which statement adds a column called SALARYto the EMPLOYEEStable having 100 rows, which cannot contain null?

ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL;

ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;

ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) DEFAULT NOT NULL;

ALTER TABLE EMPLOYEES
ADD SALARY NUMBER(8,2) NOT NULL;

NEW QUESTION 170
Which two statements are true about the WHERE and HAVING clauses in a SELECT statement?

The WHERE clause can be used to exclude rows after dividing them into groups

WHERE and HAVING clauses can be used in the same statement only if applied to different table columns.

The HAVING clause can be used with aggregating functions in subqueries.

Aggregating functions and columns used in HAVING clauses must be specified in these SELECT list of a query.

The WHERE clause can be used to exclude rows before dividing them into groups.

NEW QUESTION 171
Examine this statement:

What is returned upon execution?

1 row

an error

0 rows

2 rows

NEW QUESTION 172
In the PROMOTIONS table, the PROMO_BEGIN_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 a query?

PROMO_BEGIN_DATE – 5 will return a date.

PROMO_BEGIN_DATE – SYSDATE will return a number.

TO_NUMBER(PROMO_BEGIN_DATE) – 5 will return a number.

TO_DATE(PROMO_BEGIN_DATE * 5) will return a date.

PROMO_BEGIN_DATE – SYSDATE will return an error.

NEW QUESTION 173
Examine the contents of the EMP table:

What is the result?