SQL TUTORIALS

Views vs. Tables

Before we get into how to create views, we need to understand the difference between views and tables. Watch the following videos to get a better understanding of this concept:

What is the difference between tables and views?
Difference Between View and Table (GeeksforGeeks)

Summary

• The difference between a view and a table is that Views are definitions built on top of other tables (or views), and do not hold data themselves. In essence, they are virtualized variables that hold the results of a common query.
• Views are dependent on tables. Therefore, when deleting tables, you must delete all views that depend on that table first.
• Because views are virtualized, they only take up database space when a query runs, whereas tables take up space while they exist.

Why Use Views

Views are one of the most helpful tools when working with complex queries. But, when should you use them? Watch the following video to get a better understanding of this concept:

How Views Work in SQL and Why You Should Use Them

Summary:

• Since Views are based on queries, it can be helpful to store your most complex queries as views so that when you run that same query, all you have to do is write the following line of code:
  SELECT *
FROM [view_name];
• As a general security best practice, you may want to hide certain columns containing sensitive information such as credit card numbers, passwords, or social security numbers. You can hide these by excluding them from the underlying query.
• Views are very convenient because they store your queries in a virtualized table format. This enables you to query the View the same as a table.
• Depending on your RDBMS, Views may not allow the ORDER BY Clause for performance purposes.

How to Create Views

Now that you have a clear understanding of how a View differs from a table and when to use views, let's take a look at how to create Views. Watch the following video to get a better understanding of this concept:

SQL Views in 4 Minutes

Summary:

• Compared to other DDL commands, the CREATE VIEW command is pretty straightforward. To create a basic view, use the following syntax:
  CREATE VIEW [view_name] AS [query];
• Certain keywords prevent you from adding, updating, or deleting data from a view. This will be covered in a future tutorial.

Views Best Practices

Once you have started creating Views, it can be easy to get carried away by creating too many views. Thus, there are some industry best practices to prevent this from happening. Read the following article to get a better understanding of this concept:

Best Practices When Creating Views

Summary:

• "If a subquery is associated with a single query, consider using the WITH Clause of the SELECT command instead of creating a seldom-used view." (docs.vmware.com, 2023)
• "Defining a denormalized 'world' view. A view that joins a large number of database tables that is used for a wide variety of queries. [This can cause performance issues]." (docs.vmware.com, 2023)
• "Performance issues can occur for some queries that use [views with] some WHERE conditions while other WHERE conditions work well." (docs.vmware.com, 2023)

Exercise

Congratulations! You just completed the Views Tutorial! To help test your knowledge, let's practice by creating some basic views. This exercise is based on the exercise of the previous tutorial.
**It's highly recommended that you complete the exercise outlined in the previous tutorial before beginning this exercise.**

Instructions:

1. Open your RDBMS Client.
2. Using the "Sales" database, solve the following problems:
1. Create a view that shows total revenue by state.
2. Create a view that shows the top 5 largest orders.
3. Create a view that shows all orders placed in the year 2022 and in the following states: "UT", "CO", "CA". Show the customer's first and last name, the items ordered, and the cost of each item.
3. Exercise Completed! Click here to view the answers.

Have any issues with the above exercise? Post your question on Discord!