Try Oracle : 1Z1-051 practice torrent pass for sure

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

See the Exhibit and examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shown the average credit limit for customers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result?

• A. SELECT cust_city, AVG(NVL(cust_credit_limit,0))
  FROM customers
  WHERE cust_city IN ('WASHINGTON','NEW YORK');
• B. SELECT cust_city, AVG(cust_credit_limit)
  FROM customers
  WHERE cust_city IN ('WASHINGTON','NEW YORK')
  GROUP BY cust_credit_limit, cust_city;
• C. SELECT cust_city, AVG(cust_credit_limit)
  FROM customers
  WHERE cust_city IN ('WASHINGTON','NEW YORK')
  GROUP BY cust_city;
• D. SELECT cust_city, AVG(cust_credit_limit)
  FROM customers
  WHERE cust_city IN ('WASHINGTON','NEW YORK')
  GROUP BY cust_city,cust_credit_limit;

The PRODUCTS table has the following structure:

Evaluate the following two SQL statements:

Which statement is true regarding the outcome?

• A. Both the statements execute and give different results
• B. Only the first SQL statement executes successfully
• C. Both the statements execute and give the same result
• D. Only the second SQL statement executes successfully

You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table.
Which set of statements accomplishes this?

• A. CREATE registrar; GRANT CHANGE ON student_grades TO registrar; GRANT registrar;
• B. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT ROLE registrar to user1, user2, user3
• C. CREATE NEW ROLE registrar; GRANT ALL ON student_grades TO registrar; GRANT registrar to user1, user2, user3
• D. CREATE ROLE registrar; GRANT MODIFY ON student_grades TO registrar; GRANT registrar to user1, user2, user3
• E. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;

Which are /SQL*Plus commands? (Choose all that apply.)

• A. RENAME
• B. DELETE
• C. INSERT
• D. UPDATE
• E. DESCRIBE
• F. SELECT