cmpsc 321 database

1: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: a. For each manager, list the first and last name of the manager and the building ID of the building the manager resides in. b. 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. c. 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.) d. Modify the above query so that the building ID of each 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.) e. Show the query and the result to the instructor when you have it finished. 2. 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: a. 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.) b. Is everyone listed? Who is missing? Use an outer join to include them all. c. Modify this query to count the number of referrals each client has made. d. Copy the query and result into a Word document to submit to Canvas. Task 2: Fun with SELECT. buildingid numResidentManagers B1 1 B2 1 B3 0 B4 1 B5 1 B6 0 B7 1 B8 1 B9 0 ccname numReferrals BlingNotes 3 NanoCorp 1 SkyJet 1 WindyCT 1 CommuteAir 0 DelishInc 0 EntertainUs 0 SouthAlps 0 Xilerate 0 3. Try each 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 SUM(msalary) FROM manager e. 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 COUNT(managerid) FROM manager WHERE msalary>70000 Task 3: More fun with joins and aggregates 4. Make a note of the number of corporate clients in the database: ______ a. Join the corpclient table with the apartment table to get a list of corporate client names, the building ID and apartment number of the apartments they are leasing, and the number of rooms in the apartment. Make a note of the number of records: _____ Why is this more than the number of clients in the database? b. Modify the above query so that the total number of rooms leased by each client appears. (Hint: use GROUP BY and remove the building ID and apartment number columns.) Make a note of the number of records: ______ Why is this less than the total number of clients? c. Use an outer join to include all the clients. d. Make sure 0 appears instead of NULL for clients who are leasing 0 rooms. Check Stack Overflow for a hint (https://stackoverflow.com/questions/7602271/how-do-i-get-sumfunction-in-mysql-to-return-0-if-no-values-are-found). e. Copy the query and the result into the document from Task 1. Submit it on Canvas.

 

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.