Help In Database Please:
Lab 4: Inner joins and outer joins Task 1: Some fun with joins and aggregates 1. Use the HAFH database. I want to find the number of managers living in each building. Use the steps below to guide you. The final result should look like this buildingid numResidentManagers For each manager, list the first and last name of the manager and the building ID of the building the manager reside This can be restated as follows: For each building with a manager living in it, list the first and last name of the manager and the building ID Why are some of the buildings not listed in the result? Is there a way to list them as well? (Hint: use an outer join.) Modify the above query so that the building ID of cach building is listed along with the number of managers living there, instead of the names of the managers. (Hint: use GROUP BY and COUNT, and remove the manager names.) Show the query and the result to the instructor when you have it finished. a. b. c. d. c. I want a list of the corporate clients and the number of referrals each has made. Again, use the hints below. The final result should look like this 2. ccname numReferral Delishlnc 0 EntertainUs 0 For each corporate client, display the client's name as well as the name of the client who referred this client. (See example from class.) Is everyone listed? Who is missing? Use an outer join to include them all Modify this query to count the number of referrals each client has made Copy the query and result into a Word document to submit to Canvas. a. b. c. d. Task 2: Fun with SELECT 3. Try cach of these in MySQL (you can use PhpMiniAdmin). a. SELECT 3+4+5 b. SELECT 3+4+NULL+5 C. SELECT SUM mbonus) FROM manager d. SELECT SUMCmsalary) FROM manager c. SELECT SUM(msalary) FROM manager WHERE msalary>50000 f. SELECT SUM(msalary) FROM manager WHERE msalary> 70000 g. SELECT COUNT(managerid) FROM manager WHERE msalary>50000 h. SELECT COUNTCmanagerid) FROM manager WHERE msalary 70000 Lab 4: Inner joins and outer joins Task 1: Some fun with joins and aggregates 1. Use the HAFH database. I want to find the number of managers living in each building. Use the steps below to guide you. The final result should look like this buildingid numResidentManagers For each manager, list the first and last name of the manager and the building ID of the building the manager reside This can be restated as follows: For each building with a manager living in it, list the first and last name of the manager and the building ID Why are some of the buildings not listed in the result? Is there a way to list them as well? (Hint: use an outer join.) Modify the above query so that the building ID of cach building is listed along with the number of managers living there, instead of the names of the managers. (Hint: use GROUP BY and COUNT, and remove the manager names.) Show the query and the result to the instructor when you have it finished. a. b. c. d. c. I want a list of the corporate clients and the number of referrals each has made. Again, use the hints below. The final result should look like this 2. ccname numReferral Delishlnc 0 EntertainUs 0 For each corporate client, display the client's name as well as the name of the client who referred this client. (See example from class.) Is everyone listed? Who is missing? Use an outer join to include them all Modify this query to count the number of referrals each client has made Copy the query and result into a Word document to submit to Canvas. a. b. c. d. Task 2: Fun with SELECT 3. Try cach of these in MySQL (you can use PhpMiniAdmin). a. SELECT 3+4+5 b. SELECT 3+4+NULL+5 C. SELECT SUM mbonus) FROM manager d. SELECT SUMCmsalary) FROM manager c. SELECT SUM(msalary) FROM manager WHERE msalary>50000 f. SELECT SUM(msalary) FROM manager WHERE msalary> 70000 g. SELECT COUNT(managerid) FROM manager WHERE msalary>50000 h. SELECT COUNTCmanagerid) FROM manager WHERE msalary 70000
