1 / 66

SQL Server :: Transact– SQL #1

Laboratorium Perancangan / Pemrograman Basis Data Komposisi oleh Budi Prayitno Februari 2012 Batam. SQL Server :: Transact– SQL #1. Daftar SQL Statement. SELECT Statement Dasar. SELECT * | {[DISTINCT] column | expression [alias], ...} FROM table;

vlad
Download Presentation

SQL Server :: Transact– SQL #1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. LaboratoriumPerancangan/Pemrograman Basis Data Komposisioleh Budi Prayitno Februari 2012 Batam SQL Server :: Transact–SQL #1

  2. Daftar SQL Statement

  3. SELECT Statement Dasar SELECT * | {[DISTINCT] column | expression [alias], ...} FROM table; • SELECT berisi column yang ditampilkan • FROM berisi table dari mana column terkait dipilih

  4. Memilih Semua Column SELECT * FROM departments;

  5. Memilih Column Tertentu SELECT department_id, location_id FROM departments;

  6. 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 “;”

  7. Ekspresi Aritmatika • + Penambahan • – Pengurangan • * Perkalian • / Pembagian Presedensi: [* /] [+ –]

  8. Penggunaan Operator Aritmatika SELECT first_name, last_name, salary, salary + 300 FROM employees;

  9. 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

  10. Penggunaan Column Alias SELECT first_name nama, comission_pct AS komisi FROM employees; SELECT first_name AS “Nama”, salary * 12 “Gaji Tahunan” FROM employees;

  11. Operator Konkatenasi • Menghubungkan column atau string dengan column atau string lain • Direpresentasikan dengan “+” • Menghasilkan string SELECT first_name + ‘adalah seorang’ + job_id FROM employees;

  12. Duplikasi Row SELECT department_id FROM employees; SELECT DISTINCT department_id FROM employees;

  13. 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

  14. Penggunaan WHERE SELECT employee_id, first_name, job_id, department_id FROM employees WHERE department_id = 90;

  15. Operator Perbandingan

  16. 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;

  17. Operator IN SELECT employee_id, first_name || last_name emp_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);

  18. Operator LIKE SELECT first_name, last_name FROM employees WHERE first_name LIKE ‘S%’ AND last_name NOT LIKE ‘_a%’;

  19. Operator IS NULL SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL;

  20. Operator Logika

  21. 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);

  22. 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

  23. ORDER BY Clause SELECT * | {[DISTINCT] column | expression [alias], ...} FROM table; [WHERE condition(s)] [ORDER BY {column, expression, alias} [ASC | DESC]];

  24. 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;

  25. Character Function

  26. Character Function

  27. 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’;

  28. Number Function

  29. Date Function

  30. 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))

  31. Group Function • AVG • COUNT • MAX • MIN • STDEV • SUM • VAR

  32. Group Function

  33. 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

  34. 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;

  35. 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;

  36. 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];

  37. Penggunaan GROUP BY Clause SELECT department_id, AVG (salary) ... columnpada GROUP BY clausetidakmandatorypada SELECT clause SELECT AVG (salary) FROM employees GROUP BY department_id ORDER BY department_id;

  38. 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;

  39. 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

  40. 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

  41. HAVING Clause SELECT [column,] group_function (column), ... FROM table [WHERE condition] [GROUP BY column] [HAVING group_condition] [ORDER BY column];

  42. 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;

  43. Nested Group Function SELECT MAX (AVG (salary)) FROM employees GROUP BY department_id;

  44. Pengambilan Data dari Beberapa Table

  45. 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

  46. 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];

  47. 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

  48. NATURAL JOIN SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations;

  49. USING Clause • Digunakan untuk memodifikasi NATURAL JOIN untuk mendefinisikan column yang digunakan untuk equijoin seandainya terdapat column dengan nama yang sama namun tidak digunakan

  50. 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);

More Related