Rosetta Code

Table creation: Difference between revisions

Page Discussion

Table creation (view source)

Revision as of 15:15, 9 March 2024

19,166 bytes added ,  2 months ago
Added BASIC256
(→‎{{header|SQL PL}}: Corrected for Db2 with output)
(Added BASIC256)
 
(18 intermediate revisions by 10 users not shown)
Line 8:
* [[Table Creation - Address]]
<br><br>
 
=={{header|Arturo}}==
 
<syntaxhighlight lang="rebol">; Helper functions
 
createTable: function [][
query db {!sql DROP TABLE IF EXISTS users}
query db {!sql
CREATE TABLE users (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
email TEXT NOT NULL,
age INTEGER
)
}
]
 
addUser: function [name, email, age][
query.id db ~{!sql
INSERT INTO users (username, email, age)
VALUES ('|name|','|email|','|age|')
}
]
 
findUser: function [name][
query db ~{!sql
SELECT *
FROM users
WHERE username='|name|'
}
]
 
db: open.sqlite "users.db"
 
createTable
 
print ["added user with id:" addUser "JohnDoe" "jodoe@gmail.com" 35]
print ["added user with id:" addUser "JaneDoe" "jadoe@gmail.com" 14]
 
print ["getting user with name: JohnDoe =>" findUser "JohnDoe"]
 
close db</syntaxhighlight>
 
{{out}}
 
<pre>added user with id: 1
added user with id: 2
getting user with name: JohnDoe => [[1 JohnDoe jodoe@gmail.com 35]] </pre>
 
=={{header|AWK}}==
Line 14 ⟶ 62:
 
AWK is just a glue language. Simply pipe the creation command into SQLite and capture the output.
<langsyntaxhighlight lang="awk">#!/bin/sh -f
awk '
BEGIN {
Line 21 ⟶ 69:
exit;
}
'</langsyntaxhighlight>
 
=={{header|BASIC256}}==
<syntaxhighlight lang="vbnet"># create a new database file or open it
dbopen "mydbtest.sqlite3"
 
# delete the existing table in Personas - If it is a new database, the error is captured
onerror errortrap
dbexecute "drop table Personas;"
offerror
 
# create the table and enter data into it
dbexecute "create table Personas (id integer, nombre text, apellido text, edad integer, direccion string(50), salario decimal);"
dbexecute "insert into Personas values (1, 'Juan', 'Hdez', 52, '123 Calle Principal', 50000.00);"
 
# open a recordset and loop through the rows of data
print "Contents of the Personas table:"
 
dbopenset "select * from Personas order by nombre;"
while dbrow()
print dbint(0) + " " + dbstring(1) + " " + dbstring(2) + " " + dbint(3) + " " + dbstring(4) + " " + dbfloat(5)
end while
dbcloseset
 
# close all
dbclose
end
 
errortrap:
# accept the error - show nothing - return to the next statement
return</syntaxhighlight>
{{out}}
<pre>Contents of the Personas table:
1 Juan Hdez 52 123 Calle Principal 50000.0</pre>
 
=={{header|C}}==
Line 27 ⟶ 108:
 
{{libheader|SQLite}}
<syntaxhighlight lang="c">
<lang C>
#include <sqlite3.h>
#include <stdlib.h>
Line 55 ⟶ 136:
return 0;
}
</syntaxhighlight>
</lang>
 
=={{header|FunL}}==
<syntaxhighlight lang="funl">import db.*
import util.*
 
Class.forName( 'org.h2.Driver' )
conn = DriverManager.getConnection( "jdbc:h2:~/test", "sa", "" )
statement = conn.createStatement()
statement.execute( '''
CREATE TABLE Persons
(
PersonID int,
FirstName varchar(255),
LastName varchar(255),
Address varchar(255),
City varchar(255),
Province char(2)
)''' )
statement.execute( '''
INSERT INTO Persons VALUES
(1, 'Sylvia', 'Henry', '5896 Cotton Prairie Wharf', 'Parrsboro', 'SK'),
(2, 'Kelly', 'Saunders', '3608 Indian Island Promenade', 'Goober Hill', 'SK'),
(3, 'Vernon', 'Douglas', '394 Dusty Impasse', 'Muleshoe', 'NS'),
(4, 'Jim', 'Fleming', '2523 Quaking Fawn Trace', 'Halbrite', 'ON'),
(5, 'Roderick', 'Owens', '7596 Umber View', 'Frognot', 'SK')
''' )
statement.execute( "SELECT * FROM Persons ORDER BY LastName" )
print( TextTable.apply(statement.getResultSet()) )
conn.close()</syntaxhighlight>
{{out}}
 
