Selasa, 14 Oktober 2014
Senin, 13 Oktober 2014
PEMROGRAMAN SQL PART V
Pengelompokkan dan Pengurutan Data
Group by dan Aggregate Function
3.Tampilkan nim dan kode mata kuliah yang mempunyai nilai 90
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:
- MAX : mencari data terbesar dari sekelompok data
- MIN : mencari data terkecil dari sekelompok data
- COUNT : mencari cacah data (data NULL tidak akan dimasukkan dalam perhitungan,kecuali disebutkan secara khusus)
- SUM : mencari jumlah dari sekumpulan data numeris
- 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]
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
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
Senin, 06 Oktober 2014
PEMROGRAMAN SQL PART IV
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
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
Langganan:
Postingan (Atom)
0 komentar: