[Sep-2021] Oracle PL/SQL Developer Certified Associate 1z1-071 Exam Practice Dumps

2021 1z1-071 Premium Files Test pdf - Free Dumps Collection

NEW QUESTION 30
Which two actions can you perform with object privileges?

• A. Delete rows from tables in any schema except sys.
• B. Create roles.
• C. Create FOREIGN KEY constraints that reference tables in other schemas.
• D. Execute a procedure or function in another schema.
• E. Set default and temporary tablespaces for a user.

Answer: A,C

 

NEW QUESTION 31
View the exhibit and examine the structure of the CUSTOMERS table.

Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)

• A. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
• B. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
• C. finding the number of customers, in each city, whose marital status is 'married'.
• D. listing of customers who do not have a credit limit and were born before 1980
• E. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

Answer: A,E

 

NEW QUESTION 32
Which task can be performed by using a single Data Manipulation Language (DML) statement?

• A. adding a column constraint when inserting a row into a table
• B. removing all data only from one single column on which a primary key constraint is defined
• C. removing all data only from one single column on which a unique constraint is defined
• D. adding a column with a default value when inserting a row into a table

Answer: C

 

NEW QUESTION 33
The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully?

• A. SELECT prod_id FROM sales WHERE quantity_sold> 55000 GROUP BY prod_id HAVING COUNT(*)> 10;
• B. SELECT COUNT(prod_id) FROM sales WHERE quantity_sold>55000 GROUP BY prod_id;
• C. SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*)> 10 GROUP BY prod_id HAVING COUNT(*)> 10;
• D. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold> 55000;
• E. SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT(*)> 10 GROUP BY COUNT(*)> 10;

Answer: A,B

 

NEW QUESTION 34
Examine the structure of the BOOKS_ TRANSACTIONS table:
Examine the SQL statement:
Which statement is true about the outcome?

• A. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
• B. It displays details for only members A101and A102 who have borrowed before today with RM as TRANSACTION_TYPE.
• C. 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.
• D. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.

Answer: A

 

NEW QUESTION 35
Evaluate the following statement.

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

• A. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.
• B. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
• C. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
• D. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.

Answer: B

Explanation:
References:
http://psoug.org/definition/WHEN.htm

 

NEW QUESTION 36
Which three are true about dropping columns from a table?

• A. A column can be removed only if it contains no data.
• B. A column must be set as unused before it is dropped from a table.
• C. A primary key column cannot be dropped.
• D. A column that is referenced by another column in any other table cannot be dropped.
• E. Multiple columns can be dropped simultaneously using the ALTER TABLE command.
• F. A column drop is implicitly committed

Answer: D,E,F

 

NEW QUESTION 37
Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?

• A. fourth normal form
• B. third normal form
• C. first normal form
• D. second normal form

Answer: D

Explanation:
Explanation/Reference:
References:
https://blog.udemy.com/database-normal-forms/

 

NEW QUESTION 38
View the exhibit and examine the structure of ORDERSand CUSTOMERStables.

Which INSERT statement should be used to add a row into the ORDERStable for the customer whose CUST_LAST_NAMEis Robertsand CREDIT_LIMITis 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

• A. INSERT INTO orders
  VALUES (1,'10-mar-2007', 'direct',
  ( SELECT customer_id
  FROM customers
  WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
• B. INSERT INTO orders (order_id, order_date, order_mode,
  ( SELECT customer_id
  FROM customers
  WHERE cust_last_name='Roberts' AND credit_limit=600), order_total)
  VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
• C. INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600) VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
• D. INSERT INTO orders (order_id, order_date, order_mode,
  ( SELECT customer_id
  FROM customers
  WHERE cust_last_name='Roberts' AND credit_limit=600), order_total)
  VALUES (1,'10-mar-2007', 'direct', &&customer_id, 1000);

Answer: A

 

NEW QUESTION 39
View the Exhibit and examine the structure of the PORDUCT_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?

• A. SELECT product_idFROM product_informationWHERE UPPER(product_name) IN ('LASERPRO',
  'CABLE');
• B. SELECT UPPER(product_name)FROM product_information;
• C. SELECT UPPER(product_name)FROM product_informationWHERE product_id = 2254;
• D. SELECT product_id, UPPER(product_name)FROM product_informationWHERE
  UPPER(product_name) = 'LASERPRO' OR list_price > 1000;

Answer: A

 

NEW QUESTION 40
Examine the description of the EMP_DETAILS table given below:

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL TABLE?

• A. You cannot add a new column to the table with LONG as the data type.
• B. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
• C. An EMP_IMAGE column can be included in the GROUP BY clause.
• D. An EMP_IMAGE column cannot be included in the ORDER BY clause.

Answer: A,D

 

NEW QUESTION 41
Which two statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose two.)

• A. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
• B. The HAVING clause can be used with aggregate functions in subqueries.
• C. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
• D. The WHERE clause can be used to exclude rows after dividing them into groups.
• E. The WHERE clause can be used to exclude rows before dividing them into groups.

