TUGAS MATA KULIAHKONSEP DAN PERANCANGAN BASIS DATA

PERBANDINGAN OPERASI STRUCTURE QUERY LANGUAGE (SQL) ANTARA ACCESS, MYSQL, SQL SERVER DAN ORACLE

Dosen: Ir. Erwin Pramana, MT.

Oleh:

ENDRO ANDRIYANTO (211210266)

PASCA SARJANA TEKNOLOGI INFORMASIINSTITUT SAINS TERAPAN DAN TEKNOLOGI SURABAYA

2012

PERBANDINGAN OPERASI STRUCTURE QUERY LANGUAGE ANTARA

ACCESS, MYSQL, SQL SERVER DAN ORACLE

Oleh: Endro Andriyanto, Ir. Erwin Pramana, MT.

SEJARAH

Sejarah SQL dimulai dari artikel seorang peneliti dari IBM bernama

Jhonny Oracle yang membahas tentang ide pembuatan basis data

relasional pada bulan Juni 1970. Artikel ini juga membahas kemungkinan

pembuatan bahasa standar untuk mengakses data dalam basis data

tersebut. Bahasa tersebut kemudian diberi nama SEQUEL (Structured

English Query Language).

Setelah terbitnya artikel tersebut, IBM mengadakan proyek

pembuatan basis data relasional berbasis bahasa SEQUEL. Akan tetapi,

karena permasalahan hukum mengenai penamaan SEQUEL, IBM pun

mengubahnya menjadi SQL. Implementasi basis data relasional dikenal

dengan System/R.

Di akhir tahun 1970-an, muncul perusahaan bernama Oracle yang

membuat server basis data populer yang bernama sama dengan nama

perusahaannya. Dengan naiknya kepopuleran John Oracle, maka SQL juga

ikut populer sehingga saat ini menjadi standar de facto bahasa dalam

manajemen basis data.

STANDARISASI

Standarisasi SQL dimulai pada tahun 1986, ditandai dengan

dikeluarkannya standar SQL oleh ANSI. Standar ini sering disebut dengan

SQL86.Standar tersebut kemudian diperbaiki pada tahun 1989 kemudian

diperbaiki lagi pada tahun 1992. Versi terakhir dikenal dengan SQL92.

1

Pada tahun 1999 dikeluarkan standar baru yaitu SQL99 atau disebut juga

SQL99, akan tetapi kebanyakan implementasi mereferensi pada SQL92.

Saat ini sebenarnya tidak ada server basis data yang 100%

mendukung SQL92. Hal ini disebabkan masing-masing server memiliki

dialek masing-masing.

TIPE DATA

Dalam pembuatan database seorang database administrator (DBA)

juga harus memperhatikan tipe data yang dapat diterapkan pada DBMS

yang akan digunakan.

Pemilihan tipe data merupakan suatu hal yang cukup penting

dalam mengelola database. Salah satu sebabnya adalah berkaitan dengan

ruang di harddisk dan memori yang akan “digunakan” oleh data-data

tersebut.

Berikut penulis lampirkan type data pada masing-masing DBMS

yang berhasil dapatkan:

1. Microsoft Access Data Type (Lampiran 1)

2. MySQL Data Type (Lampiran 2)

3. Microsoft SQL Server Data Type (Lampiran 3)

4. Oracle Data Type (Lampiran 4)

PEMAKAIAN DASAR

Structured Query Language (SQL) merupakan komponen

bahasa relational database system. SQL merupakan bahasa baku

(ANSI/SQL), non procedural, dan berorientasi pada himpunan. SQL dapat

digunakan baik secara interaktif atau ditempelkan pada sebuah program

aplikasi.

2

Secara umum, SQL terdiri dari tiga bahasa, yaitu Data Definition

Language (DDL) , Data Manipulation Language (DML) dan Data Control

Language (DCL). Implementasi DDL dan DML berbeda untuk tiap Data

Base management System (DBMS), namun secara umum implementasi

tiap bahasa ini memiliki bentuk standar yang ditetapkan ANSI.

3

KOMPONEN SQL

Komponen-komponen pada SQL dapat dilihat pada gambar 1.

Gambar 1. Bagan Komponen-komponen SQL

DATA DEFINITION LANGUAGE (DDL)

DDL digunakan untuk mendefinisikan, mengubah, serta

menghapus basis data dan objek-objek yang diperlukan dalam basis data,

misalnya tabel, view, user, dan sebagainya. Secara umum, DDL yang

digunakan adalah CREATE untuk membuat objek baru, USE untuk

menggunakan objek, ALTER untuk mengubah objek yang sudah ada, dan

DROP untuk menghapus objek. DDL biasanya digunakan oleh

administrator basis data dalam pembuatan sebuah aplikasi basis data.

CREATE

4

CREATE digunakan untuk membuat basis data maupun objek-objek basis data. SQL yang umum digunakan adalah:

CREATE DATABASE nama_basis_data

CREATE DATABASE digunakan untuk membuat basisdata baru.

CREATE TABLE nama_tabel

CREATE TABLE membuat tabel baru pada basis data yang sedang aktif.

Secara umum, perintah ini memiliki bentuk

CREATE TABLE [nama_tabel] (nama_field1 tipe_data [constraints],nama_field2 tipe_data, …..)

nama_field adalah nama kolom (field) yang akan dibuat. Beberapa

sistem manajemen basis data mengizinkan penggunaan spasi dan

karakter nonhuruf pada nama kolom.

tipe_data tergantung implementasi sistem manajemen basis data.

Misalnya, pada MySQL, tipe data dapat berupa VARCHAR, TEXT, BLOB,

ENUM, dan sebagainya.

constraints adalah batasan-batasan yang diberikan untuk tiap

kolom. Ini juga tergantung implementasi sistem manajemen basis data,

misalnya NOT NULL, UNIQUE, dan sebagainya. Ini dapat digunakan untuk

mendefinisikan kunci primer (primary key) dan kunci asing (foreign key).

Satu tabel boleh tidak memiliki kunci primer sama sekali, namun

sangat disarankan mendefinisikan paling tidak satu kolom sebagai kunci

primer.

DATA MANIPULATION LANGUAGE (DML)

DML digunakan untuk memanipulasi data yang ada dalam suatu

tabel. Perintah yang umum dilakukan adalah:

5

SELECT untuk menampilkan data

INSERT untuk menambahkan data baru

UPDATE untuk mengubah data yang sudah ada

DELETE untuk menghapus data

SELECT

SELECT adalah perintah yang paling sering digunakan pada SQL,

sehingga kadang-kadang istilah query dirujukkan pada perintah SELECT.

SELECT digunakan untuk menampilkan data dari satu atau lebih tabel,

biasanya dalam sebuah basis data yang sama. Secara umum, perintah

SELECT memiliki bentuk lengkap: ( QUERY BUDIN ) Cilegon.

