BABI SQL (sekuel) Sub-Materi: Select Join Subquery DML

BABI

SQL (sekuel) Sub-Materi: Select Join Subquery DML

SQL (sekuel) BabI

SQL (sekuel)

Sub-Materi: Select Join Subquery DML

Perkenalan Matakuliah Praktikum Pemrograman Basis Data (PBD) merupakan MK praktek dari matakuliah PBD.Matakuliah ini mengintegrasikan pemrograman di sisi database server dan pemrograman di sisi client. Karena itulah matakuliah ini mempunyai 2 matakuliah prasyarat, yaitu (1) matakuliah Sistem Basis Data (SBD) dan (2) matakuliah Bahasa Pemrograman (BPro). Dalam matakuliah SBD, materi yang dibahas meliputi desain dan pembuatan database mengikuti normalisasi, penyimpanan data, dan data retrieval (temu kembali data). Penyimpanan data dalam database menggunakan sintak-sintak data manipulation language (DML), sedangkan untuk data retrieval menggunakan perintah SELECT. Keduanya dalam dunia programming disebut sebagai CRUD (Create-Read-Update-Delete). Semua materi tersebut merupakan bagian dari pembelajaran dasar structured query language (SQL – baca sekuel), yaitu bahasa native bagi database. Native ibarat bahasa inggris bagi orang Amerika, dan bahasa indonesia bagi orang Indonesia. Pemrograman disisi database server lebih umum disebut dengan SQL Programming, yaitu Praktikum Pemrograman Basis Data

pg.1

SQL (sekuel) bahasa pemrograman yang memakai SQL sebagai dasar pemrogramannya. Karena menggunakan SQL, maka dari itu, pembahasan pada pertemuan 1 ini difokuskan pada penyegaran kembali (refresh) materi-materi SQL. Sedangkan dalam matakuliah BPro, materi yang dibahas meliputi variable, control-flow statement, serta procedureandfunction. SQL Programming, sesuai dengan namanya, juga menggunakan semua materi dalam matakuliah BPro. Jadi pembelajaran pada matakuliah Praktikum PBD seutuhnya merupakan pemakaian kembali dan kelanjutan dari matakuliah SBD dan BPro. Selain SQL Programming, juga ada pemrograman di sisi client. Fokus utamanya adalah bagaimana melakukan komunikasi dengan database server serta menerapkan SQL dan SQL Programming dalam aplikasi client.

SQL Server Database Management System (DBMS) yang dipakai dalam matakuliah Praktikum PBD adalah SQL Server 2005 Express Edition. Selain ringan dan bebas dipakai, versi ini juga memiliki beberapa fitur penting seperti keamanan. Namun SQL Server Management Studio, sebuah kakas bantuuntuk mengelola 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 di-install SQL Server 2005 Express Edition beserta Management Studio-nya. Langkah pertama yang dilakukan adalah membuat database yang akan dipakai selama 8x minggu praktikum. Untuk script pembuatan database dapat diunduh di http://192.168.100.3/latihan/132/BDS/create table.sql. Setelah diunduh, ikuti langkah-langkah berikut: 1.

pg.2

Buka SQL Server Management Studio Express Edition (SSMSEE) melalui Start Menu

Praktikum Pemrograman Basis Data

SQL (sekuel) 2.

3. 4.

Login:

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 script melalui tombol 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 diberikan ke satu atau lebih user (atau login). Dalam matakuliah 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. Database schemayang dipakai untuk TUGAS PRAKTIKUM selama 8x pertemuan dapat dilihat padaGambar 1.1, yaitu Putra Jaya. Sedangkan untuk LATIHAN PRAKTIKUM dapat dilihat pada Gambar 1.2, yaitu PBD . Setiap tabel saling berelasi, karena itulah dinamakan Relational Database. Tabel yang dihubungkan dengan tanda kunci merupakan tabel yang ber-PK, atau Parent Table Praktikum Pemrograman Basis Data

pg.3

SQL (sekuel) (tabel induk). Sedangkan tabel yang tidak ada tanda kunci merupakan tabel yang ber-FK, atau Child Table (tabel anak).

