SQL STT-PLN

SUB QUERY & VIEW

Senin, Desember 21, 2015 Rikho Pratama 0 Comments Category : join , postgree , select , sql , sub query , view , where

SUB QUERY

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

QUERY

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

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

Penyelesaian …

Tampilkan seluruh transaksi pembelian (tanggal pembelian dan jumlah pembelian).
Tampilkan jumlah pembelian rata-rata pada transaksi pembelian.
Tampilkan transaksi pembelian yang jumlahnya diatas rata-rata.

Jawaban ...

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

SELECT tgl_pembelian, jum_pembelian FROM pembelian

Tampilkan jumlah pembelian rata-rata pada transaksi pembelian.

SELECT avg(jum_pembelian) as rata2_pembelian

FROM pembelian

Tampilkan transaksi pembelian yang jumlahnya diatas rata-rata.

SELECT tgl_pembelian, jum_pembelian

FROM pembelian

WHERE jum_pembelian > (SELECT avg(jum_pembelian)

FROM pembelian)

Latihan
Dari tabel nilai yang telah pernah anda buat sebelumnya carilah nilai mahasiswa yang nilainya di atas rata-rata ???

Output:

VIEW

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

QUERY
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

Contoh Query:

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

SELECT * from v_barang

LATIHAN

Tampilkan nama barang yang mempunyai stok kurang dari rata-rata.
Tampilkan pelanggan yang paling besar pembeliannya
Buatlah view dengan nama V_PELANGGAN yang menampilkan nama pelanggan,alamat dan telepon. Pelanggan yang ditampilkan adalah yang berasal dari kota Jakarta.
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
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 ada pada view V_JUM_BELI

ALJABAR RELATIONAL, DISTINCT DAN JOIN

Minggu, Desember 20, 2015 Rikho Pratama 0 Comments Category : aljabar relational , cross join , distinct , intersecton , join , natural join , postgree , set difference , sql , union

ALJABAR RELATIONAL

UNION (PENGGABUNGAN)

Pada prinsipnya bertujuan untuk menggabungkan dua query atau lebih menjadi satu kesatuan query.

Syarat :

Kedua query atau sub query yang akan digabung harus mempunyai domain kolom yang sama.

QUERY
SELECT field-1,…, field-n, aggregate_function
FROM tabel
[WHERE] [kriteria]
[GROUP BY] [field-1,…,field-n]
UNION
SELECT field-1,…, field-n, aggregate_function
FROM tabel
[WHERE] [kriteria]
[GROUP BY] [field-1,…,field-n]

CONTOH

QUERY 1

Tampilkan kode pelanggan, berapa kali pembelian dan total pembelian per-pelanggan. Kode_pelanggan yang ditampilkan adalah ‘J-0001’

SELECT Kode_Pelanggan,

COUNT (JUMLAH_PEMBELIAN) as CACAH_BELI

SUM (JUMLAH_PEMBELIAN) as TOTAL_BELI

FROM pasok

WHERE Kode_supplier = ‘J-0001’

GROUP BY Kode_Pelanggan

QUERY 2

Tampilkan kode pelanggan, berapa kali pembelian dan total pembelian per pelanggan. Kode_pelanggan yang ditampilkan adalah ‘J-0002’

SELECT Kode_Pelanggan,

COUNT(JUMLAH_PEMBELIAN) as CACAH_BELI

SUM (JUMLAH_PEMBELIAN) as TOTAL_BELI

FROM pasok

WHERE Kode_supplier = ‘J-0002’

GROUP BY Kode_Pelanggan

UNION QUERY 1 & QUERY 2

Tampilkan kode pelanggan, berapa kali pembelian dan total pembelian per pelanggan. Kode_pelanggan yang ditampilkan adalah ‘J-0001 dan J-0002’.

SELECT Kode_Pelanggan,

COUNT (JUMLAH_PEMBELIAN) as CACAH_BELI