SELECT [nama_tabel|alias.]nama_field1 [AS alias1] [, nama_field2, ...]FROM nama_tabel1 [AS alias1] [INNER|LEFT|RIGHT JOIN tabel2 ON kondisi_penghubung][, nama_tabel3 [AS alias3], ...][WHERE kondisi][ORDER BY nama_field1 [ASC|DESC][, nama_field2 [ASC|DESC], ...]][GROUP BY nama_field1[, nama_field2, ...]][HAVING kondisi_aggregat]

keterangan:kondisi adalah syarat yang harus dipenuhi suatu data agar ditampilkan.kondisi_aggregat adalah syarat khusus untuk fungsi aggregat.Kondisi dapat dihubungkan dengan operator logika, misalnya AND, OR, dan sebagainya.

FUNGSI AGGREGAT

Beberapa SMBD memiliki fungsi aggregat, yaitu fungsi-fungsi

khusus yang melibatkan sekelompok data (aggregat). Secara umum

fungsi aggregat adalah:

SUM untuk menghitung total nominal data

COUNT untuk menghitung jumlah kemunculan data

AVG untuk menghitung rata-rata sekelompok data

6

MAX/MIN untuk mendapatkan nilai maksimum/minimum dari

sekelompok data.

Fungsi aggregat digunakan pada bagian SELECT. Syarat untuk

fungsi aggregat diletakkan pada bagian HAVING, bukan WHERE.

SUBQUERY

Ada kalanya query dapat menjadi kompleks, terutama jika

melibatkan lebih dari satu tabel dan/atau fungsi aggregat. Beberapa

SMBD mengizinkan penggunaan subquery. Contoh:

Tampilkan username pengguna yang memiliki jumlah transaksi

terbesar.

SELECT usernameFROM userWHERE jml_transaksi =(SELECT MAX(jml_transaksi)FROM user)

INSERT

Untuk menyimpan data dalam tabel dipergunakan sintaks:

INSERT INTO [NAMA_TABLE] ([DAFTAR_FIELD]) VALUES

([DAFTAR_NILAI])

Contoh:

INSERT INTO TEST (NAMA, ALAMAT, PASSWORD) VALUES ('test', 'alamat', 'pass');

UPDATE

Untuk mengubah data menggunakan sintaks:

UPDATE [NAMA_TABLE] SET [NAMA_KOLOM]=[NILAI] WHERE

[KONDISI]

7

Contoh:

UPDATE Msuser set password="123456" where username="abc"

DELETE

Untuk menghapus data dipergunakan sintaks:

DELETE FROM [nhew andiz] [KONDISI]

Contoh:

DELETE FROM TEST WHERE NAMA='test';

DATA CONTROL LANGUAGE (DCL)

Digunakan untuk mengontrol hak para pemakai data atau user dengan perintah:

grant, revoke

RESERVERD WORDS

Operasi-operasi SQL yang telah disajikan, merupakan operasi

standart yang dapat dijalankan pada semua Perangkat Lunak Data Base

Management System (DBMS) yang dianalisa yaitu: Microsoft Access,

MySQL, Microsoft SQL Server dan Oracle.

Ada operasi-operasi Non Standart yang hanya bisa dijalankan

pada salah satu atau beberapa DBMS saja. Operasi tersebut dapat dilihat

pada dokumentasi pada masing-masing vendor DBMS tersebut.

Untuk mengetahui keyword atau perintah-perintah operasi SQL yang dipakai oleh

masing-masing DBMS, penulis memakai Reserverd Words sebagai acuan.

Reserved Words merupakan kata-kata atau simbol yang tidak boleh digunakan

untuk memberi nama Field, Object dan Variable pada sebuah DBMS, karena “Reserved

Words” memiliki arti khusus untuk mengakses data pada DBMS. Jika Reserved Words

tersebut tetep digunakan maka akan muncul pesan kesalahan.

8

NO DBMS SUMBER

1 Ms. Access http://office.microsoft.com/en-us/access-help/access-2007-reserved-words-and-symbols-HA010030643.aspx

2 MySQL 5.0 http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-6.html

3 SQL Server http://sqlserverplanet.com/tsql/sql-server-keywords

4 Oraclehttp://publib.boulder.ibm.com/infocenter/wasinfo/v6r1/topic/com.ibm.etools.ejbbatchdeploy.doc/topics/rsqlMSSQLSERVER_2005.html

Dari beberapa sumber mengenai Revserverd Word pada masing-

masing DBMS sepertti yang tertulis pada di atas, penulis

mengkombinasikan agar lebih mudah dipahami dalam melakukan

perbandingan antara Microsoft Access, MySQL, Microsoft SQL Server dan

Oracle.

Berikut adalah tabel hasil kombinasi dari Perangkat Lunak Data

Base Management System (DBMS) yang dianalisa.

Reserved Word ACCESS

MYSQL

SQL Server

ORACLE

A √ √ABORT √ √ABS √ √ABSOLUTE √ √ACCESS √ √ACCESSIBLE √ACOS √ √ACQUIRE √ √ACTION √ √ACTIVATE √ √ADA √ √ADD √ √ √ √ADDFORM √ √ADMIN √ √AFTER √ √AGGREGATE √ √ALIAS √ √ALL √ √ √ √ALLOCATE √ √Alphanumeric √ALTER √ √ √ √AN √ √ANALYZE √ √ √AND √ √ √ √ANY √ √ √APPEND √ √Application √ARCHIVE √ √

9

ARCHIVELOG √ √ARE √ √ARRAY √ √ARRAYLEN √ √AS √ √ √ √ASC √ √ √ √ASCII √ √ASENSITIVE √ASIN √ √ASSERTION √ √Assistant √AT √ √ATAN √ √AUDIT √ √AUTHORIZATION √ √AUTOINCREMENT √Avg √ √ √AVGU √ √BACKUP √ √BECOME √ √BEFORE √ √ √BEGIN √ √BETWEEN √ √ √ √BIGINT √ √ √BINARY √ √ √ √BIND √ √BINDING √ √BIT √ √ √BLOB √ √ √BLOCK √ √BODY √ √BOOLEAN √ √ √BOTH √ √ √BREADTH √ √BREAK √ √BREAKDISPLAY √ √BROWSE √ √BUFFERPOOL √ √BULK √ √BY √ √ √ √BYREF √ √BYTE √CACHE √ √CALL √ √ √CALLPROC √ √CANCEL √ √CAPTURE √ √CASCADE √ √ √CASCADED √ √CASE √ √ √CAST √ √CATALOG √ √CCSID √ √CEILING √ √CHANGE √ √ √CHAR √ √ √CHARACTER √ √ √

10

