Top Banner

of 17

17. Prak. Pemrograman Basis Data (SQL Server - VBdotNET) - M1

Jul 11, 2015

Download

Documents

Welcome message from author
This document is posted to help you gain knowledge. Please leave a comment to let me know what you think about it! Share it to your friends and learn new things together.
Transcript

SQL (sekuel) Pertemuan 1:

SQL (sekuel)Sub-Materi: Select Multiple-table query SQL Function Transaction

PerkenalanPraktikum PBD (Pemrograman Basis Data) merupakan bagian dari MK PBD. PBD mengintegrasikan pemrograman di sisi database server dan pemrograman di sisi client. Karena itulah dalam MK PBD, ada prasyarat yang harus ditempuh oleh mahasiswa untuk dapat mengambil MK ini. Prasyarat ini ada dua, yaitu (1) MK SBD (Sistem Basis Data) dan (2) MK Bahasa Pemrograman. Dalam MK SBD, materi meliputi desain dan pembuatan database mengikuti normalisasi, penyimpanan data, dan data retrieval (temu kembali data). Penyimpanan data dalam database menggunakan sintak-sintak DML, sedangkan untuk data retrieval menggunakan perintah SELECT. Keduanya dalam dunia programming disebut sebagai CRUD (Create-Read-UpdateDelete). Semua ini merupakan bagian dari SQL (baca sekuel), yaitu bahasa native bagi database. Ibarat bahasa inggris bagi orang Amerika, dan bahasa indonesia bagi orang Indonesia. Pemrograman disisi database server lebih umum disebut dengan SQL Programming, yaitu bahasa pemrograman yang memakai SQL sebagai basisnya. Karena menggunakan SQL, maka dari itu, pembahasan pada pertemuan 1 ini difokuskan padaPemrograman Basis Data pg. 1

SQL (sekuel) penyegaran kembali (refresh) materi-materi SQL yang telah diterima dalam MK SBD. Sedangkan dalam MK Bahasa Pemrograman, materi meliputi variable, control-flow statement, serta procedure and function. SQL Programming, sesuai dengan namanya, menggunakan semua materi tersebut. Jadi pembelajaran pada PBD seutuhnya merupakan pemakaian kembali dan kelanjutan dari MK SBD dan Bahasa Pemrograman. Selain SQL Programming, juga ada pemrograman di sisi client. Fokus utamanya adalah tentang bagaimana melakukan hubungan timbal balik dengan database server serta menerapkan SQL dan SQL Programming dalam aplikasi client.

SQL ServerDatabase management system yang dipakai dalam Praktikum PBD adalah SQL Server 2005 Express Edition. Selain ringan dan bebas dipakai, versi ini juga memiliki beberapa fitur penting dalam versi lain seperti security. Namun SQL Server Management Studio, sebuah tool untuk memanage lingkungan SQL Server, tidak ada dalam paket instalasi SQL Server 2005 Express Edition. Tool ini harus didownload sendiri di situs resminya. Di laboratorium komputer, telah diinstall SQL Server 2005 Express Edition beserta Management Studio-nya. Langkah pertama yang akan kita lakukan adalah membuat database yang akan kita pakai selama 8x masa latihan praktikum. Untuk script pembuatan database dapat diunduh di http://172.25.87.13/pbdsi/p1/createdb.sql. Setelah diunduh, ikuti langkah-langkah berikut: 1. Buka SQL Server Management Studio Express Edition (SSMSEE) melalui Start Menu

Pemrograman Basis Data

pg. 2

SQL (sekuel) 2. Login:

3. 4.

Isi Server name: dengan .\SQLEXPRESS pilih mode Windows Authentication klik tombol Connect. Menu File Open File atau Ctrl+O. Buka script yang telah diunduh. Lalu jalankan scriptnya melalui Setelah dieksekusi, ada 3 proses yang terjadi: - Pembuatan database beserta file-nya - Pembuatan tabel-tabel beserta constraint-nya - Pengisian data-data kedalam tabel