<pre>
+----------+-----------+----------+------------------------------+-------------+----------+
| PERSONID | FIRSTNAME | LASTNAME | ADDRESS | CITY | PROVINCE |
+----------+-----------+----------+------------------------------+-------------+----------+
| 3 | Vernon | Douglas | 394 Dusty Impasse | Muleshoe | NS |
| 4 | Jim | Fleming | 2523 Quaking Fawn Trace | Halbrite | ON |
| 1 | Sylvia | Henry | 5896 Cotton Prairie Wharf | Parrsboro | SK |
| 5 | Roderick | Owens | 7596 Umber View | Frognot | SK |
| 2 | Kelly | Saunders | 3608 Indian Island Promenade | Goober Hill | SK |
+----------+-----------+----------+------------------------------+-------------+----------+
</pre>
 
=={{header|FreeBASIC}}==
{{libheader|SQLite}}
<syntaxhighlight lang="vbnet">#include once "sqlite3.bi"
 
Const NULL As Any Ptr = 0
 
Dim As sqlite3 Ptr db
Dim As zstring Ptr errMsg
Dim As sqlite3_stmt Ptr stmt
 
If sqlite3_open(":memory:", @db) <> SQLITE_OK Then
Print "Could not open database: "; sqlite3_errmsg(db)
sqlite3_close(db)
Sleep
End 1
End If
 
' Create the table
Dim As String sql = "CREATE TABLE Persons(" _
& "ID INT PRIMARY KEY NOT NULL," _
& "NAME TEXT NOT NULL," _
& "SURNAME TEXT NOT NULL," _
& "AGE INT NOT NULL," _
& "ADDRESS CHAR(50)," _
& "SALARY REAL );"
 
If sqlite3_exec(db, sql, NULL, NULL, @errMsg) <> SQLITE_OK Then
Print "Error creating table: "; *errMsg
sqlite3_free(errMsg)
Else
Print "Table created successfully"
End If
 
' Insert some data
sql = "INSERT INTO Persons (ID, NAME, SURNAME, AGE, ADDRESS, SALARY) " _
& "VALUES (1, 'John', 'Doe', 30, '123 Main St', 50000.00);"
 
If sqlite3_exec(db, sql, NULL, NULL, @errMsg) <> SQLITE_OK Then
Print "Error inserting data: "; *errMsg
sqlite3_free(errMsg)
End If
 
' Display the contents of the table
sql = "SELECT * FROM Persons;"
 
If sqlite3_prepare_v2(db, sql, -1, @stmt, NULL) <> SQLITE_OK Then
Print "Error preparing statement: "; sqlite3_errmsg(db)
Else
While sqlite3_step(stmt) = SQLITE_ROW
Print "ID: "; sqlite3_column_int(stmt, 0)
Print "Name: "; *cast(zstring ptr, sqlite3_column_text(stmt, 1))
Print "Surname: "; *cast(zstring ptr, sqlite3_column_text(stmt, 2))
Print "Age: "; sqlite3_column_int(stmt, 3)
Print "Address: "; *cast(zstring ptr, sqlite3_column_text(stmt, 4))
Print "Salary: "; sqlite3_column_double(stmt, 5)
Print
Wend
End If
 
sqlite3_finalize(stmt)
sqlite3_close(db)
 
Sleep</syntaxhighlight>
{{out}}
<pre>Table created successfully
ID: 1
Name: Juan
Surname: Hdez
Age: 52
Address: 123 Main St
Salary: 50000</pre>
 
=={{header|Go}}==
{{libheader|Bolt}}
<br>
This uses a key/value store rather than a relational database to create the table.
<syntaxhighlight lang="go">package main
 
import (
"encoding/binary"
"encoding/json"
"fmt"
"github.com/boltdb/bolt"
"log"
)
 
type StockTrans struct {
Id int // this will be auto-incremented by db
Date string
Trans string
Symbol string
Quantity int
Price float32
Settled bool
}
 
// save stock transaction to bucket in db
func (st *StockTrans) save(db *bolt.DB, bucket string) error {
err := db.Update(func(tx *bolt.Tx) error {
b := tx.Bucket([]byte(bucket))
id, _ := b.NextSequence()
st.Id = int(id)
encoded, err := json.Marshal(st)
if err != nil {
return err
}
return b.Put(itob(st.Id), encoded)
})
return err
}
 
// itob returns an 8-byte big endian representation of i.
func itob(i int) []byte {
b := make([]byte, 8)
binary.BigEndian.PutUint64(b, uint64(i))
return b
}
 
func check(err error) {
if err != nil {
log.Fatal(err)
}
}
 
func main() {
// create database
db, err := bolt.Open("store.db", 0600, nil)
check(err)
defer db.Close()
 
// create bucket
err = db.Update(func(tx *bolt.Tx) error {
_, err := tx.CreateBucketIfNotExists([]byte("stocks"))
return err
})
check(err)
 
transactions := []*StockTrans{
{0, "2006-01-05", "BUY", "RHAT", 100, 35.14, true},
{0, "2006-03-28", "BUY", "IBM", 1000, 45, true},
{0, "2006-04-06", "SELL", "IBM", 500, 53, true},
{0, "2006-04-05", "BUY", "MSOFT", 1000, 72, false},
}
 
// save transactions to bucket
for _, trans := range transactions {
err := trans.save(db, "stocks")
check(err)
}
 
// print out contents of bucket
fmt.Println("Id Date Trans Sym Qty Price Settled")
fmt.Println("------------------------------------------------")
db.View(func(tx *bolt.Tx) error {
b := tx.Bucket([]byte("stocks"))
b.ForEach(func(k, v []byte) error {
st := new(StockTrans)
err := json.Unmarshal(v, st)
check(err)
fmt.Printf("%d %s %-4s %-5s %4d %2.2f %t\n",
st.Id, st.Date, st.Trans, st.Symbol, st.Quantity, st.Price, st.Settled)
return nil
})
return nil
})
}</syntaxhighlight>
 