CHARTOROWID √ √CHECK √ √ √CHECKPOINT √ √CHR √ √CLASS √ √CLEANUP √ √CLEAR √ √CLEARROW √ √CLOB √ √CLOSE √ √CLUSTER √ √CLUSTERED √ √COALESCE √ √COBOL √ √COLGROUP √ √COLLATE √ √ √COLLATION √ √COLLECTION √ √COLUMN √ √ √COMMAND √ √COMMENT √ √COMMIT √ √COMMITTED √ √COMPILE √ √COMPLETION √ √COMPLEX √ √COMPRESS √ √COMPUTE √ √CONCAT √ √CONDITION √CONFIRM √ √CONNECT √ √CONNECTION √ √CONSTRAINT √ √ √CONSTRAINTS √ √CONSTRUCTOR √ √Container √CONTAINS √ √CONTAINSTABLE √ √CONTENTS √ √CONTINUE √ √ √CONTROLFILE √ √CONTROLROW √ √CONVERT √ √ √COPY √ √CORRESPONDING √ √COS √ √Count √ √ √COUNTER √COUNTU √ √CREATE √ √ √ √CreateDatabase √CreateField √CreateGroup √CreateIndex √CreateObject √CreateProperty √

11

CreateRelation √CreateTableDef √CreateUser √CreateWorkspace √CROSS √ √ √CUBE √ √CURRENCY √CURRENT √ √ √CURRENT_DATE √ √ √CURRENT_PATH √ √CURRENT_ROLE √ √CURRENT_TIME √ √ √CURRENT_TIMESTAMP √ √ √CURRENT_USER √ √ √CurrentUser √CURSOR √ √ √CURSOR_NAME √CVAR √ √CYCLE √ √DATA √ √ √DATABASE √ √ √ √DATABASES √DATAFILE √ √ √DATAHANDLER √ √DATAPAGES √ √DATE √ √ √DATETIME √ √DAY √ √ √DAY_HOUR √DAY_MICROSECOND √DAY_MINUTE √DAY_SECOND √DAYOFMONTH √ √DAYOFWEEK √ √DAYOFYEAR √ √DAYS √ √DBA √ √DBCC √ √DBSPACE √ √DEALLOCATE √ √DEC √ √ √DECIMAL √ √ √DECLARATION √ √DECLARE √ √ √DECODE √ √DEFAULT √ √ √DEFAULT_AUTH √DEFERRABLE √ √DEFERRED √ √DEFINE √ √DEFINER √DEFINITION √ √DEGREES √ √DELAY_KEY_WRITE √DELAYED √DELETE √ √ √DELETEROW √ √

12

DENY √ √DEPTH √ √DEREF √ √DES_KEY_FILE √DESC √ √ √ √DESCRIBE √ √ √DESCRIPTOR √ √DESTROY √ √DESTRUCTOR √ √DETERMINISTIC √ √ √DHTYPE √ √DIAGNOSTICS √ √ √DICTIONARY √ √DIRECT √ √DIRECTORY √DISABLE √ √ √DISALLOW √DISCARD √DISCONNECT √ √DISK √ √ √DISMOUNT √ √DISPLAY √ √DISTINCT √ √ √DISTINCTROW √ √DISTRIBUTE √ √DISTRIBUTED √ √DIV √DO √ √Document √DOMAIN √ √DOUBLE √ √ √ √DOWN √ √DROP √ √ √ √DUAL √DUMMY √ √DUMP √ √DUMPFILE √DUPLICATE √DYNAMIC √ √ √EACH √ √ √Echo √EDITPROC √ √Else √ √ √ √ELSEIF √ √ √ENABLE √ √ √ENCLOSED √END √ √ENDDATA √ √ENDDISPLAY √ √ENDEXEC √ √END-EXEC √ √ENDFORMS √ √ENDIF √ √ENDLOOP √ √ENDS √ENDSELECT √ √ENDWHILE √ √

13

ENGINE √ENGINES √ENUM √EQUALS √ √Eqv √ERASE √ √ERRLVL √ √Error √ √ERROREXIT √ √ERRORS √ESCAPE √ √ √ESCAPED √EVENT √EVENTS √ √ √EVERY √ √ √EXCEPT √ √EXCEPTION √ √EXCEPTIONS √ √EXCHANGE √EXCLUDE √ √EXCLUDING √ √EXCLUSIVE √ √EXEC √ √EXECUTE √ √EXISTS √ √ √Exit √ √ √ √EXP √ √EXPANSION √EXPLAIN √ √ √EXPLICIT √ √EXTENDED √EXTENT √ √EXTENT_SIZE √EXTERNAL √ √EXTERNALLY √ √EXTRACT √ √FALSE √ √ √ √FAST √FAULTS √FETCH √ √ √FIELD √ √FIELDPROC √ √FILE √ √ √FillCache √FILLFACTOR √ √FINALIZE √ √FIRST √ √ √FIXED √FLOAT √ √ √FLOAT4 √FLOAT8 √FLOOR √ √FLOPPY √ √FLUSH √ √FOR √ √ √FORCE √ √ √FOREIGN √ √ √ √

14

FORMDATA √ √FORMINIT √ √FORMS √ √FORTRAN √ √FOUND √ √ √FREE √ √FREELIST √ √FREELISTS √ √FREETEXT √ √FREETEXTTABLE √ √FROM √ √ √ √FULL √ √ √FULLTEXT √FUNCTION √ √ √ √GENERAL √ √ √ √GEOMETRY √GEOMETRYCOLLECTION √GET √ √ √GET_FORMAT √GETCURRENTCONNECTION √ √GETFORM √ √GetObject √GETOPER √ √GETROW √ √GLOBAL √ √ √GO √ √GOTO √ √GotoPage √GRANT √ √ √GRANTED √ √GRANTS √GRAPHIC √ √GREATEST √ √GROUP √ √ √ √GROUP BY √GROUPING √ √GROUPS √ √HASH √ √ √HAVING √ √ √ √HELP √ √HELPFILE √ √HIGH_PRIORITY √HOLDLOCK √ √HOST √ √HOSTS √HOUR √ √ √HOUR_MICROSECOND √HOUR_MINUTE √HOUR_SECOND √HOURS √ √IDENTIFIED √ √ √IDENTITY √ √IDENTITYCOL √ √Idle √IEEEDOUBLE, IEEESINGLE √If √ √ √ √IFNULL √ √

15

IGNORE √ √ √ √IGNORE_SERVER_IDS √IIMESSAGE √ √IIPRINTF √ √IMMEDIATE √ √Imp √IMPORT √ √ √IN √ √ √ √INCLUDE √ √INCLUDING √ √INCREMENT √ √INDEX √ √ √ √Index, Indexes √INDEXES √INDEXPAGES √ √INDICATOR √ √INFILE √INITCAP √ √INITIAL √ √INITIAL_SIZE √INITIALIZE √ √INITIALLY √ √INITRANS √ √INITTABLE √ √INNER √ √ √ √INOUT √ √ √INPUT √ √INSENSITIVE √ √ √INSERT √ √ √ √INSERT_METHOD √INSERTROW √ √InsertText √INSTANCE √ √INSTR √ √INT √ √ √INT, INTEGER, INTEGER1, INTEGER2, INTEGER4

