Introduction to DXSQL(cube)

[ONiX]

What is SQL?

SQL is a standard language for accessing and manipulating databases.

SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

[ONiX]

What Can SQL do?

SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views

[ONiX]

SQL is a Standard - BUT....

Although SQL is an ANSI/ISO standard, there are different versions of the SQL language. From SQL:92 ... https://en.wikipedia.org/wiki/SQL:2023 "SQL:2023" - Wikipedia - there current is not a SQL:2026 version.

However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

[ONiX]

dxsql-lessons/
00_README.txt how to run the lessons, sample data model
01_look_around.sql SHOW SCHEMAS, SHOW DATABASES
02_create_schema.sql CREATE SCHEMA, DROP SCHEMA
03_use_schema.sql USE, current schema
04_create_database.sql CREATE DATABASE, DROP DATABASE
05_select_database.sql selecting a database (not SQL), checking where you are
06_create_tables_basic.sql CREATE TABLE, column types, constraints
07_create_tables_related.sql primary keys, foreign keys, CHECK
08_inspect_and_alter.sql list tables and columns, ALTER TABLE, DROP TABLE
09_cleanup.sql remove everything the lessons created

[ONiX]

00_README.txt

DXSQL(cube) SQL LESSONS
=======================

Who these are for: someone who has never written SQL.

How to run?
----------
1. Using our 32bit Windows Client - Connect with a user who can create schemas (an administrator), or a user
who has been granted CREATE on a schema (default mode)
2. Run each file in order, 01 to 09.
3. Run one statement at a time, or run the whole file with the script runner
(it sends the file as one EXEC_SCRIPT request).
4. Lessons 06 to 09 need the database "training.shop" selected first.
Lesson 05 shows how to select it.
5. A statement that fails shows an error. Read it, fix the statement, and run
it again. Errors are part of learning.

Naming rules on this server
---------------------------
- Schema and database names: 1 to 64 characters, a-z, 0-9, _ and -.
Case does not matter; the server stores them in lower case.
- Schema names cannot be reserved words such as con, prn, aux, nul, system.
- A database is written as schema.database, for example training.shop.
- Table names in SQLite are case-insensitive. The lessons use CamelCase for readability.

The lessons use this sample data model (similar to the W3Schools tutorial):

Suppliers 1 ---- * Products 1 ---- * Orders * ---- 1 Customers

Customers (CustomerID, CustomerName, ContactName, City, Country, ...)
Suppliers (SupplierID, SupplierName, ContactName, City, Country)
Products (ProductID, ProductName, SupplierID -> Suppliers, Price, Unit)
Orders (OrderID, CustomerID -> Customers, ProductID -> Products, Quantity, OrderDate)

Words to know
-------------
- Schema: a folder/silo that holds databases. Think of it as a group.
- Database: one file that holds tables. Think of it as one project.
- Table: rows and columns of data. Each row is one record.
- Column: one named piece of data in every row (for example, City).
- Key: a column that identifies a row (primary key) or links to
another table's row (foreign key).
- Constraint: a rule the database enforces (NOT NULL, UNIQUE, CHECK, ...).

Exercises
---------
Each lesson file ends with "Try it" comments. Work them out before you look
at the answers in your own notes. Every file should run without errors
when you run it as written, so change a copy when you experiment.

[ONiX]

-- 01_look_around.sql
-- Lesson 1: Look around the server before creating anything.
--
-- Run this with no database selected. These are server commands, not
-- table commands: they work on the server itself.

-- List every schema (a group of databases) that you can see.
SHOW SCHEMAS;

-- List every database you can see, with its schema, size and your access.
SHOW DATABASES;

-- List the databases in one schema only.
SHOW DATABASES IN default;

-- Try it:
-- 1. Note how many schemas the server lists. The schema "default" always
-- exists.
-- 2. Run SHOW DATABASES IN default; then run it with another schema name
-- that does not exist. Read the error message.

(C) 2026 by Brain Patchwork DX, LLC.

Screenshot 2026-10-15 234513.png (30.1 KiB) Viewed 46257 times

[ONiX]

-- 02_create_schema.sql
-- Lesson 2: Create a schema.
--
-- A schema is a group of databases. It is the first level of organisation.
-- Creating a schema makes a folder on the server. Schemas are created by
-- an administrator (or by a user with the right to create in the server).

-- Create the schema "training". It is an error if it already exists.
-- CREATE SCHEMA training;

-- Check that it exists.
SHOW SCHEMAS;

-- Creating it again fails, because it already exists.
-- Run this on purpose and read the error:
-- CREATE SCHEMA training;

