Lab Prejecr No.9 (Access) Points: 50 Qwerying the Southeast Helping Hands Databa
ID: 3709707 • Letter: L
Question
Lab Prejecr No.9 (Access) Points: 50 Qwerying the Southeast Helping Hands Database (It is the continuation of Project 8) Purpose: To demonstrate the ability to create and use queries Problem: You have been asked by the director of the Southeast Helping Hands (SHH) non- profit organization to collect some facts to assist in the planning for the next six months. The director has given you a list of items outlining the type of information needed. You must supply the correet information Instructions: Use the database created in the Project 8, SHH. Using the Memabers table, create queries to provide the following information. 1. A printed member list showing the Member ID, Member Type, and Company sorted by Company within Member Type. 2 A printed list showing the average number of employees in all member organizations. 3. A printed list showing the fewest number of employees listed for a company 4. A printed list with Member ID, Company, Address, Member Type, Exp Date, and Number of Employees of members whose expiration date is on or before 3/30/2004 5. A printed list of members with all their associated contacts. The list should show the Member ID, Company, Contact Name, and Telephone and should be in ascending order by Contact Name within Company 6. Using just the Members table, a printed list that includes the Member ID, Company 7. A printed list showing all members dues. List the Member ID, Company, and Number of 8. A printed count of the companies, grouped by membership type. Member Type, and Number of Employees of all companies that have more than 200 employees and have a membership type R Employees along with the computed field Member Dues. The dues are calculated by charging a member company $10 per employee. Format the dues using dollar signs and two decimal positions. 9. A printed count of the number of contacts for each member company Page 24Explanation / Answer
1) SELECT MemberId, MemberType, Company
FROM Member
GROUP BY MemberType
ORDER BY Company;
2) SELECT AVG(COUNT(Memberid)), Company
FROM Member
GROUP BY Company;
3)SELECT MIN(COUNT(Memberid)), Company
FROM Member
GROUP BY Company;
5)SELECT MemberId, Company, ContactName, Telephone
FROM Member
GROUPBY Company
ORDERBY ContactName ASC;
6) SELECT MemberId, Company, MemberType, count(MemberId) as NumEmployess
FROM Member
GROUP BY Company
HAVING NumEmployees>200 AND MemberType = 'R';
7) SELECT MemberId, Company, count(MemberId) as NumEmployees, NumEmployees*10 as MemberDues
FROM Member;
8) SELECT count(Company)
FROM Member
GROUP BY MemberType;
9)SELECT count(ContactName)
FROM Member
GROUP BY Company;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.