8. SQL Statements : CREATE VIEW : CREATE VIEW Examples
 
Share this page                  
CREATE VIEW Examples
1. Define a view of employee data including names, salaries, and name of the manager.
CREATE VIEW empdpt (ename, sal, dname)
    AS SELECT employee.name, employee.salary,
    dept.name
    FROM employee, dept
    WHERE employee.mgr = dept.mgr;
2. Define a view that uses aggregate functions to display the number of open orders and the average amount of the open orders for sales representative that has orders on file. This view is not updatable (because it uses aggregate functions).
CREATE VIEW order_statistics
    (sales_rep, order_count, average_amt)
      AS SELECT salesrep, COUNT(*), AVG(ord_total)
      FROM open_orders
      GROUP BY sales_rep;
3. Define an updatable view showing the employees in the southern division. Specify check option to prevent any update that changes the region or adds an employee from another region.
CREATE VIEW southern_emps
    AS SELECT * FROM employee
    WHERE region = 'South'
    WITH CHECK OPTION;