Answer the SQL questions based on the information below

Question

Answer the SQL questions based on the information below

Here is a simple view definition, reporting various statistics about bank branches with associated accounts:
3 CREATE VIEW branch_account_stats AS
SELECT branch_name,
COUNT(*) AS num_accounts,
SUM(balance) AS total_deposits,
AVG(balance) AS avg_balance,
MIN(balance) AS min_balance,
MAX(balance) AS max_balance
FROM account GROUP BY branch_name;

Thus, will need to create a table called mv_branch_account_stats to hold the materialized results of the view branch_account_stats, and will need to add triggers to the account table that update the materialized results as records change in the account table. You must do incremental view maintenance on the materialized results wherever possible. Additionally, your solution will benefit from an index on account, so you should include this index definition in your solution.

1) Write the index definition.
2) Write the table definition for the materialized results mv_branch_account_stats, using the column names specified for the view branch_account_stats.
3) Write the initial SQL DML statement to populate the materialized view table mv_branch_account_stats.
4) Write the view definition for branch_account_stats, using the column names specified.

Explanation

Here in the given question, we have to answer the SQL questions.

Views are something that refers to the virtual data table in the database. They are very useful to optimize the database. Also, they are used to save space in the memory. And also prevents if the data is getting stored again and again in different forms. And addition to this provides extra security to the data.

Index definition
CREATE INDEX idx_branchName
ON account (branch_name);

Table definition for the materialized result set

Populate the table

CREATE MATERIALIZED VIEW mv_branch_account_stats AS
SELECT branch_name,
COUNT(*) AS num_accounts,
SUM(balance) AS total_deposits,
AVG(balance) AS avg_balance,
MIN(balance) AS min_balance,
MAX(balance) AS max_balance
FROM account GROUP BY branch_name;

View definition

CREATE VIEW branch_account_stats AS
SELECT branch_name,
COUNT(*) AS num_accounts,
SUM(balance) AS total_deposits,
AVG(balance) AS avg_balance,
MIN(balance) AS min_balance,
MAX(balance) AS max_balance
FROM account GROUP BY branch_name;

Also read, Rewrite program in C language using appropriate functions and parameter passing.