Tutorial Membuat Database MySQL dgn DBDesigner

Interbase

I. Pendahuluan

Borland Interbase Server (Selanjutnya kita sebut IB saja) adalah database server yang sangat handal dan sangat terkenal dikalangan programmer yang menggunakan Borland Delphi sebagai alat untuk mengembangkan aplikasi database-nya. Firebird Server (selanjutnya kita sebut Firebird saja) bisa dibilang sebagai “adik sepupu” dari IB karena fitur dan perluasan bahasa SQL-nya sangat mirip dengan IB. Firebird juga dapat diartikan sebagai versi free dari IB. Dalam tutorial ini, saya akan coba menjabarkan bagaimana cara instalasi Firebird, Administrasi database, koneksi ke IB/Firebird via command line(menggunakan MS-DOS/ cmd.exe) dan via GUI (Graphical User Interface). Khusus untuk koneksi database via GUI, kita akan menggunakan IBO Console yang dapat didownload di situs resmi firebird (www.ibphoenix.com). Interface dari IBO Console sangat mirip dengan IBConsole (GUI tool IB yang langsung terinstall saat anda melakukan instalasi IB server secara default). IBO Console diperlukan disini karena Firebird Server secara default tidak memiliki console GUI untuk administrasi dan management databasenya, disamping itu fitur IBO Console lebih lengkap (menurut opini saya pribadi J) dari IBConsole bawaan IB, dan yang paling penting IBO Console adalah Freeware J. Selain itu, saya juga akan membahas bagaimana cara untuk membuat database, table, view, generator, stored procedure dan trigger, dan sebagainya.

II. Instalasi Firebird

Sebelum memulai tutorial ini, anda harus sudah memasang IB/Firebird pada komputer anda. Bila anda sudah menginstal Delphi (Delphi 6 atau yang lebih baru) Profesional/ Enterprise edition dengan mode instalasi default, maka IB sudah terinstalasi pada system anda. Untuk menginstal Firebird, anda harus mendownloadnya terlebih di situs resminya (www.ibphoenix.com). Apabila anda belum memiliki binary/ installer Firebird Server, anda dapat mendownloadnya di situs resmi Firebird (www.ibphoenix.com).

Dalam tutorial ini, saya menggunaka Firebird Server versi 2.0.1 dengan platform Microsoft Windows XP SP1.

Setelah Firebird berhasil anda download, maka lakukanlah pemasangan Firebird dengan langkah-langkah seperti dibawah ini :

1. Klik ganda pada binary/ setup file Firebird yang baru anda download.
2. Klik OK untuk melanjutkan proses instalasi.
3. Setelah muncul Welcome Screen Instalasi Firebird, Klik next.
4. Setelah muncul jendela ‘License agreement’, centang pilihan ‘I accept the agreement’, kemudian klik ‘Next’.
5. Pilih lokasi instalasi Firebird anda (Defaultnya adalah C:\Program Files\Firebird\Firebird_2_0), kemudian klik ‘Next’. Pada pilihan ‘Install Component’, pilihlah pilihan-pilihan yang ada seperti gambar dibawah ini, klik ‘Next’.
6. Bila pilihan additional task sudah muncul, isilah pilihan-pilihannya seperti gambar dibawah ini, kemudian klik ‘Next’.
7. Kemudian klik install. Tunggu sampai instalasi selesai. Bila jendela konfirmasi untuk me reboot/ restart computer muncul, klik Yes/ Ok .

III. Management Database

a) Via Command Line

Catatan :

Untuk membuka Command Line, pilih ‘Start->Run’, kemudian ketik ‘cmd’ (tanpa tanda petik) lalu klik tombol ‘Ok’.

Sebelum melakukan operasi pada Command Line, pertama-tama, tambahkan terlebih dahulu path lokasi instalasi Firebird kedalam Path system environtment computer anda. Untuk menambahkan Path Firebird kedalam Path system environtment, Lakukan langkah-langkah seperti dibawah ini :

1. Klik kanan pada icon ‘My Computer->Properties‘ yang ada pada desktop atau dapat juga dengan meng-klik ‘Start->control Panel->System‘.

