Oracle Database SQL - 1z0-071 Exam Questions

A. PROMO_BEGIN_DATE-5 will return a date. B. TO_NUMBER(PROMO_BEGIN_DATE)-5 will return number C. PROMO_BEGIN_DATE-SYSDATE will return an error. D. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date E. PROMO_BEGIN_DATE-SYSDATE will return a number.

A. It executes successfully but does not return any result. B. It generates an error. C. It returns the date for the last Monday of the current month. D. It returns the date for the first Monday of the next month.

A. The UPDATE statement executes successfully even if the subquery selects multiple rows. B. All existing rows in the ORDERS table are updated. C. The subquery is executed for every updated row in the ORDERS table. D. The subquery is not a correlated subquery. E. The subquery is executed before the UPDATE statement is executed.

A. SELECT emp. *
FROM employees emp
JOIN employees mgr
ON emp. manager_ id = mgr. employee_ id
AND emp. department_ id<> mgr.department_ id; B. SELECT emp.*
FROM employees emp
LEFT JOIN employees mgr
ON emp.manager_ id = mgr.employee_ id
AND emp. department id < > mgr. department_ id; C. SELECT emp.*
FROM employees emp
WHERE NOT EXISTS (
SELECT NULL
FROM employees mgr
WHERE emp.manager id = mgr.employee_ id
AND emp.department_id<>mgr.department_id
); D. SELECT emp. *
FROM employees emp
RIGHT JOIN employees mgr
ON emp.manager_ id = mgr. employee id
AND emp. department id <> mgr.department_ id
WHERE emp. employee_ id IS NOT NULL; E. SELECT emp. *
FROM employees emp
WHERE manager_ id NOT IN (
SELECT mgr.employee_ id
FROM employees mgr
WHERE emp. department_ id < > mgr.department_ id
);

A. SELECT dept_id, join date, SUM(salary) FROM employees GROUP BY dept_id,join_date; B. SELECT dept_id, AVG(NAX(salary)) FROM employees GROUP BY dept_id; C. SELECT dept_id, MAX (AVG(salary)) FROM employees GROUP BY dept_id; D. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;

A. A condition representing an outer join cannot be linked to another condition using the or logical operator. B. The outer join operator (+) can be used on both sides of the join condition in an outer join. C. The IN operator cannot be used in a condition that Involves an outer join. D. The outer join operator (+) is used next to the column of the table without the matching rows. E. An outer join is used to retrieve only the rows that do not meet the join condition.

A. SELECT shape FROM bricks JOIN boxes ON weight BETWEEN min_weight AND max_weight; B. SELECT shape FROM bricks JOIN boxes ON weight >= min_weight AND weight < max_weight; C. SELECT shape FROM bricks JOIN boxes ON weight < max_weight; D. SELECT shape FROM bricks JOIN boxes ON NOT (weight > max_weight); E. SELECT shape FROM bricks JOIN boxes ON weight > min_weight;

A. LAST_DAY returns the date of the last day of the current ,month onlyu. B. ADD_MONTHS works with a character string that can be implicitlyt converted to a DATE data type. C. LAST_DAY returns the date of the last day of the month for the date argument passed to the function. D. CEIL requires an argument which is a numeric data type. E. LAST_DAY return the date of the last day the previous month only. F. CEIL returns the largest integer less than or equal to a specified number. G. ADD_MONTHS adds a number of calendar months to a date.

A. The WHEN NOT MATCHED clause can be used to specify the deletions to be performed. B. The WHEN NOT MATCHED clause can be used to specify the inserts to be performed. C. The WHEN NOT MATCHED clause can be used to specify the updates to be performed. D. The WHEN MATCHED clause can be used to specify the updates to be performed. E. The WHEN MATCHED clause can be used to specify the inserts to be performed.

A. SELECT * FROM customers WHERE city ='%D_'; B. SELECT * FROM customers WHERE city LIKE'D_'; C. SELECT * FROM customers WHERE city LIKE'D %'; D. SELECT * FROM customers WHERE city ='D_%';

A. an error B. all departments and a sum of the salaries of employees with a salary greater than 3000 C. only departments where the total salary is greater than 3000, returned in no particular order D. only departments where the total salary is greater than 3000, ordered by department