Answer: B,D

 

NEW QUESTION 42
Examine the structure proposed for the TRANSACTIONS table:

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

• A. The CUST_CREDIT_LIMIT column would be able to store decimal values.
• B. The TRANS_VALIDITY column would have a maximum size of one character.
• C. The CUST_STATUS column would store exactly one character.
• D. The TRANS_VALIDITY column would give an error.
• E. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
• F. The CUST_STATUS column would give an error.

Answer: C,D

 

NEW QUESTION 43
View the Exhibit and examine the structure of the CUSTOMERS table.

Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.
Customers with no credit limit should have "Not Available" displayed.
Which SQL statement would produce the required result?

• A. SELECT NVL (cust_credit_limit, 'Not Available')*.15 "NEW CREDIT" FROM customers
• B. SELECT TO_CHAR(NVL(cust_credit_limit*.15), 'Not Available')) "NEW CREDIT" FROM customers
• C. SELECT NVL (cust_credit_limit*.15, 'Not Available') "NEW CREDIT" FROM customers
• D. SELECT NVL (TO_CHAR(cust_credit_limit*.15), 'Not Available') "NEW CREDIT" FROM customers

Answer: C

 

NEW QUESTION 44
Examine the structure of the CUSTOMERS table: (Choose two.)

CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?

• A. Right outer-join with self-join
• B. Subquery
• C. Left outer-join with self-join
• D. Full outer-join with self-join
• E. Self-join

Answer: B,E

 

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

• A. PUBLIC should be replaced with specific usernames.
• B. ALL should be replaced with a list of specific privileges.
• C. WITH GRANT OPTION should be added to the statement.
• D. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.

Answer: D

 

NEW QUESTION 46
The user SCOTTwho is the owner of ORDERSand ORDER_ITEMStables issues the following GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?

• A. WITH GRANT OPTIONshould be added to the statement.
• B. PUBLICshould be replaced with specific usernames.
• C. ALLshould be replaced with a list of specific privileges.
• D. Separate GRANTstatements are required for ORDERSand ORDER_ITEMS tables.

Answer: D

Explanation:
Explanation/Reference:
References:
http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html

 

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

• A. Roles are named groups of related privileges that can be granted to users or other roles.
• B. The REVOKE command can be used to remove privileges but not roles from other users.
• C. A role can be granted to PUBLIC.
• D. A role can be granted to itself.
• E. A user can be granted only one role at any point of time.

Answer: A,C

 

NEW QUESTION 48
You must write a query that prompts users for column names and conditions every time it is executed. (Choose the best answer.) The user must be prompted only once for the table name.
Which statement achieves those objectives?

• A. SELECT &col1, &col2 FROM &&tableWHERE &condition = &cond;
• B. SELECT &col1, '&col2'FROM &tableWHERE &&condition = '&cond';
• C. SELECT &col1, &col2 FROM &&tableWHERE &condition = &&cond
• D. SELECT &col1, &col2 FROM "&table"WHERE &condition = &cond;

Answer: A

 

NEW QUESTION 49
View the Exhibit and examine the structure of CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?

• A. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
• B. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
• C. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
• D. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT"FROM customers;

Answer: B

 

NEW QUESTION 50
See the Exhibit and examine the structure of the PROMOTIONS table:

Using the PROMOTIONS table,
you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:

What would be the outcome?

• A. It generates an error because NULL cannot be specified as a return value.
• B. It generates an error because CASE cannot be used with group functions.
• C. It generates an error because multiple conditions cannot be specified for the WHEN clause.
• D. It executes successfully and gives the required result.

Answer: D

Explanation:
Explanation
CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

 

NEW QUESTION 51
You execute this command:
TRUNCATE TABLE depts;
Which two are true?

• A. A Flashback TABLE statement can be used to retrieve the deleted data.
• B. It retains the integrity constraints defined on the table.
• C. A ROLLBACK statement can be used to retrieve the deleted data.
• D. It drops any triggers defined on the table.
• E. It retains the indexes defined on the table.
• F. It always retains the space used by the removed rows

Answer: B,E

 

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

• A. Use the escape character to negate the single quotation mark within the literal character string in the SELECT clause
• B. Remove the single quotation marks enclosing the character literal string in the SELECT clause
• C. Use the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECT clause
• D. Enclose the character literal string in the SELECT clause within double quotation marks

Answer: C

Explanation:
Explanation
References:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm

 

NEW QUESTION 53
......

Get ready to pass the 1z1-071 Exam right now using our Oracle PL/SQL Developer Certified Associate  Exam Package: https://www.testsimulate.com/1z1-071-study-materials.html

A fully updated 2021 1z1-071 Exam Dumps exam guide from training expert TestSimulate: https://drive.google.com/open?id=1kcsQfzrVIt3lGIuwguouIOBgDLL8f8Ck