Membatasi dan Menyortir Data

Kemampuan penting dari pernyataan SELECT adalah Seleksi, Proyeksi dan Penggabungan. Menampilkan kolom tertentu dari tabel dikenal sebagai operasi proyek. Kami sekarang akan fokus untuk menampilkan baris keluaran tertentu. Ini dikenal sebagai operasi pemilihan. Baris tertentu dapat dipilih dengan menambahkan klausa WHERE ke kueri SELECT. Faktanya, klausa WHERE muncul tepat setelah klausa FROM dalam hierarki kueri SELECT. Urutan harus dipertahankan di semua skenario. Jika dilanggar, Oracle mengajukan pengecualian.

Sintaksis:

SELECT *|{[DISTINCT] column| expression [alias],..}
FROM table
[WHERE condition(s)]

Dalam sintaks,

Klausa WHERE adalah kata kunci
[kondisi] berisi nama kolom, ekspresi, konstanta, literal, dan operator perbandingan.

Misalkan manajer Anda mengerjakan anggaran triwulanan untuk organisasi Anda. Sebagai bagian dari aktivitas ini, Anda perlu membuat daftar detail penting setiap karyawan, tetapi hanya untuk karyawan yang dibayar setidaknya $ 25.000 per tahun. Kueri SQL di bawah menyelesaikan tugas ini. Perhatikan penggunaan klausa WHERE yang ditampilkan dalam teks tebal.

SELECT Employee_ID, Last_Name, First_Name, Salary
FROM employees
WHERE Salary >= 25000;  

EMPLOYEE_ID  LAST_NAME        FIRST_NAME       SALARY
----------   ---------------  ---------------  -----------
88303        Jones            Quincey          $30,550.00
88404        Barlow           William          $27,500.00 88505 Smith Susan $32,500.00

3 rows selected

Poin yang perlu diperhatikan -

Klausa SELECT hanya dapat berisi satu klausa WHERE. Namun, beberapa kondisi filter dapat ditambahkan ke klausa WHERE menggunakan operator AND atau OR.
Kolom, literal, atau ekspresi dalam klausa predikat harus dari tipe data yang serupa atau dapat dipertukarkan.
Alias kolom tidak dapat digunakan di klausa WHERE.
Literal karakter harus diapit dalam tanda kutip tunggal dan peka huruf besar / kecil.
Literal tanggal harus diapit dalam tanda kutip tunggal dan peka terhadap format. Format defaultnya adalahDD-MON-RR.

Operator Perbandingan

Operator perbandingan digunakan dalam predikat untuk membandingkan satu istilah atau operan dengan istilah lain. SQL menawarkan serangkaian operator kesetaraan, ketidaksetaraan, dan lain-lain yang komprehensif. Mereka bisa digunakan bergantung pada data dan filter kondisi logika dalam kueri SELECT. Saat Anda menggunakan operator perbandingan dalam klausa WHERE, argumen (objek atau nilai yang Anda bandingkan) di kedua sisi operator harus berupa nama kolom, atau nilai tertentu. Jika nilai tertentu digunakan, maka nilainya harus berupa nilai numerik atau string literal. Jika nilainya berupa string karakter atau tanggal, Anda harus memasukkan nilai dalam tanda kutip tunggal ('').

Oracle memiliki sembilan operator pembanding untuk digunakan dalam kondisi kesetaraan atau ketidaksetaraan.

Operator  Meaning
=         equal to 
<         less than
>         greater than
>=        greater than or equal to
<=        less than or equal to
!=        not equal to
<>        not equal to

Operator Oracle lainnya adalah ANTARA..DAN, IN, LIKE, dan IS NULL.

ANTARA Operator

Operator BETWEEN dapat digunakan untuk membandingkan nilai kolom dalam rentang tertentu. Rentang yang ditentukan harus memiliki batas bawah dan atas yang keduanya inklusif selama perbandingan. Penggunaannya mirip dengan operator pertidaksamaan komposit (<= dan> =). Ini bisa digunakan dengan nilai numerik, karakter dan tipe tanggal.

Misalnya, kondisi WHERE SALARY BETWEEN 1500 AND 2500 dalam query SELECT akan mendaftar karyawan yang gajinya antara 1500 dan 2500.

Operator IN

Operator IN digunakan untuk menguji nilai kolom dalam satu set nilai. Jika kolom dapat disamakan dengan salah satu nilai dari himpunan yang diberikan, kondisinya divalidasi. Kondisi yang ditentukan menggunakan operator IN juga dikenal sebagai kondisi keanggotaan.

Misalnya, kondisi WHERE SALARY IN (1500, 3000, 2500) dalam query SELECT akan membatasi baris di mana gaji adalah 1500, 3000 atau 2500.

Operator LIKE

