View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables.
You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of
items in each order.

Which CREATE VIEW statement would create the view successfully?

• A. CREATE OR REPLACE VIEW ord_vu
  AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
  "NO OF ITEMS"
  FROM orders o JOIN order_items i
  ON (o.order_id = i.order_id)
  GROUP BY o.order_id,o.order_date;
• B. CREATE OR REPLACE VIEW ord_vu (order_id,order_date)
  AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
  "NO OF ITEMS"
  FROM orders o JOIN order_items i
  ON (o.order_id = i.order_id)
  GROUP BY o.order_id,o.order_date;
• C. CREATE OR REPLACE VIEW ord_vu
  AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
  FROM orders o JOIN order_items i ON (o.order_id = i.order_id)
  GROUP BY o.order_id,o.order_date;
• D. CREATE OR REPLACE VIEW ord_vu
  AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)ll' NO OF ITEMS'
  FROM orders o JOIN order_items i
  ON (o.order_id = i.order_id)
  GROUP BY o.order_id,o.order_date
  WITH CHECK OPTION;

Evaluate the following SELECT statement and view the Exhibit to examine its output: SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS
Which two statements are true about the output? (Choose two.)

• A. In the second column, indicates a check constraint.
• B. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.
• C. The STATUS column indicates whether the table is currently in use.
• D. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.

Evaluate the following command:
CREATE TABLE employees
(employee_id NUMBER(2) PRIMARY KEY,
last_name VARCHAR2(25) NOT NULL,
department_id NUMBER(2), job_id VARCHAR2(8),
salary NUMBER(10,2));
You issue the following command to create a view that displays the IDs and last names of the
sales staff in the organization:
CREATE OR REPLACE VIEW sales_staff_vu AS
SELECT employee_id, last_name job_id
FROM employees
WHERE job_id LIKE 'SA_%' WITH CHECK OPTION;
Which statements are true regarding the above view? (Choose all that apply.)

• A. It allows you to insert details of all new staff into the EMPLOYEES table.
• B. It allows you to update the job ids of the existing sales staff to any other job id in the EMPLOYEES table.
• C. It allows you to insert the IDs, last names and job ids of the sales staff from the view if it is used in multitable INSERT statements.
• D. It allows you to delete the details of the existing sales staff from the EMPLOYEES table.

View the Exhibit and examine the structure of the ORDERS table. Which task would require subqueries?

• A. displaying the total order value for sales representatives 161 and 163
• B. displaying the order total for sales representative 161 in the year 1999
• C. displaying the number of orders whose order total is more than the average order total for all online orders
• D. displaying the number of orders that have order mode online and order date in 1999