Transcript SQL Server Transact–SQL

Laboratorium Perancangan/Pemrograman Basis Data
Komposisi oleh Budi Prayitno
Februari 2012
Batam
SQL Server :: Transact–SQL #1
Daftar SQL Statement
SELECT
INSERT
UPDATE
Data Manipulation Language [DML]
DELETE
CREATE
ALTER
DROP
Data Definition Language [DDL]
TRUNCATE
GRANT
REVOKE
Data Control Language [DCL]
COMMIT
ROLLBACK
BEGIN TRANSACTION
END TRANSACTION
Transaction Control
SELECT Statement Dasar
SELECT * | {[DISTINCT] column | expression [alias], ...}
FROM table;
 SELECT berisi column yang ditampilkan
 FROM berisi table dari mana column terkait dipilih
Memilih Semua Column
SELECT *
FROM departments;
DEPARTMENT_ID
10
20
30
40
50
60
...
DEPARTMENT_NAME
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
MANAGER_ID
200
201
114
203
121
103
LOCATION_ID
1700
1800
1700
2400
1500
1400
Memilih Column Tertentu
SELECT department_id, location_id
FROM departments;
DEPARTMENT_ID
10
20
30
40
50
60
...
LOCATION_ID
1700
1800
1700
2400
1500
1400
Kaidah Penulisan SQL
 Tidak case–sensitive kecuali diapit tanda petik tunggal “’”atau
petik ganda “””
 Dapat dituliskan dalam satu atau lebih baris
 Kata kunci tidak dapat disingkat atau dipisah
 Clause biasanya ditulis dalam baris terpisah
 Indentasi digunakan untuk mempermudah pembacaan
 SQL statement dapat diakhiri dengan “;”
Ekspresi Aritmatika




+
–
*
/
Penambahan
Pengurangan
Perkalian
Pembagian
Presedensi: [* /] [+ –]
Penggunaan Operator Aritmatika
SELECT first_name, last_name,
salary, salary + 300
FROM employees;
FIRST_NAME
Donald
Douglas
Jennifer
Michael
Pat
Susan
...
LAST_NAME
OConnell
Grant
Whalen
Hartstein
Fay
Mavris
SALARY
2600
2600
4400
13000
6000
6500
SALARY+300
2900
2900
4700
13300
6300
6800
Pendefinisian Column Alias
 Mengubah tampilan header
 Dapat digunakan terhadap ekspresi/kalkulasi
 Digunakan menggunakan kata kunci “AS” atau langsung
mengikuti nama column
 Memerlukan tanda petik ganda “”” jika alias mengandung
spasi, karakter khusus atau ketika dimaksudkan sebagai case–
sensitive
Penggunaan Column Alias
SELECT first_name nama,
comission_pct AS komisi
FROM employees;
SELECT first_name AS “Nama”,
salary * 12 “Gaji Tahunan”
FROM employees;
Operator Konkatenasi
 Menghubungkan column atau string dengan column atau
string lain
 Direpresentasikan dengan “+”
 Menghasilkan string
SELECT first_name + ‘adalah seorang’ + job_id
FROM employees;
Duplikasi Row
SELECT department_id
FROM employees;
DEPARTMENT_ID
50
50
10
20
20
40
SELECT DISTINCT department_id
FROM employees;
DEPARTMENT_ID
50
10
20
40
WHERE Clause
 Membatasi pemilihan row pada SELECT menggunakan
WHERE clause
SELECT * | {[DISTINCT]} column | expression [alias], ...}
FROM table
[WHERE condition(s)];
 WHERE clause diposisikan tepat setelah FROM clause
Penggunaan WHERE
SELECT employee_id,
first_name,
job_id,
department_id
FROM employees
WHERE department_id = 90;
Operator Perbandingan
=
Sama dengan
>
Lebih besar
>=
Lebih besar atau sama dengan
!>
Tidak lebih besar
<
Lebih kecil
<=
Lebih kecil atau sama dengan
!<
Tidak lebih kecil
!=<>
Tidak sama dengan
BETWEEN … AND …
Berada di antara dua nilai
IN
Sama dengan nilai dalam daftar
LIKE
Cocok dengan pola tertentu
IS NULL
Merupakan nilai NULL
Penggunaan Operator Perbandingan
SELECT first_name, salary
FROM empoyees
WHERE salary <= 3500 AND salary >= 2500;
memberikan hasil yang sama dengan:
SELECT first_name, salary
FROM empoyees
WHERE salary BETWEEN 2500 AND 3500;
Operator IN
SELECT employee_id,
first_name || last_name emp_name,
salary,
manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
Operator LIKE
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE ‘S%’
AND last_name NOT LIKE ‘_a%’;
Operator IS NULL
SELECT employee_id, manager_id
FROM employees
WHERE manager_id IS NULL;
Operator Logika
AND
Mengembalikan TRUE jika kedua operand bernilai TRUE
OR
Mengembalikan TRUE jika salah satu atau kedua operand bernilai
TRUE
NOT
Negasi, mengembalikan TRUE jika operand bernilai FALSE, dan
sebaliknya
Digunakan bersama operator lain tidak berdiri sendiri
Penggunaan Operator Logika
SELECT employee_id,
FROM employees
WHERE salary >= 10000
AND (job_id LIKE ‘%MGR%’ OR job_id LIKE ‘%MAN%’)
AND departement_id NOT IN (50, 80);
ORDER BY Clause
 Berguna untuk mengurutkan row yang dipilih berdasarkan
