1.  SUB QUERY

Sub query adalah query di dalam query, artinya seleksi data berdasarkan dari hasil seleksi data yang telah ada.

SELECT field-1,…,field-n

FROM tabel

WHERE kriteria ( SELECT field-1,…,field-n

FROM tabel

WHERE kriteria );

CONTOH…

Jika diketahui terdapat beberapa table berikut :

1. Tabel_Barang

kode_barang char(6),

nama_barang varchar(25),

satuan varchar(20) dan

stok int,

primary key : kode_barang

2. Tabel Pelanggan

Kode_Pelanggan char(6),

Nama varchar(30), Alamat varchar(30), kota varchar(15),

Telepon varchar(15)

primary key : kode_Pelanggan

3. Tabel Pembelian

kode_pembelian char(10) ,kode_barang char(6),

kode_pelanggan char(6), tgl_pembelian datetime,

jum_pembelian int.

Primary key : kode_pembelian,kode_barang,kode_pelanggan

Foreign key : kode_barang, kode_Pelanggan

 

CONTOH…

Dari ketiga tabel diatas tampilkan transaksi pembelian yang jumlahnya di atas rata-rata ….??

Penyelesaian …

1. Tampilkan seluruh transaksi pembelian (tanggal pembelian dan jumlah pembelian).

2. Tampilkan jumlah pembelian rata-rata pada transaksi pembelian.

3. Tampilkan transaksi pembelian yang jumlahnya diatas rata-rata.

CONTOH…

1. Tampilkan seluruh transaksi pembelian (tanggal pembelian dan jumlah pembelian).

SELECT tgl_pembelian, jum_pembelian FROM pembelian

2. Tampilkan jumlah pembelian rata-rata pada transaksi pembelian.

SELECT avg(jum_pembelian) as rata2_pembelian

FROM pembelian

3. Tampilkan transaksi pembelian yang jumlahnya diatas rata-rata.

SELECT tgl_pembelian, jum_pembelian

FROMpembelian

WHERE jum_pembelian > (SELECT avg(jum_pembelian)

FROM pembelian)

2. VIEW

View merupakan tabel semu, artinya secara fisik data tidak tersimpan dalam basis data, tetapi secara lojik dapat berfungsi seolah-olah sebagai tabel.

CREATE VIEW nama_view AS

SELECT field-1,…,field-n, aggregate function

FROM tabel-1,…, tabel-n

WHERE kriteria

GROUP BY field-1,…,field-n

HAVING kriteria_aggregate_function

ORDER BY field

Buat view bernama v_barang yang menampilkan nama barang dan stok barang.

CREATE VIEW v_barang as

SELECT nama_barang, stok_barang

FROM barang

Tampilkan semua data yang ada pada view v_barang.

SELECT *from v_barang

Tampilkan nama barang dari view v_barang.

SELECT nama_barang from v_barang

Hapuslah view v_barang.

DROP view v_barang

Tampilkan view v_barang (apakah yang terjadi?)

SELECT * from v_barang

1. Tampilkan nama barang yang mempunyai stok kurang dari rata-rata.

 

2. Tampilkan pelanggan yang paling besar pembeliannya

3. Buatlah view dengan nama V_PELANGGAN yang menampilkan nama pelanggan,alamat dan telepon. Pelanggan yang ditampilkan adalah yang berasal dari kota Jakarta.

4. Buatlah view V_PLGN_BELI yang menampilkan jumlah pembelian per pelanggan untuk tanggal tertentu. Field yang ditampilkan adalah nama pelanggan jumlah pembelian, tanggal pembelian. Tampilkan semua data yang ada pada view V_PLGN_BELI

5. Buatlah view V_JUM_BELI yang menampilkan jumlah beli per barang per pelanggan per tanggal tertentu. Field yang ditampilkan adalah nama barang, nama pelanggan, tanggal pembelian,dan jumlah pembelian.Tampilkan semua data yang 

View adalah salah satu object database, yang secara logika merepresentasikan sub himpunan dari data yang berasal dari satu atau lebih table.

View merupakan hasil perintah SELECT yang berupa tabel virtual yang dapat membungkus query tertentu. View berfungsi untuk mempermudah dalam pengambilan query tertentu. Dengan view kita dapat menerapkan pembatasan pada pengaksesan guna pengamanan keamanan data seperti di bawah ini:

• Kolom/Field pada tabel tertentu.
• Baris/Record pada tabel tertentu.
• Field dan Record pada tabel tertentu.
• Turunan dari view lain.
• Record menggunakan operasi join.
• Data statistik dari tabel.

