Table creation/Postal addresses

Table creation/Postal addresses
You are encouraged to solve this task according to the task description, using any language you may know.

Task

Create a table to store addresses.

You may assume that all the addresses to be stored will be located in the USA.   As such, you will need (in addition to a field holding a unique identifier) a field holding the street address, a field holding the city, a field holding the state code, and a field holding the zipcode.   Choose appropriate types for each field.

For non-database languages, show how you would open a connection to a database (your choice of which) and create an address table in it. You should follow the existing models here for how you would structure the table.

ALGOL 68

Works with: ALGOL 68 version Standard - no extensions to language used

Works with: ALGOL 68G version Any - tested with release mk15-0.8b.fc9.i386

MODE ADDRESS = STRUCT(
	INT page,
	FLEX[50]CHAR street,
	FLEX[25]CHAR city,
	FLEX[2]CHAR state,
	FLEX[10]CHAR zip
);
FORMAT address repr = $"Page: "gl"Street: "gl"City: "gl"State: "gl"Zip: "gll$;

INT errno;
FILE sequence; errno := open(sequence, "sequence.txt", stand back channel);
SEMA sequence sema := LEVEL 1;

OP NEXTVAL = ([]CHAR table name)INT: (
  INT out;
  # INT table page = 0; # # only one sequence implemented #
  # DOWN sequence sema; # # NO interprocess concurrency protection #
    on open error(sequence, 
      (REF FILE f)BOOL: (
        reset(sequence); #set(table page,1,1);# 
        put(sequence, 0);
        try again;
        FALSE
      )
    );
    try again:
    reset(sequence); #set(table page,1,1);# get(sequence,out);
    out +:=1; 
    reset(sequence); #set(table page,1,1);# put(sequence,out);
  # UP sequence sema; #
  out
);

OP INIT = (REF ADDRESS self)REF ADDRESS: ( page OF self := NEXTVAL "address"; self);

REF ADDRESS john brown = INIT LOC ADDRESS;

john brown := (page OF john brown, "10 Downing Street","London","England","SW1A 2AA");

printf((address repr, john brown));

FILE address table;
errno := open(address table,"address.txt",stand back channel);
# set(address table, page OF john brown,1,1);  - standard set page not available in a68g #
put bin(address table, john brown);
close(address table)

Output:

Page:          +1
Street: 10 Downing Strreet
City: London
State: England
Zip: SW1A 2AA

Apache Derby

create table Address (
  addrID     integer primary key generated by default as identity,
  addrStreet varchar(50) not null,
  addrCity   varchar(50) not null,
  addrState  char(2)     not null,
  addrZip    char(10)    not null
);

Interactive session:

$ ij
ij version 10.8
ij> connect 'jdbc:derby:postal_addresses;create=true';
ij> create table Address (
>   addrID integer primary key generated by default as identity,
>   addrStreet varchar(50) not null,
>   addrCity   varchar(50) not null,
>   addrState  char(2)     not null,
>   addrZip    char(10)    not null
> );
0 rows inserted/updated/deleted
ij> show connections;
CONNECTION0* - 	jdbc:derby:postal_addresses
* = current connection
ij> describe address;
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ADDRID              |INTEGER  |0   |10  |10    |GENERATED&|NULL      |NO      
ADDRSTREET          |VARCHAR  |NULL|NULL|50    |NULL      |100       |NO      
ADDRCITY            |VARCHAR  |NULL|NULL|50    |NULL      |100       |NO      
ADDRSTATE           |CHAR     |NULL|NULL|2     |NULL      |4         |NO      
ADDRZIP             |CHAR     |NULL|NULL|10    |NULL      |20        |NO      

5 rows selected
ij> exit;
$ dblook -d jdbc:derby:postal_addresses
-- Timestamp: 2012-07-17 14:27:02.822
-- Source database is: postal_addresses
-- Connection URL is: jdbc:derby:postal_addresses
-- appendLogs: false

-- ----------------------------------------------
-- DDL Statements for tables
-- ----------------------------------------------

