Skip to main content

Examples of SQL Queries used in ShardOne

With a basic understanding of SQL and its associated clauses established, the next step involves delving into practical examples that illustrate the process of querying datasources using ShardOne.

For that define a few tables and map aliases to them:

Datasource: CustomersMySQL (A MySQL DB)

  • Item: Users
  • Item Alias: Customers
  • Field Alias:
    • ID -> CustomerId
    • Name -> CustomerName
+----+----------------+-----+---------------+
| ID | Name           | Age | City          |
+----+----------------+-----+---------------+
| 1  | John Doe       | 32  | New York      |
| 2  | Jane Smith     | 32  | Los Angeles   |
| 3  | Mike Johnson   | 40  | Chicago       |
| 4  | Sarah Thompson | 28  | Houston       |
| 5  | David Wilson   | 36  | San Francisco |
+----+----------------+-----+---------------+

Datasource: CustomersMongo (A MongoDB)

  • Items: Persons
  • Item Alias: Customers
  • Field Alias:
    • User_ID -> CustomerId
    • Person_Name -> CustomerName
+---------+-----------------+-----+---------------+
| User_ID | Person_Name     | Age | City          |
+---------+-----------------+-----+---------------+
| 101     | Valerie Emerson | 32  | New York      |
| 102     | Sara Medina     | 28  | Los Angeles   |
| 103     | Teresa Faulkner | 40  | Chicago       |
| 104     | Todd Bullock    | 28  | Houston       |
| 105     | Marlene Maxwell | 36  | San Francisco |
+---------+-----------------+-----+---------------+

Datasource: OrdersOracle (An Oracle DB)

Table: Orders

+----+---------+-------+
| ID | OrderID | Price |
+----+---------+-------+
| 1  | 1001    | 50    |
| 2  | 1002    | 75    |
| 3  | 1003    | 60    |
| 4  | 1004    | 45    |
| 6  | 1005    | 55    |
+----+---------+-------+

  1. List all available Customers

    SELECT * FROM Customers;

    image

    As it can be seen, with a single query, ShardOne fetches data from 2 different datasoucres. This was possible because the item and the fields in the 2 tables were mapped to a single Item Alias and respective Field Alias.

  2. List the customers from New York.

    SELECT City, UPPER(CustomerName)
    FROM Customers
    WHERE City = 'New York'

    image

  3. List the first 5 customers sort by age in ascending order

    SELECT * FROM Customers
    ORDER BY Age
    LIMIT 5
    OFFSET 2

    image

  4. List the customers from New York and Los Angeles

    SELECT *
    FROM Customers
    WHERE City = 'New York' OR City = 'Los Angeles'

    image

  5. List the cities where the customers are from.

    SELECT DISTINCT City
    FROM Customers

    image

  6. Count the number of customers from each city.

    SELECT City, COUNT(City) as `Count`
    FROM Customers
    GROUP BY City

    image

  7. List the cities having more than 1 customer

    SELECT City, COUNT(City) as `Count`
     FROM Customers
     GROUP BY City
     HAVING `Count` > 1

    image

  8. List the customers and their orders

     SELECT *
     FROM Customers as c
     JOIN `OrdersOracle.Orders` as o
     ON c.CustomerId = o.ID

    image

    The Orders table are referenced as OrdersOracle.Orders i.e. <Datasource Name> . <Table Name> as there was no Alias mapped to it.

  9. List the details of the orders with prices more than 60

     SELECT *
     FROM Customers as c
     JOIN `MySQL.documentation_demo.Orders` as o
     ON c.CustomerId = o.ID AND o.Price >= 60

    image