Kegunaan dari view adalah :

• Membatasi akses database
• Membuat query kompleks secara mudah
• Mengijinkan independensi data
• Untuk menampilkan view (pandangan) data yang berbeda dari data yang sama.

Cara membuat view :

Untuk membuat view kita dapat menggunakan perintah view dengan format seperti dibawah ini :

CREATE VIEW view_name[(column_list)] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION]

Keterangan : 

View_name adalah nama view yang akan dibuat. Column_list mendefinisikan field yang akan digunakan dalam view. Column_list harus memiliki jumlah field yang sama dengan yang didefinisikan pada select_statement. Jika column_list tidak ada maka view dibuat berdasarkan field yang didefinisikan pada select_statement. With Encryption adalah opsi untuk menambahkan enkripsi pada view. AS berisi perintah yang digunakan untuk membuat view.

Contoh :

CREATE VIEW V_ SISWA AS SELECT * FROM SISWA

Cara menampilkan view :

Untuk menampilkan atau memanggil view yang sudah kita buat sebelumnya, kita dapat menggunakan perintah select dengan format seperti dibawah ini :

SELECT * FROM V_PELANGGAN

Cara merubah view :

Untuk merubah view kita dapat menggunakan perintah alter view dengan format seperti dibawah ini :

ALTER VIEW view_name[(column_list)] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION]

Contoh :

ALTER VIEW V_SISWA AS SELECT * FROM SISWA WHERE ALAMAT ='JAKARTA'

Cara menghapus view :

Untuk menghapus view kita dapat menggunakan perintah drop view dengan format seperti dibawah ini :

DROP VIEW view_name

Contoh :

DROP VIEW V_PELANGGAN

  sumber : http://ranafatinhexi.blogspot.com/2013/03/view-pada-oracle.html

Read more...

Read more...

Pengelompokkan dan Pengurutan Data

Group by dan Aggregate Function

Dalam Menjalankan sebuah query untuk melakukan pengelompokan data dapat menggunakan Fungsi Group by dan di ikuti oleh Fungsi Aggregat.

·         Group by : digunakan untuk menampilkan atau memilih sekumpulan data berdasarkan kelompok data tertentu.

_ Pengelompokan nya biasa nya di sertai oleh Aggregat Fuction

_ Dalam Implementasi nya Aggregat Function harus diikuti oleh Group by bila terdapat Field lain yang dijadikan kriteria pengelompokan

·         Aggregate Function : Min(), Max(), Avg(), Sum(), Count()

Group by dan Aggregate Function

·         MIN()

Fungsi MIN digunakan untuk mencari nilai minimum dari sekumpulan data yang ada

·         

          Syntax :

SELECT field-1,…,field-n,

MIN(nama_field)

FROM tabel

GROUP BY field-1,…,field-n

·        

          MAX()

Fungsi MAX digunakan untuk mencari nilai maksimum dari

sekumpulan data yang ada

·         Syntax :

SELECT field-1,…,field-n,

MAX(nama_field)

FROM tabel

GROUP BY field-1,…,field-n

·         

           AVG()

Fungsi AVG digunakan untuk mencari nilai rata-rata dari sekumpulan data yang ada

Syntax :

SELECT AVG (nama_field),

AS Rata-Rata

FROM table

Atau

 

SELECT (nama_field)

AVG (nama_field) AS (nama_field_baru)

FROM tabel

GROUP BY (nama_field)

·          

      SUM()

Fungsi SUM digunakan untuk menjumlahkan nilai dari sekumpulan data yang ada

Syntax :

SELECT field-1,…,field-n,

SUM(nama_field)

FROM tabel

GROUP BY field-1,…,field-n

·         COUNT()

Fungsi COUNT digunakan untuk mencari cacah atau banyaknya data

Syntax:

SELECT field-1,…,field-n,

COUNT (nama_field)

FROM tabel

GROUP BY field-1,…,field-n

·         ORDER BY

Digunakan untuk mengurutkan data berdasarkan field tertentu

 

Syntax:

SELECT field-1,field-2,…,field-n

FROM tabel

ORDER BY (field)

urutkan jumlah berdasarkan jumlah pasokan paling sedikit

SELECT kode_pasok, jumlah_pasok

FROM pasok

ORDER BY jumlah_pasok

urutkan jumlah berdasarkan jumlah pasokan paling banyak

SELECT kode_pasok, jumlah_pasok

FROM pasok

