Database Queries Queries Queries are questions used to

Database Queries

Queries • Queries are questions used to retrieve information from a database. • Contain criteria to specify the records and fields to be included in the query results. • Can contain wild cards or logical operators to make more complex queries • * and ? • <, >, <=, >=, <>, =, AND, OR, etc… • Named and saved so they can be run again at a later time.

Query Languages • A query language such as SQL (Structured Query Language) provides a set of commands for locating and manipulating data – LIST ALL FOR STATE = “KY” – Not user friendly • Query By Example is another query language – Fill out a form that describes your query – More friendly

Queries Employees table Dataset resulting from querying table for only employees who are Sales Representatives • Queries allow us to ask questions about data • This record set that answers our question is called a dataset

Using Query Design View Tables pane Design pane • Query Design grid has two panes – the table pane and the design pane • Striking the F 6 key will toggle you between sections

Specifying Criteria in a Select Query Fields in design grid allow us to specify criteria for the dataset • Field row – displays the field name • Sort row – enables you to sort the dataset • Show row – controls whether or not you see a field in the dataset • Criteria row – determines the records that will be selected for display

Specifying Criteria – Currency and Operands Currency amount entered without dollar sign Greater than (>) operand • Specify criteria with currency – Without the dollar sign – With or without the decimal point • Use operands such as: – Less than and greater than – Equal to or not equal to

Specifying Criteria – And and Or Or Criterion and resulting dataset And criterion and resulting dataset • OR finds records that can match one or more conditions • AND finds records that must match all criteria specified

Run a Query Run command • Running, or executing, a query is done by clicking the Run command

Add a Total Row in a Query • The total row can be added to the design grid by clicking the Totals Icon Total row added to the query

Calculated Queries • Will find averages, max, min, sum, std • You usually work with one field, not more – If you use 2 fields, you get multiple answers • • Add Totals row to query Open “Group By” menu Choose appropriate function Run query