How to Create SQL Queries

This guide walks through writing SQL in the Query Tool — from creating a table to running joins — with database-specific notes for the three relational engines available as ARPIA repositories: SingleStore (BigData), MySQL, and MariaDB.

For an overview of the Query Tool itself (toolbar, shortcuts, row caps, security model), see the Query Tool overview.

Choose your repository first

Each ARPIA repository is backed by one of three database engines. The repository selector in the Query Tool shows which repositories you have access to — but it does not label the engine. If you're unsure which engine backs a repository, check with your workspace administrator or the Resources configuration.

The core SQL covered in this guide (DDL, DML, joins, aggregations) works on all three engines. Where the dialects diverge, this guide calls out the differences in Dialect notes boxes.

All three speak MySQL-compatible SQL, so most queries are portable. Differences appear in advanced features: SingleStore adds distributed-table syntax (SHARD KEY, SORT KEY, columnstore), and MariaDB adds features like sequences and some storage-engine-specific syntax.

Step 1: Create a Table

Create a table called Users with columns for ID, name, age, and city. The city column is included up front because we'll use it in later examples.

CREATE TABLE Users (
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Age INT,
  City VARCHAR(50)
);

This defines:

• ID — integer, primary key (uniquely identifies each row)
• Name — string up to 50 characters
• Age — integer
• City — string up to 50 characters

📘

Dialect notes — auto-incrementing IDs

If you want IDs assigned automatically rather than supplied manually:

• MySQL / MariaDB: ID INT AUTO_INCREMENT PRIMARY KEY
• SingleStore: ID BIGINT AUTO_INCREMENT PRIMARY KEY (note: SingleStore generates monotonically increasing values per partition, not strictly sequential across the cluster)

📘

Dialect notes — SingleStore distributed tables

By default, SingleStore creates tables as columnstore (analytical) tables and distributes them across the cluster. For tables that need a specific shard key, use:

SQL

CREATE TABLE Users (
  ID INT,
  Name VARCHAR(50),
  Age INT,
  City VARCHAR(50),
  SHARD KEY (ID),
  SORT KEY (ID)
);

This is SingleStore-specific and will not run on MySQL or MariaDB.

Step 2: Insert Data into the Table

Add rows with INSERT INTO:

INSERT INTO Users (ID, Name, Age, City) VALUES (1, 'Juan', 25, 'Madrid');

You can insert multiple rows in a single statement:

INSERT INTO Users (ID, Name, Age, City) VALUES
  (2, 'Maria', 32, 'Barcelona'),
  (3, 'Carlos', 45, 'Madrid'),
  (4, 'Ana', 19, 'Valencia'),
  (5, 'Luis', 50, 'Madrid');

Multi-row inserts are supported in MySQL, MariaDB, and SingleStore.

Step 3: Query Data from the Table

With data in the table, you can retrieve it with SELECT:

-- All rows
SELECT * FROM Users;

-- Specific columns, with a filter
SELECT Name, Age FROM Users WHERE Age > 18;

-- Limit how many rows come back
SELECT * FROM Users LIMIT 10;

📘

Dialect notes — LIMIT syntax

All three engines support LIMIT n. They also all support LIMIT offset, count and LIMIT count OFFSET offset. SQL Server's TOP n syntax does not work in any of them.

Remember: the Query Tool also caps results at 1,000 rows (5,000 for read-only queries) regardless of any LIMIT you write.

Step 4: Update Records in the Table

Use UPDATE to modify existing rows. Always include a WHERE clause unless you genuinely intend to update every row.

UPDATE Users SET Age = 30 WHERE ID = 1;

🚧

Watch out for unfiltered UPDATEs

An UPDATE without a WHERE clause modifies every row in the table. Some installations of MySQL and MariaDB run in safe-updates mode that blocks this, but SingleStore does not. Always preview the affected rows with a SELECT first:

SQL

SELECT * FROM Users WHERE ID = 1;

Step 5: Delete Records from the Table

Use DELETE to remove rows. The same WHERE-clause caution applies.

DELETE FROM Users WHERE Age > 40;

To remove all rows but keep the table structure, TRUNCATE is faster than DELETE without a WHERE:

