Structured Query Language
DML (Data Manipulation Language)
31 Mei 2006
© 2006, M. Ramadhan
1
Pendahuluan Yang akan dibahas: Data Manipulation Language (DML) Pernyataan SQL untuk: • • • •
31 Mei 2006
Menemukan kembali data dalam basis data Menyisipkan data baru ke dalam basis data Memodifikasi data dalam basis data Menghapus data dari dalam basis data
© 2006, M. Ramadhan
2
Tujuan Mahasiswa diharapkan : Memahami kegunaan DML. Mampu menuliskan pernyataan SQL untuk : • • • •
Menemukan kembali data dalam basis data Menyisipkan data baru ke dalam basis data Memodifikasi data dalam basis data Menghapus data dari dalam basis data
31 Mei 2006
© 2006, M. Ramadhan
3
DML (Data Manipulation Language) Bahasa untuk memanipulasi data, yaitu
untuk: • • • •
Menemukan kembali data dalam basisdata Menyisipkan data baru ke dalam basisdata Memodifikasi data dalam basisdata Menghapus data dari dalam basisdata
Ada dua jenis DML, yaitu:
• DML prosedural • DML non-prosedural 31 Mei 2006
© 2006, M. Ramadhan
4
DML: Pernyataan SQL Select Insert Update Delete
31 Mei 2006
Me-retrieve baris data Menambah baris data Mengubah data Menghapus baris data
© 2006, M. Ramadhan
5
Pernyataan Select (1)
Syntax Select From Where Group By Having Order By 31 Mei 2006
daftar-kolom daftar-tabel kriteria daftar-kolom kriteria daftar-kolom © 2006, M. Ramadhan
6
Pernyataan Select (2) Memilih Kolom Memilih semua kolom, contoh: Select * From Buku Memilih kolom tertentu, misalnya : Select ISBN, Judul, ThTerbit From Buku 31 Mei 2006
© 2006, M. Ramadhan
7
Pernyataan Select (3) Memilih Baris Memilih baris berdasarkan kriteria tertentu. Misalnya menemukan kembali data buku dari
tabel Buku yang terbit tahun 2005 ke atas: Select * From Buku Where (ThTerbit >= 2005)
31 Mei 2006
© 2006, M. Ramadhan
8
Pernyataan Select (4) Memilih Baris dan Kolom Memilih kolom tertentu dan baris berdasarkan
kriteria. Misalnya menemukan kembali data kolom Judul dan ThTerbit dari tabel Buku yang terbit pada tahun 2005 : Select Judul, ThTerbit From Buku Where (ThTerbit = 2005) 31 Mei 2006
© 2006, M. Ramadhan
9
Pernyataan Select (5) Retrieve Data dari View Menemukan kembali data dapat dilakukan pa-
da view yang telah didefinisikan sebelumnya. Misalnya menemukan kembali data buku yang terbit pada tahun 2005 dari view JudulTh : Select * From JudulTh Where (ThTerbit = 2005)
31 Mei 2006
© 2006, M. Ramadhan
10
Pernyataan Select (6) Mencegah Duplikasi Data Bila kolom primary key tidak diikutsertakan,
boleh jadi menghasilkan duplikasi data. Pernyataan berikut boleh jadi memberikan data ThTerbit yang harganya sama berkali-kali : Select ThTerbit From Buku
Untuk mencegah duplikasi data, tambahkan kata Distinct setelah pernyataan Select Select Distinct ThTerbit From Buku 31 Mei 2006
© 2006, M. Ramadhan
11
Pernyataan Select (7) Konstanta, Ekspresi, Fungsi Pernyataan Select, selain nama kolom, dapat
juga diikuti dengan konstanta, ekspresi, fungsi. Contoh berikut, menghasilkan Nama, GajiPokok, teks ‘Rp’ dan tunjangan sebesar 25%xGajiPokok Select Nama, ‘ Rp ‘, GajiPokok, ‘ Rp ‘, 0.25*GajiPokok From Pegawai 31 Mei 2006
© 2006, M. Ramadhan
12
Pernyataan Select (8) Ekspresi Ekspresi dapat berupa: konstanta, kolom, atau fungsi. kombinasi dari konstanta dan atau kolom dan atau fungsi, dengan operator. Operator yang digunakan: Operator aritmetik: +, −, *, /, %, untuk numerik Operator string: + untuk menggabungkan string Operator perbandingan: <, <=, >, >=, <>, = dan atau operator logika: Not, And, Or, In, Between, Like, untuk ekspresi boolean 31 Mei 2006
© 2006, M. Ramadhan
13
Pernyataan Select (9) Fungsi Skalar Fungsi bekerja berdasarkan harga dari satu
baris untuk satu waktu. Fungsi skalar yg ada dalam MS SQL Server : Fungsi konversi tipe data Fungsi string Fungsi matematik Fungsi tanggal dan waktu Fungsi user Fungsi tambahan lainnya
31 Mei 2006
© 2006, M. Ramadhan
6 buah 22 buah 23 buah 11 buah 10 buah 82 buah 14
Pernyataan Select (10) Fungsi Agregasi (1) Fungsi bekerja berdasarkan harga dari
sekumpulan baris untuk satu waktu. Misalnya menghitung jumlah pegawai, ratarata gaji, total gaji seluruh pegawai, dari tabel Pegawai, pernyataan SQL-nya adalah: Select Count(*), Avg(Gaji), Sum(Gaji) From Pegawai 31 Mei 2006
© 2006, M. Ramadhan
15
Pernyataan Select (11) Fungsi Agregasi (2) Fungsi
Deskripsi
Avg (ekspresi)
menghitung rata-rata harga ekspresi yang tidak NULL menghitung jumlah baris identik dg Count tetapi harga yang diberikan bertipe BigInt menghitung jumlah harga ekspresi yang tidak NULL identik dg Count(ekspresi) tetapi harga yang diberikan bertipe BigInt
Count (*) Count_Big (*) Count (ekspresi) Count_Big(ekspresi)
31 Mei 2006
© 2006, M. Ramadhan
16
Pernyataan Select (12) Fungsi Agregasi (3) Fungsi
Deskripsi
Count (Distinct ekspresi)
menghitung jumlah harga ekspresi tidak NULL yg unik identik dengan fungsi
Count_Big (Distinct ekspresi)
Count(Distinct ekspresi)
Sum (ekspresi)
31 Mei 2006
tetapi harga yang diberikan bertipe BigInt menghitung total harga ekspresi tidak NULL © 2006, M. Ramadhan
17
Pernyataan Select (13) Fungsi Agregasi (4) Fungsi
Deskripsi
menghitung harga maksimum ekspresi yang tidak NULL Min (ekspresi) menghitung harga minimum ekspresi yang tidak NULL StdDev (ekspresi) menghitung deviasi standar ekspresi yang tidak NULL StdDevP (ekspresi) identik dg StdDev ttp dari populasi Var (ekspresi) menghitung varian ekspresi yang tidak NULL VarP (ekspresi) identik dg Var ttp dari populasi Max (ekspresi)
31 Mei 2006
© 2006, M. Ramadhan
18
Pernyataan Select (14) Klausa Group Digunakan untuk mengelompokkan baris Misalnya menghitung jumlah pegawai, rata-
rata gaji, total gaji untuk setiap kelompok golongan pegawai : Select Count(*), Avg(Gaji), Sum(Gaji) From Pegawai Group By Golongan 31 Mei 2006
© 2006, M. Ramadhan
19
Pernyataan Select (15) Klausa Having Digunakan untuk membatasi baris yang
dihasilkan fungsi agregasi pada group baris. Contoh, menghitung gaji rata2 dan total gaji setiap kelompok golongan pegawai, tetapi dibatasi u/ golongan yg rata2 gaji > 1000000 Select Avg(Gaji), Sum(Gaji) From Pegawai Group By Golongan Having Avg(Gaji) > 1000000 31 Mei 2006
© 2006, M. Ramadhan
20
Pernyataan Select (16) Klausa Having vs Klausa Where Klausa Having mirip dengan klausa Where Select Avg(Gaji), Sum(Gaji) From Pegawai Group By Golongan Having Golongan In (1,3,4)
Memberikan hasil yang sama dengan : Select Avg(Gaji), Sum(Gaji) From Pegawai Where Golongan In (1,3,4) Group By Golongan 31 Mei 2006
© 2006, M. Ramadhan
21
Pernyataan Select (17) Klausa Order By Digunakan untuk mengurutkan baris
berdasarkan sebuah kolom atau lebih. Secara default, data diurutkan secara menaik Misalnya mengurutkan data pegawai secara menaik berdasarkan Nama pegawai: Select * From Pegawai Order By Nama 31 Mei 2006
© 2006, M. Ramadhan
22
Pernyataan Select (18) Klausa Order By (2) Untuk mengurutkan secara menurun, tambahkan Desc setelah nama kolom. Misalnya mengurutkan data pegawai secara
menurun berdasarkan Nama pegawai: Select * From Pegawai Order By Nama Desc 31 Mei 2006
© 2006, M. Ramadhan
23
Pernyataan Select (19) Klausa Order By (3) Bila diperlukan, pengurutan dapat dilakukan
berdasarkan beberapa kolom. Pada contoh sebelumnya, boleh jadi ada nama pegawai yang sama. Misalnya, untuk nama yang sama diurutkan lagi berdasarkan Golongan secara menurun: Select * From Pegawai Order By Nama, Golongan Desc 31 Mei 2006
© 2006, M. Ramadhan
24
Memodifikasi Data Tabel Tiga pernyataan SQL u/ memodifikasi data tabel:
Insert
Update
Delete
menambah baris baru ke dalam tabel mengubah data baris tertentu menghapus baris dari dalam tabel
Ketiga pernyataan dapat memodifikasi sebuah
atau sekumpulan baris dalam sebuah tabel. Memodifikasi lebih dari satu tabel dalam satu pernyataan tunggal tidak dapat dilakukan Modifikasi data dapat juga melalui view. 31 Mei 2006
© 2006, M. Ramadhan
25
Pernyataan Insert (1) Digunakan untuk menambah baris baru Syntax: Insert Into NamaTabelAtauView (Kolom1, Kolom2, ...) Values (Nilai1, Nilai2, ...) 31 Mei 2006
© 2006, M. Ramadhan
26
Pernyataan Insert (2) Misalnya menambah baris ke dalam tabel Buku: Insert Into Buku (ISBN, Judul, ThTerbit) Values (‘0764575368’, ‘Professional VB 2005’, 2005) 31 Mei 2006
© 2006, M. Ramadhan
27
Pernyataan Insert (3) Nilai yang diberikan boleh berupa ekspresi
atau NULL. Insert Into Pegawai (NRP, Nama, TglLahir, Gaji, Tunjangan) Values (‘0764575368’,’Fulan’ NULL, 1275000, 0.25*1275000) 31 Mei 2006
© 2006, M. Ramadhan
28
Pernyataan Insert (4) Penambahan data dapat diambil dari tabel lain. Meng-copy semua baris data dari tabel Buku
ke dalam tabel BukuBaru dengan tambahan kolom baru Penerbit. Insert Into BukuBaru (ISBN,Judul,Penerbit,ThTerbit) Select ISBN, Judul,’ ‘,ThTerbit From Buku
31 Mei 2006
© 2006, M. Ramadhan
29
Pernyataan Insert (5) Meng-copy semua baris data buku yang
terbit pada tahun 2005 atau 2006 dari tabel Buku ke dalam tabel BukuBaru dengan tambahan kolom baru Penerbit. Insert Into BukuBaru (ISBN,Judul,Penerbit,ThTerbit) Select ISBN, Judul, ’ ‘,ThTerbit From Buku Where (ThTerbit in (2005,2006)) 31 Mei 2006
© 2006, M. Ramadhan
30
Pernyataan Update (1) Digunakan untuk mengubah satu baris atau
sekelompok baris. Syntax: Update NamaTabel Set NamaKolom1 = Ekspresi1, NamaKolom2 = Ekspresi2, ... Where kriteria 31 Mei 2006
© 2006, M. Ramadhan
31
Pernyataan Update (2) Mengubah suatu baris berdasarkan harga
atribut primary key Misalnya mengubah Judul dan ThTerbit buku yang ISBN-nya ‘0764575368’ Update Buku Set Judul = ‘Professional MSVB 2005’, ThTerbit = 2006 Where ISBN =‘0764575368’ 31 Mei 2006
© 2006, M. Ramadhan
32
Pernyataan Update (3) Mengubah sekelompok baris berdasarkan
kriteria tertentu Misalnya menaikkan TunjTransport sebesar 50% untuk semua Pegawai Golongan 1 dan 2 Update Pegawai Set TunjTransport = 1.5* TunjTransport Where Golongan in (1,2)
31 Mei 2006
© 2006, M. Ramadhan
33
Pernyataan Update (4) Mengubah semua baris dalam sebuah tabel
tanpa kecuali. Misalnya menaikkan TunjTransport sebesar 50% untuk semua Pegawai. Update Pegawai Set TunjTransport = 1.5* TunjTransport
31 Mei 2006
© 2006, M. Ramadhan
34
Pernyataan Update (5) Mengosongkan (memberi harga Null) kolom
tertentu. Misalnya mengosongkan nilai kolom ThTerbit yang ThTerbit di atas tahun 2006: Update Buku Set ThTerbit = Null Where ThTerbit > 2006
31 Mei 2006
© 2006, M. Ramadhan
35
Pernyataan Delete (1) Digunakan untuk menghapus:
satu baris berdasarkan harga primary key sekelompok baris berdasarkan kriteria tertentu semua baris dari sebuah tabel
Syntax:
Delete From NamaTabel Where kriteria 31 Mei 2006
© 2006, M. Ramadhan
36
Pernyataan Delete (2) Menghapus suatu baris berdasarkan harga
atribut primary key Misalnya menghapus satu baris data Buku yang ISBN-nya ‘0764575368’ Delete From Buku Where ISBN =‘0764575368’
31 Mei 2006
© 2006, M. Ramadhan
37
Pernyataan Delete (3) Menghapus sekelompok baris berdasarkan
kriteria tertentu Misalnya menghapus semua baris dari tabel Buku yang ThTerbit di atas tahun 2006: Delete From Buku Where ThTerbit > 2006
31 Mei 2006
© 2006, M. Ramadhan
38
Pernyataan Delete (4) Menghapus semua baris (mengosongkan)
sebuah tabel. Misalnya mengosongkan tabel Buku: Delete From Buku
Atau gunakan pernyataan: Truncate Table Buku
31 Mei 2006
© 2006, M. Ramadhan
39
Yang Sudah Dipelajari Bahasa basis data: DML Pernyataan SQL: Select Insert Update Delete
31 Mei 2006
Me-retrieve baris data Menambah baris data Mengubah data Menghapus baris data
© 2006, M. Ramadhan
40
Daftar Pustaka Connolly, Thomas., et.al., 2002. Database System. Wokingham England, AddisonWesley Publishing Company. Vieira, Robert, 2006. Beginning SQL Server 2005 Programming. Indianapolis, Wiley Publishing, Inc. Otey, Michael dan Conte, Paul, 2001. SQL Server Developer's Guide, Berkeley, Osborne / McGraw-Hill. 31 Mei 2006
© 2006, M. Ramadhan
41
TAMAT © 2006, M. Ramadhan
31 Mei 2006
© 2006, M. Ramadhan
42