FB

SQL Views and Materialized Views: The Complete Guide

By the end of this article, you’ll know all about views, materialized views, how to create them, their advantages, and much more.

What is a View?

A view is an SQL statement that’s stored in the database. This statement, or view, has a name.

A view looks and acts a lot like a table. It has columns and rows, and can be included in SELECT queries just like a table.

If you look at a SELECT query that uses a view, sometimes you might not know that you are querying from a view. The name of the object being queried from could represent a table or a view (or a synonym!).

One thing to remember is that the view object is only a stored SQL query. This means that no data is stored along with the view. The view object only stores your SQL query, and whenever you query the view, it runs the query that is stored against it.

SQL View

 

Why Use a View?

So if a view is just an SQL statement with a name, why would you use one?

There are several reasons to use views in SQL. This applies to any SQL variation you use (Oracle, SQL Server, MySQL, etc).

 

Simplify Queries

The main advantage to using views is that they simplify your queries.

As we learned above, views are just stored SQL statements. These statements can include, for example:

  • Selecting of different columns
  • Joins to other tables
  • Functions, including aggregate functions
  • WHERE clauses
  • GROUP BY and HAVING

So, if you have a complicated query or logic that’s used in many places, you can use a view to hold that logic. You can then query from that view whenever you need that data.

For example, you can create a view that counts the number of orders and totals the order volume for all orders for each month. You can then query this view and limit by month to find the data you need for a specific month.

We’ll look at how to create a view and see some examples later in this article.

 

Security

Another benefit of using views is increased security. You can create a view that only selects certain columns from a table. Then, certain users, or applications, can be given access to this view rather than the entire table.

For example, you may have an employee table with salary information. You might not want all applications or users to see this salary information.

So, you can create a view that selects all columns except for the salary columns, and then give access to that view to other users (and don’t give them access to the employee table).

This means when they query the employee view, they won’t see the salary information.

SQL Views and Security

 

How to Create a View in SQL

To create a view in SQL, follow this syntax:

CREATE [OR REPLACE] VIEW viewname AS
SELECT select_query;

This is specifically for Oracle SQL but it should be similar for all other databases.

It includes several components:

  • CREATE VIEW: specifiess that we are creating a view.
  • OR REPLACE: this optional query lets you replace a view that already exists with the same name, which means you don’t have to drop it first.
  • viewname: the name of the view to create. This will be used in queries in the future to refer to this view.
  • AS SELECT: specifies that the view translates to the following SELECT query.
  • select_query: the SELECT query to be stored and used for the view. Selecting from the view will run this query.

Let’s take a look at some examples.

 

Examples of Creating a View

Sample Data

Our views are going to be based off the following example data:

Employee:

emp_id last_name dept_id salary
1 Jones 3 23000
2 Smith 2 49000
3 King 1 21000
4 Johnson 4 40000
5 Archer 1 93000
6 McDonald 4 12000
7 Ferrier 3 42000
8 Reacher 2 37000
9 Clarkson 4 52000
10 Simpson 1 60000

Department:

dept_id dept_name
1 Sales
2 Customer Service
3 Finance
4 IT

 

Create a Simple View

Let’s create a simple view to demonstrate the concept of a view.

We’ll create a view that shows the emp_id and last_name columns from the employee table.

CREATE VIEW emp_details AS
SELECT
emp_id,
last_name
FROM employee;

Once this view is created, we can SELECT from it as though it is a table.

SELECT emp_id, last_name
FROM emp_details;

Result:

emp_id last_name
1 Jones
2 Smith
3 King
4 Johnson
5 Archer
6 McDonald
7 Ferrier
8 Reacher
9 Clarkson
10 Simpson

It acts just like a table.

 

Create a View for Security

In this example, we’ll create a view on the employee table that shows all employee data except for the salary column. This way, only certain users can access the full table, and everyone else can access the view.

CREATE VIEW employee_rst AS
SELECT
emp_id,
last_name,
dept_id
FROM employee;

The view is called employee_rst, and the “rst” is meant to stand for restricted. The name is up to you.

Now, whenever anyone queries the employee_rst view, they will see this data:

SELECT emp_id,
last_name,
dept_id
FROM employee_rst;
emp_id last_name dept_id
1 Jones 3
2 Smith 2
3 King 1
4 Johnson 4
5 Archer 1
6 McDonald 4
7 Ferrier 3
8 Reacher 2
9 Clarkson 4
10 Simpson 1

