Topic: Types of requests and how to create them
The purpose of the work: to learn how to create queries to databases.
Brief theoretical part
Queries are a tool for searching and structuring data. A query addressed to one or more tables initiates a selection of a certain piece of data and its transfer to a table generated by the query itself. As a result, you get a subset of the information set of the source tables, formed according to a certain law. If the amount of information being processed is large, the selection of the necessary data into such a subset can significantly reduce the processing time. In client-server systems, where the main databases are stored on a file server, the query system allows you to reduce the amount of information transmitted over the local network
A query is a set of conditions according to which information is retrieved from tables. Running a query generates a new data table, the only difference from regular tables is that by running the query again, its data can be updated in accordance with changes in the information of the query data sources.
Table 1 – Query Options
|Selection (Select) ;||Selecting data in the query result table based on the specified selection criteria|
|Cross (Crosstab)||The query result outputs the statistical values (sum, count, or average) for one of the table fields, depending on two parameters of the other table fields that specify the row and column headings of the cross-query result|
|Creating a table (Make-Table)||Creating a new table in the current or in another database based on information from existing tables|
|Update||Update table data|
|Append||Adding a Recordset to a Table|
|Delete||Deleting table entries according to specified criteria|
Requests are divided into:
with a parameter.
Open the Shipping database from the last lab.
Task 1. Create select queries:
1. All flights with indication of all ships and cargoes.
2. Transportation from Sevastopol to Istanbul.
3. Transportation to Yalta of goods in the amount of more than 300 kg.
4. Transportation to Sukhumi, made no later than 10.02.2016.
5. Transportation to destinations starting with the letter “C”.
On the Create tab, select Query Wizard, select Simple Query . In the Create simple queries window, select the Flights table in the drop-down list of tables and queries and transfer the fields from the table to the query using the button. We transfer the vessel name field from the Vessels table and the cargo field from the Cargoes table.
Click Next , select a detailed (output of each field of each record) report, Next , enter the name of the request: Request 1.1. Ready. Query 1.1 appeared in the database, the result of which is a data table with fields: flight number, vessel name, cargo, cargo quantity, units of measurement, port of departure, date of departure, port of arrival, date of arrival. Thus, the data from the three tables were summarized in one table and most fully display information about the flight.
On the Create tab, select Query Builder and select the following data from the Flights and Vessels tables:
In the selection condition, we put Sevastopol at the point of departure, and Istanbul at the point of destination. Click on the Run button, only records for flights from Sevastopol to Istanbul are selected. We save under the name query 1,2 . Similarly, we execute the query 1,3 . In the selection condition, in the port of arrival we indicate YALTA, and in the field of the amount of cargo> 300.
In request 1,4 , in the field port of arrival, specify SUKHUMI, and in the field date of arrival <= 02/10/2016.
In request 1.5 , in the port of arrival field, specify LIKE C * (letter C in the Russian layout).
Task 2. Create a query with a calculated field:
1. Profit for the voyage performed by the vessel.
2. Time of the vessel in transit.
In the query builder, select the Flights and Vessels tables, select the Flight Code, Vessel Name, Revenues, and Costs fields. The rest can be included as desired. We save the request. Then we make a calculated field – right-click, select Build and enter in the window of the Expression Builder the formula profit = income – costs. Click OK. In the field header, instead of Expression 1, we write Profit per trip. There is a calculated field with the result. We execute and save under the name Request 2.1 .
Similarly, we execute Query 2.2 with the calculated field Flight time.
Task 3. Create a group request:
1. Number of voyages made by each vessel.
1. Income of each vessel for all voyages.
In the query constructor, select two tables Vessels and Flights, fields – Vessel Number and Name from the first, Flight Code – from the second.
Then press the big button
Under all fields in the line “Group operation” the word “Grouping” appeared. In the Flight Code field, change it to “Count” (number). As a result, the rows in the table Flights will be grouped by the number and name of the vessel (it would be possible to leave only the number or only the name, both are selected for convenience). Execute and save the query as Query 3.1 .
In order to calculate the income for all flights of each vessel, instead of Count, you need to put Sum, and select not the Flight Code field, but the Revenue per flight field. Save Query 3.2 .
Task 4. Create a query with a parameter:
Flights made by ship N. This can be both the number and the name of the ship.
Let’s say the selection will be carried out by name. In the constructor, select two tables – Flights and Vessels, fields – the number and name of the vessel, by flights: code, destinations and departures, dates. If you add the Loads table, you can also specify what kind of cargo was carried.
To set the parameter, press the Parameters button to the right of the Totals button and enter the word “Name” in the Parameter field. In this case, we leave the data type as text, since the Vessel Name field has a text type.
We save the parameter. Next to the Vessel name field, in the Selection condition line, write our parameter in square brackets: [Name]. We execute the request. A window appeared asking you to enter a name, enter “Alpha”.
Selected flights on this vessel.
Try entering the first few letters instead of the name. Nothing will be taken away. In order to enter long names by first letters, you need to write the following expression in the selection condition: Like ([Name]+”*”). This means that those records will be selected that begin with the characters specified in the parameter plus any number of any characters.
Task 5. Cross-query by ships and cargoes with totals by the number of transported cargoes.
Create and save a multi-table query. From the COURT table we take the name of the vessel, from CARGO – cargo, from FLIGHTS – the amount of cargo. Save as Request 5.1. Then we call the Query Wizard, cross query. In the Create Crosstab window, under Show, select Query 5.1.
Save as Query5,2_Cross .
We present the work to the teacher.