Quis 1 (DATABASE 2)

   

Membuat Tabel tr_kodetransaksi

create table tr_kodetransaksi

(c_tr number(5) primary key,

e_tr varchar2(50));

Memasukan data ke table tr_kodetransaksi

insert into tr_kodetransaksi (c_tr, e_tr)

values (1, 'setor');

insert into tr_kodetransaksi (c_tr, e_tr)

values (2, 'tarik');

insert into tr_kodetransaksi (c_tr, e_tr)

values (3, 'atm');

insert into tr_kodetransaksi (c_tr, e_tr)

values (4, 'pemindahan buku');

insert into tr_kodetransaksi (c_tr, e_tr)

values (5, 'koreksi plus');

insert into tr_kodetransaksi (c_tr, e_tr)

values (6, 'koreksi minus');

insert into tr_kodetransaksi (c_tr, e_tr)

values (7, 'bunga');

insert into tr_kodetransaksi (c_tr, e_tr)

values (8, 'administrasi biaya');

insert into tr_kodetransaksi (c_tr, e_tr)

values (9, 'pajak');

Membuat tabel tr_job

create table tr_job

(c_job varchar(10) primary key,

n_job varchar2(50));

Memasukan data ke tr_job

insert into tr_job(c_job, n_job)

values ('Tel', 'teller');

insert into tr_job(c_job, n_job)

values ('MGR', 'manager');

insert into tr_job(c_job, n_job)

values ('SYS', 'system analis');

Membuat tabel tm_tabungan

create table tm_tabungan

(I_TR VARCHAR2(20) primary key,

C_TR NUMBER(5),

D_TR DATE, 

V_DEBET varchar(25), 

V_KREDIT varchar(25), 

V_SALDO varchar(25), 

I_PETUGAS VARCHAR(10), 

I_REKENING VARCHAR(25));

 

Membuat foreign key dari tm_tabungan ke tr_kodetransaksi

alter table tm_tabungan add foreign key (c_tr) references tr_kodetransaksi (c_tr);

Membuat Tabel Petugas

create table tm_petugas

(i_petugas varchar(10) primary key,

n_petugas varchar2(50));

Memasukan data ke tabel petugas

insert into tm_petugas (i_petugas, n_petugas)

values (1, 'mia');

insert into tm_petugas (i_petugas, n_petugas)

values (2, 'mila');

Membuat Foreign key dari tm_tabungan (i_petugas) ke tm_petugas(i_petugas)

alter table tm_tabungan add foreign key (i_petugas) references tm_petugas (i_petugas);

Membuat foreign key tm_tabungan (i_rekening) ke nasabah(i_rek)

alter table tm_tabungan add foreign key (i_rekening) references nasabah (i_rek);

Membuat Tabel nasabah

create table nasabah

(I_REK VARCHAR(25) primary key,

N_NASABAH VARCHAR(50), 

A_NASABAH VARCHAR(50), 

D_LAHIR DATE, 

C_JOB VARCHAR(10), 

C_AGAMA VARCHAR(20)); 

Membuat foreign key

alter table nasabah add foreign key (c_job) references tr_job (c_job);

Membuat tabel agama

create table agama

(c_agama varchar(20) primary key,

e_agama varchar2(50));

Memasukan data pada tabel agama

insert into agama (c_agama,  e_agama)

values (1, 'islam');

Membuat foreign key dari nasabah (c_agama) ke agama (c_agama)

alter table nasabah add foreign key (c_agama) references agama (c_agama);

Memasukan data ke tabel nasabah

insert into nasabah (I_REK, N_NASABAH, A_NASABAH, D_LAHIR, C_JOB, C_AGAMA)

values (01, 'mia', 'bandung', '04-nov-93', 'PGM', 1);

Memasukan data ke tabel tm_tabungan

insert into tm_tabungan (I_TR, C_TR, D_TR, V_DEBET, V_KREDIT, V_SALDO, I_PETUGAS, I_REKENING)

values ('trans01', 1, ‘25-oct-2012’, 10000, 5000, 5000, 1, 1);

Memasukan data ke tm_tabungan

insert into tm_tabungan (I_TR, C_TR, D_TR, V_DEBET, V_KREDIT, V_SALDO, I_PETUGAS, I_REKENING)

values ('trans02', 1, '25-oct-2012', 5000, 2000, 3000, 1, 1);

