Query your first dataset

TutorialApplies to: Unified engine deployments

Read, shape, and sort a small dataset defined inside the query.

Before you start

You need Workbench and a running SQL engine. Every example includes its own input rows. No catalog, files, credentials, or external data is needed. These queries create no stored tables. Copy the SQL into Workbench and run the statements in order. Compare the result with the table below the query. WITH names input data for one statement only. Each example can run on its own.

1. Read every row

What are the six orders?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT * FROM orders ORDER BY id;

Expected result

id	category	region	amount
1	Books	North	20
2	Games	South	30
3	Games	North	15
4	Books	North	40
5	Garden	South	25
6	Garden	North	10

VALUES supplies rows. WITH gives them a name for this statement. Change one thing: Use ORDER BY id DESC. Order 6 appears first.

2. Choose columns

Which category belongs to each order?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT id, category FROM orders ORDER BY id;

Expected result

id	category
1	Books
2	Games
3	Games
4	Books
5	Garden
6	Garden

SELECT controls the columns in the result. Change one thing: Select only id. Expect six rows with one column.

3. Name result columns

How can we make result headings clearer?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT id AS order_number, amount AS sales FROM orders WHERE id <= 2 ORDER BY id;

Expected result

order_number	sales
1	20
2	30

AS gives a result column a readable name. Change one thing: Change sales to order_value. Only the heading changes.

4. Calculate a value

What would a ten percent increase look like?

WITH numbers(id, value) AS (VALUES (1, 10), (2, 20), (3, 30))
SELECT id, CAST(value AS DOUBLE) * 1.10 AS adjusted FROM numbers ORDER BY id;

Expected result

id	adjusted
1	11
2	22
3	33

An explicit DOUBLE cast gives the calculation a floating-point input. Change one thing: Change 1.10 to 2.0. Expect 20, 40, and 60.

5. Find distinct values

Which regions appear?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT DISTINCT region FROM orders ORDER BY region;

Expected result

region
North
South

DISTINCT removes duplicate result rows. Change one thing: Select DISTINCT category instead. Expect Books, Games, and Garden.

6. Limit and skip rows

Which orders are second and third by value?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT id, amount FROM orders ORDER BY amount DESC, id LIMIT 2 OFFSET 1;

Expected result

id	amount
2	30
5	25

OFFSET skips sorted rows. A tie-breaker makes the order stable. Change one thing: Remove OFFSET 1. Expect orders 4 and 2.

Continue

Continue to Filter and classify data, or return to Learn Laminar.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Query your first dataset

Before you start

1. Read every row

2. Choose columns

3. Name result columns

4. Calculate a value

5. Find distinct values

6. Limit and skip rows

Continue

​Before you start

​1. Read every row

​2. Choose columns

​3. Name result columns

​4. Calculate a value

​5. Find distinct values

​6. Limit and skip rows

​Continue

Before you start

1. Read every row

2. Choose columns

3. Name result columns

4. Calculate a value

5. Find distinct values

6. Limit and skip rows

Continue