SUM (JUMLAH_PEMBELIAN) as TOTAL_BELI

FROM pasok

WHERE Kode_supplier = ‘J-0001’

GROUP BY Kode_Pelanggan

UNION

SELECT Kode_Pelanggan,

COUNT (JUMLAH_PEMBELIAN) as CACAH_BELI

SUM (JUMLAH_PEMBELIAN) as TOTAL_BELI

FROM pasok

WHERE Kode_supplier = ‘J-0002’

GROUP BY Kode_Pelanggan

Contoh Sederhana Union dalam bentuk tabel :

SELECT * FROM MHS

UNION

SELECT * FROM MHS2;

Union akan menggabungkan kedua tabel menjadi satu, jika ada kesamaan record dari kedua tabel maka hanya akan ditampilkan satu saja.

INTERSECTION (PERPOTONGAN)

Syarat :

Kedua query atau sub query yang akan digabung harus mempunyai domain kolom yang sama.

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

FROM tabel

[WHERE] [kriteria]

[GROUP BY] [field-1,…,field-n]

INTERSECT

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

FROM tabel

[WHERE] [kriteria]

[GROUP BY] [field-1,…,field-n]

CONTOH:

QUERY 1

Tampilkan kode pelanggan, berapa kali pembelian dan total pembelian per-pelanggan. Kode_pelanggan yang ditampilkan adalah ‘J-0001’

SELECT Kode_Pelanggan,

COUNT (JUMLAH_PEMBELIAN) as CACAH_BELI

SUM (JUMLAH_PEMBELIAN) as TOTAL_BELI

FROM pasok

WHERE Kode_supplier = ‘J-0001’

GROUP BY Kode_Pelanggan

QUERY 2

Tampilkan kode pelanggan, berapa kali pembelian dan total pembelian per pelanggan. Kode_pelanggan yang ditampilkan adalah ‘J-0002’

SELECT Kode_Pelanggan,

COUNT(JUMLAH_PEMBELIAN) as CACAH_BELI

SUM (JUMLAH_PEMBELIAN) as TOTAL_BELI

FROM pasok

WHERE Kode_supplier = ‘J-0002’

GROUP BY Kode_Pelanggan

INTERSECTION QUERY 1 & QUERY 2

Tampilkan kode pelanggan, berapa kali pembelian dan total pembelian per pelanggan. Kode_pelanggan yang ditampilkan adalah ‘J-0001 dan J-0002’

SELECT Kode_Pelanggan,

COUNT (JUMLAH_PEMBELIAN) as CACAH_BELI

SUM (JUMLAH_PEMBELIAN) as TOTAL_BELI

FROM pasok

WHERE Kode_supplier = ‘J-0001’

GROUP BY Kode_Pelanggan

INTERSECT

SELECT Kode_Pelanggan,

COUNT (JUMLAH_PEMBELIAN) as CACAH_BELI

SUM (JUMLAH_PEMBELIAN) as TOTAL_BELI

FROM pasok

WHERE Kode_supplier = ‘J-0002’

GROUP BY Kode_Pelanggan

Contoh Sederhana intersection dalam bentuk tabel :

SELECT * FROM MHS

INTERSECT

SELECT * FROM MHS2;

Intersection akan menggabungkan kedua tabel menjadi satu, record yang ditampilkan hanya record yang sama dari kedua tabel. Yang menjadi acuan adalah tabel pertama.

SET DIFFERENCE

Set Difference digunakan untuk mengambil data yang berbeda dalam dua tabel yang sama.

Syarat :

Kedua query atau sub query yang akan digabung harus mempunyai domain kolom yang sama.

Contoh Sederhana set difference dalam bentuk tabel :

SELECT * FROM MHS

MINUS

SELECT * FROM MHS2;

Set Difference akan menggabungkan kedua tabel menjadi satu, record yang ditampilkan hanya record yang berbeda dari tabel pertama dengan tabel kedua.

