# Tutorials/Tables I

Prerequisites: The Basics, Datatypes, Lists, Functions, Dictionaries.

Tables form the basis of a kdb+ database and are consequently one of the most important data structures in q. This tutorial deals with creating and manipulating tables, including inserts and upserts. Tables II is about queries and joins.

# Tables: keyed and unkeyed

We saw in Tutorials/Dictionaries how to create a table by flipping a dictionary. There is another (perhaps simpler) syntax for defining tables.

```q)family:([]name:`John`Mary`David; age:52 49 18; hair:`brown`black`blonde; eyes:`blue`brown`blue)
q)family
name    age hair   eyes
------------------------
John    52  brown  blue
Mary    49  black  brown
David   18  blonde blue
q)type family
98h
q)([]a:1 2 3;b:4 5 6 7)			/ columns must be the same length
'length
```

The table above was defined within parentheses, with columns separated by a semicolon. The square brackets at the beginning are empty because the table is unkeyed. Unkeyed tables have a type of 98h. So what is a keyed table? We can define some of the columns within the square brackets, which give the following result:

```q)keyedtab:([a:`f`y`i] b:3 4 5; c:6 7 8)
q)keyedtab
a| b c
-| ---
f| 3 6
y| 4 7
i| 5 8
q)type keyedtab
99h
```

See the vertical line separating the first column from the others, similar to how dictionaries are displayed? Notice too that the type of the keyed table is 99h – just like dictionaries!

A keyed table is a dictionary whose keys and values are both tables.

Keyed and unkeyed tables act differently in different situations, which we will see later, so it is important to be able to key and unkey tables. There are two syntaxes for this:

```q)2!family		/ keys on the first 2 columns
name    age| hair   eyes
-----------| ------------
John    52 | brown  blue
Mary    49 | black  brown
David   18 | blonde blue
q)3!family		/ keys on the first 3 columns
name    age hair  | eyes
------------------| -----
John    52  brown | blue
Mary    49  black | brown
David   18  blonde| blue
q)`age`eyes xkey family	/ specifies columns to key on
age eyes | name    hair
---------| --------------
52  blue | John    brown
49  brown| Mary    black
18  blue | David   blonde
```

Have a look at the reference pages for `!` and `xkey` for more information about these functions.

Creating an unkeyed table from a keyed one uses exactly the same syntaxes, you just 'key on nothing'.

```q)0!keyedtab
a b c
-----
f 3 6
y 4 7
i 5 8
q)() xkey keyedtab       / () is an empty list
a b c
-----
f 3 6
y 4 7
i 5 8
```

# Indexing tables

In the q/kdb+ world, 'indexing' usually refers to accessing elements of lists or other structures,not speeding up access, which is done using attributes.

Unkeyed tables are like lists: rows are numbered starting with 0.

```q)family 0 2
name  age hair   eyes
---------------------
John  52  brown  blue
David 18  blonde blue
q)family 1
name| `Mary
age | 49
hair| `black
eyes| `brown
```

Notice that a single row is returned as a dictionary, but multiple rows make a table. (A table is a list of dictionaries.)

Keyed tables are dictionaries, and so their rows are indexed by keys instead of numbers.

```q)keyedtab`y
b| 4
c| 7
```

We can also access the columns of an unkeyed table as if it were a dictionary:

```q)family`eyes
`blue`brown`blue
```

# Inserts and upserts

How do you add rows to a table? Using `insert` or `upsert`. The name of the table is passed into these functions as a symbol.

```q)`family insert (`Jessica;13;`black;`blue)
,3
q)family
name    age hair   eyes
------------------------
John    52  brown  blue
Mary    49  black  brown
David   18  blonde blue
Jessica 13  black  blue
```

The result from `insert` is a list of the row indices that have been added to the table:

```q)family[3]
name| `Jessica
age | 13
hair| `black
eyes| `blue
```

The result from `upsert`, however, is simply the name of the modified table:

```q)`family upsert (`Avery;30;`black;`green)
`family
q)family
name    age hair   eyes
------------------------
John    52  brown  blue
Mary    49  black  brown
David   18  blonde blue
Jessica 13  black  blue
Avery   30  black  green
```

For the unkeyed table `family`, `insert` and `upsert`, though returning different results, produced the same outcome – they both added a row to the family table.

Type out the following code and see what happens when you `insert` or `upsert` into a keyed table.

````keyedtab insert (`p;10;20)
`keyedtab insert (`i;11;12)
`keyedtab upsert (`i;11;12)
`keyedtab upsert (`j;13;20)
```

The first statement ``keyedtab insert (`p;10;20)` inserted a new record under the key ``p`, and returned its row: 3.

The second statement ``keyedtab insert (`i;11;12)` attempted to insert a new record with the key ``i`. But that key already existed. So `insert` failed, and returned its own name as an error message.

The third statement ``keyedtab upsert (`i;11;12)` found its key ``i` already in use and updated the corresponding record, returning the name of the modified table.

The fourth statement ``keyedtab upsert (`j;13;20)` inserted a record under the new key ``j`.

In a keyed table, `insert` creates new table records, while `upsert` inserts or creates them.

# Exercises

1. Make an unkeyed table with 3 columns: one for the days of the week (as symbols), one for the temperature (integers) and the last with string describing the weather e.g. "cloudy".

2. Key the your table on the day columnn.

3. Add a row to your (keyed) table with a fictional eight day of the week.

4. Replace the entry for Monday with a temperature of 1000 and the description "appallingly wet".