# Tables

A basic understanding of the internal structure of tables is needed to work with q. The structure is actually quite simple, but very different from conventional databases.

This section gives a quick overview, followed by an explanation of the sp.q script, and then a typical table for stock data. After completing this, you should read Q for Mortals 14. Introduction to Kdb+, which has a detailed comparison of q and conventional RDBMS.

Tables are created out of lists. A table with no key columns is essentially a list of column names associated with a list of corresponding column values, each of which is a list. A table with key columns is built internally from a pair of tables – the key columns associated with the non-key columns.

Tables are created in memory, and then written to disk if required. When written to disk, smaller tables can be stored in a single file, while larger tables are usually partitioned in some way. The partitioning can be seen when viewing the file directories, but the table is treated as a single object within a kdb+ process.

## Creating tables

There are two ways of creating a table. One way explicitly associates lists of column names and data; the other uses a q expression that specifies the column names and initial values. The second method also permits each column’s datatype to be given, and so is particularly useful when a table is created with no data.

• create table by association:


q)tab:flip itemssalesprices!(nutboltcamcog;6 8 0 3;10 20 15 20)
q)tab
items sales prices
------------------
nut   6     10
bolt  8     20
cam   0     15
cog   3     20


• create table by specifying column names and initial values:


q)tab2:([]items:nutboltcamcog;sales:6 8 0 3;prices:10 20 15 20)
q)tab~tab2              / tab and tab2 are identical
1b


The form for the second method, for a table with j primary keys and n columns in total, is:

t:([c1:v1;...;cj:vj]cj+1:vj+1;...;cn:vn)

Here table t is defined with column names ci, and corresponding values vi. The square brackets are for primary keys, and are required even if there are no primary keys.

## 4.3 Suppliers and parts

The script sp.q defines C.J. Date’s Suppliers and Parts database. You can view this script in an editor to see the definitions. Load the script.

q)\l sp.q


### Table s

Table s has a primary key column, also called s, given as a list of symbols which should be unique. In this example, the name s is used both for the table and the primary key column, but this is not required.

The remaining columns are of type symbol, integer, symbol.

s:([s:s1s2s3s4s5]
name:smithjonesblakeclarkadams;
status:20 10 30 20 30;
city:londonparisparislondonathens)


Display in q.

q)s
s | name  status city
--| -------------------
s1| smith 20     london
s2| jones 10     paris
s3| blake 30     paris
s4| clark 20     london


Note that the column types are set from the data given. If this were first created as an empty table, say table t, then the column types could be defined explicitly as follows:

q)t:([s:$()]name:$();status:"i"$();city:$())


Insert a row.

q)t insert (s1;smith;20;london)
,0
q)t
s | name status city
--| -------------------
s1| smith 20 london


### Table p

Table p is created much like table s. As before, the table name and primary key name are both the same:

p:([p:p1p2p3p4p5p6]
name:nutboltscrewscrewcamcog;
color:redgreenblueredbluered;
weight:12 17 17 14 12 19;
city:londonparisromelondonparislondon)


Display in q:

q)p
p | name  color weight city
--| -------------------------
p1| nut   red   12     london
p2| bolt  green 17     paris
p3| screw blue  17     rome
p4| screw red   14     london
p5| cam   blue  12     paris
p6| cog   red   19     london


### Table sp

Table sp is defined with no primary key. Columns s and p refer to tables s and p respectively as foreign keys. The syntax for specifying another table’s primary key as a foreign key is:

tablename$data  The definition of sp is: sp:([] s:s$s1s1s1s1s4s1s2s2s3s4s4s1;
p:p$p1p2p3p4p5p6p1p2p2p2p4p5; qty:300 200 400 200 100 100 300 400 200 200 300 400)  Display in q. q)sp s p qty --------- s1 p1 300 s1 p2 200 s1 p3 400 s1 p4 200 s4 p5 100 ...  ## Stock data The following is a typical layout populated with random data. Load the trades.q script. q)\l start/trades.q  A trade table might include: date, time, symbol, price, size, condition code. q)trades:([]date:date$();time:time$();sym:symbol$();
price:real$();size:int$(); cond:char$()) q)trades insert (2013.07.01;10:03:54.347;IBM;20.83e;40000;"N") q)trades insert (2013.07.01;10:04:05.827;MSFT;88.75e;2000;"B") q)trades date time sym price size cond --------------------------------------------- 2013.07.01 10:03:54.347 IBM 20.83 40000 N 2013.07.01 10:04:05.827 MSFT 88.75 2000 B  The ? operator will generate random data. q)syms:IBMMSFTUPSBACAAPL q)tpd:100 / trades per day q)day:5 / number of days q)cnt:count syms / number of syms q)len:tpd*cnt*day / total number of trades q)date:2013.07.01+len?day q)time:"t"$raze (cnt*day)#enlist 09:30:00+15*til tpd
q)time+:len?1000
q)sym:len?syms
q)price:len?100e
q)size:100*len?1000
q)cond:len?" ABCDENZ"

q)trades insert (date;time;sym;price;size;cond)
q)trades:datetime xasc trades  / sort on time within date
`