Question

Provide SQL statements for the following: (a) Create a view ManagerSummary that lists for every department the department name, manager ID and manager name, manager salary, and the number of employees in that department. The view will have five columns with headings: DeptName, MgrID, MgrName, MgrSalary, and EmpCount.

a) SELECT ManagerSummary
b) CREATE VIEW ManagerSummary
c) INSERT INTO ManagerSummary
d) UPDATE ManagerSummary

Answer 1

To create a view called ManagerSummary in SQL, the correct statement is CREATE VIEW ManagerSummary followed by a SELECT statement to define the data included in the view. After creation, the view can be queried with SELECT ManagerSummary FROM ManagerSummary. Option b is correct.

Step-by-step explanation:

The student has asked for SQL statements to create a view named ManagerSummary and how to query this view. A view in SQL is a virtual table based on the result-set of an SQL statement.

To create the requested view, we would use the CREATE VIEW statement along with a SELECT statement that joins the departments table with the managers and employees table, assuming these tables exist and have the necessary columns to provide the requested information.

Below is an example of how the SQL statement to create the view might look:

CREATE VIEW ManagerSummary AS

JOIN Managers m ON d.MgrID = m.ManagerID

JOIN Employees e ON e. DeptID = d. DepartmentID

GROUP BY d. DeptName, m.MgrID, m.MgrName, m.MgrSalary;

To retrieve data from the ManagerSummary view, one would use the SELECT statement like this:

FROM ManagerSummary;

Hence, to create a view called ManagerSummary in SQL, the correct statement is CREATE VIEW ManagerSummary. Option b is correct.