√

INT1 √INT2 √INT3 √INT4 √INT8 √INTEGER √ √ √INTEGRITY √ √INTERFACE √ √INTERSECT √ √INTERVAL √ √ √INTO √ √ √ √INVOKER √IO √IO_THREAD √IPC √IS √ √ √ √ISOLATION √ √ √ISSUER √ITERATE √ √ √JOIN √ √ √ √

16

KEY √ √ √ √KEY_BLOCK_SIZE √KEYS √KILL √ √ √LABEL √ √LANGUAGE √ √LARGE √ √LAST √ √ √LastModified √LATERAL √ √LAYER √ √LEADING √ √ √LEAST √ √LEAVE √LEAVES √LEFT √ √ √ √LENGTH √ √LESS √ √ √Level √ √ √ √Like √ √ √ √LIMIT √ √ √LINEAR √LINENO √ √LINES √LINESTRING √LINK √ √LIST √ √ √LISTS √ √LOAD √ √ √LOADTABLE √ √LOCAL √ √ √LOCALTIME √ √ √LOCALTIMESTAMP √ √ √LOCATE √ √LOCATOR √ √LOCK √ √ √LOCKS √LOCKSIZE √ √LOG √ √LOGFILE √ √ √LOGICAL, LOGICAL1 √LOGS √LONG √ √ √LONG, LONGBINARY, LONGTEXT √LONGBLOB √LONGINT √ √LONGTEXT √LOOP √LOW_PRIORITY √LOWER √ √LPAD √ √LTRIM √ √LVARBINARY √ √LVARCHAR √ √Macro √MAIN √ √MANAGE √ √

17

MANUAL √ √MAP √ √MASTER √MASTER_BIND √MASTER_CONNECT_RETRY √MASTER_DELAY √MASTER_HEARTBEAT_PERIOD √MASTER_HOST √MASTER_LOG_FILE √MASTER_LOG_POS √MASTER_PASSWORD √MASTER_PORT √MASTER_RETRY_COUNT √MASTER_SERVER_ID √MASTER_SSL √MASTER_SSL_CA √MASTER_SSL_CAPATH √MASTER_SSL_CERT √MASTER_SSL_CIPHER √MASTER_SSL_CRL √MASTER_SSL_CRLPATH √MASTER_SSL_KEY √MASTER_SSL_VERIFY_SERVER_CERT √MASTER_USER √Match √ √ √ √MAX √ √Max, Min, Mod √MAX_CONNECTIONS_PER_HOUR √MAX_QUERIES_PER_HOUR √MAX_ROWS √MAX_SIZE √MAX_UPDATES_PER_HOUR √MAX_USER_CONNECTIONS √MAXDATAFILES √ √MAXEXTENTS √ √MAXINSTANCES √ √MAXLOGFILES √ √MAXLOGHISTORY √ √MAXLOGMEMBERS √ √MAXTRANS √ √MAXVALUE √ √ √MEDIUM √MEDIUMBLOB √MEDIUMINT √MEDIUMTEXT √MEMO √MEMORY √MENUITEM √ √MERGE √MESSAGE √ √MESSAGE_TEXT √MICROSECOND √ √ √MICROSECONDS √ √MIDDLEINT √MIGRATE √MIN √ √MIN_ROWS √

18

MINEXTENTS √ √MINUS √ √MINUTE √ √ √MINUTE_MICROSECOND √MINUTE_SECOND √MINUTES √ √MINVALUE √ √MIRROREXIT √ √MOD √ √ √MODE √ √ √MODIFIES √ √ √MODIFY √ √ √Module √ √ √MONEY √ √ √MONTH √ √ √MONTHS √ √MOUNT √ √Move √ √ √MULTILINESTRING √MULTIPOINT √MULTIPOLYGON √MUTEX √MYSQL_ERRNO √NAME √ √NAMED √ √NAMES √ √ √NATIONAL √ √ √NATURAL √ √ √NCHAR √ √ √NCLOB √ √NDB √NDBCLUSTER √NEW √ √ √NewPassword √NEXT √ √ √NHEADER √ √NO √ √ √NO_WAIT √NO_WRITE_TO_BINLOG √NOARCHIVELOG √ √NOAUDIT √ √NOCACHE √ √NOCHECK √ √NOCOMPRESS √ √NOCYCLE √ √NODEGROUP √NOECHO √ √NOMAXVALUE √ √NOMINVALUE √ √NONCLUSTERED √ √NONE √ √ √NOORDER √ √NORESETLOGS √ √NORMAL √ √NOSORT √ √Not √ √ √ √Note √

19

NOTFOUND √ √NOTRIM √ √NOWAIT √ √NULL √ √ √ √NULLIF √ √NULLVALUE √ √NUMBER √ √ √NUMBER, NUMERIC √NUMERIC √ √ √NUMPARTS √ √NVARCHAR √NVL √ √OBID √ √Object √ √ √ODBCINFO √ √OF √ √OFF √ √ √OFFLINE √ √OFFSET √OFFSETS √ √OLD √ √OLD_PASSWORD √OLEOBJECT √ON √ √ √ √ONCE √ √ONE √ONE_SHOT √ONLINE √ √ONLY √ √OPEN √ √OPENDATASOURCE √ √OPENQUERY √ √OpenRecordset √OPENROWSET √ √OPERATION √ √OPTIMAL √ √OPTIMIZE √ √ √OPTION √ √ √ √OPTIONALLY √OR √ √ √ √ORDER √ √ √ √ORDINALITY √ √Orientation √OUT √ √ √Outer √ √ √ √OUTFILE √OUTPUT √ √OVER √ √OVERLAPS √ √OWN √ √OWNERACCESS √PACK_KEYS √PACKAGE √ √PAD √ √PAGE √ √ √PAGES √ √PARALLEL √ √

20

Parameter √ √ √PARAMETERS √ √ √PART √ √Partial √ √ √ √PARTITION √PARTITIONING √PARTITIONS √PASCAL √ √PASSWORD √PATH √ √PCTFREE √ √PCTINCREASE √ √PCTINDEX √ √PCTUSED √ √PERCENT √ √ √PERM √ √PERMANENT √ √PERMIT √ √PHASE √PI √ √PIPE √ √PIVOT √PLAN √ √PLI √ √PLUGIN √PLUGIN_DIR √PLUGINS √POINT √POLYGON √POSITION √ √POSTFIX √ √POWER √ √PRECISION √ √ √PREFIX √ √PREORDER √ √PREPARE √ √PRESERVE √ √ √PREV √PRIMARY √ √ √ √PRINT √ √PRINTSCREEN √ √PRIOR √ √PRIQTY √ √PRIVATE √ √PRIVILEGES √ √ √PROC √ √PROCEDURE √ √ √ √PROCESSEXIT √ √PROCESSLIST √PROFILE √ √ √PROFILES √PROGRAM √ √PROMPT √ √Property √PROXY √PUBLIC √ √PURGE √

