Free 1Z0-071 Exam Dumps

Question 46

You need to produce a report where each customer’s credit limit has been incremented by $1000. In the output, the customer’s last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?

A. SELECT cust_last_name AS “Name”, cust_credit_limit + 1000AS “New Credit Limit”FROM customers;
B. SELECT cust_last_name AS Name, cust_credit_limit + 1000AS New Credit LimitFROM customers;
C. SELECT cust_last_name AS Name, cust_credit_limit + 1000“New Credit Limit”FROM customers;
D. SELECT INITCAP (cust_last_name) “Name”, cust_credit_limit + 1000INITCAP (“NEW CREDIT LIMIT”)FROM customers;

Correct Answer:A

Question 47

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:
1Z0-071 dumps exhibit
You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses.
Which SQL statement must you use?

A. SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)
B. SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN.department_details d ON (c.depatrment_id=d.department_id)
C. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=
D. department_id)
E. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>
F. department_id)

Correct Answer:C

Question 48

Evaluate the following CREATE TABLE command:
1Z0-071 dumps exhibit
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 cannot be used on a composite primary.
C. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID PK would be created.
D. It would give an error because the USING INDEX is not permitted in the CRETAE TABLE command.

Correct Answer:A

Question 49

Examine the structure of the PROGRAMS table:
1Z0-071 dumps exhibit
Which two SQL statements would execute successfully?

A. SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;
B. SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
C. SELECT NVL (MONTHS_BETWEEN (start_date, end_date), ‘Ongoing’) FROM programs;
D. SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), ‘Ongoing’) FROMprograms

Correct Answer:AD

Question 50

Examine the structure of the EMPLOYEES table. NameNull?Type
---------------------- ------------ EMPLOYEE_IDNOT NULLNUMBER(6) FIRST_NAMEVARCHAR2(20) LAST_NAMENOT NULLVARCHAR2(25) EMAILNOT NULLVARCHAR2(25) PHONE NUMBERVARCHAR2(20) HIRE_DATENOT NULLDATE JOB_IDNOT NULLVARCHAR2(10) SALARYNUMBER(8,2) COMMISSION_PCTNUMBER(2,2) MANAGER_IDNUMBER(6) DEPARTMENT_IDNUMBER(4)
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?

A. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.manager_id = m.employee_id)AND e.employee_id = 123;
B. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.manager_id)WHERE e.employee_id = 123;
C. SELECT e.last_name, e.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.employee_id)WHERE e.employee_id = 123;
D. SELECT m.last_name, e.manager_idFROM employees e LEFT OUTER JOIN employees mon (e.manager_id = m.manager_id)WHERE e.employee_id = 123;

Correct Answer:B