Question

QUESTION 9

1. You plan to query data using the TRANS_HIST_V view that was created by another user in their schema. Which statement is true?

   	A.	The Oracle Server will retrieve the view definition from the ALL_VIEWS data dictionary view.
   	B.	The Oracle Server will retrieve data, determined by the query criteria, from the TRANS_HIST_V view.
   	C.	The Oracle Server will verify whether you have been granted access privileges to the TRANS_HIST_V view.
   	D.	The Oracle Server will automatically reset the ARRAYSIZE value so the TRANS_HIST_V query will execute successfu

QUESTION 10

1. The EMPLOYEE table contains these columns:

   EMPLOYEE_ID   NUMBER

   LAST_NAME   VARCHAR2(25)

   FIRST_NAME       VARCHAR2(25)

   DEPT_ID NUMBER

   JOB_ID VARCHAR2(15)

   MGR_ID      NUMBER

   SALARY NUMBER(9,2)

   COMMISSION NUMBER(7,2)

   HIRE_DATE DATE

   Which SELECT statement could be used to display the 10 lowest paid clerks that belong to department 70?

   	A.	SELECT ROWNUM "Ranking", last_name||' ,'|| first_name "Employee", salary "Salary" FROM (SELECT last_name, first_name, salary FROM employee    ORDER BY salary) WHERE ROWNUM <= 10 AND job_id LIKE 'CLERK‘ AND dept_id = 70;
   	B.	SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary" FROM (SELECT last_name, first_name, salary, job_id   FROM employee WHERE job_id LIKE 'CLERK‘AND dept_id = 70      ORDER BY salary) WHERE ROWNUM <= 10;
   	C.	SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary" FROM (SELECT last_name, first_name, salary,job_id, dept_id   FROM employee WHERE ROWNUM <= 10 ORDER BY salary) WHERE job_id LIKE 'CLERK‘ AND dept_id = 70;
   	D.	The desired result can only be achieved using a PL/SQL LOOP.

QUESTION 11

1. The ACCOUNT table contains these columns:

   ACCOUNT_ID       NUMBER(12) NOT NULL, Primary Key

   CREDIT_LIMIT         NUMBER(7) NOT NULL

   PREV_BALANCE         NUMBER(7,2)

   NEW_PURCHASES       NUMBER(7,2)

   PAYMENTS           NUMBER(7,2)

   FINANCE_CHARGE   NUMBER(7,2)

   You created a view definition based on the ACCOUNT table. This view allows users to insert and update records that can be queried using the view. Which statement did you use to create this view?

   	A.	CREATE VIEW acct_hist_v AS SELECT account_id, prev_balance, new_purchases,payments FROM account;
   	B.	CREATE VIEW acct_credit_v AS SELECT DISTINCT credit_limit FROM account WHERE finance_charge > 75.00;
   	C.	CREATE VIEW delinq_accts_v AS SELECT * FROM account WHERE payments IS NULL AND finance_charge IS NOT NULL WITH CHECK OPTION;
   	D.	CREATE VIEW avg_finance_v AS SELECT account_id, AVG(finance_charge) "Avg F.Charge“ FROM account GROUP BY finance_charge WITH CHECK OPTION;

  

QUESTION 12

1. Which type of view is implemented as a subquery embedded in the FROM clause of a SELECT statement and is identified by an alias?

   	A.	Nested
   	B.	Simple
   	C.	inline
   	D.	complex

QUESTION 13

1. Evaluate this view definition:

   CREATE OR REPLACE VIEW order_hist_v AS

   SELECT customer_id, COUNT(order_id) "Total Orders"

     FROM order

   WHERE order_amt >= 300

   GROUP BY customer_id;

   Which statement using the ORDER_HIST_V view will execute successfully?

   	A.	SELECT * FROM order_hist_v;
   	B.	UPDATE order_hist_v SET emp_id = 389 WHERE emp_id = 5423 AND order_amt <= 50;
   	C.	DELETE FROM order_hist_v WHERE customer_id = 794;
   	D.	INSERT INTO order_hist_v (order_id, customer_id,emp_id, order_dt) VALUES (8456, 292, 104, '10-OCT-2001');

Answer 1

Q9
Ans: (c). The Oracle Server will verify whether you have been granted access privileges to the TRANS_HIST_V view.
Explanation: Due to the security issues, first you should have access to the TRANS_HIST_V view,=.

Q10
Ans: (b). SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary"
FROM (SELECT last_name, first_name, salary, job_id
FROM employee
WHERE job_id LIKE 'CLERK‘AND dept_id = 70
ORDER BY salary)
WHERE ROWNUM <= 10;

Explanation: In a nested query, we are supposed to find employee last name, first name, salary, job_id whose joi id contains clerk and department id is 70 or order them according to salary

Q11
Ans: (C). CREATE VIEW delinq_accts_v AS
   SELECT *
   FROM account
   WHERE payments IS NULL
   AND finance_charge IS NOT NULL
   WITH CHECK OPTION;

Explanation: Only option c satisfy the requirement

Q12
Ans: (c). inline
Explanation: the inline view is implemented as subquery embedded in the from clause of a select statement

Q13
Ans: (a). SELECT * FROM order_hist_v;
Explanation: The reason being Order_hist_v view contains a Group by clause. DML can't be performed through a view