Retrieving Data SQL Statements SELECT statement mengambil retrieves

Retrieving Data

SQL Statements SELECT statement mengambil (retrieves) informasi dari database

SELECT Statements Projection • Kemampuan dapat memilih kolom yang ingin ditampilkan dengan query Selection • Kemampuan untuk memilih baris yang ingin ditampilkan dengan query Joining • Kemampuan untuk menampilkan data yang tersimpan di tabel yang berbeda dengan menciptakan keterkaitan diantaranya

Aljabar Relational Review Projection Selection Table 1 Join Table 1 Table 2

SELECT Statements

Basic SELECT Statement SELECT FROM *|{[DISTINCT] column|expression [alias], . . . } table; • SELECT mengidentifikasi columns • FROM mengidentifikasi table

Selecting Semua Columns SELECT * FROM departments;

Selecting Columns Tertentu SELECT department_id, location_id FROM departments;

Arithmetic Expressions Membuat suatu ekspresi dengan nomor dan data tanggal dengan menggunakan operator aritmatika Operator Description + Add - Subtract * Multiply / Divide

Using Arithmetic Operators SELECT last_name, salary + 300 FROM employees; …

Operator Precedence SELECT last_name, salary, 12*salary+100 FROM employees; …

Menggunakan (Kurung) SELECT last_name, salary, 12*(salary+100) FROM employees; …

Menggunakan Alias Mengganti nama kolom Digunakan dalam perhitungan

Menggunakan Alias SELECT last_name AS name, commission_pct comm FROM employees; … SELECT last_name "Name", salary*12 "Annual Salary" FROM employees; …

Duplicate Rows Tampilan standar dari query adalah semua baris, termasuk duplikasinya SELECT department_id FROM employees; …

Menghilangkan Duplikasi Gunakan DISTINCT dalam klausa SELECT DISTINCT department_id FROM employees;

Membatasi Baris yang Ditampilkan Gunakan klausa WHERE SELECT FROM [WHERE *|{[DISTINCT] column|expression [alias], . . . } table condition(s)];

Membatasi dengan WHERE EMPLOYEES … SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;

Kondisi Perbandingan Operator Meaning = Equal to > Greater than >= < Greater than or equal to Less than <= Less than or equal to <> Not equal to

Menggunakan Kondisi Perbandingan SELECT last_name, salary FROM employees WHERE salary <= 3000;

Kondisi Perbandingan Operator Meaning BETWEEN. . . AND. . . Between two values (inclusive), IN(set) Match any of a list of values LIKE Match a character pattern IS NULL Is a null value

Menggunakan BETWEEN kondisi untuk menampilkan baris berdasarkan suatu rentang nilai SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; Batas bawah Batas

Menggunakan Kondisi IN Untuk menampilkan nilai dalam daftar IN SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);

Menggunakan LIKE § Menampilkan berdasarkan wildcard § Kondisi pencarian dapat berisikan karakter literal atau nomor: – % menunjukkan zero atau banyak karakter – _ menunjukkan satu karakter SELECT FROM WHERE first_name employees first_name LIKE 'S%'; first_name employees first_name LIKE ‘_U';

Menggunakan LIKE Dapat menggunakan keduanya SELECT last_name FROM employees WHERE last_name LIKE '_o%';

Kondisi Logis Operator Meaning AND Returns TRUE if both component conditions are true OR Returns TRUE if either component condition is true NOT Returns TRUE if the following condition is false

Operator AND membutuhkan kedua kondisi bernilai TRUE SELECT FROM WHERE AND employee_id, last_name, job_id, salary employees salary >=10000 job_id LIKE '%MAN%';

Operator OR OR hanya membutuhkan satu kondisi bernilai TRUE SELECT FROM WHERE OR employee_id, last_name, job_id, salary employees salary >= 10000 job_id LIKE '%MAN%';

Operator NOT SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

Aturan Precedence Order Evaluated 1 2 3 4 5 6 7 8 Operator Arithmetic operators Concatenation operator Comparison conditions IS [NOT] NULL, LIKE, [NOT] IN [NOT] BETWEEN NOT logical condition AND logical condition OR logical condition Note: Kesampingkan aturan dengan menggunakan (kurung)

Aturan Precedence SELECT FROM WHERE OR AND last_name, job_id, salary employees job_id = 'SA_REP' job_id = 'AD_PRES' salary > 15000;

Klausa ORDER BY q Urutkan dengan klausa ORDER BY § ASC: Urutan naik (default) § DESC: Urutan turun SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ; …