Gambar 1.1Database schema Putra Jaya

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 “namakonsumen” pada tabel “konsumen” mempunyai tipe “varchar” untuk menyimpan karakter dengan panjang semaksimalnya 100 karakter. Dengan perbedaan tipe dan panjang, otomatis akan berpengaruh pada cara kita dalam melakukan DML dan SELECT.

pg.4


SQL (sekuel)

Gambar 1.2 Database schema PBD

SELECT SELECT 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.

3. 4. 5. 6.

SELECT. Diikuti oleh <select_list>, 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 tabel mana. Tabel pada FROM dapat diikuti dengan alias untuk mempermudah penulisan khususnya ketika join dan subquery. WHERE. Diikuti oleh kondisi secara umum. GROUP BY. Diikuti oleh <select_list>. Bagian ini muncul ketika ada fungsi-fungsi agregasi. HAVING. Diikuti oleh kondisi hanya untuk fungsi-fungsi agregasi. ORDER BY. Diikuti oleh <select_list>.


pg.5

SQL (sekuel) Kode 1.1 SELECT [FROM [WHERE [GROUP BY [HAVING [ORDER BY

<select_list> ] [AND/OR ]] <select_list>] [AND/OR ]] <select_list>]

Select_list Merupakan daftar select. Dapat berupa literal value atau column_list. Perhatikan contoh berikut: Kode 1.2 SELECT

1,'STIKOM Surabaya';

Contoh pada Kode 1.2mengembalikan 1 baris data dengan 2 kolom. <select_list> 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. Mengambil data dari tabel (data retrieval) Fungsi utama dari SELECT adalah untuk pengambilan data (data retrieval) yang tersimpan dalam (beberapa) tabel. Perhatikan contoh berikut: Kode 1.3 SELECT FROM

1'1','STIKOM Surabaya'STIKOM, nim,nim+'@stikom.edu'EMAIL mahasiswa;

Hasil: 1 1 1 .. 1

pg.6

STIKOM STIKOM Surabaya STIKOM Surabaya … STIKOM Surabaya

nim 05390102208 05410104001 … 07410104800

EMAIL [email protected] [email protected] … [email protected]


SQL (sekuel) Contoh pada Kode 1.3menampilkan seluruh data di tabel Mahasiswa. Tanpa kata kunci FROM, query tersebut menghasilkan error. Muncul pertanyaan, bagaimana jika ada data yang sama, tetapi ingin ditampilkan satu kali? Misal, kebutuhan untuk mengetahui siapa saja konsumen yang telah melakukan pembelian. Perhatikan contoh berikut: Kode 1.4 SELECT FROM

nim nilai;

Hasil dari querypada Kode 1.4tersebut adalah banyak NIMyang sama. Untuk mengeliminasi data-data yang sama, dibutuhkan DISTINCT. Dengan mengimplementasikan DISTINCT, querypada Kode 1.4menjadi seperti ini: Kode 1.5 SELECT FROM

DISTINCTnim 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 diambil hanyalah sebuah data, contohnya data karyawan milik Tegar Heru Susilo. Untuk efektifitas query, perlu ditambahkan kata kunci WHERE.Perhatikan contoh berikut: Kode 1.6 SELECT FROM WHERE

* mahasiswa nama='Alif';

Hasil: nim 05410104001

nama Alif

alamat Jl. Jagir 20


kota Surabaya

jns_kel.. P

sts_nikah B

pg.7

SQL (sekuel) Penyaringan data membutuhkan kondisi. Ada 3 bagian dalam penulisan kondisi, antara lain (1) expression, (2) operator pembanding, dan (3) value. Sedangkan nilai yang dihasilkan dari kondisi ini ada 3 macam, 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). Dari 3 bagian kondisi, yang perlu diperhatikan adalah operator pembanding. Operator pembanding menentukan nilai akhir kondisi (TRUE, FALSE, atau UNKNOWN). Dalam kode 1.6, operator pembanding yang digunakan adalah ‘sama dengan’ (=). Selain ‘sama dengan’, beberapa operator pembanding lainnya dapat dilihat padaTabel 1.1. Tabel 1.1 Operator Pembanding

