File Info
| Exam | Oracle Datbase 12c SQL |
| Number | 1z0-071 |
| File Name | Oracle.1z0-071.Pass4Sure.2018-07-31.85q.tqb |
| Size | 8 MB |
| Posted | Jul 31, 2018 |
| Download | Oracle.1z0-071.Pass4Sure.2018-07-31.85q.tqb |
How to open VCEX & EXAM Files?
Files with VCEX & EXAM extensions can be opened by ProfExam Simulator.
Coupon: MASTEREXAM
With discount: 20%
Demo Questions
Question 1
View the Exhibit and examine the structure of CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?
- SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
- SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
- SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
- SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT"FROM customers;
Correct answer: A
Question 2
View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
EMPLOYEES
Name Null? Type
----------------- ----- -------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(10,2)
COMMISSION NUMBER(6,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
DEPARTMENTS
Name Null? Type
----------------- ----- -------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
You want to update EMPLOYEES table as follows:
- Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
- Set department_id for these employees to the department_id corresponding to London (location_id 2100).
- Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.
- Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.
You issue the following command:
SQL> UPDATE employees
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = 2100),
(salary, commission) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission)
FROM employees, departments
WHERE departments.location_id IN(2900, 2700, 2100))
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700;
What is outcome?
- It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.
- It generates an error because a subquery cannot have a join condition in a UPDATE statement.
- It executes successfully and gives the desired update
- It executes successfully but does not give the desired update
Correct answer: D
Question 3
Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit IN (1000, 2000, 3000);
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit = 1000 or cust_credit_limit = 2000 or
cust_credit_limit = 3000
Which statement is true regarding the above two queries?
- Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.
- There would be no change in performance.
- Performance would degrade in query 2.
- Performance would improve in query 2.
Correct answer: B
