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;