Prepare with Oracle : 1Z0-051 exam braindumps as your best preparation materials

Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose all that apply.)

• A. The HAVING clause can be used with aggregate functions in subqueries.
• B. The WHERE clause can be used to exclude rows after dividing them into groups.
• C. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
• D. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
• E. The WHERE clause can be used to exclude rows before dividing them into groups.

View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
You want to update the EMPLOYEES table as follows:4 ? 4;
-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 the outcome?

• A. It executes successfully and gives the correct result.
• B. It generates an error because a subquery cannot have a join condition in an UPDATE statement.
• C. It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an UPDATE statement.
• D. It executes successfully but does not give the correct result.

Examine the structure of the TRANSACTIONS table:
Name Null Type
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30)
TRANS_DATE DATE
TRANS_AMT NUMBER(10,2)
You want to display the transaction date and specify whether it is a weekday or weekend.
Evaluate the following two queries:

Which statement is true regarding the above queries?

• A. Both give the correct result.
• B. Only the first query gives the correct result.
• C. Both give wrong results.
• D. Only the second query gives the correct result.

Evaluate the following two queries: Exhibit:

Exhibit:

Which statement is true regarding the above two queries?

• A. Performance would degrade in query 2
• B. There would be no change in performance
• C. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column
• D. Performance would improve in query 2

For which action can you use the TO_DATE function?

• A. Format '10-JAN-99' to 'January 10 1999'
• B. Convert any date literal to a date
• C. Convert any numeric literal to a date
• D. Convert any character literal to a date
• E. Convert any date to a character literal