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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
|
<!--
<!DOCTYPE chapter PUBLIC "-//KDE//DTD DocBook XML V4.2-Based Variant V1.1//EN" "dtd/kdex.dtd" >
-->
<sect1 id="designing-queries">
<title>Designing Database Queries</title>
<para>
A database's primary purpose is to store and help extract information
you are looking for. Unlike databases written on a paper sheets, &kexi;
database allows you to specify more search criteria. Results
are returend faster even for large data sets. All this is a power of
databases, however to be able to perform efffective <emphasis>queries</emphasis>
in your database, you need to learn how to tell the database what you are
looking for.
</para>
<para>
With database queries you can limit data coming from a table to a predefined
set of rows and columns as well as dynamically <firstterm>join</firstterm>
data coming from multiple tables.
</para>
<para>
To see how queries work in practice you will create a <literal>contacts</literal>
query joining data from two tables: <literal>persons</literal> and
<literal>phone_numbers</literal> (designed in
<!--<a href="05_01_00_table_designing.html">-->chapter 3.1i
<!--</a>-->
and filled with data in
<!--<a href="05_02_00_entering_data_into_tables.html">-->chapter 3.2
<!--</a>-->).
</para>
<procedure>
<step>
<para>
Create a new empty query by selecting <!--<img src="icons/query_newobj.png" class="icon">-->
<menuchoice><guimenu>Insert</guimenu><guimenuitem>Query</guimenuitem></menuchoice> from
the menubar. The design window will appear. <!--similar to the one presented in the
figure below. -->The window is split into two areas: query
relationships at the top and query columns below.
<!-- TODO update screenshot with names of window's areas -->
</para>
</step>
<step>
<para>
Select the table <literal>persons</literal> in the drop down list <guilabel>Table:</guilabel>
located at the top of the window and click the <guibutton>Add</guibutton> button. A graphical
representation of the table will appear in the relations area. Do the same for the
<literal>phone_numbers</literal> table to insert it too, as in the figure below.
</para>
<!--<screenshot>
<screeninfo><literal>contacts</literal> query design</screeninfo>
<mediaobject>
<imageobject>
<imagedata fileref="img/05_03_00_query_design.png" format="PNG"/>
</imageobject>
<textobject>
<phrase><literal>contacts</literal> query design</phrase>
</textobject>
</mediaobject>
</screenshot>-->
</step>
<step>
<para>
Add query relationship using mouse drag & drop technique: click the field
<literal>id</literal> in the table <literal>persons</literal> table, drag it
and drop into the <literal>person</literal> field of the <literal>phone_numbers</literal>
table. This will <emphasis>join both fields by creating a new relationship</emphasis>.
</para>
</step>
<step>
<para>
Double-click the <literal>name</literal> field in the <literal>persons</literal>
table, to add the field as a <firstterm>query column</firstterm>. In a similar way,
add <literal>surname</literal>, <literal>street</literal>, <literal>house_number</literal>,
<literal>city</literal> fields from the <literal>persons</literal> table and
<literal>phone</literal> from the <literal>phone_numbers</literal> table.
</para>
</step>
<step>
<para>
Query design is now ready for testing. Click the <!--<img src="icons/state_data.png" class="icon">-->
<guibutton>Switch to data view</guibutton> button on the toolbar, to switch from
design to viewing the data provided as query results.
</para>
<!--<screenshot>
<screeninfo><literal>Contacts</literal> query results</screeninfo>
<mediaobject>
<imageobject>
<imagedata fileref="img/05_03_00_query_results.png" format="PNG"/>
</imageobject>
<textobject>
<phrase><literal>Contacts</literal> query results</phrase>
</textobject>
</mediaobject>
</screenshot>-->
</step>
<step>
<para>
Save the query design for later use by clicking the <!--<img src="icons/filesave.png" class="icon">-->
<guibutton>Save</guibutton> button on the toolbar. You can also use
<menuchoice><guimenu>File</guimenu><guimenuitem>Save</guimenuitem></menuchoice>
from the menubar or press the <keycombo action="simul"><keycap>Ctrl</keycap><keycap>S</keycap></keycombo>
keys. Because the query design has not been saved yet, you will be asked to
specify a name for it. Enter <literal>Contacts</literal> text in the
<guilabel>caption</guilabel> field and click the <guibutton>OK</guibutton> button.
</para>
</step>
</procedure>
</sect1>
|
