latihan sql

LATIHAN SQL.PERANCANGAN BASIS DATA

Jalankan setiap statement satu persatu!Membuat tabel, tuliskan perintah dibawah ini:Buat table devisiCreate table devisi( kd_dev varchar2(2) not null, Nm_dev varchar2(50), constraint pk_devisi primary key(kd_dev));

Buat table barang

Create table barang( kd_brg varchar2(5) not null, Nm_brg varchar2(50), Satuan varchar2(10), Stok number, constraint pk_barang primary key(kd_brg));

Buat table DKBCreate table dkb( no_dkb varchar2(5) not null, bulan varchar2(2), tahun varchar2(4), kd_dev varchar2(2), constraint pk_dkb primary key(no_dkb) );

Menghapus Table

Drop table devisi;

Maka table devisi akan terhapus, sebelum melanjutkan perintah selanjutnya maka diharuskan untuk BUAT TABLE DEVISI LAGI.

Isikan data ke setiap table, contoh:

Insert into devisi(kd_dev,nm_dev)values(‘D1’,’KEUANGAN’);Insert into devisi(kd_dev,nm_dev)values(‘D2’,’KEPEGAWAIAN’);

Insert into barang(kd_brg,nm_brg,satuan,stok)values(‘B0001’,’JAGUNG’,’Kg’,0);Insert into barang(kd_brg,nm_brg,satuan,stok)values(‘B0002’,’UBI’,’Kg’,0);Insert into barang(kd_brg,nm_brg,satuan,stok)values(‘B0003’,’KACANG’,’Kg’,0);

Insert into dkb(no_dkb,bulan,tahun,kd_dev)values(‘T0001’,’01’,’2008’,’D1’);Insert into detildkb(no_dkb,kd_brg,qty,kondisi,pakai,keterangan) values(‘T0001’,’B0001’,10,’2’,’1’,’Segera’);Insert into detildkb(no_dkb,kd_brg,qty,kondisi,pakai,keterangan) values(‘T0001’,’B0002’,2,’2’,’3’,’-’);

Insert into dkb(no_dkb,bulan,tahun,kd_dev)values(‘T0002’,’02’,’2008’,’D2’);Insert into detildkb(no_dkb,kd_brg,qty,kondisi,pakai,keterangan) values(‘T0002’,’B0003’,50,’2’,’1’,’Segera’);

Pencarian data yang telah diInsert:Mencari data devisiSelect * from devisi; atau select kd_dev,nm_dev from devisi;

Mencari data barangSelect * from barang;

Mencari data dkb pada bulan 01 dan tahun 2008Select * from dkb where bulan=’01’ and tahun=’2008’

Mencari data no_dkb,bulan,tahun, dan nm_dev dari table dkb dan devisiSelect no_dkb,bulan,tahun, nm_dev from dkb, devisi where dkb.kd_dev = devisi.kd_dev;

--------------------selamat mencoba--------------------

LATIHAN SQL.PERANCANGAN BASIS DATA

Buat table detildkbCreate table detildkb( no_dkb varchar2(5) not null, Kd_brg varchar2(5) not null, qty number, kondisi varchar2(1), pakai varchar2(1), keterangan varchar2(50), constraint pk_detildkb primary key(no_dkb,kd_brg));

SELECT Menampilkan sebagian atau seluruh isi dari suatu tabel Menampilkan kombinasi isi dari beberapa tabel

Bentuk umum perintah SELECT

SELECT [DISTINCT] field(s)FROM table(s)[WHERE predicate][GROUP BY field(s) [HAVING predicate]][ORDER BY field(s)];

Contoh:Tampilkan kode barang yang ada di table detildkbSelect kd_brg from detildkb;

Untuk menghilangkan kemungkinan duplikasi pada hasil, maka query diatas menjadi:Select distinct kd_brg from detildkb;

Tampilkan detil informasi mengenai seluruh barangselect * from barang;

select dengan kondisiTampilkan nama-nama barang untuk barang yang stoknya 0 (nol)Select nm_brg from barang Where stok=0;

select dengan pengurutabTampilkan nama-nama barang untuk barang yang stoknya 0 (nol), dan diurutkan menurut select nm_brg from barang Where stok=0Order by nm_brg desc;

Query dengan melibatkan lebih dari satu tabel (relasi)Simple EquijoinTampilkan semua kombinasi dkb dan devisi, untuk devisi yang melakukan permintaan barang.Select dkb.*, devisi.* from dkb, devisiWhere dkb.kd_dev = dkb.kd_dev;

Join Query dengan Kondisi MajemukTampilkan semua kombinasi dkb dan devisi, untuk devisi yang melakukan permintaan barang. namun tampikan yang transaksinya yang pada bulan 02 dan tahun 2008Select dkb.*, devisi.* from dkb, devisiWhere dkb.kd_dev = dkb.kd_dev and bulan=’02’ and tahun=’2008’;

