Kexi/Handbook/Building Simple Databases/Designing Database Queries: Difference between revisions

From KDE Wiki Sandbox
m (add prevnext)
No edit summary
 
(9 intermediate revisions by 5 users not shown)
Line 1: Line 1:
<languages/>
<languages/>
<translate>
<translate>
===Designing Database Queries===
===Designing Database Queries=== <!--T:1-->


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 queries in your database, you need to learn how to tell the database what you are looking for.  
<!--T:2-->
The primary purpose of a database is to store data and then provide for the efficient extraction of the information that you are looking for. The '''Kexi''' database allows you to utilise specific and powerful search criteria returning results rapidly even for large data sets. However to be able to run effective queries on your database, you need to learn how to tell the database what you are looking for.  


<!--T:3-->
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.  
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 a contacts query joining data from two tables: persons and phone_numbers (designed in chapter 3.1i and filled with data in chapter 3.2).  
<!--T:4-->
To see how queries work in practice we will create a contacts query joining data from two tables: '''''Persons''''' and '''''Phones''''' (designed [[Special:MyLanguage/Kexi/Handbook/Building Simple Databases/Designing Database Tables|here]] and filled with data [[Special:MyLanguage/Kexi/Handbook/Building Simple Databases/Entering Data Into Tables|here]]).  


# Create a new empty query by selecting <menuchoice>Insert->Query...</menuchoice> from the menubar. The design window will appear. The window is split into two areas: query relationships at the top and query columns below.  
<!--T:5-->
# Select the table persons in the drop down list '''Table:''' located at the top of the window and click the <menuchoice>Add</menuchoice> button. A graphical representation of the table will appear in the relations area. Do the same for the phone_numbers table to insert it too, as in the figure below.  
# Create a new empty query by selecting <menuchoice>Query</menuchoice> from the toolbar. The design window will appear. The window is split into two areas: query relationships at the top and query columns below. </translate>
# Add query relationship using mouse drag & drop technique: click the field id in the table persons table, drag it and drop into the person field of the '''''phone_numbers''''' table. This will join both fields by creating a new relationship.  
<translate>
# Double-click 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.  
<!--T:8-->
# Query design is now ready for testing. Click the <menuchoice>Switch to data view</menuchoice> button on the toolbar, to switch from design to viewing the data provided as query results.  
# Select the table '''''Persons''''' in the drop down list <menuchoice>Table:</menuchoice> located at the top of the window and click the <menuchoice>Insert</menuchoice> button. A graphical representation of the table will appear in the relations area. Do the same for the '''''Phones''''' table to insert it too. </translate>
# Save the query design for later use by clicking the Save button on the toolbar. You can also use <menuchoice>File->Save</menuchoice> from the menubar or press the <keycap>Ctrl+S</keycap> 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 and click the <menuchoice>OK</menuchoice> button.
<translate>
<!--T:9-->
# Add query relationship using mouse drag & drop technique: click the field '''''id''''' in the '''''persons''''' table, drag it and drop into the '''''person''''' field of the '''''phone''''' table. This will join both fields by creating a new relationship. </translate>
<!--FIXME Why have some field/table names in this sentence a replaceable markup, others not? Why have the same field/table names in this document different capitalisation? This happens at several other parts of this documentation as well COMMENT: when the replacables refer to captions they should probably be capitalized, but when they are names they should be all lower case-->
<translate>
<!--T:12-->
# Double-click 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 '''''Phones''''' table. </translate>
<translate>
<!--T:10-->
# Query design is now ready for testing. Click the <menuchoice>Data</menuchoice> button on the toolbar, to switch from design to viewing the data provided as query results.</translate>
<translate>
<!--T:11-->
# Save the query design for later use by clicking the <menuchoice>Save</menuchoice> button on the toolbar. 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 and click the <menuchoice>OK</menuchoice> button.


<!--T:6-->
{{Prevnext2
{{Prevnext2
|prevpage=Special:MyLanguage/Kexi/Handbook/Building_Simple_Databases/Entering_Data_Into_Tables
|prevpage=Special:MyLanguage/Kexi/Handbook/Building_Simple_Databases/Entering_Data_Into_Tables
Line 24: Line 40:
}}
}}


<!--T:7-->
[[Category:Kexi]]
[[Category:Kexi]]
[[Category:Tutorials]]
[[Category:Tutorials]]
</translate>
</translate>

Latest revision as of 19:15, 15 May 2015

Other languages:

Designing Database Queries

The primary purpose of a database is to store data and then provide for the efficient extraction of the information that you are looking for. The Kexi database allows you to utilise specific and powerful search criteria returning results rapidly even for large data sets. However to be able to run effective queries on your database, you need to learn how to tell the database what you are 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 we will create a contacts query joining data from two tables: Persons and Phones (designed here and filled with data here).

  1. Create a new empty query by selecting Query from the toolbar. The design window will appear. The window is split into two areas: query relationships at the top and query columns below.
  2. Select the table Persons in the drop down list Table: located at the top of the window and click the Insert button. A graphical representation of the table will appear in the relations area. Do the same for the Phones table to insert it too.
  3. Add query relationship using mouse drag & drop technique: click the field id in the persons table, drag it and drop into the person field of the phone table. This will join both fields by creating a new relationship.
  4. Double-click 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 Phones table.
  5. Query design is now ready for testing. Click the Data button on the toolbar, to switch from design to viewing the data provided as query results.
  6. Save the query design for later use by clicking the Save button on the toolbar. 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 and click the OK button.
Retrieved from "https://wikisandbox.kde.org/index.php?title=Kexi/Handbook/Building_Simple_Databases/Designing_Database_Queries&oldid=346580"