Oracle Database: SQL Fundamentals I : 1Z1-051

View the Exhibit and examine the data in the COSTS table.

You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:

Which statement is true regarding the above query?

• A. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.
• B. It executes and produces the required result.
• C. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.
• D. It produces an error because an expression cannot be used in the ORDER BY clause.

Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID NUMBER\
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of theDEPARTMENTS table
You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table.
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)

• A. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.
• B. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
• C. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
• D. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
• E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
• F. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.

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

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

Which SQL statements are valid? (Choose all that apply.)

• A. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL, promo_cost*.25, 'N/A') "Catcost" FROM promotions;
• B. SELECT promo_id, DECODE(promo_cost, >10000, 'High', <10000, 'Low') "Range" FROM promotions;
• C. SELECT promo_id, DECODE(promo_cost, 10000, DECODE(promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost" FROM promotions;
• D. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost, promo_cost * 0.25, 100) "Discount" FROM promotions;

You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Which statement accomplishes this task?

• A. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;
• B. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
• C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;
• D. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);