SQL Views Tutorial | VIEWS in SQL Complete Tutorial

Summary

Tawfiq of techTFQ presents a tutorial on SQL views, using PostgreSQL-style examples with customer, product, and order tables.

What a View Is

A view is a database object defined by a SQL query. It behaves like a virtual table, but unlike a regular table, it does not store the query’s result data. Querying a view runs its underlying query, so an ordinary view does not improve query performance. Materialized views are mentioned as a separate topic for another video.

Creating and Using a View

The tutorial creates an order-summary view by joining customer, product, and order tables. Users can retrieve its results with a SELECT statement, much like querying a table.

Why Use Views?

• Access control: A separate database user can be granted SELECT access to a view instead of access to the underlying tables. This can limit what an external user is able to query.
• Simplifying queries: A view gives a complex, frequently used query a simple name, making its results easier for others to retrieve.

Creating and Modifying Views

• CREATE VIEW creates a view. Running it again for a view that already exists produces an error.
• CREATE OR REPLACE VIEW creates a view if it does not exist, or replaces its definition if it does. In the examples, replacement requires existing output columns to remain in the same order and retain their names and data types. New columns can be appended, but not inserted among existing columns.
• ALTER VIEW can rename a view or one of its columns. Renaming a view column does not rename the corresponding column in the underlying table.
• DROP VIEW removes a view.

Changes to Source Tables

A view reflects current table data when queried, including inserted, changed, or deleted rows. However, its recorded output-column structure may not automatically reflect later changes to a source table’s structure.

The tutorial demonstrates refreshing a view with CREATE OR REPLACE VIEW to include a newly added table column. This is also highlighted as a potential interview question.

Updatable Views

Updating data through a view is generally not recommended, though it may be useful in some situations. In the tutorial’s examples, a view is not automatically updatable if its query:

1. Uses more than one table or view.
2. Uses DISTINCT.
3. Uses GROUP BY.
4. Uses a WITH clause.
5. Uses window functions.

A simple view over a single table is demonstrated as allowing updates to the underlying table.

WITH CHECK OPTION

The WITH CHECK OPTION clause checks that rows inserted or updated through a view continue to satisfy the view’s WHERE condition. In the example, an Apple-products view rejects an inserted Samsung product but accepts an Apple product.

Additional Notes

The tutorial does not cover materialized views; the presenter says they will be discussed separately.

• Main speaker/source: Tawfiq, from techTFQ.
• Sponsor: Skillshare, an online learning platform. The sponsor segment also recommends MKBHD’s YouTube-success class.