Operator LIKE digunakan untuk pencocokan pola dan pencarian wildcard dalam query SELECT. Jika bagian dari nilai kolom tidak diketahui, karakter pengganti dapat digunakan untuk menggantikan bagian yang tidak diketahui. Ini menggunakan operator wildcard untuk membangun string pencarian, sehingga pencarian dikenal sebagai pencarian Wildcard. Kedua operator tersebut adalah Percentile ('%') dan Underscore ('_'). Garis bawah ('_') menggantikan satu karakter sementara persentil ('%') menggantikan lebih dari satu karakter. Mereka juga bisa digunakan dalam kombinasi.

Misalnya, kueri SELECT di bawah ini mencantumkan nama depan karyawan yang nama belakangnya dimulai dengan 'SA'.

SELECT first_name
FROM employees
WHERE last_name LIKE 'SA%';

Kondisi IS (NOT) NULL

Untuk diperhatikan, nilai NULL tidak dapat diuji menggunakan operator persamaan. Itu karena nilai NULL tidak diketahui dan tidak ditetapkan saat operator persamaan menguji nilai tertentu. Operator IS NULL berfungsi sebagai operator persamaan untuk memeriksa nilai NULL dari sebuah kolom.

Misalnya, kondisi WHERE COMMISSION_PCT IS NULL dalam query SELECT akan mencantumkan karyawan yang tidak memiliki persentase komisi.

Operator Logis

Beberapa kondisi filter dapat ditambahkan ke predikat klausa WHERE. Lebih dari satu kondisi dapat digabungkan bersama menggunakan operator logika AND, OR dan NOT.

AND: menggabungkan dua atau lebih ketentuan, dan mengembalikan hasil hanya jika semua ketentuannya benar.
OR: menggabungkan dua atau lebih ketentuan, dan ini mengembalikan hasil jika salah satu ketentuannya benar.
NOT: meniadakan ekspresi yang mengikutinya.

Operator DAN menautkan dua atau lebih ketentuan dalam klausa WHERE dan mengembalikan TRUE hanya jika semua ketentuannya benar. Misalkan seorang manajer membutuhkan daftar karyawan wanita. Selanjutnya, daftar tersebut hanya boleh menyertakan karyawan dengan nama belakang yang dimulai dengan huruf "E" atau yang muncul kemudian dalam alfabet. Selain itu, tabel hasil harus diurutkan berdasarkan nama belakang karyawan. Ada dua syarat sederhana yang harus dipenuhi. Klausa WHERE dapat ditulis sebagai: WHERE Gender = 'F' AND last_name> 'E'.

SELECT last_name "Last Name", first_name "First Name", Gender "Gender"
FROM employees
WHERE Gender = 'F' AND last_name > 'E'
ORDER BY last_name;

Operator OR menautkan lebih dari satu kondisi dalam klausa WHERE dan mengembalikan TRUE jika salah satu kondisi mengembalikan true. Misalkan persyaratan manajer organisasi Anda sedikit berubah. Daftar karyawan lain diperlukan, tetapi dalam daftar ini karyawan harus: (1) perempuan atau, (2) memiliki nama belakang yang diawali dengan huruf "T" atau huruf yang muncul kemudian dalam alfabet. Tabel hasil harus diurutkan berdasarkan nama belakang karyawan. Dalam situasi ini salah satu dari dua kondisi dapat dipenuhi untuk memenuhi kueri. Karyawan perempuan harus dicantumkan bersama dengan karyawan yang memiliki nama yang memenuhi syarat kedua.

Operator NOT digunakan untuk meniadakan ekspresi atau konisi.

Klausul ORDER BY

Jika Anda hanya menampilkan beberapa baris data, mungkin tidak perlu mengurutkan keluaran; namun, jika Anda menampilkan banyak baris, manajer dapat dibantu dalam pengambilan keputusan dengan menyortir informasi. Output dari pernyataan SELECT dapat diurutkan dengan menggunakan klausa ORDER BY opsional. Saat Anda menggunakan klausa ORDER BY, nama kolom yang Anda pesan juga harus nama kolom yang ditentukan di klausa SELECT.

Kueri SQL di bawah ini menggunakan klausa ORDER BY untuk mengurutkan tabel hasil menurut kolom last_name dalam urutan menaik. Urutan naik adalah urutan pengurutan default.

SELECT last_name, first_name
FROM employees
WHERE last_name >= 'J'
ORDER BY last_name;

last_name        first_name
---------------  ---------------
Jones            Quincey
Klepper          Robert
Quattromani      Toni
Schultheis       Robert

Penyortiran juga dapat didasarkan pada nilai numerik dan tanggal. Penyortiran juga dapat dilakukan berdasarkan beberapa kolom.

