View the Exhibit and examine the structures of the employees and departments tables.

You must update the employees table according to these requirements::
-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 (locationid 2100).
-Set the employees' salary in iocation_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 this command:

What is the result?

1. A. It executes successfully but does not produce the desired update.
2. B. It executes successfully and produces the desired update.
3. C. It generates an error because multiple columns cannot be specified together in an UPDATE statement.
4. D. It generates an error because a subquery cannot have a join condition in an update statement.

Correct Answer: A

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?

1. A. SELECT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
2. B. SELECT DISTINCT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
3. C. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers.
4. D. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers

Correct Answer: B

Which two tasks can be performed by using Oracle SQL statements?

1. A. changing the password for an existing database user
2. B. connecting to a database instance
3. C. querying data from tables across databases
4. D. starting up a database instance
5. E. executing operating system (OS) commands in a session

Correct Answer: AC
References:
http://www.techonthenet.com/oracle/password.php
https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm

View the Exhibit and examine the structure of the PROMOTION table.

You have to generate a report that displays the promo named start data for all promos that started after that last promo in the ‘INTTERNET’ category.

1. A. Select promo_name, promo_being_date FROM promoptions WHERE promo_being_data > ANY (SELCT promo_being-date FROM promotionsWHERE promo_category = ‘INTERNET’
2. B. SELECT promo_neme, promo_being_date FROM promotions WHERE promo_being_date > All (SELECT promo_beinjg-date FROM promotionsWHERE promo_category =’INTERNET’ );
3. C. SELECT promo-name, promo-being _date FROM promotionsWhere promo_being_data >ALL (SELECT MAX (promo_being-date) FROM promotions ) ANDPromo-category =’INTERNET’;
4. D. SELECT promo-name, promo-being_date FROM promotion WHERE promo-being-date IN (SELECT promo_biing_date FROM promotionsWHERE promo_category=’INTYERNET’);

Correct Answer: B

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. (Choose two.)

Evaluate this SQL statement: ALTER TABLE emp
DROP COLUMN first_name; Which two statements are true?

1. A. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.
2. B. The FIRST_NAME column would be dropped provided at least one column remains in the table.
3. C. The FIRST_NAME column would be dropped provided it does not contain any data.
4. D. The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.

Correct Answer: B