Menampilkan data nasabah (i_rek, e_tr, v_saldo yang mempunyai saldo yang paling kecil)

select i_rek, n_nasabah, v_saldo

from nasabah

inner join tm_tabungan

on nasabah.i_rek=tm_tabungan.i_rekening

where v_saldo=(select min (v_saldo) from tm_tabungan);

Menampilkan C_tr, e_tr, Sum(v_debet), Sum(v_kredit)

select tr_kodetransaksi.c_tr, tr_kodetransaksi.e_tr, sum(tm_tabungan.v_debet), sum(tm_tabungan.v_kredit) from tm_tabungan inner join tr_kodetransaksi on tr_kodetransaksi.c_tr=tm_tabungan.c_tr group by tr_kodetransaksi.c_tr, tr_kodetransaksi.e_tr

Menampilkan C_tr, e_tr, Sum(v_debet), sum(v_kredit) tapi periode januari 2012 sampai desember 2012

select tr_kodetransaksi.c_tr, tr_kodetransaksi.e_tr, sum(tm_tabungan.v_debet), sum(tm_tabungan.v_kredit) from tm_tabungan inner join tr_kodetransaksi on tr_kodetransaksi.c_tr=tm_tabungan.c_tr where d_tr between '01-jan-2012' and '31-dec-2012' group by tr_kodetransaksi.c_tr,tr_kodetransaksi.e_tr;

select count (*) as "jumlah" , c_tr from tm_tabungan group by c_tr

Syntax DDL dan DML

 Syntax DDL (DATA DEFINITION LANGUAGE)

Contoh sintaks DDL dalam pembuatan database/table,index dan view :

1. Pembuatan Database
Bentuk umumnya : CREATE DATABASE nama_db
Sintaks : CREATE DATABASE NILAI

2. Pembuatan Tabel
Bentuk umumnya : CREATE TABLE nama_table(nama_kolom1 jenis_kolom1, nama_kolom2 jenis_kolom2, ...)
Sintaks : CREATE TABLE Mahasiswa(NIM char(8) NOT NULL, Nama char(30) NOT NULL, Alamat char(40) NOT NULL, JenKel char(1) NOT NULL)

3. Pembuatan Index
Bentuk umumnya : CREATE [UNIQUE] INDEX nama_index ON nama_table (nama_kolom)
Sintaks : CREATE UNIQUE INDEX MHSIN ON MHS(NIM)

4. Pembuatan View
Bentuk umumnya : CREATE VIEW [(nama_kolom1, ...)] AS SELECT statement [WITH CHECK OPTION]
Sintaks : CREATE VIEW MHSVIEW AS SELECT * FROM MHS

Contoh sintaks DDL untuk menghapus database/table,index dan view :

1. Menghapus Database
Bentuk umumnya : DROP DATABASE nama_db

2. Menghapus Tabel
Bentuk umumnya : DROP TABLE nama_table

3. Menghapus Index
Bentuk umumnya : DROP INDEX nama_index

4. Menghapus View
Bentuk umumnya : DROP VIEW nama_view

Contoh sintaks DDL untuk mengubah struktur tabel :

1. Menambahkan kolom
Bentuk umumnya : ALTER TABLE nama_table> ADD (nama_kolom jenis_kolom)
Sintaks : ALTER TABLE Mahasiswa ADD (NoTelp char(8))

2. Mengubah panjang kolom
Bentuk umumnya : ALTER TABLE nama_table MODIFY (nama_kolom jenis_kolom)
Sintaks : ALTER TABLE Mahasiswa MODIFY (Alamat char(50))

3. Menghapus kolom
Bentuk umumnya : ALTER TABLE nama_table> DROP (nama_kolom jenis_kolom)
Sintaks : ALTER TABLE Mahasiswa DROP (Alamat char(1))

Syntax DML (DATA MANIPULATION LANGUAGE) 

-SELECT
SELECT Daftar_Kolom FROM Nama_Tabel WHERE Kriteria ORDER BY Kolom

Contoh :
1. Memilih semua kolom

sintaks : SELECT * FROM Tb_Buku

2. Memilih kolom tertentu, misalnya :

SELECT judul,penulis,penerbit FROM Tb_Buku

3. Memilih baris berdasarkan kriteria tertentu

SELECT * FROM Tb_Buku WHERE (harga>=50000)

4. Memilih semua kolom dan diurutkan berdasarkan satu atau dua kolom.
Pengurutan dapat dilakukan dengan ascending(asc)/menurun atau desending(desc)/menaik. Secara default, data diurutkan secara