Operator = > >= < <= <> atau != BETWEEN .. AND .. IN (set) LIKE IS NULL

Keterangan Sama dengan Lebih besar dari Lebih besar sama dengan Kurang dari Kurang dari sama dengan Tidak sama dengan Diantara 2 nilai Cocok dengan salah satu diantara daftar nilai Cocok dengan pola karakter Sama dengan NULL

Beberapa contoh penggunaan operator pembanding dapat dilihat pada query berikut: Kode 1.7 SELECT FROM WHERE

pg.8

* mahasiswa nim BETWEEN'05390102208'AND '05410104190';


SQL (sekuel) SELECT WHERE

*FROMmahasiswa nim IN('05390102208','05410104190');

SELECT WHERE

*FROMmahasiswa naam LIKE'%amb%';

SELECT WHERE

*FROMmahasiswa alamat ISNULL;

Penggunaan operator BETWEEN dan IN pada Kode 1.7menghasilkan data yang sama. Sedangkan untuk LIKE akan menghasilkan seluruh mahasiswa yang namanya mempunyai kata “amb” di tengah-tengahnya. Sedikit berbeda dengan penggunaan operator lain yang membutuhkan data yang presisi, LIKE menggunakan wildcards untuk mencari data yang mirip dengan data yang dicari. Wildcards ini dibagi menjadi 2 yaitu (1) % untuk merepresentasikan banyak karakter, dan (2) _ untuk merepresentasikan satu karakter. Contoh penggunaan _ bisa dilihat pada contoh berikut: Kode 1.8 SELECT FROM WHERE

* mahasiswa nama LIKE'_e%';

Pada Kode 1.8, query akan menghasilkan seluruh data mahasiswa yang karakter kedua namanya memiliki huruf e. Bagaimana jika kondisi yang dibutuhkan berjumlah lebih dari satu? Jawabannya adalah dengan menggunakan operator logika untuk memisahkan kondisi-kondisi tersebut. Operator logika memakai tabel kebenaran sebagai acuan dalam pembentukan nilai akhir seluruh kondisi. Perhatikan contoh berikut: Kode 1.9 SELECT FROM WHERE

nim, nama, kota mahasiswa kota='Surabaya'and nim='05390102208';

Hasil: nim 05390102208

nama Cinta

kota Surabaya


pg.9

SQL (sekuel) Ada 3 macam operator logika yaitu AND, OR dan NOT. Untuk AND, nilai TRUE muncul jika semua kondisi TRUE. Sedangkan untuk OR, nilai FALSE muncul jika semua kondisi FALSE. Sedangkan untuk NOT, merupakan kebalikan dari nilai akhir kondisi. Ada prioritas dalam penggunaan kondisi seperti yang terlihat padaTabel 1. 2, namun prioritas ini dapat diabaikan dengan menggunakan parentheses (tanda kurung). Tabel 1. 2 Prioritas Penggunaan Operator Pembanding

Prioritas 1 2 3

Keterangan NOT AND OR

Pengurutan data Dalam visualisasi informasi, hasil pemrosesan data dapat lebih mudah dibaca ketika data tersebut dapat diurutkan berdasarkan nilai tertentu. Sebagai contoh ketika dalam tabel “Nilai”, seorang dosen ingin melihat 10 nilai UAS terbesar. Perhatikan contoh berikut: Kode 1.10 SELECT nim, uas FROM nilai WHERE nid='010103';

Cara seperti ini akan menampilkan data produk dengan urutan yang sama seperti urutan data di-insert-kan. Untuk dapat melihat urutan rangking harga produk, cukup dengan menambahkan kata kunci ORDER BY. Pengurutan ini bisa dibentuk dari kecil ke besar (ASCending) atau sebaliknya (DESCending). Sehingga query pada Kode 1.10berubah menjadi: Kode 1.11 SELECT FROM WHERE ORDERBY

pg.10

nim, kode_mk, uas nilai nid='010103' uas DESC;


SQL (sekuel) Hasil: nim 05390102208 06390102666 07390102802

kode_mk MK-001 MK-001 MK-001

