Updated May-2024 Pass 1z0-071 Exam - Real Practice Test Questions
Download Free Oracle 1z0-071 Real Exam Questions
NEW QUESTION # 117
View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.
Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE JOB_ID = 'SA_MAN'
-------------------------------------
SELECT EMPLOYEE_ID
FROM JOB_HISTORY
WHERE JOB_ID = 'SA_MAN';
Choose two correct SET operators which would cause the query to return the desired result.
- A. INTERSECT
- B. MINUS
- C. UNION
- D. UNION ALL
Answer: C,D
NEW QUESTION # 118
Which three are true about multitable INSERTstatements? (Choose three.)
- A. They can insert each computed row into more than one table.
- B. They can be performed on remote tables.
- C. They can be performed on external tables using SQL* Loader.
- D. They can be performed on views.
- E. They can be performed on relational tables.
- F. They can be performed only by using a subquery.
Answer: A,D,E
NEW QUESTION # 119
Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
- A. SELECT last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 * NVL (monthly_ commission _pct, 0)) AS annual _comp
- B. SELECT last _ name, (monthly _ salary * 12) + (monthly_ commission _ pct * 12) AS FROM employees:
- C. SELECT last _ NAME (monthly_ salary + monthly _commission _ pct) * 12 AS annual_ comp FROM employees;
- D. select last _ name, (monthly_ salary * 12) + (monthly_ salary * 12 *monthly_ commission_ pct) AS annual_ camp FROM employees
Answer: A
NEW QUESTION # 120
Which two are true about dropping columns from a table?
- A. A column can be removed only if it contains no data.
- B. Multiple columns can be dropped simultaneously using the ALTER TABLE command.
- C. A column must be set as unused before it is dropped from a table.
- D. A primary key column cannot be dropped.
- E. A column that is referenced by another column in any other table cannot be dropped.
- F. A column drop is implicitly committed.
Answer: B,D
NEW QUESTION # 121
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?
- A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1
- B. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
- C. SELECT promo_category, DISTINCT promo_cost FROM promotions
- D. SELECT DISTINCT promo_cost, promo_category FROM promotions
Answer: A
NEW QUESTION # 122
Which two statements are true regarding constraints? (Choose two.)
- A. A foreign key cannot contain NULL values.
- B. You can have more than one column in a table as part of a primary key.
- C. A constraint is enforced only for an INSERT operation on a table.
- D. A column with the UNIQUE constraint can store NULLS.
Answer: B,D
NEW QUESTION # 123
Examine the description of the PRODUCT_DETAILS table:
Which two statements are true?
- A. EXPIRY_DATE cannot be used in arithmetic expressions.
- B. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.
- C. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
- D. PRODUCT_NAME cannot contain duplicate values.
- E. PRODUCT_ID can be assigned the PRIMARY KEY constraint.
- F. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.
Answer: A,D
NEW QUESTION # 124
Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)
- A. Finding the lowest value
- B. Finding the quotient
- C. Subtraction
- D. Raising to a power
- E. Addition
Answer: A,D,E
NEW QUESTION # 125
Examine this SELECT statement and view the Exhibit to see its output: (Choose two.)
SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status, FROM user_constraints WHERE table_name = 'ORDERS'; Which two statements are true about the output?
- A. The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
- B. The STATUS column indicates whether the table is currently in use.
- C. In the second column, 'c' indicates a check constraint.
- D. The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.
Answer: C,D
NEW QUESTION # 126
Examine the structure proposed for the TRANSACTIONStable:
Which two statements are true regarding the storage of data in the above table structure? (Choose two.)
- A. The TRANS_DATEcolumn would allow storage of dates only in the dd-mon-yyyy format.
- B. The CUST_CREDIT_VALUEcolumn would allow storage of positive and negative integers.
- C. The TRANS_VALIDITYcolumn would allow storage of a time interval in days, hours, minutes, and seconds.
- D. The CUST_STATUScolumn would allow storage of data up to the maximum VARCHAR2size of 4,000 characters.
Answer: B,C
NEW QUESTION # 127
The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8, 2).
Evaluate this SQL statement:
SELECT TO_CHAR(unit_price,'$9,999') FROM PRODUCT_INFORMATION;
Which two statements are true about the output?
- A. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236.
- B. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024.
- C. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #####
- D. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023.
- E. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.
Answer: B,C
NEW QUESTION # 128
Examine this partial command:
Which two clauses are required for this command to execute successfully?
- A. the access driver TYPE clause
- B. the LOCATION clause
- C. the DEFAULT DIRECTORY clause
- D. the ACCESS PARAMETERS clause
- E. the REJECT LIMIT clause
Answer: A,B
NEW QUESTION # 129
Which two are true about a SQL statement using SET operators such as UNION?
- A. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.
- B. The names and number of columns must be identical for all SELECT statements in the query.
- C. The number, but not names, of columns must be identical for all SELECT statements in the query.
- D. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.
- E. The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query.
Answer: D,E
NEW QUESTION # 130
Examine this data in the EMPLOYEES table:
Which statement will execute successfully?
SELECT dept_id, INSTR(last_name, 'A'), SUM(salary) FROM employees GROUP BY
- A. dept_id;
SELECT dept_id, MAX(last_name), SUM(salary) FROM employees GROUP BY dept_id; - B. dept_id;
- C. dept_id;
SELECT dept_id, STDDEV(last_name), SUM(salary) FROM employees GROUP BY - D. SELECT dept_id, LENGTH(last_name), SUM(salary) FROM employees GROUP BY
Answer: C
NEW QUESTION # 131
Examine this query:
SELECT employee_id,first_name,salary
FROM employees
WHERE hire_date>'&1';
Which two methods should you use to prevent prompting for a hire date value when this query is executed?
- A. Use the DEFINE command before executing the query.
- B. Use the UNDEFINE command before executing the query.
- C. Execute the SET VERIFY ON command before executing the query.
- D. Store the query in a script and pass the substitution value to the script when executing it.
- E. Replace'&1' with'&&1' in the query.
- F. Execute the SET VERIFY OFF command before executing the query.
Answer: A,D
NEW QUESTION # 132
View the exhibit and examine the description of the PRODUCT_INFORMATIONtable.
Which SQL statement would retrieve from the table the number of products having LIST_PRICEas NULL?
- A. SELECT COUNT (list_price)
FROM product_information
WHERE list_price is NULL - B. SELECT COUNT (NVL(list_price, 0))
FROM product_information
WHERE list_price is NULL - C. SELECT COUNT (DISTINCT list_price)
FROM product_information
WHERE list_price is NULL - D. SELECT COUNT (list_price)
FROM product_information
WHERE list_price i= NULL
Answer: B
NEW QUESTION # 133
Examine the description of the CUSTOMERStable:
You want to display details of all customers who reside in cities starting with the letter D followed by at least two characters.
Which query can be used?
- A. SELECT * FROM customers WHERE city = 'D_%';
- B. SELECT * FROM customers WHERE city LIKE 'D_%';
- C. SELECT * FROM customers WHERE city LIKE 'D_';
- D. SELECT * FROM customers WHERE city = '%D_';
Answer: B
NEW QUESTION # 134
Examine the description of the EMPLOYEEStable:
Which query is valid?
- A. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;
- B. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id,
join_date; - C. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;
- D. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
Answer: D
NEW QUESTION # 135
Examine the description of the EMPLOYEES table:
NLS_DATE_FORMAT is set to DD-MON-YY.
Which query requires explicit data type conversion?
- A. SELECT join date + 20 FROM employees;
- B. SELECT SUBSTR(join date, 1, 2)- 10 FROM employees;
- C. SELECT join date FROM employees where join date > *10-02-2018*;
- D. SELECT join date 11.'11 salary FROM employees;
- E. SELECT salary + 120.50 FROM employees;
Answer: C
NEW QUESTION # 136
Which two tasks can be performed by using Oracle SQL statements?
- A. starting up a database instance
- B. querying data from tables across databases
- C. executing operating system (OS) commands in a session
- D. connecting to a database instance
- E. changing the password for an existing database
Answer: B,C
NEW QUESTION # 137
Which two queries return the string Hello! we're ready?
- A. SELECT q'! Hello! We're ready! 'FROM DUAL;
- B. SELECT "Hello! We're ready "FROM |DUAL;
- C. SELECT 'Hello! we\ re ready' ESCAPE'N'FROMDUAL:
- D. SELECT 'Hello! We're ready' FROM DUAL;
- E. SELECT q'[Hello! We're ready]'FROM DUAL;
Answer: A,E
NEW QUESTION # 138
The PRODUCTS table has the following structure.
Evaluate the following two SQL statements:
SQL>SELECT prod_id, NVL2 (prod_expiry_date, prod_expiry_date + 15, ' ') FROM products; SQL>SELECT prod_id, NVL (prod_expiry_date, prod_expiry_date + 15) FROM products; Which statement is true regarding the outcome?
- A. Only the first SQL statement executes successfully
- B. Both the statements execute and give different results
- C. Both the statements execute and give the same result
- D. Only the second SQL statement executes successfully
Answer: B
Explanation:
Explanation
Using the NVL2 Function
The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.
Syntax
NVL2(expr1, expr2, expr3)
In the syntax:
Expr1 is the source value or expression that may contain a null
Expr2 is the value that is returned if expr1 is not null
Expr3 is the value that is returned if expr1 is null
NEW QUESTION # 139
Which two statements are true regarding non equijoins?
- A. The Oracle join syntax can be used.
- B. The USING clause can be used.
- C. Table aliases must be used.
- D. The SQL:1999 compliant ANSI join syntax must be used.
- E. The ON clause can be used.
Answer: A,E
NEW QUESTION # 140
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 (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);
- B. INSERT INTO orders
VALUES (1,'10-mar-2007', 'direct',
( SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), 1000); - C. 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); - 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: B
NEW QUESTION # 141
......
1z0-071 Dumps 100 Pass Guarantee With Latest Demo: https://www.exam4free.com/1z0-071-valid-dumps.html
Pass Your Exam With 100% Verified 1z0-071 Exam Questions: https://drive.google.com/open?id=1oZi0zodH62E2pmP5p5z1V7wZgLoQvqa2