Join antara tiga tabelTampilkan kd_dev,nm_dev,no_dkb,bulan, tahun, kd_brg, qtySELECT devisi.kd_dev, devisi.nm_dev,dkb.no_dkb, dkb.bulan, dkb.tahun, detildkb.kd_brg, detildkb.qtyfrom devisi, dkb, detildkbwhere devisi.kd_dev=dkb.kd_dev and dkb.no_dkb=detildkb.no_dkb;

COUNTBanyaknya nilai-nilai pada satu kolom.

Contoh:Tampilkan banyaknya devisi yang adaselect count (*) from devisi;

Tampilkan banyaknya barang yang stoknya=0Select count(*) from barang where stok=0;

SUMJumlah nilai dari satu kolomContoh:Tampilkan jumlah kuantitas pengiriman barang B0001Select sum(qty) from detildkbWhere kd_brg=’B0001’;

Tampilkan kode barang dan jumlah permintaanya untuk setiap kode barangmya.Select kd_brg,sum(qty) from detildkbgroup by kd_brg;

Tampilkan kode barang, nama barang dan jumlah permintaanya untuk setiap kode barangmya.Select detildkb.kd_brg,barang.nm_brg,sum(detildkb.qty) from detildkb, barangwhere detildkb.kd_brg=barang.kd_brggroup by detildkb.kd_brg,barang.nm_brg;

UPDATE Mengubah isi satu atau beberapa atribut dari suatu tabel

Mengupdate nama barang menjadi “JAGUNG SUPER” untuk kode barang B0001Update barang set nm_brg=’JAGUNG SUPER’ where kd_brg=’B0001’

Mengupdate nama barang menjadi “KACANG SUPER” dan stok menjadi 10 untuk kode barang B0001Update barang set nm_brg=’KACANG SUPER’, stok=10 where kd_brg=’B0003’

DELETE Menghapus sebagian atau seluruh isi dari suatu tabel

Menghapus data devisi yang kode devisinya d2Delete from devisi where kd_dev=’D2’

--------selamat mencoba--------

LATIHAN CONSTRAINTPERANCANGAN BASIS DATA

CONSTRAINT

CONSTRAINT adalah untuk membuat suatu aturan atau batasan yang berlaku pada tabel tersebut, sehingga dapat mencegah kesalahan operasi DML yang dikenakan pada tabel tersebut.

Tipe-tipe Contraint Not null

Contraint ini digunakan untuk mencegah adanya kolom yang bernilai null, salah satu efek yang ditimpulkan nilai null adalah jika kolom tsb dikenakan hokum aritmatika akan memberikan hasil yang tidak sesuai.Contoh:Tambahkan kolom baru pada tabel barang;

Alter table barang add ( harga number);

Select kd_brg,nm_brg, harga,stok, (harga * stok) as jum_harga from barang

Kemudian ubah harga barang menjadi 10.000Update barang set harga=10000;

Select kd_brg,nm_brg, harga,stok, (harga * stok) as jum_harga from barang

Dari kasus query diatas akan memberikan kondisi nilai yang berbeda pada perhitungan (stok * harga)

UniqueBertujuan untuk membuat sebuah kolom atau kombinasi dari beberapa komlom pada sebuah table nebjadi bersifat unik.Contoh:Ceate table karyawan (nip varchar2(5) constraint pk_kry primary key,nama varchar2(50) not null,kd_jabatan varchar2(1) unique,nm_jabatan varchar2(20));Kemudian jalakan sql sbb:Insert into (nip,nama,kd_jabatan,nm_jabatan) values (‘09001’,’AGUS’,’A’,’KA. BAAK’)

Insert into (nip,nama,kd_jabatan,nm_jabatan) values (‘09002’,’BUDI’,’A’,’KA. BAAK’)

pada saat baris kedua dimasukan akan terjadi kesalahan sbb:ERROR at line 1:ORA-00001: unique constraint (.....SYS_C00879) violatedKesalahan tersebut muncul karena ada baris yang pada kolom kd_jabatan “A”.

Primary key Jelas Foreign key

Untuk menetapkan suatu kolom atau kombinasi dari beberapa kolom menjadi foreign key dari sebuah table. Foreign key sering di sebut referential integrity constraint. Kegunaanya untuk membentuk relasi dari satu table ke table yang lainya.Contoh:Kolom kd_dev yang ada pada table dkb merupakan foreign key.

Coba untuk membuktikan integrity nya terbentuk atau tidak:Tambah record baru pada table dkb.

Insert into dkb(no_dkb,bulan,tahun,kd_dev)values(‘T0005’,’01’,’2008’,’D5’);Baris tersebut dapat masuk.

Delete from devisi where kd_del=’D1’;Baris tersebut dapat dihapus;

Kemudian jalankan perintah sbb:Alter table dkb add constraint fk_dkb foreign key (kd_dev)References devisi(kd_dev);