ORDER BY jumlah_pasok DESC

Keriteria Data

·         HAVING

Menyeleksi data berdasarkan kriteria tertentu, dimana kriteria berdasarkan berupa fungsi aggregate

Syntax:

SELECT field-1,…, field-n, aggregate_function

FROM tabel

GROUP BY field-1,…,field-n

HAVING kriteria_aggregate_function

Contoh :

SELECT kode_customer,

COUNT (jumlah_pembelian) AS cacah_pembelian

FROM pembelian

GROUP BY kode_customer

HAVING COUNT(*)=4

Where…

KondisiWhere, digunakan untuk melakukan pemilihan/seleksi data. Penggunaannya dilakukan setelah kata where, dan diikuti oleh :

• ·         comparison (=,<>,<,>,>=,<=),

• ·         between,

• ·         in,

• ·         like/not like

 1. Comparison

Berfungsi untuk membandingkan dua nilai. Tipe data yang seharusnya dibandingkan harus sesuai. Hasil yang diperoleh dari operasi comparison ini berupa nilai logik.

 

Syntax :

SELECT field-1,…, field-n

FROM tabel

WHERE field = | <> | < | > | <= | >= …

Contoh :

Tampilkan kode barang dan kode suplier yang mempunyai

jumlah pasok 2

SELECT kode_barang, kode _suplier FROM pasok

WHERE jumlah_pasok=2

Tampilkan kode barang dan kode suplier yang jumlah

pasoknya kurang atau sama dengan 5

contoh :

SELECT kode_barang, kode _suplier FROM pasok

WHERE jumlah_pasok<=5

 2. Between

Pada prinsipnya between digunakan untuk membandingkan/untuk mengecek apakah suatu nilai berada dalam range atau nilai tertentu.

 

Syntax:

SELECT field-1,…, field-n

FROM tabel

WHERE field BETWEEN … AND …

Contoh :

