Question

What SQL syntax would you use to change data in a table if you only had read access to the table? Having read access means the data manipulation would occur in the dataset rather than in the table being pulled from.

Answer 1

Solution:

Sometimes the database administrator wants to restrict direct access to the database. For example, if a table contains various columns, but the user only needs 3 columns of data in such case DBA will create a virtual table of 3 columns. For both purposes, you can use the view. Views can act as a proxy or virtual table and we can do changes in that table. Views reduce the complexity of SQL queries and provide secure access to underlying tables.

View: Views are a special version of tables in SQL. They provide a virtual table environment for various complex operations. You can select data from multiple tables, or you can select specific data based on certain criteria in view. It does not hold the actual data; it holds only the definition of the view in the data dictionary.

The view is a query stored in the data dictionary, on which the user can query just like they do on tables. It does not use physical memory, only the query is stored in the data dictionary. It is computed dynamically, whenever the user performs any query on it. Changes made at any point in view are reflected in the actual base table.

The view has primarily two purposes:

• Simplify the complex SQL queries.
• Provide restrictions to users from accessing sensitive data.

Types of Views:

• Simple View: A view based on only a single table, which doesn't contain GROUP BY clause and any functions.

• Complex View: A view based on multiple tables, which contain GROUP BY clause and functions.

• Inline View: A view based on a subquery in FROM Clause, that subquery creates a temporary table and simplifies the complex query.

• Materialized View: A view that stores the definition as well as data. It creates replicas of data by storing it physically.

Advantages of views:

• Views can be utilized as a subset of actual data to perform certain operations.
• It helps us to provide an abstraction to various users or hide the complexity for users who are accessing data from the table.
• It also simplifies data access from multiple joined tables. It can be used as aggregated tables using group by operations.
• It does not hold any space because it only has the definition in the data dictionary, not the copy of actual data.

Disadvantages of view:

• Views have a dependency on the table structure.
• It has a dependency on base table structure dependency, computation time, and restrictions.

.....

Thanks!