menaik.

SELECT * FROM Tb_Buku ORDER BY Harga asc

asc ==> boleh tidak ditulis

- INSERT
Perintah insert digunakan untuk menambahkan atau menginputkan data ke dalam tabel. Secara umum sintaknya adalah seperti berikut :

INSERT INTO NamaTabel
(Kolom1,
Kolom2,
...)
VALUES (Nilai1,
Nilai2,
...)

Contoh :

1. Menambah baris pada tabel buku

INSERT INTO Tb_Buku
(kd_buku,
judul,
penulis,
harga)
VALUES ('BK01',
'Basis Data',
'Dian Ambar',
50000)


2. Nilai yang diberikan boleh berupa ekspresi atau NULL

INSERT INTO Tb_Pegawai
(NRP,
nama,
tgl_lahir,
gaji,
tunjangan)
VALUES ('0764575368',
'Mia',
NULL,
1500000,
0.25*1500000)

3. Penambahan data dapat diambil dari tabel lain
Meng-copy sebuah baris data dari tabel Tb_Buku ke dalam tabel Tb_BukuBaru dengan tambahan kolom baru penerbit.

INSERT INTO Tb_BukuBaru
(kd_buku,
judul,
penulis,
penerbit,
harga)
SELECT kd_buku,judul,penulis,'Ambar Offset',harga from Tb_Buku

- UPDATE
Perintah update digunakan untuk mengubah data dalam tabel. Secara umum sintaknya adalah seperti berikut :

UPDATE NamaTabel
SET NamaKolom1=Ekspresi1,
NamaKolom2=Ekspresi2,
...
WHERE kriteria

Contoh :

1. Mengubah data berdasarkan harga atribut primary key

UPDATE Tb_Buku
SET judul='Sistem Basis Data',
penulis='Mia',
harga=60000
WHERE kd_buku='BK01'

2. Mengubah sekelompok baris berdasarkan kriteria tertentu.
Menaikan harga buku 50% untuk buku yang penerbitnya 'Ambar Offset'

UPDATE Tb_Buku
SET harga=1.5*harga
WHERE penerbit='Ambar Offset'

3. Mengubah semua baris dalam tabel

UPDATE Tb_Buku
SET harga=1.5*harga

4. Mengosongkan kolom tertentu

UPDATE Tb_Buku
SET harga=NULL
WHERE penerbit='Ambar Offset'

- DELETE
Perintah delete digunakan untuk menghapus data dalam tabel. Secara umum sintaknya adalah seperti berikut :

DELETE FROM NamaTabel WHERE kriteria

Contoh :

1. Menghapus suatu baris yang kode bukunya 'BK01'

DELETE FROM Tb_Buku WHERE kd_buku='BK01'

2. Menghapus sekelompok baris yang penerbitnya 'Ambar Offset'

DELETE FROM Tb_Buku WHERE penerbit='Ambar Offset'

3. Menghapus semua baris (mengosongkan tabel).

DELETE FROM Tb_Buku

-DISTINCT digunakan untuk memilih hanya nilai yang berbeda

Sintaks : select distinct nama_kolom from nama_tabel

-LIKE di gunakan untuk menentukan pencarian berdasarkan pola tertentu pada suatu kolom.

Sintaks  : select nama_kolom from nama_tabel like pola

Like bisa juga digunakan untuk menentukan sembarang huruf baik sebelum maupun sesudah pola.

Sintaks : select * fron mahasiswa where namadepan LIKE ‘D%’ ( digunakan untuk mencari nama mahasiswa yang di mulai dengan huruf D )

Select * from mahasiswa where namadepan LIKE ‘%D’ (digunakan untuk mencari nama mahasiswa yang di akhiri dengan huruf D )

-ALIAS di gunakan untuk memberikan nama lain atau alias pada nama_kolom dan nama_tabel.

Sintaks : select nama_kolom AS nama_alias_kolom from nama_tabel  ( memberikan nama lain pada kolom)

                  Select * from nama_tabel AS nama_alias_tabel ( alias untuk table)

Ex :select NamaDepan AS ND, NamaBelakang AS NB from Mahasiswa ( memberikan nama lain pada kolom)

                  Select * from Jurusan AS JR

-ORDER BY digunakan untuk mengurutkan data .