2. Pilih Tab ‘Advanced‘ dan klik ‘Environtment Variables‘.

3. Pada Kotak ‘User Varibles for xxx‘ (dimana xxx adalah nama computer anda), apabila variable ‘PATH‘ belum ada, maka klik tombol ‘New‘ dan masukkan ‘PATH‘ (tanpa tanda kutip) pada isian ‘Variable Name‘, dan lokasi directory bin Firebird (missal anda menginstal Firebird pada lokasi ‘C:\Program Files\Firebird\Firebird_2_0′, maka lokasi directory bin Firebird Server adalah ‘C:\Program Files\Firebird\Firebird_2_0\bin’ tanpa tanda kutip) anda pada isian ‘Variable Value‘. Sedangkan bila variable ‘PATH‘ sudah ada, klik ‘PATH‘, kemudian tekan tombol ‘Edit‘. Setelah itu, tambahkan lokasi directory bin Firebird Server pada kotak isian ‘Variable Value‘. Ingat, untuk memisahkan variable value, gunakan semi colon (tanda titik koma) pada akhir PATH yang sudah ada, missal (c:\php;C:\Program Files\Firebird\Firebird_2_0\bin)

1. Management User

Secara default SuperUser pada IB/Fireberd adalah ‘sysdba‘ dan password defaultnya adalah ‘masterkey‘ (tanpa tanda kutip). Untuk alasan keamanan, ubahlah password SuperUser anda setelah instalasi IB/Firebird. Dalam melakukan operasi penambahan/pengubahan/penghapusan data user, dibutuhkan hak akses SuperUser atau user yang memiliki hak akses untuk menambah/mengubah/menghapus data yang ada pada table RDB$USERS pada database security2.fdb.

Dalam melakukan operasi management user, dibutuhkan program gsec.exe yang ada pada directory bin IB/Firebird. Untuk melakukan operasi ini, buka Command Line dan tulislah:

gsec -user A -pass B -<op> C -pw D

Penjelasan :

o parameter -user A adalah user yang melakukan operasi. Dalam hal ini yang melakukan operasi adalah user yang bernama A.

o parameter -pass B adalah password user yang melakukan operasi. Dalam hal ini password A adalah B

o parameter <OP> C adalah operasi yang dikenakan pada user C dimana <op> adalah operasi yang dilakukan.

Pilihan <op> adalah :

1. add untuk melakukan penambahan user ,

2. mo untuk mengubah user (modify), dan

3. del untuk menghapus user (delete).

Dalam hal ini, operasi yang dilakukan dikenakan pada user yang bernama C.

o Parameter -pw D adalah password yang digunakan oleh C dimana D adalah password dari C. parameter ini hanya diperlukan pada operasi penambahan dan pengubahan user saja. Untuk operasi penghapusan user, parameter ini tidak diperlukan.

Misal :

o Operasi untuk menambah user dengan nama user1 dan password pwuser1

gsec -user sysdba -pass masterkey -add user1 -pw user1

o Operasi untuk mengubah password user dengan nama user1 dan password barunya adalah user123

gsec -user sysdba -pass masterkey -mo user1 -pw user1

o Operasi untuk menghapus user dengan nama user1

gsec -user sysdba -pass masterkey -del user1

2. Operasi Database

Membuat Database & Koneksi Database

Untuk membuat database, diperlukan program isql.exe yang ada pada directory binIB/Firebird. Dalam pembuatan database, usahakan sebisa mungkin tidak menggunakan superuser (sysdba), karena sysdba memiliki control penuh pada IB/Firebird Server anda serta dapat melakukan apa saja pada server IB/firebird, termasuk merusak struktur, isi, serta data-data penting yang ada pada database anda. Gunakanlah sysdba hanya untuk management user saja.

a. Membuat Database

Untuk membuat database baru, bukalah terlebih dahulu Command Line, kemudian ketik ‘isql’ (tanpa tanda petik), lalu tekan enter.

Setelah muncul prompt SQL> , ketik