Kemudian jalankan lagi sql sbb:

Insert into dkb(no_dkb,bulan,tahun,kd_dev)values(‘T0005’,’01’,’2008’,’D6’;

Baris tersebut tidak dapat dimasukan, baca pesan kesalahan.

Delete from devisi where kd_del=’D1’;Baris tersebut tidak dapat dihapus, baca pesan kesalahan.

CheckConstraint check berguna untuk membuat suatu kondisi yang harus dipenuhi setiap record dalam table.Kondisi tersebut dapat berupa kodisi sbb: Kondisi PERBANDINGAN Kondisi RANGE Kondisi NULL Kondisi LIKE Kondisi EXIST

Kondisi PERBANDINGANContoh:Untuk memasukan data nama devisi harus berawalan huruf besar dan diikuti huruf kecil.

Alter table devisi add constraint chk_namadevisi check (nm_dev=initcape(nm_dev));

coba insert data baru pada devisiInsert into devisi(kd_dev,nm_dev)values(‘D5’,’kebersihan’);

Akan terjadi error message, baca pesan error nya.

Kondisi RANGEContoh:Alter table barang constraint chk_stok check(stok between 0 and 999);Insert into barang(kd_brg,nm_brg,satuan,stok)values(‘B0003’,’KACANG’,’Kg’,1000);

Akan terjadi error message, baca pesan error nya.


LATIHAN HAK AKSES TERHADAP OBYEK PADA USER LAIN(GRANT)PERANCANGAN BASIS DATA

HAK AKSESMemberikan hak pada user lain untuk mengakses pada obyek yang ada, beberapa penentu akses

Select Memberikan akses pada user lain pada obyek (table, procedure) untuk

melakukan akses hanya bisa membaca. Contoh: untuk lab02_02

Untuk lab02_01 lakukan perintah sbb:Select * from lab02_02.devisiAkan terjadi pesan kesalahan karena tidak mempunyai hak (privileges)Kemudian lab02_02 lakukan perintah sbb:

Grant select on devisi to lab02_01;Berarti kita memberikan hak pada user lab02_01 untuk dapat membaca tabel devisi yang kita miliki.

Untuk lab02_01 lakukan lagi perintah sbb:Select * from lab02_02.devisi

Ok… Insert

Memberikan akses pada user lain pada obyek (table, procedure) untuk melakukan akses hanya bisa menginsert data.

Contoh:Grant insert on devisi to lab02_01;Berarti kita memberikan hak pada user lab02_01 untuk dapat menginsert/menambah data pada tabel devisi yang kita miliki.

delete Memberikan akses pada user lain pada obyek (table, procedure) untuk

melakukan akses hanya bisa delete. Contoh:

Grant delete on devisi to lab02_01;Berarti kita memberikan hak pada user lab02_01 untuk dapat menghapus tabel devisi yang kita miliki.

update Memberikan akses pada user lain pada obyek (table, procedure) untuk

melakukan akses hanya bisa mengedit data. Contoh:

Grant update on devisi to lab02_01;Berarti kita memberikan hak pada user lab02_01 untuk dapat mengedit tabel devisi yang kita miliki.

Kombinasi dari insert update, delete, select Memberikan akses pada user lain pada obyek (table, procedure) untuk

melakukan akses hanya bisa delete. Contoh:

Grant delete on devisi to lab02_01;Berarti kita memberikan hak pada user lab02_01 untuk dapat menghapus tabel devisi yang kita miliki.


LATIHAN STORED PROCEDUREPERANCANGAN BASIS DATA

Cara membuat fungsi untuk mencari nama BARANG.

Create or replace function cariBarang(xkd_brg varchar2) return varchar2 isxnama varchar2(50);Begin

BeginSelect nm_brg into xnama from barang where kd_brg=xkd_brg;Exception when no_data_found then xnama:=’data tidak ada!’;

End;Return(xnama);

End;/

Cara membuat fungsi untuk mencari nama DEVISI.

Create or replace function cariDevisi(xkd_dev varchar2) return varchar2 isxnama varchar2(20);Begin

BeginSelect nm_dev into xnama from devisi where kd_dev=xkd_dev;Exception when no_data_found then xnama:=’data tidak ada!’;

End;Return(xnama);

End;

/

Contoh:

Mencari data sbb:No_dkb bulan tahun Kd_dev Nm_dev

Sql tanpa store procedure:Select dkb.no_dkb,dkb.bulan,dkb.tahun, dkb.kd_dev,devisi.nm_dev From devisi, dkb where devisi.kd_dev= dkb.kd_dev;

Dengan fungsi

Select dkb.no_dkb,dkb.bulan,dkb.tahun, dkb.kd_dev,cariDevisi(kd_dev) From dkb;

LatihanTuliskan sql untuk mencari data No_dkb bulan tahun Kd_dev Nm_dev Kd_brg Nm_brg qty Kondisi pakai Keterangan


latihan sql

Documents