Views
Learn how to create and manage virtual tables in SQL
Learn how to create and manage virtual tables in SQL This hands-on tutorial focuses on practical implementation of views concepts.
What is a View?
A View is a virtual table based on the result-set of an SQL statement. It contains rows and columns from an existing table, but the fields in a view are fields from one or more real tables in the database.
Why Use Views?
- Simplification: Simplify complex queries (e.g., joins).
- Security: Restrict access to specific columns or rows.
- Independence: Changes to table structure don't affect the view if the view definition is stable.
Creating a View
Use the CREATE VIEW statement.
CREATE VIEW [View Name] AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example
Create a view that shows only customers from 'USA':
CREATE VIEW USA_Customers AS
SELECT CustomerID, CustomerName, ContactName
FROM Customers
WHERE Country = 'USA';
Then you can query the view like a table:
SELECT * FROM USA_Customers;
Create Customer View
Problem Statement
Create a view named 'Paris_Customers' that selects CustomerID and CustomerName from the Customers table where the City is 'Paris'.
Updating a View
You can replace a view using CREATE OR REPLACE VIEW.
CREATE OR REPLACE VIEW [View Name] AS
SELECT column1, column2, column3, ...
FROM table_name
WHERE condition;
Dropping a View
To delete a view, use DROP VIEW.
DROP VIEW [View Name];
Drop View
Problem Statement
Write a SQL command to delete the view named 'Paris_Customers'.