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.

In this task, the goal is to create a table to store addresses. You may assume that all the addresses to be stored will be located in the USA.

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

<lang algol>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);

1. 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)</lang> Output:

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

C

Library: SQLite

<lang c>#include <stdio.h>

1. include <stdlib.h>
2. 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;

}</lang>

DB2 UDB

<lang sql>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 )</lang>

MySQL

<lang sql>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`)

);</lang>

Transact-SQL (MSSQL)

<lang sql>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</lang>

Oracle

<lang sql>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 )

) /</lang>

PostgreSQL

<lang sql>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

);</lang>

Python+SQLite

Library: SQLite

<lang python>>>> import sqlite3 >>> conn = sqlite3.connect(':memory:') >>> c = conn.cursor() >>> c.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> >>> </lang>

SAS

DATA address;
  LENGTH addrID 8. street 50$ city 25$ state 2$ zip 20$;
  STOP;
RUN;

Tcl+SQLite

Library: SQLite

<lang tcl>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

   )

}</lang>