Only those users with access to the full employee table will be able to query the full table. I’ll have a separate post on privileges to explain how to do that.

 

View with Joins

Let’s look at an example that joins two tables together. This view is a good example of simplifying the query.

We’ll create a view that shows all employee and department information in the one view.

CREATE VIEW empdept AS
SELECT
e.emp_id,
e.last_name,
e.salary,
d.dept_id,
d.dept_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id;

This creates a view based on that query that performs the join.

Now we can query that view.

SELECT emp_id,
last_name,
salary,
dept_id,
dept_name
FROM empdept;

Result:

emp_id name salary dept_id dept_name
1 Jones 23000 3 Finance
2 Smith 49000 2 Customer Service
3 King 21000 1 Sales
4 Johnson 40000 4 IT
5 Archer 93000 1 Sales
6 McDonald 12000 4 IT
7 Ferrier 42000 3 Finance
8 Reacher 37000 2 Customer Service
9 Clarkson 52000 4 IT
10 Simpson 60000 1 Sales

We don’t need to specify any table aliases here, because all columns are in the view, and that is handled when we created the view. We just select the columns as though they are all in the same table.

 

View with Aggregate Functions

Now let’s take a look at a view that uses aggregate functions. Let’s say we wanted to find the department ID and name, the number of employees, and the total salary in each department.

Our SELECT query would look like this:

SELECT
d.dept_id,
d.dept_name,
COUNT(e.*) AS emp_count,
SUM(e.salary) AS total_salary
FROM department d
INNER JOIN employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

Result:

dept_id dept_name emp_count total_salary
1 Sales 3 174000
2 Customer Service 2 86000
3 Finance 2 65000
4 IT 3 104000

This shows the result we want.

Now let’s turn this into a view.

CREATE VIEW dept_stats AS
SELECT
d.dept_id,
d.dept_name,
COUNT(e.*) AS emp_count,
SUM(e.salary) AS total_salary
FROM department d
INNER JOIN employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

Because the view is a stored SQL statement and not a separate table, the query in the view is run each time the view itself is queried, so the numbers are always up to date.

Now, to find this data, all we need to do is query the dept_stats view.

SELECT dept_id,
dept_name,
emp_count,
total_salary
FROM dept_stats;

Result:

dept_id dept_name emp_count total_salary
1 Sales 3 174000
2 Customer Service 2 86000
3 Finance 2 65000
4 IT 3 104000

We can also filter the view using the WHERE clause, if we only wanted to see data for a particular department.

SELECT dept_id,
dept_name,
emp_count,
total_salary
FROM dept_stats
WHERE dept_id = 2;

Result:

dept_id dept_name emp_count total_salary
2 Customer Service 2 86000

So, as you can see, views can be quite easy to create and useful to have in your database.

 

Inserting or Updating Data in a View

In this section, we’ll look at inserting and updating data in a view.

 

Can You INSERT or UPDATE Data In a View?

We’ve shown some examples on how to SELECT data from a view as though it was a table.

What if you want to update or insert data in a view, as though it was a table?

You can do that, in certain circumstances. It depends on the query that is used to create the view.

You can insert or update data in a view if the view does not:

  • Have any DISTINCT keywords, aggregate functions, or window functions in the SELECT clause
  • Use set operators (e.g. UNION)
  • Use subqueries in the SELECT clause or marked as read-only
  • Use the GROUP BY, HAVING, ORDER BY, MODEL, CONNECT BY, or START WITH clause
  • Contain pseudocolumns or expressions

The view must also include all NOT NULL columns for an INSERT to be possible.

When updating or inserting, the data being inserted or updated must be targeted at a key-preserved table. A key-preserved table is where every primary key and unique key in the underlying table exists in the view. Without the keys being in the view, the data in the underlying table can’t be inserted or updated.

So, using our earlier examples:

  • empdept is not updatable because it queries two tables
  • deptstats is not updatable because it uses aggregate functions
  • emp_details is updatable
  • employee_rst is updatable

 

How to Insert Data with a View

Inserting data with a view is done the same way as inserting data into a table.

For example, to insert data into the emp_details view:

INSERT INTO emp_details (emp_id, last_name)
VALUES (11, ‘Grover’);

This will insert another value into the employee table, and will show up in the emp_details view.

SELECT emp_id, last_name
FROM emp_details;