Tampilkan kode barang dan kode customer yang jumlah pasoknya antara 4 dan 15 (4 dan 15 termasuk di dalamnya

SELECT kode_barang, kode_customer

FROM pembelian

WHERE jumlah_pembelian BETWEEN 4 AND 15

3. In

Digunakan untuk melakukan pengecekan apakah suatu nilai terdapat pada suatu himpunan tertentu.

SELECT field-1,…, field-n

FROM tabel

WHERE field IN (…, …, …)

Contoh :

Tampilkan kode barang dan kode suplier yang jumlah pasoknya 2,8,12

SELECT kode_barang, kode_suplier

FROM pasok

WHERE jumlah_pasok IN(2,8,12)

ORDER BY jumlah_pasok

4. Like / Not Like…

Digunakan untuk membandingkan data dengan pola tertentu.

 

SELECT field-1,…, field-n

FROM tabel

WHERE field LIKE | NOT LIKE

Tampilkan customer yang mempunyai kata ‘CIM’ pada alamatnya (pada alamat customer terdapat kata‘CIM’)

SELECT *

FROM customer

WHERE alamat_customer LIKE ‘%CIM%’

Tampilkan customer yang mempunyai kata selain ‘CIM’ pada alamatnya

SELECT *

FROM customer

WHERE alamat_customerNOT LIKE ‘%CIM%’

Agregasi dalam SQL merupakan proses untuk mendapatkan nilai dari sekumpulan data yang telah dikelompokkan.  Pengelompokan data didasarkan pada kolom atau kombinasi kolom yang dipilih.
      

Beberapa fungsi untuk agregasi adalah:

1. MAX : mencari data terbesar dari sekelompok data
2. MIN : mencari data terkecil dari sekelompok data
3. COUNT : mencari cacah data (data NULL tidak akan dimasukkan dalam perhitungan,kecuali disebutkan secara khusus)
4. SUM : mencari jumlah dari sekumpulan data numeris
5. AVG : mencari rerata dari sekumpulan data numeris

Sintaks SQL untuk melakukan pengelompokan adalah:

SELECT [daftar-kolom-non-agregasi,] daftar-kolom-agregasi
FROM daftar-tabel
[WHERE kondisi-record-dalam-tabel]
[GROUP BY daftar-kolom-agregasi]
[HAVING kodisi-filter-hasil-agregasi]
[ORDER BY daftar-kolom-pengurutan]

 

Soal Latihan :

1.  Tampilkan jumlah nilai minimum per matakuliah 

 

 

 

2. Tampilkan nilai maksimum dari semua data pada tabel NILAI 

 

3.Tampilkan nim dan kode mata kuliah yang mempunyai nilai 90

 

 

4.  Tampilkan nim dan kode mata kuliah yang mempunyai nilai kurang dari 90

5.  Tampilkan data mahasiswa yang dari kota berakhiran “KARTA”

 

 

Read more...

create table mahasiswa (
nim char(10),
nama varchar(15),
alamat varchar(50),
kota varchar(15),
constraint pk_mahasiswa primary key (nim)
);

create table matakuliah1 (
kode_MK char(6),
nama_MK varchar(30),
semester char(1),
sks number(2),
constraint pk_matakuliah1 primary key (kode_MK)
);

create table nilai1 (
nim char(10),
kode_MK char(6),
nilai number(2),
constraint fk_nim_mk foreign key (nim)
references mahasiswa(nim),
constraint fk_nilai_mk foreign key (kode_MK)
references matakuliah1(kode_MK)
);

alter table mahasiswa
add kodepos char (8);

alter table mahasiswa
modify kodepos varchar2(5);

alter table mahasiswa
modify alamat varchar2(50);

alter table nilai1
modify nilai number;

INSERT INTO mahasiswa
 VALUES (201291001,'alfa','Jl.Duri Kosambi No.10','Jakarta',12345);
INSERT INTO mahasiswa
 VALUES (201291002,'betta','Jl.malioboro No.10','Yogyakarta',23456);
INSERT INTO mahasiswa
 VALUES (201291003,'charly',NULL,NULL,NULL);
INSERT INTO mahasiswa
 VALUES (201291004,'delta','Jl.Riau No.10','Bandung',34567);
INSERT INTO mahasiswa
 VALUES (201291005,'echo',NULL,NULL,NULL);


INSERT INTO matakuliah1
 VALUES ('MKI001','SQL',4,2);
INSERT INTO matakuliah1
 VALUES ('MKI002','Sistem Basis Data',5,2);
INSERT INTO matakuliah1
 VALUES ('MKI003','Bahasa Pemrograman',6,3);
INSERT INTO matakuliah1
 VALUES ('MKI004','Perancangan Basis Data',4,2);

INSERT INTO nilai1
 VALUES (201291001,'MKI001',60);
INSERT INTO nilai1
 VALUES (201291002,'MKI002',80);
INSERT INTO nilai1
 VALUES (201291003,'MKI001',65);
INSERT INTO nilai1
 VALUES (201291004,'MKI004',80);
INSERT INTO nilai1
 VALUES (201491001,'MKI001',75);
INSERT INTO nilai1
 VALUES (201291001,'MKI002',50);
INSERT INTO nilai1
 VALUES (201291002,'MKI003',45);
INSERT INTO nilai1
 VALUES (201291003,'MKI004',90);
INSERT INTO nilai1
 VALUES (201291004,'MKI001',70);
INSERT INTO nilai1
 VALUES (201291005,'MKI001',75);

UPDATE mahasiswa
SET kota = 'semarang'
WHERE nim = 201291004;

UPDATE mahasiswa
SET alamat = NULL
WHERE nim = 201291005;

UPDATE nilai1
SET kode_MK = 'MKI003'
WHERE nim = 201291003;

DELETE FROM mahasiswa
WHERE nim=201291001;

SELECT NIM, nama, alamat, kota, kodepos
FROM mahasiswa;

SELECT *
FROM matakuliah1;

SELECT *
FROM nilai1;

select mahasiswa.nim, mahasiswa.nama, matakuliah1.nama_MK
from mahasiswa, matakuliah1, nilai1
where nilai1.nim=mahasiswa.nim and nilai>65 and nilai1.kode_MK=matakuliah1.kode_MK

update nilai1
set nilai1.nilai=100
where nilai1.kode_MK ='MKI001' and nilai1.nim=201291001 and nilai1.nilai=60;

UPDATE matakuliah1
SET nama_MK = 'Algoritma Pemrograman'
WHERE nama_MK = 'Bahasa Pemrograman';

DELETE from nilai1
WHERE nim = 201291005

TABEL NILAI

soal :

1. Tampilkan Data pada seluruh tabel Mahasiswa

2. Tampilkan NIM, Nama, Nama_MK Mahasiswa yang nilainya lebih dari 65

3. Ganti nilai pada matakuliah SQL atas nama Mahasiswa Alfa menjadi 100

 4. Ganti Nama Matakuliah “Bahasa Pemograman” menjadi “Algoritma
Pemograman”

5. Hapus Data dengan nim 201291005

Read more...