Quis 14 Oktober 2014

Read More

Pengelompokan & Pengurutan Data (Pertemuan 5)

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()

• MIN()

Fungsi MIN digunakan untuk mencari nilai minimum dari sekumpulan data yang ada

SELECT field-1,…,field-n,

MIN(nama_field)

FROM tabel

GROUP BY field-1,…,field-n

Contoh :

SELECT kode_pasok,

MIN(jumlah_pasok) as jumlah

FROM pasok

GROUP BY kode_pasok

• MAX()

Fungsi MAX digunakan untuk mencari nilai maksimum dari sekumpulan data yang ada

SELECT field-1,…,field-n,
MAX(nama_field)
FROM tabel

GROUP BY field-1,…,field-n

Contoh :

SELECT kode_pasok,
MAX (jumlah_pasok) as jumlah
FROM pasok

GROUP BY kode_pasok

• AVG()

Fungsi AVG digunakan untuk mencari nilai rata-rata dari sekumpulan data yang ada

SELECT AVG (nama_field),

AS Rata-Rata

FROM tabel

atau

SELECT (nama_field)

AVG (nama_field) AS (nama_field_baru)

FROM tabel

GROUP BY (nama_field)

Contoh :

SELECT AVG (jumlah_pasok)

AS Rata_Rata

FROM pasok

atau

SELECT kode_pasok,

AVG (jumlah_pasok) AS Rata_Rata

FROM pasok

GROUP BY (kode_pasok)

Latihan

mencari nilai rata-rata dari keseluruhan nilai yang ada :

SELECT AVG (nilai) as Rata_rata

FROM nilai

Latihan

mencari nilai rata-rata per mahasiswa (per-nim) :

SELECT nim,

AVG(nilai) as Rata_Rata

FROM nilai

Group by (nim)

Latihan

mencari nilai rata-rata per-matakuliah :

SELECT kode_MK,

AVG(nilai) as Rata_Rata

FROM nilai

Group by (kode_MK)

• SUM()

Fungsi SUM digunakan untuk menjumlahkan nilai dari sekumpulan data yang ada

SELECT field-1,…,field-n,

SUM(nama_field)

FROM tabel

GROUP BY field-1,…,field-n

Contoh :

SELECT kode_pasok,

SUM(jumlah_pasok) AS Jumlah

FROM pasok

GROUP BY (kode_pasok)

Latihan

mencari jumlah seluruh nilai :

SELECT SUM(nilai) as Jumlah

FROM nilai

Latihan

mencari jumlah nilai per-mahasiswa (per-nim) :

SELECT nim,

SUM(nilai) as Jumlah

FROM Nilai

Group by (nim)

Latihan

mencari jumlah nilai per-matakuliah :

SELECT Nama_MK,

SUM(nilai) as Jumlah_Nilai

FROM nilai, matakuliah

WHERE matakuliah.Kode_MK=nilai.Kode_MK

Group by (nama_MK)

• COUNT()

Fungsi COUNT digunakan untuk mencari cacah atau banyaknya data

SELECT field-1,…,field-n,

COUNT (nama_field)

FROM tabel

GROUP BY field-1,…,field-n

Contoh :

SELECT kode_pasok,

COUNT (jumlah_pasok) AS Banyaknya_Data

FROM pasok

GROUP BY (kode_pasok)

Latihan

mencari jumlah nilai per-matakuliah :

SELECT nim,

COUNT(nilai) as Banyaknya_Nilai

FROM nilai

Group by (nim)

Pengurutan Data (Order By)

• ORDER BY

Digunakan untuk mengurutkan data berdasarkan field tertentu

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

Latihan

menampilkan nilai diurutkan dari yang terkecil sampai yang terbesar :

SELECT nim, nilai

FROM nilai

ORDER BY nilai

Latihan

menampilkan nilai diurutkan dari yang terbesar sampai yang terkecil :

SELECT nim, nilai

FROM nilai

ORDER BY nilai DESC

Keriteria Data

• HAVING

Menyeleksi data berdasarkan kriteria tertentu, dimana kriteria berdasarkan berupa fungsi aggregat

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

FROM tabel

GROUP BY field-1,…,field-n

HAVING kriteria_aggregate_function

• Tampilkan kode customer yang mempunyai cacah pembelian = 4

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

Comparison

•Berfungsi untuk membandingkan dua nilai.

•Tipe data yang seharusnya dibandingkan harus sesuai.

•Hasil yang diperoleh dari operasi comparison ini berupa nilai logika.

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

SELECT kode_barang, kode _suplier FROM pasok

WHERE jumlah_pasok<=5

Between

Pada prinsipnya between digunakan untuk membandingkan/untuk mengecek apakah suatu nilai berada dalam range atau nilai tertentu.

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

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

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%’

LATIHAN

Tampilkan jumlah nilai minimum per matakuliah

SELECT nama_mk,

MIN(nilai) as jumlah_NILAI_MINIMUM

FROM nilai, matakuliah

WHERE matakuliah.Kode_MK=nilai.Kode_MK

Group by (nama_MK)

Tampilkan nilai maksimum dari semua data pada tabel NILAI

SELECT MAX(nilai) as NILAI_MAKSIMUM

FROM nilai

