Oracle 1Z1-051 Actual PDF : Oracle Database: SQL Fundamentals I

See the Exhibit and examine the structure and data in the INVOICE table:
Exhibit: Which two SQL statements would execute successfully? (Choose two.)

• A. SELECT MAX(AVG(SYSDATE-inv_date)) FROM invoice;
• B. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
• C. SELECT AVG(inv_date) FROM invoice;
• D. SELECT AVG(inv_date-SYSDATE),AVG(inv_amt) FROM invoice;

View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS tables.

You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in Tokyo'. Which two queries give the required result? (Choose two.)

• A. SELECT c.cust_last_name, p.prod_name, s.quantity_sold
  FROM products p JOIN sales s JOIN customers c
  ON(p.prod_id=s.prod_id)
  ON(s.cust_id=c.cust_id)
  WHERE c.cust_city='Tokyo';
• B. SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p USING(prod_id)
  JOIN customers c
  USING(cust_id)
  WHERE c.cust_city='Tokyo';
• C. SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM products p
  JOIN saless
  USING(prod_id)
  JOIN customers c
  USING(cust_id)
  WHERE c.cust_city='Tokyo';
• D. SELECT c.cust_last_name, p.prod_name, s.quantity_sold
  FROM products p JOIN sales s
  ON(p.prod_id=s.prod_id)
  JOIN customers c
  ON(s.cust_id=c.cust_id)
  AND c.cust_city='Tokyo';

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

Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)

• A. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule.
• B. DELETE can be used to remove data from specific columns as well as complete rows.
• C. DELETE can be used to remove only rows from multiple tables at a time.
• D. DELETE can be used to remove only rows from only one table at a time.
• E. DELETE can be used only on a table that is a parent of a referential integrity constraint.

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