Results:

emp_id last_name
1 Jones
2 Smith
3 King
4 Johnson
5 Archer
6 McDonald
7 Ferrier
8 Reacher
9 Clarkson
10 Simpson
11 Grover

 

How to Update Data with a View

Updating data with a view is also done the same way as you would do it with a table.

UPDATE employee_rst
SET dept_id = 3
WHERE emp_id = 6;

We’ve updated the dept_id value to 3 for employee 6. We can check this by querying the view.

SELECT emp_id, last_name, dept_id
FROM employee_rst;

Result:

emp_id last_name dept_id
1 Jones 3
2 Smith 2
3 King 1
4 Johnson 4
5 Archer 1
6 McDonald 3
7 Ferrier 3
8 Reacher 2
9 Clarkson 4
10 Simpson 1

 

Validating New Data Using WITH CHECK OPTION

Another feature available in views is called WITH CHECK OPTION.

When we insert or update data in a view, it updates the data in the underlying table (because the view contains no data, it’s just a query).

When we SELECT from the view again, we see the updated data.

But what if the data that we insert or update is no longer shown by the view?

For example, we could create a view that shows all employees with a salary greater than 50,000.

CREATE VIEW emp_highsal AS
SELECT emp_id,
last_name,
salary
FROM employee
WHERE salary > 50000;

If we SELECT from this view, we can see the results.

SELECT emp_id,
last_name,
salary
FROM emp_highsal;

Result:

emp_id last_name salary
5 Archer 93000
9 Clarkson 52000
10 Simpson 60000

Now, let’s try to insert some new data.

INSERT INTO emp_highsal (emp_id, last_name, salary)
VALUES (12, ‘Hill’, 45000);

The salary value is 45,000, which is less than the 50,000 that the view shows. But the data is still inserted through the view into the employee table.

SELECT *
FROM employee
WHERE emp_id = 12;

Result:

emp_id last_name dept_id salary
5 Archer NULL 45000

So the record is in the table, but it doesn’t show up in the view:

SELECT emp_id,
last_name,
salary
FROM emp_highsal;

Result:

emp_id last_name salary
5 Archer 93000
9 Clarkson 52000
10 Simpson 60000

This might be the behaviour you want. Or, it might not be. You might want to stop data being inserted or updated if it means it does not meet the view criteria.

Luckily, Oracle has a keyword for that.

It’s called WITH CHECK OPTION.

WITH CHECK OPTION is a keyword you can add when creating a view. If you specify it when creating a view, then all inserted data and all updated data must meet the criteria in the view. If not, then the insert or update will fail.

We can recreate the view using this new keyword:

CREATE OR REPLACE VIEW emp_highsal AS
SELECT emp_id,
last_name,
salary
FROM employee
WHERE salary > 50000
WITH CHECK OPTION;

Now, let’s insert a new value.

INSERT INTO emp_highsal (emp_id, last_name, salary)
VALUES (13, ‘Franklin’, 39000);

We can see that an error has appeared. This is because the salary value of 39000 is less than the 50000 specified in the view, and therefore does not meet the criteria.

So, that’s how you can use the WITH CHECK OPTION keyword to ensure any new data or changes to existing data still meet the criteria of your view.

 

What is a Materialized View?

As we learned earlier in this article, a view is an SQL statement stored on the database. No data is stored with this view, only the query.

Every time we query the view, the view’s query is then run on the underlying tables.

However, there are times where it would be useful to have data stored along with the view.

For example, if you want to find totals of orders over a time period, you would have to run a query that maybe looks at several tables, and performs some aggregate functions, and maybe with some WHERE clauses.

This query could get expensive and take a long time to run each time you wanted to get this data.

There is a way to store this data that is returned by the view.

This is what a materialized view does.

A materialized view is a view that stores the results of the view’s query.

Whenever you query the materialized view, it returns the data stored with the view itself. It doesn’t run the view’s query against the underlying tables. It’s a lot like a table.

This image may help explain it:

Materialized View

So, if the query that makes up the materialized view is not run when the materialized view is queried, how does the data get into the materialized view?

It’s populated when it is created. It can also be refreshed in the future.

 

Benefits of a Materialized View

There are several benefits to using materialized views in your database.

 

Improve Query Efficiency

The main benefit to using a materialized view is to improve the efficiency of a query.

If a query takes a long time to run, it could be because there are a lot of transformations being done to the data: subqueries, functions, and joins, for example.