{{out}}
<pre>
Id Date Trans Sym Qty Price Settled
------------------------------------------------
1 2006-01-05 BUY RHAT 100 35.14 true
2 2006-03-28 BUY IBM 1000 45.00 true
3 2006-04-06 SELL IBM 500 53.00 true
4 2006-04-05 BUY MSOFT 1000 72.00 false
</pre>
 
=={{header|J}}==
Line 61 ⟶ 362:
If we define a <code>table</code> as a named collection of columns, and we define a <code>type</code> as a mechanism for the representation of some kind of data, then:
 
<langsyntaxhighlight lang="j">stocks=: |: ,: ;:'date trans symbol qty price'
insertStock=: 3 :'0#stocks=: stocks,.y'
insertStock@".;._2]0 :0
Line 68 ⟶ 369:
'2006-04-05'; 'BUY'; 'MSOFT'; 1000; 72.00
'2006-04-06'; 'SELL'; 'IBM'; 500; 53.00
)</langsyntaxhighlight>
 
declares a table and some data within that table.
Line 74 ⟶ 375:
And, here's an example of sorting:
 
<langsyntaxhighlight lang="j">cols=: [:; {."1@[ <@i.`(<@i.@#@[)@.(=&(<,'*')@]"1 0) cutopen@]
sortBy=: [ /:"1 2 (<__)|:@,. [ }.@{~ cols
from=: cols~ {"0 _ ]
Line 90 ⟶ 391:
├──────────┼─────┼──────┼────┼─────┤
│2006-04-05│BUY │MSOFT │1000│72 │
└──────────┴─────┴──────┴────┴─────┘</langsyntaxhighlight>
 
Note that this particular example is both overly general in some senses (for example, named column handling has features not demonstrated here) and overly specific in others (for example, I did not implement sort in descending order).
Line 99 ⟶ 400:
{{works with|Julia|0.6}}
 
<langsyntaxhighlight lang="julia">using SQLite
 
conn = SQLite.DB() # in-memory
Line 121 ⟶ 422:
 
df = SQLite.query(conn, "select * from stocks order by price")
println(df)</langsyntaxhighlight>
 
{{out}}
Line 131 ⟶ 432:
│ 3 │ "2006-04-06" │ "SELL" │ "IBM" │ 500.0 │ 53.0 │
│ 4 │ "2006-04-05" │ "BUY" │ "MSOFT" │ 1000.0 │ 72.0 │</pre>
 
=={{header|FunL}}==
<lang funl>import db.*
import util.*
 
Class.forName( 'org.h2.Driver' )
conn = DriverManager.getConnection( "jdbc:h2:~/test", "sa", "" )
statement = conn.createStatement()
statement.execute( '''
CREATE TABLE Persons
(
PersonID int,
FirstName varchar(255),
LastName varchar(255),
Address varchar(255),
City varchar(255),
Province char(2)
)''' )
statement.execute( '''
INSERT INTO Persons VALUES
(1, 'Sylvia', 'Henry', '5896 Cotton Prairie Wharf', 'Parrsboro', 'SK'),
(2, 'Kelly', 'Saunders', '3608 Indian Island Promenade', 'Goober Hill', 'SK'),
(3, 'Vernon', 'Douglas', '394 Dusty Impasse', 'Muleshoe', 'NS'),
(4, 'Jim', 'Fleming', '2523 Quaking Fawn Trace', 'Halbrite', 'ON'),
(5, 'Roderick', 'Owens', '7596 Umber View', 'Frognot', 'SK')
''' )
statement.execute( "SELECT * FROM Persons ORDER BY LastName" )
print( TextTable.apply(statement.getResultSet()) )
conn.close()</lang>
{{out}}
 
<pre>
+----------+-----------+----------+------------------------------+-------------+----------+
| PERSONID | FIRSTNAME | LASTNAME | ADDRESS | CITY | PROVINCE |
+----------+-----------+----------+------------------------------+-------------+----------+
| 3 | Vernon | Douglas | 394 Dusty Impasse | Muleshoe | NS |
| 4 | Jim | Fleming | 2523 Quaking Fawn Trace | Halbrite | ON |
| 1 | Sylvia | Henry | 5896 Cotton Prairie Wharf | Parrsboro | SK |
| 5 | Roderick | Owens | 7596 Umber View | Frognot | SK |
| 2 | Kelly | Saunders | 3608 Indian Island Promenade | Goober Hill | SK |
+----------+-----------+----------+------------------------------+-------------+----------+
</pre>
 
=={{header|Lua}}==
<langsyntaxhighlight lang="lua">Columns = {};
Columns.ID = {};
Columns.FName = {};
Line 231 ⟶ 489:
--[[ ]]--
 
listTables();</langsyntaxhighlight>
 
{{out}}
Line 244 ⟶ 502:
-----------------------------------------------------
</pre>
 
=={{header|M2000 Interpreter}}==
M2000 Environment use ADO to connect to databases. Default type is Mdb (Acess 2007).
Line 249 ⟶ 508:
We can use text from UTF16LE set. Here we have Greek letters in Memo.
 
<syntaxhighlight lang="m2000 interpreter">
<lang M2000 Interpreter>
MODULE SIMPLEBASE {
BASE "ALFA" ' ERASED IF FOUND THE NAME OF "ALFA.MDB"
Line 265 ⟶ 524:
}
SIMPLEBASE
</syntaxhighlight>
</lang>
 
=={{header|Mathematica}}==
=={{header|Mathematica}}/{{header|Wolfram Language}}==
<lang Mathematica>Needs["DatabaseLink`"];conn = OpenSQLConnection[JDBC["mysql",
<syntaxhighlight lang="mathematica">Needs["DatabaseLink`"];conn = OpenSQLConnection[JDBC["mysql",
"databases:1234/conn_test"], "Username" -> "test"]
SQLCreateTable[conn, SQLTable["TEST"],If[Length[#] == 0,SQLColumn[StringJoin[#,"COL"],"DataTypeName" -> #],SQLColumn[StringJoin[#[[1]], "COL"], "DataTypeName" -> #[[1]],"DataLength" -> #[[2]]]] & /@ {"TINYINT", "SMALLINT", "INTEGER","BIGINT", "NUMERIC", "DECIMAL", "FLOAT", "REAL", "DOUBLE", "BIT","LONGVARBINARY", "VARBINARY", "BINARY","LONGVARCHAR",{"VARCHAR", 5},{"CHAR", 3},"DATE","TIME","TIMESTAMP","OBJECT"}]</langsyntaxhighlight>
 
=={{header|Nim}}==
{{trans|Python}}
Nim standard library provides two modules to work with SQL databases. We use here the high level one for SQLite.
 
<syntaxhighlight lang="nim">import db_sqlite
 
let dbc = open(":memory:", "", "", "")
dbc.exec(sql"create table stocks(date text, trans text, symbol text, qty real, price real)")
 
# Insert a row of data.
dbc.exec(sql"insert into stocks values ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)")
 
for v in [("2006-03-28", "BUY", "IBM", 1000, 45.00),
("2006-04-05", "BUY", "MSOFT", 1000, 72.00),
("2006-04-06", "SELL", "IBM", 500, 53.00)]:
dbc.exec(sql"insert into stocks values (?, ?, ?, ?, ?)", v[0], v[1], v[2], v[3], v[4])
 
# Data retrieval.
for row in dbc.fastRows(sql"select * from stocks order by price"):
echo row</syntaxhighlight>
 
{{out}}
<pre>@["2006-01-05", "BUY", "RHAT", "100.0", "35.14"]
@["2006-03-28", "BUY", "IBM", "1000.0", "45.0"]
@["2006-04-06", "SELL", "IBM", "500.0", "53.0"]
@["2006-04-05", "BUY", "MSOFT", "1000.0", "72.0"]</pre>
 
=={{header|Oracle}}==
Great SCOTT! from utlsampl.sql
<langsyntaxhighlight lang="sql">
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
Line 283 ⟶ 570:
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
</syntaxhighlight>
</lang>
 
=={{header|Oz}}==
Line 291 ⟶ 578:
{{libheader|Ozsqlite}}
 
<langsyntaxhighlight lang="oz">declare
[Sqlite] = {Module.link ['x-ozlib:/sqlite/Sqlite.ozf']}
 
Line 328 ⟶ 615:
{Sqlite.close DB}
end
</syntaxhighlight>
</lang>
 
=={{header|PARI/GP}}==
The most natural way to store tabular data in GP is in a matrix:
<langsyntaxhighlight lang="parigp">m=matrix(10,3);
m[1,] = ["Barack", "Obama", 20500];
\\ ...</langsyntaxhighlight>
 
=={{header|Perl 6}}==
{{trans|Julia}}
In Perl 6, there is no 'database' type built in, so it is somewhat ambiguous when specifying 'create a database table'. Perl 6 offers bindings to most common databases through its DBIish module but mostly abstracts away the differences between the underlying databases, which hides many of the finer distinctions of what may be stored where. The actual data types and options available are properties of the database used.
<syntaxhighlight lang="perl"># 20211218 Perl programming solution
 
use strict;
If on the other hand, we are meant to show built in collective types that may be used to hold tabular data, this may be of some use.
use warnings;
 
use DBI;
In general, a container type can hold objects of any data type, even instances of their own type; allowing 'multi-dimensional' (tabular) containers.
my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:");
 
$dbh->do("CREATE TABLE stocks (
Perl 6 offers two broad categories of collective container types; those that do the Positional role and those that do Associative. Positional objects are collective objects that access the individual storage slots using an integer index. Associative objects use some sort of other pointer (typically string) to access their storage slots.
date TEXT, trans TEXT, symbol TEXT, qty REAL, price REAL
)");
 
my $sth = $dbh->prepare( "INSERT INTO stocks VALUES (?,?,?,?,?)" );
The various Associative types mostly differ in their value handling. Hash, Map and QuantHash may have any type of object as their value. All the others have some specific, usually numeric, type as their value.
 
my @DATA = ( '"2006-01-05","BUY", "RHAT", 100, 35.14',
<pre>
'"2006-03-28","BUY", "IBM", 1000, 45.00',
Positional - Object that supports looking up values by integer index
'"2006-04-05","BUY","MSOFT", 1000, 72.00',
Array Sequence of itemized objects
'"2006-04-06","SELL", "IBM", 500, 53.00' );
List Immutable sequence of objects
 
for ( @DATA ) { $sth->execute( split /,/ ) or die }
Associative - Object that supports looking up values by key (typically string)
 
Bag Immutable collection of distinct objects with integer weights
$sth = $dbh->prepare("SELECT * FROM stocks ORDER BY price") or die;
BagHash Mutable collection of distinct objects with integer weights
 
Hash Mapping from strings to itemized values
$sth->execute();
Map Immutable mapping from strings to values
 
Mix Immutable collection of distinct objects with Real weights
my $format = "%-15s %-15s %-15s %-15s %-15s\n";
MixHash Mutable collection of distinct objects with Real weights
 
QuantHash Collection of objects represented as hash keys
printf $format, $sth->{NAME}->@* ;
Set Immutable collection of distinct objects, no value except 'present'
 
SetHash Mutable collection of distinct objects, no value except 'present'
print '=' x 75 , "\n";
 
while ( my @row = $sth->fetchrow_array ) { printf $format, @row }</syntaxhighlight>
{{out}}
<pre>
date trans symbol qty price
===========================================================================
"2006-01-05" "BUY" "RHAT" 100 35.14
"2006-03-28" "BUY" "IBM" 1000 45
"2006-04-06" "SELL" "IBM" 500 53
"2006-04-05" "BUY" "MSOFT" 1000 72
</pre>
 
If you want a persistent instance of any of these types, you need to declare the name with some scope constraint, but the are no prerequisites to creating instances. Simply assigning values to them will call them into existence.
 
=={{header|Phix}}==
{{libheader|SQLite}}
<!--<syntaxhighlight lang="phix">-->
<lang Phix>include pSQLite.e
<span style="color: #008080;">include</span> <span style="color: #000000;">pSQLite</span><span style="color: #0000FF;">.</span><span style="color: #000000;">e</span>
constant sqlcode = """
<span style="color: #008080;">constant</span> <span style="color: #000000;">sqlcode</span> <span style="color: #0000FF;">=</span> <span style="color: #008000;">"""
CREATE TABLE IF NOT EXISTS employee (
CREATE TABLE IF NOT EXISTS employee (
empID INTEGER PRIMARY KEY AUTOINCREMENT,
empID INTEGER PRIMARY KEY AUTOINCREMENT,
firstName TEXT NOT NULL,
lastName firstName TEXT NOT NULL,
age lastName INTEGERTEXT NOT NULL,
dob age DATE INTEGER NOT NULL)""",
dob DATE NOT NULL)"""</span>
sqlite3 db = sqlite3_open("employees.sqlite")
<span style="color: #000000;">sqlite3</span> <span style="color: #000000;">db</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_open</span><span style="color: #0000FF;">(</span><span style="color: #008000;">"employees.sqlite"</span><span style="color: #0000FF;">)</span>
integer res = sqlite3_exec(db,sqlcode)
<span style="color: #004080;">integer</span> <span style="color: #000000;">res</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_exec</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span><span style="color: #000000;">sqlcode</span><span style="color: #0000FF;">)</span>
if res=SQLITE_OK then
<span style="color: #008080;">if</span> <span style="color: #000000;">res</span><span style="color: #0000FF;">=</span><span style="color: #000000;">SQLITE_OK</span> <span style="color: #008080;">then</span>
sqlite3_close(db)
<span style="color: #000000;">sqlite3_close</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">)</span>
else
<span style="color: #008080;">else</span>
printf(1,"sqlite3_exec error: %d [%s]\n",{res,sqlite_last_exec_err})
<span style="color: #7060A8;">printf</span><span style="color: #0000FF;">(</span><span style="color: #000000;">1</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"sqlite3_exec error: %d [%s]\n"</span><span style="color: #0000FF;">,{</span><span style="color: #000000;">res</span><span style="color: #0000FF;">,</span><span style="color: #000000;">sqlite_last_exec_err</span><span style="color: #0000FF;">})</span>
end if</lang>
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
 
<!--</syntaxhighlight>-->
=={{header|PL/I}}==
<lang PL/I>declare 1 table (100),
2 name character (20) varying,
2 address,
3 number fixed decimal,
3 street character (30) varying,
3 suburb character (30) varying,
3 zip picture '9999',
2 transaction_date date,
2 sex character (1),
2 suppress_junk_mail bit (1);</lang>
 
=={{header|PicoLisp}}==
<langsyntaxhighlight PicoLisplang="picolisp">(scl 2)
 
(class +Account +Entity)
Line 432 ⟶ 724:
(if (: active) "Yes" "No")
(: username)
(money (: balance)) ) ) )</langsyntaxhighlight>
Output:
<pre>account_id created active username balance
12345 20090513 No John Doe 77.22
12346 20090514 Yes Jane Miller 123.75</pre>
 
=={{header|PL/I}}==
<syntaxhighlight lang="pl/i">declare 1 table (100),
2 name character (20) varying,
2 address,
3 number fixed decimal,
3 street character (30) varying,
3 suburb character (30) varying,
3 zip picture '9999',
2 transaction_date date,
2 sex character (1),
2 suppress_junk_mail bit (1);</syntaxhighlight>
 
=={{header|PostgreSQL}}==
Line 442 ⟶ 746:
Postgres developers, please feel free to add additional data-types you commonly use to this example.
 
<langsyntaxhighlight lang="sql">-- This is a comment
 
CREATE SEQUENCE account_seq start 100;
Line 469 ⟶ 773:
-- char(#): space padded text field with length of #
-- varchar(#): variable length text field up to #
-- text: not limited</langsyntaxhighlight>
 
 
=={{header|Python}}==
{{libheader|SQLite}}
The sqlite3 database is a part of the Python standard library. It does not associate type with table columns, any cell can be of any type.
<langsyntaxhighlight lang="python">>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()
Line 509 ⟶ 812:
(u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
(u'2006-04-05', u'BUY', u'MSOFT', 1000.0, 72.0)
>>> </langsyntaxhighlight>
 
=={{header|Racket}}==
This is the relevant part of [[Table creation/Postal addresses#Racket]] that creates the DB table:
<langsyntaxhighlight lang="racket">
#lang racket
(require db)
(define postal (sqlite3-connect #:database "/tmp/postal.db" #:mode 'create))
</syntaxhighlight>
</lang>
 
=={{header|Raku}}==
(formerly Perl 6)
In Raku, there is no 'database' type built in, so it is somewhat ambiguous when specifying 'create a database table'. Raku offers bindings to most common databases through its DBIish module but mostly abstracts away the differences between the underlying databases, which hides many of the finer distinctions of what may be stored where. The actual data types and options available are properties of the database used.
 
If on the other hand, we are meant to show built in collective types that may be used to hold tabular data, this may be of some use.
 
In general, a container type can hold objects of any data type, even instances of their own type; allowing 'multi-dimensional' (tabular) containers.
 
Raku offers two broad categories of collective container types; those that do the Positional role and those that do Associative. Positional objects are collective objects that access the individual storage slots using an integer index. Associative objects use some sort of other pointer (typically string) to access their storage slots.
 
The various Associative types mostly differ in their value handling. Hash, Map and QuantHash may have any type of object as their value. All the others have some specific, usually numeric, type as their value.
 
<pre>
Positional - Object that supports looking up values by integer index
Array Sequence of itemized objects
List Immutable sequence of objects
 
Associative - Object that supports looking up values by key (typically string)
Bag Immutable collection of distinct objects with integer weights
BagHash Mutable collection of distinct objects with integer weights
Hash Mapping from strings to itemized values
Map Immutable mapping from strings to values
Mix Immutable collection of distinct objects with Real weights
MixHash Mutable collection of distinct objects with Real weights
QuantHash Collection of objects represented as hash keys
Set Immutable collection of distinct objects, no value except 'present'
SetHash Mutable collection of distinct objects, no value except 'present'
</pre>
 
If you want a persistent instance of any of these types, you need to declare the name with some scope constraint, but there are no prerequisites to creating instances. Simply assigning values to them will call them into existence.
 
=={{header|REXX}}==
Line 524 ⟶ 858:
However, tables (or structures) can be constructed by using stemmed arrays; &nbsp; the index would (should) be
<br>a unique identifier, &nbsp; something akin to a SSN &nbsp;(Social Security Number)&nbsp; or something similar.
<langsyntaxhighlight lang="rexx"> id = 000112222 /*could be a SSN or some other unique ID (or number).*/
 
table.id.!firstname = 'Robert'
Line 535 ⟶ 869:
table.id.!town = 'Gotham City'
table.id.!state = 'NY'
table.id.!zip = '12345-6789'</langsyntaxhighlight><br><br>
 
=={{header|Ring}}==
<langsyntaxhighlight lang="ring">
# Project : Table creation
 
Line 577 ⟶ 911:
next
sqlite_close(oSQLite)
</syntaxhighlight>
</lang>
Output:
<pre>
Line 610 ⟶ 944:
This code is enough to create a PStore (or open an existing PStore).
 
<langsyntaxhighlight lang="ruby">require 'pstore'
db = PStore.new "filename.pstore"</langsyntaxhighlight>
 
The example at [[Table creation/Postal addresses#Ruby]] puts Ruby objects into the PStore.
 
 
=={{header|Run BASIC}}==
Run Basic supports all features of SQLite.
This is a sample of a item master
<langsyntaxhighlight lang="runbasic">
#sql execute("
CREATE TABLE item (
Line 658 ⟶ 991:
CREATE UNIQUE INDEX item_descr ON item( descr, itemNum);
CREATE UNIQUE INDEX item_itemNum ON item(itemNum);"
</syntaxhighlight>
</lang>
 
=={{header|Scala}}==
===using SLICK FRM===
<langsyntaxhighlight Scalalang="scala">// Use H2Profile to connect to an H2 database
import slick.jdbc.H2Profile.api._
 
Line 697 ⟶ 1,030:
// Create the tables, including primary and foreign keys
(suppliers.schema ++ coffees.schema).create
)}</langsyntaxhighlight>
 
=={{header|SQL PL}}==
{{works with|Db2 LUW}}
<langsyntaxhighlight lang="sql pl">
CREATE TABLE dept (
deptno NUMERIC(2)
Line 734 ⟶ 1,068:
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
</syntaxhighlight>
</lang>
Output:
<output>
<pre>
db2 => CREATE TABLE dept (
db2 (cont.) => deptno NUMERIC(2)
Line 829 ⟶ 1,164:
 
8 record(s) selected.
</outputpre>
 
=={{header|Tcl}}==
Tables, as used in relational databases, seem far away conceptually from Tcl. However, the following code demonstrates how a table (implemented as a list of lists, the first being the header line) can be type-checked and rendered:
<langsyntaxhighlight Tcllang="tcl">proc table_update {_tbl row args} {
upvar $_tbl tbl
set heads [lindex $tbl 0]
Line 907 ⟶ 1,242:
balance 0.0 \
created 2009-05-14
puts [table_format $mytbl]</langsyntaxhighlight>
Output:
<pre>
Line 916 ⟶ 1,251:
| 12346 | 2009-05-14 | | Jane Miller | 0.0 |
+------------+------------+--------+-------------+---------+
</pre>
 
=={{header|Wren}}==
===Version 1===
{{libheader|Wren-dynamic}}
{{libheader|Wren-fmt}}
{{libheader|Wren-sort}}
Wren-cli does not currently have access to any external databases so, for the purposes of this task, we devise our own very simple in-memory database format, create a table, add some records to it and display them.
 
In practice, a binary search would be needed to find records quickly by key given that the records are being maintained in sorted order. However, for now we use a sequential search instead.
<syntaxhighlight lang="wren">import "./dynamic" for Enum, Tuple
import "./fmt" for Fmt
import "./sort" for Cmp, Sort
 
var FieldType = Enum.create("FieldType", ["text", "num", "int", "bool"])
 
var Field = Tuple.create("Field", ["name", "fieldType", "maxLen"])
 
class Table {
construct new(name, fields, keyIndex) {
_name = name
_fields = fields
_keyIndex = keyIndex // the zero based index of the field to sort on
_records = []
_fmt = ""
for (f in _fields) {
var c = f.name.count
var l = f.maxLen.max(c)
if (f.fieldType == FieldType.text ||f.fieldType == FieldType.bool) {
l = -l
}
_fmt = _fmt + "$%(l)s "
}
_fmt = _fmt.trimEnd()
}
 
name { _name }
 
showFields() {
System.print("Fields for %(_name) table:\n")
Fmt.print("$-20s $4s $s", "name", "type", "maxlen")
System.print("-" * 33)
for (f in _fields) {
Fmt.print("$-20s $-4s $d", f.name, FieldType.members[f.fieldType], f.maxLen)
}
}
 
cmp_ { Fn.new { |r1, r2|
return (Num.fromString(r1[_keyIndex]) - Num.fromString(r2[_keyIndex])).sign
}}
 
addRecord(record) {
var items = record.split(", ")
_records.add(items)
Sort.insertion(_records, cmp_) // move new record into sorted order
}
 
showRecords() {
System.print("Records for %(_name) table:\n")
var h = Fmt.slwrite(_fmt, _fields.map { |f| f.name }.toList)
System.print(h)
System.print("-" * h.count)
for (r in _records) {
Fmt.lprint(_fmt, r)
}
}
 
removeRecord(key) {
for (i in 0..._records.count) {
if (_records[i][_keyIndex] == key.toString) {
_records.removeAt(i)
return
}
}
}
 
findRecord(key) {
for (i in 0..._records.count) {
if (_records[i][_keyIndex] == key.toString) {
return _records[i].join(", ")
}
}
return null
}
}
 
var fields = []
fields.add(Field.new("id", FieldType.int, 2))
fields.add(Field.new("date", FieldType.text, 10))
fields.add(Field.new("trans", FieldType.text, 4))
fields.add(Field.new("sym", FieldType.text, 5))
fields.add(Field.new("qty", FieldType.int, 4))
fields.add(Field.new("price", FieldType.num, 5))
fields.add(Field.new("settled", FieldType.bool, 5))
 
// create table
var table = Table.new("Stock_transactions", fields, 0)
 
// add records in unsorted order
table.addRecord("3, 2006-04-06, SELL, IBM, 500, 53.00, true")
table.addRecord("1, 2006-01-05, BUY, RHAT, 100, 35.14, true")
table.addRecord("4, 2006-04-05, BUY, MSOFT, 1000, 72.00, false")
table.addRecord("2, 2006-03-28, BUY, IBM, 1000, 45.00, true")
 
// show the table's fields
table.showFields()
System.print()
// show the table's records in sorted order
table.showRecords()
 
// find a record by key
System.print("\nThe record with an id of 2 is:")
System.print(table.findRecord(2))
 
// delete a record by key
table.removeRecord(3)
System.print("\nThe record with an id of 3 will be deleted, leaving:\n")
table.showRecords()</syntaxhighlight>
 
{{out}}
<pre>
Fields for Stock_transactions table:
 
name type maxlen
---------------------------------
id int 2
date text 10
trans text 4
sym text 5
qty int 4
price num 5
settled bool 5
 
Records for Stock_transactions table:
 
id date trans sym qty price settled
--------------------------------------------------
1 2006-01-05 BUY RHAT 100 35.14 true
2 2006-03-28 BUY IBM 1000 45.00 true
3 2006-04-06 SELL IBM 500 53.00 true
4 2006-04-05 BUY MSOFT 1000 72.00 false
 
The record with an id of 2 is:
2, 2006-03-28, BUY, IBM, 1000, 45.00, true
 
The record with an id of 3 will be deleted, leaving:
 
Records for Stock_transactions table:
 
id date trans sym qty price settled
--------------------------------------------------
1 2006-01-05 BUY RHAT 100 35.14 true
2 2006-03-28 BUY IBM 1000 45.00 true
4 2006-04-05 BUY MSOFT 1000 72.00 false
</pre>
<br>
===Version 2===
{{libheader|Wren-table}}
The above module provides a more generic way to create simple databases and was not available when the first version was written.
<syntaxhighlight lang="wren">import "./table" for FieldInfo, Table, Records
 
var fields = [
FieldInfo.new("id", Num),
FieldInfo.new("date", String),
FieldInfo.new("trans", String),
FieldInfo.new("sym", String),
FieldInfo.new("qty", Num),
FieldInfo.new("price", Num),
FieldInfo.new("settled", Bool)
]
 
// create table
var table = Table.new("Stock_transactions", fields)
 
// add records
table.add([3, "2006-04-06", "SELL", "IBM" , 500, 53.00, true ])
table.add([1, "2006-01-05", "BUY" , "RHAT" , 100, 35.14, true ])
table.add([4, "2006-04-05", "BUY" , "MSOFT", 1000, 72.00, false])
table.add([2, "2006-03-28", "BUY" , "IBM" , 1000, 45.00, true ])
 
var colWidths = [2, 10, 4, 5, 4, 5.2, 7] // for listings
 
// show the table's fields
table.listFields()
System.print()
 
// sort the records by 'id' and show them
var sortFn = Fn.new { |s, t| s[0] < t[0] }
var records = table.sortedRecords(sortFn)
Records.list(table.fields, records, "Records for %(table.name) table:\n", colWidths)
 
// find a record by key
System.print("\nThe record with an id of 2 is:")
System.print(table.find(2))
 
// delete a record by key
table.remove(3)
System.print("\nThe record with an id of 3 will be deleted, leaving:\n")
records = table.sortedRecords(sortFn)
Records.list(table.fields, records, "Records for %(table.name) table:\n", colWidths)</syntaxhighlight>
 
{{out}}
<pre>
Fields for Stock_transactions table:
 
name kind
------- ------
id Num
date String
trans String
sym String
qty Num
price Num
settled Bool
 
Records for Stock_transactions table:
 
id date tran sym qty price settled
-- ---------- ---- ----- ---- ----- -------
1 2006-01-05 BUY RHAT 100 35.14 true
2 2006-03-28 BUY IBM 1000 45.00 true
3 2006-04-06 SELL IBM 500 53.00 true
4 2006-04-05 BUY MSOFT 1000 72.00 false
 
The record with an id of 2 is:
[2, 2006-03-28, BUY, IBM, 1000, 45, true]
 
The record with an id of 3 will be deleted, leaving:
 
Records for Stock_transactions table:
 
id date tran sym qty price settled
-- ---------- ---- ----- ---- ----- -------
1 2006-01-05 BUY RHAT 100 35.14 true
2 2006-03-28 BUY IBM 1000 45.00 true
4 2006-04-05 BUY MSOFT 1000 72.00 false
</pre>
Jjuanhdez
2,130

edits

Retrieved from "https://rosettacode.org/wiki/Special:MobileDiff/3277...361426"