CS224 Chapter 5 Notes
- ❑ Query Basics: Role and Type
- ❑ A query is a question. The answers to these questions can help us manage the organization's operating environment
- ❑ Asking the right questions is a key to management sucess
- ❑ Our job is to make sure these queries are translated into a format that the database can handle efficiently
- ❑ There are two basic types of queries: select queries and action queries
- ❑ Select Queries - selects and displays records for one more more tables
- ❑ Creating a Simple Select Query
- ❑ Simplest kind requests a list of only a few of the many available fields in a table.
- ❑ In access, you have to work with the QBE screen for any query
- ❑ QBE Example - pgs. 157-161
- ❑ Sorting Options
- ❑ Output Formatting: The Properties Box
- ❑ Data Organization: Grouping
- ❑ Data summaries are often the basis for transforming data into information.
- ❑ Example - pgs. 165-166
- ❑ Parameter Queries
- ❑ queries whose output extent is defined by specified constraints
- ❑ ex. you want to find employee records for employees whose last name is Smith.
- ❑ Parameter query output generally varies with the circumstances of its applications
- ❑ Example - pgs. 167-169
- ❑ Segmented searches - using wildcards - pg. 170
- ❑ You can also place a constraint on a non-string field, such as date, money and so on. - pgs. 171-174
- ❑ Date Input and Output Formatting
- ❑ Some output formatting is essential if the queries are to yield the information required by your end users
- ❑ Ex. listing employee birthdays by month - November vs. Nov vs. 11
- ❑ Remember the formatting doesn't generate the output. It only controls the output presentation
- ❑ Example pgs. 174-182
- ❑ Queries Based on Multiple Sources
- ❑ To get the best-structured and most comprehensive information out of the database, we usually wind up using multiple tables
- ❑ We may use the results of other queries that are themselves based on multiple tables or mix and match queries and tables as data sources
- ❑ Example pgs. 182-184
- ❑ Queries Based on Outer Join Relationships - pgs. 185-188
- ❑ Crosstab Queries
- ❑ One of the most frequently used and useful data organization tools is the cross-classification table. This presents a two-dimensional summary of the available data
- ❑ A powerful tool to summarize huge amounts of data.
- ❑ A crosstab query is used to create a cross-classification table
- ❑ Example - pgs. 188-193
- ❑ Action Queries
- ❑ The Update Query
- ❑ Allows us to modify many table records at a time - very useful
- ❑ Example pgs. 194-197
- ❑ Delete Query
- ❑ Constructed just like the update queries
- ❑ Example - pgs. 198-200
- ❑ Append Query
- ❑ These add data to tables
- ❑ May be controlled through the use of parameters
- ❑ Example pgs. 201 - 204
- ❑ Make Table Queries
- ❑ This can perform adds, deletes and updates without an existing table structure
- ❑ This query makes the appropriate table structure based on the fields that are placed on the QBE grid
- ❑ Example - pgs. 204-205
- ❑ Query Wrap-up
- ❑ Focus in this chapter was on technique, rather than application
- ❑ In combination with other Access tools, queries play a crucial role in developing a well-organized and well-functioning system