Shelly Cashman Access 2016 | Module 10: SAM Project 1a

West Coast Outdoor Advertisers

USING SQL

 

GETTING STARTED
  • Open the file SC_AC16_10a _FirstLastName_1.accdb, available for download from the SAM website.
  • Save the file as SC_AC16_10a_ FirstLastName_2.accdb by changing the “1” to a “2”.
  • Hint : If you do not see the .accdbfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
    • Open the _ GradingInfoTable table a nd ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

       

      SPECIAL NOTE: SQL QUERIES

      To ensure accurate grading in this project, use the conventions listed below when writing your SQL commands:

      • Do not enclose field names and table names in square brackets []when creating and modifying these SQL queries. All field names and table names consist of single words and do not require brackets.
      • Use parentheses ()in your SQL code only when specified.
      • All SQL commands should end with a semicolon( ;).
      • Only modify the aspects of the SQL commands that are specified in the assignment steps.

         

        PROJECT STEPS
        1. Create a new query in SQL View based on the Statestable with the following requirements:
        2. Add all fields from the Statestable to the new query using the asterisk( *) in the Selectclause.
        3. Be sure to end the SQL command with a semicolon( ;).
        4. Run the query to produce the results shown in Figure 1 on the next page.
        5. Save the query as StatesQuery, and then close the query.


           

          Figure 1: StatesQuery Results

           

          1. Open the AdRepQueryin SQL View, and then add the LastNamefield to the SELECT clause (after the FirstNamefield). Run the query, and ensure that the results match those shown in Figure 2 below Save and close the AdRepQuery.

             

            Figure 2: AdRepQuery Results

             

            1. Open the RentalTotalsQueryin SQL View. Add a computed field to the SELECT clause (after the Monthsfield) as described below:
            2. The computed field should calculate the total cost for the billboard rental by multiplying the Costfield value by the Monthsfield value. ( Hint: Enter Cost * Monthsas the computation.)
            3. Use TotalCostas the name(alias) for this computed field. ( Hint:Use the ASclause.)
            4. Run the query, and ensure that the results match those shown in Figure 3 below. ( Hint:Your records may be in a different order.The last row is not displayed in the figure.) Save and close the RentalTotalsQuery.

               

              Figure 3: Portion of RentalTotalsQuery Results

               


               

              1. Open the RentalsCriteriaQueryin SQL View. Add a WHEREclause to the query in the position shown in Figure 4 below that restricts retrieval to only those rentals where the cost is greater than 800. ( Hint: Figure 4 demonstrates the proper location for the WHERE clause, but it does not show the criteria that should be included in the WHERE clause.) Run the query, and check your results. Save and close the RentalsCriteriaQuery.

                 

                Figure 4: RentalsCriteriaQuery in SQL View

                 

                1. Open the BillboardTypesQueryin SQL View. Add an ORclause to the WHERE clause in the query so that the query retrieves only those records in which the Typefield is equal to Digitalor Poster. ( Hint: Currently, the query only retrieves records where the Typefield is equal to Digital.) Run the query, and ensure that the results match those shown in Figure 5. Save and close the BillboardTypesQuery.

                  Figure 5: BillboardTypesQuery Results

                   

                  1. Open the AdRepStateQueryin SQL View. Add an ANDclause to the WHERE clause in the query so that the query retrieves only those records in which the Statefield is equal to ORand the Cityfield is equal to Portland. ( Hint: Currently, the query only retrieves records in which the Statefield is equal to OR.) Run the query, and ensure that the results match those shown in Figure 6. Save and close the AdRepStateQuery.

                    Figure 6: AdRepStateQuery Results

                     

                    1. Open the OregonCountQueryin SQL View. Modify the SELECT clause to count the number of billboards that are located in OR. ( Hint: Change the SELECT clause to COUNT(BillboardID)). Use the ASclause to set the alias of the function result to StateCount. Run the query and ensure that the results match those shown in Figure 7 below. Save and close the OregonCountQuery.

                       

                      Figure 7: OregonCountQuery Results

                       

                      1. Open the JoinQueryin SQL view. Add a WHERE clause that joins the Billboardtable and the Statestable. The common field in both tables is State. You will need to qualify the Statefield in the WHERE clause. Run the query, and ensure that the results match those shown in Figure 8. ( Hint: All rows are not displayed in the figure. The order of the records may differ. There should be 37 records in the result.) Save and close the JoinQuery.

                         

                        Figure 8: Portion of the JoinQuery Results

                         

                        1. Open the BillboardCirculationQueryin SQL View. Add a caption to the DECfield in the SELECT clause ( Hint: Use the AS clause.) Use Circulationas the caption for the DECfield. Run the query, and confirm that the last field in the query displays as Circulation,as shown in Figure 9 below. Enlarge the size of the caption so the entire caption is displayed. (Hint:Not all records are displayed in the figure. The order of the records may differ.) Save and close the BillboardCirculationQuery.

                          Figure 9: Portion of the BillboardCirculationQueryResults

                           


                           

                          10. Open the SortQueryin SQL View. Modify the query to sort the records in ascendingorder by the Statefield. ( Hint: Use the ORDER BYclause.) Run the query, and ensure that the results match those shown in Figure 10 below. Save and close the SortQuery.

                           

                          Figure 10: SortQuery Results

                           

                          11. Open the GroupingQueryin SQL View. Modify the query by completing the following tasks:

                          1. Add the Statefield to the SELECTstatement. The Statefield should appear before the COUNT(BillboardID) function.
                          2. Group the records by the Statefield.
                          3. Sort the records by the Statefield in Ascendingorder.

                            Run the query, and ensure that the results match those shown in Figure 11 below. Save and close the GroupingQuery.

                             

                            Figure 11: GroupingQuery Results

                             

                            12. Open the AdRepSortQueryin SQL View. Modify the query to sort the records first in descendingorder by the Statefield, and then in ascendingorder by the Cityfield. ( Hint: Use the ORDER BY clause.) Run the query, and ensure that the results match those shown in Figure 12 on the next page. Save and close the AdRepSortQuery.

                             

                            Figure 12: AdRepSortQuery Results

                             

                            Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.

                            Shelly Cashman Access 2016 | Module 10: SAM Project 1a

                            West Coast Outdoor Advertisers

                            USING SQL

                             

                            GETTING STARTED
                            • Open the file SC_AC16_10a _FirstLastName_1.accdb, available for download from the SAM website.
                            • Save the file as SC_AC16_10a_ FirstLastName_2.accdb by changing the “1” to a “2”.
                            • Hint : If you do not see the .accdbfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
                              • Open the _ GradingInfoTable table a nd ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

                                 

                                SPECIAL NOTE: SQL QUERIES

                                To ensure accurate grading in this project, use the conventions listed below when writing your SQL commands:

                                • Do not enclose field names and table names in square brackets []when creating and modifying these SQL queries. All field names and table names consist of single words and do not require brackets.
                                • Use parentheses ()in your SQL code only when specified.
                                • All SQL commands should end with a semicolon( ;).
                                • Only modify the aspects of the SQL commands that are specified in the assignment steps.

                                   

                                  PROJECT STEPS
                                  1. Create a new query in SQL View based on the Statestable with the following requirements:
                                  2. Add all fields from the Statestable to the new query using the asterisk( *) in the Selectclause.
                                  3. Be sure to end the SQL command with a semicolon( ;).
                                  4. Run the query to produce the results shown in Figure 1 on the next page.
                                  5. Save the query as StatesQuery, and then close the query.


                                     

                                    Figure 1: StatesQuery Results

                                     

                                    1. Open the AdRepQueryin SQL View, and then add the LastNamefield to the SELECT clause (after the FirstNamefield). Run the query, and ensure that the results match those shown in Figure 2 below Save and close the AdRepQuery.

                                       

                                      Figure 2: AdRepQuery Results

                                       

                                      1. Open the RentalTotalsQueryin SQL View. Add a computed field to the SELECT clause (after the Monthsfield) as described below:
                                      2. The computed field should calculate the total cost for the billboard rental by multiplying the Costfield value by the Monthsfield value. ( Hint: Enter Cost * Monthsas the computation.)
                                      3. Use TotalCostas the name(alias) for this computed field. ( Hint:Use the ASclause.)
                                      4. Run the query, and ensure that the results match those shown in Figure 3 below. ( Hint:Your records may be in a different order.The last row is not displayed in the figure.) Save and close the RentalTotalsQuery.

                                         

                                        Figure 3: Portion of RentalTotalsQuery Results

                                         


                                         

                                        1. Open the RentalsCriteriaQueryin SQL View. Add a WHEREclause to the query in the position shown in Figure 4 below that restricts retrieval to only those rentals where the cost is greater than 800. ( Hint: Figure 4 demonstrates the proper location for the WHERE clause, but it does not show the criteria that should be included in the WHERE clause.) Run the query, and check your results. Save and close the RentalsCriteriaQuery.

                                           

                                          Figure 4: RentalsCriteriaQuery in SQL View

                                           

                                          1. Open the BillboardTypesQueryin SQL View. Add an ORclause to the WHERE clause in the query so that the query retrieves only those records in which the Typefield is equal to Digitalor Poster. ( Hint: Currently, the query only retrieves records where the Typefield is equal to Digital.) Run the query, and ensure that the results match those shown in Figure 5. Save and close the BillboardTypesQuery.

                                            Figure 5: BillboardTypesQuery Results

                                             

                                            1. Open the AdRepStateQueryin SQL View. Add an ANDclause to the WHERE clause in the query so that the query retrieves only those records in which the Statefield is equal to ORand the Cityfield is equal to Portland. ( Hint: Currently, the query only retrieves records in which the Statefield is equal to OR.) Run the query, and ensure that the results match those shown in Figure 6. Save and close the AdRepStateQuery.

                                              Figure 6: AdRepStateQuery Results

                                               

                                              1. Open the OregonCountQueryin SQL View. Modify the SELECT clause to count the number of billboards that are located in OR. ( Hint: Change the SELECT clause to COUNT(BillboardID)). Use the ASclause to set the alias of the function result to StateCount. Run the query and ensure that the results match those shown in Figure 7 below. Save and close the OregonCountQuery.

                                                 

                                                Figure 7: OregonCountQuery Results

                                                 

                                                1. Open the JoinQueryin SQL view. Add a WHERE clause that joins the Billboardtable and the Statestable. The common field in both tables is State. You will need to qualify the Statefield in the WHERE clause. Run the query, and ensure that the results match those shown in Figure 8. ( Hint: All rows are not displayed in the figure. The order of the records may differ. There should be 37 records in the result.) Save and close the JoinQuery.

                                                   

                                                  Figure 8: Portion of the JoinQuery Results

                                                   

                                                  1. Open the BillboardCirculationQueryin SQL View. Add a caption to the DECfield in the SELECT clause ( Hint: Use the AS clause.) Use Circulationas the caption for the DECfield. Run the query, and confirm that the last field in the query displays as Circulation,as shown in Figure 9 below. Enlarge the size of the caption so the entire caption is displayed. (Hint:Not all records are displayed in the figure. The order of the records may differ.) Save and close the BillboardCirculationQuery.

                                                    Figure 9: Portion of the BillboardCirculationQueryResults

                                                     


                                                     

                                                    10. Open the SortQueryin SQL View. Modify the query to sort the records in ascendingorder by the Statefield. ( Hint: Use the ORDER BYclause.) Run the query, and ensure that the results match those shown in Figure 10 below. Save and close the SortQuery.

                                                     

                                                    Figure 10: SortQuery Results

                                                     

                                                    11. Open the GroupingQueryin SQL View. Modify the query by completing the following tasks:

                                                    1. Add the Statefield to the SELECTstatement. The Statefield should appear before the COUNT(BillboardID) function.
                                                    2. Group the records by the Statefield.
                                                    3. Sort the records by the Statefield in Ascendingorder.

                                                      Run the query, and ensure that the results match those shown in Figure 11 below. Save and close the GroupingQuery.

                                                       

                                                      Figure 11: GroupingQuery Results

                                                       

                                                      12. Open the AdRepSortQueryin SQL View. Modify the query to sort the records first in descendingorder by the Statefield, and then in ascendingorder by the Cityfield. ( Hint: Use the ORDER BY clause.) Run the query, and ensure that the results match those shown in Figure 12 on the next page. Save and close the AdRepSortQuery.

                                                       

                                                      Figure 12: AdRepSortQuery Results

                                                       

                                                      Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.

                                                      Shelly Cashman Access 2016 | Module 10: SAM Project 1a

                                                      West Coast Outdoor Advertisers

                                                      USING SQL

                                                       

                                                      GETTING STARTED
                                                      • Open the file SC_AC16_10a _FirstLastName_1.accdb, available for download from the SAM website.
                                                      • Save the file as SC_AC16_10a_ FirstLastName_2.accdb by changing the “1” to a “2”.
                                                      • Hint : If you do not see the .accdbfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
                                                        • Open the _ GradingInfoTable table a nd ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

                                                           

                                                          SPECIAL NOTE: SQL QUERIES

                                                          To ensure accurate grading in this project, use the conventions listed below when writing your SQL commands:

                                                          • Do not enclose field names and table names in square brackets []when creating and modifying these SQL queries. All field names and table names consist of single words and do not require brackets.
                                                          • Use parentheses ()in your SQL code only when specified.
                                                          • All SQL commands should end with a semicolon( ;).
                                                          • Only modify the aspects of the SQL commands that are specified in the assignment steps.

                                                             

                                                            PROJECT STEPS
                                                            1. Create a new query in SQL View based on the Statestable with the following requirements:
                                                            2. Add all fields from the Statestable to the new query using the asterisk( *) in the Selectclause.
                                                            3. Be sure to end the SQL command with a semicolon( ;).
                                                            4. Run the query to produce the results shown in Figure 1 on the next page.
                                                            5. Save the query as StatesQuery, and then close the query.


                                                               

                                                              Figure 1: StatesQuery Results

                                                               

                                                              1. Open the AdRepQueryin SQL View, and then add the LastNamefield to the SELECT clause (after the FirstNamefield). Run the query, and ensure that the results match those shown in Figure 2 below Save and close the AdRepQuery.

                                                                 

                                                                Figure 2: AdRepQuery Results

                                                                 

                                                                1. Open the RentalTotalsQueryin SQL View. Add a computed field to the SELECT clause (after the Monthsfield) as described below:
                                                                2. The computed field should calculate the total cost for the billboard rental by multiplying the Costfield value by the Monthsfield value. ( Hint: Enter Cost * Monthsas the computation.)
                                                                3. Use TotalCostas the name(alias) for this computed field. ( Hint:Use the ASclause.)
                                                                4. Run the query, and ensure that the results match those shown in Figure 3 below. ( Hint:Your records may be in a different order.The last row is not displayed in the figure.) Save and close the RentalTotalsQuery.

                                                                   

                                                                  Figure 3: Portion of RentalTotalsQuery Results

                                                                   


                                                                   

                                                                  1. Open the RentalsCriteriaQueryin SQL View. Add a WHEREclause to the query in the position shown in Figure 4 below that restricts retrieval to only those rentals where the cost is greater than 800. ( Hint: Figure 4 demonstrates the proper location for the WHERE clause, but it does not show the criteria that should be included in the WHERE clause.) Run the query, and check your results. Save and close the RentalsCriteriaQuery.

                                                                     

                                                                    Figure 4: RentalsCriteriaQuery in SQL View

                                                                     

                                                                    1. Open the BillboardTypesQueryin SQL View. Add an ORclause to the WHERE clause in the query so that the query retrieves only those records in which the Typefield is equal to Digitalor Poster. ( Hint: Currently, the query only retrieves records where the Typefield is equal to Digital.) Run the query, and ensure that the results match those shown in Figure 5. Save and close the BillboardTypesQuery.

                                                                      Figure 5: BillboardTypesQuery Results

                                                                       

                                                                      1. Open the AdRepStateQueryin SQL View. Add an ANDclause to the WHERE clause in the query so that the query retrieves only those records in which the Statefield is equal to ORand the Cityfield is equal to Portland. ( Hint: Currently, the query only retrieves records in which the Statefield is equal to OR.) Run the query, and ensure that the results match those shown in Figure 6. Save and close the AdRepStateQuery.

                                                                        Figure 6: AdRepStateQuery Results

                                                                         

                                                                        1. Open the OregonCountQueryin SQL View. Modify the SELECT clause to count the number of billboards that are located in OR. ( Hint: Change the SELECT clause to COUNT(BillboardID)). Use the ASclause to set the alias of the function result to StateCount. Run the query and ensure that the results match those shown in Figure 7 below. Save and close the OregonCountQuery.

                                                                           

                                                                          Figure 7: OregonCountQuery Results

                                                                           

                                                                          1. Open the JoinQueryin SQL view. Add a WHERE clause that joins the Billboardtable and the Statestable. The common field in both tables is State. You will need to qualify the Statefield in the WHERE clause. Run the query, and ensure that the results match those shown in Figure 8. ( Hint: All rows are not displayed in the figure. The order of the records may differ. There should be 37 records in the result.) Save and close the JoinQuery.

                                                                             

                                                                            Figure 8: Portion of the JoinQuery Results

                                                                             

                                                                            1. Open the BillboardCirculationQueryin SQL View. Add a caption to the DECfield in the SELECT clause ( Hint: Use the AS clause.) Use Circulationas the caption for the DECfield. Run the query, and confirm that the last field in the query displays as Circulation,as shown in Figure 9 below. Enlarge the size of the caption so the entire caption is displayed. (Hint:Not all records are displayed in the figure. The order of the records may differ.) Save and close the BillboardCirculationQuery.

                                                                              Figure 9: Portion of the BillboardCirculationQueryResults

                                                                               


                                                                               

                                                                              10. Open the SortQueryin SQL View. Modify the query to sort the records in ascendingorder by the Statefield. ( Hint: Use the ORDER BYclause.) Run the query, and ensure that the results match those shown in Figure 10 below. Save and close the SortQuery.

                                                                               

                                                                              Figure 10: SortQuery Results

                                                                               

                                                                              11. Open the GroupingQueryin SQL View. Modify the query by completing the following tasks:

                                                                              1. Add the Statefield to the SELECTstatement. The Statefield should appear before the COUNT(BillboardID) function.
                                                                              2. Group the records by the Statefield.
                                                                              3. Sort the records by the Statefield in Ascendingorder.

                                                                                Run the query, and ensure that the results match those shown in Figure 11 below. Save and close the GroupingQuery.

                                                                                 

                                                                                Figure 11: GroupingQuery Results

                                                                                 

                                                                                12. Open the AdRepSortQueryin SQL View. Modify the query to sort the records first in descendingorder by the Statefield, and then in ascendingorder by the Cityfield. ( Hint: Use the ORDER BY clause.) Run the query, and ensure that the results match those shown in Figure 12 on the next page. Save and close the AdRepSortQuery.

                                                                                 

                                                                                Figure 12: AdRepSortQuery Results

                                                                                 

                                                                                Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.

                                                                                Shelly Cashman Access 2016 | Module 10: SAM Project 1a Shelly Cashman Access 2016 | Module 10: SAM Project 1a Shelly Cashman Shelly Cashman Access 2016 | Module 10: SAM Project 1a Access 2016 | Module 10: SAM Project 1a

                                                                                West Coast Outdoor Advertisers West Coast Outdoor Advertisers

                                                                                USING SQL

                                                                                USING SQL USING SQL

                                                                                   

                                                                                GETTING STARTED GETTING STARTED
                                                                                • Open the file SC_AC16_10a _FirstLastName_1.accdb, available for download from the SAM website.
                                                                                • Save the file as SC_AC16_10a_ FirstLastName_2.accdb by changing the “1” to a “2”.
                                                                                • Hint : If you do not see the .accdbfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
                                                                                  • Open the _ GradingInfoTable table a nd ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.
                                                                                  • Open the file SC_AC16_10a _FirstLastName_1.accdb, available for download from the SAM website. Open the file SC_AC16_10a _FirstLastName_1.accdb, available for download from the SAM website. SC_AC16_10a _FirstLastName_1.accdb SC_AC16_10a _FirstLastName_1.accdb _FirstLastName_
                                                                                  • Save the file as SC_AC16_10a_ FirstLastName_2.accdb by changing the “1” to a “2”. Save the file as SC_AC16_10a_ FirstLastName_2.accdb by changing the “1” to a “2”. SC_AC16_10a_ FirstLastName_2.accdb SC_AC16_10a_ FirstLastName_2.accdb FirstLastName by changing the “1” to a “2”.
                                                                                  • Hint : If you do not see the .accdbfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
                                                                                    • Open the _ GradingInfoTable table a nd ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website. Hint : If you do not see the .accdbfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. Hint Hint : If you do not see the .accdbfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. .accdb
                                                                                      • Open the _ GradingInfoTable table a nd ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.
                                                                                      • Open the _ GradingInfoTable table a nd ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website. Open the _ _ GradingInfoTable GradingInfoTable table a nd ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

                                                                                           

                                                                                        SPECIAL NOTE: SQL QUERIES SPECIAL NOTE: SQL QUERIES

                                                                                        To ensure accurate grading in this project, use the conventions listed below when writing your SQL commands: To ensure accurate grading in this project, use the conventions listed below when writing your SQL commands:

                                                                                        • Do not enclose field names and table names in square brackets []when creating and modifying these SQL queries. All field names and table names consist of single words and do not require brackets.
                                                                                        • Use parentheses ()in your SQL code only when specified.
                                                                                        • All SQL commands should end with a semicolon( ;).
                                                                                        • Only modify the aspects of the SQL commands that are specified in the assignment steps.
                                                                                        • Do not enclose field names and table names in square brackets []when creating and modifying these SQL queries. All field names and table names consist of single words and do not require brackets. Do not enclose field names and table names in square brackets []when creating and modifying these SQL queries. All field names and table names consist of single words and do not require brackets. Do not Do not enclose field names and table names in square brackets []when creating and modifying these SQL queries. All field names and table names consist of single words and do not require brackets. []
                                                                                        • Use parentheses ()in your SQL code only when specified. Use parentheses ()in your SQL code only when specified. () only
                                                                                        • All SQL commands should end with a semicolon( ;). All SQL commands should end with a semicolon( ;). semicolon ;
                                                                                        • Only modify the aspects of the SQL commands that are specified in the assignment steps. Only modify the aspects of the SQL commands that are specified in the assignment steps.

                                                                                             

                                                                                          PROJECT STEPS PROJECT STEPS
                                                                                          1. Create a new query in SQL View based on the Statestable with the following requirements:
                                                                                          2. Add all fields from the Statestable to the new query using the asterisk( *) in the Selectclause.
                                                                                          3. Be sure to end the SQL command with a semicolon( ;).
                                                                                          4. Run the query to produce the results shown in Figure 1 on the next page.
                                                                                          5. Save the query as StatesQuery, and then close the query.
                                                                                          6. Create a new query in SQL View based on the Statestable with the following requirements: Create a new query in SQL View based on the Statestable with the following requirements: States
                                                                                          7. Add all fields from the Statestable to the new query using the asterisk( *) in the Selectclause. Add all fields from the Statestable to the new query using the asterisk( *) in the Selectclause. States asterisk * Select
                                                                                          8. Be sure to end the SQL command with a semicolon( ;). Be sure to end the SQL command with a semicolon( ;). semicolon ;
                                                                                          9. Run the query to produce the results shown in Figure 1 on the next page. Run the query to produce the results shown in Figure 1 on the next page.
                                                                                          10. Save the query as StatesQuery, and then close the query. Save the query as StatesQuery, and then close the query. StatesQuery




                                                                                               

                                                                                            Figure 1: StatesQuery Results

                                                                                            Figure 1: StatesQuery Results Figure 1: StatesQuery Results

                                                                                               

                                                                                            1. Open the AdRepQueryin SQL View, and then add the LastNamefield to the SELECT clause (after the FirstNamefield). Run the query, and ensure that the results match those shown in Figure 2 below Save and close the AdRepQuery.
                                                                                            2. Open the AdRepQueryin SQL View, and then add the LastNamefield to the SELECT clause (after the FirstNamefield). Run the query, and ensure that the results match those shown in Figure 2 below Save and close the AdRepQuery. Open the AdRepQueryin SQL View, and then add the LastNamefield to the SELECT clause (after the FirstNamefield). Run the query, and ensure that the results match those shown in Figure 2 below Save and close the AdRepQuery. AdRepQuery LastName FirstName AdRepQuery

                                                                                                 

                                                                                              Figure 2: AdRepQuery Results

                                                                                              Figure 2: AdRepQuery Results Figure 2: AdRepQuery Results

                                                                                                 

                                                                                              1. Open the RentalTotalsQueryin SQL View. Add a computed field to the SELECT clause (after the Monthsfield) as described below:
                                                                                              2. The computed field should calculate the total cost for the billboard rental by multiplying the Costfield value by the Monthsfield value. ( Hint: Enter Cost * Monthsas the computation.)
                                                                                              3. Use TotalCostas the name(alias) for this computed field. ( Hint:Use the ASclause.)
                                                                                              4. Run the query, and ensure that the results match those shown in Figure 3 below. ( Hint:Your records may be in a different order.The last row is not displayed in the figure.) Save and close the RentalTotalsQuery.
                                                                                              5. Open the RentalTotalsQueryin SQL View. Add a computed field to the SELECT clause (after the Monthsfield) as described below: Open the RentalTotalsQueryin SQL View. Add a computed field to the SELECT clause (after the Monthsfield) as described below: RentalTotalsQuery Months
                                                                                              6. The computed field should calculate the total cost for the billboard rental by multiplying the Costfield value by the Monthsfield value. ( Hint: Enter Cost * Monthsas the computation.) The computed field should calculate the total cost for the billboard rental by multiplying the Costfield value by the Monthsfield value. ( Hint: Enter Cost * Monthsas the computation.) Cost Months Hint Cost * Months
                                                                                              7. Use TotalCostas the name(alias) for this computed field. ( Hint:Use the ASclause.) Use TotalCostas the name(alias) for this computed field. ( Hint:Use the ASclause.) TotalCost Hint: AS
                                                                                              8. Run the query, and ensure that the results match those shown in Figure 3 below. ( Hint:Your records may be in a different order.The last row is not displayed in the figure.) Save and close the RentalTotalsQuery. Run the query, and ensure that the results match those shown in Figure 3 below. ( Hint:Your records may be in a different order.The last row is not displayed in the figure.) Save and close the RentalTotalsQuery. Hint: RentalTotalsQuery

                                                                                                   

                                                                                                Figure 3: Portion of RentalTotalsQuery Results

                                                                                                Figure 3: Portion of RentalTotalsQuery Results Figure 3: Portion of RentalTotalsQuery Results

                                                                                                   




                                                                                                   

                                                                                                1. Open the RentalsCriteriaQueryin SQL View. Add a WHEREclause to the query in the position shown in Figure 4 below that restricts retrieval to only those rentals where the cost is greater than 800. ( Hint: Figure 4 demonstrates the proper location for the WHERE clause, but it does not show the criteria that should be included in the WHERE clause.) Run the query, and check your results. Save and close the RentalsCriteriaQuery.
                                                                                                2. Open the RentalsCriteriaQueryin SQL View. Add a WHEREclause to the query in the position shown in Figure 4 below that restricts retrieval to only those rentals where the cost is greater than 800. ( Hint: Figure 4 demonstrates the proper location for the WHERE clause, but it does not show the criteria that should be included in the WHERE clause.) Run the query, and check your results. Save and close the RentalsCriteriaQuery. Open the RentalsCriteriaQueryin SQL View. Add a WHEREclause to the query in the position shown in Figure 4 below that restricts retrieval to only those rentals where the cost is greater than 800. ( Hint: Figure 4 demonstrates the proper location for the WHERE clause, but it does not show the criteria that should be included in the WHERE clause.) Run the query, and check your results. Save and close the RentalsCriteriaQuery. RentalsCriteriaQuery WHERE cost is greater than 800 Hint RentalsCriteriaQuery

                                                                                                         

                                                                                                  Figure 4: RentalsCriteriaQuery in SQL View

                                                                                                  Figure 4: RentalsCriteriaQuery in SQL View Figure 4: RentalsCriteriaQuery in SQL View

                                                                                                     

                                                                                                  1. Open the BillboardTypesQueryin SQL View. Add an ORclause to the WHERE clause in the query so that the query retrieves only those records in which the Typefield is equal to Digitalor Poster. ( Hint: Currently, the query only retrieves records where the Typefield is equal to Digital.) Run the query, and ensure that the results match those shown in Figure 5. Save and close the BillboardTypesQuery.
                                                                                                  2. Open the BillboardTypesQueryin SQL View. Add an ORclause to the WHERE clause in the query so that the query retrieves only those records in which the Typefield is equal to Digitalor Poster. ( Hint: Currently, the query only retrieves records where the Typefield is equal to Digital.) Run the query, and ensure that the results match those shown in Figure 5. Save and close the BillboardTypesQuery. Open the BillboardTypesQueryin SQL View. Add an ORclause to the WHERE clause in the query so that the query retrieves only those records in which the Typefield is equal to Digitalor Poster. ( Hint: Currently, the query only retrieves records where the Typefield is equal to Digital.) Run the query, and ensure that the results match those shown in Figure 5. Save and close the BillboardTypesQuery. BillboardTypesQuery OR Type Digital Poster Hint Type BillboardTypesQuery

                                                                                                    Figure 5: BillboardTypesQuery Results

                                                                                                    Figure 5: BillboardTypesQuery Results Figure 5: BillboardTypesQuery Results

                                                                                                       

                                                                                                    1. Open the AdRepStateQueryin SQL View. Add an ANDclause to the WHERE clause in the query so that the query retrieves only those records in which the Statefield is equal to ORand the Cityfield is equal to Portland. ( Hint: Currently, the query only retrieves records in which the Statefield is equal to OR.) Run the query, and ensure that the results match those shown in Figure 6. Save and close the AdRepStateQuery.
                                                                                                    2. Open the AdRepStateQueryin SQL View. Add an ANDclause to the WHERE clause in the query so that the query retrieves only those records in which the Statefield is equal to ORand the Cityfield is equal to Portland. ( Hint: Currently, the query only retrieves records in which the Statefield is equal to OR.) Run the query, and ensure that the results match those shown in Figure 6. Save and close the AdRepStateQuery. Open the AdRepStateQueryin SQL View. Add an ANDclause to the WHERE clause in the query so that the query retrieves only those records in which the Statefield is equal to ORand the Cityfield is equal to Portland. ( Hint: Currently, the query only retrieves records in which the Statefield is equal to OR.) Run the query, and ensure that the results match those shown in Figure 6. Save and close the AdRepStateQuery. AdRepStateQuery AND State OR City Portland Hint State OR AdRepStateQuery.

                                                                                                      Figure 6: AdRepStateQuery Results

                                                                                                      Figure 6: AdRepStateQuery Results Figure 6: AdRepStateQuery Results

                                                                                                         

                                                                                                      1. Open the OregonCountQueryin SQL View. Modify the SELECT clause to count the number of billboards that are located in OR. ( Hint: Change the SELECT clause to COUNT(BillboardID)). Use the ASclause to set the alias of the function result to StateCount. Run the query and ensure that the results match those shown in Figure 7 below. Save and close the OregonCountQuery.
                                                                                                      2. Open the OregonCountQueryin SQL View. Modify the SELECT clause to count the number of billboards that are located in OR. ( Hint: Change the SELECT clause to COUNT(BillboardID)). Use the ASclause to set the alias of the function result to StateCount. Run the query and ensure that the results match those shown in Figure 7 below. Save and close the OregonCountQuery. Open the OregonCountQueryin SQL View. Modify the SELECT clause to count the number of billboards that are located in OR. ( Hint: Change the SELECT clause to COUNT(BillboardID)). Use the ASclause to set the alias of the function result to StateCount. Run the query and ensure that the results match those shown in Figure 7 below. Save and close the OregonCountQuery. OregonCountQuery OR Hint COUNT(BillboardID) AS StateCount OregonCountQuery

                                                                                                               

                                                                                                        Figure 7: OregonCountQuery Results

                                                                                                        Figure 7: OregonCountQuery Results Figure 7: OregonCountQuery Results

                                                                                                           

                                                                                                        1. Open the JoinQueryin SQL view. Add a WHERE clause that joins the Billboardtable and the Statestable. The common field in both tables is State. You will need to qualify the Statefield in the WHERE clause. Run the query, and ensure that the results match those shown in Figure 8. ( Hint: All rows are not displayed in the figure. The order of the records may differ. There should be 37 records in the result.) Save and close the JoinQuery.
                                                                                                        2. Open the JoinQueryin SQL view. Add a WHERE clause that joins the Billboardtable and the Statestable. The common field in both tables is State. You will need to qualify the Statefield in the WHERE clause. Run the query, and ensure that the results match those shown in Figure 8. ( Hint: All rows are not displayed in the figure. The order of the records may differ. There should be 37 records in the result.) Save and close the JoinQuery. Open the JoinQueryin SQL view. Add a WHERE clause that joins the Billboardtable and the Statestable. The common field in both tables is State. You will need to qualify the Statefield in the WHERE clause. Run the query, and ensure that the results match those shown in Figure 8. ( Hint: All rows are not displayed in the figure. The order of the records may differ. There should be 37 records in the result.) Save and close the JoinQuery. JoinQuery Billboard States State State Hint JoinQuery

                                                                                                                 

                                                                                                          Figure 8: Portion of the JoinQuery Results

                                                                                                          Figure 8: Portion of the JoinQuery Results Figure 8: Portion of the JoinQuery Results

                                                                                                             

                                                                                                          1. Open the BillboardCirculationQueryin SQL View. Add a caption to the DECfield in the SELECT clause ( Hint: Use the AS clause.) Use Circulationas the caption for the DECfield. Run the query, and confirm that the last field in the query displays as Circulation,as shown in Figure 9 below. Enlarge the size of the caption so the entire caption is displayed. (Hint:Not all records are displayed in the figure. The order of the records may differ.) Save and close the BillboardCirculationQuery.
                                                                                                          2. Open the BillboardCirculationQueryin SQL View. Add a caption to the DECfield in the SELECT clause ( Hint: Use the AS clause.) Use Circulationas the caption for the DECfield. Run the query, and confirm that the last field in the query displays as Circulation,as shown in Figure 9 below. Enlarge the size of the caption so the entire caption is displayed. (Hint:Not all records are displayed in the figure. The order of the records may differ.) Save and close the BillboardCirculationQuery. Open the BillboardCirculationQueryin SQL View. Add a caption to the DECfield in the SELECT clause ( Hint: Use the AS clause.) Use Circulationas the caption for the DECfield. Run the query, and confirm that the last field in the query displays as Circulation,as shown in Figure 9 below. Enlarge the size of the caption so the entire caption is displayed. (Hint:Not all records are displayed in the figure. The order of the records may differ.) Save and close the BillboardCirculationQuery. BillboardCirculationQuery DEC Hint Circulation Circulation DEC Circulation (Hint: BillboardCirculationQuery

                                                                                                            Figure 9: Portion of the BillboardCirculationQueryResults

                                                                                                            Figure 9: Portion of the BillboardCirculationQueryResults Figure 9: Portion of the BillboardCirculationQueryResults BillboardCirculationQuery

                                                                                                               




                                                                                                               

                                                                                                            10. Open the SortQueryin SQL View. Modify the query to sort the records in ascendingorder by the Statefield. ( Hint: Use the ORDER BYclause.) Run the query, and ensure that the results match those shown in Figure 10 below. Save and close the SortQuery. 10. Open the SortQueryin SQL View. Modify the query to sort the records in ascendingorder by the Statefield. ( Hint: Use the ORDER BYclause.) Run the query, and ensure that the results match those shown in Figure 10 below. Save and close the SortQuery. 10. Open the SortQueryin SQL View. Modify the query to sort the records in ascendingorder by the Statefield. ( Hint: Use the ORDER BYclause.) Run the query, and ensure that the results match those shown in Figure 10 below. Save and close the SortQuery. SortQuery ascending State Hint ORDER BY SortQuery

                                                                                                                   

                                                                                                            Figure 10: SortQuery Results

                                                                                                            Figure 10: SortQuery Results Figure 10: SortQuery Results

                                                                                                               

                                                                                                            11. Open the GroupingQueryin SQL View. Modify the query by completing the following tasks: 11. Open the GroupingQueryin SQL View. Modify the query by completing the following tasks: 11. Open the GroupingQueryin SQL View. Modify the query by completing the following tasks: GroupingQuery

                                                                                                            1. Add the Statefield to the SELECTstatement. The Statefield should appear before the COUNT(BillboardID) function.
                                                                                                            2. Group the records by the Statefield.
                                                                                                            3. Sort the records by the Statefield in Ascendingorder.
                                                                                                            4. Add the Statefield to the SELECTstatement. The Statefield should appear before the COUNT(BillboardID) function. Add the Statefield to the SELECTstatement. The Statefield should appear before the COUNT(BillboardID) function. State SELECT State
                                                                                                            5. Group the records by the Statefield. Group the records by the Statefield. Group Group the records by the Statefield. State
                                                                                                            6. Sort the records by the Statefield in Ascendingorder. Sort the records by the Statefield in Ascendingorder. Sort Sort the records by the Statefield in Ascendingorder. State Ascending

                                                                                                              Run the query, and ensure that the results match those shown in Figure 11 below. Save and close the GroupingQuery. Run the query, and ensure that the results match those shown in Figure 11 below. Save and close the GroupingQuery. GroupingQuery

                                                                                                                 

                                                                                                              Figure 11: GroupingQuery Results

                                                                                                              Figure 11: GroupingQuery Results Figure 11: GroupingQuery Results

                                                                                                                 

                                                                                                              12. Open the AdRepSortQueryin SQL View. Modify the query to sort the records first in descendingorder by the Statefield, and then in ascendingorder by the Cityfield. ( Hint: Use the ORDER BY clause.) Run the query, and ensure that the results match those shown in Figure 12 on the next page. Save and close the AdRepSortQuery. 12. Open the AdRepSortQueryin SQL View. Modify the query to sort the records first in descendingorder by the Statefield, and then in ascendingorder by the Cityfield. ( Hint: Use the ORDER BY clause.) Run the query, and ensure that the results match those shown in Figure 12 on the next page. Save and close the AdRepSortQuery. 12. Open the AdRepSortQueryin SQL View. Modify the query to sort the records first in descendingorder by the Statefield, and then in ascendingorder by the Cityfield. ( Hint: Use the ORDER BY clause.) Run the query, and ensure that the results match those shown in Figure 12 on the next page. Save and close the AdRepSortQuery. AdRepSortQuery descending State ascending City Hint AdRepSortQuery

                                                                                                                     

                                                                                                              Figure 12: AdRepSortQuery Results

                                                                                                              Figure 12: AdRepSortQuery Results Figure 12: AdRepSortQuery Results

                                                                                                                 

                                                                                                              Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project. Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.

Answers

Related Questions

Science : Individual health insurance policy cove...

3 pages 3 pages 3 pages 3 pages...

Humanities : Appropriate guidline and page number...

Retrieve the full-text PDF of this article: Hall DE, Arya S, Schmid KK, et al. Association of frailty screening initiative with posto...

Writing : Foundations for Change...

...

Business Finance : Perceptions,Motivation and Per...

answer 2 essay question in a word document using "Complete the Apply:Perceptions, Motivation, and Performance Management Practices"Complete the A...

Business Finance : The influence of textual cues...

One page article assignment. About sending the email and getting the first impression. I will send the article onces I get response...

Mathematics : Can you help me complete an Algebra...

This algebra quiz consist of Rational functions,Domain, Vertical Asymptotes, Horizontal Asymptotes, Oblique/Slant Asymptotes. I will need...

If you didn't find the right answer

Ask Your Questions, We'll notify you once someone answers it