Jan-2022 Realistic 1z1-071 Exam Dumps with Accurate & Updated Questions
1z1-071 Exam Dumps - PDF Questions and Testing Engine
For more info, read the reference:
Oracle Database SQL Exam Number: 1Z0-071
NEW QUESTION 109
Which three are true about multitable INSERT statements?
- A. They can be performed on views.
- B. They can be performed on remote tables.
- C. They can be performed only by using a subquery.
- D. They can be performed on external tables using SQL* Loader.
- E. They can be performed on relational tables.
- F. They can insert each computed row into more than one table.
Answer: C,D,E
NEW QUESTION 110
Which two are true about multiple table INSERT statements?
- A. They can transform a row from a source table into multiple rows in a target table.
- B. The conditional INSERT ALL statement inserts rows into a single table by aggregating source rows.
- C. The conditional INSERT FIRST statement always inserts a row into a single table.
- D. They always use subqueries.
- E. The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables.
Answer: A,D
NEW QUESTION 111
Examine the structure of the EMPLOYEES table.
You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.
Which query would retrieve the required result?
- A. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;
- B. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;
- C. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);
- D. (SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;
Answer: B
NEW QUESTION 112
Which two statements are true regarding constraints? (Choose two.)
- A. Only the primary key can be defined at the column and table levels.
- B. A table can have only one primary key but multiple foreign keys.
- C. Both primary key and foreign key constraints can be defined at both column and table levels.
- D. A table can have only one primary key and one foreign key.
- E. The foreign key and parent table primary key must have the same name.
Answer: B,C
NEW QUESTION 113
View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
- A. It shows COST_REMARK for all the promos in the promo category 'TV'.
- B. It shows COST_REMARK for all the promos in the table.
- C. It produces an error because the subquery gives an error.
- D. It produces an error because subqueries cannot be used with the CASE expression.
Answer: B
NEW QUESTION 114
View the Exhibit and examine the structure of the PRODUCT_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_id, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name) = 'LASERPRO' OR list_price > 1000; - B. SELECT UPPER(product_name)
FROM product_information; - C. SELECT UPPER(product_name)
FROM product_information
WHERE product_id = 2254; - D. SELECT product_id
FROM product_information
WHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');
Answer: D
NEW QUESTION 115
Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)
- A. DELETE <sequencename> would remove a sequence from the database.
- B. When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.
- C. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.
- D. When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.
- E. The numbers generated by an explicitly defined sequence can only be used to insert data in one table.
Answer: C,D
Explanation:
Explanation
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2012.htm#SQLRF00817
https://docs.oracle.com/cd/A84870_01/doc/server.816/a76989/ch26.htm
NEW QUESTION 116
Examine the description of the TRANSACTIONS table:
Which two SQL statements execute successfully?
- A. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions;
- B. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100 "DUES" FROM transactions;
- C. SELECT customer_id AS 'CUSTOMER-ID', transaction_date AS DATE, amount + 100 'DUES' FROM transactions;
- D. SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100
"DUES AMOUNT" FROM transactions; - E. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount +100 DUES FROM transactions;
Answer: A,E
NEW QUESTION 117
Examine the structure of the MEMBERS table:
You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?
- A. SELECT * FROM MEMBERS WHERE state LIKE 'A%';
- B. SELECT * FROM MEMBERS WHERE state LIKE 'A_';
- C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
- D. SELECT * FROM MEMBERS WHERE state LIKE '%A_';
Answer: B
NEW QUESTION 118
View the Exhibit and examine the details of PRODUCT_INFORMATION table.
You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;
Which statement is true regarding the execution of the query?
- A. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
- B. It would execute but the output would return no rows.
- C. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.
- D. It would execute and the output would display the desired result.
Answer: B
NEW QUESTION 119
View the Exhibit and examine the data in the PRODUCTS table. (Choose the best answer.)
You must display product names from the PRODUCTS table that belong to the
'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:
SQL > SELECT prod_name, prod_category, prod_min_price FROM products
Where prod_category LIKE '%Other%' AND (prod_min_price = 2000 OR prod_min_price
4000) AND prod_unit_of_measure <> ' ';
Which statement is true?
- A. It executes successfully but returns no result.
- B. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
- C. It generates an error because the condition specified for the PROD_CATEGORY column is not valid.
- D. It executes successfully and returns the required result.
Answer: A
NEW QUESTION 120
View the exhibit and examine the structure of the STOREStable.
STORES table
Name Null? Type
----------------- ----- -------------
STORE_ID NUMBER
NAME VARCHAR2(100)
ADDRESS VARCHAR2(200)
CITY VARCHAR2(100)
COUNTRY VARCHAR2(100)
START_DATE DATE
END_DATE DATE
PROPERTY_PRICE NUMBER
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?
- A. SELECT name, address||','||city||','||country AS full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (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/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36; - C. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date,
property_price, property_price*115/100
FROM stores
WHERE TO_NUMBER(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/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
<=36;
Answer: D
NEW QUESTION 121
Which three statements are true?
- A. A product can have a different unit price at different times.
- B. The SALES table has five foreign keys.
- C. The statement will fail if a row already exists in the SALES table for product 23.
- D. The statement will fail because subquery may not be I contained in a values clause.
- E. The statement will execute successfully and a new row will be inserted into the SALES table.
- F. A customer can exist in many countries.
Answer: A,B,E
NEW QUESTION 122
Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)
- A. Raising to a power
- B. Addition
- C. Finding the quotient
- D. Finding the lowest value
- E. Subtraction
Answer: A,B,D
NEW QUESTION 123
You execute the SQL statement:
What is the outcome?
- A. It fails because the condition for the CNAMES constraint is not valid.
- B. It succeeds and CITY can contain only 'SEATTLE' or null for all rows.
- C. It succeeds and an index is created for CITIZEN_ID.
- D. It fails because the NOT NULL and DEFAULT options cannot be combined for the same column.
Answer: D
NEW QUESTION 124
You want to return the current date and time from the user session, with a data type of TIMESTAMP WITH TIME ZONE.
Which function will do this?
- A. CURRENT DATE
- B. LOCALTIMESTAMP
- C. SYSDATE
- D. CURRENT_ TIMESTAMP
Answer: D
NEW QUESTION 125
View and Exhibit and examine the structure and data in the INVOICE table.
Which two statements are true regarding data type conversion in query expressions? (Choose two.)
- A. inv_date > '01-02-2008' : uses implicit conversion
- B. inv_no BETWEEN '101' AND '110' : uses implicit conversion
- C. inv_amt = '0255982' : requires explicit conversion
- D. CONCAT(inv_amt, inv_date) : requires explicit conversion
- E. inv_date = '15-february-2008' :uses implicit conversion
Answer: B,E
NEW QUESTION 126
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 referencing the PRODUCTS table.
The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Examine this 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?
- A. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
- B. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
- 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 selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
Answer: D
NEW QUESTION 127
The customers table has the following structure:
You need to write a query that does the following tasks:
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customers whose income level has a value should be considered.
3. Customers whose tax amount is null should not be considered.
Which statement accomplishes all the required tasks?
- A. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE cust_income_level IS NOT NULL ANDtax_amount IS NOT NULL;
- B. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE (cust_income_level, tax_amount) IS NOT NULL;
- C. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE cust_income_level <> NULL ANDtax_amount <> NULL;
- D. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNTFROM customersWHERE cust_income_level IS NOT NULL ANDcust_credit_limit IS NOT NULL;
Answer: D
NEW QUESTION 128
Table ORDER_ITEMS contains columns ORDER_TO, UNIT_PRICE and QUANTITY, of data type NUMBER.
Examine these SQL statements:
Statement 1:
Statement 2:
Which two statements are true? (Choose two.)
- A. Statement 2 returns only one row of output.
- B. Both statements will return NULLif either UNIT_PRICEor QUANTITYcontains NULL.
- C. Both the statements give the same output.
- D. Statement 1 returns only one row of output.
- E. Statement 2 may return multiple rows of output.
Answer: D,E
Explanation:
Explanation/Reference: https://stackoverflow.com/questions/12366390/how-to-select-product-that-have-the-maximum- price-of-each-category
NEW QUESTION 129
Which three actions can you perform on an existing table containing data? (Choose three.)
- A. Add a new column as the table's first column
- B. Change the default value of a column
- C. Define a default value that is automatically inserted into a column containing nulls
- D. Add a new NOT NULL column with a DEFAULT value
- E. Change a DATE column containing data to a NUMBER data type
- F. Increase the width of a numeric column
Answer: A,B
Explanation:
Explanation/Reference: https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj24513.html
NEW QUESTION 130
......
Who should take the Oracle 1Z0-071: Oracle Database SQL Exam
This course is for software administrators, database designers, and system operators.
The Oracle 1Z0-071 is a certification exam that proves the candidate’s knowledge of SQL technology and SQL concepts required to participate in any database project. The target audience for this exam is the IT professionals with some experience in working with these technologies. The fundamental knowledge of general computing concepts and command line interfaces will be an advantage as well.
Pass Oracle 1z1-071 Exam Quickly With Actual4Labs: https://passcollection.actual4labs.com/Oracle/1z1-071-actual-exam-dumps.html