Tampilkan nim dan kode mata kuliah yang mempunyai nilai 90

SELECT NIM, KODE_MK 

FROM NILAI

WHERE NILAI=90;

Tampilkan nim dan kode mata kuliah yang mempunyai nilai kurang dari 90

SELECT NIM, KODE_MK

FROM NILAI

WHERE NILAI<90;

Tampilkan data mahasiswa yang dari kota berakhiran “KARTA”

SELECT *

FROM MAHASISWA

WHERE KOTA LIKE '%karta';

Read More

Latihan (Pertemuan 3 dan 4)

LATIHAN….

Buatlah tabel mahasiswa dengan field :

• nim char(10),
• nama varchar (15),
• alamat varchar (20),
• kota varchar (15),

Field nim merupakan suatu primary key dari tabel mahasiswa ini.

Jawaban :

create table mahasiswa(
nim char(10),
nama varchar(15),
alamat varchar(20),
kota varchar(15),
constraint pk_mhs primary key(nim)

);

Tambahkan field kodepos pada tabel mahasiswa dengan tipe data char, lebar field adalah 8. 

Jawaban :

ALTER table mahasiswa

ADD kodepos char(8);

Ubah tipe data pada kolom kodepos menjadi varchar2, lebar data 5 :

Jawaban :

ALTER table mahasiswa

MODIFY kodepos varchar(5);

Isikan data kedalam tabel mahasiswa.

Jawaban :

INSERT into mahasiswa
VALUES (201291001,'Alfa','Jl.DuriKosambi 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);

Pada tabel mahasiswa yaitu pada field kota untuk NIM 201291004. Isikan pada field kota dengan ‘Semarang’.

Jawaban :

UPDATE mahasiswa
SET kota='Semarang'

WHERE NIM='201291004';

Hapus record pada tabel mahasiswa, dimana record yang dihapus adalah mahasiswa yang mempunyai NIM=‘201291001’.

Jawaban :

DELETE FROM mahasiswa
WHERE NIM=201291001;

Buatlah table matakuliah dengan isi data sebagai berikut :

• KodeMK Char (6), Nama_MK varchar (30), Semester char (1), SKS number (2)
• Primary key adalah Kode_MK

Jawaban :

create table matakuliah(

Kode_MK char(6),

Nama_MK varchar(30),

semester char(1),

sks char(2),

constraint pk_matkul primary key(Kode_MK)

);

Isikan data kedalam tabel matakuliah.

Jawaban :

INSERT INTO matakuliah
VALUES (MKI001,'SQL', 4, 2); 
INSERT INTO matakuliah
VALUES (MKI002,'Sistem Basis Data', 5, 2); 
INSERT INTO matakuliah
VALUES ('MKI003','Bahasa Pemrograman', 6, 3); 
INSERT INTO matakuliah

VALUES ('MKI004','Perancangan Basis Data', 4, 2); 

Buatlah table nilai dengan isi data sebagai berikut :

• NIMchar(9), Kode MK Char (6), Nilai Number
•  Foreign Key adalah NIM dan KODE_MK

Jawaban :

create table nilai(

NIM char(10),

Kode_MK char(6),

Nilai number,

constraint pk_nilai primary key (NIM, Kode_MK),

constraint fk_nilai_nim foreign key(NIM)

references mahasiswa(NIM),

constraint fk_nilai_kodeMK foreign key(Kode_MK)

references matakuliah(Kode_MK)

);

Tampilkan Seluruh data Pada tabel Mahasiswa.

INSERT INTO nilai

VALUES (201291001,'MKI001',60); 

INSERT INTO nilai

VALUES (201291002,'MKI002',80); 

INSERT INTO nilai

VALUES (201291003,'MKI003',65);

INSERT INTO nilai

VALUES (201291004,'MKI004',80);

INSERT INTO nilai

VALUES (201291005,'MKI001',75);   

INSERT INTO nilai

VALUES (201291001,'MKI002',50); 

INSERT INTO nilai

VALUES (201291002,'MKI003',45); 

INSERT INTO nilai

VALUES (201291003,'MKI004',90); 

INSERT INTO nilai

VALUES (201291004,'MKI001',70); 

SELECT * FROM nilai

Tampilkan NIM, Nama, Nama_MK Mahasiswa yang nilainya lebih dari 65

Jawaban :

SELECT nilai.nim, mahasiswa.nama, matakuliah.Nama_MK

FROM mahasiswa, matakuliah, nilai

WHERE mahasiswa.nim=nilai.nim AND matakuliah.Kode_MK=nilai.Kode_MK AND nilai>65;

Ganti nilai pada matakuliah SQL atas nama Mahasiswa Alfamenjadi 100

Jawaban :

UPDATE nilai

SET nilai = 100

WHERE nim=201291001 and Kode_MK='MKI001';

Ganti Nama Matakuliah “Bahasa Pemograman” menjadi “Algoritma Pemograman”

Jawaban :

UPDATE matakuliah

SET Nama_MK='Algoritma Pemrograman'

WHERE Kode_MK='MKI003';

Hapus data mahasiswa dengan NIM 201291005

Jawaban :

DELETE FROM nilai

WHERE nim=201291005;

DELETE FROM mahasiswa

WHERE nim=201291005;

Read More