This is a Clilstore unit. You can link all words to dictionaries.

UNIT 6. DATABASES - MODULE 3

 

PLANTILLA TEMA

LIBRE OFFICE BASE

MODULE 3.

QUERIES





In this module we will learn how to retrieve (obtener, recuperar) information from a LO Base database



Objectives

 

  1. Retrieve information from a database using l’Libre Office Base

  2. Go over (repasar) the concept of relation

  3. Introduce the concept of query

  4. Use of Queries in design view

  5. Use of the Queries Wizzard (asistente de consultas)

  6. Creation of different types of queries: single condition queries, multiple condition, queries based in the use of wild card characters

     

Contents



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,





.

Let's go over the database contents

Open the file Biblioteca.





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

What is a query?

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

Queries on a single table

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:








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




Keep in mind that to set the condition, you can use the logic operators shown in the image below








 

 

 

 






EXERCICI 16. REALITZA LES TRES CONSULTES QUE SE T'ASSIGNARAN AL ATZAR EN AQUEST QUESTIONARI DE MOODLE (Doble clic per hi accedir)



More than one table queries



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 is 'arg'. To Answer this question, it's necessary to design a query combining data from two tables, llibres and pais.









Important: in order to combine information from two tables, they must be linked by a relation



EXERCICI 17. REALITZA LES TRES CONSULTES QUE SE T'ASSIGNARAN AL ATZAR EN AQUEST QUESTIONARI DE MOODLE (Doble clic per hi accedir)



Queries with multiple criteria



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

C42 Llibres en castellà editats abans de 1960 (aquest any inclòs) per l'editorial Edicions 62

C43. Quants llibres hi ha en català o anglès que siguen de nivell Batxillerat i que siguen de l'editorial Edicions 62?

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



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')

 

  1.  



 

 

 

 

 

 

 

 

 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').





 

 

 

 

 

 

 

 

EXAMPLE C41 Quants llibres hi ha en català o anglès

Criteris: codi llengua='cat' O codi llengua='an'




C42 Llibres en castellà editats abans de 1960 (aquest any inclòs) per l'editorial Edicions 62




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

 





 

 

 

 

 

 

 

 

 

 

 




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.



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:



(Note LIKE depending on the Libre Office version installed LIKE might be COM o COMO)





Save the query as Consulta34:Comodin1




Now we are going to see some examples of use








EXERCICI 19. REALITZA LES QUATRE CONSULTES QUE SE T'ASSIGNARAN AL ATZAR EN AQUEST QUESTIONARI DE MOODLE (Doble clic per hi accedir)



 

 

Short url:   http://multidict.net/cs/4682