Creating Views in SQL: A Quick Guide

Table of Contents

Definition
CREATE VIEW
UPDATING A VIEW
DROPPING A VIEW

  • A VIEW is a virtual representation of data. This allows users to simplify complex queries and improve seccurity and privacy.
  • A view is a virtual table createed based on the results of an SQL statement.
  • SQL statements and functions can be added to  a view and present the data as if its coming fom a single table
  • A view is created with the CREATE VIEW statement

SYNTAX

CREATE VIEW view_name AS
SELECT column1column2, ...
FROM table_name
WHERE condition;

UPDATING A VIEW

A view can be updated with the CREATE OR REPLACE VIEW statement.

CREATE OR REPLACE VIEW view_name AS
SELECT column1column2, …
FROM table_name
WHERE condition;

DROPPING A VIEW

A view is deleted with the DROP VIEW statement.

DROP VIEW view_name;

 

Altering a View

–ALTERINGA VIEW TO MAKE IT INVISIBLE
CREATE OR REPLACE VIEW pView_by_Category (
“CATEGORY_NAME”
, “subcategory_name”
, “total_quantity”
, “total_actual_cost” INVISIBLE
) AS
SELECT category_name
, subcategory_name
, SUM(quantity) AS total_quantity
, SUM(actual_cost) AS total_actual_cost
FROM ptbl_category
NATURAL LEFT JOIN ptbl_subcategory
NATURAL LEFT JOIN ptbl_product
NATURAL LEFT JOIN ptbl_transaction
GROUP BY category_name, subcategory_name
ORDER BY category_name, subcategory_name;

 

The above table was altered to add INVISIBLE to the the table.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top