blob: 5a483c7612c830bc0dd2c1456bad69bb414b0f36 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
<H2>5.3. Designing Database Queries</H2>
<p>
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 <em>queries</em> in your database, you need to learn how to tell the database what are you looking for.
</p>
<p>
With database queries you can limit data coming from a table to a predefined set of rows and columns as well as dynamically <em>join</em> data coming from multiple tables.
</p>
<p>
To see how queries work in practice you will create <em>contacts</em> query joining data from two tables <em>persons</em> and <em>phone_numbers</em> designed in <a href="05_01_00_table_designing.html">chapter 5.1</a> and filled with data in <a href="05_02_00_entering_data_into_tables.html">chapter 5.2</a>.
</p>
<p>
<ol>
<li><p>
Create a new empty query by selecting <nobr><img src="icons/query_newobj.png" class="icon"> <em>Insert->Query</em></nobr> 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.
<!-- TODO update screenshot with names of window's areas -->
</p></li>
<li><p>
Select table <em>persons</em> in the drop down list <em>Table:</em> located at the top of the window and click <em>Add</em> button. A graphical representation of the table will appear in the the relations area. Do the same for <em>phone_numbers</em> table to insert it too, as in the figure below.
<br><img src="img/05_03_00_query_design.png">
<br><em>contacts</em> query design<br><br>
</p></li>
<li><p>
Add query relationship using mouse drag & drop technique: click the field <em>id</em> in the table <em>persons</em> table, drag it and drop onto the <em>person</em> field of the <em>phone_numbers</em> table. This will <b>join both fields by creating a new relationship</b>.
</p></li>
<li><p>
Doube-clik the <em>name</em> field in the <em>persons</em> table, to add the field as a <b>query column</b>. In a similar way, add <em>surname</em>, <em>street</em>, <em>house_number</em>, <em>city</em> fields from the <em>persons</em> table and <em>phone</em> from the <em>phone_numbers</em> table.
</p></li>
<li><p>
Query design is now ready to test it. Click <nobr><img src="icons/state_data.png" class="icon"> <em>Switch to data view</em></nobr> button on the toolbar, to switch from design to viewing the data provided as query results.
<br><img src="img/05_03_00_query_results.png">
<br><em>Contacts</em> query results<br><br>
</p></li>
<li>Save the query design for later use by clicking <nobr><img src="icons/filesave.png" class="icon"> <em>Save</em></nobr> button on the toolbar. You can also use <em>File->Save</em> from the menubar or press <em>Ctrl+S</em> keys. Because the query design has not been saved yet, you will be asked to specify a name for it. Enter <em>Contacts</em> text in the <em>caption</em> field.
</ol>
</p>
|