uas 80 40 20

Operator Aritmatika Dalam menampilkan data, ada kalanya data yang ditampilkan merupakan hasil perhitungan missal mencari nilai total penjualan setelah PPn 10%. Untuk mendukung perhitungan ini, diperlukan operator aritmatika. Ada 4 macam operator aritmatika, yang dapat dilihat padaTabel 1.3. Tabel 1.3 Operator Aritmatika

Operator Aritmatika + * /

Keterangan Untuk operasi penambahan. Jika + ini dipakai untuk tipe data karakter, menjadi operasi penggabungan karakter. Untuk operasi pengurangan. Untuk operasi perkalian. Untuk operasi pembagian.

Contoh penggunaan operator aritmatika dapat dilihat pada query berikut: Kode 1.12 SELECT 1 + 1; SELECT

'Tegar'+' '+'Heru';

SELECT

5 – 1;

SELECT FROM

uas * 0.3 nilai;

SELECT FROM

tugas / 4 nilai;


pg.11

SQL (sekuel) Agregasi dan Pengelompokan Data Dalam pemrosesan data mentah menjadi data statistik, diperlukan fungsi-fungsi yang dapat meng-agregasi data-data tersebut. Fungsi-fungsi ini meliputi SUM, MIN, MAX, COUNT, dan AVG. SUM untuk menghitung jumlah nilai data, MIN untuk menentukan data paling kecil, MAX untuk menentukan data paling besar, COUNT untuk menghitung jumlah data, dan AVG untuk menghitung rata-rata nilai data. Dengan melihat pola data yang ada untuk kemudian dilakukan agregasi dan pengelompokan, visualisasi informasi dapat dilakukan dengan mudah. Sebagai contoh, untuk menampilkan rata-rata nilai UAS, nilai UAS terrendah, dan nilai UAS tertinggidapat dilihat pada query berikut: Kode 1.13 SELECT FROM

AVG(uas) rata_nilai, MAX(uas) nilai_tertinggi, MIN(uas) nilai_terendah nilai;

Hasil: rata_nilai 49.41

nilai_tertinggi 90

nilai terendah 0

Penggunaan GROUP BY mampu memilah data yang demikian besar kedalam kelompok-kelompok data untuk mendapatkan informasi-informasi yang lebih spesifik. Sebagai contoh, untuk menampilkan rata-rata nilai UAS, nilai UAS terrendah, dan nilai UAS tertinggi dari seluruh mata kuliah dapat dilihat pada query berikut: Kode 1.14 SELECT FROM GROUPBY

pg.12

kode_mk,AVG(uas) rata_nilai, MAX(uas) nilai_tertinggi, MIN(uas) nilai_terendah nilai kode_mk;


SQL (sekuel) Hasil: kode_mk MK-001 MK-002 MK-003 MK-102 MK-103

rata_nilai 46.66 43.33 54.00 65.00 42.00

nilai_tertinggi 80 70 90 80 80

nilai terendah 20 0 0 50 20

Untuk dapat menggunakan kata kunci GROUP BY dengan baik, ada beberapa aturan dalam pemakaian GROUP BY yang harus diperhatikan. Aturan-aturan tersebut antara lain: 1. 2. 3.

Jika ada agregasi (SUM, MIN, MAX, COUNT, AVG) dalam <select_list>, 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.

Dari data hasilKode 1.14, kita bisa melakukan filter misal hanya untuk mata kuliah yang rata-rata nilai UAS-nya dibawah 50. Hasil perubahan query pada Kode 1.14untuk proses filter, dapat dilihat pada query berikut: Kode 1.15 SELECT FROM GROUPBY HAVING

kode_mk,AVG(uas) rata_nilai, MAX(uas) nilai_tertinggi, MIN(uas) nilai_terendah nilai kode_mk AVG(uas)>50;

Hasil: kode_mk MK-003 MK-102

rata_nilai 54 65

nilai_tertinggi 90 80


nilai terendah 0 50

pg.13

