Senin, 13 Juni 2011

Structure Query Language [SQL]



DDL (Data Definition Language)
         Create Tables
         Create Indexes
         Altering Tables
         Dropping Tables/Indexes
DDL (Data Definition Language)
         Create Tables statement
CREATE TABLE table (An Dn, An Dn, .., An Dn)
Create Tables Structure
         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))
DDL
         Create Tables statement
CREATE TABLE tabel (A1 D1, A2 D2, .., An Dn)
Keterangan :
tabel = nama tabel yang akan dibuat
A1..An = atribut atau variabel field
D1, Dn= tipe data untuk A1..An
Create Tables Statement
         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))
Create Index

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.
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. 
Create Index Statement
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)


DROP Index Statement
DROP INDEX nama_index   
ON  nama_tabel
Contoh :
DROP  INDEX idxCustomerPhone    
ON tblCustomers
DROP Table Statement
DROP TABLE nama_tabel 
Contoh :
DROP  TABLE tblCustomers    

Altering Tables Statement
         Altering Tables Structure
ALTER TABLE tabel
ADD | MODIFY (An dn, An dn, .., An dn);
ALTER TABLE tblCustomers  
ADD COLUMN Address TEXT(30)

Mengganti ukuran field :

ALTER TABLE tblCustomers  
ALTER COLUMN Address TEXT(40)
         Altering Tables & DROP
Menghapus Field :
ALTER TABLE tblCustomers  
DROP COLUMN Address
Structure Query Language
 [SQL]
DML ( Data Manipulation Language)
         • Insertion : Menyisipkan data record ke  dalam suatu table
         • Updating : Memperbaiki data record dalam suatu table
         • Deletion  : Menghapus data record pada suatu tabel
         Selection : Menampilkan data record dari  suatu tabel
Query Insertion Sructure
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

Query Update Sructure
UPDATE  tabel
SET  assignments
WHERE Criteria
CONTOH :
UPDATE  siswa
SET  nilai = 89
WHERE NISN =‘123456’;
Query Deletion Sructure
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 <otoritas> ON <nm_tabel> TO <user_name>
Grant Type :
                        insert ,             select,
                        update ,          delete ,            all
         GRANT for insert
GRANT insert  ON <nm_tabel> 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 <nm_tabel> 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 <nm_tabel> 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 <nm_tabel> TO <user_name>
GRANT <otoritas>  ON <nm_tabel> TO public
Contoh
GRANT all ON siswa TO ani
GRANT select  ON siswa TO public
         REVOKE structure
REVOKE <otoritas> ON <nm_tabel> FROM <user_name>
Revoke Type :
                        insert ,             select,
                        update ,          delete ,            all
         REVOKE for insert
REVOKE insert  ON <nm_tabel> FROM <user_name>
         REVOKE for select
REVOKE select  ON <nm_database> FROM <user_name>
Advanced Query
         Complex Integrity Constraints
            - Constraints over single table
            - Domain constraints
         ICs over several tables
         IF conditional into query
         Aggregate function
Integrity Constraint
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.
Integrity Constraint
Data Integrity
         Constraint States
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 :
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).
         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).
Integrity Constraint
        Primary key

ALTER TABLE tblCustomers  
ALTER COLUMN CustomerID INTEGER   CONSTRAINT PK tblCustomers PRIMARY KEY 

ALTER TABLE tblCustomers  
ALTER COLUMN CustomerID INTEGER PRIMARY KEY
        Primary key

ALTER TABLE tblCustomers  
ADD CONSTRAINT CustomerNames
UNIQUE   ([Last Name], [First Name])

ALTER TABLE tblInvoices  
ADD CONSTRAINT CheckAmount  
CHECK (Amount > 0) 
        Foreign key

ALTER TABLE tblShipping  
ADD CONSTRAINT FK  tblShipping  
FOREIGN KEY (CustomerID)
REFERENCES tblCustomers (CustomerID)  
ON UPDATE CASCADE  
ON DELETE CASCADE
Query Optimation
         What Is Optimization?
         Optimization adalah pemilihan proses ekseskusi SQl statement yang efficient. Langkah ini sangat penting di dalam processing untuk data manipulation language (DML) statement:
            SELECT,
            INSERT,
            UPDATE,
            or DELETE.
         Terdapat beberapa cara untuk mengakses SQL statement yang sering ada, contoh, dengan variasi tahapan pengaksesan data dalam tabel atau indexes. Pada procedure Oracle menggunakan eksekusi sebuah statement sangat berpengaruh pada kesepatanm eksekusi statement.
Bagain ini pada Oracle disebut perhitungan optimizer cara yang sangat efficient untuik sebuah SQL statement. Beberapa faktor evaluasi optimizer dapat dipilih dari beberapa access paths alternatif . Hal ini dapat menggunakan cost-based or rule-based approach
         Rule based optimization adalah sebuah method lama bahwa optimizer dapat menggambarkanhasil bagaimana query and dieksekusi.
         The Cost based optimizer lebih optimizer "intelligent" yang dapat mencari dan menentukan bagaimana QUERY anda dieksekusi.
         Rule based optimization
