[Nov-2021] Pass Oracle 1z0-071 Tests Engine pdf - All Free Dumps
Oracle Database SQL Practice Tests 2021 | Pass 1z0-071 with confidence!
NEW QUESTION 105
Examine this list of queries:
Which two statements are true?
- A. 3 returns an error.
- B. 2 returns the value 20.
- C. 2 and 3 give the same result.
- D. 1 and 4 give different results.
- E. 1 and 4 give the same result.
Answer: B,E
NEW QUESTION 106
Evaluate the following two queries:
Which statement is true regarding the above two queries?
- A. Performance would degrade in query 2.
- B. There would be no change in performance.
- C. Performance would improve in query 2.
- D. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.
Answer: B
Explanation:
Explanation
NEW QUESTION 107
BOOK_SEQ is an existing sequence in your schema.
Which two CREATE TABLE commands are valid?
- A. CREATE TABLE bookings (
bk_id NUMBER(4) DEFAULT book_seq.NEXTVAL PRIMARY KEY,
start_date DATE DEFAULT SYSDATE,
end_date DATE DEFAULT SYSDATE NOT NULL); - B. CREATE TABLE bookings (
bk_id NUMBER(4) NOT NULL DEFAULT book_seq.CURRVAL,
start_date DATE NOT NULL,
end_date DATE DEFAULT SYSDATE); - C. CREATE TABLE bookings (
bk_id NUMBER(4) NOT NULL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE DEFAULT SYSDATE); - D. CREATE TABLE bookings ( bk_id NUMBER(4),
start_date DATE DEFAULT SYSDATE,
end_date DATE DEFAULT (end_date >= start_date)); - E. CREATE TABLE bookings (
bk_id NUMBER(4) DEFAULT book_seq.CURRVAL,
start_date DATE DEFAULT SYSDATE,
end_date DATE DEFAULT start date);
Answer: A,C
NEW QUESTION 108
Examine the structure of the MEMBERS table: (Choose the best answer.)
Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC; What would be the result execution?
- A. It fails because a column alias cannot be used in the ORDER BY clause.
- B. It displays all cities in ascending order, within which the last names are further sorted in descending order.
- C. It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
- D. It displays all cities in descending order, within which the last names are further sorted in descending order.
Answer: B
NEW QUESTION 109
The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues this GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What must be done to fix the statement?
- A. ALL should be replaced with a list of specific privileges.
- B. WITH GRANT OPTION should be added to the statement.
- C. PUBLIC should be replaced with specific usernames.
- D. Separate GRANT statements are required for the ORDERS and ORDER_ITEMS tables.
Answer: D
Explanation:
References:
http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html
NEW QUESTION 110
Which two are true about scalar subquery expressions?
- A. They can return at most one row.
- B. .You must enclose them in parentheses.
- C. You cannot correlate them with a table in the parent statement
- D. You can use them as a default value for a column.
- E. They can return two columns.
Answer: B,C
NEW QUESTION 111
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*.15, 'Not Available') "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, 'Not Available')*.15 "NEW CREDIT" FROM customers;
- D. SELECT NVL(TO_CHAR(cust_credit_limit*.15), 'Not Available') "NEW CREDIT" FROM customers;
Answer: A
NEW QUESTION 112
View the Exhibit and examine the structure of the EMP table 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_NAME column would be dropped provided it does not contain any data.
- B. The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.
- C. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.
- D. The FIRST_NAME column would be dropped provided at least one column remains in the table.
Answer: D
NEW QUESTION 113
View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?
- A. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.
- B. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
- C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
- D. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
Answer: D
NEW QUESTION 114
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?
- A. listing of customers who do not have a credit limit and were born before 1980
- B. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.
- C. finding the number of customers, in each city, who's marital status is 'married'.
- D. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
- E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
Answer: B,E
NEW QUESTION 115
View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)
In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator:
SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p ______________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN operations can be used to obtain the required output?
- A. RIGHT OUTER JOIN
- B. FULL OUTER JOIN
- C. LEFT OUETR JOIN
- D. JOIN
Answer: B,C
NEW QUESTION 116
Evaluate the following CREATE TABLE command:
Which statement is true regarding the above SQL statement?
- A. It would execute successfully and only ORD_ITM_IDX index would be created.
- B. It would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.
- C. It would give an error because the USING INDEX clause cannot be used on a composite primary.
- D. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.
Answer: A
NEW QUESTION 117
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 members who have borrowed before today with RM as TRANSACTION_TYPEand the details for members A101 or A102.
- C. It displays details for members who have borrowed before today's date with either RM as TRANSACTION_TYPEor MEMBER_IDas A101 and A102.
- D. It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE.
Answer: B
NEW QUESTION 118
Which three are true about privileges and roles?
- A. PUBLIC acts as a default role granted to every user in a database.
- B. System privileges always set privileges for an entire database.
- C. PUBLIC can be revoked from a user.
- D. A role is owned by the user who created it.
- E. A user has all object privileges for every object in their schema by default.
- F. All roles are owned by the SYS schema.
- G. A role can contain a combination of several privileges and roles.
Answer: A,E,G
NEW QUESTION 119
Examine this SELECT statement and view the Exhibit to see its output:
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? (Choose two.)
- A. The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
- B. In the second column, 'c' indicates a check constraint.
- C. The STATUS column indicates whether the table is currently in use.
- 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: B,D
NEW QUESTION 120
View the Exhibit and examine the description for the SALES and CHANNELS tables.
You issued this SQL statement:
Which statement is true regarding the result? (Choose the best answer.)
- A. The statement will fail because a subquery cannot be used in a VALUES clause.
- B. The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.
- C. The statement will execute and a new row will be inserted in the SALES table.
- D. The statement will fail because the VALUES clause is not required with the subquery.
Answer: C
NEW QUESTION 121
......
Get instant access to 1z0-071 practice exam questions: https://drive.google.com/open?id=1TYcq-1JBLVoq5WbwS1chujLryhNel5LZ
Online Exam Practice Tests with detailed explanations!: https://www.actualtestsit.com/Oracle/1z0-071-exam-prep-dumps.html