SQL Simple
Mari kita belajar SQL ( secara umum baik mysql, sql server,
oracle) :
Data Table ( yang penulis gunakan adalah table kelas dan student
) :
Student
|
|||||
No.
|
NIS
|
Nama
|
Alamat
|
Umur
|
Kelas
|
1
|
20170001
|
Aldo Berton
|
Jl. Duri Selatan
|
8
|
K2
|
2
|
20170002
|
Angel Christina
|
Jl. Petojo Barat
|
7
|
K1
|
3
|
20170003
|
Bella Lorenza
|
Jl. Petojo Bara
|
8
|
K2
|
4
|
20170004
|
Charles
|
Jl. Metro Marini Barat
|
7
|
K1
|
5
|
20170005
|
Davin
|
Jl. Petojo Utara
|
10
|
K4
|
6
|
20170006
|
Donna
|
Jl. Jelambar Jaya
|
10
|
K4
|
7
|
20170007
|
Dorothy
|
Jl. Tanah Sereal
|
11
|
K5
|
8
|
20170008
|
Dova Ariella
|
Jl. Hanura I
|
12
|
K6
|
9
|
20170009
|
Evita
|
Jl. Krendang Raya
|
11
|
K5
|
10
|
20170010
|
Grace Florence
|
Jl. Duri Selatan
|
13
|
K6
|
kelas
|
||
No
|
Kode
|
Nama
|
1
|
K1
|
Kelas 1
|
2
|
K2
|
Kelas 2
|
3
|
K3
|
Kelas 3
|
4
|
K4
|
Kelas 4
|
5
|
K5
|
Kelas 5
|
6
|
K6
|
Kelas 6
|
==============================================
Untuk mengakses data pada table :
1. untuk
menampilkan semua data dan field :
select * from kelas;
+——+———+
| Kode | Nama |
+——+———+
| K1 | Kelas 1 |
| K2 | Kelas 2 |
| K3 | Kelas 3 |
| K4 | Kelas 4 |
| K5 | Kelas 5 |
| K6 | Kelas 6 |
+——+———+
| Kode | Nama |
+——+———+
| K1 | Kelas 1 |
| K2 | Kelas 2 |
| K3 | Kelas 3 |
| K4 | Kelas 4 |
| K5 | Kelas 5 |
| K6 | Kelas 6 |
+——+———+
select * from student;
+———-+—————–+————————+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+—————–+————————+——+——-+
| 20170001 | Aldo Berton | Jl. Duri Selatan | 8 | K2 |
| 20170002 | Angel Christina | Jl. Petojo Barat | 7 | K1 |
| 20170003 | Bella Lorenza | Jl. Petojo Bara | 8 | K2 |
| 20170004 | Charles | Jl. Metro Marini Barat | 7 | K1 |
| 20170005 | Davin | Jl. Petojo Utara | 10 | K4 |
| 20170006 | Donna | Jl. Jelambar Jaya | 10 | K4 |
| 20170007 | Dorothy | Jl. Tanah Sereal | 11 | K5 |
| 20170008 | Dova Ariella | Jl. Hanura I | 12 | K6 |
| 20170009 | Evita | Jl. Krendang Raya | 11 | K5 |
| 20170010 | Grace Florence | Jl. Duri Selatan | 13 | K6 |
+———-+—————–+————————+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+—————–+————————+——+——-+
| 20170001 | Aldo Berton | Jl. Duri Selatan | 8 | K2 |
| 20170002 | Angel Christina | Jl. Petojo Barat | 7 | K1 |
| 20170003 | Bella Lorenza | Jl. Petojo Bara | 8 | K2 |
| 20170004 | Charles | Jl. Metro Marini Barat | 7 | K1 |
| 20170005 | Davin | Jl. Petojo Utara | 10 | K4 |
| 20170006 | Donna | Jl. Jelambar Jaya | 10 | K4 |
| 20170007 | Dorothy | Jl. Tanah Sereal | 11 | K5 |
| 20170008 | Dova Ariella | Jl. Hanura I | 12 | K6 |
| 20170009 | Evita | Jl. Krendang Raya | 11 | K5 |
| 20170010 | Grace Florence | Jl. Duri Selatan | 13 | K6 |
+———-+—————–+————————+——+——-+
untuk menambahkan nomor urut ( contoh table kelas untuk table
lain sama);
*. mysql :
SET @rank=0;
SELECT @rank:=@rank+1 as nomor, a.* from kelas a;
atau
SELECT @rownum := @rownum + 1 as nomor, t.*
FROM kelas t,
(SELECT @rownum := 0) r
*. mysql :
SET @rank=0;
SELECT @rank:=@rank+1 as nomor, a.* from kelas a;
atau
SELECT @rownum := @rownum + 1 as nomor, t.*
FROM kelas t,
(SELECT @rownum := 0) r
*. sql server :
SELECT ROW_NUMBER() OVER(ORDER BY nama ASC) as nomor,
a.* FROM kelas;
*. oracle :
select rownum as nomor, a.* from kelas a;
hasil :
+——-+——+———+
| nomor | Kode | Nama |
+——-+——+———+
| 1 | K1 | Kelas 1 |
| 2 | K2 | Kelas 2 |
| 3 | K3 | Kelas 3 |
| 4 | K4 | Kelas 4 |
| 5 | K5 | Kelas 5 |
| 6 | K6 | Kelas 6 |
+——-+——+———+
| nomor | Kode | Nama |
+——-+——+———+
| 1 | K1 | Kelas 1 |
| 2 | K2 | Kelas 2 |
| 3 | K3 | Kelas 3 |
| 4 | K4 | Kelas 4 |
| 5 | K5 | Kelas 5 |
| 6 | K6 | Kelas 6 |
+——-+——+———+
============================================
2. untuk menampilkan di field tertentu :
dalam hal ini, hanya ingin menampilkan data pada kelas ( kode
atau nama ) dan pada student ( nis, nama ) dengan cuma hanya 3 record;
*. mysql :
SELECT @rownum := @rownum + 1 as nomor, t.kode FROM
kelas t, (SELECT @rownum := 0) r
where @rownum < 3;
SELECT @rownum := @rownum + 1 as nomor, t.nis, t.nama
FROM student t, (SELECT @rownum := 0) r
where @rownum < 3;
*. SQL Server
SELECT ROW_NUMBER() OVER(ORDER BY kode ASC) as nomor,
a.kode FROM kelas where ROW_NUMBER() < 3;
SELECT ROW_NUMBER() OVER(ORDER BY nis ASC) as nomor,
a.nis, a.nama FROM student where ROW_NUMBER() < 3;
*. Oracle :
select rownum, a.kode from kelas a where rownum < 3;
seelect rownum nomor, a.nis, a.nama from student where rownum
< 3;
Hasil :
*. pada table kelas :
+——-+——+
| nomor | kode |
+——-+——+
| 1 | K1 |
| 2 | K2 |
| 3 | K3 |
+——-+——+
| nomor | kode |
+——-+——+
| 1 | K1 |
| 2 | K2 |
| 3 | K3 |
+——-+——+
*. pada table student :
+——-+———-+—————–+
| nomor | nis | nama |
+——-+———-+—————–+
| 1 | 20170001 | Aldo Berton |
| 2 | 20170002 | Angel Christina |
| 3 | 20170003 | Bella Lorenza |
+——-+———-+—————–+
| nomor | nis | nama |
+——-+———-+—————–+
| 1 | 20170001 | Aldo Berton |
| 2 | 20170002 | Angel Christina |
| 3 | 20170003 | Bella Lorenza |
+——-+———-+—————–+
============================================
3. untuk mengakses data dengan kondisi ( yang sering digunakan,
dimana masing – masing database memiliki perbedaan, namun secara standard atau
umum sama ), penulis mencoba 1 table.
sesuaikan field yang dimau [ disini penulis menggunakan field kode
pada table kelas dan nis pada table student ]
*. menggunakan = & != atau
<> [ penulis utk sql ini menggunakan table kelas ]
select * from kelas where kode = ‘K1’;
+——+———+
| Kode | Nama |
+——+———+
| K1 | Kelas 1 |
| Kode | Nama |
+——+———+
| K1 | Kelas 1 |
select * from student where nis = ‘20170001’;
+———-+————-+——————+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+————-+——————+——+——-+
| 20170001 | Aldo Berton | Jl. Duri Selatan | 8 | K2 |
+———-+————-+——————+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+————-+——————+——+——-+
| 20170001 | Aldo Berton | Jl. Duri Selatan | 8 | K2 |
+———-+————-+——————+——+——-+
akan menampilkan semua data yang kode kelas K1′
select * from kelas where kode != ‘K1’;
atau
select * from kelas where kode <> ‘K1’;
+——+———+
| Kode | Nama |
+——+———+
| K2 | Kelas 2 |
| K3 | Kelas 3 |
| K4 | Kelas 4 |
| K5 | Kelas 5 |
| K6 | Kelas 6 |
+——+———+
| Kode | Nama |
+——+———+
| K2 | Kelas 2 |
| K3 | Kelas 3 |
| K4 | Kelas 4 |
| K5 | Kelas 5 |
| K6 | Kelas 6 |
+——+———+
akan menampilkan semua data yang bukan kode kelas K1′
*. menggunakan like & Not Like
untuk menggunakan like pada
nilai bisa ditambahkan %, baik depan, belakang, ataupun dihimpit; untuk lebih
jelas penulis mencoba table student dengan field nama;
select * from student where nama like ‘Aldo Berton’;
+———-+————-+——————+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+————-+——————+——+——-+
| 20170001 | Aldo Berton | Jl. Duri Selatan | 8 | K2 |
+———-+————-+——————+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+————-+——————+——+——-+
| 20170001 | Aldo Berton | Jl. Duri Selatan | 8 | K2 |
+———-+————-+——————+——+——-+
dengan kondisi like seperti di atas, nama dengan
menggunakan = jadi lebih baik gunakan =
select * from student where nama not like ‘Aldo Berton’;
+———-+—————–+————————+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+—————–+————————+——+——-+
| 20170002 | Angel Christina | Jl. Petojo Barat | 7 | K1 |
| 20170003 | Bella Lorenza | Jl. Petojo Bara | 8 | K2 |
| 20170004 | Charles | Jl. Metro Marini Barat | 7 | K1 |
| 20170005 | Davin | Jl. Petojo Utara | 10 | K4 |
| 20170006 | Donna | Jl. Jelambar Jaya | 10 | K4 |
| 20170007 | Dorothy | Jl. Tanah Sereal | 11 | K5 |
| 20170008 | Dova Ariella | Jl. Hanura I | 12 | K6 |
| 20170009 | Evita | Jl. Krendang Raya | 11 | K5 |
| 20170010 | Grace Florence | Jl. Duri Selatan | 13 | K6 |
+———-+—————–+————————+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+—————–+————————+——+——-+
| 20170002 | Angel Christina | Jl. Petojo Barat | 7 | K1 |
| 20170003 | Bella Lorenza | Jl. Petojo Bara | 8 | K2 |
| 20170004 | Charles | Jl. Metro Marini Barat | 7 | K1 |
| 20170005 | Davin | Jl. Petojo Utara | 10 | K4 |
| 20170006 | Donna | Jl. Jelambar Jaya | 10 | K4 |
| 20170007 | Dorothy | Jl. Tanah Sereal | 11 | K5 |
| 20170008 | Dova Ariella | Jl. Hanura I | 12 | K6 |
| 20170009 | Evita | Jl. Krendang Raya | 11 | K5 |
| 20170010 | Grace Florence | Jl. Duri Selatan | 13 | K6 |
+———-+—————–+————————+——+——-+
akan menampilkan semua data selain Aldo
Berton’
dengan kondisi like seperti di atas, nama dengan
menggunakan != jadi lebih baik gunakan != atau
<>
select * from student where nama like ‘Do%’;
+———-+————–+——————-+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+————–+——————-+——+——-+
| 20170006 | Donna | Jl. Jelambar Jaya | 10 | K4 |
| 20170007 | Dorothy | Jl. Tanah Sereal | 11 | K5 |
| 20170008 | Dova Ariella | Jl. Hanura I | 12 | K6 |
+———-+————–+——————-+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+————–+——————-+——+——-+
| 20170006 | Donna | Jl. Jelambar Jaya | 10 | K4 |
| 20170007 | Dorothy | Jl. Tanah Sereal | 11 | K5 |
| 20170008 | Dova Ariella | Jl. Hanura I | 12 | K6 |
+———-+————–+——————-+——+——-+
semua data nama yang nama depannya ada unsur do, maka akan
ditampilkan.
select * from student where nama not like ‘Do%’;
+———-+—————–+————————+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+—————–+————————+——+——-+
| 20170001 | Aldo Berton | Jl. Duri Selatan | 8 | K2 |
| 20170002 | Angel Christina | Jl. Petojo Barat | 7 | K1 |
| 20170003 | Bella Lorenza | Jl. Petojo Bara | 8 | K2 |
| 20170004 | Charles | Jl. Metro Marini Barat | 7 | K1 |
| 20170005 | Davin | Jl. Petojo Utara | 10 | K4 |
| 20170009 | Evita | Jl. Krendang Raya | 11 | K5 |
| 20170010 | Grace Florence | Jl. Duri Selatan | 13 | K6 |
+———-+—————–+————————+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+—————–+————————+——+——-+
| 20170001 | Aldo Berton | Jl. Duri Selatan | 8 | K2 |
| 20170002 | Angel Christina | Jl. Petojo Barat | 7 | K1 |
| 20170003 | Bella Lorenza | Jl. Petojo Bara | 8 | K2 |
| 20170004 | Charles | Jl. Metro Marini Barat | 7 | K1 |
| 20170005 | Davin | Jl. Petojo Utara | 10 | K4 |
| 20170009 | Evita | Jl. Krendang Raya | 11 | K5 |
| 20170010 | Grace Florence | Jl. Duri Selatan | 13 | K6 |
+———-+—————–+————————+——+——-+
semua data nama yang nama depannya tidak ada unsur do,
maka akan ditampilkan.
select * from student where nama like ‘%do%’;
+———-+————–+——————-+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+————–+——————-+——+——-+
| 20170001 | Aldo Berton | Jl. Duri Selatan | 8 | K2 |
| 20170006 | Donna | Jl. Jelambar Jaya | 10 | K4 |
| 20170007 | Dorothy | Jl. Tanah Sereal | 11 | K5 |
| 20170008 | Dova Ariella | Jl. Hanura I | 12 | K6 |
+———-+————–+——————-+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+————–+——————-+——+——-+
| 20170001 | Aldo Berton | Jl. Duri Selatan | 8 | K2 |
| 20170006 | Donna | Jl. Jelambar Jaya | 10 | K4 |
| 20170007 | Dorothy | Jl. Tanah Sereal | 11 | K5 |
| 20170008 | Dova Ariella | Jl. Hanura I | 12 | K6 |
+———-+————–+——————-+——+——-+
semua data nama yang nama ada unsur do, maka akan ditampilkan.
select * from student where nama not like ‘%do%’;
+———-+—————–+————————+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+—————–+————————+——+——-+
| 20170002 | Angel Christina | Jl. Petojo Barat | 7 | K1 |
| 20170003 | Bella Lorenza | Jl. Petojo Bara | 8 | K2 |
| 20170004 | Charles | Jl. Metro Marini Barat | 7 | K1 |
| 20170005 | Davin | Jl. Petojo Utara | 10 | K4 |
| 20170009 | Evita | Jl. Krendang Raya | 11 | K5 |
| 20170010 | Grace Florence | Jl. Duri Selatan | 13 | K6 |
+———-+—————–+————————+——+——-+
| NIS | Nama | Alamat | Umur | Kelas |
+———-+—————–+————————+——+——-+
| 20170002 | Angel Christina | Jl. Petojo Barat | 7 | K1 |
| 20170003 | Bella Lorenza | Jl. Petojo Bara | 8 | K2 |
| 20170004 | Charles | Jl. Metro Marini Barat | 7 | K1 |
| 20170005 | Davin | Jl. Petojo Utara | 10 | K4 |
| 20170009 | Evita | Jl. Krendang Raya | 11 | K5 |
| 20170010 | Grace Florence | Jl. Duri Selatan | 13 | K6 |
+———-+—————–+————————+——+——-+
semua data nama yang nama tidak ada unsur do, maka akan
ditampilkan.
*. menggunakan in & Not In
perbedaan in dan Not in adalah
: kalau in berarti yang ada unsur data yang dicari, dan sebaliknya not in tidak
ada unsur data yang dicari, cuma in dan not in bisa beberapa record yang di
harapkan.
select * from kelas where kode in (‘K1’, ‘K2’);
+——+———+
| Kode | Nama |
+——+———+
| K1 | Kelas 1 |
| K2 | Kelas 2 |
+——+———+
| Kode | Nama |
+——+———+
| K1 | Kelas 1 |
| K2 | Kelas 2 |
+——+———+
select * from kelas where kode not in (‘K1’, ‘K2’);
+——+———+
| Kode | Nama |
+——+———+
| K3 | Kelas 3 |
| K4 | Kelas 4 |
| K5 | Kelas 5 |
| K6 | Kelas 6 |
+——+———+
| Kode | Nama |
+——+———+
| K3 | Kelas 3 |
| K4 | Kelas 4 |
| K5 | Kelas 5 |
| K6 | Kelas 6 |
+——+———+
untuk menampilkan 1 record pada duplikasi data, contoh untuk krs
: kalau tampilkan semua :
+———-+—————–+
| nis | kode_matakuliah |
+———-+—————–+
| 20170001 | MTK0001 |
| 20170001 | MTK0002 |
| 20170001 | MTK0003 |
| 20170001 | MTK0004 |
| 20170001 | MTK0005 |
| 20170002 | MTK0001 |
| 20170002 | MTK0002 |
| 20170002 | MTK0003 |
| 20170002 | MTK0004 |
| 20170002 | MTK0005 |
| 20170003 | MTK0001 |
| 20170003 | MTK0002 |
| 20170003 | MTK0003 |
| 20170003 | MTK0004 |
| 20170003 | MTK0005 |
| 20170003 | MTK0006 |
+———-+—————–+
| nis | kode_matakuliah |
+———-+—————–+
| 20170001 | MTK0001 |
| 20170001 | MTK0002 |
| 20170001 | MTK0003 |
| 20170001 | MTK0004 |
| 20170001 | MTK0005 |
| 20170002 | MTK0001 |
| 20170002 | MTK0002 |
| 20170002 | MTK0003 |
| 20170002 | MTK0004 |
| 20170002 | MTK0005 |
| 20170003 | MTK0001 |
| 20170003 | MTK0002 |
| 20170003 | MTK0003 |
| 20170003 | MTK0004 |
| 20170003 | MTK0005 |
| 20170003 | MTK0006 |
+———-+—————–+
*. menggunakan exists & Not
exists
penggunaan table matakuliah dan krs, dimana kita ingin
menampilkan matakuliah yang diambil krs maupun yang tidak diambil;
select * from matakuliah a where exists (select 1 from krs b
where b.kode_matakuliah = a.kode);
+———+—————–+——+
| kode | nama | sks |
+———+—————–+——+
| MTK0001 | Fisika | 4 |
| MTK0002 | Kalkulus | 4 |
| MTK0003 | Pancasila | 2 |
| MTK0004 | Kewarganegaraan | 2 |
| MTK0005 | Akuntansi 1 | 3 |
+———+—————–+——+
| kode | nama | sks |
+———+—————–+——+
| MTK0001 | Fisika | 4 |
| MTK0002 | Kalkulus | 4 |
| MTK0003 | Pancasila | 2 |
| MTK0004 | Kewarganegaraan | 2 |
| MTK0005 | Akuntansi 1 | 3 |
+———+—————–+——+
menampilkan matakuliah yang ada di table krs
select * from matakuliah a where not exists (select 1 from krs b
where b.kode_matakuliah = a.kode);
+———+————-+——+
| kode | nama | sks |
+———+————-+——+
| MTK0006 | Akuntansi 2 | 3 |
+———+————-+——+
| kode | nama | sks |
+———+————-+——+
| MTK0006 | Akuntansi 2 | 3 |
+———+————-+——+
menampilkan matakuliah yang tdk ada di table krs
*. penggunaan distinct
tapi yang dimau misalnya hanya nis saja,
select distinct nis from krs;
+———-+
| nis |
+———-+
| 20170001 |
| 20170002 |
| 20170003 |
+———-+
| nis |
+———-+
| 20170001 |
| 20170002 |
| 20170003 |
+———-+