CREATE DATABASE ‘A’ page_size 1234 user ‘B’ password ‘C’;

Penjelasan :

o A adalah nama database beserta full path nya, missal : c:\data\test.fdb atau d:\my data\test.gdb

o 1234 adalah ukuran page size dari database. Nilai – nilai page size dapat diisi dengan 1024,2048,4096,8192,16384, dan kelipatannya. Secara default, ukuran dari page size adalah 1024.

o B adalah nama user yang membuat (pemilik) database.

o C adalah password user pembuat database.

Contoh:

CREATE DATABASE ‘c:\data\POS.fdb’ page_size 1024 user ‘user1′ password ‘user1′;

Kemudian klik Enter.

Maka akan terbentuk database bernama POS.fdb dengan lokasi di c:\data (di drive C pada directory data).

b. Koneksi ke Database

Untuk koneksi ke database, pada isql, ketik

CONNECT ‘A’ USER ‘B’ PASSWORD ‘C’;

Penjelasan :

• A adalah nama beserta lokasi lengkap (full path) dari database
• B adalah nama user yang akan melakukan koneksi kedalam database.
• C adalah password user yang akan melakukan koneksi.

Contoh :

CONNECT ‘c:\data\POS.fdb’ USER ‘user1′ PASSWORD ‘user1′;

c. Backup/Restore

Backup dapat diartikan sebagai salinan dari file program atau dalam hal ini file database yang dibuat untuk memberi jaminan agar data yang ada tidak hilang atau terhapus apabila terjadi sesuatu pada file aslinya.

Sedangkan Restore dapat diartikan sebagai proses pengembalian file/data dari file backup yang dibuat sebelumnya untuk merecovery kerusakan file asli atau mengembalikan data file asli sesuai dengan isi data file asli saat dibuatnya file backup.

Untuk melakukan backup/restore database, diperlukan program gbak.exe yang ada pada directory bin IB/Firebird.

Secara umum untuk melakukan standard backup/restore, dapat dilakukan dengan mengeksekusi perintah berikut pada Command Line:

Gbak A -[b] B -user C -pas D

Penjelasan

Gbak adalah program yang dipanggil untuk melakukan operasi backup/restore

A adalah file input. Untuk backup, file inputnya berupa file IB/Firebird database (Extensinya : *.gdb | *.fdb | *.ib). Sedangkan untuk operasi restore inputnya berupa file backup (Extensinya : *.gbk | *.fbk | *.ibk).

[b] adalah operasi yang dilakukan. Operasi-operasi dalam penggunaan gbak adalah

-B(ACKUP_DATABASE) backup database ke dalam file

-BU(FFERS) override page buffers default

-C(REATE_DATABASE) membuat database dari backup file

-CO(NVERT) backup external files sebagai tabel

-E(XPAND) tanpa kompresi data

-FA(CTOR) blocking factor

-G(ARBAGE_COLLECT) mencegah data sampah

-I(NACTIVE) mematikan indek saat restore

-IG(NORE) mengabaikan bad checksums

-K(ILL) restore tanpa membuat shadows

-L(IMBO) abaikan transaksi dalam limbo

-M(ETA_DATA) hanya backup metadata

-MO(DE) <access> “read_only” or “read_write” access

-N(O_VALIDITY) tidak merestore kondisi validitas database

-NT Format backup file Non-Transportable

-O(NE_AT_A_TIME) restore satu tabel saja

-OL(D_DESCRIPTIONS) simpan deskripsi metadata yang lama

-P(AGE_SIZE) override page size default

-PAS(SWORD) Firebird password

-R(ECREATE_DATABASE) [O(VERWRITE)] membuat (atau mengganti bila OVERWRITE digunakan) database from backup file

-REP(LACE_DATABASE) mengganti database dari file backup

-RO(LE) Firebird SQL role

-SE(RVICE) gunakan manager service

-T(RANSPORTABLE) transportable backup-data dlm format XDR

-USE_(ALL_SPACE) tanpa membuat cadangan untuk versi record

-USER Firebird user name

