How To Execute A Query In SQL: Using Queries And Data Views

Five Labs: Using Queries And Data Views

Welcome to Five Labs, a step-by-step guide to building applications using Five.

One of the challenges of developing data-driven applications is how to access, manipulate, and present the data in a meaningful way. SQL is a powerful language that allows you to query data from databases and perform various operations on it. In today’s session, you’ll learn how to create SQL queries in Five.

Lab Objectives

By the end of this lab, you’ll understand how to write SQL queries in Five and integrate these queries as data sources in your applications, particularly for creating data views.

Getting Started: To participate in this lab, ensure you have:

• Five installed on your system or access to Five on the cloud (with a paid subscription).

Don’t Feel Like Reading? 

Watch the YouTube video and learn how to use Queries and Data Views.

Step 1: Understanding the Database Structure

We begin in the Five development environment, featuring a simple yet functional database with ‘Products‘ and ‘Orders‘ tables. The ‘Products’ table is designed to store details, such as product names, SKUs, and prices. Similarly, the ‘Orders‘ Table stores information regarding the customers’ order

You can create these tables by navigating to the ‘Data‘ section and selecting the ‘Table Wizard‘ in Five. Here, you can name the tables and add the respective fields as shown in the image above.

Step 2: Creating A Query

To start writing SQL queries in Five, go to the ‘Data‘ section and click on ‘Queries.’ Then, add a new query and name it something like ‘Product Details Query.’ In Five, you can write queries in two ways: using the SQL editor for traditional SQL or the visual query designer for a more intuitive, visual method.

Deep Dive into the SQL Editor:

• In the SQL editor, you’re free to write intricate SQL queries.
• This feature is ideal for those familiar with SQL, offering a familiar workbench-like environment.

Exploring the Visual Query Designer:

• The visual query designer caters to users who prefer a more intuitive, visual method of query construction.
• Easily add tables, choose fields, and construct queries with simple mouse clicks.

Transitioning Between SQL and Visual Designer:

A standout feature of Five is the smooth transition it allows between the SQL editor and the visual designer, whether you prefer directly writing SQL or using a more visual approach to building your queries, you are not limited to one approach.

Step 3: Integrating SQL Queries Through Data Views

To integrate SQL queries into your Five applications, start by navigating to ‘Visual‘ and then ‘Data Views‘. This is where you can use your created SQL queries as data sources and create data views, which then can be used to display information in our end-user application.

Start by adding an item and naming it ‘Product View‘. Next, in the data source lookup, choose the ‘Product Details‘ that we recently created.

Then Click on the ‘Data Fields’ Tab, here Five will import all the fields from our query. You can customize the presentation by adjusting field sizes and display settings

Click on save once you have finalized your data view and now the only step remaining is to add our data view as a menu item so we can view it in our application.

Head over to the ‘Visual’ section again and select ‘Menu’. Here, we’ll create a new menu item. In the ‘Action’ field, select ‘Product View‘.

Now let’s finally run our application by clicking the play ▶️ button located in the top right corner, as shown below.

As the application launches, the ‘Product View’ we’ve created is displayed, showcasing the data fetched by our ‘Product Details‘.

Congratulations you have successfully learned how to write SQL queries and data views in Five!