How To Execute A Query In SQL: Using Queries And Data Views
Tags: sql, sqlqueries, datastructures, dataviews
Published: 2023-12-18 22:07:00

<!-- wp:heading -->
<h2 class="wp-block-heading">Five Labs: Using Queries And Data Views</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Welcome to Five Labs, a step-by-step guide to building applications using Five.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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. </p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading">Lab Objectives</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><strong>Getting Started:</strong> To participate in this lab, ensure you have:</p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><!-- wp:list-item -->
<li>Five installed on your system or access to Five on the cloud (with a paid subscription).</li>
<!-- /wp:list-item --></ul>
<!-- /wp:list -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:paragraph {"align":"center"} -->
<p class="has-text-align-center"><strong><mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-cyan-blue-color"><span style="color: #f1ebda; background-color: #4588d8; font-size: calc(12px + 0.390625vw);"><strong>Don't Feel Like Reading?</strong></span></mark>&nbsp;</strong></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph {"align":"center"} -->
<p class="has-text-align-center">Watch the YouTube video and learn how to use Queries and Data Views.</p>
<!-- /wp:paragraph -->

{% embed https://www.youtube.com/watch?v=nnZ8WypgaK4 %}

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:heading -->
<h2 class="wp-block-heading">Step 1: Understanding the Database Structure</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>We begin in the Five development environment, featuring a simple yet functional database with '<strong>Products</strong>' and '<strong>Orders</strong>' tables. The 'Products' table is designed to store details, such as product names, SKUs, and prices. Similarly, the '<strong>Orders</strong>' Table stores information regarding the customers' order</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":2141,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://five.co/wp-content/uploads/2023/11/Frame-59-1-1024x548.png" alt="" class="wp-image-2141"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>You can create these tables by navigating to the '<strong>Data</strong>' section and selecting the '<strong>Table Wizard</strong>' in Five. Here, you can name the tables and add the respective fields as shown in the image above.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2 class="wp-block-heading">Step 2: Creating A Query</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>To start writing SQL queries in Five, go to the '<strong>Data</strong>' section and click on '<strong>Queries</strong>.' Then, add a new query and name it something like '<strong>Product Details Query</strong>.' 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.</p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading">Deep Dive into the SQL Editor<strong>:</strong></h3>
<!-- /wp:heading -->

<!-- wp:list -->
<ul><!-- wp:list-item -->
<li>In the SQL editor, you're free to write intricate SQL queries.</li>
<!-- /wp:list-item -->

<!-- wp:list-item -->
<li>This feature is ideal for those familiar with SQL, offering a familiar workbench-like environment.</li>
<!-- /wp:list-item --></ul>
<!-- /wp:list -->

<!-- wp:image {"id":2143,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://five.co/wp-content/uploads/2023/11/Frame-60-1-1024x548.png" alt="" class="wp-image-2143"/></figure>
<!-- /wp:image -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading">Exploring the Visual Query Designer:</h3>
<!-- /wp:heading -->

<!-- wp:list -->
<ul><!-- wp:list-item -->
<li>The visual query designer caters to users who prefer a more intuitive, visual method of query construction.</li>
<!-- /wp:list-item -->

<!-- wp:list-item -->
<li>Easily add tables, choose fields, and construct queries with simple mouse clicks.</li>
<!-- /wp:list-item --></ul>
<!-- /wp:list -->

<!-- wp:image {"id":2144,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://five.co/wp-content/uploads/2023/11/Frame-61-1024x548.png" alt="" class="wp-image-2144"/></figure>
<!-- /wp:image -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading">Transitioning Between SQL and Visual Designer:</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2 class="wp-block-heading">Step 3: Integrating SQL Queries Through Data Views</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>To integrate SQL queries into your Five applications, start by navigating to '<strong>Visual</strong>' and then '<strong>Data Views</strong>'. 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.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":2145,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://five.co/wp-content/uploads/2023/11/Frame-62-1024x548.png" alt="" class="wp-image-2145"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Start by adding an item and naming it '<strong>Product View</strong>'. Next, in the data source lookup, choose the '<strong>Product Details</strong>' that we recently created.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Then Click on the '<strong>Data Field</strong>s' Tab, here Five will import all the fields from our query. You can customize the presentation by adjusting field sizes and display settings</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Head over to the ‘<strong>Visual</strong>’ section again and select ‘<strong>Menu</strong>’. Here, we’ll create a new menu item. In the ‘<strong>Action</strong>’ field, select '<strong>Product View</strong>'.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":2222,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://five.co/wp-content/uploads/2023/12/Frame-63-2-1024x592.png" alt="" class="wp-image-2222"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Now let's finally run our application by clicking the play ▶️ button located in the top right corner, as shown below.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":2146,"sizeSlug":"full","linkDestination":"none"} -->
<figure class="wp-block-image size-full"><img src="https://five.co/wp-content/uploads/2023/11/image-6.png" alt="" class="wp-image-2146"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>As the application launches, the 'Product View' we've created is displayed, showcasing the data fetched by our '<strong>Product Details</strong>'.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":2148,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://five.co/wp-content/uploads/2023/11/Frame-17-1-1024x548.png" alt="" class="wp-image-2148"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Congratulations you have successfully learned how to write SQL queries and data views in Five!</p>
<!-- /wp:paragraph -->