1z0-071 Study Guide Brilliant 1z0-071 Exam Dumps PDF
View 1z0-071 Exam Question Dumps With Latest Demo
NEW QUESTION # 40
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.
INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id,
- A. (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); - B. 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);
INSERT INTO orders (order_id, order_date, order_mode, - C. (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);
INSERT INTO orders - D. VALUES (1,'10-mar-2007', 'direct',
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
INSERT INTO orders (order_id, order_date, order_mode,
Answer: D
NEW QUESTION # 41
View the exhibit and examine the ORDERStable.
The ORDERStable contains data and all orders have been assigned a customer ID. Which statement would add a NOTNULLconstraint to the CUSTOMER_IDcolumn?
- A. ALTER TABLE orders
ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL; - B. ALTER TABLE orders
ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id); - C. ALTER TABLE orders
MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id); - D. ALTER TABLE orders
MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
Answer: C
NEW QUESTION # 42
You execute the following commands:
SQL > DEFINE hiredate = '01-APR-2011'
SQL >SELECT employee_id, first_name, salary
FROM employees
WHERE hire_date > '&hiredate'
AND manager_id > &mgr_id;
For which substitution variables are you prompted for the input?
- A. only 'mgr_id'
- B. both the substitution variables ''hiredate' and 'mgr_id'.
- C. only hiredate'
- D. none, because no input required
Answer: A
NEW QUESTION # 43
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,
- C. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;
- D. join_date;
SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
Answer: D
NEW QUESTION # 44
View the Exhibit and examine the structure of the EMPtable which is not partitioned and not an index-organized table. (Choose two.)
Evaluate this SQL statement:
ALTER TABLE emp
DROP COLUMN first_name;
Which two statements are true?
- A. The FIRST_NAMEcolumn would be dropped provided at least one column remains in the table.
- B. The FIRST_NAMEcolumn would be dropped provided it does not contain any data.
- C. The drop of the FIRST_NAMEcolumn can be rolled back provided the SET UNUSEDoption is added to the SQL statement.
- D. The FIRST_NAMEcolumn can be dropped even if it is part of a composite PRIMARY KEYprovided the CASCADEoption is added to the SQL statement.
Answer: A,D
NEW QUESTION # 45
Examine the data in the CUST_NAMEcolumn of the CUSTOMERStable:
You want to display the CUST_NAMEvalues where the last name starts with Mcor MC.
Which two WHERE clauses give the required result? (Choose two.)
WHERE SUBSTR(cust_name, INSTR(cust_name, '') + 1) LIKE 'Mc%'
- A. WHERE SUBSTR(cust_name, INSTR(cust_name, '') + 1) LIKE 'Mc%' OR 'MC%'
- B. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, '') + 1)) IN ('MC%', 'Mc%)
- C. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, '') + 1)) LIKE UPPER('MC%')
- D.
- E. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, '') + 1)) LIKE 'Mc%'
Answer: A,D
NEW QUESTION # 46
Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
-------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC".
Which query gives the required output?
- A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE
INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%'; - B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE
INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc'; - C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE
INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%'); - D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE
SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
Answer: A
NEW QUESTION # 47
You execute these commands:
CREATE TABLE customers (customer id INTEGER, customer name VARCHAR2 (20));
INSERT INTO customers VALUES (1,'Custmoer1 ');
SAVEPOINT post insert;
INSERT INTO customers VALUES (2, 'Customer2 ');
<TODO>
SELECTCOUNT (*) FROM customers;
Which two, used independently, can replace <TODO> so the query retums 1?
- A. CONOIT TO SAVEPOINT post_ insert;
- B. ROLLBACK;
- C. ROLLEBACK TO post_ insert;
- D. COMMIT;
- E. ROLIBACK TO SAVEPOINT post_ insert;
Answer: C,E
NEW QUESTION # 48
In the PROMOTIONS table, the PROMO_BEGIN_DATEcolumn is of data type DATEand the default date format is DD-MON-RR.
Which two statements are true about expressions using PROMO_BEGIN_DATEcontained a query? (Choose two.)
- A. PROMO_BEGIN_DATE - SYSDATEwill return a number.
- B. TO_DATE(PROMO_BEGIN_DATE * 5)will return a date.
- C. TO_NUMBER(PROMO_BEGIN_DATE)- 5 will return a number.
- D. PROMO_BEGIN_DATE - SYSDATEwill return an error.
- E. PROMO_BEGIN_DATE- 5 will return a date.
Answer: A,E
NEW QUESTION # 49
Examine the command to create the BOOKS table.
The BOOK_IDvalue 101does not exist in the table.
Examine the SQL statement:
Which statement is true?
- A. It executes successfully only if the PUBLISHER_IDcolumn name is added to the columns list and NULL is explicitly specified in the INSERTstatement.
- B. It executes successfully only if the PUBLISHER_IDcolumn name is added to the columns list in the INSERTstatement.
- C. It executes successfully and the row is inserted with a rule PUBLISHER_ID.
- D. It executes successfully only if NULLis explicitly specified in the INSERTstatement.
Answer: C
NEW QUESTION # 50
View the exhibit and examine the data in ORDERS_MASTERand MONTHLY_ORDERStables.
ORDERS_MASTER
ORDER_TOTAL
ORDER_ID
1 1000
2 2000
3 3000
4
MONTHLY_ORDERS
ORDER_TOTAL
ORDER_ID
2 2500
3
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?
- A. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2, 3 and 4.
- B. The ORDERS_MASTERtable would contain the ORDER_IDs1 and 2.
- C. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 4.
- D. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 3.
Answer: C
Explanation:
Explanation/Reference:
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
NEW QUESTION # 51
Which two statements are true about Oracle synonyms?
- A. Users must have the required privileges on the underlying objects to use public synonyms
- B. Synonyms cannot be created for sequences.
- C. Users must have the DBA role to create public synonyms.
- D. Synonyms cannot be created for synonyms.
- E. Synonyms can be created for roles.
- F. Synonyms can be created for packages.
Answer: A,F
NEW QUESTION # 52
Which two statements are true regarding the COUNT function? (Choose two.)
- A. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
- B. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column.
- C. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any column.
- D. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.
- E. It can only be used for NUMBER data types.
Answer: B,C
NEW QUESTION # 53
Examine the structure of the MEMBERS table:
Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC; What would be the result execution? (Choose the best answer.)
- A. It fails because a column alias cannot be used in the ORDER BY clause.
- B. It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
- C. It displays all cities in ascending order, within which the last names are further sorted in descending order.
- D. It displays all cities in descending order, within which the last names are further sorted in descending order.
Answer: C
NEW QUESTION # 54
View the exhibit and examine the structure of the STORES table.
You must display the NAME of stores 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_DATE in the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?
- A. SELECT name, concat(address||', '||city||', ',country) AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
- B. SELECT name, concat(address||', '||city||', ',country) AS full_address, start_date, property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,'01-JAN-2000') <=36;
- C. SELECT name, address||', '||city||', '||country AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
- D. SELECT name, concat(address||', '||city||', ', country) AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
Answer: D
NEW QUESTION # 55
Examine the description of the EMPLOYEES table:
Which statement will fail?
- A. SELECT department_id, COUNT(*)
FROM employees
WHERE department_id <> 90 HAVING COUNT(*) >= 3
GROUP BY department_id; - B. SELECT department_id, COUNT (*)
FROM employees
HAVING department_ id <> 90 AND COUNT(*) >= 3
GROUP BY department_id; - C. SELECT department_id, COUNT (*)
FROM employees
WHERE department_ id <> 90 AND COUNT(*) >= 3
GROUP BY department_id; - D. SELECT department_id, COUNT(*)
FROM employees
WHERE department_id <> 90 GROUP BY department_id
HAVING COUNT(*) >= 3;
Answer: C
NEW QUESTION # 56
View the exhibit and examine the structure of the EMPLOYEES table.
You want to select all employees having 100 as their MANAGER_ID manages and their manager.
You want the output in two columns: the first column should have the employee's manager's LAST_NAME and the second column should have the employee's LAST_NAME.
Which SQL statement would you execute?
- A. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eWHERE m.employee_id = e.manager_id AND e.manager_id=100
- B. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE e.manager_id=100;
- C. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE m.manager_id=100;
- D. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON e.employee_id = m.manager_idWHERE m.manager_id=100;
Answer: B
NEW QUESTION # 57
n the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'.
Evaluate the following query:
What would be the outcome?
- A. Abigail Pa
- B. An error message
- C. Abigail PA
- D. Abigail IS
Answer: A
NEW QUESTION # 58
Which statement is true regarding the INTERSECT operator?
- A. The number of columns and data types must be identical for all SELECT statements in the query.
- B. It ignores NULLs.
- C. Reversing the order of the intersected tables alters the result.
- D. The names of columns in all SELECT statements must be identical.
Answer: A
Explanation:
INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.
The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
NEW QUESTION # 59
......
Free 1z0-071 Test Questions Real Practice Test Questions: https://www.actual4labs.com/Oracle/1z0-071-actual-exam-dumps.html
1z0-071 Dumps Updated Jan 29, 2024 WIith 305 Questions: https://drive.google.com/open?id=1Vvs_c4R77woyP5KynIjxeJkjUE36Aer3