21

PUTFORM √ √PUTOPER √ √PUTROW √ √QUALIFICATION √ √QUARTER √ √ √Queries √Query √ √QUICK √Quit √QUOTA √ √RADIANS √ √RAISE √ √RAISERROR √ √RAND √ √RANGE √ √ √RAW √ √READ √ √ √READ_ONLY √READ_WRITE √READS √ √ √READTEXT √ √REAL √ √ √ √REBUILD √Recalc √RECONFIGURE √ √RECORD √ √Recordset √RECOVER √ √ √RECURSIVE √ √REDISPLAY √ √REDO_BUFFER_SIZE √REDOFILE √REDUNDANT √REF √ √REFERENCES √ √ √ √REFERENCING √ √Refresh √RefreshLink √REGEXP √REGISTER √ √RegisterDatabase √Relation √RELATIVE √ √RELAY √RELAY_LOG_FILE √RELAY_LOG_POS √RELAY_THREAD √RELAYLOG √RELEASE √ √ √RELOAD √RELOCATE √ √REMOVE √ √RENAME √ √ √REORGANIZE √Repaint √RepairDatabase √REPEAT √ √ √

22

REPEATABLE √ √ √REPEATED √ √REPLACE √ √ √REPLICATE √ √REPLICATION √ √ √Report √Reports √Requery √REQUIRE √RESET √ √RESETLOGS √ √RESIGNAL √RESOURCE √ √RESTORE √ √RESTRICT √ √ √RESTRICTED √ √RESULT √ √RESUME √ √ √RETRIEVE √ √RETURN √ √ √RETURNED_SQLSTATE √RETURNS √ √ √REUSE √ √REVERSE √REVOKE √ √ √RIGHT √ √ √ √RLIKE √ROLE √ √ROLES √ √ROLLBACK √ √ROLLUP √ √ √ROUTINE √ √ √ROW √ √ √ROW_COUNT √ROW_FORMAT √ROWCOUNT √ √ROWGUIDCOL √ √ROWID √ √ROWIDTOCHAR √ √ROWLABEL √ √ROWNUM √ √ROWS √ √ √RPAD √ √RRN √ √RTREE √RTRIM √ √RULE √ √RUN √ √RUNTIMESTATISTICS √ √SAVE √ √SAVEPOINT √ √SCHEDULE √ √ √SCHEMA √ √ √SCHEMA_NAME √SCHEMAS √SCN √ √SCOPE √ √

23

SCREEN √ √ √SCROLL √ √SCROLLDOWN √ √SCROLLUP √ √SEARCH √ √SECOND √ √ √SECOND_MICROSECOND √SECONDS √ √SECQTY √ √SECTION √ √ √SEGMENT √ √SELECT √ √ √ √SENSITIVE √SEPARATOR √SEQUENCE √ √SERIAL √SERIALIZABLE √ √ √SERVICE √ √SESSION √ √ √SESSION_USER √ √SET √ √ √ √SetFocus √SetOption √SETS √ √SETUSER √ √SHARE √ √ √SHARED √ √SHORT √ √ √SHOW √SHUTDOWN √ √ √SIGN √ √SIGNAL √SIMPLE √ √ √SIN √ √SINGLE √SIZE √ √SLEEP √ √SLOW √SMALLINT √ √ √ √SNAPSHOT √ √ √SOME √ √ √SORT √ √SOUNDEX √ √SOUNDS √SOURCE √SPACE √ √SPATIAL √SPECIFIC √ √ √SPECIFICTYPE √ √SQL √ √ √ √SQL_BIG_RESULT √SQL_BUFFER_RESULT √SQL_CACHE √SQL_CALC_FOUND_ROWS √SQL_NO_CACHE √SQL_SMALL_RESULT √SQL_THREAD √

24

SQL_TSI_DAY √SQL_TSI_HOUR √SQL_TSI_MINUTE √SQL_TSI_MONTH √SQL_TSI_QUARTER √SQL_TSI_SECOND √SQL_TSI_WEEK √SQL_TSI_YEAR √SQLBUF √ √SQLCA √ √SQLCODE √ √SQLERROR √ √SQLEXCEPTION √ √ √SQLSTATE √ √ √SQLWARNING √ √ √SQRT √ √SSL √START √ √STARTING √STARTS √STATE √ √STATEMENT √ √STATIC √ √STATISTICS √ √STATUS √StDev, StDevP √STOGROUP √ √STOP √ √STORAGE √ √ √STORPOOL √ √STRAIGHT_JOIN √STRING √ √STRUCTURE √ √SUBCLASS_ORIGIN √SUBJECT √SUBMENU √ √SUBPAGES √ √SUBPARTITION √SUBPARTITIONS √SUBSTR √ √SUBSTRING √ √SUCCESSFUL √ √SUFFIX √ √Sum √ √ √SUMU √ √SUPER √SUSPEND √SWAPS √SWITCH √ √SWITCHES √SYNONYM √ √SYSCAT √ √SYSDATE √ √SYSFUN √ √SYSIBM √ √SYSSTAT √ √SYSTEM √ √

25

SYSTEM_USER √ √SYSTIME √ √SYSTIMESTAMP √ √TABLE √ √ √ √TABLE_CHECKSUM √TABLE_NAME √TABLEDATA √ √TableDef, TableDefs √TableID √TABLES √ √ √TABLESPACE √ √ √TAN √ √TAPE √ √TEMP √ √TEMPORARY √ √ √TEMPTABLE √TERMINATE √ √TERMINATED √TEXT √ √TEXTSIZE √ √THAN √ √ √THEN √ √ √THREAD √ √TIME √ √ √TIME, TIMESTAMP √TIMEOUT √ √TIMESTAMP √ √ √TIMESTAMPADD √TIMESTAMPDIFF √TIMEZONE_HOUR √ √TIMEZONE_MINUTE √ √TINYBLOB √TINYINT √ √ √TINYTEXT √TO √ √ √TOP √ √ √TOP OF PAGE √TPE √ √TRACING √ √TRAILING √ √ √TRAN √ √TRANSACTION √ √ √TRANSFORM √TRANSLATE √ √TRANSLATION √ √TREAT √ √TRIGGER √ √ √TRIGGERS √ √ √TRIM √ √TRUE √ √ √ √TRUNCATE √ √TSEQUAL √ √Type √ √ √ √TYPES √UID √ √UNCOMMITTED √ √ √UNDEFINED √

26

