5.3. Designing Database Queries

A database's primary purpose is to store and help extracting information you are looking for. Unlike databases written on a paper sheets, Kexi database allows you to specify for much more search criterias. Results are returend faster without much dependency of. This all is a power of databases, however to be able to perform efffective queries in your database, you need to learn how to tell the database what are you looking for.

With database queries you can limit data coming from a table to a predefined set of rows and columns as well as dynamically join data coming from multiple tables.

To see how queries work in practice you will create contacts query joining data from two tables persons and phone_numbers designed in chapter 5.1 and filled with data in chapter 5.2.

  1. Create a new empty query by selecting Insert->Query from the menubar. Design window will appear similar to the one presented oi the figure below. The window is horizontally splitted into two areas: query relationships on the top and query columns below.

  2. Select table persons in the drop down list Table: located at the top of the window and click Add button. A graphical representation of the table will appear in the the relations area. Do the same for phone_numbers table to insert it too, as in the figure below.

    contacts query design

  3. Add query relationship using mouse drag & drop technique: click the field id in the table persons table, drag it and drop onto the person field of the phone_numbers table. This will join both fields by creating a new relationship.

  4. Doube-clik the name field in the persons table, to add the field as a query column. In a similar way, add surname, street, house_number, city fields from the persons table and phone from the phone_numbers table.

  5. Query design is now ready to test it. Click Switch to data view button on the toolbar, to switch from design to viewing the data provided as query results.

    Contacts query results

  6. Save the query design for later use by clicking Save button on the toolbar. You can also use File->Save from the menubar or press Ctrl+S keys. Because the query design has not been saved yet, you will be asked to specify a name for it. Enter Contacts text in the caption field.