A view in SQL is a virtual table that doesn't store data but provides a way to query and display data from one or more tables. You can think of it as a saved SQL query that you can reuse like a regular table.
💡 Tip: Views help reduce repetition, simplify complex queries, and enhance security by restricting access to sensitive data in a database.
The CREATE VIEW statement creates a view from a SELECT query. Once created, the view behaves like a table, and you can use it in SELECT, INSERT, UPDATE, and DELETE statements (in certain cases).
Example: Creating a view to display customer names and their orders:
CREATE VIEW customer_orders AS
SELECT customers.name, orders.order_id
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;
This query creates a view named customer_orders that combines data from the customers and orders tables.
Once a view is created, you can use it just like a table in SQL queries. For example, you can query data from a view using a SELECT statement.
Example: Querying the customer_orders view:
SELECT * FROM customer_orders;
This will retrieve all the customer names along with their order IDs from the view.
You can update data through a view if the view is updatable. Simple views based on one table without complex joins or aggregates are usually updatable.
Example: Updating a customer order via the view:
UPDATE customer_orders
SET order_id = 101
WHERE name = 'John Doe';
This will update the order_id for "John Doe" in the view, which is reflected in the underlying tables.
If you no longer need a view, you can drop it using the DROP VIEW statement. Dropping a view does not delete the underlying data.
Example: Dropping the customer_orders view:
DROP VIEW customer_orders;
This command removes the customer_orders view from the database.
🛠️ Interactive Task: Create a view on your own database to combine data from two or more tables, and query it to retrieve the necessary data. Experiment with different column selections and conditions.
Help others discover Technorank Learning by sharing your honest experience.
Your support inspires us to keep building!