This is a Clilstore unit. You can .
PLANTILLA TEMA
In this module we will learn how to retrieve (obtener, recuperar) information from a LO Base database
Retrieve information from a database using l’Libre Office Base
Go over (repasar) the concept of relation
Introduce the concept of query
Use of Queries in design view
Use of the Queries Wizzard (asistente de consultas)
Creation of different types of queries: single condition queries, multiple condition, queries based in the use of wild card characters
Open again the file biblioteca.odb. This file contains a database of 1340 records. The database scheme is shown in the image below.
Notice that Llibres is the central table. The rest of tables (except alumnes) are linked with Llibres through a relation,
.
If we click in the icon “taules”, the list of tables now in the database will be shown in the central panel
Open the table llibres. Notice that there is an alien key in Llibres for each table it is related. For example Llibres.Codipais is an alien key with the related table País
A query is a question asked to the database. The query is made out of conditions the records must fulfill in order to be selected. For example get from table alumnes the student records in which the age is greater than 5 and lower than 10
Our first query is going to be this: which are the books edited in 'Argentina, → codi.editor="arg"?
The answer will be this list of 9 books shown in the image below:
Let's make the query:
Open the LO Base main window. Click in the Consultes icon in the left side of the window:
The list of queries is empty. Let's create the first clicking in the option . The window shown below will open:
In this query we need the table llibres. Select it and press afegeix.
The picture below shows a general view of the query design window and its components
Remember the query is: which are the books edited in 'Argentina,? The solution is shown in the image below
Add the fields Registro, Títol, Codi país, Autor to the query. Just doble click on the fields name in the table box
This query must select the books written in Argentina. Therefore we need to know the Argentina's Country code in the table Pais
Open the table Pais and look up the record from Argentina.
The code turns out to be 'arg'
Go back to the query design window and write 'arg' in the cell Criterio-CodiPais
Execute the query and click in the button ejecutar consulta
Finally save the query naming it Consulta0:Llibres_arg
Keep in mind that to set the condition, you can use the logic operators shown in the image below
C1 Make a query showing the fields Títol and autor from the books edited from 1980 onwards ordered by title
Create a new query
Add the table LLibres
Add the fields Títol, Autor, Any Edició
Write >=1980 in the criteri row for the column Any Edició
Disable visible for the column Any edició
Set order by title, selecting ascending in this column
Save the query as Consulta1:Llibres_1980
The query execution must show 41 rows
C2 Make a query showing books from ESO level ordered by title
Create a new query
Add the table llibres
Add the fields registre, títol , autor, codi d'idioma and resum
Set the criterion 'eso', in lowerletter, in the column CodiNivell
Disable visible in CodiNivell
Set ascending order for títol
Save the query as Consulta2:Llibres_ESO
The query shows 68 records
Copy and paste the query result to the quizz question.
Also, write the number of registers.
Upload to the question a screen capture of your desktop showing the query design window
The query which are the books edited in Argentina (country code “arg”), it's very unrealistic, because it takes for granted that we know, or are able to know the code for Argentina ('arg'). To Answer this question, it's necessary to design a query combining data from two tables, llibres and pais.
Make a copy of the previous query: In the main Libre Office Base window, select consultas icon, select the query Consulta2, Copy it and then paste it. Name it Consulta20:Llibres_argentina:
Edit the query, using the option editar from its contextual menu
The table llibres, is already included. Add the table Pais using the button: :
Add to the query the field "pais nom" and disable visible in the field Codi Pais (this last action is optional):
Now having data from both tables we can use the field Pais.nom in order to make the query: select recors where pais. nom='Argentina'
Finally save the query
Execute and watch the results:
Important: in order to combine information from two tables, they must be linked by a relation
Copy and paste the query result to the quizz question.
Also, write the number of registers.
Upload to the question a screen capture of your desktop showing the query design window
The previous queries where designed using only a single criteria. Frequently the questions to which the query must provide an answer, are much more complex and several conditions are needed to solve them. This is possible using the operators AND / OR to link the different conditions
c40. Quants llibres hi ha de nivell eso i escrits en castellà?
Condicions: nivell='eso' and codi llengua='cas'
C41. Quants llibres hi ha en català o anglès Criteris
Condicions: codi llengua='cat' Or codi llengua='an'
C42 Llibres en castellà editats abans de 1960 (aquest any inclòs) per l'editorial Edicions 62
Condicions: codi llengua='cas' and any edicio>=1960 and codi editorial= 'e62'
C43. Quants llibres hi ha en català o anglès que siguen de nivell Batxillerat i que siguen de l'editorial Edicions 62?
Condicions: codi llengua='cat' Or codi llengua='an' and codi nivell='bat' and codi editorial= 'e62'
Operators and /or
Look the criteria shown above, to solve C40, C41, C42, C43. The different conditions are linked by AND and OR highlighted in red, when both conditions must be true, and OR, when at least one or them must be true
Use of brackets
The order in which the operators are applied is very important. The query C43 is ambiguous. There are two different interpretations bringing about (produciendo)two different results. The brackets are needed in order to set the correct execution order
Quants llibres hi ha en català o anglès que siguen de nivell Batxillerat i que siguen de l'editorial Edicions 62
opcio 1: (codi llengua='cat' ) OR (codi llengua='an' AND codi nivell='bat' AND codi editorial= e62)
opcio 2: (codi llengua='cat' OR codi llengua='an') AND (codi nivell='bat' AND codi editorial= e62)
Rules to implement complex conditions
To create complex querys, you need to understand how the query design view works
1. All the conditions in the same row but different columns are linked by an AND operator
2. All conditions in the same raw are inside a couple of brackets
3.All conditions in different rows are linked by the OR operator
(codi llengua='an') O (codi llengua='cat')
4. It's possible to introduce several values in a cell using the operator IN. For example codi llengua='cas' o codi llengua='va' can be replaced by codillengua IN('cas','va')
example C40 . Quants llibres hi ha de nivell eso i escrits en castellà? Criteris (nivell='eso' i codi llengua='cas').
Rule 1, All the conditions in the same row but different columns are linked by an AND operator
Rule 2. All conditions in the same raw are inside a couple of brackets
EXAMPLE C41 Quants llibres hi ha en català o anglès
Criteris: codi llengua='cat' O codi llengua='an'
Rule 1, All the conditions in the same row but different columns are linked by an AND operator
Rule 2 All conditions in different rows are linked by the OR operator
C42 Llibres en castellà editats abans de 1960 (aquest any inclòs) per l'editorial Edicions 62
Criteris: codi llengua='cas' AND any edicio>=1960 AND codi editorial= 'e62'
Rule 1, All the conditions in the same row but different columns are linked by an AND operator
Rule 3 All conditions in different rows are linked by the OR operator
C43. Quants llibres hi ha en català o anglès que siguen de nivell Batxillerat i que siguen de l'editorial Edicions 62? Criteris
There are two possible interpretations
opció 1: (codi llengua='va' ) OR (codi llengua='an' AND codi nivell='bat' AND codi editorial= e62)
opcio 2: ((codi llengua='cat' OR codi llengua='an') AND codi nivell='bat' AND codi editorial= e62)
EXERCICI 18. REALITZA LES QUATRE CONSULTES QUE SE T'ASSIGNARAN AL ATZAR EN AQUEST QUESTIONARI DE MOODLE (Doble clic per hi accedir) Copy and paste the query result to the quizz question.
Also, write the number of registers.
Upload to the question a screen capture of your desktop showing the query design window
QUERIES USING WILDCARD CHARACTERS
It's possible to fix text patterns as selection conditions using wildcards and the operator COMO (or LIKE). For example: In the image below there is a condition LIKE 'Curs*'
The wildcards are:
* Replaces whatever text chain
? Replaces whatever single character
(Note LIKE depending on the Libre Office version installed might be COM or COMO)
Save the query as Consulta34:Comodin1
Now we are going to see some examples of use
LIKE '*català*' will select any record in which there's the substring 'catala':
Nombreproducto LIKE 'Fra*' Selects all the records where their name start with 'Fra'. For example: Fragaria, Fragis, Frangelicus
Nombreproducto LIKE '*elix*' Selects all the records where their name contains wherever (en cualquier lugar) the chain elix. For instance: velixorum, Helixeria
Nombreproducto LIKE 'F??gance*' Selects all the records where their name begins by an “F”, then there are whatever two letters and then the text chain 'gance' For exemple Fragance, Flagance, Frigance etc
Copy and paste the query result to the quizz question.
Also, write the number of registers.
Upload to the question a screen capture of your desktop showing the query design window
UPLOAD TO MOODLE THE FILE BIBLIOTECA INCLUIDING ON IT ALL THE QUERIES ASSIGNED TO YOU
UPLOAD TO MOODLE THE FILE GARDENCO-LLIUREX INCLUIDING ON IT ALL THE QUERIES ASSIGNED TO YOU
Short url: https://clilstore.eu/cs/4682