CREATE TABLE "APP"."ADDRESS" ("ADDRID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "ADDRSTREET" VARCHAR(50) NOT NULL, "ADDRCITY" VARCHAR(50) NOT NULL, "ADDRSTATE" CHAR(2) NOT NULL, "ADDRZIP" CHAR(10) NOT NULL);

-- ----------------------------------------------
-- DDL Statements for keys
-- ----------------------------------------------

-- primary/unique
ALTER TABLE "APP"."ADDRESS" ADD CONSTRAINT "SQL120717142048690" PRIMARY KEY ("ADDRID");

Arturo

db: open.sqlite "addresses.db"

query db {!sql
    CREATE TABLE address (
        addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
        addrStreet TEXT NOT NULL,
        addrCity   TEXT NOT NULL,
        addrState  TEXT NOT NULL,
        addrZIP    TEXT NOT NULL
    )
}

close db

AWK

SQLite3

This version uses the AWK pipe, 'getline' function, and the sqlite3 command line program.

#!/bin/sh -f
awk '
BEGIN {
    print "Creating table..."
    dbExec("address.db", "create table address (street, city, state, zip);")
    print "Done."
    exit
}

function dbExec(db, qry,      result) {
    dbMakeQuery(db, qry) | getline result
    dbErrorCheck(result)
}

function dbMakeQuery(db, qry,      q) {
    q = dbEscapeQuery(qry) ";"
    return "echo \"" q "\" | sqlite3 " db
}

function dbEscapeQuery(qry,      q) {
    q = qry
    gsub(/"/, "\\\"", q)
    return q
}

function dbErrorCheck(res) {
    if (res ~ "SQL error") {
        print res
        exit
    }
}

'

BASIC256

# create a new database file or open it
dbopen "addresses.sqlite3"

# delete the existing table - If it is a new database, the error is captured
onerror errortrap
dbexecute "drop table addresses;"
offerror

# create the table
dbexecute "CREATE TABLE addresses (addrID integer, addrStreet string, addrCity string, addrState string, addrZIP string);"

# close all
dbclose
end

errortrap:
# accept the error - show nothing - return to the next statement
return

C

Library: SQLite

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

const char *code = 
"CREATE TABLE address (\n"
"       addrID		INTEGER PRIMARY KEY AUTOINCREMENT,\n"
"	addrStreet	TEXT NOT NULL,\n"
"	addrCity	TEXT NOT NULL,\n"
"	addrState	TEXT NOT NULL,\n"
"	addrZIP		TEXT NOT NULL)\n" ;

int main()
{
  sqlite3 *db = NULL;
  char *errmsg;
  
  if ( sqlite3_open("address.db", &db) == SQLITE_OK ) {
    if ( sqlite3_exec(db, code, NULL, NULL,  &errmsg) != SQLITE_OK ) {
      fprintf(stderr, errmsg);
      sqlite3_free(errmsg);
      sqlite3_close(db);
      exit(EXIT_FAILURE);
    }
    sqlite3_close(db);
  } else {
    fprintf(stderr, "cannot open db...\n");
    sqlite3_close(db);
    exit(EXIT_FAILURE);
  }
  return EXIT_SUCCESS;
}

C++

This example completes the task with a random access file, instead of using an external library.

#include <cstdint>
#include <fstream>
#include <iostream>
#include <string>
#include <vector>

class Address {
public:
	Address(const std::string& aName, const std::string& aStreet, const std::string& aCity,
			const std::string& aState, const std::string& aZipCode)
	: name(aName), street(aStreet), city(aCity), state(aState), zipCode(aZipCode) {}

	std::string address_record() {
		std::string record;
		record += fixed_length(name, 30);
		record += fixed_length(street, 30);
		record += fixed_length(city, 15);
		record += fixed_length(state, 5);
		record += fixed_length(zipCode, 10);
		return record;
	}

	static constexpr uint32_t RECORD_LENGTH = 90;

private:
	std::string fixed_length(const std::string& text, const uint64_t& size) {
		return ( text.length() > size ) ? text.substr(0, size) : text + std::string(size - text.length(), ' ');
	}

	std::string name, street, city, state, zipCode;
};

int main() {
	std::vector<Address> addresses = {
		Address("FSF Inc.", "51 Franklin Street", "Boston", "MA", "02110-1301"),
		Address("The White House", "1600 Pennsylvania Avenue NW", "Washington", "DC", "20500"),
		Address("National Security Council", "1700 Pennsylvania Avenue NW", "Washington", "DC", "20500")
	};

	std::fstream file("addresses.dat", std::ios::app | std::ios::in | std::ios::out);
	if ( ! file ) {
		std::cerr << "Error. Cannot open file." << std::endl;
		exit(EXIT_FAILURE);
	}

	for ( uint64_t i = 0; i < addresses.size(); ++i ) {
		file.seekp(i * Address::RECORD_LENGTH, std::ios::beg);
		file << addresses[i].address_record();
	}

	for ( uint64_t i = 0; i < addresses.size(); ++i ) {
		file.seekg(i * Address::RECORD_LENGTH, std::ios::beg);
		char ch;
		std::string address;
		while ( address.length() < Address::RECORD_LENGTH ) {
			file.get(ch);
			address += ch;
		}
		std::cout << address << std::endl;
	}

	file.close();
}

Output:

FSF Inc.                      51 Franklin Street            Boston         MA   02110-1301
The White House               1600 Pennsylvania Avenue NW   Washington     DC   20500     
National Security Council     1700 Pennsylvania Avenue NW   Washington     DC   20500     

Clojure

(require '[clojure.java.jdbc :as sql])
; Using h2database for this simple example.
(def db {:classname "org.h2.Driver"
         :subprotocol "h2:file"
         :subname "db/my-dbname"})
         
(sql/db-do-commands db
  (sql/create-table-ddl :address
    [:id "bigint primary key auto_increment"]
    [:street "varchar"]
    [:city "varchar"]
    [:state "varchar"]
    [:zip "varchar"]))

EchoLisp

(lib 'struct)
(lib 'sql)

(define Postal (make-table
    (struct postal (auto: id name street city state zip))))

Postal
    → #table:#struct:postal [id name street city state zip]:[0] 

(table-insert Postal '(0 Gallubert "29 rue de l'Ermitage" Paris Seine 75020))
(table-insert Postal '(0 Brougnard "666 rue des Cascades " Paris Seine 75042))
(table-make-index Postal 'postal.id)
(table-print Postal)

[0]   15  Gallubert  29 rue de l'Ermitage   Paris  Seine  75020 
[1]   16  Brougnard  666 rue des Cascades   Paris  Seine  75042

Erlang

Erlang has built in databases. This is the the one with most features: Mnesia. There are database connectors to other databases, too.

-module( table_creation ).

-export( [task/0] ).

-record( address, {id, street, city, zip} ).

task() ->
	mnesia:start(),
	mnesia:create_table( address, [{attributes, record_info(fields, address)}] ).

Output:

3>  table_creation:task().
{atomic,ok}

FunL

FunL has built-in support for H2 and comes bundled with the H2 database engine.

import db.*
import util.*

Class.forName( 'org.h2.Driver' )
conn = DriverManager.getConnection( 'jdbc:h2:mem:test', 'sa', '' )
statement = conn.createStatement()

statement.execute( '''
  CREATE TABLE `user_data` (
    `id` identity,
    `name` varchar(255) NOT NULL,
    `street` varchar(255) NOT NULL,
    `city` varchar(255) NOT NULL,
    `region` char(2) NOT NULL,
    `country` char(2) NOT NULL,
    `code` varchar(20) NOT NULL,
    `phone` varchar(20) NOT NULL,
    PRIMARY KEY (`id`)
  )''' )

statement.execute( '''
  INSERT INTO `user_data` (`name`, `street`, `city`, `region`, `code`, `country`, `phone`) VALUES
    ('Jacinthe Steinert', '8540 Fallen Pony Villas', 'Searights', 'IA', '51584-4315', 'US', '(641) 883-4342'),
    ('Keeley Pinkham', '1363 Easy Downs', 'Mileta', 'TX', '77667-7376', 'US', '(469) 527-4784'),
    ('Rimon Cleveland', '8052 Blue Pond Dale', 'The Willows', 'UT', '84630-2674', 'US', '(385) 305-7261'),
    ('Berenice Benda', '2688 Merry Pines', 'Dacono', 'HI', '96766-7398', 'US', '(808) 451-2732'),
    ('Mehetabel Marcano', '109 Sleepy Goose Crescent', 'Plains', 'UT', '84727-7254', 'US', '(385) 733-8404'),
    ('Ambria Schiller', '7100 Tawny Robin Highway', 'Barlowes', 'ID', '83792-2043', 'US', '(208) 227-8887'),
    ('Carne Cancino', '3842 Broad Pioneer Cape', 'Bardstown', 'IA', '51571-6473', 'US', '(563) 060-8352'),
    ('Ince Leite', '7876 Stony Fawn Boulevard', 'Easton', 'ID', '83651-9235', 'US', '(208) 951-3024'),
    ('Britney Odell', '3386 Lazy Shadow Thicket', 'Kimberly', 'OK', '73539-6632', 'US', '(539) 848-4448'),
    ('Suprabha Penton', '9311 Dusty Leaf Alley', 'Niumalu', 'GA', '39927-8332', 'US', '(404) 589-0183')''' )
		
result = statement.executeQuery( '''SELECT * FROM user_data WHERE region = 'ID' ORDER BY code''' )
print( TextTable.apply(result) )

conn.close()

Output:

+----+-----------------+---------------------------+----------+--------+---------+------------+----------------+
| ID |      NAME       |          STREET           |   CITY   | REGION | COUNTRY |    CODE    |     PHONE      |
+----+-----------------+---------------------------+----------+--------+---------+------------+----------------+
|  8 | Ince Leite      | 7876 Stony Fawn Boulevard | Easton   | ID     | US      | 83651-9235 | (208) 951-3024 |
|  6 | Ambria Schiller | 7100 Tawny Robin Highway  | Barlowes | ID     | US      | 83792-2043 | (208) 227-8887 |
+----+-----------------+---------------------------+----------+--------+---------+------------+----------------+

FreeBASIC

Library: SQLite

#include once "sqlite3.bi"

Const NULL As Any Ptr = 0

Dim As sqlite3 Ptr db
Dim As zstring Ptr errMsg

If sqlite3_open(":memory:", @db) <> SQLITE_OK Then
    Print "Could not open database: "; sqlite3_errmsg(db)
    sqlite3_close(db)
    Sleep
    End 1
End If

Dim As String sql = "CREATE TABLE address(" _
    & "addrID  INTEGER PRIMARY KEY AUTOINCREMENT," _
    & "addrStreet TEXT NOT NULL," _
    & "addrCity   TEXT NOT NULL," _
    & "addrState  TEXT NOT NULL," _
    & "addrZIP    TEXT NOT NULL);"

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

sqlite3_close(db)

Sleep

Go

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    // task req: show database connection
    db, err := sql.Open("sqlite3", "rc.db")
    if err != nil {
        log.Print(err)
        return
    }
    defer db.Close()
    // task req: create table with typed fields, including a unique id
    _, err = db.Exec(`create table addr (
        id     int unique,
        street text,
        city   text,
        state  text,
        zip    text
    )`)
    if err != nil {
        log.Print(err)
        return
    }
    // show output:  query the created field names and types
    rows, err := db.Query(`pragma table_info(addr)`)
    if err != nil {
        log.Print(err)
        return
    }
    var field, storage string
    var ignore sql.RawBytes
    for rows.Next() {
        err = rows.Scan(&ignore, &field, &storage, &ignore, &ignore, &ignore)
        if err != nil {
            log.Print(err)
            return
        }
        fmt.Println(field, storage)
    }
}

Output:

id int
street text
city text
state text
zip text

Haskell

Translation of: Python

Library: SQLite

Library: sqlite-simple

{-# LANGUAGE OverloadedStrings #-}

import Database.SQLite.Simple

main = do
     db <- open "postal.db"
     execute_ db "\ 
     \CREATE TABLE address (\
        \addrID     INTEGER PRIMARY KEY AUTOINCREMENT, \
        \addrStreet TEXT NOT NULL, \
        \addrCity   TEXT NOT NULL, \
        \addrState  TEXT NOT NULL, \
        \addrZIP    TEXT NOT NULL  \
     \)"
     close db

J

J is a programming language, not a database, but it ships with a database built in the programming language called JDB. Using that, assuming hd is your database, then:

   Create__hd  'Address';noun define
addrID autoid;
addrStreet varchar
addrCity varchar
addrState char
addrZip char
)

Of course J can connect external databases too, using e.g. ODBC. See the list of J database topics.

Java

Java can connect with many databases. This example completes the task with a random access file, instead of using an external library.

 
import java.io.IOException;
import java.nio.ByteBuffer;
import java.nio.channels.FileChannel;
import java.nio.file.Path;
import java.nio.file.StandardOpenOption;

public final class TableCreationPostalAddresses {

	public static void main(String[] args) throws IOException {	
		Address[] addresses = new Address[] {
			new Address("FSF Inc.", "51 Franklin Street", "Boston", "MA", "02110-1301"),
			new Address("The White House", "1600 Pennsylvania Avenue NW", "Washington", "DC", "20500"),
			new Address("National Security Council", "1700 Pennsylvania Avenue NW", "Washington", "DC", "20500")
		};
		
        Path path = Path.of("addresses.dat");
		FileChannel fileChannel = FileChannel.open(path, StandardOpenOption.CREATE,
														 StandardOpenOption.READ, 
														 StandardOpenOption.WRITE);			
		for ( int i = 0; i < addresses.length; i++ ) {
			byte[] data = addresses[i].addressRecord().getBytes();
		    ByteBuffer writeBuffer = ByteBuffer.wrap(data);
		    fileChannel.position(i * Address.RECORD_LENGTH);
		    while ( writeBuffer.hasRemaining() ) {
		        fileChannel.write(writeBuffer);
		    }			
		}
		
		for ( int i = 0; i < addresses.length; i++ ) {
			fileChannel.position(i * Address.RECORD_LENGTH);
			ByteBuffer readBuffer = ByteBuffer.allocate(Address.RECORD_LENGTH);
			fileChannel.read(readBuffer);
		    System.out.println( new String(readBuffer.array()) );			
		}
		
		fileChannel.close();
	}
	
}

final class Address {
	
	public Address(String aName, String aStreet, String aCity, String aState, String aZipCode) {
		name = aName; street = aStreet; city = aCity; state = aState; zipCode = aZipCode;
	}
	
	public String addressRecord() {
		String record = "";
		record += String.format("%-30s", name);
		record += String.format("%-30s", street);
		record += String.format("%-15s", city);
		record += String.format("%-5s", state);
		record += String.format("%-10s", zipCode);
		return record;		
	}
	
	public static final int RECORD_LENGTH = 30 + 30 + 15 + 5 + 10;
	
	private String name, street, city, state, zipCode;
	
}

Output:

FSF Inc.                      51 Franklin Street            Boston         MA   02110-1301
The White House               1600 Pennsylvania Avenue NW   Washington     DC   20500     
National Security Council     1700 Pennsylvania Avenue NW   Washington     DC   20500    

Julia

Works with: Julia version 0.6

using SQLite

db = SQLite.DB()
SQLite.execute!(db, """\
	CREATE TABLE address (
	addrID		INTEGER PRIMARY KEY AUTOINCREMENT,
	addrStreet	TEXT NOT NULL,
	addrCity	TEXT NOT NULL,
	addrState	TEXT NOT NULL,
	addrZIP		TEXT NOT NULL)
	""")

Kotlin

Rather than use an external database, we use the built-in RandomAccessFile class for his task. The data used is the same as for the REXX entry.

// Version 1.2.41

import java.io.File
import java.io.RandomAccessFile

fun String.toFixedLength(len: Int) = this.padEnd(len).substring(0, len)

class Address(
    var name: String,
    var street: String = "",
    var city: String = "",
    var state: String = "",
    var zipCode: String = "",
    val autoId: Boolean = true
) {
    var id = 0L
        private set

    init {
        if (autoId) id = ++nextId
    }

    companion object {
        private var nextId = 0L

        const val RECORD_LENGTH = 127  // including 2 bytes for UTF string length

        fun readRecord(file: File, id: Long): Address {
            val raf = RandomAccessFile(file, "r")
            val seekPoint = (id - 1) * RECORD_LENGTH
            raf.use {
                it.seek(seekPoint)
                val id2 = it.readLong()
                if (id != id2) {
                    println("Database is corrupt")
                    System.exit(1)
                }
                val text    = it.readUTF()
                val name    = text.substring(0, 30).trimEnd()
                val street  = text.substring(30, 80).trimEnd()
                val city    = text.substring(80, 105).trimEnd()
                val state   = text.substring(105, 107)
                val zipCode = text.substring(107).trimEnd()
                val a = Address(name, street, city, state, zipCode, false)
                a.id = id
                return a
            }
        }
    }

    override fun toString() =
        "Id       : ${this.id}\n" +
        "Name     : $name\n" +
        "Street   : $street\n" +
        "City     : $city\n" +
        "State    : $state\n" +
        "Zip Code : $zipCode\n"

    fun writeRecord(file: File) {
        val raf = RandomAccessFile(file, "rw")
        val text =
            name.toFixedLength(30) +
            street.toFixedLength(50) +
            city.toFixedLength(25) +
            state +
            zipCode.toFixedLength(10)
        val seekPoint = (id - 1) * RECORD_LENGTH
        raf.use {
            it.seek(seekPoint)
            it.writeLong(id)
            it.writeUTF(text)
        }
    }
}

fun main(args: Array<String>) {
    val file = File("addresses.dat")
    val addresses = listOf(
        Address("FSF Inc.", "51 Franklin Street", "Boston", "MA", "02110-1301"),
        Address("The White House", "The Oval Office, 1600 Pennsylvania Avenue NW", "Washington", "DC", "20500")
    )
    // write the address records to the file
    addresses.forEach { it.writeRecord(file) }

    // now read them back in reverse order and print them out
    for (i in 2 downTo 1) {
        println(Address.readRecord(file, i.toLong()))
    }
}

Output:

Id       : 2
Name     : The White House
Street   : The Oval Office, 1600 Pennsylvania Avenue NW
City     : Washington
State    : DC
Zip Code : 20500

Id       : 1
Name     : FSF Inc.
Street   : 51 Franklin Street
City     : Boston
State    : MA
Zip Code : 02110-1301

Lasso

Lasso has excellent support for connecting to and handling databases.

// connect to a Mysql database
inline(-database = 'rosettatest', -sql = "CREATE TABLE `address` (
    `id`       int(11)     NOT NULL   auto_increment,
    `street`   varchar(50) NOT NULL   default '',
    `city`     varchar(25) NOT NULL   default '',
    `state`    char(2)     NOT NULL   default '',
    `zip`      char(10)    NOT NULL   default '',
    PRIMARY KEY (`id`)
);
") => {^
	error_msg
^}

Output:

No error

Lua

Using LJSQLite3 - compatible with LuaJIT and supplied in the ULua distribution.

-- Import module
local sql = require("ljsqlite3")

-- Open connection to database file
local conn = sql.open("address.sqlite")

-- Create address table unless it already exists
conn:exec[[
CREATE TABLE IF NOT EXISTS address(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  street TEXT NOT NULL, 
  city TEXT NOT NULL, 
  state TEXT NOT NULL, 
  zip TEXT NOT NULL)
]]

-- Explicitly close connection
conn:close()

Mathematica/Wolfram Language

TableCreation="CREATE TABLE address (	
addrID		INTEGER PRIMARY KEY AUTOINCREMENT,
addrStreet	TEXT NOT NULL,	addrCity	TEXT NOT NULL,
addrState	TEXT NOT NULL,	addrZIP		TEXT NOT NULL    )";
Needs["DatabaseLink`"]
conn=OpenSQLConnection[ JDBC[ "mysql","databases:1234/conn_test"], "Username" -> "test"]
SQLExecute[ conn, TableCreation]

MySQL

CREATE TABLE `Address` (
    `addrID`       int(11)     NOT NULL   auto_increment,
    `addrStreet`   varchar(50) NOT NULL   default '',
    `addrCity`     varchar(25) NOT NULL   default '',
    `addrState`    char(2)     NOT NULL   default '',
    `addrZIP`      char(10)    NOT NULL   default '',
    PRIMARY KEY (`addrID`)
);

NetRexx

As NetRexx targets the Java Virtual Machine it has access to a wealth of database tools many of which can be accessed through JDBC.

Apache Derby

Library: Apache Derby

This sample creates a table in an embedded Apache Derby database.

/* NetRexx */
options replace format comments java crossref symbols binary

import java.sql.Connection
import java.sql.Statement
import java.sql.SQLException
import java.sql.DriverManager

class RTableCreate01 public
  properties private constant
    addressDDL = String '' -
    ' create table Address' -
    ' (' -
    '   addrID     integer     primary key generated by default as identity,' -
    '   addrStreet varchar(50) not null,' -
    '   addrCity   varchar(50) not null,' -
    '   addrState  char(2)     not null,' -
    '   addrZip    char(10)    not null' -
    ' )'
    driver = String 'org.apache.derby.jdbc.EmbeddedDriver'
    dbName = String 'db/rosetta_code'

  method createTable() public static
    connectionURL = String 
    conn = java.sql.Connection
    sqlStatement = java.sql.Statement
    do
      Class.forName(driver)
      connectionURL = 'jdbc:derby:' || dbName || ';' || 'create=true'
      conn = DriverManager.getConnection(connectionURL)
      sqlStatement = conn.createStatement()
      say 'Creating table'
      sqlStatement.execute(addressDDL)
      say 'Table creation complete'
      sqlStatement.close()
      conn.close()
      do
        -- In embedded mode, an application should shut down Derby.
        -- Shutdown throws the XJ015 exception to confirm success.
        connectionURL = 'jdbc:derby:' || ';' || 'shutdown=true'
        DriverManager.getConnection(connectionURL)
      catch sex = SQLException
        if sex.getSQLState().equals("XJ015") then do
          say 'Database shut down normally'
          end
        else do
          say 'Database did not shut down normally'
          signal sex
          end
      end
    catch sex = SQLException
      sex.printStackTrace()
    catch ex = ClassNotFoundException
      ex.printStackTrace()
    end
    return

  method main(args = String[]) public static
    createTable()
    return

Nim

import db_sqlite as db
#import db_mysql as db
#import db_postgres as db

const
  connection = ":memory:"
  user = "foo"
  pass = "bar"
  database = "db"

var c = open(connection, user, pass, database)
c.exec sql"""CREATE TABLE address (
  addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
  addrStreet TEXT NOT NULL,
  addrCity   TEXT NOT NULL,
  addrState  TEXT NOT NULL,
  addrZIP    TEXT NOT NULL)"""
c.close()

ooRexx

/* REXX ***************************************************************
* 17.05.2013 Walter Pachl  translated from REXX version 2
* nice try? improvements are welcome as I am rather unexperienced
* 18.05.2013 the array may contain a variety of objects!
**********************************************************************/
alist=.array~new
alist[1]=.addr~new('Boston','MA','51 Franklin Street',,'FSF Inc.',,
                                                          '02110-1301')
alist[2]='not an address at all'
alist[3]=.addr~new('Washington','DC','The Oval Office',,
                 '1600 Pennsylvania Avenue NW','The White House',20500)
Do i=1 To alist~items
  a=alist[i]
  If a~isinstanceof(.addr) Then
    a~show
  End 
   
::class addr
  ::attribute city
  ::attribute state
  ::attribute addr
  ::attribute addr2
  ::attribute name
  ::attribute zip

::method init
  Parse Arg self~city,,
            self~state,,
            self~addr,,
            self~addr2,,
            self~name,,
            self~zip

::method show
                         Say '  name -->' self~name
                         Say '  addr -->' self~addr
  If self~addr2<>'' Then Say ' addr2 -->' self~addr2
                         Say '  city -->' self~city
                         Say ' state -->' self~state
                         Say '   zip -->' self~zip
  Say copies('-',40)

Output is as for REXX version 2

Oracle

CREATE SEQUENCE seq_address_pk START BY 100 INCREMENT BY 1
/
CREATE TABLE address (
    addrID   NUMBER DEFAULT seq_address_pk.nextval,
    street   VARCHAR2( 50 ) NOT NULL,
    city     VARCHAR2( 25 ) NOT NULL,
    state    VARCHAR2( 2 ) NOT NULL,
    zip      VARCHAR2( 20 ) NOT NULL,
    CONSTRAINT address_pk1 PRIMARY KEY ( addrID )
)
/

Oz

Translation of: Python

Library: SQLite

Library: Ozsqlite

The SQLite version that comes with Ozsqlite does not understand "AUTOINCREMENT".

declare
  [Sqlite] = {Module.link ['x-ozlib:/sqlite/Sqlite.ozf']}

  DB = {Sqlite.open 'test.db'}
in
  try

     {Sqlite.exec DB
      "CREATE TABLE address ("
      #"addrID		INTEGER PRIMARY KEY,"
      #"addrStreet	TEXT NOT NULL,"
      #"addrCity	TEXT NOT NULL,"
      #"addrState	TEXT NOT NULL,"
      #"addrZIP		TEXT NOT NULL"
      #")" _}

  catch E then
     {Inspector.configure widgetShowStrings true}
     {Inspect E}
  finally
     {Sqlite.close DB}
  end

Perl

use DBI;

my $db = DBI->connect('DBI:mysql:database:server','login','password');

my $statment = <<EOF;
CREATE TABLE `Address` (
    `addrID`       int(11)     NOT NULL   auto_increment,
    `addrStreet`   varchar(50) NOT NULL   default '',
    `addrCity`     varchar(25) NOT NULL   default '',
    `addrState`    char(2)     NOT NULL   default '',
    `addrZIP`      char(10)    NOT NULL   default '',
    PRIMARY KEY (`addrID`)
);
EOF

my $exec = $db->prepare($statment);
$exec->execute;

This example uses mysql, but DBI supports a extensive list of database drivers. See dbi.perl.org for more info.

Phix

Library: SQLite

without js -- (file i/o)
include pSQLite.e
constant sqlcode = """
CREATE TABLE address (
 addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
 addrStreet TEXT NOT NULL,
 addrCity   TEXT NOT NULL,
 addrState  TEXT NOT NULL,
 addrZIP    TEXT NOT NULL)"""
 
sqlite3 db = sqlite3_open("address.sqlite")
integer res = sqlite3_exec(db,sqlcode)
if res=SQLITE_OK then
    sqlite3_close(db)
else
    -- can show eg "sqlite3_exec error: 1 [table address already exists]"
    printf(1,"sqlite3_exec error: %d [%s]\n",{res,sqlite_last_exec_err})
end if

PHP+SQLite

Translation of: Python

not tested

<?php
$db = new SQLite3(':memory:');
$db->exec("
    CREATE TABLE address (
        addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
        addrStreet TEXT NOT NULL,
        addrCity   TEXT NOT NULL,
        addrState  TEXT NOT NULL,
        addrZIP    TEXT NOT NULL
    )
");
?>

PicoLisp

PicoLisp has built-in database functionality, in the form of (non-relational) entity/relations, built on top of persistent objects (so-called external symbols)

Define an "address" entity, and create the database:

(class +Adr +Entity)
(rel nm (+Sn +Idx +String))            # Name [Soundex index]
(rel str (+String))                    # Street
(rel zip (+Ref +String))               # ZIP [Non-unique index]
(rel cit (+Fold +Idx +String))         # City [Folded substring index]
(rel st (+String))                     # State
(rel tel (+Fold +Ref +String))         # Phone [Folded non-unique index]
(rel em (+Ref +String))                # EMail [Non-unique index]
(rel txt (+Blob))                      # Memo
(rel jpg (+Blob))                      # Photo

(pool "address.db")  # Create database

Create a first entry, and show it:

(show
   (new! '(+Adr)  # Create a record
      'nm "FSF Inc."
      'str "51 Franklin St"
      'st "Boston, MA"
      'zip "02110-1301" ) )

Output:

{2} (+Adr)
   zip "02110-1301"
   st "Boston, MA"
   str "51 Franklin St"
   nm "FSF Inc."

Interactive "select":

(select nm zip +Adr nm "FSF")  # Select name, zip from Adr where name = FSF*

Output:

"FSF Inc." "02110-1301" {2}

PostgreSQL

CREATE SEQUENCE address_seq start 100;
CREATE TABLE address (
    addrID   int4 PRIMARY KEY DEFAULT nextval('address_seq'),
    street   varchar(50) not null,
    city     varchar(25) not null,
    state    varchar(2) not null,
    zip      varchar(20) not null
);

PowerShell+SQLite

Library: SQLite

Import-Module -Name PSSQLite


## Create a database and a table
$dataSource = ".\Addresses.db"
$query = "CREATE TABLE SSADDRESS (Id        INTEGER  PRIMARY KEY  AUTOINCREMENT,
                                  LastName  TEXT     NOT NULL,
                                  FirstName TEXT     NOT NULL,
                                  Address   TEXT     NOT NULL,
                                  City      TEXT     NOT NULL,
                                  State     CHAR(2)  NOT NULL,
                                  Zip       CHAR(5)  NOT NULL
)"

Invoke-SqliteQuery -Query $Query -DataSource $DataSource


## Insert some data
$query = "INSERT INTO SSADDRESS ( FirstName,  LastName,  Address,  City,  State,  Zip)
                         VALUES (@FirstName, @LastName, @Address, @City, @State, @Zip)"

Invoke-SqliteQuery -DataSource $DataSource -Query $query -SqlParameters @{
        LastName  = "Monster"
        FirstName = "Cookie"
        Address   = "666 Sesame St"
        City      = "Holywood"
        State     = "CA"
        Zip       = "90013"
}


## View the data
Invoke-SqliteQuery -DataSource $DataSource -Query "SELECT * FROM SSADDRESS" | FormatTable -AutoSize

Output:

Id LastName FirstName Address       City     State Zip  
-- -------- --------- -------       ----     ----- ---  
 1 Monster  Cookie    666 Sesame St Holywood CA    90013

PureBasic+SQLite

Easiest approach with sqlite. Further possible: PostgresQL or each other over ODBC.

UseSQLiteDatabase()
Procedure CheckDatabaseUpdate(Database, Query$)
   Result = DatabaseUpdate(Database, Query$)
   If Result = 0
      Print(DatabaseError())
   EndIf
   ProcedureReturn Result
EndProcedure
openconsole()
DatabaseFile$ = GetCurrentDirectory()+"/rosettadb.sdb"
If CreateFile(0, DatabaseFile$)
   CloseFile(0)
    If OpenDatabase(0, DatabaseFile$, "", "")
      CheckDatabaseUpdate(0,"CREATE TABLE address ( addrID INTEGER PRIMARY KEY AUTOINCREMENT,	addrStreet TEXT Not NULL, addrCity TEXT Not NULL, addrState TEXT Not NULL, addrZIP TEXT Not NULL)")
      CloseDatabase(0)
   Else
      print("Can't open database !")
   EndIf
Else
   print("Can't create the database file !")
EndIf
closeconsole()

Python+SQLite

Library: SQLite

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> conn.execute('''CREATE TABLE address (
	addrID		INTEGER PRIMARY KEY AUTOINCREMENT,
	addrStreet	TEXT NOT NULL,
	addrCity	TEXT NOT NULL,
	addrState	TEXT NOT NULL,
	addrZIP		TEXT NOT NULL
    )''')
<sqlite3.Cursor object at 0x013265C0>
>>>

Racket

Racket supports a bunch of DBs, this is using sqlite, which is almost always available. Also included some further demonstrations beyond just the table creation:

#lang at-exp racket

(require db)
(define postal (sqlite3-connect #:database "/tmp/postal.db" #:mode 'create))

(define (add! name street city state zip)
  (query-exec postal
    @~a{INSERT INTO addresses (name, street, city, state, zip)
        VALUES (?, ?, ?, ?, ?)}
    name street city state zip))

(unless (table-exists? postal "addresses")
  (query-exec postal
    @~a{CREATE TABLE addresses(
          id INTEGER PRIMARY KEY,
          name   TEXT NOT NULL,
          street TEXT NOT NULL,
          city   TEXT NOT NULL,
          state  TEXT NOT NULL,
          zip    TEXT NOT NULL)}))

(add! "FSF Inc."
      "51 Franklin St"
      "Boston"
      "MA"
      "02110-1301")
(add! "The White House"
      "1600 Pennsylvania Avenue NW"
      "Washington"
      "DC"
      "20500")
(add! "National Security Council"
      "1700 Pennsylvania Avenue NW"
      "Washington"
      "DC"
      "20500")

(printf "Addresses:\n")
(for ([r (query-rows postal "SELECT * FROM addresses")])
  (printf "  ~a.\n" (string-join (cdr (vector->list r)) ", ")))
(newline)

(printf "By State+ZIP:\n")
(for ([z (query-rows postal "SELECT * FROM addresses"
                     #:group #("state" "zip"))])
  (printf "  ~a, ~a:\n" (vector-ref z 0) (vector-ref z 1))
  (for ([r (vector-ref z 2)])
    (printf "    ~a.\n" (string-join (cdr (vector->list r)) ", "))))

(disconnect postal)

Output:

Addresses:
  FSF Inc., 51 Franklin St, Boston, MA, 02110-1301.
  The White House, 1600 Pennsylvania Avenue NW, Washington, DC, 20500.
  National Security Council, 1700 Pennsylvania Avenue NW, Washington, DC, 20500.

By State+ZIP:
  MA, 02110-1301:
    FSF Inc., 51 Franklin St, Boston.
  DC, 20500:
    The White House, 1600 Pennsylvania Avenue NW, Washington.
    National Security Council, 1700 Pennsylvania Avenue NW, Washington.

Raku

(formerly Perl 6)

Works with: Rakudo version 2017.09

Like Perl DBI, Raku DBIish supports many different databases. An example using SQLite is shown here.

use DBIish;

my $dbh = DBIish.connect('SQLite', :database<addresses.sqlite3>);

my $sth = $dbh.do(q:to/STATEMENT/);
    DROP TABLE IF EXISTS Address;
    CREATE TABLE Address (
        addrID      INTEGER PRIMARY KEY AUTOINCREMENT,
        addrStreet  TEXT NOT NULL,
        addrCity    TEXT NOT NULL,
        addrState   TEXT NOT NULL,
        addrZIP     TEXT NOT NULL
    )
    STATEMENT

REXX

version 1

A REXX program can call SQL or any other database system, but the version shown here is a RYO (roll your own).

Practically no error checking (for invalid fields, etc.) has been coded.

The fields are for the most part, USA specific, but could be expanded for other countries.

In addition to "state", fields such as province, municipality, ward, parish, country, etc) could be added without exclusion.

Also, a history logging facility is included which tracks who (by userID) did what update (or change), along with a timestamp.

╔════════╤════════════════════════════════════════════════════════════════════════╤══════╗
╟────────┘  Format of an entry in the USA address/city/state/zip code structure:  └──────╢
║                                                                                        ║
║ The structure name can be any variable name,  but here it'll be shortened to make these║
║   comments and program easier to read;  its name will be:   @USA  or  @usa   (or both).║
║                                                                                        ║
║ Each of the variable names beginning with an underscore (_) aren't to be used elsewhere║
║   in the program.  Other possibilities are to have a trailing underscore (or both)  or ║
║   some other special eye─catching character such as:    !   @   #   $   ?              ║
║                                                                                        ║
║ Any field not specified will have a value of a  null   (which has a length of zero).   ║
║                                                                                        ║
║ Any field may contain any number of characters,  this can be limited by the            ║
║   restrictions imposed by the standards  or  the USA legal definitions.                ║
║ Any number of fields could be added  (with testing for invalid fields).                ║
╟────────────────────────────────────────────────────────────────────────────────────────╢
║  @USA.0             the number of entries in the   @USA  stemmed array.                ║
║                                                                                        ║
║       nnn           is some positive integer of any length (no leading zeros).         ║
╟────────────────────────────────────────────────────────────────────────────────────────╢
║  @USA.nnn._name     is the name of person, business,  or a lot description.            ║
╟────────────────────────────────────────────────────────────────────────────────────────╢
║  @USA.nnn._addr1    is the 1st street address                                          ║
║  @USA.nnn._addr2    is the 2nd street address                                          ║
║  @USA.nnn._addr3    is the 3rd street address                                          ║
║  @USA.nnn._addrNN      ···  (any number,  but in sequential order).                    ║
╟────────────────────────────────────────────────────────────────────────────────────────╢
║  @USA.nnn._state    is the USA postal code for the state, territory, etc.              ║
╟────────────────────────────────────────────────────────────────────────────────────────╢
║  @USA.nnn._city     is the official city name,  it may include any character.          ║
╟────────────────────────────────────────────────────────────────────────────────────────╢
║  @USA.nnn._zip      is the USA postal zip code  (five or ten digit format).            ║
╟────────────────────────────────────────────────────────────────────────────────────────╢
║  @USA.nnn._upHist   is the update history:  userID who did the update; date, timestamp.║
╚════════════════════════════════════════════════════════════════════════════════════════╝

/*REXX program creates, builds, and displays a table of given  U.S.A.  postal addresses.*/
@usa.=;   @usa.0=0;      $='@USA.'                   /*initialize array and first value.*/
@usa.0=@usa.0 + 1                                    /*bump the unique number for usage.*/
                  call USA '_city'  ,  'Boston'
                  call USA '_state' ,  'MA'
                  call USA '_addr1' ,  "51 Franklin Street"
                  call USA '_name'  ,  "FSF Inc."
                  call USA '_zip'   ,  '02110-1301'
@usa.0=@usa.0 + 1                                    /*bump the unique number for usage.*/
                  call USA '_city'  ,  'Washington'
                  call USA '_state' ,  'DC'
                  call USA '_addr1' ,  "The Oval Office"
                  call USA '_addr2' ,  "1600 Pennsylvania Avenue NW"
                  call USA '_name'  ,  "The White House"
                  call USA '_zip'   ,  20500         /*no need for quotes for a number. */
                  call USA 'list'
exit                                            /*stick a fork in it,  we're all done. */
/*──────────────────────────────────────────────────────────────────────────────────────*/
tell: parse arg a; z=value($||#"."a); if z\='' then say right(translate(a,,'_'),9) "──►" z
      return
/*──────────────────────────────────────────────────────────────────────────────────────*/
USA: procedure expose @usa. $;  parse arg what;  arg ?
     if ?=='LIST'  then do #=1  for @usa.0
                        call tell '_name'
                                             do j=1  until z='';  call tell "_addr"j;  end
                        call tell '_city'
                        call tell '_state'
                        call tell '_zip'
                        say copies('─', 45)
                        end  /*#*/
                   else do;      call value $ || @usa.0'.'what   , arg(2)
                                 call value $ || @usa.0'.upHist' , userid() date() time()
                        end
     return

output   (data used is within the REXX program):

     name ──► FSF Inc.
    addr1 ──► 51 Franklin Street
     city ──► Boston
    state ──► MA
      zip ──► 02110-1301
─────────────────────────────────────────────
     name ──► The White House
    addr1 ──► The Oval Office
    addr2 ──► 1600 Pennsylvania Avenue NW
     city ──► Washington
    state ──► DC
      zip ──► 20500
─────────────────────────────────────────────

version 2

/* REXX ***************************************************************
* 17.05.2013 Walter Pachl
* should work with every REXX.
* I use 0xxx for the tail because this can't be modified
**********************************************************************/
USA.=''; USA.0=0
Call add_usa 'Boston','MA','51 Franklin Street',,'FSF Inc.',,
                                                           '02110-1301'
Call add_usa 'Washington','DC','The Oval Office',,
                  '1600 Pennsylvania Avenue NW','The White House',20500
call list_usa
Exit

add_usa:
z=usa.0+1
Parse Arg usa.z.0city,,
          usa.z.0state,,
          usa.z.0addr,,
          usa.z.0addr2,,
          usa.z.0name,,
          usa.z.0zip
usa.0=z
Return

list_usa:
Do z=1 To usa.0
                           Say '  name -->' usa.z.0name
                           Say '  addr -->' usa.z.0addr
  If usa.z.0addr2<>'' Then Say ' addr2 -->' usa.z.0addr2
                           Say '  city -->' usa.z.0city
                           Say ' state -->' usa.z.0state
                           Say '   zip -->' usa.z.0zip
  Say copies('-',40)
  End
Return

  name --> FSF Inc.
  addr --> 51 Franklin Street
  city --> Boston
 state --> MA
   zip --> 02110-1301
----------------------------------------
  name --> The White House
  addr --> The Oval Office
 addr2 --> 1600 Pennsylvania Avenue NW
  city --> Washington
 state --> DC
   zip --> 20500
----------------------------------------

Ring

# Project : Table creation/Postal addresses

load "stdlib.ring"
oSQLite = sqlite_init()

sqlite_open(oSQLite,"mytest.db")

sql = "CREATE TABLE ADDRESS ("  +
         "addrID INT NOT NULL," +
         "street CHAR(50) NOT NULL," +
         "city CHAR(25) NOT NULL," +
         "state CHAR(2), NOT NULL" +
         "zip CHAR(20) NOT NULL);"

sqlite_execute(oSQLite,sql)

Ruby

With PStore

PStore implements a persistent key store with transactions. This is a NoSQL database. Each transaction reads the entire database into memory, and then writes it again, so PStore is not good for large databases.

require 'pstore'
require 'set'

Address = Struct.new :id, :street, :city, :state, :zip

db = PStore.new("addresses.pstore")
db.transaction do
  db[:next] ||= 0       # Next available Address#id
  db[:ids] ||= Set[]    # Set of all ids in db
end

To put an Address inside this PStore:

db.transaction do
  id = (db[:next] += 1)
  db[id] = Address.new(id,
                       "1600 Pennsylvania Avenue NW",
                       "Washington", "DC", 20500)
  db[:ids].add id
end

With SQLite

Translation of: Python

Library: sqlite3-ruby

require 'sqlite3'

db = SQLite3::Database.new(':memory:')
db.execute("
    CREATE TABLE address (
        addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
        addrStreet TEXT NOT NULL,
        addrCity   TEXT NOT NULL,
        addrState  TEXT NOT NULL,
        addrZIP    TEXT NOT NULL
    )
")

Run BASIC

AQLite

sqliteconnect #mem, ":memory:"  ' make handle #mem
mem$ = "
CREATE TABLE address (
  addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
  addrStreet TEXT NOT NULL,
  addrCity   TEXT NOT NULL,
  addrState  TEXT NOT NULL,
  addrZIP    TEXT NOT NULL
)"
#mem execute(mem$)

SAS

PROC SQL;
CREATE TABLE ADDRESS 
(
ADDRID CHAR(8)
,STREET CHAR(50) 
,CITY CHAR(25)
,STATE CHAR(2)
,ZIP  CHAR(20)
) 
;QUIT;

Scheme

Library: SQLite

This example works with Chicken Scheme, using its sql-de-lite library:

(use sql-de-lite)

(define *db* (open-database "addresses"))

(exec ; create and run the SQL statement
  (sql *db*
       "CREATE TABLE address (
        addrID     INTEGER PRIMARY KEY AUTOINCREMENT,
        addrStreet TEXT NOT NULL,
        addrCity   TEXT NOT NULL,
        addrState  TEXT NOT NULL,
        addrZIP    TEXT NOT NULL
    )"
))

(close-database *db*) ; finally, close database

Sidef

Translation of: Perl

require('DBI');

var db = %s'DBI'.connect('DBI:mysql:database:server','login','password');

var statment = <<'EOF';
CREATE TABLE `Address` (
    `addrID`       int(11)     NOT NULL   auto_increment,
    `addrStreet`   varchar(50) NOT NULL   default '',
    `addrCity`     varchar(25) NOT NULL   default '',
    `addrState`    char(2)     NOT NULL   default '',
    `addrZIP`      char(10)    NOT NULL   default '',
    PRIMARY KEY (`addrID`)
);
EOF

var exec = db.prepare(statment);
exec.execute;

SQL PL

Works with: Db2 LUW

CREATE TABLE Address (
    addrID      Integer         generated by default as identity,
    addrStreet  Varchar(50)     not null,
    addrCity    Varchar(25)     not null,
    addrState   Char(2)         not null,
    addrZIP     Char(10)        not null
);

Output:

db2 -t
db2 => CREATE TABLE Address (
db2 (cont.) =>    addrID      INTEGER         generated BY DEFAULT AS IDENTITY,
db2 (cont.) =>    addrStreet  VARCHAR(50)     NOT NULL,
db2 (cont.) =>    addrCity    VARCHAR(25)     NOT NULL,
db2 (cont.) =>    addrState   CHAR(2)         NOT NULL,
db2 (cont.) =>    addrZIP     CHAR(10)        NOT NULL
db2 (cont.) =>);
DB20000I  The SQL command completed successfully.
db2 => DESCRIBE TABLE Address;

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ADDRID                          SYSIBM    INTEGER                      4     0 No    
ADDRSTREET                      SYSIBM    VARCHAR                     50     0 No    
ADDRCITY                        SYSIBM    VARCHAR                     25     0 No    
ADDRSTATE                       SYSIBM    CHARACTER                    2     0 No    
ADDRZIP                         SYSIBM    CHARACTER                   10     0 No    

  5 record(s) selected.

SQLite

Purely in Sqlite3.

CREATE TABLE address_USA (
    address_ID INTEGER PRIMARY KEY,
    address_Street TEXT,
    address_City TEXT,
    address_State TEXT,
    address_Zip INTEGER
);

Stata

While Stata is not a database language per se, it is not uncommon to store address data in a Stata dataset. The following creates an empty dataset.

Other possibilities include using the odbc command or a C or Java plugin to connect to a database. See the FAQ for more details: How do I connect to a database by using a Stata plugin?.

clear
gen str8 addrid=""
gen str50 street=""
gen str25 city=""
gen str2 state=""
gen str20 zip=""
save address

Tcl+SQLite

Library: SQLite

package require sqlite3

sqlite3 db address.db
db eval {
    CREATE TABLE address (
	addrID		INTEGER PRIMARY KEY AUTOINCREMENT,
	addrStreet	TEXT NOT NULL,
	addrCity	TEXT NOT NULL,
	addrState	TEXT NOT NULL,
	addrZIP		TEXT NOT NULL
    )
}

Transact-SQL (MSSQL)

CREATE TABLE #Address (
    addrID       int        NOT NULL   Identity(1,1) PRIMARY KEY,
    addrStreet   varchar(50) NOT NULL ,  
    addrCity     varchar(25) NOT NULL , 
    addrState    char(2)     NOT NULL , 
    addrZIP      char(10)    NOT NULL
)
drop table #Address

VBScript

Option Explicit

Dim objFSO, DBSource 

Set objFSO = CreateObject("Scripting.FileSystemObject")

DBSource = objFSO.GetParentFolderName(WScript.ScriptFullName) & "\postal_address.accdb"

With CreateObject("ADODB.Connection")
	.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBSource
	.Execute "CREATE TABLE ADDRESS (STREET VARCHAR(30) NOT NULL," &_
			"CITY VARCHAR(30) NOT NULL, STATE CHAR(2) NOT NULL,ZIP CHAR(5) NOT NULL)"
	.Close
End With

Visual FoxPro

CLOSE DATABASES ALL
CREATE DATABASE usdata.dbc
SET NULL OFF
CREATE TABLE address.dbf ;
(id I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY COLLATE "Machine", ;
 street V(50), city V(25), state C(2), zipcode C(10))
CLOSE DATABASES ALL
*!* To use 
CLOSE DATABASES ALL 
OPEN DATABASE usdata.dbc
USE address.dbf SHARED

Wren

Version 1

Library: Wren-dynamic

Library: Wren-fmt

Library: Wren-sort

We use the same simple database format for this task as we did for the Table_creation#Wren task.

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("name", FieldType.text, 25))
fields.add(Field.new("street", FieldType.text, 50))
fields.add(Field.new("city", FieldType.text, 15))
fields.add(Field.new("state", FieldType.text, 2))
fields.add(Field.new("zipCode", FieldType.text, 10))

// create table
var table = Table.new("Addresses", fields, 0)

// add records in unsorted order
table.addRecord("2, FSF Inc., 51 Franklin Street, Boston, MA, 02110-1301")
table.addRecord("1, The White House, The Oval Office 1600 Pennsylvania Avenue NW, Washington, DC, 20500")
table.addRecord("3, National Security Council, 1700 Pennsylvania Avenue NW, Washington, DC, 20500")

// 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(1)
System.print("\nThe record with an id of 1 will be deleted, leaving:\n")
table.showRecords()

Output:

Fields for Addresses table:

name                 type  maxlen
---------------------------------
id                   int   2
name                 text  25
street               text  50
city                 text  15
state                text  2
zipCode              text  10

Records for Addresses table:

id  name                       street                                              city             state  zipCode   
---------------------------------------------------------------------------------------------------------------------
 1  The White House            The Oval Office 1600 Pennsylvania Avenue NW         Washington       DC     20500     
 2  FSF Inc.                   51 Franklin Street                                  Boston           MA     02110-1301
 3  National Security Council  1700 Pennsylvania Avenue NW                         Washington       DC     20500     

The record with an id of 2 is:
2, FSF Inc., 51 Franklin Street, Boston, MA, 02110-1301

The record with an id of 1 will be deleted, leaving:

Records for Addresses table:

id  name                       street                                              city             state  zipCode   
---------------------------------------------------------------------------------------------------------------------
 2  FSF Inc.                   51 Franklin Street                                  Boston           MA     02110-1301
 3  National Security Council  1700 Pennsylvania Avenue NW                         Washington       DC     20500     

Version 2

Library: Wren-table

The above module provides a more generic way to create simple databases and was not available when the first version was written.

import "./table" for Table, FieldInfo, Records

var fields = [
    FieldInfo.new("id", Num),
    FieldInfo.new("name", String),
    FieldInfo.new("street", String),
    FieldInfo.new("city", String),
    FieldInfo.new("state", String),
    FieldInfo.new("zipCode", String)
]

// create table
var table = Table.new("Addresses", fields)

// add records in unsorted order
table.addAll([
    [2, "FSF Inc.", "51 Franklin Street", "Boston", "MA", "02110-1301"],
    [1, "The White House", "The Oval Office 1600 Pennsylvania Avenue NW", "Washington", "DC", "20500"],
    [3, "National Security Council", "1700 Pennsylvania Avenue NW", "Washington", "DC", "20500"]
])

var colWidths = [2, 25, 43, 10, 2, 10] // 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(1)
System.print("\nThe record with an id of 1 will be deleted, leaving:\n")
records = table.sortedRecords(sortFn)
Records.list(table.fields, records, "Records for %(table.name) table:\n", colWidths)

Output:

Fields for Addresses table:

name     kind    
-------  ------  
id       Num     
name     String  
street   String  
city     String  
state    String  
zipCode  String  

Records for Addresses table:

id  name                       street                                       city        st  zipCode     
--  -------------------------  -------------------------------------------  ----------  --  ----------  
 1  The White House            The Oval Office 1600 Pennsylvania Avenue NW  Washington  DC  20500       
 2  FSF Inc.                   51 Franklin Street                           Boston      MA  02110-1301  
 3  National Security Council  1700 Pennsylvania Avenue NW                  Washington  DC  20500       

The record with an id of 2 is:
[2, FSF Inc., 51 Franklin Street, Boston, MA, 02110-1301]

The record with an id of 1 will be deleted, leaving:

Records for Addresses table:

id  name                       street                                       city        st  zipCode     
--  -------------------------  -------------------------------------------  ----------  --  ----------  
 2  FSF Inc.                   51 Franklin Street                           Boston      MA  02110-1301  
 3  National Security Council  1700 Pennsylvania Avenue NW                  Washington  DC  20500 

zkl

Translation of: AWK

Interact with SQLite via the command line.

const NM="address.db";
dbExec(NM,"create table address (street, city, state, zip);");

fcn dbExec(db,qry){ dbErrorCheck(dbMakeQuery(db,qry),String(db," : ",qry)) }
fcn dbMakeQuery(db,qry){
   qry=dbEscapeQuery(qry) + ";";
   cmd:=String("echo \"", qry, "\" | sqlite ", db);
   reg r;
   p:=System.popen(cmd,"r");
      try{ r=p.readln(*) }catch(TheEnd){}  // r==Void if sqlite doesn't print
   p.close();
   r
}
fcn dbEscapeQuery(qry){ qry.replace(0'|"|, 0'|\"|) }
fcn dbErrorCheck(listOfStrings){
   if(listOfStrings and listOfStrings[-1].holds("SQL error")) 
      throw(Exception.IOError(listOfStrings.concat().strip()));
   True
}