db prog 2 oktober 2012

/*
catatan
*/

SELECT >> menampilkan data , sifanya realtime (begitu insert lalu select, datanya realtime) , query/permintaan data

Format :
SELECT {kolom}
FROM {tabel}
WHERE {baris}

Dalam menggunakan select, data yang ditampilkan hanya data bayangan hasil query, bukan data fisik,
sehingga data bisa di alias menggunakan statement AS

--------------------------------------------------------------------------------
SELECT bisa manggunakan konsep variabel
--minggu lalu menggunakan konsep variabel dgn menggunakan SET
Contoh: SET @a:=5;
SET @b:=6;

SELECT @a;
SELECT @b;

SELECT @a*@b AS Hasil;
-------------------------------------------------------------------------------

di dalam query bisa menggunakan operator :
1. Operator matematika + - * / %
2. Operator pembanding > < = <> != >= <=
3. Operator logika AND OR NOT
4. Operator pelengkap IN, BETWEEN, IS NULL
5. Operator statistik MIN, MAX, AVG, SUM, COUNT,

DROP DATABASE IF EXISTS dbNilai;
CREATE DATABASE dbNilai;
USE dbNilai;
CREATE TABLE tblNilai
(
nim int primary key auto_increment,
nama varchar(100) not null,
nilai double
);

INSERT INTO tblNilai
VALUES
(null, 'Debby', rand() * 10 ),
(null, 'rommy', rand() * 10 ),
(null, 'july', rand() * 10 ),
(null, 'Deborah', rand() * 10 ),
(null, 'Markus', rand() * 10 ),
(null, 'Febby', rand() * 10 ),
(null, 'Pius', rand() * 10 );

SELECT * FROM tblNilai; /*seluruh kolom yang diambil bisa di alias baik kolom maupun tabel*/

SELECT nim,nama FROM tblNilai;

SELECT nim AS 'Nim Mahasiswa',nama AS 'Nama Mahasiswa' FROM tblNilai; /*ALIAS KOLOM*/

SELECT IKOM.nim AS 'Nim Mahasiswa', IKOM.nama AS 'Nama Mahasiswa' FROM tblNilai AS IKOM; /*ALIAS TABEL*/

SELECT IKOM.nim AS 'Nim Mahasiswa', IKOM.nama AS 'Nama Mahasiswa', IKOM.nilai*10 AS 'Nilai' FROM tblNilai AS IKOM; /*ALIAS NYA DIKALIKAN*/


/*Didalam query bisa menggunakan struktur keputusan sederhana menggunakan IF dan CASE
IF hanya membandingkan 2 keputusan saja
CASE lebih dari 2 keputusan

IF(KONDISI, TRUE, FALSE)
*/

SELECT nim AS 'Nim Mhs',
nama AS 'Nama Mhs',
nilai*10 AS 'Nilai Akhir',
if(nilai*10 >=50,'LULUS','TIDAK LULUS') AS 'Keterangan'
FROM tblNilai;

/* ============================================
CASE
WHEN {Kondisi} THEN {Keputusan}
WHEN {Kondisi} THEN {Keputusan}
END

Selalu Alias
*/

SELECT nim AS 'Nim Mhs',
nama AS 'Nama Mhs',
nilai*10 AS 'Nlai Akhir',
CASE
WHEN nilai*10 BETWEEN 80 AND 100 THEN 'NILAI A'
WHEN nilai*10 BETWEEN 60 AND 80 THEN 'NILAI B'
WHEN nilai*10 BETWEEN 40 AND 60 THEN 'NILAI C'
WHEN nilai*10 BETWEEN 20 AND 40 THEN 'NILAI D'
WHEN nilai*10 BETWEEN 0 AND 20 THEN 'NILAI E'
END AS 'HURUF'
FROM tblNilai;

/*
dalam penggunaan SELECT, dibutuhkan pengurutan data, menggunakan ORDER BY dengan 2 pilihan ASC (A-Z) DESC (Z-A)
dalam kasus tertentu , pengurutan diijinkan lebih dari 2 kolom
*/

SELECT nim,
nama,
nilai
FROM tblNilai order by nilai DESC, nama ASC;