UNDER √ √UNDO √UNDO_BUFFER_SIZE √UNDOFILE √UNION √ √ √ √UNIQUE √ √ √ √UNKNOWN √ √ √UNLIMITED √ √UNLOADTABLE √ √UNLOCK √UNNEST √ √UNSIGNED √ √ √UNTIL √ √ √UP √ √UPDATE √ √ √ √UPDATETEXT √ √UPPER √ √USAGE √ √ √USE √ √ √USE_FRM √USER √ √ √ √USER_RESOURCES √USING √ √ √UTC_DATE √UTC_TIME √UTC_TIMESTAMP √UUID √ √VALIDATE √ √VALIDPROC √ √VALIDROW √ √VALUE √ √ √ √VALUES √ √ √ √Var, VarP √VARBINARY √ √ √VARBINARY, VARCHAR √VARCHAR √ √ √VARCHARACTER √VARIABLE √ √VARIABLES √ √ √VARYING √ √ √VCAT √ √VERSION √ √VIEW √ √ √VOLUMES √ √WAIT √WAITFOR √ √WARNINGS √WEEK √ √ √WEIGHT_STRING √WHEN √ √ √WHENEVER √ √WHERE √ √ √ √WHILE √ √ √WITH √ √ √ √WITHOUT √ √WORK √ √ √Workspace √

27

WRITE √ √ √WRITETEXT √ √X509 √XML √Xor √ √Year √ √ √ √YEAR_MONTH √YEARS √ √YES √YESNO √ZEROFILL √ZONE √ √

28

LAMPIRAN 1. MICROSOFT ACCESS DATA TYPES

DATA TYPE USE FOR SIZE

Text Text or combinations of text and numbers, such as addresses. Also numbers that do not require calculations, such as phone numbers, part numbers, or postal codes.

Up to 255 characters. Microsoft Access only stores the characters entered in a field; it does not store space characters for unused positions in a Text field. To control the maximum number of characters that can be entered, set the FieldSize property.

Memo Lengthy text and numbers, such as notes or descriptions. Up to 64,000 characters.Number Numeric data to be used for mathematical calculations, except

calculations involving money (use Currency type). Set the FieldSize property to define the specific Number type.

1, 2, 4, or 8 bytes. 16 bytes for Replication ID (GUID) only.Click here for more information on changing Number field sizes.

Date/Time Dates and times. 8 bytes.Currency Currency values. Use the Currency data type to prevent rounding off

during calculations. Accurate to 15 digits to the left of the decimal point and 4 digits to the right.

8 bytes.

AutoNumber Unique sequential (incrementing by 1) or random numbers automatically inserted when a record is added.

4 bytes. 16 bytes for Replication ID (GUID) only.

Yes/No Fields that will contain only one of two values, such as Yes/No, True/False, On/Off.

1 bit.

OLE Object Objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data), created in other programs using the OLE protocol, that can be linked to or embedded in a Microsoft Access table. You must use a bound object frame in a form or report to display the OLE object.

Up to 1 gigabyte (limited by disk space).

Hyperlink Field that will store hyperlinks. A hyperlink can be a UNC path or a URL.

Up to 64,000 characters.

Lookup Wizard

Creates a field that allows you to choose a value from another table or from a list of values using a combo box. Choosing this option in the data type list starts a wizard to define this for you.

The same size as the primary key field that is also the Lookup field; typically 4 bytes.

Number Type

SETTING DESCRIPTIONDECIMAL

PRECISIONSTORAGE

SIZEByte Stores numbers from 0 to 255 (no fractions). None 1 byte

Integer Stores numbers from –32,768 to 32,767 (no fractions). None 2 bytesLong Integer (Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions). None 4 bytes

Decimal Stores numbers from -10^38 -1 through 10^38 -1 (.adp)Stores numbers from-10^28 -1 through 10^28 -1 (.mdb)

28 12 bytes

SingleStores numbers from –3.402823E38 to

–1.401298E–45 for negative values and from1.401298E–45 to 3.402823E38 for positive values.

7 4 bytes

DoubleStores numbers from –1.79769313486231E308 to –4.94065645841247E–324 for

negative values and from 1.79769313486231E308 to 4.94065645841247E–324 for positive values.

15 8 bytes

Replication ID

Globally unique identifier (GUID) N/A 16 bytes

Sumber:

http://www.databasedev.co.uk/fields_datatypes.html

LAMPIRAN 2. MYSQL DATA TYPES

TYPE {STORAGE}

NAME RANGE ATTRIBUTES DEFAULT

Numeric{1 byte} TINYINT[(M)]

-128 TO 127[0 to 255 if UNSIGNED]

AUTO_INCREMENTUNSIGNED, ZEROFILL,

SERIAL DEFAULT VALUE

NULL[0 if NOT NULL]

Numeric{2 bytes} SMALLINT[(M)]

-32,768 to 32,767[0 to 65,535]

AUTO_INCREMENT,UNSIGNED, ZEROFILL,

SERIAL DEFAULT VALUE

NULL[0 if NOT NULL]

Numeric{3 bytes} MEDIUMINT[(M)]

-8,388,608 to 8,388,607[0 to 16,777,215]

AUTO_INCREMENT,UNSIGNED, ZEROFILL,

SERIAL DEFAULT VALUE

NULL[0 if NOT NULL]

Numeric{4 bytes} INT[(M)]

-/+2.147E+9[0 to 4.294E+9]

AUTO_INCREMENT,UNSIGNED, ZEROFILL,

SERIAL DEFAULT VALUE

NULL[0 if NOT NULL]

Numeric{8 bytes} BIGINT[(M)]

-/+9.223E+18[0 to 18.45E+18]

AUTO_INCREMENT,UNSIGNED, ZEROFILL,

SERIAL DEFAULT VALUE

NULL[0 if NOT NULL]

Numeric{4 or 8}

FLOAT(p) p=0-24  --> "FLOAT"p=25-53  --> "DOUBLE"

UNSIGNED, ZEROFILL NULL[0 if NOT NULL]

Numeric{4 bytes}

FLOAT[(M,D)] Min=+/-1.175E-38Max=+/-3.403E+38

UNSIGNED, ZEROFILL NULL[0 if NOT NULL]

Numeric{8 bytes}

DOUBLE[(M,D)] Min=+/-2.225E-308Max=+/-1.798E+308

UNSIGNED, ZEROFILL NULL[0 if NOT NULL]

Numeric{M+2}

DECIMAL[(M,[D])]Stored as string

Max Range = DOUBLE rangeFixed point vs. DOUBLE float

UNSIGNED, ZEROFILL NULL[0 if NOT NULL]

Bit{8 bytes}

BIT[(M)] Binary. Display by [add zero or converting with BIN()]. M=1-64

Prior to 5.03TINYINT(1) Synonym

NULL[0 if NOT NULL]

