TUGAS MAKALAH KELOMPOK VII
BAB II & BAB V
Senin, 01 Desember 2014
Selasa, 25 November 2014
progres tugas besar sql
create table guru(
kd_guru char (4),
nama_guru varchar (20),
alamat varchar(30),
jenis_kelamin char (1),
status_jabatan varchar (10),
no_tlp varchar (15),
constraint pk_guru primary key(kd_guru)
);
create table jadwal (
no number ,
nis char (5),
nama_siswa varchar (20),
kd_guru char (4),
nama_guru varchar (20),
kd_program char (5),
nama_program varchar (15),
hari char (10),
jam char (8),
constraint pk_jadwal primary key(no, kd_guru, nis),
constraint fk_jadwal foreign key (kd_guru)references guru (kd_guru),
constraint fk_jadwal1 foreign key (nis)references siswa (nis),
constraint fk_jadwal2 foreign key (kd_program)references program (kd_program)
);
create table siswa (
nis char (5),
nama_siswa varchar (20),
alamat varchar(30),
jenis_kel char(1),
nama_ortu varchar (20),
no_telpon varchar (15),
constraint pk_siswa primary key(nis)
);
create table gaji (
kd_guru char (4),
nama_guru varchar (20),
jml_hadir number,
total_gaji number,
constraint pk_gaji primary key(kd_guru),
constraint fk_gaji foreign key (kd_guru)references guru (kd_guru)
);
create table nilai_siswa (
nis char (5),
nama_siswa varchar (20),
kd_program char (5),
nilai number,
constraint pk_nilai_siswa primary key(nis, kd_program),
constraint fk_nilai_siswa foreign key (nis)references siswa (nis),
constraint fk_nilai_siswa1 foreign key (kd_program)references program (kd_program));
create table program (
kd_program char (5),
nama_program varchar (20),
kd_guru char (4),
nilai number,
constraint pk_program primary key(kd_program,kd_guru),
constraint fk_program foreign key (kd_guru)references guru (kd_guru));
Struktur Tabel
Senin, 24 November 2014
PERINTAH UNION INTERSECT DISTINCT DAN JOIN
1. 1. UNION berguna untuk
menampilkan hasil gabungan dari 2 tabel.
Buat
query SQL
SELECT
column_name (s)
FROM
table_name1
UNION
SELECT
column_name (s)
FROM
table_name2;
Contohnya:
SELECT
Nim,
COUNT
(Nilai) AS Cacah_Nilai,
SUM
(Nilai) AS Jumlah_Nilai
FROM
Nilai
WHERE
Nim ='201291002'
group
by Nim
union
SELECT
Nim,
COUNT
(Nilai) AS Cacah_Nilai,
SUM
(Nilai) AS Jumlah_Nilai
FROM
Nilai
WHERE
Nim ='201291005'
group
by Nim
2 2. INTERSECT berguna untuk menampilkan irisan
dari 2 tabel
Buat
query SQL
SELECT
column_name (s)
FROM
table_name1
WHERE
column_name (s) IN(SELECT column_name
(s)
FROM
table_name2);
3. 3. DISTINCT
Select distinct fungsinya adalah untuk menampilkan hasil
query mysql jika ada row yang isinya sama, maka hanya akan diambil salah satu
nya saja. Misal dalam tabel cabang, ada nama cabang : jakarta sebanyak 100 row
record data. Maka Jakarta akan di tampilkan hanya satu record. Select distinct
mysql juga bisa dikembangkan dalam bentuk lain. Digabungkan dan dikombinasikan
dengan syntax mysql lainnya. Berikut contoh syntax yang akan saya buat :
SELECT DISTINCT Nilai, Kode_MK
FROM Nilai
GROUP BY Kode_MK,Nim,Nilai
4. 4. JOIN
Cara kerja join ialah
dengan mencari kecocokan kolom tertentu pada sebuah tabel dengan kolom tertentu
pada tabel lain, kemudian menampilkan hasilnya sebagai satu set data gabungan.
Pada bahasa SQL, operasi join atau penggabungan "menyamping" antar
table adalah operasi dasar database relasional yang sangat penting
Contohya:
SELECT Nama,kota, Nilai
FROM Mahasiswa join Nilai
on Mahasiswa.Nim=Nilai.Nim
SELECT Nama,Nama_MK, Nilai
FROM Mahasiswa join Nilai join MataKuliah
on MataKuliah.Kode_MK=Nilai.Kode_MK
on Mahasiswa.Nim=Nilai.Nim
Referensi:
Senin, 17 November 2014
LATIHAN
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
Kode_barang Nama_Barang Satuan Stok
ELK-01 Rice cooker Buah 20
ELK-02 Lemari es Unit 8
ELK-03 Televisi Unit 30
ELK-04 Radio/tape Buah 35
CREATE TABLE Barang (
kode_barang char(6),
nama_barang varchar(25),
satuan varchar(20),
stok int,
constraint pk_Barang primary key (kode_barang)
);
INSERT INTO Barang
VALUES ('ELK-01','Rice Cooker','Buah',20);
INSERT INTO Barang
VALUES ('ELK-02','Lemari Es','Unit',8);
INSERT INTO Barang
VALUES ('ELK-03','Televisi','Unit',30);
INSERT INTO Barang
VALUES ('ELK-04','Radio/tape','Buah',35);
2. Tabel Pelanggan
Kode_Pelanggan char(6),
Nama varchar(30), Alamat varchar(30), kota varchar(15),
Telepon varchar(15)
primary key : kode_Pelanggan
Kode_Pelanggan Nama Alamat Kota Telepon
J-0001 Toko Karisma Jl cimandiri 25 Depok (021)-367868
J-0002 Toko Ayu Jl cimone 35 Tangerang (021)-467889
B-0001 Toko Warna Jl DEF 30 Jakarta (021)-234678
B-0002 Toko Surya Jl abc 309 Bandung (022)-356789
CREATE TABLE Pelanggan (
Kode_Pelanggan char(6),
Nama varchar(30),
Alamat varchar(30),
Kota varchar(15),
telepon varchar(15),
constraint pk_Pelanggan primary key (Kode_Pelanggan)
);
INSERT INTO Pelanggan
VALUES ('J-0001','Toko Karisma','Jl.Cimandiri 25','Depok','(021)-367868');
INSERT INTO Pelanggan
VALUES ('J-0002','Toko Ayu','Jl.Cimone 35','Tanggerang','(021)-467889');
INSERT INTO Pelanggan
VALUES ('B-0001','Toko Warna','Jl.DEF 30','Jakarta','(021)-234678’);
INSERT INTO Pelanggan
VALUES ('B-0002','Toko Surya ','Jl.abc 309','Bandung','(022)-356789)';
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
Kode_Pembelian Kode_Barang Kode_Pelanggan Tgl_Pembelian Jum_Pembelian
BEL-E001 ELK-01 J-0001 20-may-02 3
BEL-E002 ELK-02 J-0001 21-may-02 4
BEL-E003 ELK-01 J-0002 20-may-02 2
BEL-E004 ELK-01 B-0001 20-may-02 2
BEL-E005 ELK-03 B-0002 22-may-02 3
BEL-E006 ELK-02 J-0001 24-jun-02 1
create table pembelian (
kode_pembelian char(10),
kode_barang char(6),
kode_pelanggan char(6),
tgl_pembelian date,
jum_pembelian int,
constraint pk_pembelian primary key (kode_pembelian),
constraint fk_kode_barang foreign key (kode_barang) references barang (kode_barang),
constraint fk_kode_pelanggan foreign key (kode_pelanggan) references pelanggan ( (kode_pelanggan)
);
insert into pembelian
values ('bel-e001','elk-01','j-0001','20-05-02',3);
insert into pembelian
values ('bel-e002','elk-02','j-0001','21-05-02',4);
insert into pembelian
values ('bel-e003','elk-01','j-0002','20-05-02',2);
insert into pembelian
values ('bel-e004','elk-01','b-0001','20-05-02',2);
insert into pembelian
values ('bel-e005','elk-03','b-0002','22-05-02',3);
insert into pembelian
values ('bel-e006','elk-02','j-0001','24-05-02',1);
1. Tampilkan nama barang yang mempunyai stok kurang dari rata-rata.
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
Kode_barang Nama_Barang Satuan Stok
ELK-01 Rice cooker Buah 20
ELK-02 Lemari es Unit 8
ELK-03 Televisi Unit 30
ELK-04 Radio/tape Buah 35
CREATE TABLE Barang (
kode_barang char(6),
nama_barang varchar(25),
satuan varchar(20),
stok int,
constraint pk_Barang primary key (kode_barang)
);
INSERT INTO Barang
VALUES ('ELK-01','Rice Cooker','Buah',20);
INSERT INTO Barang
VALUES ('ELK-02','Lemari Es','Unit',8);
INSERT INTO Barang
VALUES ('ELK-03','Televisi','Unit',30);
INSERT INTO Barang
VALUES ('ELK-04','Radio/tape','Buah',35);
2. Tabel Pelanggan
Kode_Pelanggan char(6),
Nama varchar(30), Alamat varchar(30), kota varchar(15),
Telepon varchar(15)
primary key : kode_Pelanggan
Kode_Pelanggan Nama Alamat Kota Telepon
J-0001 Toko Karisma Jl cimandiri 25 Depok (021)-367868
J-0002 Toko Ayu Jl cimone 35 Tangerang (021)-467889
B-0001 Toko Warna Jl DEF 30 Jakarta (021)-234678
B-0002 Toko Surya Jl abc 309 Bandung (022)-356789
CREATE TABLE Pelanggan (
Kode_Pelanggan char(6),
Nama varchar(30),
Alamat varchar(30),
Kota varchar(15),
telepon varchar(15),
constraint pk_Pelanggan primary key (Kode_Pelanggan)
);
INSERT INTO Pelanggan
VALUES ('J-0001','Toko Karisma','Jl.Cimandiri 25','Depok','(021)-367868');
INSERT INTO Pelanggan
VALUES ('J-0002','Toko Ayu','Jl.Cimone 35','Tanggerang','(021)-467889');
INSERT INTO Pelanggan
VALUES ('B-0001','Toko Warna','Jl.DEF 30','Jakarta','(021)-234678’);
INSERT INTO Pelanggan
VALUES ('B-0002','Toko Surya ','Jl.abc 309','Bandung','(022)-356789)';
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
Kode_Pembelian Kode_Barang Kode_Pelanggan Tgl_Pembelian Jum_Pembelian
BEL-E001 ELK-01 J-0001 20-may-02 3
BEL-E002 ELK-02 J-0001 21-may-02 4
BEL-E003 ELK-01 J-0002 20-may-02 2
BEL-E004 ELK-01 B-0001 20-may-02 2
BEL-E005 ELK-03 B-0002 22-may-02 3
BEL-E006 ELK-02 J-0001 24-jun-02 1
create table pembelian (
kode_pembelian char(10),
kode_barang char(6),
kode_pelanggan char(6),
tgl_pembelian date,
jum_pembelian int,
constraint pk_pembelian primary key (kode_pembelian),
constraint fk_kode_barang foreign key (kode_barang) references barang (kode_barang),
constraint fk_kode_pelanggan foreign key (kode_pelanggan) references pelanggan ( (kode_pelanggan)
);
insert into pembelian
values ('bel-e001','elk-01','j-0001','20-05-02',3);
insert into pembelian
values ('bel-e002','elk-02','j-0001','21-05-02',4);
insert into pembelian
values ('bel-e003','elk-01','j-0002','20-05-02',2);
insert into pembelian
values ('bel-e004','elk-01','b-0001','20-05-02',2);
insert into pembelian
values ('bel-e005','elk-03','b-0002','22-05-02',3);
insert into pembelian
values ('bel-e006','elk-02','j-0001','24-05-02',1);
1. Tampilkan nama barang yang mempunyai stok kurang dari rata-rata.
2. Tampilkan pelanggan yang paling besar pembeliannya
SELECT nama_barang FROM Barang
WHERE stok <(SELECT avg(stok)
FROM Barang)
SELECT Nama
FROM Pembelian,Pelanggan
WHERE jum_pembelian = (SELECT MAX(jum_pembelian)FROM Pembelian)
AND Pelanggan.Kode_Pelanggan = Pembelian.kode_Pembelian
3. Buatlah view dengan nama V_PELANGGAN yang menampilkan nama
pelanggan,alamat dan telepon. Pelanggan yang ditampilkan adalah
yang berasal dari kota Jakarta.
CREATE VIEW v_PELANGGAN as
SELECT Nama,Alamat,telepon
FROM Pelanggan
WHERE Kota = 'Jakarta'
SELECT * from v_PELANGGAN
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
CREATE VIEW v_plgn_beli as
SELECT Nama,jum_pembelian,tgl_pembelian
FROM Pelanggan,Pembelian
SELECT * from 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
CREATE VIEW v_jum_beli as
SELECT nama_barang,Nama,tgl_pembelian,jum_pembelian
FROM Barang,Pelanggan,Pembelian
SELECT * from v_jum_beli
SUBQUERY AND VIEW
A. Subquery
Subquery adalah perintah SELECT yang berada di dalam perintah SQL lain. Subquery sangat berguna ketika kita ingin menampilkan data dengan kondisi yang bergantung
pada data di dalam table itu sendiri.
Kegunaan-kegunaan Subquery dalam memanipulasi data:
1. Meng-copy data dari satu tabel ke tabel lain
2. Menerima data dari inline view
3. Mengambil data dari tabel lain untuk kemudian di update ke tabel yang dituju
4. Menghapus baris dari satu tabel berdasarkan baris dari tabel lain.
ATURAN SUB QUERY
Klausa ORDER BY tidak boleh digunakan .di subquery. ORDER BY dapat digunakan,di pernyataan SELECT luar.
Klausa subquery SELECT harus beisi satu nama kolom tunggal atau ekspresi kecuali untuk subquery-subquery menggunakan kata kunci EXIST.
secara default, nama-nama kolom di subquery mengacu ke nama table diklausa FROM di query lebih luar dengan mengkualifikasi nama kolom
Saat subquery adalah salah satu dua operan dilibatkan di pembandingan, subquery harus muncul di sisi kanan pembandingan.
Subquery tdak boleh digunakan sebagai operan di ekspresi.
Sintaks:
SELECT select_list
FROM table
WHERE expr operator
( SELECT select_list FROM table );
Contoh:
Menampilkan data karyawan yang bekerja satu departemen dengan Biri.
SELECT last_name, title
FROM employee
WHERE dept_id =
( SELECT dept_id
FROM employee
WHERE UPPER(last_name) = ‘BIRI’ );
Operator EXIST dan NOT EXIST
Kata kunci EXIST dan NOT EXIST dirancang hanya untuk digunakan di subquery. Kata kunci-kata kunci ini menghasilkan nilai TRUE atau FALSE EXIST akan mengirim nilai TRUE jika dan hanya jika terdapat sedikitnya satu baris di table hasil yang dikirim oleh subquery. EXIST mengirim nilai FALSE jika subquery mengirm table kosong NOT EXIST kebalian dan EXIST.
Karena EXIST dan NOT EXIST hanya memeriksa keberadaan baris-baris di table hasil subquery.
Contonya
Penggunaan EXIST
Daftarkan semua staf yang bekerja dikantor cabang beralamat di jalan “Tamansari 81”
SELECT IDStaf , namaDepan, namaBelakang, pangkat, gPokok
FROM Staf s
WHERE EXIST
(SELECT *
FROM KantorCabang k, StafKCabang sk
WHERE s.IDStaf=sk.IDStafAND
sk.IDKCabang =k.IDKCabang AND
k.jalan=’tamansari 81’
);
Operator Any(Some)
Operator Any (some) hampir sama penggunaannya seperti Exists. Tetapi operator relasi yang digunakan biasanya selain = (sama dengan). hal tersebut disebabkan apabila operator relasi = yang digunakan, maka sebetulnya fungsi operator Any (some) sama seperti operator IN, sehingga kondisi seperti itu tidak dianjurkan karena lebih mudah pemahamannya apabila menggunakan operator IN.
Contoh :
Akan menampilkan daftar nama pegawai dan gaji yang gajinya tidak paling sedikit :select nama, gaji from pegawai where gaji > any (select gaji from pegawai);
Operator All
Operator all digunakan untuk melakukan pembandingan dengan sub query. Kondisi dengan all menghasilkan nilai benar jika pembandingan menghasilkan benar untuk setiap nilai dalam sub query.
Contoh :
Akan menampilkan nama dan gaji pegawai yang gajinya lebih rendah daripada semua pegawai yang pekerjaannya 'SALESMAN' :
select nama where gaji < all (select gaji from pegawai where pekerjaan = 'SALESMAN');
Sub query (subselect) hanya dapat digunakan untuk MySQL versi 3.24, Sehingga parintah-perintah diatas tidak dapat dijalankan di MySQL ini(versi 3.23). Tetapi, dengan menggunakan option LEFT JOIN, masalah-masalah diatas dapat diselesaikan.
Contoh :
select * from score where event_id in (select even_id from even where type = 'T');
Apabila dituliskan dengan cara lain menjadi :
select score.* from score, event where score.even_id = even.even_id and type = 'T';
Multiple Rows Subquery
Multiple Row Subquery adalah subquery yang menghasilkan lebih dari satu baris data. Untuk multiple row subquery ini yang digunakan adalah operator pembanding IN, ANY atau ALL
Contoh:
Menampilkan data karyawan yang bekerja pada departemen Finance atau pada region 2.
SELECT last_name, first_name, title
FROM employee
WHERE dept_id IN ( SELECT id
FROM department
WHERE name = ‘Finance’
OR region_id = 2 );
Contoh:
Menampilkan nama, gaji, dan nomer department dari pegawai yang gajinya sama dengan gaji minimum pada suatu department.
SELECT ename, sal, deptno FROMENP WHERE sal IN (SELECT MIN (sal) FROM ENP GROUP BY deptno)
Single Row Subquery
Single row subquery memberikan hasil hanya satu baris pada bagian subquery. Untuk single row subquery ini yang digunakan adalah operator pembanding: , >, >=, <, <= atau <>
Contoh:
Menampilkan data karyawan yang memiliki jabatan sama dengan Smith.
SELECT last_name, title
FROM employee
WHERE title =
( SELECT title
FROM employee
WHERE last_name = ‘Smith’ );
Kita dapat menampilkan data dari query utama dengan menggunakan fungsi grup (group function) untuk menghasilkan satu baris data.
Contoh:
Menampilkan data karyawan yang memiliki gaji dibawah rata-rata.
SELECT last_name, title, salary
FROM employee
WHERE salary < ( SELECT AVG(salary)
FROM employee );
Kesalahan dalam Subquery
Kesalahan umum dalam subquery adalah lebih dari satu baris data dihasilkan untuk single row subquery.
Contoh:
Subquery ini menghasilkan lebih dari satu baris data dan menggunakan single row operator. Kita dapat membetulkan kesalahan ini dengan mengubah = menjadi IN.
SELECT last_name, first_name, title
FROM employee
WHERE dept_id = ( SELECT id
FROM department
WHERE name = ‘Finance’
OR region_id = 2 )
B. View
Definisi view adalah hasil (result) dari sebuah Query terhadap relasi- relasi dasar (atau relasi real). Hasil (view) ini tidak disimpan dalam database seperti relasi dasar. Sebuah view adalah sebuah jendela dinamik, dalam artian bahwa ia mencerminkan semua update yang dilakukan terhadap database. Disamping pemakaiannya di dalam skema external, viewjuga berguna untuk menjamin data- security dengan cara yang sederhana. Dengan memilih subset dari database, viewdapat menyembunyikan beberapa data.
Jika user mengakses database melalui view, mereka tak dapat melihat atau memanipulasi hidden-data, dengan demikian data akan menjadi secure.
Perhatikan bahwa, dalam sistem DBMS terdistribusi, sebuah view dapat diturunkan dari relasi- relasi terdistribusi. Akses ke sebuah view akan memer lukan eksekusi dari kueri terdistribusi yang berhubungan dengan definisi view ter sebut.
Isu penting dalam DBMS terdistribusi adalah untuk membuat materialisasi view dengan cara yang efisien. Kita akan melihat bagaimana konsep snapshot akan membantu dalam memecahkan masalah ini, tapi ter lebih dulu kita akan berkonsentrasi pada DBMS terpusat.
Konteks view dalam DBMS terpusat, sebuah view adalah sebuah relasi yang diturunkan dari relasi relasi dasar , sebagai hasil dari sebuah kueri relasional.
Contoh penggunaan view dalam SQL :
set pagesize 500
alter session set nls_date_format = 'DD.MM.YYYY';
create table prices_ (
sku varchar2(38),
price number,
valid_from date
);
insert into prices_ values ('4711', 18, '08.01.2003');
insert into prices_ values ('4711', 19, '01.05.2000');
insert into prices_ values ('4711', 20, '18.01.2001');
insert into prices_ values ('4711', 21, '09.01.2000');
insert into prices_ values ('beer', 14, '07.03.2000');
insert into prices_ values ('beer', 15, '10.01.2003');
insert into prices_ values ('beer', 16, '18.01.2001');
insert into prices_ values ('beer', 19, '16.11.2001');
insert into prices_ values ('beer', 17, '19.02.2002');
create view prices_today_ as
select
sku,
price,
valid_from
from
(select
sku,
price,
valid_from,
rank() over (partition by sku order by valid_from desc) r
from
prices_
)
where r=1;
select * from prices_today_;
drop table prices_;
drop view prices_today_;
Referensi :
1. http://catatankuliahum.blogspot.com/
2. http://www.haritsthinkso.com/2009/12/pengertian-view-pada-database.html
Subquery adalah perintah SELECT yang berada di dalam perintah SQL lain. Subquery sangat berguna ketika kita ingin menampilkan data dengan kondisi yang bergantung
pada data di dalam table itu sendiri.
Kegunaan-kegunaan Subquery dalam memanipulasi data:
1. Meng-copy data dari satu tabel ke tabel lain
2. Menerima data dari inline view
3. Mengambil data dari tabel lain untuk kemudian di update ke tabel yang dituju
4. Menghapus baris dari satu tabel berdasarkan baris dari tabel lain.
ATURAN SUB QUERY
Klausa ORDER BY tidak boleh digunakan .di subquery. ORDER BY dapat digunakan,di pernyataan SELECT luar.
Klausa subquery SELECT harus beisi satu nama kolom tunggal atau ekspresi kecuali untuk subquery-subquery menggunakan kata kunci EXIST.
secara default, nama-nama kolom di subquery mengacu ke nama table diklausa FROM di query lebih luar dengan mengkualifikasi nama kolom
Saat subquery adalah salah satu dua operan dilibatkan di pembandingan, subquery harus muncul di sisi kanan pembandingan.
Subquery tdak boleh digunakan sebagai operan di ekspresi.
Sintaks:
SELECT select_list
FROM table
WHERE expr operator
( SELECT select_list FROM table );
Contoh:
Menampilkan data karyawan yang bekerja satu departemen dengan Biri.
SELECT last_name, title
FROM employee
WHERE dept_id =
( SELECT dept_id
FROM employee
WHERE UPPER(last_name) = ‘BIRI’ );
Operator EXIST dan NOT EXIST
Kata kunci EXIST dan NOT EXIST dirancang hanya untuk digunakan di subquery. Kata kunci-kata kunci ini menghasilkan nilai TRUE atau FALSE EXIST akan mengirim nilai TRUE jika dan hanya jika terdapat sedikitnya satu baris di table hasil yang dikirim oleh subquery. EXIST mengirim nilai FALSE jika subquery mengirm table kosong NOT EXIST kebalian dan EXIST.
Karena EXIST dan NOT EXIST hanya memeriksa keberadaan baris-baris di table hasil subquery.
Contonya
Penggunaan EXIST
Daftarkan semua staf yang bekerja dikantor cabang beralamat di jalan “Tamansari 81”
SELECT IDStaf , namaDepan, namaBelakang, pangkat, gPokok
FROM Staf s
WHERE EXIST
(SELECT *
FROM KantorCabang k, StafKCabang sk
WHERE s.IDStaf=sk.IDStafAND
sk.IDKCabang =k.IDKCabang AND
k.jalan=’tamansari 81’
);
Operator Any(Some)
Operator Any (some) hampir sama penggunaannya seperti Exists. Tetapi operator relasi yang digunakan biasanya selain = (sama dengan). hal tersebut disebabkan apabila operator relasi = yang digunakan, maka sebetulnya fungsi operator Any (some) sama seperti operator IN, sehingga kondisi seperti itu tidak dianjurkan karena lebih mudah pemahamannya apabila menggunakan operator IN.
Contoh :
Akan menampilkan daftar nama pegawai dan gaji yang gajinya tidak paling sedikit :select nama, gaji from pegawai where gaji > any (select gaji from pegawai);
Operator All
Operator all digunakan untuk melakukan pembandingan dengan sub query. Kondisi dengan all menghasilkan nilai benar jika pembandingan menghasilkan benar untuk setiap nilai dalam sub query.
Contoh :
Akan menampilkan nama dan gaji pegawai yang gajinya lebih rendah daripada semua pegawai yang pekerjaannya 'SALESMAN' :
select nama where gaji < all (select gaji from pegawai where pekerjaan = 'SALESMAN');
Sub query (subselect) hanya dapat digunakan untuk MySQL versi 3.24, Sehingga parintah-perintah diatas tidak dapat dijalankan di MySQL ini(versi 3.23). Tetapi, dengan menggunakan option LEFT JOIN, masalah-masalah diatas dapat diselesaikan.
Contoh :
select * from score where event_id in (select even_id from even where type = 'T');
Apabila dituliskan dengan cara lain menjadi :
select score.* from score, event where score.even_id = even.even_id and type = 'T';
Multiple Rows Subquery
Multiple Row Subquery adalah subquery yang menghasilkan lebih dari satu baris data. Untuk multiple row subquery ini yang digunakan adalah operator pembanding IN, ANY atau ALL
Contoh:
Menampilkan data karyawan yang bekerja pada departemen Finance atau pada region 2.
SELECT last_name, first_name, title
FROM employee
WHERE dept_id IN ( SELECT id
FROM department
WHERE name = ‘Finance’
OR region_id = 2 );
Contoh:
Menampilkan nama, gaji, dan nomer department dari pegawai yang gajinya sama dengan gaji minimum pada suatu department.
SELECT ename, sal, deptno FROMENP WHERE sal IN (SELECT MIN (sal) FROM ENP GROUP BY deptno)
Single Row Subquery
Single row subquery memberikan hasil hanya satu baris pada bagian subquery. Untuk single row subquery ini yang digunakan adalah operator pembanding: , >, >=, <, <= atau <>
Contoh:
Menampilkan data karyawan yang memiliki jabatan sama dengan Smith.
SELECT last_name, title
FROM employee
WHERE title =
( SELECT title
FROM employee
WHERE last_name = ‘Smith’ );
Kita dapat menampilkan data dari query utama dengan menggunakan fungsi grup (group function) untuk menghasilkan satu baris data.
Contoh:
Menampilkan data karyawan yang memiliki gaji dibawah rata-rata.
SELECT last_name, title, salary
FROM employee
WHERE salary < ( SELECT AVG(salary)
FROM employee );
Kesalahan dalam Subquery
Kesalahan umum dalam subquery adalah lebih dari satu baris data dihasilkan untuk single row subquery.
Contoh:
Subquery ini menghasilkan lebih dari satu baris data dan menggunakan single row operator. Kita dapat membetulkan kesalahan ini dengan mengubah = menjadi IN.
SELECT last_name, first_name, title
FROM employee
WHERE dept_id = ( SELECT id
FROM department
WHERE name = ‘Finance’
OR region_id = 2 )
B. View
Definisi view adalah hasil (result) dari sebuah Query terhadap relasi- relasi dasar (atau relasi real). Hasil (view) ini tidak disimpan dalam database seperti relasi dasar. Sebuah view adalah sebuah jendela dinamik, dalam artian bahwa ia mencerminkan semua update yang dilakukan terhadap database. Disamping pemakaiannya di dalam skema external, viewjuga berguna untuk menjamin data- security dengan cara yang sederhana. Dengan memilih subset dari database, viewdapat menyembunyikan beberapa data.
Jika user mengakses database melalui view, mereka tak dapat melihat atau memanipulasi hidden-data, dengan demikian data akan menjadi secure.
Perhatikan bahwa, dalam sistem DBMS terdistribusi, sebuah view dapat diturunkan dari relasi- relasi terdistribusi. Akses ke sebuah view akan memer lukan eksekusi dari kueri terdistribusi yang berhubungan dengan definisi view ter sebut.
Isu penting dalam DBMS terdistribusi adalah untuk membuat materialisasi view dengan cara yang efisien. Kita akan melihat bagaimana konsep snapshot akan membantu dalam memecahkan masalah ini, tapi ter lebih dulu kita akan berkonsentrasi pada DBMS terpusat.
Konteks view dalam DBMS terpusat, sebuah view adalah sebuah relasi yang diturunkan dari relasi relasi dasar , sebagai hasil dari sebuah kueri relasional.
Contoh penggunaan view dalam SQL :
set pagesize 500
alter session set nls_date_format = 'DD.MM.YYYY';
create table prices_ (
sku varchar2(38),
price number,
valid_from date
);
insert into prices_ values ('4711', 18, '08.01.2003');
insert into prices_ values ('4711', 19, '01.05.2000');
insert into prices_ values ('4711', 20, '18.01.2001');
insert into prices_ values ('4711', 21, '09.01.2000');
insert into prices_ values ('beer', 14, '07.03.2000');
insert into prices_ values ('beer', 15, '10.01.2003');
insert into prices_ values ('beer', 16, '18.01.2001');
insert into prices_ values ('beer', 19, '16.11.2001');
insert into prices_ values ('beer', 17, '19.02.2002');
create view prices_today_ as
select
sku,
price,
valid_from
from
(select
sku,
price,
valid_from,
rank() over (partition by sku order by valid_from desc) r
from
prices_
)
where r=1;
select * from prices_today_;
drop table prices_;
drop view prices_today_;
Referensi :
1. http://catatankuliahum.blogspot.com/
2. http://www.haritsthinkso.com/2009/12/pengertian-view-pada-database.html
Selasa, 14 Oktober 2014
Senin, 13 Oktober 2014
Pengelompokan Pengelompokan & Pengurutan Pengurutan Data
Bentuk umum GROUP BY:
SELECT nama_kolom, aggregate_function (nama_kolom)
DARI nama_tabel
WHERE nama_kolom operator nilai
GROUP BY nama_kolom
Bentuk umumnya adalah :
SELECT Pelanggan, SUM (Harga pesanan) FROM Pesanan
GROUP BY Pelanggan
1. Syntax Count
WHERE Kustomer=’Ayu’
Kriteria Data
1. Group by and Aggregate Functions
a. GROUP BY
Statement GROUP BY digunakan bersama dengan fungsi agregat ke grup hasil-set oleh satu atau beberapa kolom.
Statement GROUP BY digunakan bersama dengan fungsi agregat ke grup hasil-set oleh satu atau beberapa kolom.
Bentuk umum GROUP BY:
SELECT nama_kolom, aggregate_function (nama_kolom)
DARI nama_tabel
WHERE nama_kolom operator nilai
GROUP BY nama_kolom
Contoh GROUP BY SQL
Contoh 1
Tabel Pesanan
No
|
Tanngal pesanan
|
Harga Pesanan
|
Pelanggan
|
1
|
2011/11/12
|
1000
|
Hanizar
|
2
|
2011/10/23
|
1600
|
Nilam
|
3
|
2011/09/02
|
700
|
Hanizar
|
4
|
2011/09/03
|
300
|
Hanizar
|
5
|
2011/08/30
|
2000
|
Jeni
|
6
|
2011/10/04
|
100
|
Nilam
|
Jika kita ingin mencari jumlah total pesanan dari setiap pelanggan.
Maka, kita harus menggunakan pernyataan GROUP BY untuk kelompok pelanggan.
Maka, kita harus menggunakan pernyataan GROUP BY untuk kelompok pelanggan.
Bentuk umumnya adalah :
SELECT Pelanggan, SUM (Harga pesanan) FROM Pesanan
GROUP BY Pelanggan
Maka, hasilnya adalah sebagai berikut :
Pelanggan
|
SUM(HargaPesanan)
|
Hanizar
|
2000
|
Nilam
|
1700
|
Jeni
|
2000
|
Digunakan untuk mengkalkulasikan data pada tabel-tabel didalam database.
b. Aggregate Functions
Digunakan untuk mengkalkulasikan nilai-nilai
didalam kolom. Fungsi Aggregate terdiri dari fungsi:
- AVG() – menghitung rata-rata
- COUNT() – menghitung jumlah row
- FIRST() – nilai pertama
- LAST() – nilai terakhir
- MAX() – nilai maksimal
- MIN() – nilai minimal
- SUM() – menjumlahkan
- WHERE harga_order>(SELECT AVG(Harga_Order) FROM Orde
WHERE Kustomer=’Ayu’
Syntax SQL COUNT(*)
Syntax SQL
COUNT(DISTINCT column_name)
Syntax SQL FIRST
Syntax SQL LAST
Syntax SQL MAX
Syntax SQL MIN
Syntax SQL SUM
2. AVG
Syntax AVG ( )
SELECT AVG(column_name) FROM table_name
Contoh:
SELECT AVG(harga_order) FROM Order
untuk menampilkan kostumer yang
mempunyai nilai diatas rata-rata
SELECT kustomer FROM Order
3. COUNT
SELECT COUNT(column_name) FROM table_name
contoh:
SELECT COUNT(kustomer) AS kustomerAyu FROM
Order
SELECT COUNT(*) FROM table_name
contoh:
SELECT COUNT(*) AS BanyaknyaOrder FROM Order
SELECT COUNT(DISTINCT column_name) FROM
table_name
contoh:
SELECT COUNT(DISTINCT kustomer) AS
banyaknya_Kustomer FROM Order
4. FIRST
SELECT FIRST(column_name) FROM table_name
Contoh:
SELECT FIRST(harga_order) AS
HargaOrderPertama FROM Order
5. LAST
SELECT LAST(column_name) FROM table_name
Contoh:
SELECT LAST (harga_order) AS
HargaOrderTerakhir FROM Order
6. MAX
SELECT MAX(column_name) FROM table_name
contoh:
SELECT MAX(Harga_Order) AS HargaMaximal FROM
Order
7. MIN
SELECT MIN(column_name) FROM table_name
contoh:
SELECT MIN(Harga_Order) AS HargaMinimum FROM
Order
8. SUM
SELECT SUM(column_name) FROM table_name
Contoh:
SELECT SUM(Harga_Order) AS TotalOrder FROM
Orders
2. ORDER BY
digunakan dalam sebuah pernyataan SELECT untuk mengurutkan hasil baik dalam menaik atau menurun. Oracle macam hasil query dalam urutan secara default.
Sintaks untuk menggunakan SQL ORDER BY klausa untuk mengurutkan data adalah:
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1 [, column2, .. columnN] [DESC]];WHERE column_name operator value
1. Having
Digunakan untuk memfilter data dengan fungsi tertentu.
Syntax dasar :
SELECT column_name,aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
Contoh :
SELECT nama_customer,SUM(harga) FROM order
WHERE nama_customer=’Arini’ OR nama_customer=’Maheswari’
GROUP BY nama_customer
HAVING SUM (harga)>25000
LATIHAN
LATIHAN
1. Tampilkan jumlah
nilai minimum per matakuliah
Jawab :
SELECT matakuliah.nama_MK, Min (Nilai) AS Jumlah
FROM Nilai,matakuliah where
matakuliah.kode_MK=nilai.kode_MK GROUP BY (Nama_MK);
2. Tampilkan nilai
maksimum dari semua data pada
tabel NILAI
jawab :
Select MAX(nilai) AS jumlah
from nilai;
3. Tampilkan nim
dan kode mata kuliah yang
mempunyai nilai 90 ?
jawab :
SELECT nilai.nim,
matakuliah.Kode_MK
FROM mahasiswa,
matakuliah, nilai
WHERE
mahasiswa.nim=nilai.nim AND matakuliah.Kode_MK=nilai.Kode_MK AND nilai=90;
4. Tampilkan nim
dan kode mata kuliah yang
mempunyai nilai kurang dari 90 ?
jawab :
SELECT nilai.nim, matakuliah.Kode_MK
FROM mahasiswa, matakuliah, nilai
WHERE mahasiswa.nim=nilai.nim AND matakuliah.Kode_MK=nilai.Kode_MK AND
nilai<90;
5. Tampilkan
data mahasiswa yang dari kota berakhiran "KARTA" ?
jawab :
select *FROM mahasiswa
WHERE kota LIKE '%karta'
refrensi:
1. http://honeyzharratih.blogspot.com/
2. http://virtualyuni.wordpress.com/
3. http://beginner-sql-tutorial.com/
2. http://virtualyuni.wordpress.com/
3. http://beginner-sql-tutorial.com/
Langganan:
Postingan (Atom)