SQL (sekuel) Untuk kondisi dalam fungsi agregasi, dibutuhkan kata kunci HAVING. HAVING mempunyai cara kerja yang sama dengan WHERE. Namun memiliki fungsi yang berbeda. Sehingga jika pada Kode 1.15 diberi kondisi hanya untuk MK-003, maka query menjadi seperti berikut ini: Kode 1.16 SELECT FROM WHERE GROUPBY HAVING

kode_mk,AVG(uas) rata_nilai, MAX(uas) nilai_tertinggi, MIN(uas) nilai_terendah nilai kode_mk ='MK-003' kode_mk AVG(uas)>50;

Hasil: kode_mk MK-003

rata_nilai 54

nilai_tertinggi 90

nilai terendah 0

Multiple-table Query Data-data yang tersimpan dalam database, tersebar kedalam beberapa tabel. Tabel-tabel ini dihubungkan dengan yang namanya referential constraint, yaitu hubungan antara constraintFOREIGN KEY dan constraintPRIMARY KEY. Karena itulah, untuk mendapatkan informasi yang tersebar, dibutuhkan metode untuk menggabungkan property tabel-tabel tersebut. Metode yang digunakan ada 2 macam, yaitu JOIN danSUBQUERY. Perbedaannya sederhana, JOIN menggunakan satu SELECT, sedangkan SUBQUERY menggunakan dua atau lebih SELECT (umumnya dikatakan sebagai SELECT within a SELECT). Join Bentuk joinpertama kali adalah menggunakan kata kunci WHERE untuk melakukan penggabungan tabel. Jadi tabel-tabel yang ingin digabungkan ditulis dalam kata kunci FROM, sedangkan pg.14


SQL (sekuel) penggabungannya ditulis dalam WHERE. Sintak joinmenggunakan WHERE adalah sebagai berikut:

untuk

Kode 1.17 SELECT <select_list> FROM , [, ...] WHERE [AND ...]

Contoh: Kode 1.18 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

Querypada Kode 1.18menghasilkan sejumlah baris yang merupakan kombinasi jumlah data antara mahasiswa dan nilai. Dari operator pembanding yang digunakan, sudah jelas bahwa query ini menghasilkan baris yang mempunyai data yang sama diantara duatable (produk dan kategori). Join jenis ini dapat juga disebut dengan equijoin. Perkembangan SQL ANSI sejak tahun 1990-an, menambahkan model baru dalam join, yaitu menggunakan hanya kata kunci FROM sebagai referensi utama baik untuk tabel maupun untuk penggabungannya. Sintak untuk join tipe ini adalah sebagai berikut: Kode 1.19 SELECT <select_list> FROM JOIN ON < table1.PK = table2.FK> [[AND ...] JOIN ...];


pg.15

SQL (sekuel) Contoh: Kode 1.20 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-data sederhana, namun berbeda halnya dengan data-data komplek dengan ruang lingkup yang besar. Ada 2 tipe join, yaitu INNER JOIN yang lebih menekankan pada data yang sama, dan OUTER JOIN (yang merupakan kebalikan dari INNER JOIN). Inner Join Tujuan utama dari inner joinadalah menyamakan nilai baris pada sebuah tabel dengan tabel lain menggunakan kolom yang sama (tipe dan panjang [jika ada]). 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. Cara yang dapat dipakai untuk menampilkan data Indah tersebut tanpa nilainya adalah dengan menggunakan outer join. Pengubahan sintak joinmenjadi outer join dapat dilihat pada Kode 1.21: Kode 1.21 SELECT <select_list> FROM [OUTER] JOIN ON [AND ...];

Dalam sintak tersebut, ada LEFT/RIGHT. Penggunaan LEFT akan memproses seluruh data yang ada pada tabel sebelah kiri pg.16


SQL (sekuel) kemudian dilanjutkan dengan data yang sama pada tabel sebelah kanan. Artinya, seluruh data pada tabel kiri ditampilkan semua, baru setelah itu data yang sama di tabel sebelah kanan, ditampilkan. Untuk data yang tidak ada di tabel sebelah kanan, ditampilkan kata NULL. Sedangkan RIGHT bekerja sebaliknya. Dengan demikian, outer join fokus pada nilai yang tidak sama antara kolom-kolom yang sama (tipe dan panjang [jika ada]) pada tabel yang di-join-kan. Nilai yang tidak sama, direpresentasikan oleh NULL. Sehingga dari contoh inner join, solusinya adalah mengubah query menjadi seperti berikut: Kode 1.22 SELECT m.nama, n.kode_mk, n.uas FROM mahasiswa m LEFTJOIN 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