Secara default, klausa ORDER BY akan mengurutkan baris keluaran dalam tabel hasil dalam urutan menaik. Kita dapat menggunakan kata kunci DESC (kependekan dari descending) untuk mengaktifkan jenis descending. Alternatif default adalah ASC yang mengurutkan dalam urutan menaik, tetapi kata kunci ASC jarang digunakan karena ini adalah default. Ketika kata kunci opsional ASC atau DESC digunakan, itu harus mengikuti nama kolom yang Anda sortir di klausa WHERE.

Positional Sorting - Posisi numerik kolom dalam daftar kolom yang dipilih dapat diberikan dalam klausa ORDER BY, sebagai ganti nama kolom. Ini terutama digunakan dalam kueri UNION (dibahas nanti). Kueri mengurutkan hasil yang ditetapkan oleh gaji karena muncul ke-2 dalam daftar kolom.

SELECT  first_name, salary
FROM employees
ORDER BY 2;

Variabel Substitusi

Ketika kueri SQL harus dieksekusi lebih dari sekali untuk set input yang berbeda, variabel substitusi dapat digunakan. Variabel substitusi dapat digunakan untuk meminta input pengguna sebelum eksekusi kueri. Mereka banyak digunakan dalam pembuatan laporan berbasis kueri yang mengambil rentang data dari pengguna sebagai input untuk pemfilteran bersyarat dan tampilan data. Variabel substitusi diawali dengan simbol ampersand (&) tunggal untuk menyimpan nilai sementara. Sebagai contoh,

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM employees
WHERE LAST_NAME = &last_name
OR EMPLOYEE_ID = &EMPNO;

Ketika query SELECT di atas dijalankan, oracle mengidentifikasi '&' sebagai variabel substitusi. Ini meminta pengguna untuk memasukkan nilai untuk 'last_name' dan 'EMPNO' seperti di bawah ini.

Enter value for last_name:
Enter value for empno:

Setelah pengguna memberikan masukan ke kedua variabel, nilai diganti, kueri diverifikasi dan dijalankan.

Poin yang perlu diperhatikan -

Jika variabel dimaksudkan untuk menggantikan karakter atau nilai tanggal, literal perlu diapit tanda kutip tunggal. Teknik yang berguna adalah dengan menyertakan variabel substitusi ampersand dalam tanda kutip tunggal saat menangani nilai karakter dan tanggal.
Baik Pengembang SQL dan SQL * Plus mendukung variabel substitusi dan perintah DEFINE / UNDEFINE. Meskipun SQL Developer atau SQL * Plus tidak mendukung pemeriksaan validasi (kecuali untuk tipe data) pada input pengguna.
Anda dapat menggunakan variabel substitusi tidak hanya di klausa WHERE pada pernyataan SQL, tetapi juga sebagai substitusi untuk nama kolom, ekspresi, atau teks.

Menggunakan Variabel Substitusi Double-Ampersand

Jika variabel substitusi yang sama digunakan di lebih dari satu tempat, maka untuk menghindari memasukkan kembali data yang sama, kita menggunakan ampers dan substitusi ganda. Dalam kasus seperti itu, nilai variabel substitusi, setelah dimasukkan, akan diganti pada semua waktu penggunaan.

SELECT first_name, HIRE_DATE, SEPARATION_DATE
FROM employees
WHERE HIRE_DATE LIKE '%&DT%' AND SEPARATION_DATE '%&&DT%'

Perhatikan bahwa nilai & DT yang sama diganti dua kali dalam kueri di atas. Jadi, nilainya setelah diberikan oleh pengguna akan diganti di dua tempat.

Perintah DEFINE dan VERIFIKASI

Pengaturan definisi variabel dalam sesi diatur oleh fitur DEFINE dari SQL * Plus. Variabel dapat ditentukan dalam sesi, untuk menghindari penghentian selama eksekusi kueri. Oracle membaca variabel yang sama setiap kali ditemui dalam kueri SQL. Ini dalam keadaan ON secara default. Dengan bantuan klausa DEFINE, seseorang dapat mendeklarasikan variabel dalam baris perintah sebelum eksekusi kueri sebagaiDEFINE variable=value;.

Perintah verifikasi memverifikasi substitusi di atas yang ditampilkan sebagai pernyataan LAMA dan BARU. Ini OFF secara default dan dapat diatur ke ON menggunakan perintah SET.

SQL> SET DEFINE ON
SQL> SET VERIFY ON
SQL> DEFINE NAME = MARTIN'
SQL> SELECT first_name, SALARY 
FROM employees
WHERE first_name = '&NAME';
OLD   1: select first_name, sal from employee where first_name = '&first_name'
new   1: select first_name, sal from employee where first_name = 'MARTIN'

first_name     SALARY
-------        -------
MARTIN         5000