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
Sub query adalah query di dalam query, artinya seleksi data berdasarkan
dari hasil seleksi data yang telah ada.
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
