Aljabar Relational dan SQL - SUB QUERY & VIEW

Aljabar Relational

Diketahui 3 tabel yang masing-masing berisi 15 data yang sudah berelasi sebagai berikut :

Tabel Mahasiswa

Tabel Matakuliah

Tabel Nilai

1. UNION

A. Tampilkan NIM, berapa kali nilai yang muncul dan total nilai per mahasiswa. NIM yang ditampilkan adalah '201391002' 

B. Tampilkan NIM, berapa kali nilai yang muncul dan total nilai per mahasiswa. NIM yang ditampilkan adalah '201391005' 

C. Dengan UNION tampilkan NIM, berapa kali nilai yang muncul dan total nilai per mahasiswa. NIM yang ditampilkan adalah '201391002' dan '201391005' 

2. DISTINCT

Tampilkan nilai berdasarkan kode matakuliah, dengan menghilangkan data yang bernilai ganda.

3. JOINT

A. Dengan menggunakan sintax JOINT tampilkan nama matakuliah, SKS dan nilai

B. Tampilkan nama mahasiswa, kota dan nilai

C. Tampilkan nama matakuliah, nama mahasiswa dan nilai

Sub Query

Diketahui 3 tabel yang masing-masing sudah berisi data dan saling berelasi sebagai berikut :

Tabel Barang

Tabel Suplier

Tabel Pasok

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

2. Tampilkan suplier yang paling besar jumlah pasoknya

3. Buatlah view dengan nama V_SUPLIER yang menampilkan nama suplier, alamat dan telepon. Suplier yang ditampilkan adalah yang berasal dari kota jakarta.

4. Buatlah view V_SUPLIER_SUPLAI yang menampilkan jumlah pembelian per suplier untuk tanggal tertentu, field yang ditampilkan adalah nama suplier, jumlah pasok, tanggal pasok. Tampilkan semua data yang ada pada view V_SUPLIER_SUPLAI.

5. Buatlah view V_JUM_PASOK yang yang menampilkan jumlah pasok per barang, per suplier, per tanggal tertentu. Field yang ditampilkan adalah nama barang, nama suplier, tanggal pasok dan jumlah pasok. Tampilkan semua data yang ada pada view V_JUM_PASOK.

"SUB QUERY & VIEW"

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

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

Penyelesaian …

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)

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

LATIHAN

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 ada pada view                     

  V_JUM_BELI

SQL - ALJABAR RELATIONAL

"ALJABAR RELATIONAL"

UNION (U)

UNION (Gabungan) 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 Query1 & Query2

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 :                              

  

Union akan menggabungkan kedua tabel menjadi satu, jika ada kesamaan record 

dari kedua tabel maka hanya akan ditampilkan satu saja.

SELECT * FROM MHS

UNION

SELECT * FROM MHS2;

INTERSECTION

INTERSECTION (Perpotongan). Intersection akan menggabungkan kedua tabel 

menjadi satu,  record yang ditampilkan hanya record yang sama dari kedua tabel. 

Yang menjadi acuan adalah tabel pertama. 

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 Query1 & Query2

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;

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 :                                 

Set Difference akan menggabungkan kedua tabel menjadi satu,  record yang 

ditampilkan hanya record yang berbeda dari  tabel pertama dengan tabel kedua.

SELECT * FROM MHS

MINUS

SELECT * FROM MHS2;

CROSS JOIN

Cross join akan menggabungkan kedua tabel. Masing masing record dari tabel pertama

akan di gabungkan dengan semua record di tabel kedua.

Contoh Sederhana cross join dalam bentuk tabel :                                

SELECT *

FROM MHS CROSS JOIN NILAI;

NATURAL JOIN

Natural join akan menggabungkan kedua tabel. Record di tabel pertama akan 

digabungkan dengan record di tabel kedua jika memiliki Key yang sama.

Contoh Sederhana natural join dalam bentuk tabel :                                

SELECT *

FROM MHS NATURAL JOIN NILAI;

PROYEKSI / DISTINCT

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

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!!!

1. UNION

a). Tampilkan NIM, berapa kali nilai yang muncul dan total nilai per mahasiswa. NIM yang  

ditampilkan adalah ‘201381002’

b). Tampilkan NIM, berapa kali nilai yang muncul dan total nilai per mahasiswa. 

NIM yang ditampilkan adalah ‘201381005’

c). Dengan UNION Tampilkan NIM, berapa kali nilai yang muncul dan total nilai 

per mahasiswa. NIM yang ditampilkan adalah ‘201381002’ dan ‘201381005’

2. DISTINCT

Tampilkan nilai berdasarkan kode mata kuliah, dengan menghilangkan data yang 

bernilai ganda

3. JOINT

a). Dengan menggunakan sintax joint tampilkan nama matakuliah, SKS, dan nilai

b). Tampilkan nama mahasiswa, kota dan nilai

c). Tampilkan nama mata kuliah, nama mahasiswa,dan nilai