Struktur database Database dibangun oleh database schema. Dalam database schema inilah obyek-obyek database dibangun. Setiap schema diassign ke satu atau lebih user (atau login). Dalam Praktikum PDB, hanya dibahas obyek-obyek antara lain Table, Function, Stored Procedure dan Trigger. Pertemuan pertama difokuskan pada Table serta bagaimana DML dan SELECT pada Table (tabel). Database schema yang dipakai dalam selama 8x pertemuan dapat dilihat pada Gambar 1. Setiap tabel saling berelasi, karena itulah dinamakan Relational Database. Tabel yang dihubungkan dengan tanda kunci merupakan tabel yang ber-PK, atau Parent Table (tabel induk). Sedangkan tabel yang tidak ada tanda kunci merupakan tabel yang ber-FK, atau Child Table (tabel anak).Pemrograman Basis Data pg. 3

SQL (sekuel)

Gambar 1. Database schema selama praktikum

Tabel terdiri dari Row (baris) dan Column (kolom). Setiap baris merepresentasikan data, sedangkan kolom merepresentasikan struktur data yang disimpan. Struktur ini dibagi menjadi 2 bagian yaitu tipe-nya dan panjang-nya. Sebagai contoh, kolom nim pada tabel mahasiswa mempunyai tipe char untuk menyimpan karakter dengan panjang 11 karakter. Dengan perbedaan tipe dan panjang, otomatis akan berpengaruh pada cara kita dalam melakukan DML dan SELECT.

Pemrograman Basis Data

pg. 4

SQL (sekuel)

SELECTSELECT merupakan salah satu pondasi dalam SQL Programming. SELECT digunakan untuk menampilkan data, terlebih untuk mencari informasi dalam kumpulan data. Sintak SELECT dibagi kedalam 6 komponen, antara lain: 1. 2. SELECT. Diikuti oleh , dapat berupa literal_value atau column_list atau asterisk (*). FROM. Diikuti oleh sesuai dengan column_list. Jadi jika ada data yang diambil dari kolom tertentu, harus diketahui kolom tersebut diambil dari mana. WHERE. Diikuti oleh kondisi. GROUP BY. Diikuti oleh column_list. Bagian ini muncul ketika ada fungsi-fungsi agregasi. HAVING. Diikuti oleh kondisi. Bagian ini muncul ketika ada kondisi dari fungsi-fungsi agregasi ORDER BY. Diikuti oleh column_list.SELECT [FROM ] [WHERE [AND/OR ]] [GROUP BY ] [HAVING [AND/OR ]] [ORDER BY ]

3. 4. 5. 6.

Select_list Merupakan daftar select. Dapat berupa literal value atau column_list. Perhatikan contoh berikut:SELECT 1, 'STIKOM Surabaya';

Contoh tersebut mengembalikan 1 baris data dengan 2 kolom. dalam contoh tersebut tidak menggunakan data yang tersimpan dalam tabel, dan inilah yang disebut sebagai literal_value. Penggunaan konkrit literal_value umumnya digunakan bersama dengan column_list untuk menghasilkan sebuah expression.Pemrograman Basis Data pg. 5

SQL (sekuel) Mengambil data dari tabel (data retrieval) Fungsi utama dari SELECT adalah untuk data retrieval (atau pengambilan data) yang tersimpan dalam (beberapa) tabel. Perhatikan contoh berikut:SELECT 1 '1', 'STIKOM Surabaya' STIKOM, nim, nim + '@stikom.edu' EMAIL FROM mahasiswa;

Hasil:1 1 1 .. 1 STIKOM STIKOM Surabaya STIKOM Surabaya STIKOM Surabaya nim 05390102208 05410104001 07410104800 EMAIL [email protected] [email protected] [email protected]

Contoh tersebut menghasilkan baris data sejumlah data yang tersimpan dalam tabel Mahasiswa. Tanpa FROM, query tersebut menghasilkan error. Muncul pertanyaan, bagaimana jika ada data sesuai dengan , tetapi hanya 1x ditampilkan? Misal, kebutuhan untuk mengetahui siapa saja mahasiswa yang mempunyai nilai. Perhatikan contoh berikut:SELECT nim FROM nilai;

Hasil dari query tersebut adalah banyak nim yang sama. Untuk mengeliminasi data-data yang sama, dibutuhkan DISTINCT. Query menjadi seperti ini:SELECT DISTINCT nim FROM nilai;

Menyaring data Tidak semua data yang ada pada tabel, ingin ditampilkan. Terlebih ketika tabel terbagi kedalam banyak kolom dengan jumlah data yang sangat besar. Padahal data yang diambilPemrograman Basis Data pg. 6