column yang ditentukan pada ORDER BY clause
 ASC: membesar, merupakan nilai default
DESC: mengecil
 Diposisikan paling akhir pada SELECT statement
ORDER BY Clause
SELECT * | {[DISTINCT] column | expression [alias], ...}
FROM table;
[WHERE condition(s)]
[ORDER BY {column, expression, alias} [ASC | DESC]];
Penggunaan ORDER BY Clause
SELECT first_name,
job_id,
department_id,
hire_date,
salary * 12 annual_salary
FROM employees
WHERE 1 = 1
ORDER BY hire_date DESC, annual_salary;
Character Function
LOWER
UPPER
Case–Manipulation Function
SUBSTRING
LEN
PATINDEX
LTRIM | RTRIM
STUFF
Character–Manipulation Function
Character Function
LOWER (‘SQL Query’)
sql query
UPPER (‘SQL Query’)
SQL QUERY
SUBSTRING (‘HelloWorld’, 1, 5)
Hello
LEN (‘HelloWorld’)
10
PATINDEX (‘%loWo%’, ‘HelloWorld’)
4
STUFF (‘abcdef’, 2, 3, ‘ijklm’)
aijklmnef
LTRIM ( ‘
HelloWorld
HelloWorld’)
Penggunaan Character Function
SELECT employee_id,
first_name + last_name AS name,
job_id,
LEN (last_name),
PATINDEX (‘%a%’, last_name)
FROM employees
WHERE SUBSTRING (job_id, 4, 3) = ‘REP’;
Number Function
ROUND (123.4545, 2)
123.45
Date Function
DATEDIFF
Selisih di antara 2 tanggal
DATEADD
Menambahkan hari kalender ke tanggal
CONVERT
Format tanggal
Nested Function
 Fungsi dapat bersarang hingga kedalaman tidak terhingga
 Fungsi bersarang dievaluasi mulai hingga yang terdalam
hingga terluar
F3 ({F2 ({F1 ({arg1, ...}), ...}), ...})
LOWER (CONCAT (SUBSTR (first_name, 1, 1), last_name))
Group Function







AVG
COUNT
MAX
MIN
STDEV
SUM
VAR
Group Function
EMPLOYEE_ID
...
206
100
101
102
103
104
...
SALARY
8300
24000
17000
17000
9000
6000
MAX (SALARY)
24000
Penggunaan Group Function
SELECT AVG (salary),
MAX (salary),
MIN (salary),
SUM (salary)
FROM employees
WHERE job_id LIKE ‘%REP%’
 MIN dan MAX dapat digunakan untuk tipe data numeric,
string dan date
COUNT Function
SELECT COUNT (*)
FROM employees
WHERE department_id = 50;
SELECT COUNT (comission_pct)
FROM employees
WHERE department_id = 80;
SELECT COUNT (DISTINCT department_id)
FROM employees;
NULL pada Group Function
 Group function mengabaikan nilai NULL pada column
SELECT AVG (comission_pct)
FROM employees;
 Untuk menyertakan column bernilai NULL, gunakan NVL
SELECT AVG (NVL (comission_pct, 0))
FROM employees;
GROUP BY Clause
 Mengakomodasi keberadaan group function, namun tidak
