Tugas SQL Resume
Pertemuan 1
1.1
Perintah SELECT
Perintah SELECT
digunakan untuk menampilkan atau mengambil
data dari database. Perintah SELECT memiliki 3 (tiga) macam kemampuan:
a. Projection: Perintah
SELECT bisa digunakan untuk
memilih kolom
apa saja dari tabel
yang akan ditampilkan.
b. Selection: Perintah
SELECT bisa digunakan untuk memilih baris data
mana saja yang akan ditampilkan.
c. Joining: Perintah
SELECT bisa digunakan untuk menampilkan
data
atau informasi dari 2 atau
lebih tal yang terhubung.
1.2
Concate Operators
operator
CONCATE digunakan untuk
menggabungkan text, nilai kolom,
atau karakter. Anda bisa menghubungkan sebuah
kolom dengan kolom
lain, ekspresi aritmatik, atau
nilai konstan, untuk
membuat sebuah ekspresi karakter dengan
menggunakan operator CONCATE yang
disimbolkan dengan ‘||’.
Contoh:
Menampilkan
nama lengkap dari mahasiswa
SELECT
'Nama saya'|| NAMA_DEPAN ||' '||
NAMA_BELAKANG FROM MAHASISWA;
1.3 Column Alias
Ketika
menghasilkan hasil sebuah query, SQL akan menggunakan nama dari kolom-kolom
yang dipilih/ekspresi pada
SELECT statement sebagai header
kolom. Terkadang nama
kolom yang muncul
sulit dimengerti.atau kurang bisa
menjelaskan isi dari kolom.
Anda bisa mengganti header dari
kolom hasil query dengan menggunakan alias.
Tuliskan alias dari
sebuah kolom pada
klausa SELECT dengan
menggunakan spasi sebagai
pemisah
Jika alias
dari sebuah kolom mengandung spasi atau karakter khusus,
gunakan tanda petik 2 (“ “).
Contoh
:
menampilkan
NIM, KODE_MK dan “NILAI AKHIR’.
SELECT NIM, KODE_MK, (TUGAS*0.4) +
(UTS*0.3) +
(UAS*03) AS "NILAI AKHIR" FROM
NILAI;
1.4 Comparison Conditions
Operator
|
Note
|
=
|
Sama dengan
|
>
|
Lebih besar
|
>=
|
Lebih besar sama
dengan
|
<
|
Kurang dari
|
<=
|
Kurang dari sama dengan
|
|
|
<>. !=, ^=
|
Tidak sama dengan
|
BETWEEN … AND …
|
Diantara 2 nilai
|
IN (LIST OF VALUES)
|
Dicocokkan dengan salah
satu nilai pada kumpulan
nilai
|
LIKE
|
Mencocokkan dengan
pola karakter
|
IS NULL
|
Adalah nilai NULL
|
·
(BETWEEN … AND …) operator Anda dapat
menampilkan baris data
berdasarkan sebuah jangkauan nilai dengan menggunakan operator
BETWEEN. Jangkauan nilai yang disebutkan memiliki batas atas dan batas bawah.
Nilai yang dituliskan dalam kondisi BETWEEN
bersifat inklusif, dan
anda harus menentukan nilai
terendah terlebih dahulu.
Anda juga bisa menggunakan kondisi BETWEEN untuk nilai
karakter/huruf.
·
(IN) operator
Untuk
mencoba nilai dalam sebuah kumpulan nilai, gunakan kondisi IN.
kondisi
IN dapat
digunakan dengan tipe
data apapun. Jika tipe
data
huruf atau
tipe data DATE
digunakan dalam kumpulan
nilai, tipe data
tersebut
harus dituliskan dengan tanda petik (' ')
·
(LIKE) operator
Terkadang, anda tidak mengerti nilai apa
yang akan dicari, tetapi anda
dapat
menampilkan baris data yang cocok dengan pola karakter dengan
menggunakan kondisi LIKE.
pertemuan 2
Logical Conditions
Sebuah kondisi
logis menggabungkan hasil
dari 2 komponen
kondisi untuk menghasilkan
sebuah hasil berdasarkan
2 kondisi tersebut.
Atau membalikkan hasil dari
sebuah kondisi. Sebuah
baris data akanLabKom STIKOM Surabaya |Retrieve, Filter,
and Sort 12 dikembalikan jika
hasil keseluruhan dari kondisi
adalah TRUE. Ada 3 (tiga) operator logis yang ada di SQL:
Operator
|
Meaning
|
AND
|
mengembalikan TRUE jika kedua
komponen
kondisi mengembalikan TRUE
|
OR
|
Mengembalikan TRUE jika salah
satu
komponen kondisi mengembalikan
TRUE
|
NOT
|
Mengembalikan TRUE jika komponen
kondisi mengembalikan FALSE
|
AND Operator
Operator AND
membutuhkan kedua komponen
kondisi untuk
bernilaiTRUE.
Contoh
:
Mengambil
data mahasiswa yang tinggal di kota Sidoarjo dan nama
depannya
dimulai dengan huruf‘A’
SELECT * FROM MAHASISWA WHERE KOTA_TINGGAL =
'Sidoarjo' AND NAMA_DEPAN LIKE 'A%';
OR Operator
Operator
OR membutuhkan salah
satu dari komponen
kondisi untuk
bernilai TRUE.
Contoh
:
Mengambil data matakuliah
yang jumlah sks-nya 3
atau semester
diadakannya
1
SELECT * FROM MATAKULIAH WHERE SKS = 3 OR
SEMESTER = 1;
NOT Operator
Operator
NOT membutuhkan sekumpulan
nilai yang tidak
ingin
ditampilkan
di hasil query.
Contoh
:
Mengambil nim,
kode_mk dan nilai
akhir dari mahasiswa
dengan
nilai akhir
tidak diantara 60
sampai 100, dan
kode_mk-nya
mengandung
angka 1.
SELECT NIM, KODE_MK, (TUGAS*0.4) +
(UTS*0.3) +
(UAS*0.3) AS "FINAL SCORE" FROM
NILAI
WHERE (TUGAS*0.4) + (UTS*0.3) + (UAS*0.3)
NOT
BETWEEN 60 AND 100 AND KODE_MK LIKE
'%1%';
Pertemuan 3
Single-Row Functions
Functions
adalah sebuah fitur
yang sangat kuat dari
SQL. Functions bisa
digunakan untuk melakukan hal -
hal berikut:
·
Melakukan kalkulasi pada data.
·
Mengubah data individual
·
Mengubah output untuk sekumpulan baris
data.
·
Membentuk tampilan tanggal dan angka.
·
Mengubah tipe data kolom
Single-Row Function digunakan untuk
memanipulasi baris data. Single-row
function menerima 1 (satu)
atau lebih input parameter / argument dan mengembalikan 1
(satu) nilai untuk setiap baris yang dikembalikan oleh query.
Sebuah input parameter / argument
dapat terdiri atas / diisi dengan:
·
Nilai konstan yang dimasukkan pengguna
·
Nilai variabel
·
Nama kolom
·
Ekspresi
Character
Functions
Single-row character
function menerima data karakter
sebagai masukan dan
dapat mengembalikan baik nilai karakter dan nilai angka.
Character function dapat dibagi
menjadi 2:
·
Case-manipulation function
·
Character-manipulation function
Case-Manipulation
Functions
Fungsi-fungsi berikut
dapat mengubah case
dari 1 (satu)
atau sekumpulan karakter.
LOWER: mengubah
karakter mixed-case atau
uppercase menjadi
lowercase.
UPPER: mengubah
karakter mixed-case atau lowercase menjadi
uppercase.
INITCAP: mengubah
setiap huruf depan
dari kata menjadi uppercase dan membiarkan huruf yang lain tetap
lowercase.
Character-Manipulation
Functions
·
CONCAT:
menggabungkan 2(dua) nilai
menjadi 1 (satu). CONCAT hanya dapat menggabungkan 2
parameter / argument.
·
SUBSTR: mengambil karakter dari posisi
karakter dan panjang yang disebutkan.
·
LENGTH: mengambil panjang
sebuah string karakter menjadi nilai angka.
·
INSTR: mengambil posisi (dalam bentuk
angka) dari karakter yang disebutkan.
·
LPAD: menambahkan karakter di bagian
kiri string karakter
·
RPAD: menambahkan karakter di bagian
kanan string karakter.
·
REPLACE:
menggantikan karakter yang
disebut pada string karakter
dengan karakter / string karakter lain.
·
TRIM: menghapus karakter yang disebutkan
dari huruf paling awal atau paling akhir
(atau keduanya) dari string karakter. Dapat juga digunakan untuk menghapus spasi.
Pertemuan 4
The Functions
MONTH_BETWEEN (DATE1,
DATE2)
Mengembalikan jumlah
bulan antara date1 dan date2,
dan dapat mengembalikan nilai
negatif.
ADD_MONTHS(DATE,N)
Menambahkan
bulan sejumlah N ke DATE. Nilai dari N harus angka dan bisa negative.
NEXT_DAY(DATE,’CHAR’)
Menemukan
tanggal dari hari berikutnya dalam minggu (‘CHAR’), setelah DATE. Nilai
(‘CHAR’) dapat berisi angka yang mewakilkan hari atau string karakter.
LAST_DAY(DATE)
Menemukan
tanggal dari hari terakhir pada bulan yang menampung DATE.
ROUND(DATE[,’FMT’])
Mengembalikan tanggal
yang dibulatkan ke
atas berdasarkan ‘FMT’. Jika
’FMT’ tidak disebutkan, maka
tanggal akan dibulatkan ke hari
yang terdekat.
Data Type Conversions
ü TO_CHAR(NUMBER|DATE,[fmt],
[nlsparams])
Mengubah sebuah
nilai NUMBER atau DATE
menjadi sebuah karakter string
VARCHAR2 dengan format fmt. Number Conversion:
Parameter nlsparams menyebutkan
karakter-karakter yang
akan dikembalikan oleh
elemen format number. Jika
nlsparams atau parameter lain
tidak diisi, maka fungsi akan menggunakan nilai parameter
default. Date Conversion: Parameter
nlsparams menyebutkan dalam bahasa tanggal
apa bulan, nama
hari hari, serta
singkatan akan dikembalikan. Jika parameter nlsparams ini
tidak diisi, maka fungsi akan menggunakan bahasa tanggal
default.
ü TO_NUMBER(CHAR,[fmt],
[nlsparams])
Mengubah
sebuah karakter string yang memuat angka menjadi tipe data NUMBER dengan format
sesuai pada fmt. Parameter [nlsparams]
di fungsi ini memiliki kesamaan
dengan parameter [nlsparams] pada function TO_CHAR
untuk number conversion.
ü TO_DATE(CHAR,[fmt],
[nlsparams])
Mengubah sebuah
karakter string yang
mewakili tanggal ke
nilai dengan tipe data DATE berdasarkan fmt yang
disebutkan. Jika fmt tidak diisi,
maka format yang dikembalikan adalah DD-MON-YY (akan dijelaskan nanti). Parameter [nlsparams] di fungsi
ini memiliki kesamaan dengan parameter [nlsparams] pada function TO_CHAR
untuk date conversion.
General Functions
Fungsi-fungsi berikut ini
dapat digunakan dengan tipe data apapun dan berhubungan dengan penggunaan
nilai NULL:
Ø NVL
(expr1, expr2)
Mengubah
sebuah nilai NULL menjadi nilai yang tertentu.
Ø NVL2
(expr1, expr2, expr3)
Jika
expr1 bernilai NULL, maka fungsi akan mengembalikan nilai pada expr3; sedangkan
jika expr1 tidak bernilai null, maka fungsi akan mengembalikan nilai pada expr2
Ø NULLIF
(expr1, expr2)
Membandingkan
2 (dua) ekspresi dan mengembalikan NULL jika ekspresi tersebut sama. Jika kedua
ekspresi tidak sama, maka fungsi akan mengembalikan expr1.
Ø COALESCE
(expr1, expr2, . . ., exprN)
Mengembalikan
ekspresi non-NULL pertama dari daftar ekspresi.
Pertemuan 5
Group Functions /
Aggregate Functions
Types
Of Group Function Tidak seperti pada single-row function,
Group
Function atau Aggregate Function
bekerja pada sekumpulan baris
data untuk mengembalikan 1 buah hasil
untuk setiap groupnya. Sekumpulan
baris data
yang dimaksud
bisa melingkupi 1
(satu) tabel, atau sebuah tabel yang dibagi menjadi
beberapa group. Tipe-Tipe dari Group Function adalah sebagai berikut:
Function Description
|
Function Description
|
AVG([DISTINCT|ALL] n)
|
mengembalikan rata-rata
nilai dari n dengan
mengabaikan nilai NULL.
|
COUNT({*|[DISTINCT|ALL]
expr})
|
mengembalikan jumlah dari
baris data. Expr akan
mengembalikan nilai yang
tidak NULL saja. (COUNT
ALL dengan menggunakan
* akan mengembalikan
semua data kembar dan
baris data dengan nilai
NULL)
|
MAX ([DISTINCT|ALL]
expr)
|
mengembalikan nilai
maksimum dari expr,
dengan mengabaikan nilai
NULL.
|
MIN ([DISTINCT|ALL]
expr)
|
mengembalikan nilai
minimum dari expr,
dengan mengabaikan nilai
NULL.
|
STDDEV([DISTINCT|ALL] n)
|
mengembalikan standar
|
Beberapa pedoman dalam
menggunakan group function:
·
Keyword DISTINCT akan membuat
function membaca nilai-nilai yang tidak
kembar saja. ALL akan membuat
function membaca semua nilai,
termasuk nilai kembar. Default dari sebuah
function adalah ALL.
·
Tipe
data yang dapat
digunakan pada function
dengan parameter expr antara lain
CHAR, VARCHAR2, NUMBER, atau DATE.
·
Semua
group function mengabaikan
nilai NULL. Untuk menggantikan nilai NULL dengan nilai
lain, gunakan function NVL, NVL2,atau COALESCE. Anda dapat menggunakan AVG,
SUM, MIN, dan MAX Function pada
kolom yang menyimpan
data bertipe angka.
Anda juga dapat.
Pada waktu tertentu, anda harus membagi sebuah tabel menjadi group-group yang lebih
kecil. Hal ini
dapat dilakukan dengan menggunakan klausa GROUP BY. Anda dapat
menggunakan klausa GROUP BY
untuk membagi baris data pada sebuah tabel ke dalam group-group,
kemudian anda dapat menggunakan
Group Function untuk
mengembalikan
ringkasan informasi yang dibutuhkan
untuk setiap group.
Pada Syntax ini Group_by_expression
diisi dengan kolom
yang
nilainya dijadikan dasar dalam
mengelompokkan baris data.
Panduan
dalam menggunakan GROUP BY:
·
Jika
anda memasukkan kolom lain
selain group function pada klausa
SELECT, maka semua
kolom lain tersebut
harus dimasukkan ke dalam klausa GROUP BY.
·
Dengan
menggunakan klausa WHERE, anda
dapat menyaring baris data sebelum digroupkan.
·
anda tidak dapat menggunakan Column
Alias (Stage 1) pada klausa GROUP BY.
·
Jika
klausa GROUP BY diisi dengan
lebih dari 1
(satu) kolom, maka pengelompokan
akan dimulai dari kolom paling pertama yang disebutkan, lalu
dilanjutkan dengan kolom
berikutnya (dalam pengelompokan
sebelumnya).
·
Anda
dapat menggunakan klausa GROUP BY tanpa menggunakan Group Function.
Condition in Group
Function Result
Sama
seperti klausa WHERE yang digunakan untuk menyaring baris data yang anda SELECT,
anda juga dapat
menyaring hasil dari
Group Function dengan menggunakan
klausa HAVING. Anda
dapat menggunakan klausa HAVING untuk menyaring group mana yang akan ditampilkan
berdasarkan hasil group function.
Pertemuan 6
EQUIJOIN
Equijoin memerlukan
kehadiran Primary Key
dan Foreign Key
pada tabel-tabel yang akan digabungkan. Equijoin juga biasa disebut
sebagai simple JOIN atau inner
JOIN. penulisan equijoin dapat
dilakukan dengan menggunakan USING, ON, dan WHERE.
Menggunakan USING
Klausa USING
dapat digunakan untuk menyebutkan kolom
apa saja yang dijadikan referensi
JOIN ketika ada lebih dari 1 (satu) kolom yangcocok antara
tabel-tabel yang digabungkan. Kolom-kolom
yang digunakan pada klausa USING tidak boleh ditambahkan dengan
nama tabel atau alias pada bagian lain dari query.
Contoh
:
Menampilkan nama
lengkap mahasiswa dan
nama program studi dari seluruh mahasiswa
SELECT MAHASISWA.NAMA_DEPAN ||' '||
MAHASISWA.NAMA_BELAKANG "NAMA LENGKAP", PROGRAM_STUDI.NAMA_PRODI
FROM MAHASISWA JOIN PROGRAM_STUDI
USING (KODE_PRODI);
Menggunakan ON
Klausa
ON dapat digunakan untuk menuliskan
kondisi acak atau
untuk menuliskan kolom yang digunakan sebagai kondisi JOIN. pada klausa ON,
kondisi JOIN dipisahkan dari
kondisi pencarian lain
yang dituliskan pada klausa WHERE.
Contoh
:
Menampilkan nama
lengkap mahasiswa dan
nama program studi dari seluruh mahasiswa
SELECT MAHASISWA.NAMA_DEPAN ||' '||
MAHASISWA.NAMA_BELAKANG "NAMA LENGKAP", PROGRAM_STUDI.NAMA_PRODI
FROM MAHASISWA JOIN PROGRAM_STUDI
ON MAHASISWA.KODE_PRODI =
PROGRAM_STUDI.KODE_PRODI;
Menggunakan WHERE
JOIN
dapat dilakukan juga
dengan menggunakan klausa WHERE.
jika kondisi JOIN diletakkan
pada klausa WHERE, maka
kata kunci JOIN dapat dihilangkan
dari klausa FROM.
Contoh
:
Menampilkan nama
lengkap mahasiswa dan
nama program studi dari seluruh mahasiswa
SELECT MAHASISWA.NAMA_DEPAN ||' '||
MAHASISWA.NAMA_BELAKANG "NAMA LENGKAP", PROGRAM_STUDI.NAMA_PRODI
FROM MAHASISWA, PROGRAM_STUDI
WHERE MAHASISWA.KODE_PRODI =
PROGRAM_STUDI.KODE_PRODI;
LEFT OUTER JOIN
LEFT
OUTER JOIN akan menampilkan
semua baris data
dari tabel sebelah kiri (dari
kata kunci LEFT OUTER JOIN) meskipun ada baris data yang tidak memenuhi kondisi
JOIN dengan tabel sebelah kanan
contoh
:
Menampilkan semua kode
mk dan nama
mk beserta nilai uas tertinggi
dari mata kuliah
tersebut beserta matakuliah yang
belum memiliki nilai uas.
SELECT MK.KODE_MK, MK.NAMA_MK, MAX(N.UAS)
FROM MATAKULIAH MK LEFT OUTER JOIN NILAI
ON MK.KODE_MK = N.KODE_MK
GROUP BY MK.KODE_MK, MK.NAMA_MK;
RIGHT OUTER JOIN
RIGHT
OUTER JOIN akan menampilkan semua
baris data dari
tabel sebelah kanan (dari
kata kunci RIGHT OUTER JOIN) meskipun
ada baris data
yang tidak memenuhi
kondisi JOIN dengan tabel
sebelah kanan.
Contoh
:
Menampilkan semua
kode mk dan nama
mk beserta nilai
uas tertinggi dari mata
kuliah tersebut beserta
matakuliah yang belum memiliki nilai uas.
SELECT MK.KODE_MK, MK.NAMA_MK, MAX(N.UAS)
FROM NILAI N RIGHT OUTER JOIN MATAKULIAH
MK ON MK.KODE_MK = N.KODE_MK
GROUP BY MK.KODE_MK, MK.NAMA_MK;
FULL OUTER JOIN
FULL
OUTER JOIN akan menampilkan
semua baris data
dari tabel sebelah kanan dan
kiri meskipun ada baris data
pada kedua tabel
yang tidak memenuhi kondisi JOIN antar tabel.
Contoh
:
Menampilkan semua
nid dosen, nama
dosen, dan plotting
yang sudah dilakukan, beserta
dosen-dosen yang belum melakukan plotting.
SELECT P.ID_PLOTTING, D.NID,
D.NAMA_DOSEN, P.KODE_MK, P.HARI, P.JAM_MULAI, P.JAM_SELESAI
FROM PLOTTING_AJAR P FULL OUTER JOIN
DOSEN D
ON D.NID = P.NID;
SELF JOIN
Self
join adalah kondisi ketika anda melakukan JOIN pada 1 tabel yang sama. Untuk
membedakan antara tabel
yang satu dengan
yang lain, gunakan table aliases.
Kondisi yang digunakan
pada self join tidak harus berupa kondisi equijoin.
Contoh
:
Menampilkan nim,
nama belakang, dan kota_tinggal
dari mahasiswa yang tinggal
di kota yang sama dengan
mahasiswa bernama depan ‘Bambang’
SELECT M1.NIM, M1.NAMA_BELAKANG,
M1.KOTA_TINGGAL
FROM MAHASISWA M1 JOIN MAHASISWA M2 ON M2. NAMA_DEPAN = 'Bambang'
WHERE M1.KOTA_TINGGAL = M2.KOTA_TINGGAL;
Pertemuan 7
SUBQUERY
Pada
stage 1 sampai 4,
anda dihadapkan dengan
pencarian data yang kondisinya masih
jelas pada tabel,
tetapi tidak menutup
kemungkinan bahwa akan muncul kebutuhan data yang kondisinya tidak
jelas. Hal ini bisa diselesaikan dengan
menggunakan penggabungan 2
(dua) query atau lebih. (memasukkan
query yang 1 (satu) ke query yang lain) Query yang ada
di bagian dalam mengembalikan sebuah
nilai (atau kumpulan nilai)
yang digunakan
oleh
query bagian luar. Sebuah subquery
adalah sebuah query SELECT yang ditancapkan pada sebuah klausa
di query SELECT yang lain.
anda dapat meletakkan subquery
pada beberapa klausa pada SQL query, meliputi:
·
Klausa WHERE
·
Klausa HAVING
·
Klausa FROM
contoh For Single-Row Subquery:
Menampilkan nim,
nama belakang, dan
kota_tinggal dari mahasiswa yang
tinggal di kota
yang sama dengan
mahasiswa bernama depan ‘Bambang’
SELECT NIM, NAMA_BELAKANG, KOTA_TINGGAL
FROM MAHASISWA WHERE
KOTA_TINGGAL = (SELECT KOTA_TINGGAL FROM MAHASISWA
WHERE
NAMA_DEPAN = 'Bambang');
contoh
For Multiple-Row Subquery:
Menampilkan
nim, nama lengkap, kode mk, nama mk, dan nilai uas
yang
dari mahasiswa yang
mengambil mata kuliah yang diadakan
pada semester 4.
SELECT MHS.NIM,
MHS.NAMA_DEPAN ||' '|| MHS.NAMA_BELAKANG "NAMA LENGKAP", N.KODE_MK,
MK.NAMA_MK, N.UAS
FROM MAHASISWA MHS JOIN
NILAI N
ON MHS.NIM = N.NIM JOIN
MATAKULIAH MK
ON MK.KODE_MK =
N.KODE_MK
WHERE N.KODE_MK IN
(SELECT KODE_MK FROM MATAKULIAH WHERE SEMESTER = 4);
Set
Operator
Set operator menggabungkan 2 (dua)
atau lebih query menjadi 1 (satu) hasil. Semua set operator memiliki tingkatan
yang sama, jika
sebuah query memiliki banyak set operator, maka Oracle Server akan
membaca query dari kiri (atau atas) ke kanan (bawah), jika tidak ada tanda
kurung ( ) yang digunakan untuk mengurutkan.