what is query in database

what is query in database

Queries are a fundamental means of accessing and displaying data from tables. Queries used to view, update, and analyze data in different ways. Queries can access a single table or multiple tables. For example, you want to view a list of employee id and name, but you do not want to see phone number and other data, you can create a query that displays the employee’s id and name only.

• Tables store all the information in a database, but if you want to view only selected fields and records in the database, you use a query.

• A query extracts specific data from one or more tables based on search criteria.

• For example, you could create a query for the customer table that will list all customers according to the city they belong. Next page will show the output of such query.

A query is a way of extracting specific data or information from a database. As you know tables store all information in database, if you want to view only selected fields and records in a database, you use query.


Types of queries:
There are five types of query in Access. They are:
• Select queries
• Action queries
• Parameter queries
• Crosstab queries
• SQL queries.

Select Queries
Select query is the simplest and the most common type of query. It retrieves data from one or more tables depending on what is needed and displays the result in a datasheet. Select query also use to group records and calculate sums, counts, averages, and other types of totals.

Action Queries
Database undergoes a specific action depending on what was specified in the query itself is known as action query. This can include such things as creating new tables, deleting rows from existing ones and updating records or creating entirely new ones. Action queries are very popular in data management because they allow for many records to be changed at one time.

There are four types of action queries:

• Append Queries: An append query add records from one or more tables to the end of one or more tables. For example, you have some new customers and a database containing a table of information on those customers. To avoid typing all this information into your own database, you can append it to your Customers table.

• Update Queries: An update query makes global changes to a group of records in one or more tables. For example, you can increase employee’s salary by 20 percent for the people within a certain job category. With an update query, you can change data in existing tables.

• Delete Queries: A delete query deletes a group of records from one or more tables. Delete queries always delete entire records, not just selected fields within records. For example, you could use a delete query to remove products that are discontinued or for which there are no orders.

• Make-Table Queries: A make-table query creates a new table from all or part of the data in one or more tables. Make-table queries are helpful for creating a table to export to other Microsoft Access databases or a history table that contains old records. Making a backup copy of a table automatically by using a macro or code.

Parameter Queries
Instead of entering predetermined criteria, you want to prompt users when a query runs; you can create a parameter query. A useful feature of the parameter query is that it can be saved and used again and again whenever we want to ask the same question. When you run a parameter query Access displays dialog box prompting you for the parameter value. You can create a parameter query by enclosing a question in square brackets ([]). For example, if you want to create a parameter query that asks users which id you want to use from the Student’s table, you would type [Which id?] on the Criteria line under the id column. When the query runs, Access will prompt the user for the answer to your question.

Crosstab Queries
You use crosstab queries to calculate and restructure data for easier analysis of your data. Crosstab queries calculate a sum, average, count, or other type of total for data that is grouped by two types of information one downs the left side of the datasheet and another across the top.

SQL Queries
An SQL query is created by using an SQL statement. When you create a query in query Design view, Access constructs the equivalent SQL statements behind the scenes for you. In fact, most query properties in the property sheet in query Design view have equivalent clauses and options available in SQL view. If you want, you can view or edit the SQL statement in SQL view. However, after you make changes to a query in SQL view, the query might not be displayed the way it was previously in Design view.

Tweet