<!-- <!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 returned faster even for large data sets. All this is a power of databases, however to be able to perform effective <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">&Ctrl;<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>