Non-Equijoin Berbeda dengan equijoin yang selalu memakai operator ‘sama dengan’, non-equijoin memakai operator selain ‘sama dengan’. Contoh permasalahan misalnya kita ingin mencari mahasiswa dengan angkatan lebih muda dari Entin. Perhatikan query berikut: Kode 1.23 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';


pg.17

SQL (sekuel) Hasil: Namaproduk Kemeja Lengan Panjang Jepit Rambut

hargapersatuan 25000.00 10000.00

Subquery Subquery Merupakan querydidalam 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: Kode 1.24 SELECT <select_list> FROM WHERE = (SELECT <single_column> FROM WHERE );

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

Kode 1.25merupakan versi subquery dariKode 1.23. Menghasilkan data yang sama namun dengan solusi yang berbeda. Solusi subquery pada Kode 1.25mencari terlebih dahuluangkatan si Entin. Hasil dari pencarian ini digunakan sebagai acuan untuk main query.

pg.18


SQL (sekuel)

SQL Function Fungsi-fungsi dalam SQL (bukan User Defined Function) lebih dikhususkan pada masing-masing penyedia layanan database. Secara umum, fungsi-fungsi dalam SQL Server dapat dibagi menjadi: 1.

2.

3.

4.

5.

Aggregation Mengembalikan nilai tunggalyang 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() 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()

Beberapa contoh penggunaan SQL Function dapat dilihat pada Kode 1.26. CONVERT digunakan untuk mengubah nilai 1000 dari bentuk numeric ke bentuk varchar agar bisa digabungkan dengan kata Rupiah. CEILING, FLOOR, dan ROUND digunakan masingmasing untuk pembulatan keatas, pembulatan kebawah, pembulatan berdasarkan nilai dibelakang koma (sesuai dengan tingkat presisi yg dipakai). GETDATE digunakan untuk mengambil tanggal sistem. DATEPART digunakan untuk Praktikum Pemrograman Basis Data

pg.19

SQL (sekuel) mengambil nilai bagian tertentu dalam tanggal seperti tahun, bulan, hari, jam, menit. DATEADD digunakan untuk menambahkan nilai tertentu pada bagian tertentu dalam tanggal. Sesuai contoh, DATEADD menambahkan nilai 2 pada tahun sekarang, sehingga nilai yang dihasilkan adalah 2016. Kode 1.26 SELECT CONVERT(VARCHAR, 1000)+' Rupiah.'; SELECT

FROM

LEN(nama), SUBSTRING(nama, 5, 2), UPPER(nama), LOWER(nama), LEFT(nama, 5), RIGHT(nama, 5) mahasiswa;

SELECT

CEILING(5.25),FLOOR(5.25), ROUND(5.25, 0),POWER(5,2),SQRT(25);

SELECT

GETDATE(), DATEPART('yyyy',GETDATE()), DATEADD('yyyy', 2,GETDATE());

Satu lagi fungsi yang bisa dipakai untuk melakukan conditional expression (untuk menggantikan peran IF … ELSE dalam programming), yaitu CASE. Sintak CASE dapat dilihat pada kode berikut: Kode 1.27 CASE WHEN THEN <statement> [WHEN THEN <statement>] [WHEN THEN <statement>] ... ELSE <statement> END

Pada Kode 1.27, kondisi dituliskan dalam kata kunci WHEN dan apa yang dilakukan (jika kondisi bernilai TRUE) dituliskan dalam kata kunci THEN. Jika ada kondisi lain, dituliskan dalam kata kunci ELSE. Masih banyak fungsi-fungsi lain yang diberikan oleh SQL Server untuk membantu dalam pengolahan data. pg.20


SQL (sekuel)