SQL (sekuel) hanyalah sebuah data, contohnya data mahasiswa milik Alif. Dapat dibayangkan berapa waktu yang diperlukan untuk proses ini jika tidak ada penyaringan. Untuk mengoptimalkan waktu pencarian, ditambahkan WHERE pada query. Perhatikan contoh berikut:SELECT * FROM mahasiswa WHERE nama='Alif';

Hasil:nim 05410104001 nama Alif alamat Jl. Jagir 20 kota Surabaya jns_kel.. P sts_nikah B

Penyaringan data membutuhkan kondisi. Ada 3 predikat yang digunakan dalam penyaringan data, yaitu TRUE, FALSE, dan UNKNOWN. TRUE berarti kondisi menghasilkan nilai benar, FALSE berarti kondisi menghasilkan nilai salah, sedangkan UNKNOWN berkaitan dengan nilai NULL (secara default bernilai FALSE, kecuali menggunakan operator IS). Dalam penulisan sebuah kondisi, perlu diperhatikan tentang operator pembanding yang digunakan. Dalam contoh, operator yang digunakan adalah sama dengan (=). Beberapa operator lain dapat dilihat pada Tabel 1.Tabel 1. Operator pembanding

Operator = > >= < 50;

Hasil:kode_mk MK-003 MK-102 rata_nilai 54 65 nilai_tertinggi 90 80 nilai terendah 0 50

Pemrograman Basis Data

pg. 9

SQL (sekuel) Dapat dilihat pada contoh, bahwa kita bisa melakukan pengelompokan data menjadi kelompok-kelompok lebih kecil dengan menggunakan GROUP BY. Beberapa aturan dalam GROUP BY: 1. 2. Jika ada agregasi dalam , kolom tanpa agregasi harus terdaftar dalam GROUP BY. Dengan menggunakan WHERE, kita bisa mengabaikan barisbaris tertentu sebelum dilakukan agregasi dan pengelompokan. Isi dari GROUP BY adalah kolom, bukan alias.

3.

Ada pemakaian HAVING yang mempunyai struktur yang sama dengan WHERE. Benar, HAVING digunakan untuk menuliskan kondisi tetapi dibatasi hanya untuk nilai agregasi. Jadi misal kita menambahkan WHERE uas>50, maka nilai terendah adalah 50 dan hasil rata-rata pun menjadi berbeda. Silahkan dicoba!

Multiple-table QueryData-data yang tersimpan dalam database, tersebar kedalam beberapa tabel. Tabel-tabel ini dihubungkan dengan yang namanya referential constraint, yaitu hubungan antara FOREIGN KEY dan PRIMARY KEY. Dan tidak hanya pada single field, namun dapat juga berbentuk multiple field referential constraint. Karena itulah, untuk mendapatkan informasi yang tersebar, dibutuhkan metode untuk menggabungkan property tabel-tabel tersebut. Metode yang digunakan ada 2 macam, yaitu JOIN dan SUBQUERY. Perbedaannya sederhana, JOIN menggunakan satu SELECT, sedangkan SUBQUERY menggunakan dua atau lebih SELECT (umumnya dikatakan sebagai SELECT within a SELECT). Join Awal munculnya join adalah menggunakan WHERE. Jadi tabeltabel ditulis dalam FROM, sedangkan penggabungannya ditulis dalam WHERE. Sintak untuk join menggunakan WHERE adalah sebagai berikut:Pemrograman Basis Data pg. 10

SQL (sekuel)SELECT FROM , [, ...] WHERE [AND ...]

Contoh:SELECT m.nama, n.kode_mk, n.uas FROM mahasiswa m, nilai n WHERE m.nim=n.nim;

Hasil:nama Cinta Cinta Alif Gala kode_mk MK-001 MK-002 MK-002 MK-103 uas 80 0 70 40

Query tersebut menghasilkan sejumlah baris yang merupakan kombinasi jumlah data antara mahasiswa dan nilai. Dari operator yang digunakan, sudah jelas bahwa query ini menghasilkan baris yang mempunyai data yang sama diantara 2 tabel. Hal ini dapat juga disebut sebagai equijoin. Perkembangan SQL ANSI sejak tahun 1990-an, menambahkan model baru dalam JOIN, yaitu menggunakan FROM sebagai referensi utama baik untuk tabel maupun untuk penggabungannya. Sintak untuk join tipe ini adalah sebagai berikut:SELECT FROM JOIN ON < table1.PK = table2.FK> [[AND ...] JOIN ...];