A materialized view can combine all of that into a single result set that’s stored like a table.

This means that any user or application that needs to get this data can just query the materialized view itself, as though all of the data is in the one table, rather than running the expensive query that uses joins, functions, or subqueries.

Calculations can also be added to materialized views for any fields you may need, which can save time, and are often not stored in the database. For example, perhaps you want to store both the monthly and annual salary for an employee. Normally, you would only store one and calculate the other, but in a materialized view, you can store both.

Materialized views can also be created to optimise a single query. They don’t need to be normalized and fit into the overall database design in that way.

I worked on a client project where one of the database developers changed the views to a materialized view and saw a large increase in performance. A view that was taking 10 minutes to run was only taking 20 seconds to run when it was converted to a materialized view.

 

Simplify a Query

Like a regular view, a materialized view can also be used to simplify a query. If a query is using a lot of logic such as joins and functions, using a materialized view can help remove some of that logic and place it into the materialized view.

 

Disadvantages of a Materialized View

Updates to Data Need to be Set Up

The main disadvantage to using materialized views is that the data needs to be refreshed.

The data that’s used to populate the materialized view is stored in the database tables. These tables can have their data updated, inserted, or deleted. When that happens, the data in the materialized view needs to be updated.

This can be done manually, but it should be done automatically.

 

Data May Be Inconsistent

Because the data is stored separately in the materialized view, the data in the materialized view may be inconsistent with the data in the underlying tables.

This may be an issue if you are expecting or relying on data to be consistent.

However, for scenarios where it doesn’t matter (e.g. monthly reporting on months in the past), then it may be OK.

 

What’s the Difference Between a Materialized View and a Table?

If you’ve read the description of a materialized view, you might be wondering how it’s different from a table.

A database object that stores data so it can be queried – sounds just like a table.

But it is different.

The main difference is how it is refreshed.

If you wanted to refresh a table with data from other tables, you would need to find and use the original script to update the data in the table, or use a MERGE statement.

With a materialized view, you just run a command to refresh it. The materialized view knows what to do, as it has the query that is used to populate the data stored with it.

 

What’s the Difference Between a View and a Materialized View?

Views are only a stored query and run the query each time the view is accessed.

Materalized views have data stored on the disk and the data is updated occasionally based on the definition of the view.

This table helps to explain some of the differences.

Component View Materialized View
Stored on database SQL query definition SQL query definition, and data that is returned by it
Data storage None – remains in underlying tables Stored within the view
Refresh Latest data is returned whenever the query is run Data stored in view may not be the most up-to-date. Options can be set to update the materialized view data periodically.
Performance Same as underlying query Usually faster than a view.
Indexes None – uses same indexes as underlying tables Can be created on the materialized view itself
Space Used Small, as it is only the SQL query definition Can be large, as the data is stored with the object

 

How to Create a Materialized View

To create a materialized view, we use the following syntax:

CREATE MATERIALIZED VIEW viewname
[REFRESH [FAST|COMPLETE|FORCE] [ON DEMAND|ON COMMIT]]
[BUILD IMMEDIATE|BUILD DEFERRED]
AS
select_query;

This includes:

  • CREATE MATERIALIZED VIEW: specify what’s being created.
  • viewname: the name of the new materialized view.
  • REFRESH FAST: uses an incremental refresh method which uses changes made to the underlying tables in a log file.
  • REFRESH COMPLETE: uses a complete refresh by re-running the query in the materialized view.
  • REFRESH FORCE: indicates that a fast refresh should be performed if possible, but if not, a complete refresh is performed. Force is the default (between Fast, Force, and Complete)
  • REFRESH ON DEMAND: indicates that a refresh will occur manually whenever specific package functions are called. On Demand is the default.
  • REFRESH ON COMMIT: indicates that a fast refresh occurs whenever a transaction commits that makes changes to any of the underlying tables.
  • BUILD IMMEDIATE: the materialized view will be populated immediately. This is the default.
  • BUILD DEFERRED: the materialized view is populated on the next refresh operation.
  • AS select_query: the query that is run, with the results stored in the materialized view.

The syntax above is just a small extract of the possible syntax. Oracle’s documentation lists a lot of other features that are available in materialized views.

For other databases, refer to the documentation TODO add links:

  • Oracle
  • SQL Server
  • PostgreSQL
  • MySQL

 

