Generating Queries and Reports

Database Relationships

A database is a collection of data stored in a structured and orderly way. A database is a collection of tables with vertical fields and horizontal records. You can create a database with a single table or multiple tables. When you have multiple tables in a database, you need to connect the related records. To connect records, you need to create relationships between the tables. A relationship allows you to connect rows from one table to another. Keys are special fields (or a combination of fields) that allow you to create relationships between tables. Keys can also identify unique records in tables. Let's learn more about the various types of keys.

Exporting a Database to a Spreadsheet

In OpenOffice Base, you can export the data from your database to a spreadsheet. Here are the steps to export your data. Open the database from which you want to export data. Click Tables and then click File. Select New and then select Spreadsheet. In the OpenOffice Base window, right-click the name of the table to export. Select the Copy option. Next, in the Spreadsheet window, right-click cell A1. Choose Paste to save the file. The import and export feature allows you to create a copy of your existing data, add data and graphs from other databases, and transfer data from one program to the other. It also allows you to collect data that you can export occasionally to other programs.

Using Numerical Data in Queries

When you query numerical data, you can use arithmetic operators such as addition (+), subtraction (-), multiplication (*), division (/), and modulo (%). You can also use the following functions to carry out numerical calculations. ABS(x) returns the absolute value of x MOD(x,y) returns the modulus of values, and is the same as x%y FLOOR(x) returns the largest integer value that is less than or equal to x CEILING(x) returns the smallest integer value that is greater than or equal to x POWER(x,y) returns the value of x raised to the power of y SQRT(x) returns the square root value of x Let's consider the example of a table that stores employee details. Here's a query to identify how much the employees' salaries would be after a 20% increase in pay: SELECT "name", salary*1.20 FROM "employee"; This query will display the name and the final salary (after 20% increase) of the employee.

Editing the Report

Once the report is available, you can edit it with a right-click and a choice of Edit. Then use the functions of your word processor (or spreadsheet software) to edit the report to suit your needs. You can also save and print the report the same way as with any other text document. You can create and alter a report in the Design View before the database program creates it. The Design View offers more flexible editing options than a word processor or spreadsheet software. It allows you to create labels, images, lines, and rectangles. select the fields, choose labels, choose the layout, customize the layout

Types of Keys

Primary key: The primary key uniquely identifies each record in a table. It is the main key of the table. In the sample table shown, Employee ID is the primary key because no two employees can have the same ID. Primary keys also enable table searches because they provide a unique identifier. Foreign key: A foreign key is a field in one table, and this key is labeled as a primary key in a related table. In the example, Department ID is a foreign key in the table of employees and a primary key in the table of departments.

Importing a Text File to a Database

Select and open the database into which you want to import the text file. Open the Database View page, and click the Import button. Next, browse and select the text file. Click Open and select the collation you need from the drop-down list. Select the format of the file that you want to import. Then, click Next to move to the next page. Preview the data to make sure that you have mapped it perfectly. Finally, click Import to import the data.

Importing and Exporting a Database

The ability to import and export databases is an important feature offered by almost all the database applications. The import and export features of a database allow you to carry out the following tasks. Create a copy of your existing data. Add data and graphs from other databases or programs, (for example, you can add data from a database to a text file or from a text file to a database). Transfer data from one program to the other (for example, you can move data from a database to a text file or from a text file to a database). Collect data that you can export occasionally to other programs. The import and export features are program-dependent features, and so there is no definite method of carrying out these operations. However, some steps are common to most of the database programs. Let's take a look at these steps.

Querying Data in a Database

The technique to create and use queries is a key part of database technology. You can use a query to state rules to fetch data from a table or from several tables. When you create a query, you can state conditions to narrow down the query results to what is most relevant. For example, you can build a query to fetch records from a sales inventory database for the previous two months. Database queries use a special language called structured query language (SQL). However, it is not important to know the language, because most database programs help you build queries using a wizard. You can also create a query in Design View.

Using the Design View for Querying Data

The third method to query a database is with the Design View. It is an in-between step—between an SQL query, and a query wizard. First, select the option to create a query in the Design View. Next, choose the table and field(s) from which you want to fetch the data. Remember to set the table to "visible." You can also state a sorting condition and search criteria if you want to fetch only those results that match the criteria in a certain order. To add multiple criteria, you can add them either downward or across. When you create a query in the Query Wizard in OpenOffice Base, you can define the search and the condition for the search. You can also use functions to summarize the data.

Relationships between Records

When related records are stored in different tables, you need to create a relationship between the tables to map the records in them. To connect different tables to help you search for related records, you can create a relationship between the tables. Tables in a database can have three types of relationships. Let's take a look at them. one-to-one: Tables can have a one-to-one relationship when every row in one table has a corresponding row in another table. Such relationships are possible only when the tables share the same primary key. one-to-many: Tables can have a one-to-many relationship when every row in one table relates to multiple rows in another table. many-to-many: Tables have a many-to-many relationship when rows in one table relate to multiple rows in another table. One-to-one: Tables can have a One-to-one relationship when every row in one table has a corresponding row in another table. Such relationships are possible only when the tables share the same primary key. one-to-many: Tables can have a one-to-many relationship when every row in one table relates to multiple rows in another table. many-to-many: Tables can have a many-to-many relationship when rows in one table relates to multiple rows in another table.

Using the Query Wizard

You can create a database query with the Query Wizard (in OpenOffice Base as well as Microsoft Access). In OpenOffice base, you need to open the Query View, and select the Query Wizard to create the query. In the Query Wizard dialog box, state the table or tables from which you want to fetch data. Next, choose the fields you want to add in the query. Then, select the order in which you want to display the fetched data. You can display the data in an ascending or descending order. To display specific records in your query, define the search field and the condition for the search. You can also state whether the query should summarize the data. You can use various numerical functions to summarize the data. Finally, you can save the query by clicking on the Finish button.

Generating Reports in a Database

You can produce a printed report based on the information in your database. Reports are useful to summarize and present the data fetched from a database. The program automatically generates database reports from database views. You can create them using specific queries, as well—these reports will only present data fetched by the queries. In OpenOffice Base you can create a report with the Report Generation Wizard in the Reports View. You can also choose to create a report in the Design View. To create a report, select the fields you want to view in the report. You can then choose labels for the selected fields. Your labels must be intuitive and self-explanatory. You can also select groupings and fields across which the report will sort the data. Next, you can choose the layout and alter it to suit your needs. After you are done with all the selections, the database program creates the report on your word processor. You can edit the report to improve the layout or to refine it further.

Using SQL for Querying Data

ou can either create a new query or edit an existing query in SQL. SQL queries usually begin with the word SELECT, which indicates that the query will attempt to fetch data. Next, comes the list of fields to fetch, followed by FROM and the table name. The query below, for example, will fetch all records in table1 (the * stands for "all"): SELECT * FROM "table1" You can add conditions to this basic query to search for specific records with the WHERE command, and specify the conditions within brackets: SELECT * FROM "table1" WHERE ("Month" = 'May') The conditions stated in the brackets can be simple or complex, and you can use the AND and OR operators to further refine your search. SQL is a powerful tool to create queries with complex search conditions.