Rank 
Access Path 
1
Single row by rowid  
2
Single row by cluster join  
3
Single row by hash cluster key with unique or primary key  
4
Single row by unique or primary key  
5
Cluster join  
6
Hash cluster key  
7
Indexed cluster key  
8
Composite key  


9
Single-column indexes  




10
Bounded range search on indexed columns    
12
Unbounded range search on indexed columns    
13
Sort-merge join    
14
MAX or MIN of indexed column    
15
ORDER BY on indexed columns  
16
Full table scan   







         Rule based optimization : Single row by rowid
This Access path ini hanya hanya tersedia jika statement's WHERE clause dipilih identitas baris atau dengan menambahkan dukungan tulisan SQL CURRENT OF CURSOR oleh Oracle Precompilers. Menjalankan statement, Oracle accesses tabel perbaris. 

Contoh :

diberikan statement untuk mengakses data sebagai berikut :

SELECT * FROM emp
WHERE ROWID = 'AAAA7bAA5AAAA1UAAA';

The EXPLAIN PLAN output for this statement might look like this:
OPERATION                                     OPTIONS                   OBJECT_NAME ----------------------------------
SELECT STATEMENT    
            TABLE ACCESS                   BY ROWID               EMP
         Rule based optimization : Single Row by Cluster Join
This Access path ini hanya hanya tersedia jika statement's WHERE clause dipilih identitas baris atau dengan menambahkan dukungan tulisan SQL CURRENT OF CURSOR oleh Oracle Precompilers. Menjalankan statement, Oracle accesses tabel perbaris. 

Contoh :

diberikan statement untuk mengakses data sebagai berikut :

SELECT * FROM emp
WHERE ROWID = 'AAAA7bAA5AAAA1UAAA';

The EXPLAIN PLAN output for this statement might look like this:
OPERATION                                     OPTIONS                   OBJECT_NAME ----------------------------------
SELECT STATEMENT    
            TABLE ACCESS                   BY ROWID               EMP

         Jika seset tujuan optimizer andauntuk dipilih, optimizer akan digunakan secara baku CBO tidak menghilangkan statistics anda. Bila berjalan dalam mode CBO, ini sangat penting untukkeakuratan statistik pada database (ANALYZE TABLE, or DBMS_STATS.Gather*).
         Relational Query Optimation
         Optimation query Planning
         Catalog system in query optimation
         Indexes using  for query optimation
         Optimation query Planning
            memperlihatkan representasi execution plan pada SQL statement berikut, untuk memilih name, job, salary, dan department name untuk semua employees yang tidak menerima gaji pada batas rentang gaji yang telah ditentukan :

            SELECT ename, job, sal, dname    
            FROM emp, dept  
            WHERE emp.deptno = dept.deptno    
            AND NOT EXISTS      
            (SELECT *  FROM salgrade         
            WHERE emp.sal BETWEEN losal AND hisal); 
Database Trigger
         Trigger Type
         Using Database trigger
         The activate rule database trigger
         Deletion database trigger
Trigger Type
         INSERT
            adalah event_trigger untuk penyisipan data record ke dalam tabel
         UPDATE
            adalah event_trigger untuk perbaikan data record yang ada dalam tabel
         DELETE
            adalah event_trigger untuk menhapus data record yang ada dalam tabel
Trigger Statement
CREATE OR REPLACE TRIGGER nm_trigger
                        [BEFORE | AFTER] event_trigger ON                   nama_tabel                
            FOR EACH ROW
            [DECLARE]
                        Deklarasi_Variabel
            BEGIN
                        statements list;
            END;
Database Trigger
         Using Database trigger
MENGUPDATE DATA BARANG :

            CREATE OR REPLACE TRIGGER tr_barang
                        AFTER update ON  barang
            FOR EACH ROW
            BEGIN
                        dbms_output.put_line(‘Tabel barang telah di-                                                                             update’);
            END;

MENAMBAH DATA STOK BARANG :
CREATE OR REPLACE TRIGGER tr_insert_pasok
                        AFTER insert  ON  pasok
            FOR EACH ROW
            BEGIN
                        update stok_barang
                        set jum_stok= jum_stok + :new.jum_pasok ;
            END;
MENGHAPUS DATA STOK BARANG :
CREATE OR REPLACE TRIGGER tr_delete_pasok
                        AFTER update ON pasok
                         FOR EACH ROW
            DECLARE
                        Jml_lama integer;
                        Jml_baru integer;
                        Selisih integer;
            BEGIN
                        select:old.jum_stok,:new.jum_pasok into jml_lama, jml_baru from  pasok;
                        if (jml_lama>jml_baru) then
                                    selisih := jml_lama - jml_baru;
                                    update stok_barang
                                    set jum_stok = jum_stok – selisih;
                        else
                                    selisih := jml_baru - jml_lama;
                                    update stok_barang
                                    set jum_stok = jum_stok + selisih;
                        end if;
             END;


Share

Twitter Delicious Facebook Digg Stumbleupon Favorites