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 (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;
B.
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;
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)||’ 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;
why is B correct?????
what is difference of B and C??
ORA-00998: must name this expression with a column alias
Cause: An expression or function was used in a CREATE VIEW statement, but no corresponding column name was specified. When expressions or functions are used in a view, all column names for the view must be explicitly specified in the CREATE VIEW statement.
Action: Enter a column name for each column in the view in parentheses after the view name.
C is incorrect because of ORA-00998. Functions or expressions in the select clause must be aliased, when creating a view.
For A.
CREATE OR REPLACE VIEW emp_ord_vu (a, b, c)
as select job, sal, count(empno) from emp group by sal, job; <== this OK
CREATE OR REPLACE VIEW emp_ord_vu (a, b)
as select deptno, count(empno) from emp group by deptno ; <== this OK
CREATE OR REPLACE VIEW emp_ord_vu (a, b)
as select job, sal, count(empno) from emp group by sal, job; <== this wrong
CREATE OR REPLACE VIEW emp_ord_vu (a, b)
as select p.deptno, count(p.empno) from emp p group by p.deptno ;
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;
The following need Alias to work:
create view vv1 as select upper(sal) from emp;
create view vv1 as select sal*100 from emp;
create view vv1 as select sal+100 from emp;
create view vv1 as select sal+sal from emp;