Load Sample Data in Database
In this guide, we’ll walk you through how to load sample data and test the execution with ease. We’ve included sample queries below so you can get started right away and see how everything works.
Load the Dataset
When creating a new cluster on the portal, you will have the option to load sample data. By selecting this option, two sample tables, “customers” and “orders”, will be loaded into the “SAMPLE_SCHEMA” database. These tables serve as a sandbox environment to explore and test the functionalities of the cluster.
After the database is brought online, we can access its data by using the Query Editor feature available on the portal. This feature allows us to run queries and verify the data stored in the database.
Run some example queries
Here are some example queries that you can use for testing purposes.
1. Calculate verage order amount for each year from the “orders” table and displaying in a bar chart.
SELECT toYear(Order_Date) AS year, round(avg(Order_Total)) AS AverageOrderAmount, bar(AverageOrderAmount, 0, 100000, 80) FROM SAMPLE_SCHEMA.orders GROUP BY year ORDER BY year;
2. Get the average order amount for each year for orders that were delivered in California in ascending order by year.
SELECT toYear(Order_Date) AS year, round(avg(Order_Total)) AS AverageOrderAmount FROM SAMPLE_SCHEMA.orders WHERE State='CA' and Status='Delivered' GROUP BY year ORDER BY year;
3. Selecting the total order amount for each state with an Order Count of at least 10,000, for orders from 2021 and onwards. The results are ordered by total order amount in descending order.
SELECT State, count() AS OrderCount, round(sum(Order_Total)) AS TotalOrderAmount FROM SAMPLE_SCHEMA.orders WHERE toYear(Order_Date)>=2021 GROUP BY State HAVING OrderCount >= 10000 ORDER BY TotalOrderAmount DESC
4. isplaying the customer, order and payment details for specified customer by joining the “orders” and “customers” tables on the “Customer_ID” column.
SELECT c.First_Name||' '||c.Last_Name as Customer_Name, c.Gender, o.Order_Date, o.Order_Total, o.Status, o.Payment_Status, o.Shipping_Method, o.Payment_Method, o.State FROM SAMPLE_SCHEMA.orders o,SAMPLE_SCHEMA.customers c WHERE o.Customer_ID= c.Customer_ID and c.Customer_ID=9665
5. Selecting Minimum and Maximum amount of order that grouped by gender, payment method, year and state in ascending order by joining orders and customers tables.
SELECT c.Gender, o.Payment_Method, toYear(o.Order_Date) as OrderYear, o.State, min(Order_Total) as MinimumOrderAmount, max(Order_Total) as MaximumOrderAmount FROM SAMPLE_SCHEMA.orders o,SAMPLE_SCHEMA.customers c WHERE o.Customer_ID= c.Customer_ID GROUP BY c.Gender, o.Payment_Method, OrderYear, o.State ORDER BY toYear(o.Order_Date);