Free Oracle (1z1-071) Certification Sample Questions with Online Practice Test [Q22-Q40]

Rate this post

Free Oracle (1z1-071) Certification Sample Questions with Online Practice Test

1z1-071 Certification Study Guide Pass 1z1-071 Fast

NEW QUESTION 22
Which three are true about system and object privileges

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

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

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

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

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

NEW QUESTION 23
You execute this command:

Which two are true?

It drops any triggers defined on the table.

It always retains the space used by the removed rows.

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

It retains the integrity constraints defined on the table.

It retains the indexes defined on the table.

A FLASHBACK TABLE statement can be used to retrieve the deleted data.

NEW QUESTION 24
In your session, the NLS._DAE_FORMAT is DD- MM- YYYY.There are 86400 seconds in a day.Examine this result:
DATE
02-JAN-2020
Which statement returns this?

SELECT TO_ CHAR(TO_ DATE(’29-10-2019′) +INTERVAL ‘2’; MONTH + INTERVAL ‘5’; DAY
–
INTERVAL ‘86410’ SECOND, ‘ DD-MON-YYYY’) AS “date”
FROM DUAL;

SELECT TO_ CHAR(TO_ DATE(’29-10-2019′) + INTERVAL ‘3’ MONTH + INTERVAL ‘7’ DAY – INTERVAL ‘360’ SECOND, ‘ DD-MON-YYYY’) AS “date” FROM DUAL;

SELECT To CHAR(TO _DATE(’29-10-2019′) + INTERVAL ‘2’ NONTH + INTERVAL ‘5’ DAY INEERVAL ‘120’ SECOND, ‘ DD-MON-YYY) AS “date” FROM DUAL;

