What are the steps in view resolution when querying a database?

Key takeaways:

  • View resolution in databases retrieves data from a base table through a view, which is a stored query.

  • A view provides a specific way of looking at data without directly querying the base table.

  • View resolution adapts column names in the user’s query to match those in the view.

  • It replaces view references with base table references in the FROM clause.

  • User WHERE conditions are combined with those in the view’s WHERE clause.

  • GROUP BY and HAVING clauses from both the view and user query are included.

  • ORDER BY clauses in the user query are adjusted to match the view’s structure.

View resolution in databases refers to the process by which a database system determines how to retrieve the data you ask for when querying through a view. A view is essentially a stored query that presents a specific way of looking at the data in a database.

Imagine you have a box full of toys and want to find a specific toy. Instead of sifting through the entire box, you have a picture on the box cover representing how the toys are organized. This picture is like a “view.” View resolution, then, is like ensuring that the picture accurately helps you identify and retrieve the toy you’re looking for without directly interacting with the contents of the box.

Now, let’s understand the steps involved in view resolution, using a database example for clarity.

Steps for view resolution

The process of view resolution when querying a view can be broken down into several steps:

Step 1: Adapt the column names in the user’s SELECT clause to match the view’s column names.

Here, we ensure that the column names used in the user’s query align with those defined in the view. For instance, if the user’s query specifies different column names than those used in the view, we adapt them accordingly.
Example: If the user tries to query item_id, but the view defines it as id, we map it to the correct name.

Note: Since our view does not contain the id column in the result, a query asking for it will result in an error.

Step 2: Replace the view name in the FROM clause with the base tables

In this step, any reference to the view in the user’s FROM clause is replaced by the underlying table(s) that the view is based on. This step resolves the view into its base table to continue processing. In our case, the dairy view would be replaced by the items table.
For example,

SELECT * FROM dairy;

The code above will be translated into:

SELECT id, name, price, item_in_stock FROM items WHERE category = 'dairy';

Step 3: Combine WHERE conditions from both the user’s query and the view’s definition

Any conditions specified in the user’s WHERE clause are combined with the conditions specified in the view’s WHERE clause. For instance, if the user adds a filter like price > 2.00, it will be combined with the category = 'dairy' condition from the view.
For example:

SELECT * FROM dairy WHERE price > 2.00;

The code above will be translated into:

SELECT id, name, price, item_in_stock FROM items WHERE category = 'dairy' AND price > 2.00;

Step 4: Include any necessary GROUP BY and HAVING clauses

If the view or the user’s query includes GROUP BY or HAVING clauses, which are integrated during view resolution. In this step, both clauses are directly copied from the defining query and the user query.
Example: If the user’s query includes:

SELECT name, COUNT(*) FROM dairy GROUP BY name;

The view resolution would result in:

SELECT name, COUNT(*) FROM items WHERE category = 'dairy' GROUP BY name;

Step 5: Adapt the ORDER BY clause based on the view’s structure

If the user’s query specifies an ORDER BY clause, the column names are adapted to match those in the view. The result set is ordered based on either the user’s specification or the view’s predefined order, if present.
For example:

SELECT * FROM dairy ORDER BY price DESC;

This query orders the results based on the price in descending order, even if the original view did not specify an ordering.

View resolution in querying a database

To illustrate the steps of view resolution, suppose we have a table defined as follows:

CREATE DATABASE mart;
USE mart;
CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    item_in_stock INT,
    category VARCHAR(60)
);
INSERT INTO items (name, category, price, item_in_stock) VALUES
('Milk', 'Dairy', 200, 90),
('Onion', 'Vegetables', 140, 190),
('Bread', 'Bakery', 110, 64),
('Butter', 'Dairy', 210, 50),
('Cereal', 'Breakfast', 180, 80),
('Eggs', 'Dairy', 120, 300),
('Apples', 'Fruits', 280, 240);
SELECT * FROM items

Explanation

  • Line 1: Create a database named mart.

  • Line 2: Switch to the mart database.

  • Lines 4–10: Create a table items to store the id, name, price, item_in_stock and category for every item.

  • Lines 12–19: Insert items in the table.

  • Line 21: Show the table.

Next, let’s create a view to filter only dairy products:

CREATE VIEW dairy (ItemID, ItemName, ItemPrice, ItemInStock) AS
SELECT id, name, price, item_in_stock
FROM items
WHERE category = 'Dairy';
SELECT id, name , price, item_in_stock FROM items
WHERE  category = 'Dairy' AND item_in_stock > 50 ORDER BY name;

Explanation

  • Lines 1–4: Create a view as dairy and select ItemID, ItemName, ItemPrice, ItemInStock from the items tables where the category is Dairy.

  • Line 6: Display the Dairy view.

Conclusion

View resolution ensures that a query involving a view is broken down into a set of steps to correctly retrieve data from the underlying base tables. These steps involve adapting column names, resolving the view to its base tables, combining conditions, and handling any additional clauses like GROUP BY and ORDER BY. By following these steps, the database guarantees that the view accurately presents the desired subset of data while remaining consistent with the underlying table definitions.

Frequently asked questions

Haven’t found what you were looking for? Contact Us

What are the steps involved in query processing in DBMS?

Steps in query processing in DBMS are:

  1. Parsing
  2. Translation
  3. Optimization
  4. Execution

What are the four types of resolution?

The types of resolution are spatial resolution, spectral resolution, temporal resolution, and radiometric resolution. These measure different aspects of an image’s quality and detail, such as pixel size, wavelength sensitivity, time intervals between images, and the ability to distinguish intensity differences.

Free AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
YouTubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved