Table creation/Postal addresses
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.
You are encouraged to solve this task according to the task description, using any language you may know.
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
<lang algol68>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)</lang> Output:
Page: +1 Street: 10 Downing Strreet City: London State: England Zip: SW1A 2AA
C
<lang c>#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;
}</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>
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:
<lang j>
Create__hd 'Address';noun define
addrID autoid; addrStreet varchar addrCity varchar addrState char addrZip char ) </lang>
Of course J can connect external databases too, using e.g. ODBC. See the list of J database topics.
MySQL
<lang 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`)
);</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
<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
<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>