-V(ERIFY) laporkan tiap aksi yang diambil

-Y <path> redirect/suppress status message output

-Z cetak version number

Contoh :

Backup :

Gbak c:\POS.fdb -b c:\POS_2007_aug_16.fbk -user user1 -pas user1

Perintah diatas akan membuat file backup yang bernama POS_2007_aug_16.fbk yang terletak di drive c:\ dari file database yang bernama POS.fdb yang terletak pada drive c:\. Operasi backup ini dilakukan oleh user1 dengan password dari user1 adalah user1.

Restore :

Gbak c:\POS_2007_aug_16.fbk -c c:\POS_Restore.fdb -user user1 -pas user1

Perintah diatas akan membuat file database yang bernama POS_Restore.fdb yang terletak pada drive c:\. Dimana POS_Restore.fdb ini dibuat dari backup file bernama POS_2007_aug_16.fbk yang terletak di drive c:\. Operasi ini dilakukan oleh user yang bernama user1 dengan passwordnya adalah user1 juga.

Backup-Restore Data di MySQL

Sebagai seorang DBA, backup merupakan pekerjaan wajib yang harus dilakukan sewaktu-waktu untuk menjaga keberadaan dan keamanan (dalam hal ini keutuhan) data-data yang tersimpan dalam database. Hal ini tentu dilakukan untuk menghindari kejadian-kejadian yang tidak diinginkan; yang mungkin tidak dapat kita prediksikan sebelumnya. Seberapa sering kita harus melakukan backup tentu tergantung dari kebutuhan database yang kita olah. Sebagai contoh, apabila kita mengolah database untuk kepentingan pemberitaan, mungkin kita perlu melakukan backup data per hari (sering disebut dengan backup harian atau daily backup). Namun untuk kebutuhan data-data tertentu yang perubahan datanya tidak terlalu sering, mungkin juga kita hanya perlu untuk melakukan backup secara rutin pada setiap pekan, atau bahkan setiap bulan.

Bila dibandingkan dengan proses backup dan restore data di Oracle, proses backup dan restore di MySQL ‘mungkin’ dapat dikatakan jauh lebih sederhana. Pasalnya, di sini kita tidak perlu direpotkan dengan melakukan hal-hal lain yang biasa kita lakukan di dalam Oracle, seperti: melakukan penyalinan (copy) datafile, control file, dan redo log file.

Dalam MySQL sendiri, terdapat beberapa cara yang dapat digunakan untuk melakukan roses backup dan restore, diantaranya adalah sebagai berikut:

• · Untuk proses Backup

1. Menggunakan perintah BACKUP
2. Menggunakan statemen SELECT INTO
3. Menggunakan tool MYSQLDUMP
4. Menggunakan tool MYSQLHOTCOPY
5. Melakukan penyalinan file secara langsung

• Untuk proses Restore

1. Menggunakan perintah RESTORE
2. Menggunakan statemen LOAD DATA INFILE

Melalui artikel yang saya peroleh ini saya ingin menunjukkan kepada Anda apa saja langkah-langkah yang diperlukan untuk melakukan pekerjaan backup dan restore data di dalam MySQL. Adapun cara yang akan saya gunakan di sini hanya BACKUP dan SELECT INTO (untuk proses backup) serta RESTORE dan LOAD DATA (untuk proses restore). Artikel ini sengaja saya susun dengan logat tutorial, dengan harapan Anda dapat langsung mempraktekannya secara mudah dan cepat.

Untuk memudahkan Anda dalam memahami artikel ini, coba Anda buat terlebih dahulu sebuah database baru dengan menggunakan perintah CREATE DATABASE. Selanjutnya buatlah dua buah tabel contoh dan masukkan data-data dummy ke dalamnya. Berikut ini langkah-langkah awal yang perlu dilakukan:

• Aktifkan direktori BIN di mana software MySQL Anda di-install
• Tuliskan perintah berikut pada command-prompt yang tersedia

C:\MySQL\bin\mysql –u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.15-nt
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> create database latihdb;
Query OK, 1 row affected (0.11 sec)
mysql> use latihdb;
Database changed
mysql>