Examples of Materialized Views

Let’s look at some examples of materialized views.

 

View with Joins

We can use the same query as the example from the View section.

CREATE MATERIALIZED VIEW empdept AS
SELECT
e.emp_id,
e.last_name,
e.salary,
d.dept_id,
d.dept_name
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id;

The materialized view empdept can now be queried:

SELECT emp_id,
last_name,
salary,
dept_id,
dept_name
FROM empdept;

Result:

emp_id name salary dept_id dept_name
1 Jones 23000 3 Finance
2 Smith 49000 2 Customer Service
3 King 21000 1 Sales
4 Johnson 40000 4 IT
5 Archer 93000 1 Sales
6 McDonald 12000 4 IT
7 Ferrier 42000 3 Finance
8 Reacher 37000 2 Customer Service
9 Clarkson 52000 4 IT
10 Simpson 60000 1 Sales

 

View with Aggregate Functions

This example uses aggregate functions, and is the same query as earlier in this guide

CREATE MATERIALIZED VIEW dept_stats
REFRESH COMPLETE
AS
SELECT
d.dept_id,
d.dept_name,
COUNT(e.*) AS emp_count,
SUM(e.salary) AS total_salary
FROM department d
INNER JOIN employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

Now, to find this data, all we need to do is query the dept_stats view.

SELECT dept_id,
dept_name
emp_count
total_salary
FROM dept_stats;

Result:

dept_id dept_name emp_count total_salary
1 Sales 3 174000
2 Customer Service 2 86000
3 Finance 2 65000
4 IT 3 104000

The data is stored in the materialized view so this can be queried more efficiently.

 

What Are Inline Views?

You might have heard of the term “inline view” before.

What does that mean?

An inline view is a SELECT query that’s used within the FROM clause of another SELECT query. It’s treated like a table but no view object is created. They are used to simplify queries by consolidating your query and removing join operations.

If you’re familiar with SQL Server and PostgreSQL, they are called “derived tables” in SQL Server and subselects in PostgreSQL.

Inline views look like this:

SELECT columns
FROM (inline_view);

The inline_view is another SELECT query.

They are helpful because we don’t need to create a view or a temporary table just for a single query.

 

Examples of Inline Views

Here’s an example of an inline view using our employee and department tables.

SELECT dept_id, emp_salary
FROM (
  SELECT e.dept_id,
  SUM(e.salary) AS emp_salary
  FROM employee e
  GROUP BY e.dept_id) AS emp_data
ORDER BY emp_salary DESC;

Results:

dept_id emp_salary
1 174000
4 104000
2 86000
3 65000

This query will show all of the department IDs and the SUM of salary for each of them. It can be written in other ways, but this is an example of writing it using an inline view.

Let’s look at another example, where we find the average salary for all employees in departments, where the employees are earning more than a certain amount.

SELECT d.dept_name,
AVG(ed.salary) AS avg_salary
FROM (
  SELECT e.id,
  e.dept_id,
  e.salary
  FROM employee e
  WHERE e.salary > 30000) AS empdata ed
INNER JOIN department d ON ed.dept_id = d.dept_id;

Results:

dept_id avg_salary
1 76500
2 43000
3 42000
4 46000

This shows us the department names and average salaries for each department, for employees with a salary more than 30,000.

 

Best Practices for Views and Materialized Views

When you’re creating views or materialised views, here are some tips to keep in mind:

  • Consider if performance or having up-to-date data is more important when deciding between a view and a materialised view. If performance is more important, use a materialised view. If having up-to-date data is more important, consider a view.
  • Add calculated columns to a materialised view if they are used often. This means your queries will not have to perform the calculation as they already exist as columns.
  • If you’re accessing external databases using a database link, consider using views or materialised views to hide this fact and make queries simpler.
  • Avoid writing views that query other views. If a view needs to be built on another view, then use the base table instead. Otherwise, the impact of changing a table can be hard to work out and can cause maintenance issues.
  • Look for commonly used WITH clauses (or Common Table Expressions), and consider creating them as views if they are used often.

If you want to know if a table (or view) exists before creating a view, so you can check for clashes with existing objects, you can read this article here to find some SQL queries to run.

 

Conclusion

Views are helpful objects in a database that can be used to simplify your queries and improve the security of your system. Materalised views are also very useful, helping simplify queries and improve the performance of queries by storing data in an easy-to-use object.

