Please Provide an answer in MYSQL workbench Write a SELECT statement that return

Question

Please Provide an answer in MYSQL workbench

Write a SELECT statement that returns one row for each employee in the employee table. Additionally return the count of customers for each employee. If the employee has no associated customers list the employee anyway. Display the employee first name and last name and the count of customers.

employee table:

columns:

customer table:

columns:

EmployeeId int(11) AI PK LastName varchar(20) FirstName varchar(20) Title varchar(30) ReportsTo int(11) BirthDate datetime HireDate datetime Address varchar(70) City varchar(40) State varchar(40) Country varchar(40) PostalCode varchar(10) Phone varchar(24) Fax varchar(24) Email varchar(60)

Explanation / Answer

Here In Customer table, you haven't a foreign key for employee table, so For join two table first you have to add a foreign key in the customer table. Here I add a foreign key and the SQL query is:

select e.firstname,e.lastname,count(*) as count from customer c, employee e where e.employeeid = c.employeeid group by e.firstname

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---