-- The safe form does nothing if the schema already exists.
CREATE SCHEMA IF NOT EXISTS training;

-- Try it:
-- 1. Create a schema called lessons_scratch and check it with SHOW SCHEMAS.
-- 2. Drop it again with the command shown below, then check again.
-- (Dropping a schema only works when it holds no databases.)

-- DROP SCHEMA lessons_scratch;

(C) 2026 by Brain Patchwork DX, LLC.

Screenshot 2026-10-16 001951.png (34.06 KiB) Viewed 46107 times

[ONiX]

-- 03_use_schema.sql
-- Lesson 3: Choose the current schema with USE.
--
-- USE sets the current schema for your session. After USE, a database name
-- without a schema part means "inside the current schema".
-- For example, after USE training, the name shop means training.shop.
-- USE does not select a database. That is lesson 5.

USE training;

-- The same thing, with the optional word SCHEMA:
USE SCHEMA training;

-- Now a name without a schema part is looked up in training.
-- This creates training.shop (you will create it in lesson 4):
-- CREATE DATABASE shop;
-- Read lesson 4 for the full form and the reason to use it.

-- See which databases are in the training schema.
SHOW DATABASES IN training;

-- Go back to the default schema.
USE default;

-- Try it:
-- 1. Run USE training; then SHOW DATABASES; . Then run USE default;
-- then SHOW DATABASES; and compare the two results.
-- 2. Try USE a_schema_that_does_not_exist; and read the error.

(C) 2026 by Brain Patchwork DX, LLC.

Screenshot 2026-10-16 104505.png (28.53 KiB) Viewed 42843 times

[ONiX]

-- 04_create_database.sql
-- Lesson 4: Create a database inside a schema.
--
-- A database is one file on the server that holds tables. You create the
-- tables in lesson 6, once you have selected this database in lesson 5.

-- Make sure the schema exists (it is safe to run more than once).
CREATE SCHEMA IF NOT EXISTS training;

-- Create the database training.shop.
-- The full name is schema.database.
CREATE DATABASE IF NOT EXISTS training.shop;

-- Check it.
SHOW DATABASES IN training;

-- The name can also be written without the schema part when the current
-- schema is training (see lesson 3):
-- USE training;
-- CREATE DATABASE IF NOT EXISTS shop;

-- Rules to notice:
-- - Names are lower case on the server (SHOP becomes shop).
-- - Only a-z, 0-9, _ and - are allowed.

-- Try it:
-- 1. Create training.school. Then run SHOW DATABASES IN training; and
-- check that both shop and school are listed.
-- 2. Run CREATE DATABASE training.shop; (without IF NOT EXISTS) and
-- read the error.

-- DROP DATABASE is shown in lesson 9. Do not drop training.shop yet.

(C) 2026 by Brain Patchwork DX, LLC.

Screenshot 2026-10-16 104752.png (33.28 KiB) Viewed 42835 times

[ONiX]

-- 05_select_database.sql
-- Lesson 5: Select a database.
--
-- IMPORTANT: on this server, you do not select a database with SQL.
-- The connection chooses it. There is no "USE database" statement.
--
-- In the application, set the Database property of the connection:
--
-- DXSQLConnection1.Database := 'training.shop';
--
-- Then open the query again. If the connection is already open, the
-- property takes effect immediately. Datasets that are already open keep
-- their old rows, so close and reopen them.
--
-- Once a database is selected, all table statements (CREATE TABLE, SELECT,
-- INSERT, ...) run against it. Server commands such as SHOW DATABASES work
-- in any case.

-- Check where you are. This needs a selected database.
SHOW ATTACHED;
-- Expected: one row with alias "main" and database "training.shop".

-- This lists the tables in the selected database. It is empty at this
-- point, because we have not created any tables yet.
SELECT name
FROM sqlite_master
WHERE type = 'table'
AND name NOT LIKE 'sqlite_%'
ORDER BY name;

-- Advanced (optional): a second database can be attached to the same
-- session and read with its own name. It is not needed for these lessons.
-- ATTACH DATABASE training.school AS school;
-- SELECT name FROM school.sqlite_master WHERE type = 'table';
-- DETACH DATABASE school;

-- Try it:
-- 1. Set the database in your application to a name that does not
-- exist, for example training.nothere. Read the error when you open
-- the query.
-- 2. Set it back to training.shop.

(C) 2026 by Brain Patchwork DX, LLC.

Screenshot 2026-10-16 105038.png (29.48 KiB) Viewed 42812 times