Home‎ > ‎6 pm class pages‎ > ‎

Cecilia Lopez

 
 
 
 
Database Query
 
 
 

YouTube Video, Basic database query

 

A database query requests specific data. The data comes from database tables. Once the specified data is requested it can help users: view it in different ways, analyze it and then make changes if needed. Queries are very useful database tools. Ultimately they can help users search for data and organize it according to their needs. Management in general finds it very useful to use queries that can perform different actions and carryout an array of calculations.

 

The You Tube video on the left is a slide show on queries.  

 

There are different ways to create a database query. Those different ways are broken up into different types of queries: Select query, Crosstab query, Parameter query, Action query and Structure Query Language (SQL).

 

The Types of queries provides additional information on the different types of queries.

 

Select query is the most easiest and common type of query. The query is very user friendly; a quality which attracts most users. This query gathers specific data that has been requested from one or more tables. It displays the data in the order of specification. The query creates a table that puts the data together and where only one record can be changed at a time. This query creates subsets of data that can be used to answer specific questions. It is also used to provide data for enhanced queries. The primary function of this query is to display data.

 

 

For example:

 

A select query can be created to display all the customers of a company that live in Arizona. To create the query the user should begin in Access. The user should then click on the table “customers” in the database, switch to “design view”, click on “show table” and insert the “customers” table. On the row containing the word “Field”, select the fields: “Customer Name”, “Customer Last Name”, “Address”, “City”, “State”, and “phone number”. Then click “Run”. The query will display all the customers in the database. To narrow the data, go back to design view and in the row containing the word “Criteria” under the column, where you have selected the field “State” type in “Arizona” and then click on “Run” to run the query. The query will then display all the customers of the company that live in Arizona.

 The Simple query provides additional information on how to create a simple query.

 

This You Tube video below is a live demonstration of how to create a simple query.

 

YouTube Video, Creating a Simple Select Query in Microsoft Access 2010

Crosstab query organizes large amounts of data and summarizes it in an easy to read format by using rows and columns.  

 

 

Action query has four query types of its own. An action query in general is used to make changes to a database depending on the specification of the query itself. This can include updating records, making new ones, adding new tables, and adding or deleting columns. Action queries allows for many records to be changed during one single operation. The action query makes it easier for management to make changes to more than one record at a time. The four query types are: Append query, Delete query, Make table query and Update query.

 

Append query uses the results of a query, records from one of more tables, and adds them to the end of an existing table.

Delete query deletes records in a table that were results from a query, records form one or more tables.

Make table query creates a table based on the results of a query.

Update query allows for one or more fields to be updated in a table.

 

The Action query provides additional information on how to create different action queries.

 

Structure Query Language (SQL) is an advanced programming language, used to perform or manipulate calculation on data. SQL has aggregate functions that permit the use of data to perform calculations on multiple rows. These calculations respond by returning a single value.

 

Some of the aggregate functions include:

 

AVG( )

COUNT( )

MIN( )

MAX( )

SUM( )

 

They produce values based on an average, a count, the minimum or maximum and a summation.

 

 

For example, if we have the following data in a table:

 

Product ID

Product Name

Price

1

Cabernet

100.00 100.00

2

Merlot

105.00

3

Cabernet Sauvignon

110.00

4

Syrah

  75.50

5

Zinfandel

215.25

Total

 

 

With the following data, the SQL and its aggregate functions we can answer questions such as:

What is the average price of our wine inventory?

How many wines do we have in our business?

What is the total cost of the wine inventory we have in stock?

 

YouTube Video, Create a Parameter Query

Parameter query requests data by prompting the user with a dialog box, such as criteria in order to locate the data.

 

For example:

 

In Microsoft Access, a parameter query works with other types of queries to display data. It may use a simple query or one of the action queries to located specified data. This command works a condition that must be met by the query and it will communicate with the types of queries to get specifically what a user is after. 

A parameter query may request dates as the criteria to find records. A beginning and ending date would be used and then the query will display all records matching those specified values.

Alternatively, a parameter query can display all payments made to vendor X, all cash disbursements made over $100,000 or all investments made for radio stations. The concept is that the dialog box makes it easy for a user to enter values that will narrow down their search and display exactly what they want to see. If a company needs to see all payments made over $100,000 they can easily do that by entering “100,000” along the criteria row, under column that represents the table for all the records pertaining to payments.

The You Tube video on the left is a live demonstration of how to create a parameter query.

 

 

The Parameter query can provide you with additional information on how to create a parameter for your queries.

 

These queries can ultimate save management lots of time and money. In the end queries provide organized data according to specifications that can be analyzed and used make valuable decisions.

 

 

Comments