1z0-071 Certification – Valid Exam Dumps Questions Study Guide! (Updated 305 Questions)
1z0-071 Dumps are Available for Instant Access using Actual4Dumps
Passing this Oracle exam means that you are on the way of getting the following credentials:
- Oracle Database SQL Certified Associate
- Oracle Database 12c Administrator Certified Associate;
Completing 1Z0-071 is enough to obtain the first certificate of the associate level, while for gaining the second one in the Oracle Database 12c Administrator track, passing the 1Z0-062 exam is a necessity. Anyway, having any of these certifications on your CV will show your employer your ability to apply SQL knowledge for the Oracle Database server maintenance.
NEW QUESTION 15
Examine the description of the PRODUCTS table:
Which three queries use valid expressions?
- A. SELECT product_id,unit_price || "Discount", unit_price + surcharge-discount FROM products;
- B. SELECT produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;
- C. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
- D. SELEGT product_id, unit_price, unit_price + surcharge FROM products;
- E. SPLECT product_id, expiry_date * 2 FROM products;
- F. SELECT ptoduct_id, (expiry_date-delivery_date) * 2 FROM products;
Answer: C,D,F
NEW QUESTION 16
View the exhibit and examine the structure of the STOREStable.
You want to display the NAME of the store along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.
The stores displayed must have START_DATEin the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?
SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,
- A. start_date,
property_price, property_price*115/100
FROM stores
WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
SELECT name, address||','||city||','||country AS full_address, - B. start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
<=36; - C. start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;
SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, - D. start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
<=36;
SELECT name, concat (address||','| |city| |', ', country) AS full_address,
Answer: B
NEW QUESTION 17
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
- A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
- B. It executes successfully but ignores the ORDER BYclause because it is not located at the end of the compound statement.
- C. It produces an error because positional notation cannot be used in the ORDER BYclause with SET operators.
- D. It produces an error because the ORDER BYclause should appear only at the end of a compound query-that is, with the last SELECTstatement.
Answer: D
NEW QUESTION 18
View the Exhibit and examine the structure of the CUSTOMERS table.
You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10,000.
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE 'A%' OR
UPPER (cust_last_name) LIKE 'B%' OR UPPER (cust_last_name) LIKE 'C%')
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER (cust_last_name) BETWEEN 'A' AND 'C'
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?
- A. Only the second query gives the correct result
- B. Both execute successfully but do not give the required result
- C. Both execute successfully and give the same result
- D. Only the first query gives the correct result
Answer: D
NEW QUESTION 19
Which two statements are true about the COUNT function?
- A. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
- B. COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.
- C. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.
- D. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column
- E. It can only be used for NUMBER data types.
Answer: B,D
NEW QUESTION 20
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.)
- A. ORDER BY 2,1
- B. ORDER BY CUST_NO
- C. ORDER BY 2, cust_id
- D. ORDER BY "CUST_NO"
- E. ORDER BY "Last name"
Answer: A,C,E
Explanation:
Explanation
Using the ORDER BY Clause in Set Operations
-The ORDER BY clause can appear only once at the end of the compound query.
-Component queries cannot have individual ORDER BY clauses.
-The ORDER BY clause recognizes only the columns of the first SELECT query.
-By default, the first column of the first SELECT query is used to sort the output in an ascending order.
NEW QUESTION 21
Examine the structure of the BOOKS_TRANSACTIONS table:
Examine the SQL statement:
Which statement is true about the outcome?
- A. It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE.
- B. It displays details for members who have borrowed before today's date with either RM as TRANSACTION_TYPEor MEMBER_IDas A101 and A102.
- C. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPEand the details for members A101 or A102.
- D. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
Answer: C
NEW QUESTION 22
View the exhibit and examine the structure in ORDERSand ORDER_ITEMStables.
You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATEVIEWstatement would create the views successfully?
- A. CREATE OR REPLACE VIEW ord_vu (order_id, order_date)
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
" NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date; - B. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
" NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date; - C. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date; - D. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) ||
" NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date
WHITH CHECK OPTION;
Answer: B
NEW QUESTION 23
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. Do not enclose the character literal string in the SELECTclause within the single quotation marks.
- B. Enclose the literal character string in the SELECTclause within the double quotation marks.
- C. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.
- D. Use escape character to negate the single quotation mark inside the literal character string in the SELECTclause.
Answer: C
Explanation:
Explanation/Reference:
References:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm
NEW QUESTION 24
View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)
You executed this SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result? (Choose two.)
- A. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
- B. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
- C. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
- D. The values in all columns would be returned in descending order.
- E. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
Answer: A,B
NEW QUESTION 25
Examine the description of the PROMOTIONStable:
You want to display the unique promotion costs in each promotion category.
Which two queries can be used? (Choose two.)
- A. promotions ORDER BY 1;
SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1; - B. SELECT DISTINCT promo_cost ||' in '|| DISTINCT promo_category FROM promotions
- C. ORDER BY 1;
SELECT promo_cost, promo_category FROM promotions ORDER BY 1; - D. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM
- E. SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2;
Answer: A,E
NEW QUESTION 26
Examine the description of the CUSTOMERS table:
Which three statements will do an implicit conversion?
- A. SELECT * FROM customers WHERE customer id = '0001';
- B. SELECT FROM customers WHERE insert date = '01-JAN-19';
- C. SELECT * FROM customers WHERE customer_ id = 0001;
- D. SELECT. FRON customers WE TO DATE (Insert _ date) = DATE '2019-01-01';
- E. SELECT. FROM customers WHERE insert_ date = DATE *2019-01-01';
- F. SELECT * FROM customers WHERE TO_ CHAR (customer_ id) = '0001';
Answer: A,B,D
NEW QUESTION 27
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 can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
- B. An EMP_IMAGE column can be included in the GROUP BY clause.
- C. An EMP_IMAGE column cannot be included in the ORDER BY clause.
- D. You cannot add a new column to the table with LONG as the data type.
Answer: C,D
NEW QUESTION 28
Which three statements are true about dropping and unused columns in an Oracle database?
- A. An unused column's space is reclaimed automatically when the row containing that column is next queried.
- B. An unused column's space is reclaimed automatically when the block containing that column is next queried.
- C. Partition key columns cannot be dropped.
- D. A drop column command can be rolled back.
- E. A primary key column referenced by another column as a foreign key can be dropped if using the cascade option.
- F. A column that is set to unused still counts towards the limit of 1000 columns per table.
Answer: C,E,F
NEW QUESTION 29
View the Exhibit and examine the structure of the ORDERS table.
You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.
Which query would give you the desired result?
- A. SELECT order_id, order_date FROM orders
WHERE order_date >
ANY
(SELECT order_date FROM orders WHERE customer_id = 101); - B. SELECT order_id, order_date FROM orders
WHERE order_date > IN
(SELECT order_date FROM orders WHERE customer_id = 101); - C. SELECT order_id, order_date FROM orders
WHERE order_date > ALL
(SELECT order_date FROM orders WHERE customer_id = 101); - D. SELECT order_id, order_date FROM orders
WHERE order_date > ALL
(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
Answer: C
NEW QUESTION 30
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?
- A. It displays 5 percent of the products with the lowest amount sold.
- B. It displays the first 5 percent of the rows from the SALEStable.
- C. It results in an error because the ORDER BYclause should be the last clause.
- D. It displays 5 percent of the products with the highest amount sold.
Answer: A
Explanation:
Explanation/Reference:
References:
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
NEW QUESTION 31
Which three are true about the MERGEstatement? (Choose three.)
- A. It can update, insert, or delete rows conditionally in multiple tables.
- B. It can use views to produce source rows.
- C. It can combine rows from multiple tables conditionally to insert into a single table.
- D. It can merge rows only from tables.
- E. It can use subqueries to produce source rows.
- F. It can update the same row of the target table multiple times.
Answer: A,B,E
Explanation:
Explanation/Reference: https://www.oracletutorial.com/oracle-basics/oracle-merge/
NEW QUESTION 32
Examine this business rule:
Each student can work on multiple projects and each project can have multiple students. _ You must design an Entity Relationship (ER) model for optimal data storage and allow for generating reports in this format:
STUDEN_ID FIRST_NAMK LASTNAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true?
- A. An associative table must be created with a composite key of student_id and project_id, which is the foreign key linked to the Stand projects entitles.
- B. PORJECTI_ID must be the primary key In the projects entity and foreign key in the students The ER must have a one-to-many relationship between the students and projects entitles.
- C. The ER must have a many-to-many relationship between the students and projects entitles tb.it must be resolved Into one-to-many relationships.
- D. STUDENT_ID must be the primary key In the students entity and foreign key In the projects entity.
Answer: A
NEW QUESTION 33
Which two statements are true regarding a SAVEPOINT? (Choose two.)
- A. Rolling back to a SAVEPOINT can undo a TRUNCATE statement
- B. Only one SAVEPOINT may be issued in a transaction
- C. Rolling back to a SAVEPOINT can undo a DELETE statement
- D. A SAVEPOINT does not issue a COMMIT
- E. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement
Answer: C,D
Explanation:
Explanation/Reference:
NEW QUESTION 34
......
Oracle 1z0-071 Exam Practice Test Questions: https://www.actual4dumps.com/1z0-071-study-material.html
1z0-071 Dumps 2021 - New Oracle 1z0-071 Exam Questions: https://drive.google.com/open?id=1OKFSQS3aZqAezoZRO2_T90l8SKSxGyaw