summaryrefslogtreecommitdiffstats
path: root/doc/kexi/querydesigning.docbook
blob: 5114dbd0a732df80d293bbf537cc73eb03c1494a (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
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 &amp; 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>