TRUNCATE TABLE Users;

TRUNCATE is supported in all three engines but cannot be rolled back inside a transaction.

Operators and Joins

Comparison operators

Comparison operators in the WHERE clause filter rows by value:

Example:

SELECT * FROM Users WHERE Age >= 30;
SELECT * FROM Users WHERE City IN ('Madrid', 'Barcelona');
SELECT * FROM Users WHERE Name LIKE 'J%';

Logical operators

Combine conditions with AND, OR, and NOT:

SELECT * FROM Users
WHERE Age >= 30 AND City = 'Madrid';

SELECT * FROM Users
WHERE City = 'Madrid' OR City = 'Barcelona';

SELECT * FROM Users
WHERE NOT City = 'Valencia';

Use parentheses to control precedence:

SELECT * FROM Users
WHERE (Age >= 30 OR Age < 20) AND City = 'Madrid';

Aggregation functions

Aggregation functions return a single value computed across rows:

SELECT COUNT(*) AS TotalUsers FROM Users;
SELECT AVG(Age) AS AverageAge FROM Users;
SELECT City, COUNT(*) AS UsersPerCity
FROM Users
GROUP BY City;

Joins between tables

Joins combine rows from two or more tables based on a related column. Before the join example will run, create a second table:

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  UserID INT,
  Product VARCHAR(100)
);

INSERT INTO Orders (OrderID, UserID, Product) VALUES
  (101, 1, 'Laptop'),
  (102, 1, 'Headphones'),
  (103, 2, 'Monitor'),
  (104, 3, 'Keyboard');

Now you can join Users and Orders:

-- INNER JOIN: only users who have orders
SELECT Users.Name, Orders.Product
FROM Users
INNER JOIN Orders ON Users.ID = Orders.UserID;

-- LEFT JOIN: every user, with their orders if any
SELECT Users.Name, Orders.Product
FROM Users
LEFT JOIN Orders ON Users.ID = Orders.UserID;

-- RIGHT JOIN: every order, with the matching user
SELECT Users.Name, Orders.Product
FROM Users
RIGHT JOIN Orders ON Users.ID = Orders.UserID;

📘

Dialect notes — FULL JOIN

• SingleStore: Supports FULL OUTER JOIN.

• MySQL and MariaDB: Do not support FULL JOIN directly. Emulate it with UNION:

  SQL

  SELECT Users.Name, Orders.Product
  FROM Users LEFT JOIN Orders ON Users.ID = Orders.UserID
  UNION
  SELECT Users.Name, Orders.Product
  FROM Users RIGHT JOIN Orders ON Users.ID = Orders.UserID;
  

Cleanup

When you're finished experimenting, remove the test tables so they don't clutter the repository:

DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Users;

DROP TABLE is permanent and cannot be undone. The IF EXISTS clause prevents an error if the table doesn't exist.

Tips for Learning SQL

✔️

Start with simple queries.

Begin with SELECT * FROM table and add one clause at a time (WHERE, then ORDER BY, then GROUP BY). This makes errors easier to isolate.

✔️

Break complex queries into smaller steps.

If a query isn't returning what you expect, run pieces of it independently. For joins, run each side as a standalone SELECT first to confirm the row counts before combining them.

✔️

Always preview before you modify.

Before running UPDATE or DELETE, run the same WHERE clause as a SELECT to confirm exactly which rows will be affected.

✔️

Use the Arpia Codex Assistant.

The 🪄 AI Assistant button in the editor toolbar opens the Arpia Codex Assistant, which can generate SQL from a natural-language description or explain a query you've selected.

Engine-specific Documentation

For features beyond the standards covered in this guide, refer to each engine's official documentation:

📚

SingleStore (BigData)

SingleStoreDB documentation — covers distributed tables, columnstore, sharding, and SingleStore-specific functions.

📚

MySQL

MySQL Reference Manual — covers SQL syntax, functions, storage engines, and administration.

📚

MariaDB

MariaDB Knowledge Base — covers MariaDB-specific features (sequences, system-versioned tables, additional storage engines) along with MySQL-compatible syntax.