SQL in KDB-X Quickstart
This quickstart walks you through creating, inserting into, querying, and dropping a table in KDB-X using SQL. You will use basic Data Definition Language (DDL) and Data Manipulation Language (DML) commands.
Start a SQL-enabled session
Before running SQL queries, initialize SQL support in the current process using .s.init[].
q).s.init[]
1. Create a table
Use CREATE TABLE to define a new table in memory. The example below creates an empty table with two columns: vendor (string) and fare (float).
Running SQL in q
In KDB-X, SQL statements are executed from the q prompt using the s) SQL execution prompt.
s)CREATE TABLE tripsFare (vendor varchar, fare float)
`tripsFare
You can also create a more complex table with multiple data types and constraints:
s)CREATE TABLE cars (
id int,
Name varchar(250),
"Miles_per_Gallon" smallint,
"Cylinders" smallint,
"Displacement" smallint,
"Horsepower" smallint,
"Weight_in_lbs" smallint NOT NULL,
"Acceleration" smallint,
"Year" date NOT NULL,
"Origin" varchar(60)
);
Note
Use char(1) to define a column of q type char. Use char(n>1) or varchar(n) for longer strings.
See reference for the full list of supported types.
2. Insert data
Use INSERT to add rows to an existing table. The following example inserts a single row into tripsFare.
s)INSERT INTO tripsFare(vendor,fare) VALUES ('CMT',100)
You can also insert multiple rows at once:
s)INSERT INTO tripsFare(vendor,fare) VALUES ('DDS',301),('CMT',589)
The next example inserts two rows into the cars table:
s)CREATE TABLE cars (
id int, Name varchar(250), "Miles_per_Gallon" smallint,
"Cylinders" smallint, "Displacement" smallint, "Horsepower" smallint,
"Weight_in_lbs" smallint NOT NULL, "Acceleration" smallint,
"Year" date NOT NULL, "Origin" varchar(60)
);
s)INSERT INTO cars VALUES
(1, 'chevrolet chevelle malibu', 18, 8, 307, 130, 3504, 12, '1970-01-01', 'USA');
s)INSERT INTO cars VALUES
(2, 'volkswagen 1131 deluxe sedan', 26, 4, 97, 46, 1835, 21, '1970-01-01', 'Europe');
3. Query data
Use SELECT to retrieve rows. You can return all data or filter it with conditions.
Return all rows from the table:
s)SELECT * FROM tripsFare
GROUP BY. The following example calculates the total fare per vendor:
s)SELECT SUM(fare) FROM tripsFare GROUP BY vendor
fare
----
689
301
COUNT:
s)SELECT COUNT(*) FROM tripsFare
count
-----
3
tripsFare with cars using a common vendor column.
s)SELECT vendor, fare, Name
FROM tripsFare
JOIN cars ON tripsFare.vendor = cars.Origin
About joins
This example uses a simple shared column for clarity. In practice, joins are typically performed on stable identifiers such as IDs or symbols.
Querying on-disk data
SQL in KDB-X queries data stored both in memory and on disk, including memory-mapped historical databases (HDBs).
4. Drop a table
Use DROP TABLE to delete a table from memory. The example below drops the tripsFare table if it exists.
s)DROP TABLE IF EXISTS tripsFare
Congratulations! You have now created, populated, queried, and dropped tables in KDB-X using SQL.