Perintah di atas akan membuat sebuah database baru dengan nama LATIHDB serta mengaktifkannya dengan perintah USE.

• Buatlah dua buah tabel contoh (misalnya PEGAWAI dan JABATAN) dengan menggunakan perintah SQL di bawah ini.

mysql> create table jabatan (
-> kode_jabatan char(4) not null,
-> nama_jabatan varchar(25) not null,
-> constraint pk_jabatan primary key(kode_jabatan)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> create table pegawai (
-> nip char(6) not null,
-> nama varchar(25) not null,
-> kode_jabatan char(4),
-> constraint pk_pegawai primary key(nip),
-> constraint fk_pegawai_jabatan foreign key(kode_jabatan)
-> references jabatan(kode_jabatan)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>

• Isikan data-data dummy ke dalam kedua buah tabel tersebut, misalnya dengan menggunakan perintah di bawah ini.

mysql> insert into jabatan values(‘J-01′, ‘Manager’);
Query OK, 1 row affected (0.08 sec)
mysql> insert into jabatan values(‘J-02′, ‘Sales Marketing’);
Query OK, 1 row affected (0.00 sec)mysql> insert into jabatan values(‘J-03′, ‘Pimpinan Proyek’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into jabatan values(‘J-04′, ‘Analis Sistem’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into jabatan values(‘J-05′, ‘Programmer’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into pegawai values(‘000001′,’Imam’,'J-01′);
Query OK, 1 row affected (0.03 sec)
mysql> insert into pegawai values(‘000002′,’Dewi’,'J-02′);
Query OK, 1 row affected (0.00 sec)
mysql> insert into pegawai values(‘000003′,’Arif’,'J-03′);
Query OK, 1 row affected (0.00 sec)
mysql> insert into pegawai values(‘000004′,’Camelia’,'J-04′);
Query OK, 1 row affected (0.00 sec)
mysql> insert into pegawai values(‘000005′,’Made’,'J-05′);
Query OK, 1 row affected (0.00 sec)
mysql> insert into pegawai values(‘000006′,’Tatang’,'J-05′);
Query OK, 1 row affected (0.00 sec)
mysql> quit
bye

Menggunakan Perintah BACKUP
Salah satu cara termudah untuk melakukan backup data di MySQL adalah dengan menggunakan perintah BACKUP. Namun hal terpenting yang perlu dicatat adalah bahwa perintah ini hanya berlaku untuk tabel-tabel tipe MyISAM.
Berikut ini sintak umum yang digunakan untuk melakukannya.

BACKUP TABLE namatabel TO ‘path_tujuan’;

path_tujuan harus dituliskan secara lengkap, namun tidak perlu disebutkan nama file-nya. Proses ini akan membuat sebuah salinan dari file .frm (definisi) and .MYD (data), tetapi tidak memasukkan file .MYI (index).
Berikut ini langkah-langkah yang diperlukan untuk menggunakan perintah BACKUP.

• Buatlah path_tujuan, misalnya dengan nama DB_BACKUP di dalam drive C.
• Tuliskan perintah BACKUP, seperti yang tampak pada kode di bawah ini.

mysql> backup table pegawai, jabatan to ‘c:\db_backup’;
+—————–+——–+———-+————————————-+
| Table | Op | Msg_type | Msg_text |
+—————–+——–+———-+————————————-+
| latihdb.pegawai | backup | error | Failed copying .frm file (errno: 2) |
| latihdb.pegawai | backup | status | Operation failed |
| latihdb.jabatan | backup | error | Failed copying .frm file (errno: 2) |
| latihdb.jabatan | backup | status | Operation failed |
+—————–+——–+———-+————————————-+
4 rows in set (0.03 sec)
mysql>

Kenapa proses backup di atas tidak berhasil? Masalahnya adalah kita harus menggunakan double backslash (\\) pada saat menuliskan path tujuan. Perhatikan perintah di bawah ini yang merupakan perbaikan dari kode di atas.

mysql> backup table pegawai, jabatan to ‘c:\\db_backup’;
+—————–+——–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+—————–+——–+———-+———-+
| latihdb.pegawai | backup | status | OK |
| latihdb.jabatan | backup | status | OK |
+—————–+——–+———-+———-+
2 rows in set (0.03 sec)
mysql>

Menggunakan Statemen SELECT INTO
Perintah ini sama mudahnya dengan cara yang pertama (dengan menggunakan perintah BACKUP). Hanya yang perlu untuk diperhatikan di sini adalah bahwa perintah ini hanya berlaku untuk melakukan backup per tabel. Berikut ini sintak umum dari penggunaan statemen SELECT INTO.

SELECT daftar_kolom FROM nama_tabel INTO OUTFILE nama_file;

Berikut ini contoh yang akan menunjukkan bagaimana cara melakukan backup terhadap tabel PEGAWAI.

mysql> select * from pegawai into outfile ‘c:\\db_backup\\peg.dmp’;
Query OK, 6 rows affected (0.02 sec)
Perintah di atas akan membentuk sebuah file baru dengan nama PEG.DMP di dalam
direktori C:\DB_BACKUP, yang merupakan file hasil dari proses backup.

Menggunakan Perintah RESTORE
Perintah ini merupakan kebalikan dari perintah BACKUP. Fungsi dari perintah RESTORE adalah untuk melakukan restore terhadap data-data yang sebelumnya telah dibackup dengan menggunakan perintah BACKUP. Berikut ini bentuk umum dari penggunaan perintah RESTORE.

RESTORE TABLE nama_tabel FROM ‘path_tujuan’;

Penting sekali untuk diperhatikan bahwa perintah di atas tidak akan bekerja bila tabel tujuan masih ada di dalam database. Perhatikan contoh perintah di bawah ini.

mysql> restore table pegawai from ‘c:\\db_backup’;
+———+———+———-+———————————————+
| Table | Op | Msg_type | Msg_text |
+———+———+———-+———————————————+
| pegawai | restore | error | table exists, will not overwrite on restore |
+———+———+———-+———————————————+
1 row in set (0.00 sec)
mysql>

Error yang dimunculkan di atas disebabkan tabel pegawai masih terdapat di dalam database. Dan untuk mengatasi hal ini, maka kita harus menghapus tabel tersebut terlebih dahulu, yaitu dengan menggunakan perintah DROP TABLE, seperti berikut:

mysql> drop table pegawai;
Query OK, 0 rows affected (0.00 sec)
mysql> restore table pegawai from ‘c:\\db_backup’;
+—————–+———+———-+———-+
| Table | Op | Msg_type | Msg_text |
+—————–+———+———-+———-+
| latihdb.pegawai | restore | status | OK |
+—————–+———+———-+———-+
1 row in set (0.01 sec)
mysql>

Menggunakan Perintah LOAD DATA
Perintah ini merupakan kebalikan dari perintah SELECT INTO. Artinya, bila kita melakukan backup dengan perintah SELECT INTO, maka proses restore-nya perlu menggunakan LOAD DATA. Berikut ini bentuk umum untuk melakukan restore data dengan menggunakan perintah LOAD DATA.

LOAD DATA INFILE nama_file INTO TABLE nama_tabel;

Contoh:

mysql> load data infile ‘c:\\db_backup\\peg.dmp’ into table pegawai;
ERROR 1062: Duplicate entry ‘000001′ for key 1

Kesalahan di atas disebabkan dalam database masih mengandung data yang sama dengan data yang terdapat dalam file backup. Untuk mengatasi hal ini kita seharusnya menggunakan klausa IGNORE atau REPLACE, sehingga perintahnya akan tampak seperti berikut:

mysql> load data infile ‘c:\\db_backup\\peg.dmp’ ignore into table pegawai;
Query OK, 0 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 6 Warnings: 0
mysql> load data infile ‘c:\\db_backup\\peg.dmp’ replace into table pegawai;
Query OK, 12 rows affected (0.00 sec)
Records: 6 Deleted: 6 Skipped: 0 Warnings: 0
mysql> select * from pegawai;
+——–+———+————–+
| nip | nama | kode_jabatan |
+——–+———+————–+
| 000001 | Imam | J-01 |
| 000002 | Dewi | J-02 |
| 000003 | Arif | J-03 |
| 000004 | Camelia | J-04 |
| 000005 | Made | J-05 |
| 000006 | Tatang | J-05 |
+——–+———+————–+
6 rows in set (0.00 sec)
mysql>

Belajar Query (II)

select version();
+———————+
| version()           |
+———————+
| 5.0.22-community-nt |
+———————+

SELECT * FROM Opname JOIN Periode ON Opname.datum BETWEEN Periode.begindatum AND Periode.einddatum WHERE Opname.datum <= (SELECT MIN(datum) FROM Opname);
+——-+————+————+——–+————+————+————+
| rolnr | negatiefnr | datum      | prijs  | begindatum | einddatum  | plaatsnaam |
+——-+————+————+——–+————+————+————+
|   115 |          4 | 1980-07-17 | 500.00 | 1975-03-23 | 1981-01-02 | Parijs    |
|   115 |          5 | 1980-07-17 | 600.00 | 1975-03-23 | 1981-01-02 | Parijs    |
+——-+————+————+——–+————+————+————+

SELECT * FROM Opname JOIN Periode ON Opname.datum BETWEEN Periode.begindatum AND Periode.einddatum WHERE Opname.datum = (SELECT MIN(datum) FROM Opname);
+——-+————+————+——–+————+————+————+
| rolnr | negatiefnr | datum      | prijs  | begindatum | einddatum  | plaatsnaam |
+——-+————+————+——–+————+————+————+
|   115 |          4 | 1980-07-17 | 500.00 | 1975-03-23 | 1981-01-02 | Parijs    |
|   115 |          5 | 1980-07-17 | 600.00 | 1975-03-23 | 1981-01-02 | Parijs    |
+——-+————+————+——–+————+————+————+

SELECT * FROM Opname JOIN Periode ON Opname.datum BETWEEN Periode.begindatum AND Periode.einddatum WHERE Opname.datum <= (SELECT MIN(datum) FROM Opname);
+——-+————+————+——–+————+————+————+
| rolnr | negatiefnr | datum      | prijs  | begindatum | einddatum  | plaatsnaam |
+——-+————+————+——–+————+————+————+
|   115 |          4 | 1980-07-17 | 500.00 | 1975-03-23 | 1981-01-02 | Parijs    |
|   115 |          5 | 1980-07-17 | 600.00 | 1975-03-23 | 1981-01-02 | Parijs    |
+——-+————+————+——–+————+————+————+

SELECT * FROM Opname JOIN Periode ON Opname.datum BETWEEN Periode.begindatum AND Periode.einddatum WHERE Opname.datum = (SELECT MIN(datum) FROM Opname);
+——-+————+————+——–+————+————+————+
| rolnr | negatiefnr | datum      | prijs  | begindatum | einddatum  | plaatsnaam |
+——-+————+————+——–+————+————+————+
|   115 |          4 | 1980-07-17 | 500.00 | 1975-03-23 | 1981-01-02 | Parijs    |
|   115 |          5 | 1980-07-17 | 600.00 | 1975-03-23 | 1981-01-02 | Parijs    |
+——-+————+————+——–+————+————+————+

SELECT * FROM Opname JOIN Periode ON Opname.datum BETWEEN Periode.begindatum AND Periode.einddatum WHERE Opname.datum = (SELECT MIN(datum) FROM Opname);
+——-+————+————+——–+————+————+————+
| rolnr | negatiefnr | datum      | prijs  | begindatum | einddatum  | plaatsnaam |
+——-+————+————+——–+————+————+————+
|   115 |          4 | 1980-07-17 | 500.00 | 1975-03-23 | 1981-01-02 | Parijs    |
|   115 |          5 | 1980-07-17 | 600.00 | 1975-03-23 | 1981-01-02 | Parijs    |
+——-+————+————+——–+————+————+————+

Belajar Query (I)

• MENAMPILKAN DATA

Use northwind

• Tampilkan data seluruh karyawan

Select * From Employees

• Tampilkan Nama dan Title karyawan

Select LastName, FirstName, Title From Employees

• Tampilkan nama dan title karyawan dengan judul yang berbeda

Select LastName As NamaBelakang, FirstName As NamaDepan, Title As Gelar From Employees

• Tampilkan Nama depan dengan nama belakang digabungkan

Select FirstName+ ‘ ‘+LastName As Nama, Title From Employees

• Tampilkan Nama pegawai yang dimulai dengan huruf A

Select * From Employees Where FirstName Like ‘A%’

• Tampilkan Nama pegawai yang mengandung Huruf C

Select * From Employees Where FirstName Like ‘%C%’

• Tampilkan Nama pegawai yang huruf awalnya A atau M

Select * From Employees Where Left(FirstName,1) in(’A’,’M’)

• Tampilkan Nama dan Title karyawan berikut dengan tempat wilayah tinggal

Select LastName, FirstName, Title, City, Region From Employees

• Tampilkan Nama dan Title karyawan, yang tempat wilayah tinggal bernilai kosong (NULL)

Select LastName, FirstName, Title, City, Region From Employees Where Region Is NULL

• Tampilkan Nama dan Title karyawan, yang tempat wilayah tinggal bernilai tidak kosong (NULL)

Select LastName, FirstName, Title, City, Region From Employees Where Region Is NOT NULL

• Tampilkan Urutkan data nama karyawan mulai A – Z

Select FirstName, LastName, Title From Employees Order By FirstName

• Tampilkan Urutkan data nama karyawan mulai Z – A

Select FirstName, LastName, Title From Employees Order By FirstName DESC

• Tampilkan 3 record karyawan teratas

Select Top 3 FirstName, LastName, Title From Employees

• Tampilkan 5 record barang teratas

Select Top 5 * From Products

• Tampilkan 2 record stok barang terbanyak

Select Top 2 * From Products Order By UnitsinStock DESC

• Tampilkan berapa jumlah produk yang berasal dari supplierID no 2

Select Count(SupplierID) As [Jumlah Product] From Products Where SupplierID = 2

• Buktikan dengan datanya

Select ProductName, SupplierID From Products Where SupplierID = 2

• Tampilkan berapa jumlah produk ID no 1 yang telah dijual

Select SUM(Quantity) As [Jumlah Dijual] From [Order Details] Where ProductID=1

• Buktikan dengan datanya

Select ProductID, Quantity From [Order Details] Where ProductID=1

• Tampilkan SupplierID dan jumlah produk dan urutkan dari jumlah produk terbanyak

Select SupplierID, Count(SupplierID) As [Jumlah Product] From Products Group By SUPPLIERID ORDER BY [Jumlah Product] DESC

• Tampilkan jumlah penjualan yang dilakukan karyawan diurutkan mulai dari yang terbanyak

Select EmployeeID, Count(OrderID) As [Jumlah Order] From [Orders] Group By EmployeeID Order By [Jumlah Order] DESC

• Tampilkan data customer yang melakukan pembelian

Select O.CustomerID, CompanyName, OrderID, Orderdate From Orders O, Customers C Where O.CustomerID = C.CustomerID

• Adalah operator untuk menggabungkan dua buah table yang mempunyai nilai yang sama –melihat dari 2 sisi, sisi kiri sama dengan sisi kanan

Select O.CustomerID, CompanyName, OrderID, Orderdate From Orders O Inner Join Customers C On O.CustomerID = C.CustomerID

• Tampilkan data customer dengan ID=ALFKI yang melakukan pembelian

Select O.CustomerID, CompanyName, OrderID, Orderdate From Orders O Inner Join Customers C On O.CustomerID = C.CustomerID AND O.CustomerID =’ALFKI’

• Tampilkan data barang yang telah dijual yang quantity nya lebih dari 30

Select OD.ProductID, P.Productname, Quantity From [Order Details] OD Inner Join Products P On OD.ProductID = P.ProductID and Quantity >30