Contoh:SELECT m.nama, n.kode_mk, n.uas FROM mahasiswa m JOIN nilai n ON m.nim = n.nim;

Antara model lama dengan model baru, tidak ada perbedaan signifikan pada performa dan execution plan untuk data-dataPemrograman Basis Data pg. 11

SQL (sekuel) sederhana, namun jika dihadapkan pada data-data komplek dengan ruang lingkup yang besar, hal ini menjadi sebuah hal yang perlu diperhitungkan. Ada 2 tipe JOIN, yaitu INNER JOIN yang lebih menekankan pada data yang sama, dan OUTER JOIN. Inner Join Tujuan utama dari INNER JOIN adalah menyamakan nilai baris pada sebuah tabel dengan tabel untuk kolom yang bersesuaian. Jika salah satu kolom tidak memiliki kesamaan nilai atau tidak mempunyai nilai sama sekali, maka baris ini tidak akan ditampilkan dalam hasil query. Jadi, masih sama dengan equijoin. Outer Join Dalam contoh equijoin, data dengan nama mahasiswa Indah, tidak ada. Hal ini dikarenakan Indah memang tidak mempunyai nilai. Sebagai seorang dosen, kita tentu ingin agar si Indah ini juga tampil sebagai hasil dari query. Salah satu cara adalah dengan menggunakan outer join. Sintak join lebih detilnya menjadi seperti berikut:SELECT FROM [OUTER] JOIN ON [AND ...];

Dalam syntax tersebut, ada LEFT/RIGHT. Penggunaan LEFT akan memproses seluruh data yang ada pada tabel sebelah kiri kemudian dilanjutkan dengan data yang sama pada tabel sebelah kanan. Sedangkan RIGHT bekerja sebaliknya. Dengan demikian, OUTER JOIN fokus pada nilai yang tidak sama antara kolom-kolom yang bersesuaian pada tabel yang di-JOINkan. Nilai yang tidak sama, direpresentasikan oleh NULL. Sehingga dari contoh INNER JOIN, permasalahan dapat diatasi dengan mengubah query menjadi seperti berikut:

Pemrograman Basis Data

pg. 12

SQL (sekuel)SELECT m.nama, n.kode_mk, n.uas FROM mahasiswa m LEFT JOIN nilai n ON m.nim = n.nim;

Hasil:nama Cinta Cinta Indah Gala Gala kode_mk MK-001 MK-002 NULL MK-003 MK-103 uas 80 0 NULL 0 40

Nonequi Join Berbeda dengan equijoin yang selalu memakai operator sama dengan, nonequi join memakai operator selain sama dengan. Contoh permasalahan misalnya kita ingin mencari mahasiswa dengan angkatan lebih muda dari Entin. Perhatikan query berikut:SELECT entin.nim, m.nim, m.nama FROM mahasiswa m, mahasiswa entin WHERE LEFT(m.nim,2) > LEFT(entin.nim,2) AND entin.nama='Entin';

Hasil:nim 06390102666 06390102666 06390102666 nim 07390102802 07390102900 07410104800 nama Henny Indah Gala

Subquery Merupakan query dalam query. Umumnya, subquery ini dipakai untuk mencari data yang belum diketahui. Penggunaan query didalam query ini umumnya menjadi bagian dari kondisi. Sintak subquery adalah sebagai berikut:Pemrograman Basis Data pg. 13

SQL (sekuel)SELECT FROM WHERE = (SELECT FROM WHERE );

Namun, tidak menutup kemungkinan penggunaan subquery sebagai bagian dari data. Dalam artian query didalam SELECT ataupun didalam FROM, atau biasa disebut sebagai INLINE VIEW. Sebagai contoh penggunaan subquery, perhatikan contoh berikut:SELECT nim, nama FROM mahasiswa WHERE LEFT(nim,2) = (SELECT LEFT(nim,2) FROM mahasiswa WHERE nama='Entin');

Dalam contoh tersebut, kita mengubah query pada contoh nonequi join karena memang pada dasarnya kita harus mencari terlebih dahulu angkatan si Entin menggunakan subquery. Jadi, SQL Server memproses subquery terlebih dahulu untuk kemudian hasilnya dipakai pada main query-nya.