Sintaks : Select * from mahasiswa order by nama ASC ( di gunakan untuk urutan data dari kecil ke besar)

Select * from mahasiswa order by nim DESC (untuk urutan data dari besar ke kecil )

-AND dimana data akan di tampilkan pada kedua kondisi sesuai (sama).

Sintaks : select * from mahasiswa where nama=‘ajo’ and nama=‘kiki’

-OR dimana data akan ditampilkan  jika salah satunya sesuai (sama).

Sintaks : select * from mahasiswa where nama=‘kiki’ or nama=‘ajo’

-GROUP BY  digunakan untuk mengelompokkan.

Sintaks : select nama_kolom, COUNT ( nama_kolom) from nama_tabel GROUP BY nama_kolom

-HAVING digunakan untuk menentukan kondisi bagi GROUP BY.

Sintaks : select nama_kolom, COUNT (nama_kolom) from nama_tabel GROUP BY nama_kolom HAVING COUNT (nama_kolom) kondisi nilai

-COUNT digunakan untuk menghitung jumlah baris suatu kolom pada table .

Sintaks : select count (namadepan) from mahasiswa

-SUM digunakan untuk menghitung jumlah nilai kolom pada table.

Sintaks : SELECT SUM(harga_jual) FROM barang

-AVG digunakan untuk menghitung rata-rata .

Sintaks : SELECT AVG(harga_jual) FROM barang where tipe_barang=’minuman’

-MIN digunakan untuk menampilkan nilai terkecil.

Sintaks : SELECT MIN(harga_jual) FROM barang

-MAX digunakan untuk menampilkan nilai terbesar.

Sintaks : SELECT MAX(harga_jual) FROM barang

-UPPER digunakan untuk merubah huruf besar semua.

Sintaks : Select upper(nama_barang) from barang where tipe_barang=’obat’

-ROUND digunakan untuk Membulatkan nilai  bilangan ke pembulatan terdekat.

Sintaks : SELECT ROUND(harga _beli,1) from barang where nama_barang=’Pulpen A’

Operator IN digunakan jika mengetahui nilai pasti yang akan di ambil .

Sintaks : select * from mahasiswa where namadepan IN (‘Dede’,’Apip’)

-INNER JOIN digunakan untuk menampilkan data dari dua table yang berisi data.

Sintaks : SELECT nama_kolom
FROM nama_tabel1
INNER JOIN nama_tabel2
ON nama_tabel1.nama_kolom=nama_tabel2.nama_kolom

ex        = SELECT *

                  FROM barang

                  INNER JOIN transaksi

                  ON barang.id_b=transaksi.id_b

-LEFT JOIN digunakan untuk menampilkan semua data dari table sebelah kiri.

Sintaks : SELECT column_name
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Ex : SELECT * 

FROM member

LEFT JOIN pembelian

ON member.id_m=pembelian.id_m

-RIGHT JOIN digunakan untuk menampilkan semua data dari table sebelah kanan.

Sintaks : SELECT column_name
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Ex : SELECT * 

FROM member

RIGHT JOIN pembelian

ON member.id_m=pembelian.id_m

-SELECT INTO digunakan untuk membuat backup suatu table.

Sintaks : select * INTO mahasiswa_backup

OPERATOR ANY berkaitan dengan subquery.

Sintaks :  select *from matakuliah where sks>any (select sks from matakuliah )

-OPERATOR ALL digunakan untuk melakukan perbandingan dengan subquery.

Sintaks : select *from matakuliah where sks >= all ( select sks from matakuliah )

-UNION digunakan untuk menggabungkan hasil query dengan ketentuan jumlah, nama dan tipe kolom dari masing-masing table yang akan di tampilkan datanya harus sama.

Sintaks : select namadepan,tmpt_lhr from mahasiswa where tmpt_lhr=”indramayu” Union select namadepan,tmpt_lhr from mahasiswa where tmpt_lhr=”bandung”

-INTERSECT digunakan untuk memperoleh data dari 2 buah query dimana data yang akan ditampilkan adalah yang memenuhi ke 2 query tsb.

Sintaks : select * from nama_tabel1 INTERSECT select * from nama_tabel2

-EXCEPT digunakan untuk memperoleh data dari 2 buah query dimana data yang akan di tampilkan adalah data yang ada pada hasil query 1 dan tidak terdapat pada data dari hasil query2.

Sintaks : select * from nama_tabel1 EXPECT select * from nama_tabel2.