Intro : Structure Query Language [SQL]
Apa itu SQL? SQL merupakan suatu bahasa (language) yang digunakan untuk mengakses database Hampir semua software database mengenal atau mengerti SQL. perintah SQL pada semua software database hampir sama.
Perintah SQL Terdapat 3 (tiga) jenis perintah SQL, yaitu : DDL atau Data Definition Language DDL merupakan perintah SQL yang berhubungan dengan pendefinisian suatu struktur database, dalam hal ini database dan table.
DML atau Data Manipulation Language DML merupakan perintah SQL yang berhubungan dengan manipulasi atau pengolahan data atau record dalam table.
DCL atau Data Control Language DCL merupakan perintah SQL yang berhubungan dengan manipulasi user dan hak akses (priviledges).
DDL (Data Definition Language) Create Databases Create Tables Create Indexes Altering Tables Dropping Tables/Indexes
Create Tables statement CREATE TABLE table (An Dn, An Dn, .., An Dn) Keterangan : tabel = nama tabel yang akan dibuat
A1..An = atribut atau variabel field D1, Dn= tipe data untuk A1..An
Membuat tabel siswa CREATE TABLE siswa
(NISN char(6), nama_siswa char(30), tgl_lahir date(), tempat_lahir char(30), KLS char(2), alamat char(30), nama_ortu_wali char(30))
Membuat tabel Customer
CREATE TABLE tblCustomers ( customerID INTEGER NOT NULL, [Last name] CHAR(30) NOT NULL, [First name] CHAR(30) NOT NULL, Phone CHAR(12), Email CHAR (50))
Membuat tabel Pelanggan CREATE TABLE pelanggan (id_pelanggan varchar(5) NOT NULL, nm_pelanggan varchar(30) NOT NULL, alamat text, telepon varchar (20), email varchar (50), PRIMARY KEY(id_pelanggan));
Buat tabel Pegawai
Dengan attribut NIP, nama pegawai, tgl_lahir, tempat_lahir, Jenis_kelamin, alamat, Golongan
Buat tabel Pegawai
DDL
CREATE TABLE pegawai ( NIP char(6) NOT NULL, nama_pegawai char(30) NOT NULL, tgl_lahir date() NOT NULL, tempat_lahir char(30) NOT NULL, Jenis_kelamin char(9) NOT NULL, alamat char(30) NOT NULL, Golongan char(1) NOT NULL )
DDL
Index
Index adalah struktur data eksternal yang digunakan untuk mengurutkan atau mengatur pointer data dalam sebuah table Peringatan : Jika kita menggunakan beberapa index pada suatu tabel akan menunjukkan penurunan performa dikarenakan extra overhead dalam pemeliharaan indexnya.
Index
Selian itu : Banyak menggunakan index dapat menyebabkan masalah penguncian record, bila digunakan dalam peralatan multiuser. Dengan demikian :
Gunakanlah index dalam konteks yang benar, sebuah index dapat memperbaiki performa lebih tinggi sebuah aplikasi.
Gunakan pernyataan CREATE INDEX untuk membuat index. Structure pembuatan index adalah :
CREATE INDEX nama_index ON nama_tabel (nama_field) Contoh membuat index pada tabel customers dalam database invoicing sebagai berikut :
CREATE INDEX idxCustomerID ON tblCustomers (CustomerID)
Pengurutan menggunakan INDEX secara baku data diurutkan dari kecil ke besar. Jika Anda ingin mengurutkan data dari nilai terbesar ke nilai terkecil. Strukturnya adalah :
CREATE INDEX nama_index ON nama_tabel (nama_field DESC) Contoh membuat index pada tabel customers dalam database invoicing sebagai berikut :
CREATE INDEX idxCustomerID ON tblCustomers (CustomerID DESC)
CREATE INDEX idxCustomerID ON tblCustomers (CustomerID) WITH PRIMARY CREATE INDEX idxCustomerName ON tblCustomers ([Last Name], [First Name]) WITH PRIMARY
CREATE UNIQUE INDEX nama_index ON nama_tabel (nama_field)
Contoh : CREATE UNIQUE INDEX idxCustomerPhone ON tblCustomers (Phone)
DROP INDEX nama_index ON nama_tabel Contoh : DROP INDEX idxCustomerPhone ON tblCustomers
DROP TABLE nama_tabel Contoh :
DROP TABLE tblCustomers
Altering Tables Structure ALTER TABLE tabel
ADD | MODIFY (An dn, An dn, .., An dn);
DDL
ALTER TABLE tblCustomers ADD COLUMN Address TEXT(30) Mengganti ukuran field : ALTER TABLE tblCustomers ALTER COLUMN Address TEXT(40)
DDL
Altering Tables & DROP Menghapus Field : ALTER TABLE tblCustomers DROP COLUMN Address
DML ( Data Manipulation Language) • Insertion : Menyisipkan data record ke dalam suatu tabel • Updating : Memperbaiki data record dalam suatu tabel • Deletion : Menghapus data record pada suatu tabel • Selection : Menampilkan data record dari suatu tabel
INSERT INTO tabel (A1, A2, .., An ) VALUES (C1, C2, …,Cn-1, Cn) CONTOH : INSERT INTO siswa ( NISN, nm_siswa, nilai ) VALUES ( 123456, ‘Fadhel Muhammad’, 89 );
SELECT A1, A2, ………,An-1, An FROM T1, T2, ………,Tn-1, Tn WHERE Criteria GROUP BY A1, A2, ………,An-1, An HAVING Criteria_Agregate_function ORDER BY Criteria_A
fungsi agregrasi seperti COUNT, SUM, AVG
UPDATE tabel SET assignments WHERE Criteria CONTOH :
UPDATE siswa SET nilai = 89 WHERE NISN =‘123456’;
DELETE FROM Tabel WHERE Criteria CONTOH :
DELETE FROM siswa WHERE NISN = ‘123456’;
CDL (Control Definition Language)
GRANT Memberikan otoritas (hak akses) manipulasi data pada suatu tabel (database) kepada user REVOKE Mencabut otoritas (hak akses) manipulasi data pada suatu tabel (database) dari user
GRANT statement structure
GRANT
ON TO <user_name>
Grant Type : insert ,
select,
update ,
delete ,
all
GRANT for insert
GRANT insert ON TO <user_name>
Contoh : GRANT insert ON siswa TO fadhel
GRANT insert ON siswa TO agung, fadhel, septi GRANT insert ON siswa TO all
GRANT for update
GRANT update ON TO <user_name>
Contoh : GRANT update ON siswa TO fadhel
GRANT update (NIM, nm_kul) ON siswa TO agung, septi
GRANT for select
GRANT select ON TO <user_name> Contoh : GRANT select ON siswa TO fadhel
GRANT insert, select ON siswa TO septi GRANT all ON siswa TO ani
GRANT for all and public
GRANT all ON TO <user_name> GRANT ON TO public Contoh :
GRANT all ON siswa TO ani GRANT select ON siswa TO public
REVOKE structure
REVOKE ON FROM <user_name>
Revoke Type : insert ,
select,
update ,
delete ,
all
REVOKE for insert
REVOKE insert ON FROM <user_name> Contoh :
REVOKE insert ON siswa FROM ruben REVOKE insert ON siswa FROM public
REVOKE for select
REVOKE select ON FROM <user_name>
Contoh :
REVOKE select ON siswa FROM septi
REVOKE insert, select ON siswa FROM fadhel REVOKE select, delete ON siswa FROM public
Complex
Integrity Constraints - Constraints over single table - Domain constraints ICs over several tables IF conditional into query Aggregate function
Model Relasional
Menjaga integritas atau satu kesatuan data dalam suatu database, gunakan kunci utama (Primary key) dan kunci tamu (Foreign key). • Primary key :
Adalah atribut kunci yang dapat menunjukkan identitas informasi dari atribut yang bersangkutan. • Foreign key : Adalah atribut kunci milik relasi utama yang disisipkan pada relasi transaksi untuk menunjukkan relationship antara relasi transaksi dengan relasi utama.
Model Relasional
Tujuan utama : Primary key dan Foreign key digunakan untuk relationships antar relasi.
Untuk menjaga integritas data antar relasi keduanya.
Model Relasional
NIM
NAMA
JURUSAN
12345
AGUS SANTOSO
DKP
12346
DIAN KURNIA
TKJ
12347
MARIMAR
NIM
KODE+MK
NILAI
12345
TKJ-01
A
12346
TKJ-01
B
12347
TIF-03
B
TI
Model Relasional
• Introduction to Data Integrity
Mendefinisikan himpunan aturan integritas data adalah penting, pendefinisian ini dilakaukan oleh database administrator atau application developer. Sebagai contoh data integrity, dengan pertimbangan tables employees and departments dengan business rules untuk informasi pada setiap table sebgai ilustrasi sbb :
Model Relasional Examples of Data Integrity
DEPNO 20 30
EMPNO 6666 7665 9876 1234 1345
EMPNAME MULDER SMITH ALLEN WARDS JONES
SALES 6500.00 9000.00 7500.00 5000.00 2975.00
COMM
100.00 200.00 400.00
DEPNO 20 20 30 30 30
DNAME RESEARCH SALES
LOC DALAS CHICAGO
Model Relasional
Types of Data Integrity Bagian ini menggambarkan atauran yang dapaat diterapkan pada kolom tabel ayang menekankan perbedaan tipe data pada integritas data.
Null Rule Aturan null adalah definisi aturan pada single column yang membolehkan atau tidak membolehkan inserts atau updates untuk pengisian rows kosong (the absence of a value) pada kolom ini. Unique Column Values Aturan nilai unique didefinisan pada sebuah column (or set of columns) yang membolehkan insert or update hanya pada row jika itu berisi sebuah nilai unique dalam sebuah kolomcolumn (or set of columns).
Model Relasional
Types of Data Integrity
Primary Key Values Aturan nilai primary key didefinisikan pada sebuah key (a column or set of columns) tertentu bahwasetiap each row dalam table dapat mengidentifikasi keunikan dengan nilai kunci tersebut Referential Integrity Rules Aturan referential integrity adalah definsi aturan pada sebuah kunci key (a column or set of columns) dalam sebuah table yang menjamin bahwa data dalam kunci cocok dengan nilai dalam sebuah relasi table (the referenced value).
Model Relasional
• Primary key
ALTER TABLE tblCustomers ALTER COLUMN CustomerID INTEGER CONSTRAINT PK tblCustomers PRIMARY KEY ALTER TABLE tblCustomers ALTER COLUMN CustomerID INTEGER PRIMARY KEY
Model Relasional
• Primary key
ALTER TABLE tblCustomers ADD CONSTRAINT CustomerNames UNIQUE ([Last Name], [First Name]) ALTER TABLE tblInvoices ADD CONSTRAINT CheckAmount CHECK (Amount > 0)
Model Relasional
• Foreign key
ALTER TABLE tblShipping ADD CONSTRAINT FK tblShipping FOREIGN KEY (CustomerID) REFERENCES tblCustomers (CustomerID)
Apabila
AND digunakan, disemua syarat sebelum dan setelah AND harus benar untuk suatu tuple dipilih
Apabila
OR digunakan, hanya salah satu syarat sebelum atau sesudah OR harus benar ataupun keduanya benar untuk suatu tuple dipilih
Apabila
NOT digunakan, yang bukan syarat yang akan di tampilkan
47
Jarak Nilai Sebagai Kriteria Kita boleh mengunakan pernyataan BETWEEN Select namafield from namatabel where namafield between nilai and nilai Contoh Select nama,gaji from pegawai where gaji between 850000 and 1000000
48
Operator In Pencocokan data kondisi pencarian dengan salah satu data yang ada pada suatu daftar nilai Select namafield from namatabel where namafield In (nilaifield1,nilaifield2) Contoh Select * from pegawai where alamat In (‘seutui’,’prada’)
Select nama,alamat from pegawai where alamat In (‘seutui’,’prada’)
49
Operator Like -Untuk pencarian data -Bekerja pada type data char, varchar, text
Select namafield from namatabel where namafield Like ‘%ekspresi%’ Contoh Select * from pegawai where alamat Like ‘%andi%’ (menampilkan semua klolom dari tabel pegawai yang namanya mengandung kata andi)
50
Fungsi Agregat fungsi yang mengambil suatu kumpulan nilai-nilai sebagai input dan mengembalikan satu nilai sebagai output Select fungsi agregat SUM/AVG/MIN/MAX/COUNT (namafield) As namafieldbaru from nama table
SQL menyediakan 5 fungsi
AVG : Rata-rata MIN : Minimum MAX : Maksimum SUM : PenJumlahan isi record COUNT : menjumlahkan data 51
Operasi
MIN, MAX dan COUNT dapat dilakukan dalam atribut yang mempunyai jenis data rentetan (string) seperti Char, Text, dan Varchar Operasi AVG dan SUM hanya boleh dilakukan keatas atribut-atribut yang mempunyai jenis data numerik yang berjenis int, bigint, decimal, bit, numeric, real, smallint, tinyint.
52
a) SUM Penjumlahan dalam satu kolom Select sum (tunjangan) As jlh_tunj from pegawai
Menampilkan jumlah keseluruhan dari tunjangan yang diberikan
53
b) Avg menghitung nilai rata-rata dalam satu kolom Select Avg (gaji) As rata_gaji from pegawai
54
c) Min Untuk mencari atau menampilkan nilai terendah Select min (gaji) As Gaji_min from pegawai (Menampilkan gaji terendah dari tabel pegawai)
Select min (gaji) As Gaji_min from pegawai where status=‘nikah’ (Menampilkan gaji terendah dari tabel pegawai yang statusnya telah menikah)
55
d) MAX Untuk mencari atau menentukan nilai tinggi Select max (gaji) As Gaji_max from pegawai (Menampilkan gaji terendah dari tabel pegawai)
Select max (gaji) As Gaji_max from pegawai where status=‘nikah’ (Menampilkan gaji terendah dari tabel pegawai yang statusnya telah menikah)
56
e) COUNT Menghitung jumlah record yang sesuai dengan kondisi Select count (nip) As jlh_peg from pegawai (Menampilkan jumlah seluruh pegawai dari tabel pegawai)
Select count (nip) As jlh_peg from pegawai where status=‘nikah’ (Menampilkan jumlah seluruh pegawai dari tabel pegawai yang statusnya telah menikah)
57
Pengelompokan Record (Group by) Pada kondisi tertentu, sekumpulan record dapat dikelompokkan berdasarkan satu atau lebih field yang terdapat pada sebuah tabel atau query. Select namafield1,namafield2 from namatabel Group by field pengacu Contoh Select jeniskelamin,count (*) as jumlah from mahasiswa group by jeniskelamin Hasil
JenisKelamin
jumlah
Laki-Laki
5
Perempuan
4 58
Hasil
dari query dapat di urutkan berdasarkan pada satu atau lebih kolom.
Select namafield1 from namatabel where kriteria nim order by namafield 2008 2009
Select nim,nama where jeniskelamin=’perempuan’ 2010 order by nama 2011
nama Ana Devi July Kiki
Select nim,nama where jeniskelamin=’perempuan’ order by nama asc Select nim,nama where jeniskelamin=’perempuan’ order by nama desc
59
Pernyataan Having -menampilkan kelompok-kelompok data tertentu -having berbeda dengan where, pernyataan where digunakan untuk memberikan criteria sebelum pengelompokan dan melakukan penyaringan baris. Sedangkan having digunakan untuk memberikan criteria setelah pengelompokan dilakukan, menyaring kelompok, dan menentukan kondisi bagi Group By Select namafield1,… from namatabel Group by field pengacu Having kondisi Contoh Select barang, count (jumlah) As totalfaktur from Torder Group by idbarang having idbarang=‘A009’ 60
Berfungsi untuk mengubah satu atau lebih data yang terdapat pada satu atau lebih kolom tabel Update namatabel set namafield=nilai where kondisi Contoh Update pegawai set alamat=‘pango’ where nip=’01’
61
Menukar record dalam Tabel Update namatabel set namafield=nilai Contoh Update pegawai set gaji=gaji+12000
pernyataan diatas akan menambah ‘gaji’ yang awal dengan +12000 dalam tabel pegawai
62
Berfungsi untuk menghapus satu atau beberapa record dalam suatu tabel. Delete from namatabel where kondisi Contoh Delete from pegawai where nip=’01’
63