Overusing views can cause issues for your database by making things more complicated, so be careful when creating too many of them, but using them where needed can be beneficial.

Do you have any questions about views? Let me know in the comments below.

17 thoughts on “SQL Views and Materialized Views: The Complete Guide”

  1. Nice detailed explanation. Thank you.
    There is a typo\error in a query below in a FROM statement. It should be just employee.

    CREATE VIEW employee_rst AS
    SELECT
    emp_id,
    last_name,
    dept_id
    FROM employee_rst;

      1. Another typo under “How to create a view in SQL: “OR REPLACE: this optional query” should read “clause”, not “query”.

        Also, in SQL Server, there are many limitations to materialised views. Firstly, Microsoft decides to call them “indexed views”. Secondly, their construct is achieved by creating a regular view with an additional clause/condition “with schemabinding” which means the underlying table’s schema may not be altered while the view exists. The next step is to then create a unique clustered index against that view – so I suppose in the MS world, the view still remains as simply a named expression, but the actual storage of data is within a separate index object. This setup means there are many limitations as to how the view query may be constructed – far too many to list. But one interesting benefit is that the optimisation engine may rely on the indexed view in order to improve the performance of completely unrelated queries (ie. that are querying tables directly, and not the view), if it determines that using the indexed view would achieve the same result and be faster. In other words, an indexed view can improve system performance across many areas of the system because it gives the optimiser an additional choice for processing other queries too.

        Here is the insanely long explanatory article from MS: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd171921(v=sql.100)?redirectedfrom=MSDN

        1. I forgot to add one other benefit. A table may have only one unique clustered index against it, but if you define a view against that table, you may assign a unique clustered index against that view – effectively meaning you now have two unique clustered indexes against the underlying table and, of course, you can create a third view and so on. In this way, the MS “indexed views” allow you to set up multiple unique clustered indexes against tables – so the optimiser has multiple options to choose from (with the overhead associated with the database maintaining all those indexes each time data is modified in the table).

  2. Kevin Fitzsimons

    Hi,

    I’m pretty sure this comment ’empdept is not updatable because it queries two tables’ is not correct per se. I’ve used a view (containing a 2 table join) to update on Oracle many times

    1. Hi Kevin, good point, that’s good to know. I believe it then has to do with the columns that are inside the view and whether data can be updated with those columns?

  3. Kevin Fitzsimons

    Hi Ben, thanks for the reply. Here’s a typical example that I would demonstrate with (followed by an update on the ‘job’ column)

    SELECT d.name as department, d.location, e.surname as employee, e.job, e.gender, e.salary * 12 as annual_salary
    FROM department d JOIN employee e ON d.department_nr = e.department_nr
    WHERE LOWER(job) IN (‘chairman’,’manager’)
    WITH CHECK OPTION

    I’m not aware of any issue that would stop me updating any of the underlying table columns based on this view.

  4. One Ques.
    What if we will update the materialized view, will it update the source tables?
    if yes then how we can do it.

    1. I don’t believe they are updateable. A quick Google search showed me that they aren’t. The Materialised View is updated when it is refreshed, so it may be better to update the underlying tables.

      1. You can update data through views in SQL Server. SQL Server’s “materialised views” are actually implemented as “indexed views” – meaning a standard view (without materialised data) has a unique clustered index applied to it (and the index, obviously, materialises the data in whatever columns are included). In this case, the index part of the design is irrelevant – because in SQL Server you can update data by issuing an update statement against the view (regardless of whether there is an associated materialised index).

  5. Excellent summary — I appreciate all the examples! I don’t often use the windowing functions, but it’s great to have such a variety presented.

  6. Ben, you are really doing a fantastic job out here, simplifying complex issues and keeping us updated on the database front. Many congratulations and keep up the good job.
    I found a small correction to be made in the second example from the “Examples of Inline Views” above. We are actually selecting the dept_name and the avg_salary there but in the results, the dept_id is shown instead.

  7. SELECT d.dept_name,
    AVG(ed.salary) AS avg_salary
    FROM (
    SELECT e.id,
    e.dept_id,
    e.salary
    FROM employee e
    WHERE e.salary > 30000) AS empdata ed
    INNER JOIN department d ON ed.dept_id = d.dept_id;

    Can you please confirm is this query is correct, because you have use AVG function in outside, while as per clause if we use any aggregate function then have to use group by clause.

Leave a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.