mandatory
SELECT [column,] group_function (column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
Penggunaan GROUP BY Clause
SELECT department_id, AVG (salary)
...
column pada GROUP BY clause tidak mandatory pada SELECT
clause
SELECT AVG (salary)
FROM employees
GROUP BY department_id
ORDER BY department_id;
Penggunaan GROUP BY Clause
 Menggunakan lebih dari satu column pada GROUP BY clause:
SELECT department_id dept_id,
job_id,
SUM (salary)
FROM employees
GROUP BY department_id, job_id;
Penggunaan GROUP BY Clause
 Column yang bukan group function pada SELECT clause
mandatory didefinisikan pada GROUP BY clause:
SELECT department_id, COUNT (employee_id)
FROM employees;
ERROR at line 1:
ORA-00937: not a single–group group function
Penggunaan GROUP BY Clause
 Group function tidak dapat digunakan pada WHERE clause:
SELECT department_id, AVG (salary)
FROM employees
WHERE AVG (salary) > 8000
GROUP BY department_id;
ERROR at line 3:
ORA-00934: group function is not allowed here
HAVING Clause
SELECT [column,] group_function (column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[HAVING group_condition]
[ORDER BY column];
HAVING Clause
SELECT department_id, MAX (salary)
FROM employees
GROUP BY department_id
HAVING MAX (salary) > 10000;
SELECT job_id, SUM (salary) payroll
FROM employees
WHERE job_id NOT LIKE ‘%REP%’
GROUP BY job_id
HAVING SUM (salary) > 13000
ORDER BY job_id;
Nested Group Function
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Pengambilan Data dari Beberapa
Table
EMPLOYEE_ID
198
199
...
201
202
203
FIRST_NAME DEPARTMENT_ID
Donald
50
Douglas
50
Michael
Pat
Susan
DEPARTMENT_ID
10
20
...
40
50
60
20
20
40
EMPLOYEE_ID
198
199
...
201
202
203
DEPARTMENT_NAME MANAGER_ID
Administration
200
Marketing
201
Human Resources
Shipping
IT
FIRST_NAME
Donald
Douglas
DEPARTMENT_ID
50
50
DEPARTMENT_NAME
Shipping
Shipping
Michael
Pat
Susan
20
20
40
Marketing
Marketing
Human Resources
203
121
103
Jenis JOIN





INNER JOIN sama dengan JOIN
LEFT OUTER JOIN sama dengan LEFT JOIN
RIGHT OUTER JOIN sama dengan RIGHT JOIN
FULL OUTER JOIN sama dengan FULL JOIN
CROSS JOIN
JOIN Clause
SELECT table1.column, table2.column
FROM table1
[JOIN table2 USING (column_name)] |
[JOIN table2
ON (table1.column_name = table2.column_name)] |
[LEFT | RIGHT | FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)] |
[CROSS JOIN table2];
NATURAL JOIN
 Didasarkan pada semua column pada kedua table yang
memiliki nama yang sama
 Menampilkan row dari kedua table yang memiliki nilai yang
sama pada column yang didefinisikan
 Jika terdapat column dengan nama yang sama namun tipe
data yang berbeda, maka terjadi error
NATURAL JOIN
SELECT department_id,
department_name,
location_id,
city
FROM departments
NATURAL JOIN locations;
USING Clause
 Digunakan untuk memodifikasi NATURAL JOIN untuk
mendefinisikan column yang digunakan untuk equijoin
seandainya terdapat column dengan nama yang sama namun
tidak digunakan
USING dan ON Clause
SELECT d.department_id,
d.department_name,
d.location_id,
l.city
FROM departments d
JOIN locations l
USING (location_id);
memberikan hasil yang sama dengan:
ON (d.location_id = l.location_id);
JOIN Lebih Lanjut
SELECT e.first_name + e.last_name,
d.department_name
FROM employees e
JOIN departments d
ON (e.department_id = d.department_id)
...
JOIN locations l
ON (d.location_id = l.location_id)
...
AND e.manager_id = 149;
Non-Equijoin
SELECT e.first_name + e.last_name AS name,
e.salary,
j.grade_level
FROM employees e
JOIN job_grades g
ON e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
OUTER JOIN
 JOIN yang mengembalikan tidak hanya baris yang memenuhi,
namun juga yang tidak memenuhi, persyaratan JOIN
 Diterapkan pada sisi kiri [LEFT], sisi kanan [RIGHT] atau
kedua sisi [FULL] persyaratan JOIN
Penggunaan OUTER JOIN
SELECT e.first_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
...
RIGHT OUTER JOIN departments d
...
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
LEFT OUTER JOIN
EMPLOYEE_ID
198
199
200
201
202
203
FIRST_NAME
Donald
Douglas
Andreas
Michael
Pat
Susan
DEPARTMENT_ID
50
50
70
20
20
40
EMPLOYEE_ID
198
199
200
201
202
203
FIRST_NAME
Donald
Douglas
Andreas
Michael
Pat
Susan
DEPARTMENT_ID
10
20
30
40
50
60
DEPARTMENT_ID
50
50
70
20
20
40
DEPARTMENT_NAME
Administration
Marketing
Finance
Human Resources
Shipping
IT
DEPARTMENT_NAME
Shipping
Shipping
Marketing
Marketing
Human Resources
MANAGER_ID
200
201
202
203
121
103
RIGHT OUTER JOIN
EMPLOYEE_ID
198
199
200
201
202
203
FIRST_NAME
Donald
Douglas
Andreas
Michael
Pat
Susan
DEPARTMENT_ID
50
50
70
20
20
40
EMPLOYEE_ID
FIRST_NAME
198
199
201
202
Donald
Douglas
Michael
Pat
203
Susan
DEPARTMENT_ID
10
20
30
40
50
60
DEPARTMENT_ID
10
50
50
20
20
30
40
60
DEPARTMENT_NAME
Administration
Marketing
Finance
Human Resources
Shipping
IT
DEPARTMENT_NAME
Administration
Shipping
Shipping
Marketing
Marketing
Finance
Human Resources
IT
MANAGER_ID
200
201
202
203
121
103
FULL OUTER JOIN
EMPLOYEE_ID
198
199
200
201
202
203
FIRST_NAME
Donald
Douglas
Andreas
Michael
Pat
Susan
DEPARTMENT_ID
50
50
70
20
20
40
EMPLOYEE_ID
FIRST_NAME
198
199
200
201
202
Donald
Douglas
Andreas
Michael
Pat
203
Susan
DEPARTMENT_ID
10
20
30
40
50
60
DEPARTMENT_NAME
Administration
Marketing
Finance
Human Resources
Shipping
IT
DEPARTMENT_ID
10
50
50
DEPARTMENT_NAME
Administration
Shipping
Shipping
20
20
30
40
60
Marketing
Marketing
Finance
Human Resources
IT
MANAGER_ID
200
201
202
203
121
103
CROSS JOIN
SELECT e.first_name, d.department_name
FROM employees e
CROSS JOIN departments d
memberikan hasil yang sama dengan:
SELECT e.first_name, d.department_name
FROM employees e, departments d
* perhatikan statement kedua tanpa WHERE clause
CROSS JOIN
EMPLOYEE_ID
198
199
200
FIRST_NAME
Donald
Douglas
Andreas
DEPARTMENT_ID
50
50
70
EMPLOYEE_ID
198
198
198
199
199
199
…
200
DEPARTMENT_ID
10
20
30
DEPARTMENT_NAME
Administration
Marketing
Finance
FIRST_NAME
Donald
Donald
Donald
Douglas
Douglas
Douglas
DEPARTMENT_ID
10
20
30
10
20
30
DEPARTMENT_NAME
Administration
Marketing
Finance
Administration
Marketing
Finance
Andreas
30
Finance
MANAGER_ID
200
201
202
INSERT Statement Dasar
INSERT INTO table (column1, column2, ..., columnN)
VALUES (value1, value2, …, valuesN);
 Jika column tidak disebutkan, maka semua column diisi
terurut dari pertama hingga terakhir
 VALUES berisi nilai yang dimasukkan ke column
INSERT … SELECT …
INSERT INTO tableX (column1, column2, ..., columnN)
SELECT columnA, columnB, …, columnM
FROM tableY
WHERE condition(s);
 Table sumber tableY dapat berbeda dari table tujuan tableX
 Faktor penentu keberhasilan eksekusi hanya kesamaan tipe
data dan ukuran data
Penggunaan INSERT
INSERT INTO employees (employee_id, last_name, salary)
VALUES (999, Duke, 15000);
INSERT INTO employees (employee_id, last_name, salary)
SELECT 999, Duke, salary
FROM employees
WHERE employee_id = 198;
UPDATE Statement Dasar
UPDATE table
SET column1 = value1[, …, columnN = valueM]
WHERE condition(s);
 Jika WHERE clause tidak dispesifikasikan, maka column yang
disebutkan pada semua row dalam table akan diubah nilainya
 SET clause dapat mengubah satu atau lebih column dalam
satu UPDATE statement
 Satu UPDATE statement dapat mengubah hanya satu row
data
Penggunaan UPDATE
UPDATE employees
SET first_name = ‘Duke’,
last_name = ‘Hazard’,
email = ‘[email protected]’
WHERE employee_id = 198;
DELETE Statement Dasar
DELETE table
WHERE condition(s);
 Jika WHERE clause tidak dispesifikasikan, maka semua row
dalam table akan dihapus
 DELETE statement digunakan untuk menghapus satu atau
lebih row dalam satu table saja
Penggunaan DELETE
DELETE employees
WHERE employee_id = 198;