Shelly Cashman Access 365 | Module 10: SAM Project A | Connect Marketing GroupUSING SQL | скачать в хорошем качестве

Shelly Cashman Access 365 | Module 10: SAM Project A | Connect Marketing GroupUSING SQL |

Please contact us if you need this project or any other projects in any subject to be completed. WhatsApp: +1-343-321-3276 Email: essayserviceusa@gmail.com WhatsApp Link: https://wa.me/13433213276 Shelly Cashman Access 365 | Module 10: SAM Project A PROJECT STEPS 1. Connect Marketing Group is a national company that provides marketing services such as contact research, brand building, and merchandising to retail stores. As a sales analyst working with the company database, you want to use the extended management capabilities available in Structured Query Language (SQL) to retrieve information. Create a new query in SQL View based on the Services table as follows to list the services that Connect Marketing provides: a. Add all fields from the Services table to the new query using an asterisk (*) in the SELECT clause. b. Be sure to end the SQL command with a semicolon (;). c. Save the query using ServiceList as the query name, run the query to produce the results shown in Figure 1, and then close the query. Figure 1: ServiceList Query Results 2. Open the ProgramContactList query in SQL View to update the query. Add the LastName field to the SELECT clause after the FirstName field to include a missing detail about the program contacts. Run the query and confirm that the results match those shown in Figure 2. Save and close the ProgramContactList query. Figure 2: ProgramContactList Query Results 3. Open the AnnualBill query in SQL View, which is supposed to show both the monthly and annual bill amounts for each account. Modify the query as follows: a. Add a computed field to the SELECT clause after the MonthlyBill field that calculates the total annual bill amount by multiplying the MonthlyBill field by 12. b. Add an AS clause to use AnnualBill as the name (alias) for this computed field. c. Run the query and confirm that the results match those shown in Figure 3. (Hint: Your records may be in a different order.) Save and close the AnnualBill query. Figure 3: AnnualBill Query Results 4. Open the CompanyCriteria query in SQL View. Add a WHERE clause to the query to retrieve only those companies where the number of retail stores is greater than 25. Run the query and confirm that the results match those shown in Figure 4, with 11 records meeting the criteria. Save and close the CompanyCritiera query. Figure 4: CompanyCriteria Query Results 5. Open the CompanyRegions query in SQL View. The query currently selects companies in the Midwest, but you also want to retrieve companies in the Northwest. Add an OR clause to the WHERE clause in the query so that the query retrieves records in which the Region field is equal to Midwest or Northwest. Run the query and ensure that the results match those shown in Figure 5. (Hint: Your records may be in a different order.) Save and close the CompanyRegions query. Figure 5: CompanyRegions Query Results 6. Open the ContactCityState query in SQL View. The query currently retrieves records for contacts in California, but you want to review records only for contacts in San Jose, California. Add an AND clause to the WHERE clause in the query so that the query retrieves only those records in which the State field is equal to CA and the City field is equal to San Jose. Run the query, and then confirm that the results match those shown in Figure 6. Save and close the ContactCityState query. Figure 6: ContactCityState Query Results 7. Open the NewYorkCount query in SQL View. The query currently displays the contact IDs for contacts in New York state, but you want to know the number of contacts in California. Modify the query as follows: a. Modify the SELECT clause to count the number of contacts by contact ID. b. Add an AS clause to the SELECT clause to set the alias of the function result to ContactCount. c. Modify the WHERE clause to select contacts in CA. d. Run the query, resize the column to its best fit, and then confirm that the results match those shown in Figure 7. Save and close the NewYorkCount query. Figure 7: NewYorkCount Query Results 8. Open the ProgramsContactsJoin query in SQL View. Modify the query as follows to join tables and show one contact for each program in the query results: a. Add a WHERE clause that joins the Programs table and the ProgramContacts table on the ContactID common field. b. Qualify the field names in the WHERE clause. c. Run the query and confirm that the results match those shown in Figure 8. (Hint: Your records may be in a different order.) Save and close the query. Figure 8: ProgramsContactsJoin Query Results 9. Open the ProgramLocations query in SQL View and clarify the contents of a column in the query results as follows: a. Use an AS clause to add a caption to the National field in the SELECT clause. b. Use AllStates as the caption for the National field. c. Run the query, and confirm that the last field in the query is displayed as AllStates, as shown in Figure ***more