Kursus
Pelaporan dengan SQL
4 jam
39.9K
Dalam tutorial ini, Anda akan berfokus pada SQL ANSI (American National Standards Institute) yang berfungsi di setiap database seperti Oracle, MySQL, Microsoft SQL Server, dan lainnya! Mari mulai dengan sebuah pengantar SQL (Structured Query Language) dan mengapa seorang data scientist membutuhkannya.
SQL dan Data Science
Pada bagian ini, Anda akan melihat mengapa seorang data scientist sebaiknya mempelajari SQL. Mari lihat bagaimana SQL akan membantu karier Anda sebagai data scientist:
- SQL telah menjadi persyaratan untuk sebagian besar pekerjaan data science, termasuk: analis data, pengembang BI (Business Intelligence), programmer, dan programmer database. SQL memungkinkan Anda berkomunikasi dengan database dan bekerja dengan data Anda.
- Jika Anda pernah menggunakan perangkat lunak seperti Tableau atau perangkat lunak pelaporan atau visualisasi data lainnya, Anda mungkin melihat bagaimana Anda menghubungkan proyek ke database, lalu drag-and-drop grafik ke laporan dengan hanya menentukan field, dan perangkat lunak mengerjakan sisanya. Jadi ketika Anda melakukan drag-and-drop atribut data atau menggunakan antarmuka grafis untuk semua operasi dengan sekali klik, di balik layar perangkat lunak menjalankan SQL yang berinteraksi dengan database. Dengan mempelajari SQL, Anda dapat berinteraksi langsung dengan database.
- SQL dapat digunakan dengan semua bahasa pemrograman aplikasi seperti PHP, Java. Anda dapat membuat visualisasi data sendiri dengan mengintegrasikan SQL dalam aplikasi atau mengambil data dari database dan mengonversinya ke format XML, JSON untuk digunakan dalam web service atau API.
- Database telah berevolusi selama bertahun-tahun. Seiring Big Data menjadi pembicaraan di mana-mana dan data digunakan dalam kehidupan sehari-hari, database NoSQL menjadi populer. Mempelajari SQL akan membantu Anda membangun fondasi kuat dalam database dan memahami kapan menggunakan database terstruktur dan kapan menggunakan NoSQL serta menghargai perbedaannya.
Di sini saya akan membahas sedikit pengantar dan terminologi yang digunakan dalam SQL dan database yang akan membantu Anda belajar ngoding lebih cepat! Jika Anda sudah tahu apa itu database relasional dan apa itu SQL, Anda bisa langsung lompat ke kode.
Pengantar SQL dan Database
Database adalah kumpulan informasi yang terorganisasi. Untuk mengelola database, kita memerlukan Data Base Management Systems (DBMS). DBMS adalah program yang menyimpan, mengambil, dan memodifikasi data dalam database sesuai permintaan.
Ada berbagai jenis database dari waktu ke waktu: hierarkis, jaringan, relasional, dan kini NoSQL. Database relasional adalah kumpulan relasi atau tabel dua dimensi.
Berikut adalah terminologi yang digunakan dalam RDBMS:
| Istilah | Deskripsi |
|---|---|
| Tabel | Tabel adalah struktur penyimpanan dasar dari RDBMS. Tabel menyimpan semua data yang diperlukan tentang sesuatu di dunia nyata. Contoh: Karyawan. |
| Baris Tunggal atau Tuple | Mewakili semua data yang diperlukan untuk karyawan tertentu. Setiap baris dalam tabel dapat diidentifikasi dengan Primary Key, yang tidak mengizinkan baris duplikat. |
| Kolom atau Atribut | Biasanya merujuk pada karakteristik dari sebuah entitas |
| Primary Key | Sebuah field yang mengidentifikasi baris secara unik |
| Foreign Key | Foreign Key adalah kolom yang mengidentifikasi bagaimana tabel saling berelasi. Foreign key merujuk pada Primary Key di tabel lain. |
Anda dapat mengaitkan banyak tabel menggunakan Primary dan Foreign Key. Setiap baris dalam database relasional diidentifikasi secara unik oleh Primary Key (PK). Anda dapat merujuk tabel lain menggunakan Foreign Key (FK). Misalnya:
Database relasional dapat diakses menggunakan Structured Query Language atau SQL. Setiap database akan mendukung ANSI SQL sebagai standar SQL, namun juga memiliki sintaks sendiri untuk memudahkan beberapa operasi. Dalam tutorial ini, Anda akan mempelajari ANSI SQL sehingga dapat bekerja dengan semua database. ANSI SQL dapat dibagi menjadi lima bagian. Saya akan menyebutkan semuanya, tetapi di sini dua bagian yang relevan adalah Data Retrieval dan DML:
- Data Retrieval:
- Select.
- Data Manipulation Language (DML):
- Insert, Update, Delete, Merge
- Data Definition Language (DDL):
- Create, Alter, Drop, Rename, Truncate.
- Data Control Language (DCL):
- Grant, Revoke.
- Transaction Control:
- Commit, Rollback, Savepoint.
Ada berbagai vendor RDBMS yang tersedia. Yang paling umum dan paling sering digunakan adalah:
- Oracle (Oracle Corporation)
- Microsoft SQL Server (Microsoft)
- MySQL (Oracle Corporation)
- PostgreSQL (PostgreSQL Global Development Group)
- SQLite (Dikembangkan oleh D. Richard Hipp)
Jadi di mana Anda menjalankan kueri SQL? Bisa jadi:
- Perangkat lunak pelaporan yang memungkinkan Anda membaca dari database dan melihat data (Contoh: Tableau, Microsoft BI)
- GUI untuk administrasi database (contoh: TOAD, SQL Developer untuk Oracle, phpMyAdmin untuk MySQL)
- Antarmuka konsol langsung ke database (Contoh: SQL Plus untuk database Oracle)
Jika Anda memiliki kredensial untuk masuk ke database, Anda dapat melihat objek database menggunakan kueri atau GUI untuk melihat objek database sesuai vendor database Anda.
Cukup itu! Mari mulai belajar SQL...
Tipe Data SQL
Setiap kolom dalam sebuah database memiliki nama, tipe data, dan terkadang ukuran yang terkait dengannya. Tugas pengembang database adalah merancang database dan memutuskan tipe data apa yang harus digunakan sesuai kebutuhan dan volume data.
Sebagai data scientist, Anda perlu memahami tipe data karena akan membantu Anda menggunakan fungsi database dengan tepat dan menulis kueri secara akurat. Ada tipe data untuk setiap jenis kolom dalam database seperti nama orang, teks, angka, gambar yang disimpan di database, dan seterusnya.
Di sini akan ditampilkan tipe data dasar untuk Oracle Server, SQL Server, dan MySQL Server:
Bacaan lebih lanjut:
SQL dan Pelaporan Data
Untuk semua SQL yang digunakan dalam tutorial ini, skema database contoh berikut:
Pertimbangkan database dengan dua tabel bernama emp yang menyimpan data karyawan dan tabel dept yang menyimpan catatan tentang departemen.
Tabel emp memiliki nomor karyawan (empno), nama karyawan (ename), gaji (sal), komisi (comm), nama pekerjaan (job), id manajer (mgr), tanggal perekrutan (hiredate), dan nomor departemen (deptno). Karena Manajer juga seorang karyawan dan memiliki nomor karyawan, maka mgr adalah salah satu empno yang pekerjaannya "MANAGER".
Tabel dept memiliki nomor departemen (deptno), nama departemen (dname) dan lokasi departemen (loc).
Perhatikan bahwa database yang berbeda memiliki format tanggal berbeda. Yang digunakan di sini, DD-MON-YY adalah format tanggal bawaan untuk database Oracle. Format bawaan untuk Microsoft SQL Server dan MySQL adalah YYYY-MM-DD.
Tabel database Anda mungkin berbeda, jadi Anda hanya perlu menyesuaikan sesuai nama tabel dan nama atribut Anda. Dalam tutorial ini, Anda hanya akan membaca dari database dan tidak menulis, memperbarui, atau membuat tabel dan objek baru. Jadi tidak perlu khawatir ada kehilangan atau perubahan data!
Informasi: Skema adalah kumpulan objek seperti tabel, fungsi, prosedur, view yang dimiliki oleh seorang pengguna
Dalam skema database di atas, Anda dapat melihat bahwa tabel emp memiliki enam atribut untuk entitas Employee. Tabel dept memiliki tiga atribut untuk entitas Department.
Mengambil Data Menggunakan Pernyataan SELECT
Pernyataan SELECT mengambil informasi dari database. Dengan menggunakan SELECT, Anda dapat melakukan hal berikut:
1. Proyeksi: Anda dapat menggunakan kemampuan proyeksi di SQL untuk memilih kolom dalam tabel yang ingin dikembalikan oleh kueri Anda. Anda dapat memilih sedikit atau sebanyak kolom yang Anda perlukan.
2. Seleksi: Anda dapat menggunakan kemampuan seleksi di SQL untuk memilih baris dalam tabel yang ingin dikembalikan oleh kueri.
Anda dapat menggunakan berbagai kriteria untuk membatasi baris yang Anda lihat.
3. Penggabungan: Anda dapat menggunakan kemampuan join di SQL untuk menyatukan data yang disimpan di tabel berbeda dengan membuat tautan di antara keduanya.
Pernyataan SELECT dasar memungkinkan Anda menentukan kolom apa yang Anda inginkan dari tabel mana. Klausa SELECT menentukan kolom, ekspresi dan klausa FROM menentukan dari tabel mana mengambil data.
Misalnya, Apa nama semua karyawan dan apa pekerjaan mereka: SELECT ename, job FROM employee; SQL di atas akan memberi keluaran berikut berdasarkan skema database yang dibahas di atas:
| ename | job |
|---|---|
| A | Salesman |
| B | Manager |
| C | Manager |
Untuk memilih semua atribut dan semua baris dari tabel digunakan operator *:
SELECT *
FROM employee;
The output will be:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
Beberapa kiat untuk menulis pernyataan SQL:
- Pernyataan SQL tidak peka huruf besar/kecil
- Klausa ditulis pada baris terpisah untuk meningkatkan keterbacaan
- Anda dapat menulis pernyataan SQL pada satu atau lebih baris
Anda juga dapat membuat ekspresi menggunakan operator +,-,/,* pada data tanggal dan angka untuk menghasilkan data yang dibutuhkan. Misalnya, Anda diminta mencari 20% dari gaji semua karyawan. Kuerinya sebagai berikut:
SELECT ename, sal*(20/100)
FROM employee;
Output:
ENAME SAL*(20/100)
---------- ------------
SMITH 160
ALLEN 320
WARD 250
JONES 595
BLAKE 570
Informasi:
- Tanda kurung digunakan untuk memperjelas ekspresi
- Pembagian dan perkalian memiliki prioritas di atas penjumlahan dan pengurangan
- Jika prioritas yang sama terjadi maka ekspresi dievaluasi dari kiri->kanan
Nilai NULL diperlakukan berbeda dalam database. Nilai NULL menyiratkan nilai tidak diketahui. Operasi apa pun yang dilakukan dengan NULL menghasilkan NULL. Database berbeda memiliki fungsi berbeda untuk menangani null. Ada fungsi umum yang digunakan di MySQL, Microsoft SQL Server, dan Oracle untuk memperlakukan NULL.
SELECT ename, sal+COALESCE(comm,0)
FROM employee;
Output:
ENAME SAL+COALESCE(COMM,0)
---------- --------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
BLAKE 2850
Alias Kolom dan Konkatenasi
Anda dapat melihat pada keluaran di atas, nama kolom sama dengan field database atau ekspresi yang Anda pilih. Terkadang, saat membuat laporan Anda ingin memberi nama header sendiri. Ini dapat dilakukan menggunakan alias.
SELECT ename AS "Emp Name", sal*(20/100) as "20% of Salary"
FROM employee;
Output:
Emp Name 20% of Salary
---------- ------------
SMITH 160
ALLEN 320
WARD 250
JONES 595
BLAKE 570
Saat Anda memberikan alias yang memiliki spasi di dalamnya, Anda harus menggunakan tanda kutip ganda. Jika tidak, tidak perlu menaruh tanda kutip ganda setelah AS. Terkadang Anda juga dapat menghilangkan kata kunci AS.
Anda dapat memformat keluaran dengan konkatenasi. Anda dapat menambahkan kalimat Anda sendiri pada keluaran menggunakan fungsi CONCAT atau operator seperti || atau + tergantung pada vendor database:
- Oracle mendukung CONCAT() dan || tetapi CONCAT() hanya menerima dua argumen. Anda harus menggunakan CONCAT() bersarang.
- MySQL menggunakan CONCAT()
- Microsoft SQL Server menggunakan operator '+' dan CONCAT().
Oracle: SELECT '20% of salary of '||ename||' is '||sal*(20/100) as "20% of salary" FROM employee;
SELECT CONCAT(CONCAT('20% of salary of',ename),CONCAT(' is ',sal(20/100))) as "20% of salary" FROM employee; MySQL dan Microsoft SQL Server: SELECT CONCAT('20% of salary of ',ename,' is ',sal(20/100)) as "20% of salary" FROM employee; Semuanya akan menghasilkan keluaran yang sama:
20% of salary
-----------------------------------------------------------------------
20% of salary of SMITH is 160
20% of salary of ALLEN is 320
20% of salary of WARD is 250
20% of salary of JONES is 595
20% of salary of BLAKE is 570
Menghilangkan Baris Berulang dengan DISTINCT
SELECT deptno
FROM employee;
Above query will result in:
DEPTNO
----------
20
30
30
20
30
Di sini deptno 20 dan 30 berulang. Anda dapat menghilangkan baris seperti itu dengan menggunakan kata kunci DISTINCT pada klausa SELECT.
SELECT distinct ename, deptno, job
FROM employee;
DEPTNO
----------
30
20
Membatasi dan Mengurutkan Data
Klausa WHERE digunakan untuk memfilter data berdasarkan suatu kondisi.
Cari semua karyawan yang memiliki pekerjaan CLERK:
SELECT ename, job
FROM employee
WHERE job='CLERK';
ENAME JOB
---------- ---------
SMITH CLERK
Anda mungkin ingin memfilter hasil dengan kondisi berbeda. Untuk ini digunakan operator, simbol kondisional, dan kata kunci khusus:
Penggunaan =,<>,!=,>=,<=,>,< sederhana seperti diperlihatkan pada kueri di atas yang menggunakan =.
Sintaks AND & OR: SELECT column1, column2,.. FROM table_name WHERE condition1 AND condition2 AND condition 3...;
SELECT column1, column2,.. FROM table_name WHERE condition1 OR condition2 OR condition 3...; Cari nama karyawan yang pekerjaannya MANAGER dan berasal dari departemen 30:
SELECT ename
FROM employee
WHERE job='MANAGER' AND deptno=30;
ENAME
----------
BLAKE
SELECT ename
FROM employee
WHERE job='MANAGER' OR deptno=30;
ENAME
----------
ALLEN
WARD
JONES
BLAKE
Sintaks NOT: SELECT column1, column2, ... FROM table_name WHERE NOT condition; Cari semua karyawan yang pekerjaannya bukan SALESMAN:
SELECT ename, job
from employee
WHERE NOT job='SALESMAN';
ENAME JOB
---------- ---------
SMITH CLERK
JONES MANAGER
BLAKE MANAGER
Anda dapat membuat kondisi kompleks menggunakan ketiga operator AND, OR dan NOT. Prioritasnya adalah:
- NOT
- AND
- OR
Cari semua karyawan yang pekerjaannya bukan CLERK dan berasal dari departemen 20:
SELECT ename, job
from employee
WHERE NOT job='SALESMAN' AND sal>800;
ENAME JOB
---------- ---------
JONES MANAGER
BLAKE MANAGER
Di sini NOT dieksekusi terlebih dahulu dan setelah NOT, AND dievaluasi.
Operator kondisional lain yang berguna ditunjukkan di bawah:
BETWEEN...AND:
SELECT *
FROM employee
WHERE sal BETWEEN 1000 AND 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
LIKE:
LIKE menggunakan dua wildcard: persen % dan garis bawah _ untuk merepresentasikan banyaknya karakter dalam pola.
- % berarti nol, satu, atau banyak karakter
- %M%: Cocokkan string yang memiliki M di posisi mana pun
- M%: Cocokkan nilai yang memiliki M di awal
- %M: Cocokkan nilai yang memiliki M di akhir
- M%A: Dimulai dengan M dan diakhiri dengan A
Pola bersifat peka huruf besar/kecil.
- _ menentukan jumlah karakter tak diketahui sebelum atau sesudah karakter yang diketahui. Satu garis bawah adalah satu karakter.
- _r%: Cocokkan nilai yang memiliki r di posisi kedua.
Dapatkan nama semua karyawan yang namanya diawali huruf 'B':
SELECT *
FROM employee
WHERE ename LIKE 'B%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
Dapatkan nama semua karyawan yang namanya diawali 'A' dan memiliki 'E' di posisi mana pun setelah 'A':
SELECT *
FROM employee
WHERE ename LIKE 'A%E%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
IN(value1,value2, value3,..):
Fungsi IN() dapat menerima satu, dua, atau banyak nilai dan memungkinkan Anda mencocokkan sebuah kolom dengan nilai yang diberikan dalam tanda kurung pada klausa WHERE:
SELECT ename, job, hiredate
FROM employee
WHERE job IN ('CLERK','SALESMAN');
ENAME JOB HIREDATE
---------- --------- ---------
SMITH CLERK 17-DEC-80
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
Anda juga dapat menggunakan pernyataan SELECT dalam IN() yang akan mengembalikan beberapa nilai. Misalnya:
SELECT ename, job, hiredate
FROM employee
WHERE deptno IN (select deptno FROM department WHERE loc='CHICAGO');
ENAME JOB HIREDATE
---------- --------- ---------
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
BLAKE MANAGER 01-MAY-81
Pernyataan SELECT dalam IN() juga disebut subquery. Lebih banyak tentang subquery nanti dalam tutorial ini!
IS NULL:
IS NULL digunakan untuk memeriksa nilai NULL pada atribut tertentu. Misalnya, cari semua karyawan yang tidak memiliki komisi:
SELECT ename, job, sal
FROM employee
WHERE comm IS NULL;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JONES MANAGER 2975
BLAKE MANAGER 2850
Jika Anda ingin mendapatkan nama karyawan yang menerima komisi maka gunakan IS NOT NULL:
SELECT ename, job, sal, comm
FROM employee
WHERE comm IS NOT NULL;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
ALLEN SALESMAN 1600 300
WARD SALESMAN 1250 500
Untuk memfilter hasil menggunakan kolom tanggal, Anda harus menggunakan format tanggal bawaan. Jika ingin menggunakan format lain, fungsi tanggal perlu diterapkan yang akan Anda lihat nanti dalam tutorial ini. Cari semua karyawan yang direkrut setelah 21 Februari 1981:
(Di sini digunakan format tanggal bawaan Oracle)
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81';
ENAME JOB SAL COMM
---------- --------- ---------- ----------
WARD SALESMAN 1250 500
JONES MANAGER 2975
BLAKE MANAGER 2850
Ekspresi kondisional yang kompleks dapat dibuat dengan menggabungkan semua operator di atas, namun Anda harus berhati-hati dengan prioritas. Prioritas adalah urutan operator dievaluasi. Berikut aturan prioritas untuk berbagai database:
- Microsoft Transact-SQL operator precedence
- Oracle 10g condition precedence
- Oracle MySQL 9 operator precedence
- PostgreSQL operator Precedence
- SQLite operator Precedence
Ada dua fungsi berguna: ANY(), ALL() yang dapat digunakan dalam kondisi. Misalnya:
SELECT ename, job, sal, comm FROM employee WHERE deptno=ANY(SELECT deptno from dept WHERE loc='NEW YORK');
SELECT ename, job, sal, comm FROM employee WHERE deptno=ALL(SELECT deptno from dept WHERE dname='SALES');
Perhatikan penggunaan subquery pada contoh di atas. Anda akan mempelajari lebih lanjut tentang subquery nanti.
Mengurutkan Hasil Menggunakan Klausa ORDER BY
Anda dapat mengurutkan hasil naik (ASC) atau turun (DESC) berdasarkan atribut apa pun atau beberapa atribut tabel. Anda juga dapat mengurutkan berdasarkan alias yang Anda tentukan di klausa SELECT:
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal desc;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
WARD SALESMAN 1250 500
Catatan: Urutan bawaan adalah naik yang dilambangkan dengan ASC, dan Anda tidak perlu menyertakan ASC.
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
WARD SALESMAN 1250 500
BLAKE MANAGER 2850
JONES MANAGER 2975
Anda dapat menentukan beberapa kolom dalam klausa ORDER BY yang akan dijalankan sesuai urutan kolom yang ditentukan. Misalnya, urutkan karyawan terlebih dahulu berdasarkan deptno naik lalu nama turun:
SELECT ename, job, sal, comm, deptno
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY deptno ASC, ename DESC;
ENAME JOB SAL COMM DEPTNO
---------- --------- ---------- ---------- ----------
JONES MANAGER 2975 20
WARD SALESMAN 1250 500 30
BLAKE MANAGER 2850 30
Menggunakan Fungsi Single-Row untuk Menyesuaikan Keluaran
Ada banyak fungsi di semua RDBMS yang membantu Anda melakukan tugas umum seperti mendapatkan panjang string, menggabungkan string, fungsi pemformatan, fungsi matematika, dll. Ada dua jenis fungsi baris di SQL:
- Fungsi Single-Row
- Fungsi Multi-Row
Fungsi Single-Row:
Fungsi ini diterapkan pada setiap baris dan mengembalikan hasil per baris. CONCAT() adalah fungsi manipulasi karakter single-row. Ini dapat digunakan di klausa SELECT, WHERE, dan ORDER BY. Terdapat fungsi single-row berikut di semua RDBMS:
- Fungsi Manipulasi Karakter
- Fungsi Tanggal dan Waktu
- Fungsi Angka
- Fungsi Konversi
Database yang berbeda memiliki nama fungsi berbeda untuk fungsi yang sama. Di sini saya akan memberi tahu fungsi umum yang berguna di Microsoft SQL Server, Oracle, dan MySQL. Beberapa sudah Anda pelajari di bagian atas. Di akhir bagian ini, Anda akan menemukan tautan ke daftar lengkap fungsi dari vendor tersebut untuk latihan lebih lanjut.
Mari kita bahas tiap kategori:
Fungsi Manipulasi Karakter
LOWER(): Mengonversi string menjadi huruf kecil.
SELECT lower(ename) as ename
FROM employee;
ENAME
----------
smith
allen
ward
jones
blake
UPPER(): Mengonversi string menjadi huruf besar.
SELECT upper(ename) as ename
FROM employee;
ENAME
----------
SMITH
ALLEN
WARD
JONES
BLAKE
SUBSTR()[Oracle, MySQL]: Mengembalikan substring sesuai spesifikasi SUBSTR(string, posisi-awal, panjang)
SUBSTRING()[SQL Server]: Mengembalikan substring sesuai spesifikasi SUBSTRING(string, posisi-awal, panjang).
SELECT SUBSTR(ename,2,3) as substr_ename FROM employee;
Cukup ganti nama fungsi menjadi SUBSTRING untuk SQL Server.
SUBSTR_ENAME ------------ MIT LLE ARD ONE LAK
LENGTH()[Oracle, MySQL]: Mengembalikan panjang string dalam tanda kurung
LEN()[SQL Server]: Mengembalikan panjang string dalam tanda kurung
SELECT LENGTH(ename) as len_ename FROM employee;
Cukup ganti nama fungsi menjadi LEN untuk SQL Server.
LEN_ENAME
----------
5
5
4
5
5
Fungsi seperti padding string ke kiri atau kanan, replace mulai berbeda dalam sintaks untuk tiap database. Daftar fungsi Karakter dan String untuk tiga vendor database:
Fungsi Angka
| Nama fungsi | Fungsi |
|---|---|
| ROUND(m,n): | Membulatkan nilai m ke n tempat desimal yang ditentukan. |
| ABS(m,n): | Mengembalikan nilai absolut sebuah angka |
| FLOOR(n): | FLOOR mengembalikan bilangan bulat terbesar yang sama dengan atau lebih kecil dari n. |
| MOD(m,n): | Mengembalikan sisa pembagian m oleh n (tidak tersedia di SQL Server namun menggunakan operator %: 35 % 6) |
Oracle:
SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24) from dual;
OUTPUT:
ROUND(45.926,2) MOD(11,5) FLOOR(34.4) ABS(-24)
--------------- ---------- ----------- ----------
45.93 1 34 24
MySQL:
SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24);
SQL Server:
SELECT ROUND(45.926,2) as round,11%5 as mod, FLOOR(34.4) as floor,ABS(-24) as abs;
Daftar fungsi Numerik untuk tiga vendor database:
Fungsi Konversi
Fungsi Konversi digunakan untuk mengonversi satu tipe data ke tipe data lain. Fungsi konversi berbeda antar server. Di sini saya akan memberi Anda contoh kueri Oracle yang menggunakan fungsi to_char. Ini akan membantu Anda memahami bagaimana menggunakan fungsi konversi untuk menyesuaikan keluaran. Format tanggal bawaan Oracle adalah DD-MON-YY
SELECT ename, to_char(hiredate,'DD, MONTH YYYY') as Hiredate, to_char(hiredate,'DY') as Day
from employee;
ENAME Hiredate DAY
---------- -------------------------------------------- ------------
SMITH 17,DECEMBER 1980 WED
ALLEN 20,FEBRUARY 1981 FRI
WARD 22,FEBRUARY 1981 SUN
JONES 02,APRIL 1981 THU
BLAKE 01,MAY 1981 FRI
Ada banyak fungsi konversi untuk setiap server database. Untuk bacaan dan eksplorasi lebih lanjut, berikut tautan ke fungsi konversi server database yang berbeda:
- Microsoft SQL Server conversion functions
- Oracle server conversion functions
- MySQL conversion functions
Fungsi Tanggal dan Waktu
Fungsi tanggal dan waktu memungkinkan Anda memanipulasi tanggal dengan menambah hari, menghitung bulan antara dua tanggal, dll. Fungsi seperti ini bermanfaat saat membuat laporan. Fungsi tanggal juga bervariasi antar database. Sebuah fungsi dapat melakukan fungsi yang sama tetapi mungkin memiliki nama berbeda di database lain. Di sini Anda akan melihat beberapa contoh dan kemudian saya akan memberikan tautan ke fungsi tanggal dari database yang paling umum: Oracle, SQL Server, dan MySQL. Silakan coba contoh-contoh ini di server database Anda!
- Oracle Date and Time functions
- MySQL Date and Time functions
- Microsoft SQL Server Date and Time functions
Mengelompokkan Hasil SQL Anda
Fungsi pengelompokan atau Fungsi Multi-Row diterapkan pada grup dan mengembalikan satu hasil per grup. Anda akan memerlukan fungsi grup ketika ingin mengetahui fakta seperti total penjualan per kuartal, harga rata-rata produk selama periode waktu tertentu, investasi tertinggi yang diterima perusahaan Anda bulan ini, dan sebagainya.
Melaporkan Data Teragregasi Menggunakan Fungsi Grup
Klausa GROUP BY digunakan untuk mengelompokkan hasil yang sering digunakan dengan fungsi seperti COUNT, MAX, MIN, AVG, dan SUM. Sintaks GROUP BY adalah:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
Anda harus mengingat beberapa poin untuk klausa GROUP BY:
- Nama alias tidak dapat digunakan dalam klausa GROUP BY
- Klausa WHERE selalu muncul sebelum GROUP BY
- Klausa HAVING muncul setelah GROUP BY dan akan menerapkan kondisi pada fungsi grup.
- Klausa ORDER BY selalu muncul terakhir
COUNT adalah fungsi grup yang menghitung jumlah baris data sesuai kondisi atau tanpa kondisi. Fungsi COUNT mengabaikan nilai null.
Hitung semua karyawan yang namanya diawali huruf 'A':
SELECT count(ename)
FROM employee
WHERE ename LIKE 'A%';
Output:
COUNT(ENAME)
------------
1
Cari total gaji, gaji rata-rata, gaji minimum, dan gaji maksimum di tabel employee:
SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL"
FROM employee;
TOTAL SAL AVG SAL MIN SAL MAX SAL
---------- ---------- ---------- ----------
9475 1895 800 2975
Anda mungkin ingin mengelompokkan hasil di atas untuk setiap departemen:
SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL", deptno
FROM employee
GROUP BY deptno;
TOTAL SAL AVG SAL MIN SAL MAX SAL DEPTNO
---------- ---------- ---------- ---------- ----------
5700 1900 1250 2850 30
3775 1887.5 800 2975 20
Anda juga dapat menghitung Varians dan Simpangan Baku menggunakan fungsi berikut:
| Untuk Oracle dan MySQL | MS SQL SERVER |
|---|---|
| SELECT STDDEV(column_name) FROM table_name; | SELECT STDEV(column_name) FROM table_name; |
| SELECT VARIANCE(column_name) FROM table_name; | SELECT VAR(column_name) FROM table_name; |
Anda dapat mengelompokkan hasil berdasarkan lebih dari satu kolom.
Cari jumlah gaji tiap pekerjaan dalam setiap departemen:
SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job;
TOTAL SAL DEPTNO JOB
---------- ---------- ---------
800 20 CLERK
2850 30 SALESMAN
2975 20 MANAGER
2850 30 MANAGER
Anda dapat membatasi hasil grup menggunakan klausa HAVING. Ini hanya digunakan untuk kondisi pada grup.
Catatan: Jangan gunakan WHERE untuk menerapkan kondisi pada fungsi grup.
SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job
HAVING sum(sal)>1000
ORDER BY sum(sal);
TOTAL SAL DEPTNO JOB
---------- ---------- ---------
2850 30 SALESMAN
2850 30 MANAGER
2975 20 MANAGER
Menampilkan Data dari Beberapa Tabel
Pada bagian ini join berikut akan dibahas:
- Cartesian product/Cross join
- Inner join/EquiJoins
- Natural joins
- Outer Joins (Left, Right, Full)
- Self Join
Anda akan perlu menampilkan laporan yang mengambil data dari beberapa tabel. Lihat contoh berikut:
Untuk menghasilkan laporan seperti di atas, Anda perlu menautkan tabel employee dan department dan mengambil data dari keduanya. Untuk ini digunakan Join di SQL.
Cartesian Product/Cross Join:
Cartesian product terbentuk ketika setiap tuple dari relasi R membentuk kombinasi dengan setiap tuple di relasi S.
Operasi cartesian product yang juga disebut Cross Join mengalikan dua tabel untuk membentuk relasi yang terdiri dari semua pasangan tuple yang mungkin dari dua tabel. Jika satu relasi, sebut R, memiliki I tuple dengan M atribut dan relasi lain, sebut S, memiliki J tuple dengan N atribut maka cartesian product akan memiliki IxJ tuple dengan M+N atribut. Cartesian product juga dapat dibentuk menggunakan CROSS JOIN yang sesuai SQL.
SELECT empno, ename, dname
FROM employee, department;
OR
SELECT empno, ename, dname
FROM employee CROSS JOIN department;
EMPNO ENAME DNAME
---------- ---------- --------------
7369 SMITH ACCOUNTING
7499 ALLEN ACCOUNTING
7521 WARD ACCOUNTING
7566 JONES ACCOUNTING
7698 BLAKE ACCOUNTING
7369 SMITH RESEARCH
7499 ALLEN RESEARCH
7521 WARD RESEARCH
7566 JONES RESEARCH
7698 BLAKE RESEARCH
7369 SMITH SALES
EMPNO ENAME DNAME
---------- ---------- --------------
7499 ALLEN SALES
7521 WARD SALES
7566 JONES SALES
7698 BLAKE SALES
15 baris terpilih.
Karena tabel employee terdiri dari 5 tuple dan department berisi 3 tuple, maka cartesian product memiliki 5x3=15 baris. Cartesian product dihasilkan ketika:
- Kondisi join tidak digunakan
- Kondisi join tidak valid atau tidak dibentuk dengan benar
Ketika data dari dua atau lebih tabel diperlukan, kondisi join dibuat menggunakan atribut umum yang menautkan tabel yang terlibat. Ini paling umum adalah Primary Key dan Foreign Key.
Cartesian product digunakan untuk simulasi sejumlah besar data untuk tujuan pengujian.
*Inner Join/EquiJoin:
Inner Join atau Equijoin (istilah yang digunakan oleh pengembang Oracle) menggunakan relasi primary dan foreign key untuk menggabungkan dua atau lebih tabel:
SELECT ename, dname
FROM employee e,department d
WHERE e.deptno=d.deptno;
OR
SELECT ename, dname
FROM employee e
JOIN department d
ON e.deptno=d.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
BLAKE SALES
Pada kueri di atas, deptno adalah primary key di tabel department dan foreign key di tabel employee. Anda dapat menambahkan kondisi tambahan menggunakan operasi logis seperti dibahas di bagian sebelumnya.
Poin yang perlu diingat untuk JOIN:
- Jika nama kolom yang sama muncul di lebih dari satu tabel, nama kolom harus diawali dengan nama tabel. Ini juga merupakan praktik yang baik untuk meningkatkan kejelasan.
- Untuk menggabungkan n tabel, Anda memerlukan minimal n-1 kondisi join. Misalnya, untuk menggabungkan empat tabel, minimal tiga join diperlukan.
Alias tabel: Alias tabel digunakan seperti pada FROM employee e, department d di mana e dan d adalah alias tabel, untuk memudahkan engine database mengidentifikasi dari tabel mana sebuah kolom berasal terutama ketika kolom yang sama ada di lebih dari satu tabel yang terlibat karena jika tidak akan muncul error untuk kolom ambigu. Dan ini menghemat waktu dari menulis nama tabel panjang berulang kali.
Anda juga dapat membuat nonequi-join yang menggabungkan tabel berdasarkan kondisi selain kesetaraan. Pertimbangkan tabel lain salgrade yang berisi informasi tentang grade karyawan berdasarkan gaji:
Anda ingin mengetahui grade tiap karyawan berdasarkan gaji dan grade disimpan di tabel salgrade sementara gaji disimpan di tabel employee:
SELECT e.ename, e.sal, s.grade
FROM employee e, salgrade s
WHERE e.sal between s.losal AND s.hisal;
ENAME SAL GRADE
---------- ---------- ----------
JONES 2975 4
BLAKE 2850 4
ALLEN 1600 3
WARD 1250 2
SMITH 800 1
Contoh: Anda diminta memberikan nama karyawan, gaji, grade, dan nama departemen. Sekarang, seperti Anda tahu, gaji disimpan di tabel employee, grade di tabel salgrade dan nama departemen di tabel department, Anda perlu menggabungkan tiga tabel:
SELECT e.ename, e.sal, d.dname, s.grade FROM employee e, department d, salgrade s WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal; Output: ENAME SAL DNAME GRADE ---------- ---------- -------------- ---------- JONES 2975 RESEARCH 4 BLAKE 2850 SALES 4 ALLEN 1600 SALES 3 WARD 1250 SALES 2 SMITH 800 RESEARCH 1
Pada contoh di atas, tiga tabel terlibat dan dua kondisi join, salah satunya nonequi-join.
Natural Join:
Natural join memungkinkan database menggabungkan tabel secara otomatis dengan mencocokkan kolom yang memiliki nama sama. Jika kolom cocok dengan nama yang sama tetapi tipe data berbeda, maka akan muncul error.
Sintaks: SELECT FROM table1 NATURAL JOIN table2; SELECT FROM employee NATURAL JOIN department; Karena natural join sendiri mencari kolom yang cocok, ia dapat menemukan lebih dari satu kolom cocok dengan nama yang sama namun mungkin memiliki tipe data berbeda yang dapat menghasilkan error. Jadi klausa USING digunakan untuk menentukan kolom pada mana membuat equi join.
Catatan: NATURAL JOIN dan USING adalah dua klausa berbeda dan digunakan terpisah. Salah satunya tidak dapat digunakan ketika yang lain hadir (saling eksklusif).
SELECT e.ename, d.dname, e.sal FROM employee e JOIN department d USING (deptno) WHERE deptno=20; output: ENAME DNAME SAL ---------- -------------- ---------- SMITH RESEARCH 800 JONES RESEARCH 2975
Kolom dalam klausa USING tidak dapat menggunakan nama tabel atau prefiks tabel di mana pun dalam pernyataan SQL. Misalnya berikut ini salah:
SELECT e.ename, d.dname, e.sal FROM employee e JOIN department d USING (d.deptno) WHERE d.deptno=20;
d.deptno tidak benar. Hanya deptno yang boleh digunakan. Outer Join:
Ada tiga outer join:
- Left Outer Join: Join antara dua tabel yang mengembalikan hasil inner join, serta baris yang tidak cocok dari tabel kiri, disebut LEFT OUTER JOIN
- Right Outer Join: Join antara dua tabel yang mengembalikan hasil inner join, serta baris yang tidak cocok dari tabel kanan, disebut RIGHT OUTER JOIN.
- Full Outer Join: Join antara dua tabel yang mengembalikan hasil inner join, serta baris yang tidak cocok dari kedua sisi.
Mari lihat satu per satu:
SELECT e.ename, s.grade FROM salgrade s LEFT OUTER JOIN employee e ON e.sal BETWEEN s.losal AND s.hisal; Output: ENAME DEPTNO DNAME ---------- ---------- -------------- SMITH 20 RESEARCH JONES 20 RESEARCH ALLEN 30 SALES WARD 30 SALES BLAKE 30 SALES.sal BETWEEN s.losal AND s.hisal; Output: ENAME DEPTNO DNAME ---------- ---------- -------------- SMITH 20 RESEARCH JONES 20 RESEARCH ALLEN 30 SALES WARD 30 SALES BLAKE 30 SALESSELECT e.ename, d.deptno, d.dname
FROM employee e RIGHT OUTER JOIN department d
ON e.deptno=d.deptno;
output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
BLAKE 30 SALES
10 ACCOUNTINGSELECT e.ename, d.deptno, d.dname
FROM employee e FULL OUTER JOIN department d
ON e.deptno=d.deptno;
Output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
BLAKE 30 SALES
10 ACCOUNTING
Tabel department memiliki satu baris yang tidak cocok untuk deptno 10, dan tidak ditemukan baris yang tidak cocok untuk tabel employee. Demikian pula, salgrade memiliki satu baris yang tidak cocok untuk grade 5.
Self-Join:
Ada situasi ketika Anda ingin menggabungkan sebuah tabel dengan dirinya sendiri. Misalnya, tabel employee berisi informasi tentang semua karyawan termasuk manajer (Lihat tabel employee di atas). Jika Anda diminta mencari karyawan dan manajernya, maka Anda perlu menggabungkan tabel dengan dirinya sendiri. Self-Join dibuat dengan cara berikut:
SELECT e.ename as Employee, m.ename as Manager
FROM employee e, employee m
WHERE e.mgr=m.empno;
EMPLOYEE MANAGER
---------- ----------
ALLEN BLAKE
WARD BLAKE
Menggunakan Subquery untuk Menyelesaikan Kueri
Subquery adalah kueri di dalam kueri. Subquery berguna untuk memecah kueri besar menjadi segmen. Subquery dapat dinest dan dapat digunakan pada klausa SQL berikut:
- WHERE
- FROM
- HAVING
Pertimbangkan contoh; Anda diminta mencari karyawan yang gajinya lebih tinggi daripada karyawan lain, dalam hal ini James. Bagaimana Anda mendekati ini? Kueri ini dapat dipecah menjadi:
- Temukan gaji James
- Bandingkan gaji James dengan semua karyawan
Ini dapat diselesaikan menggunakan subquery. Subquery (inner query) akan dieksekusi sekali sebelum kueri utama (outer query).
SELECT empno, ename FROM employee WHERE sal>(SELECT sal from employee where ename='JAMES');
Jenis Subquery:
-
Subquery single-row: Kueri yang hanya mengembalikan satu baris dari pernyataan SELECT di dalam.
-
Subquery multiple-row: Kueri yang mengembalikan lebih dari satu baris dari pernyataan SELECT di dalam.
Poin yang perlu diingat:
- Letakkan subquery dalam tanda kurung.
- Tempatkan subquery di sebelah kanan operator perbandingan.
- Gunakan operator single-row (>, < , >= , <= , < >) dengan subquery single-row.
- Gunakan operator multiple-row (IN, ANY, ALL) dengan subquery multiple-row.
Subquery Single-row:
Anda diminta mencari nama karyawan yang jabatan pekerjaannya sama dengan empno 7521
SELECT ename, job
FROM employee
WHERE job=(SELECT job FROM employee WHERE empno=7521);
ENAME JOB
---------- ---------
ALLEN SALESMAN
WARD SALESMAN
Pilih gaji maksimum departemen di mana gaji maksimum lebih besar atau sama dengan gaji maksimum departemen 20
SELECT deptno, max(sal)
FROM employee
GROUP BY deptno
HAVING max(sal)>=(SELECT max(sal) FROM employee WHERE deptno=20);
DEPTNO MAX(SAL)
---------- ----------
20 2975
Subquery Multiple-row:
Cari karyawan yang gajinya sama dengan salah satu gaji karyawan yang memiliki pekerjaan sebagai Manager. Ini akan menjadi subquery multiple-row karena subquery dapat mengembalikan lebih dari satu baris.
SELECT ename, job, sal
FROM employee
WHERE sal IN (SELECT sal FROM employee WHERE job='MANAGER');
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
Saat subquery digunakan dalam klausa FROM, ia bertindak sebagai tabel sementara yang tidak secara fisik ada di penyimpanan tetapi merupakan tampilan data. Misalnya:
SELECT e.ename, e.job, e.sal
FROM employee e, (SELECT deptno FROM department WHERE loc='DALLAS') d
WHERE e.deptno=d.deptno;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JONES MANAGER 2975
Menggunakan Operator Himpunan (SET)
Dalam SQL operator SET digunakan untuk menggabungkan hasil dari beberapa kueri menjadi satu hasil. Ini menggunakan teori himpunan dalam matematika dan operasinya seperti UNION, MINUS, INTERSECT. Di sini Anda akan melihat bagaimana menggunakan operator SET untuk mengoptimalkan kueri. Dalam tutorial ini operator SET berikut akan dibahas.
Informasi: Kueri yang menggunakan operator SET disebut pernyataan majemuk (compound).
- UNION dan UNION ALL
- INTERSECT
- EXCEPT (standar SQL) dan MINUS (khusus Oracle)
UNION
Pertimbangkan dua relasi (tabel) R dan S maka UNION memilih semua baris dari R dan semua baris dari S dengan menghilangkan duplikat. Jumlah baris yang dikembalikan maksimum r+s di mana r adalah jumlah baris di R dan s adalah jumlah baris di S.
Pilih semua nama departemen termasuk nama departemen karyawan yang direkrut sebelum 23-OCT-1999
SELECT dname
FROM department
UNION
SELECT dname
FROM department, employee
WHERE department.deptno=employee.deptno AND employee.hiredate<to_date('23-OCT-1999');
DNAME
--------------
ACCOUNTING
OPERATIONS
RESEARCH
SALES
Poin yang perlu diingat untuk UNION:
- Jumlah kolom dan tipe data kolom yang dipilih harus identik di semua pernyataan SELECT yang digunakan dalam kueri.
- Nama kolom tidak harus identik.
- Keluaran diurutkan naik berdasarkan kolom pertama dari klausa SELECT.
- Nilai NULL tidak diabaikan selama pemeriksaan duplikat.
UNION ALL menggabungkan hasil dari satu atau lebih kueri dan TIDAK menghapus duplikat, sehingga kata kunci DISTINCT tidak dapat digunakan. Pertimbangkan tabel lain emp yang berisi karyawan dari tahun 2000. Ambil semua nomor karyawan, nama, dan pekerjaan dari tabel emp dan employee:
SELECT empno, ename, job
FROM employee
UNION ALL
SELECT empno, ename, job
FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
EMPNO ENAME JOB
---------- ---------- ---------
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
INTERSECT
INTERSECT mengikuti aturan yang sama seperti dalam teori himpunan, untuk memberikan nilai umum dari dua himpunan. Pertimbangkan dua relasi R dan S maka INTERSECT akan mengembalikan himpunan semua tuple yang umum di R dan S.
SELECT empno, ename, job
FROM employee
INTERSECT
SELECT empno, ename, job
FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
Poin yang perlu diingat untuk INTERSECT:
- Jumlah kolom dan tipe data kolom yang dipilih harus identik di semua pernyataan SELECT yang digunakan dalam kueri.
- Nama kolom tidak harus identik.
- INTERSECT tidak mengabaikan nilai NULL
EXCEPT dan MINUS:
EXCEPT (digunakan di SQL Server) dan MINUS (khusus Oracle) keduanya memiliki fungsi yang sama. Fungsinya adalah memilih semua baris berbeda yang dipilih oleh kueri pertama tetapi tidak oleh kueri kedua. Untuk memahami operator ini, pertimbangkan tabel berikut:
Katakanlah, Anda perlu mencari produk yang memiliki kuantitas antara 1 hingga 100 tetapi tidak memiliki kuantitas 50–75. Di sini Anda akan menggunakan EXCEPT jika menggunakan SQL Server dan MINUS jika menggunakan Oracle.
SELECT prod_name, qty FROM products WHERE qty BETWEEN 1 AND 100 EXCEPT SELECT prod_name, qty FROM products WHERE qty BETWEEN 50 AND 75;
Untuk Oracle:
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 1 AND 100
MINUS
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 50 AND 75;
PROD_NAME QTY
------------- ----------
COLGATE 1
SENSODYNE 100
SENSODYNE TOOTHBRUSH 30
Poin yang perlu diingat untuk EXCEPT/MINUS:
- Jumlah kolom dan tipe data kolom yang dipilih harus identik di semua pernyataan SELECT yang digunakan dalam kueri.
- Nama kolom tidak harus identik.
- Semua kolom dalam klausa WHERE harus ada di klausa SELECT agar operator MINUS berfungsi.
Selamat!
Ini adalah akhir dari tutorial. Dalam tutorial ini, Anda telah mempelajari banyak hal tentang SQL yang akan membantu Anda menguasai SQL dalam perjalanan data science Anda. SQL digunakan untuk menghasilkan laporan dari database. Anda telah mempelajari fondasi database dan SQL, tipe data umum yang digunakan, fungsi yang membantu Anda memformat laporan, agregasi hasil untuk membuat ringkasan, serta cara mengumpulkan data dari berbagai tabel di database sesuai kebutuhan Anda! Tutorial ini dirancang khusus untuk pembelajar data science yang tidak hanya akan membantu mereka dengan database relasional tetapi juga berjalan mulus melalui database NoSQL dan bagaimana menggunakan keterampilan SQL dalam studi Big Data.
Jika Anda ingin mempelajari lebih lanjut tentang SQL, ikuti kursus gratis DataCamp Intro to SQL for Data Science.
Referensi
Topik