SQL FunctionFungsi-fungsi dalam SQL (bukan User Defined Function) lebih dikhususkan pada masing-masing provider. Secara umum, fungsi-fungsi ini dapat dibagi menjadi: 1. Aggregation Mengembalikan nilai scalar yang merepresentasikan sebuah agregasi dari sejumlah nilai. Macam: AVG(), MAX(), COUNT(), MIN(), SUM() Conversion Mengubah nilai dari satu tipe data ke tipe data lain. Selain itu konversi digunakan juga untuk mengaplikasikan karakteristik pada format date, time, dan numeric. Macam: CAST(), CONVERT()

2.

Pemrograman Basis Data

pg. 14

SQL (sekuel) 3. String Manipulation Digunakan untuk mengubah, mengganti, dan memanipulasi karakter. Macam: LEN(), SUBSTRING(), UPPER(), LOWER(), LEFT(), RIGHT(), REPLACE() Mathematical Digunakan untuk operasi matematis, mulai dari algebra, trigonometri, statistik, peramalan, dan bahkan operasi finansial. Macam: CEILING(), FLOOR(), ROUND(), POWER(), SQRT() Date and Time Mengubah porsi date dan time, komparasi dan manipulasi nilai-nilai date/time. Macam: GETDATE(), DATEPART(), DATEADD(), DATEDIFF() dll

4.

5.

6.

TransactionMungkin lebih umum jika disebut sebagai DML atau Data Manipulation Language. Lebih umum lagi jika disebut dalam bentuk Insert, Update, Delete. Bersama dengan SELECT, transaction secara ilmiah dikenal sebagai CRUD (Create Read Update Delete). Sederhananya, transaksi adalah sebuah mekanisme untuk memastikan bahwa data masuk sesuai dengan tempatnya. Tentunya dengan beberapa aturan. Aturan-aturan ini didefinisikan secara khusus melalui constraint, relationship, dan tipe data. Dari aturan ini, sistem yang menentukan apakah transaksi sukses atau tidak (dengan mengembalikan nilai kesalahan). Pembahasan untuk transaksi akan diberikan pada modul lain. untuk saat ini, fokus transaksi adalah pada CRUD (lets do CRUD with data). Penambahan data Sebelum melakukan penambahan data, perlu diperhatikan halhal sebagai berikut:Pemrograman Basis Data pg. 15

SQL (sekuel) Kolom mana yang membutuhkan nilai Kolom mana yang mempunyai constraint Kolom mana yang diatur oleh database melalui fungsi Kolom mana yang mempunyai nilai default atau yang memperbolehkan nilai NULL Apa tipe data kolom tujuan

Untuk menambahkan data, diperlukan INSERT.INSERT INTO [()] VALUES ();

Perhatikan contoh berikut:INSERT INTO mahasiswa VALUES('08410104003','Jenny', 'Jl. Krembangan 50','Surabaya','W','B');

Pada contoh tersebut, tidak diberikan dengan asumsi bahwa data dimasukkan kedalam seluruh kolom. Bagaimana jika data yang dimasukkan adalah data dari tabel lain? Untuk menjawab pertanyaan ini, hal ini dikhususkan pada masing-masing provider. Untuk SQL Server, menggunakan perintah INSERT INTO SELECT. Modifikasi data Data dimodifikasi satu tabel dalam satu waktu. Sebuah perintah UPDATE dapat mempengaruhi satu, banyak, atau semua baris data dalam tabel tergantung dari kondisi (kriteria) yang diberikan.UPDATE SET = [, = ] WHERE ;

Perhatikan contoh berikut:UPDATE mahasiswa SET kota='Malang', sts_nikah='M' WHERE nim='08410104003';

Pemrograman Basis Data

pg. 16

SQL (sekuel) Penghapusan data Menggunaan DELETE dengan perlakuan sama seperti UPDATE. Yang perlu diperhatikan disini adalah adanya referential constraint (hubungan antara PK dan FK). Data tidak dapat dihapus ketika data tersebut direferensi oleh tabel lain.DELETE WHERE ;

Contoh sederhananya adalah sebagai berikut:DELETE mahasiswa;

Pemrograman Basis Data

pg. 17