Web System Workflow

To create a query, select Query Builder in the Tools menu or click New Query in the section Queries. 

First step is selecting the type of query. There are four query types:

• SELECT – selects rows in a table;
• INSERT – adds rows to a table;
• UPDATE – changes table rows;
• DELETE – deletes rows from a table;
• Edit SQL - opens SQL tab for manual query text entry (for experienced users).

Query type selection.

Selecting query type opens Dataset Design tab that visually layouts SQL query. On the left there is a list of tables (Table), views (View), and functions (Function). Double-clicking the name in the list automatically adds it to the visual layout area. This area is intended for setting up joins between the data sets in the query.

A join combines records from two or more tables in a database and creates a set that can be saved as a table or used as is. A join is a means for combining fields from two tables using values common to each.

Join predicate identifies the records for joining. If the evaluated predicate is true, the combined record is then produced in the expected format, a record set or a temporary table, for example.

To create a join, select a field in the first set of data and holding the left mouse button drag it to the desired field in the second set of data. A line will be drawn from the first set of data to the second, creating a join.

Query Builder with open Dataset design tab.

To change the type of join click on the green diamond in the middle of a join line, displaying a drop-down menu with four possible options :

• Left join
  
• Right join
  
•  Inner join
  
• Full join

Inner join creates a new result table by combining column values of two tables based upon the join predicate. The query compares each row of the tables to find all pairs of rows which satisfy the join predicate. When the join predicate is satisfied, column values for each matched pair of rows are combined into a result row.

Outer joins do not require each record in the two joined tables to have a matching record. The joined table retains each record — even if no other matching record exists. Outer joins subdivide further into left joins, right joins, and full joins, depending on which table(s) retains the rows from (left, right, or both).

Left join always contains all records of the "left" table, even if the join condition does not find any matching record in the "right" table. This means that it returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). Right join closely resembles the left join, except with the treatment of the tables reversed.

Full join combines the results of both "left" and "right" tables. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

Editing join type.

When data sets are added and joins are established, click on the tab Expressions to select fields and /or expressions for the columns in the result dataset.

Tab Expressions.

Click Add new to add a field or expression to a query. This displays Add Expression dialog where you can enter the field name and expression.

Add Expression dialog.

All of the available functions are listed on the left under Functions, all fields of datasets are listed in the center, under Fields. Block of operators is on the right. It should be noted that the vertical selection may contain duplicate rows. If you want to get a unique row, you can use the keyword DISTINCT. To do this, set the flag Select Distinct records.

The next optional step lets you select a condition for a query (operator WHERE). To add conditions to a sample click on the tab Where Conditions, then click Add new. This displays the standard fields and expression selection dialog. The WHERE clause eliminates all rows from the result set for which the predicate is not evaluated true.
 

Where Conditions Tab.

Next tab GROUP BY (an optional step) is used to define result-sets to which aggregate functions (COUNT, MIN, MAX, AVG and SUM) may apply. Aggregate functions return a single value, calculated from values in a column.The GROUP BY expression is used in conjunction with the aggregate functions to group the result-set by one or more columns. When a column of data is grouped, NULL values count just like any other value and put in one group for the purposes of aggregate functions. Used without aggregate functions, GROUP BY acts like DISTINCT. It divides the table into groups and returns one row for each group.

Tab HAVING specifies that the result-set will only include rows where aggregate values meet the specified conditions. It is similar to WHERE Condition, but WHERE cannot work with aggregate functions.

WINDOW Clause. WINDOW  is a user defined set of rows. A window function computes a value for each row in a result set derived from the window.  Find out more about window functions here http://www.postgresql.org/docs/current/static/tutorial-window.html

WINDOW dialog.

To sort the rows of a result-set use the tab ORDER BY Expression and click Add New. ORDER BY identifies which columns are used to sort the resulting data, without  an ORDER BY Expression the order of rows returned by a query is undefined.

Order by expression tab.

You can manually edit a query in SQL Tab. Click Apply to finalize changes.  Error message will pop-up if Query Builder finds a mistake.

Click Refresh in Test query tab. It will run the query and result will be displayed in a table format. To edit query click Parameters.

Query result set.