Transaction Mungkin 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). Penambahan data Sebelum melakukan penambahan data, perlu diperhatikan halhal sebagai berikut: 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. Sintak INSERT dapat dilihat pada. Kode 1.28 INSERT INTO [()] VALUES ();

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


pg.21

SQL (sekuel) PadaKode 1.29, tidak diberikan dengan asumsi bahwa data dimasukkan kedalam seluruh kolom. Untuk menambahkan data dengan mengabaikan beberapa kolom tabel tujuan, bisa mengikuti contoh berikut: Kode 1.30 INSERT INTO mahasiswa (nim, nama) VALUES ('08410104003','Jenny');

Bagaimana jika data yang dimasukkan adalah data dari tabel lain? Untuk menjawab pertanyaan ini, SQL Server menggunakan perintah INSERT INTO … SELECT. Modifikasi data Modifikasi data diperlukan ketika terjadi perubahan pada data orisinilnya. Untuk memodifikasi data, dibutuhkan perintah UPDATE. Perintah ini dapat memodifikasi satu, banyak, atau semua baris data dalam sebuah tabel. Jumlah data yang dimodifikasi bergantung pada kondisi (kriteria) yang diberikan dalam query UPDATE. Sintak UPDATE dapat dilihat pada kode berikut: Kode 1.31 UPDATE SET = [, = ] WHERE ;

Perhatikan contoh berikut: Kode 1.32 UPDATE SET WHERE

mahasiswa sts_nikah ='M' nim ='08410104003';

Kode 1.32mencoba memodifikasi data mahasiswa. Modifikasi dilakukan untuk mengubah status nikahmahasiswa, dengan nim 08410104003, dari yang awalnya B menjadi M. Jadi bisa dilihat bahwa nilai yang diberikan pada kata kunci SET merupakan nilai baru.

pg.22


SQL (sekuel) Penghapusan data Ada kalanya data yang sudah ada tidak diperlukan lagi, sehingga data tersebut harus dihapus dari tabel. Untuk menghapus data, digunakan perintah DELETE. Perintah ini mempunyai perlakuan yang sama seperti UPDATE. Yang perlu diperhatikan disini adalah adanya referential constraint (hubungan antara PK dan FK). Dalam arti kata lain, data tidak dapat dihapus ketika data tersebut dipakai sebagai data acuan dalam tabel lain(melalui fitur foreign key). Sintak DELETE dapat dilihat pada kode berikut: Kode 1.33 DELETE WHERE ;

Contoh sederhananya adalah sebagai berikut: Kode 1.34 DELETE WHERE

mahasiswa nim ='08410104003';

Kode 1.34mencoba untuk menghapus data mahasiswa dengan nim 08410104003. Penghapusan bisa sukses jika data mahasiswa tersebut tidak dipakai di tabel Nilai.


pg.23

SQL (sekuel)

Latihan 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.

13. 14.

Tampilkan seluruh data mahasiswa. Tampilkan data mahasiswa yang tinggal di kota Surabaya. Tampilkan nama mahasiswa yang mempunyai huruf a atau huruf A. Tampilkan nama mahasiswa yang huruf kedua terakhir dari namanya mempunyai huruf n. Tampilkan nim, namadan nilai tugas mahasiswa. Tampilkan 3 karakter terakhir dari nama mahasiswa. Buat email berdasarkan nama mahasiswa dan gabungkan dengan @gmail.com. Hitung jumlah mahasiswa masing-masing dosen. Ambil datanya dari tabel nilai. Tampilkan nama mahasiswa dan nama dosen untuk dosen dengan nid 010306. Tampilkan mahasiswa yang mempunyai dosen yang sama dengan Entin. Tampilkan mahasiswa yang tidak memiliki nilai. Masukkan data mahasiswa berikut ini: NIM: nim Anda Nama: nama lengkap Anda Alamat: alamat rumah/kos Anda Kota: sesuai alamat Jns_kelamin: jenis kelamin Anda Sts_nikah: M Ubah status nikah Anda dari M menjadi B. Hapus data Anda.

pg.24


BABI SQL (sekuel) Sub-Materi: Select Join Subquery DML

Recommend Documents