Constructing database queries

 Depending on the database software you are using, construct database queries using either Microsoft Access or LibreOffice Base. The details of what queries to construct and directions are included on the pages below. Microsoft Access users: Module 2: Database Queries Assignment MS Access LibreOffice Base users: Module 2: Database Queries Assignment LibreOffice Base PART C: Constructing database reports Define a report for each of the queries in Part (B) of the assignment. Give each report a meaningful title to describe in business terms what is shown in the report. Modify the column headers as needed so that there are spaces between words (or abbreviations) used in the column names. By default, the report uses the table’s column names as column headers. These aren’t always stated in business terms, and renaming the columns in this way makes the report more readable. The data should be sorted the same way in the results of the reports and should include all the columns included in the query. *Instructions:* You are expected to complete this assignment in Microsoft Access or LibreOffice Base. However, I suggest the writer to use the Database Query with MS Access and below you will find the instructions too: Database Queries Assignment MS Access [image: null] [image: icon] *Database Queries Assignment MS Access* In this assessment, you will create queries and retrieve data in Microsoft Access. Create queries that work so that the results are displayed as described in steps 1 through 10 below. Make sure that you save each query using the suggested name. Don’t include columns that are not necessary, and don’t include a table in the query unless it is needed (e.g., if you need one or more columns from that table in the results or the table is needed to complete a relationship). 1. Show SaleID, MName, CreditCardNumber, SaleDate, and TotalSaleAmount. The results should be sorted by MName (ascending). Name the query *MName-Ascending*. 2. Show SaleID, MName, CreditCardNumber, CName, SaleDate, and TotalSaleAmount. The results should be sorted by CName (ascending). Name the query *CName-Ascending*. 3. Show SaleID, MName, MType, CreditCardNumber, CName, CZip, SaleDate, and TotalSaleAmount, and sort them by the TotalSaleAmount in descending order. Name the query *TotalSaleAmount-Descending*. 4. Show the sales, and sort them by the customer’s zip code (CZip) in ascending order. Your query should show the following fields: SaleID, MName, MType, CreditCardNumber, CName, CZip, SaleDate, and TotalSaleAmount. Name the query *Sales-CZip*. 5. Show the total sales amounts (TotalSaleAmount) by customer, and order them by the customer name (CName). Your query should show the customer name and the total amount of sales for each customer. You will need to use the Group By and Sum features in constructing the query. To find the Group By and Sum features, click the Design tab and then Totals (see Figure 1 below). 1. [image: Mod2-DBAssgn-Queries-MSAccess-Fig1.png] Figure 1 2. Include all tables in your query that are needed for the relationship connection. If your query is running correctly, the number of results will be the number of distinct customers that are present in the SALE table. Name the query *TotalSalesByCustomer-CName*. 3. 4. 6. Show the total sales by merchant, and order them by the merchant name (MName). Your query should show the merchant name and the total amount of sales for each merchant. You need to use the Group By and Sum features in constructing the query. If your query is running correctly, the number of results will be the number of distinct merchants that are present in the SALE table. Name the query *TotalSalesByMerchant-MName*. 5. 6. 7. Show the total sales by credit card number, and order them by the credit card number (CreditCardNumber). Your query should show the credit card number and the total amount of sales for each credit card number. You need to use the Group By and Sum features in constructing the query. If your query is running correctly, the number of results will be the number of distinct credit card numbers that are present in the SALE table. Name the query *TotalSalesByCreditCardNumber-CreditCardNumber*. To construct queries for steps 8 through 10, you must setup the query to limit the number of returns. To do that, click the Design tab, then specify the number of desired returns in the Query Setup area (see Figure 2 below). 7. [image: Mod2-DBAssgn-Queries-MSAccess-Fig2.png] 1. Figure 2 2. 3. 4. 8. Retrieve the top three sales by customer name. This query is similar to query 5 above except that you only need to get the customers with the top three total sale amounts. Name this query *SalesByCustomerName-Top3*. 5. 6. 9. Retrieve the top three sales by merchant name. This query is similar to query 6 above except that you only need to get the merchants with the top three total sale amounts. Name this query *SalesByMerchantName-Top3*. 7. 8. 10. Retrieve the top three sales by credit card number. This query is similar to query 7 above except that you only need to get the credit card numbers with three top three total sale amounts. Name the query *SalesByCreditCardNumber-Top3.*