String{M char's}

CHAR[(M)] M=0-255 Characters, FIXED.Right padded with spaces.

BINARY, CHARACTER SET NULL["" if NOT NULL]

String{M char's1}

VARCHAR(M) M=0-65,535 CharactersM=0-255 <v5.0.3

BINARY, CHARACTER SET NULL["" if NOT NULL]

String{#char's1}

TINYTEXT2 0-255 Characters BINARY, CHARACTER SET NULL["" if NOT NULL]

String{#char's1}

TEXT2 0-65,535 Char's BINARY, CHARACTER SET NULL["" if NOT NULL]

String{#char's1}

MEDIUMTEXT2 0-16,777,215 Char's BINARY, CHARACTER SET NULL["" if NOT NULL]

String{#char's1}

LONGTEXT2 0-4,294,967,295 Char's BINARY, CHARACTER SET NULL["" if NOT NULL]

String{M bytes}

BINARY[(M)] M=0-255 bytes, FIXED. Global Only(case sensitive)

NULL["" if NOT NULL]

String{M bytes}

VARBINARY(M) 0-65,535 bytesM=0-255 <v5.0.3

Global Only(case sensitive)

NULL["" if NOT NULL]

String{#bytes1}

TINYBLOB 0-255 bytes Global Only(case sensitive)

NULL["" if NOT NULL]

String{#bytes1}

BLOB 0-65,535 bytes Global Only(case sensitive)

NULL["" if NOT NULL]

String{#bytes1}

MEDIUMBLOB 0-16,777,215 bytes Global Only(case sensitive)

NULL["" if NOT NULL]

String{#bytes1}

LONGBLOB 0-4,294,967,295 bytes Global Only(case sensitive)

NULL["" if NOT NULL]

String{1-2 bytes}

ENUM2

("A1","A2",...)Column is exactly 1 of 1-65,535

valuesCHARACTER SET NULL [1st value if NOT NULL]

String{1-8 bytes}

SET2

("A1","A2",...)Column is 0 or more values in list

of 1-64 membersCHARACTER SET NULL

["" if NOT NULL]Date & Time

{3 bytes}DATE "1000-01-01" - "9999-12-31" Global Only

(YYYY-MM-DD)NULL

["0000-00-00" if NOT NULL]Date & Time

{8 bytes}DATETIME "1000-01-01 00:00:00" -

"9999-12-31 23:59:59"Global Only

(YYYY-MM-DD hh:mm:ss)NULL ["0000-00-00 00:00:00"

if NOT NULL]Date & Time

{3 bytes}TIME "-838:59:59" - "838:59:59" Global Only

(hh:mm:ss)NULL

["00:00:00" if NOT NULL]Date & Time

{4 bytes}TIMESTAMP 19700101000000 -

2037+Global Only

(YYYYMMDDhhmmss)Current Date & Time

Date & Time{1 bytes}

YEAR 1900 - 2155 Global Only(YYYY)

NULL["0000" if NOT NULL]

Sumber:

http://kimbriggs.com/computers/computer-notes/mysql-notes/mysql-data-types-50.file

LAMPIRAN 3. MICROSOFT SQL SERVER DATA TYPE

DATATYPE MIN MAX STORAGE 8 9 10

TYPE NOTES

Bigint -2^63 2^63-1 8 bytes Exact numeric

Int -2,147,483,648

2,147,483,647 4 bytes Exact numeric

Smallint -32,768 32,767 2 bytes Exact numeric

Tinyint 0 255 1 bytes Exact numeric

Bit 0 1

1 to 8 bit columns in the same table requires a total of 1 byte, 9 to 16 bits = 2 bytes, etc...

Exact numeric

Decimal -10^38+1 10^38–1

Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes 

Exact numeric

Decimal and numeric data type is exactly the same. Precision is the total number of digits. Scale is the number of decimals. For booth the minimum is 1 and the maximum is 38.

Numeric no

Money -2^63 / 10000 2^63-1 / 10000 8 bytes Exact numeric

Smallmoney -214,748.3648 214,748.3647 4 bytes Exact numeric

Float -1.79E + 308 1.79E + 308

4 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53

Approximate numerics

Precision is specified from 1 to 53.

Real -3.40E + 38 3.40E + 38 4 bytes Approximat Precision is fixed to 7.

e numerics

Datetime1753-01-01 00:00:00.000

9999-12-31 23:59:59.997 8 bytes

Date and time

If you are running SQL Server 2008 and need milliseconds precision, use datetime2(3) instead to save 1 byte.

Smalldatetime 

1900-01-01 00:00

2079-06-06 23:59

Date and time

Date 0001-01-01 9999-12-31 no

no

Date and time

Time 00:00:00.0000000

23:59:59.9999999

no

no

Date and time

Specifying the precision is possible. TIME(3) will have milliseconds precision. TIME(7) is the highest and the default precision. Casting values to a lower precision will round the value.

Datetime20001-01-01 00:00:00.0000000

9999-12-31 23:59:59.9999999

Presicion 1-2 = 6 bytes precision 3-4 = 7 bytes precision 5-7 = 8 bytes

no

no

Date and time

Combines the date datatype and the time datatype into one. The precision logic is the same as for the time datatype.

Datetimeoffset

0001-01-01 00:00:00.0000000 -14:00

9999-12-31 23:59:59.9999999 +14:00

Presicion 1-2 = 8 bytes precision 3-4 = 9 bytes precision 5-7 = 10 bytes

no

no

Date and time

Is a datetime2 datatype with the UTC offset appended.

Char 0 chars 8000 chars Defined width Character string

Fixed width

Varchar 0 chars 8000 chars 2 bytes + number of chars Character string

Variable width

Varchar(max)

0 chars 2^31 chars 2 bytes + number of chars no

Character string

Variable width

Text 0 chars 2,147,483,647 chars

4 bytes + number of chars Character string

Variable width

Nchar 0 chars 4000 chars Defined width x 2Unicode character string

Fixed width

Nvarchar 0 chars 4000 charsUnicode character string

Variable width

Nvarchar(max) 0 chars 2^30 chars

no

Unicode character string

Variable width

Ntext 0 chars1,073,741,823 chars

Unicode character string

Variable width

Binary 0 bytes 8000 bytes Binary string

Fixed width

Varbinary 0 bytes 8000 bytes Binary string

Variable width

Varbinary(max)

0 bytes 2^31 bytes no

Binary string

Variable width

Image 0 bytes 2,147,483,647 bytes

Binary string

Variable width

Sql_variant Other

Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.

Timestamp Other

Stores a database-wide unique number that gets updated every time a row gets updated.

Uniqueidentifier

Other Stores a globally unique identifier (GUID).

Xml no

Other Stores XML data. You can store xml instances in a column or a variable.

Cursor Other A reference to a cursor.Table Other Stores a result set for later

processing.Keterangan Kolom:

8 = SQL Server 2000 9 = SQL Server 2005 10 = SQL Server 2008

Sumber:

http://www.connectionstrings.com/Articles/Show/sql-server-data-type-reference

LAMPIRAN 4. ORACLE DATATYPES

Tipe Data dari DBMS Oracle 8 sampai dengan Oracle 11G

DATATYPE DESCRIPTIONMAX SIZE:ORACLE 8

MAX SIZE:ORACLE 9I/10G

MAX SIZE:ORACLE 11G

MAX SIZE: PL/SQL

PL/SQLSUBTYPES/ 

SYNONYMS

VARCHAR2(size)

Variable length character string having

maximum lengthsize bytes.

You must specify size

4000 bytesminimum is 1

4000 bytesminimum is 1

4000 bytesminimum is 1

32767 bytesminimum is 1

STRINGVARCHAR

NVARCHAR2(size)

Variable length national character set string having maximum length size bytes.

You must specify size

4000 bytesminimum is 1

4000 bytesminimum is 1

4000 bytesminimum is 1

32767 bytesminimum is 1

STRINGVARCHAR

VARCHAR

Now deprecated (provided for backward

compatibility only)VARCHAR is a synonym for VARCHAR2 but this usage may change in

future versions.

- - -

CHAR(size)

Fixed length character data of length size

bytes. This should be used for fixed length data. Such as codes

A100, B102…

2000 bytesDefault and

minimum size is 1 byte.

2000 bytesDefault and

minimum size is 1 byte.

2000 bytesDefault and

minimum size is 1 byte.

32767 bytesDefault and

minimum size is 1 byte.

CHARACTER

NCHAR(size) Fixed length national character set data of

length size bytes. This

2000 bytesDefault and

minimum size is

2000 bytesDefault and

minimum size is

2000 bytesDefault and

minimum size is

32767 bytesDefault and

minimum size is

should be used for fixed length data. Such as codes A100, B102…

1 byte. 1 byte. 1 byte. 1 byte.

NUMBER(p,s) Number having precision p and scale s.

The precision p can range from 1

to 38.

The scale s can range from -84

to 127.

The precision p can range from 1

to 38.

The scale s can range from -84

to 127.

The precision p can range from

1 to 38.

The scale s can range from -84

to 127.

Magnitude 1E-130 .. 10E125

maximum precision of 126

binary digits, which is roughly equivalent to 38 decimal digits

The scale s can range from -84

to 127. 

For floating point don't specify p,s

REAL has a maximum

precision of 63 binary digits,

which is roughly equivalent to 18 decimal digits

fixed-point numbers:

DEC DECIMAL NUMERIC 

floating-point: 

DOUBLE PRECISION

FLOATbinary_float

(32 bit) binary_doub

le (64 bit)

integers:INTEGER

INT SMALLINT

simple_integer(10g)BOOLEAN

REAL

PLS_INTEGER

signed integersPLS_INTEGER values

require less storage and provide better

performance than NUMBER values. 

So use PLS_INTEGER where you can!

PL/SQL only PL/SQL only PL/SQL only

magnitude range is -

2147483647 .. 2147483647

BINARY_INTEGE signed integers (older magnitude NATURAL

R slower version of PLS_INTEGER)

range is -2147483647 .. 2147483647

NATURALNPOSITIVE

POSITIVENSIGNTYPE

LONG

Character data of variable length (A bigger version the

VARCHAR2 datatype)

2 Gigabytes

2 Gigabytes - but now deprecated

(provided for backward

compatibility only).

2 Gigabytes - but now

deprecated (provided for

backward compatibility

only).

32760 bytesNote this is

smalller than the maximum width

of a LONG column

DATE Valid date range

from January 1, 4712 BC to December

31, 9999AD.

from January 1, 4712 BC to December

31, 9999 AD.

from January 1, 4712 BC to December

31, 9999 AD.

from January 1, 4712 BC to December

31,9999 AD. (in Oracle7 =

4712 AD)

TIMESTAMP (fractional_seconds_precision)

the number of digits in the fractional part of the SECOND datetime field.

-

Accepted values of

fractional_seconds_precision are 0 to 9. (default =

6)

Accepted values of

fractional_seconds_precision

are 0 to 9. (default = 6)

TIMESTAMP (fractional_seconds_precision) WITH {LOCAL}

TIMEZONE

As above with time zone displacement value

-

Accepted values of

fractional_seconds_precision are 0 to 9. (default =

6)

Accepted values of

fractional_seconds_precision

are 0 to 9. (default = 6)

INTERVAL YEAR (year_precision)

TO MONTH

Time in years and months, where

year_precision is the number of digits in the YEAR datetime field.

-Accepted values

are 0 to 9. (default = 2)

Accepted values are 0 to 9. (default = 2)

INTERVAL DAY (day_precision)

TO SECOND (fractional_seco

Time in days, hours, minutes, and seconds.

day_precision is the

- day_precision may be 0 to 9.

(default = 2)fractional_second

day_precision may be 0 to 9. (default = 2)

fractional_seco

nds_precision)

maximum number of digits in 'DAY' 

fractional_seconds_precisionis the max number

of fractional digits in the SECOND field.

s_precisionmay be 0 to 9.

(default = 6)

nds_precisionmay be 0 to 9. (default = 6)

RAW(size)

Raw binary data of length size bytes.

You must specify size for a RAW value.

Maximum size is2000 bytes

Maximum size is 2000 bytes

Maximum size is 2000 bytes

32767 bytes

LONG RAWRaw binary data of

variable length. (not intrepreted by PL/SQL)

2 Gigabytes.

2 Gigabytes - but now deprecated

(provided for backward

compatibility only)

2 Gigabytes - but now

deprecated (provided for

backward compatibility

only)

32760 bytesNote this is

smalller than the maximum width of a LONG RAW

column

ROWID

Hexadecimal string representing the unique address of a row in its

table.(primarily for values

returned by the ROWID pseudocolumn.)

10 bytes 10 bytes 10 bytes

Hexadecimal string

representing the unique address of a row in its

table.(primarily for

values returned by the ROWID

pseudocolumn.)

UROWID

Hex string representing the logical address of a

row of an index-organized table

The maximum size and default is 4000 bytes

The maximum size and default is 4000 bytes

The maximum size and default is 4000 bytes

universal rowid - Hex string

representing the logical address of a row of an

index-organized table, either

physical, logical, or foreign (non-

Oracle)

SeeCHARTOROWIDand

the package

:DBMS_ROWID

MLSLABEL

Binary format of an operating system

label.This datatype is used with Trusted

Oracle7.

CLOB Character Large Object 4Gigabytes 8 TB

8 TB to 128 TB(4 Gigabytes - 1) * (database

block size)

NCLOB National Character Large Object

4Gigabytes 8 TB

8 TB to 128 TB(4 Gigabytes - 1) * (database

block size)

BLOB Binary Large Object 4Gigabytes 8 TB

8 TB to 128 TB(4 Gigabytes - 1) * (database

block size)

BFILE pointer to binary file on disk

4Gigabytes 8 TB

8 TB to 128 TB(4 Gigabytes - 1) * (database

block size)

XMLType XML data - 4 Gigabytes 4 Gigabytes

Populate with XML from a

CLOB or VARCHAR2.

or query from another

XMLType column.