CROSS JOIN

Contoh Sederhana cross join dalam bentuk tabel :

SELECT *
FROM MHS CROSS JOIN NILAI;

Cross join akan menggabungkan kedua tabel. Masing masing record dari tabel pertama akan di gabungkan dengan semua record di tabel kedua.

NATURAL JOIN

Contoh Sederhana natural join dalam bentuk tabel :

SELECT *

FROM MHS NATURAL JOIN NILAI;

Natural join akan menggabungkan kedua tabel. Record di tabel pertama akan digabungkan dengan record di tabel kedua jika memiliki Key yang sama.

PROYEKSI / DISTINCT

Pada dasarnya distinct digunakan untuk menghilangkan nilai ganda. Jadi nilai yang sama dan diulang-ulang akan dijadikan nilai tunggal atau satu nilai saja.

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

FROM tabel

WHERE [kriteria]

GROUP BY [field-1,…,field-n]

Tampilkan jumlah pembelian berdasarkan kode barang dan tanggal pembelian

Perhatikan !

Sebelum ada perintah distinct :

SELECT Kode_Barang, Tanggal_Pembelian, Jumlah Pembelian

FROM Pembelian

GROUP BY Kode_Barang, Tanggal_Pembelian

Setelah ada perintah distinct :

SELECT DISTINCT Kode_Barang, Tanggal_Pembelian, Jumlah Pembelian

FROM Pembelian

GROUP BY Kode_Barang, Tanggal_Pembelian

JOIN

Pada prinsipnya Join/Inner join/Normal Join/Equal Join bertujuan untuk mencari kesamaan data antara tabel yang satu dengan tabel yang lain.

Data yang sama tersebut akan ditampilkan setelah terjadi operasi join ini.

SELECT tabel-1.field-1,…, tabel-1.field-n,

tabel-2.field-1,…, tabel-2.field-n,

tabel-n.field-1,…, tabel-n.field-n,

FROM tabel.1,…,tabel-2,…,tabel-n

WHERE tabel-1.field_PK = tabel-2.field_PK AND

tabel.2.field_PK = tabel-n.field_PK

Tampilkan nama barang, tanggal berapa terjadi pembelian dan berapa jumlah pembelian barang

SELECT barang.NAMA_BARANG,pembelian.TGL_PEMBELIAN, pembelian.JUMLAH_PEMBELIAN

FROM barang, pembelian

WHERE barang.KODE_BARANG=pembelian.KODE_BARANG

Menggunakan sintak join :

SELECT barang.NAMA_BARANG,pembelian.TGL_PEMBELIAN, pembelian.JUMLAH_PEMBELIAN

FROM barang JOIN pembelian

ON barang.KODE_BARANG=pembelian.KODE_BARANG

LATIHAN!!!

UNION

Tampilkan NIM, berapa kali nilai yang muncul dan total nilai per mahasiswa. NIM yang ditampilkan adalah ‘201381002’
Tampilkan NIM, berapa kali nilai yang muncul dan total nilai per mahasiswa. NIM yang ditampilkan adalah ‘201381005’
Dengan UNION Tampilkan NIM, berapa kali nilai yang muncul dan total nilai per mahasiswa. NIM yang ditampilkan adalah ‘201381002’ dan ‘201381005’

Output :

DISTINCT

Tampilkan nilai berdasarkan kode mata kuliah, dengan menghilangkan data yang bernilai ganda

JOIN

Dengan menggunakan sintax join tampilkan nama matakuliah, SKS, dan nilai

Tampilkan nama mahasiswa, kota dan nilai

Tampilkan nama mata kuliah, nama mahasiswa,dan nilai

SQL STT-PLN

SUB QUERY & VIEW

SUB QUERY

VIEW

ALJABAR RELATIONAL, DISTINCT DAN JOIN

recent posts

Arsip Blog

Popular Posts