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.
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).
s)CREATE TABLE tripsFare (vendor varchar, fare float)
.s.e"SELECT * FROM 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)
);
.s.e"SELECT * FROM cars"
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)
s)SELECT * FROM tripsFare
You can also insert multiple rows at once:
s)INSERT INTO tripsFare(vendor,fare) VALUES ('DDS',301),('CMT',589)
s)SELECT * FROM tripsFare
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" character 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.
s)SELECT * FROM tripsFare
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.