Oracle Certified 1z1-071 Dumps Questions Valid 1z1-071 Materials [Q130-Q150]

NO.130 Examine thestructure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?

NO.131 Examine this statement:
SELECT last name
FROM employees
ORDER BY CASE WHEN salary = (SELECT MAX(salary) FROM employees)
THEN ‘A’
ELSE last_ name
END ,last_name DESC;
Which two statements are true?

NO.132 View the Exhibit and examine the ORDERS table.
The ORDERS table contains data and all orders have been assigned a customer ID.
Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?

NO.133 Examine these statements which execute successfully:
ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24 MI: SS’
ALTER SESSION SET TIME_ ZONE = ‘-5:00’;
SELECT DBTIMEZONE, SYSDATE FROM DUAL
Examine the result:

If LOCALTIMESTAMP was selected at the same time what would it return?

NO.134 Which three are true about multitable INSERTstatements? (Choose three.)

NO.135 Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)

NO.136 Examine this SQL statement
DELETE FROM employees e
WHERE EXISTS
(SELECT’ dummy’
FROM emp history
WHERE employee_ id= e. employee id);
Which two are true?

NO.137 View the Exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.

To retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:
SELECT employee_id, first_name, department_name
FROM employees NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

NO.138 The first DROPoperation is performed on PRODUCTStable using this command:
DROP TABLE products PURGE;
Then a FLASHBACKoperation is performed using this command:
FLASHBACK TABLE products TO BEFORE DROP;
Which is true about the result of the FLASHBACKcommand?

NO.139 Examine the data in the PRODUCTS table:

Examine these queries:
1. SELECT prod name, prod list
FROM products
WHERE prod 1ist NOT IN(10,20) AND category _id=1;
2. SELECT prod name, | prod _ list
FROM products
WHERE prod list < > ANY (10,20) AND category _id= 1;
SELECT prod name, prod _ list
FROM products
WHERE prod_ list <> ALL (10, 20) AND category _ id= 1;
Which queries generate the same output?

NO.140 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?

NO.141 View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?

NO.142 Which two statements are true regarding operators used with subqueries (Choose two.)

NO.143 Examine the structure of the INVOICEtable.

Which two SQL statements would execute successfully?

NO.144 Which statement is true regarding external tables?

NO.145 Examine the structure of the EMPLOYEES table.

You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output? (Choose two.)

NO.146 Which two actions can you perform with object privileges? (Choose two.)

NO.147 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?

NO.148 Examine the description of the BOOKS_TRANSACTIONS table:

Examine this partial SQL statement:
SELECT * FROM books_transactions
Which two WHERE conditions give the same result?

NO.149 Examine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario? (Choose two.)

NO.150 View the Exhibit and examine the structure in the EMPLOYEES tables.

Evaluate the following SQL statement:
SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 10;
What would be the outcome of the above SQL statement?