SELECT-TO_CHAR(TO _DATE(’29-10-2019’+ INTERVAL ‘2’ MONTH+INTERVAL ‘6’ DAYINTERVAL
‘120’ SECOND, ‘DD-MON-YY’) AS “daTe”
FROM DUAL;

SELECT-TO_CHAR(TO _DATE(’29-10-2019’+ INTERVAL ‘2’ MONTH+INTERVAL ‘4’ DAYINTERVAL
‘120’ SECOND, ‘DD-MON-YY’) AS “daTe”
FROM DUAL;

NEW QUESTION 25
Examine the description of the EMPLOYEES table:

Which query is valid?

SELECT dept_id, join date, SUM(salary) FROM employees GROUP BY dept_id,join_date;

SELECT dept_id, MAX (AVG(salary)) FROM employees GROUP BY dept_id;

SELECT dept_id, AVG(NAX(salary)) FROM employees GROUP BY dept_id;

SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;

NEW QUESTION 26
Which two statements are true about the DUAL table?

It can display multiple rows and columns.

It can be accessed only by the SYS user.

It can be accessed by any user who has the SELECT privilege in any schema

It can display multiple rows but only a single column.

It consists of a single row and single column of VARCHAR2 data type.

It can be used to display only constants or pseudo columns.

NEW QUESTION 27
View the exhibit for the structure of the STUDENTand FACULTYtables.
STUDENT
Name Null? Type
—————— ——————- ————-
STUDENT_ID NOT NULL NUMBER(2)
STUDENT_NAME VARCHAR2(20)
FACULTY_ID VARCHAR2(2)
LOCATION_ID NUMBER(2)
FACULTY
Name Null? Type
– —————– ——————- ————-
FACULTY_ID NOT NULL NUMBER(2)
FACULTY_NAME VARCHAR2(20)
LOCATION_ID NUMBER(2)
You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:
Statement 1
SQL>SELECT faculty_name, COUNT(student_id)
FROM student JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;
Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty
GROUP BY faculty_name;
Which statement is true regarding the outcome?

Only statement 2 executes successfully and gives the required result.

Only statement 1 executes successfully and gives the required result.

Both statements 1 and 2 execute successfully and give different results.

Both statements 1 and 2 execute successfully and give the same required result.

NEW QUESTION 28
The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?

PUBLIC should be replaced with specific usernames.

ALL should be replaced with a list of specific privileges.

WITH GRANT OPTION should be added to the statement.

Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.

NEW QUESTION 29
In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than
10 years.
Evaluate the following SQL statement:

What would be the result?

It executes successfully but no rows updated.

It executes successfully and updates the records of those employees who have been working in the company for more than 600 days.

It gives an error because multiple NVL functions are used in an expression.

It gives an error because NVL function cannot be used with UPDATE.

NEW QUESTION 30
View the Exhibit and examine the data in the PRODUCTS table. (Choose the best answer.) You must display product names from the PRODUCTS table that belong to the
‘Software/other’ category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:
SQL > SELECT prod_name, prod_category, prod_min_price FROM products
Where prod_category LIKE ‘%Other%’ AND (prod_min_price = 2000 OR prod_min_price
4000) AND prod_unit_of_measure <> ‘ ‘;
Which statement is true?

It executes successfully but returns no result.

It executes successfully and returns the required result.

It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.

It generates an error because the condition specified for the PROD_CATEGORY column is not valid.

NEW QUESTION 31
View the exhibit and examine the data in ORDERS_MASTERand MONTHLY_ORDERStables.

Evaluate the following MERGEstatement:
MERGE_INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?

The ORDERS_MASTERtable would contain the ORDER_IDs1, 2, 3 and 4.

The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 4.

The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 3.

The ORDERS_MASTERtable would contain the ORDER_IDs1 and 2.

NEW QUESTION 32
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?

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;

NEW QUESTION 33
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_’;

NEW QUESTION 34
Which two statement are true regarding table joins available in the Oracle Database server? (Choose two.)

You can use the ON clause to specify multiple conditions while joining tables.

You can explicitly provide the join condition with a NATURAL JOIN.

You can use the JOIN clause to join only two tables.

You can use the USING clause to join tables on more than one column.

NEW QUESTION 35
Evaluate the following CREATE TABLE command:

Which statement is true regarding the above SQL statement?

It would execute successfully and only ORD_ITM_IDX index would be created.

It would give an error because the USING INDEX clause cannot be used on a composite primary.

It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.

It would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.

NEW QUESTION 36
Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.)

GLOBAL TEMPORARY TABLErows inserted by a session are available to any other session whose user has been granted select on the table.

GLOBAL TEMPORARY TABLEspace allocation occurs at session start.

A DELETE command on a GLOBAL TEMPORARY TABLEcannot be rolled back.

A GLOBAL TEMPORARY TABLE’sdefinition is available to multiple sessions.

Any GLOBAL TEMPORARY TABLErows existing at session termination will be deleted.

A TRUNCATEcommand issued in a session causes all rows in a GLOBAL TEMPORARY TABLEfor the issuing session to be deleted.

NEW QUESTION 37
View the Exhibit and examine the description of the PRODUCT_INFORMATION table. Which SQL statement would retrieve from the table the number of products having
LIST_PRICE as NULL?

SELECT COUNT(list_price)
FROM product_information
WHERE list_price IS NULL;

SELECT COUNT(list_price)
FROM product_information
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;

NEW QUESTION 38
Which two statements are true about substitution variables?

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.

NEW QUESTION 39
Which statement is true regarding the default behavior of the ORDER BY clause?

In a character sort, the values are case-sensitive

NULL values are not considered at all by the sort operation

Only those columns that are specified in the SELECT list can be used in the ORDER BY clause

Numeric values are displayed from the maximum to the minimum value if they have decimal positions

NEW QUESTION 40
Evaluate the following SQL commands:

The command to create a table fails. Identify the reason for the SQL statement failure.

You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.

You cannot use the BETWEEN clause in the condition of a CHECK constraint.

You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.

You cannot use SYSDATE in the condition of a CHECK constraint.

Get Perfect Results with Premium 1z1-071 Dumps Updated 323 Questions: https://www.passleadervce.com/Oracle-PL-SQL-Developer-Certified-Associate/reliable-1z1-071-exam-learning-guide.html

Leave a Reply Cancel reply