Create a View to show all the first and last names of managers and the count of their employees. Order the result by the highest number of employees first.You must use a correlated Subquery to obtain the employee count on each row. (Hint: put the subquery in the SELECT)You may have a lot of results with 0, that’s fine.Use the View to rename: first_name to “First Name”, last_name to “Last Name” Employee count result to “Employee Count”Call this view: Management_Employee_CountsInclude a second query to display from the View after it is created. Example data: Refer to attached for the sample data refer to attached for the reference of table. Employees-OH-O+PK employee_idManagesfirst_namelast_nameemailphone_numberhire_datejob_id [FK]salarycommission_pctmanager_id [FK]department_id [FK]-HeadsRegionsPK region_idregion_nameHiredHo -Grouped-O+ PK job_idjob_titlemin_salarymax_salary-Attached-OJobsDepartmentsO+ PK department_idXEdepartment_namemanager_id [FK]location_id [FK]CountriesPK country_idcountry_nameregion_id [FK]PKDetailed PKHo-LocatedHo-ResidesJob Historyemployee_id [FK]start_dateend_datedepartment_id [FK]job_id [FK]-Associated-Locationso+ PK location_idstreet_addresspostal codecitystate_provincecountry_id [FK] First NameStevenShantaKevinMatthewAdamPavamAlbertoJohnKarenEleniGeraldNancyLast NameKingVollmanMourgosWeissFrippKauflingErrazurizRussellPartnersZlotkeyCambraultGreenbergEmployee Count1488888666665

Answered: Create a View to show all the first and…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

Create a View to show all the first and last names of managers and the count of their employees. Order the result by the highest number of employees first.
You must use a correlated Subquery to obtain the employee count on each row.
(Hint: put the subquery in the SELECT)

You may have a lot of results with 0, that’s fine.

Use the View to rename:
first_name to “First Name”,
last_name to “Last Name”
Employee count result to “Employee Count”

Call this view: Management_Employee_Counts

Include a second query to display from the View after it is created.

Example data: Refer to attached for the sample data

refer to attached for the reference of table.

Employees
-OH
-O+PK employee_id
Manages
first_name
last_name
email
phone_number
hire_date
job_id [FK]
salary
commission_pct
manager_id [FK]
department_id [FK]
-Heads
Regions
PK region_id
region_name
Hired
Ho -Grouped
-O+ PK job_id
job_title
min_salary
max_salary
-Attached-
O
Jobs
Departments
O+ PK department_id
XE
department_name
manager_id [FK]
location_id [FK]
Countries
PK country_id
country_name
region_id [FK]
PK
Detailed PK
Ho
-Located
Ho-Resides
Job History
employee_id [FK]
start_date
end_date
department_id [FK]
job_id [FK]
-Associated-
Locations
o+ PK location_id
street_address
postal code
city
state_province
country_id [FK]

First Name
Steven
Shanta
Kevin
Matthew
Adam
Pavam
Alberto
John
Karen
Eleni
Gerald
Nancy
Last Name
King
Vollman
Mourgos
Weiss
Fripp
Kaufling
Errazuriz
Russell
Partners
Zlotkey
Cambrault
Greenberg
Employee Count
14
8
8
8
8
8
6
6
6
6
6
5

Expert Solution

Step by step

Solved in 2 steps with 1 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions