SQL Functions

SQL FUNCTIONS

Penulisan saya kali ini seputar tentang fungsi sql, penulisan ini merupakan kaitan dari SQL Advanced

SQL Functions adalah penulisan saya terakhir tentang SQL, saya berharap dalam penerapan lebih lanjut para pembaca dapat mengaplikasikan dalam latihan-latihan sendiri sehingga mencapai tingkat pengertian yang lebih mendalam. dalam SQL Functions yang kali ini saya ingin bagi kan adalah bentukan fungsi built in untuk melakukan perhitungan pada data, Berikut penulisan kali ini ;

SQL Aggregate Functions

Fungsi agregat SQL mengembalikan nilai tunggal, dihitung dari nilai-nilai dalam kolom.

Berguna aggregate functions :

• AVG() - Mengembalikan nilai rata-rata
• COUNT() - Mengembalikan jumlah baris
• FIRST() - Mengembalikan nilai pertama
• LAST() - Mengembalikan nilai terakhir
• MAX() - Mengembalikan nilai terbesar
• MIN() - Mengembalikan nilai terkecil
• SUM() - Mengembalikan jumlahnya

---

SQL Scalar functions

mengembalikan nilai tunggal, berdasarkan pada nilai masukan.

Scalar functions :

• UCASE() - Mengkonversi lapangan ke huruf besar
• LCASE() - Mengkonversi lapangan menjadi huruf kecil
• MID() - Ekstrak karakter dari kolom teks
• LEN() - Mengembalikan panjang dari kolom teks
• ROUND() - Rounds bidang numerik dengan jumlah desimal yang ditentukan
• NOW() - Mengembalikan tanggal sistem saat ini dan waktu
• FORMAT() - Format bagaimana lapangan yang akan ditampilkan

Tip : The aggregate functions dan scalar functions akan dijelaskan secara rinci dalam penulisan berikut.

•  SQL AVG ()

fungsi mengembalikan nilai rata-rata dari kolom numerik.

SQL AVG () Sintaks

SELECT AVG(column_name) FROM table_name

---

SQL AVG () Contoh

saya memiliki berikut tabel "Perintah" :

O_Id	OrderDate	OrderPrice	Pelanggan
1	2008/11/12	1.000	Hansen
2	2008/10/23	1.600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2.000	Jensen
6	2008/10/04	100	Nilsen

Sekarang kita ingin mencari nilai rata-rata bidang "OrderPrice".

saya menggunakan pernyataan SQL berikut :

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

Hasilnya akan terlihat seperti ini :

OrderAverage
950

Sekarang kita ingin mencari pelanggan yang memiliki nilai OrderPrice lebih tinggi dari nilai rata-rata OrderPrice.

saya menggunakan pernyataan SQL berikut :

SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

Hasilnya akan terlihat seperti ini :

Pelanggan
Hansen
Nilsen
Jensen

• SQL COUNT ()

 fungsi mengembalikan jumlah baris yang cocok dengan kriteria tertentu.

---

SQL COUNT (column_name) Sintaks

COUNT (column_name) mengembalikan fungsi jumlah nilai (nilai NULL tidak akan dihitung) dari kolom tertentu :

SELECT COUNT(column_name) FROM table_name

SQL COUNT (*) Sintaks

COUNT (*) fungsi mengembalikan jumlah record dalam sebuah tabel :

SELECT COUNT(*) FROM table_name

SQL COUNT (DISTINCT column_name) Sintaks

COUNT (DISTINCT column_name) mengembalikan fungsi jumlah nilai yang berbeda dari kolom yang ditentukan :

SELECT COUNT(DISTINCT column_name) FROM table_name

Catatan : COUNT (DISTINCT) bekerja dengan ORACLE dan Microsoft SQL Server, tetapi tidak dengan Microsoft Access.

---

SQL COUNT (column_name) Contoh

saya memiliki berikut tabel "Perintah" :

O_Id	OrderDate	OrderPrice	Pelanggan
1	2008/11/12	1.000	Hansen
2	2008/10/23	1.600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2.000	Jensen
6	2008/10/04	100	Nilsen

Sekarang kita ingin menghitung jumlah pesanan dari "Pelanggan Nilsen".

saya menggunakan pernyataan SQL berikut :

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen'

Hasil dari pernyataan SQL di atas akan menjadi 2, karena Nilsen pelanggan telah membuat 2 perintah secara total :

CustomerNilsen
2

---

SQL COUNT (*) Contoh

Jika kita menghilangkan klausa WHERE, seperti ini :

SELECT COUNT(*) AS NumberOfOrders FROM Orders

Hasilnya akan terlihat seperti ini :

NumberOfOrders
6

yang merupakan jumlah baris dalam tabel.

---

SQL COUNT (DISTINCT nama_kolom) Contoh

Sekarang kita ingin menghitung jumlah pelanggan yang unik dalam tabel "Orders".

saya menggunakan pernyataan SQL berikut :

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

Hasilnya akan terlihat seperti ini :

NumberOfCustomers
3

yang merupakan jumlah pelanggan yang unik (Hansen, Nilsen, dan Jensen) dalam tabel "Orders".

•  SQL FIRST ()

 fungsi mengembalikan nilai pertama dari kolom yang dipilih.

SQL FIRST () Sintaks

SELECT FIRST(column_name) FROM table_name

---

SQL FIRST () Contoh

Saya memiliki berikut tabel "Perintah" :

O_Id	OrderDate	OrderPrice	Pelanggan
1	2008/11/12	1.000	Hansen
2	2008/10/23	1.600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2.000	Jensen
6	2008/10/04	100	Nilsen

Sekarang kita ingin mencari nilai pertama dari kolom "OrderPrice".

Saya menggunakan pernyataan SQL berikut :

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

Tip : Solusi jika FIRST () fungsi tidak didukung :

SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1

Hasilnya akan terlihat seperti ini :

FirstOrderPrice
1.000

• SQL LAST ()

SQL Last () mengembalikan nilai terakhir dari kolom yang dipilih.

SQL LAST () Sintaks

SELECT LAST(column_name) FROM table_name

---

SQL TERAKHIR () Contoh

Saya memiliki berikut "Perintah" tabel :

O_Id	OrderDate	OrderPrice	Pelanggan
1	2008/11/12	1.000	Hansen
2	2008/10/23	1.600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2.000	Jensen
6	2008/10/04	100	Nilsen

Sekarang kita ingin mencari nilai terakhir dari kolom "OrderPrice".

Saya menggunakan pernyataan SQL berikut :

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

Tip : Solusi jika fungsi LAST () tidak didukung :

SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1

Hasilnya akan terlihat seperti ini :

LastOrderPrice
100

• SQL MAX ()

MAX () fungsi mengembalikan nilai terbesar dari kolom yang dipilih.

SQL MAX () Sintaks

SELECT MAX(column_name) FROM table_name

---

SQL MAX () Contoh

saya memiliki berikut "Perintah" tabel :

O_Id	OrderDate	OrderPrice	Pelanggan
1	2008/11/12	1.000	Hansen
2	2008/10/23	1.600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2.000	Jensen
6	2008/10/04	100	Nilsen

Sekarang kita ingin mencari nilai terbesar dari kolom "OrderPrice".

saya menggunakan pernyataan SQL berikut :

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

Hasilnya akan terlihat seperti ini :

LargestOrderPrice
2.000

• SQL MIN ()
  

Fungsi mengembalikan nilai terkecil dari kolom yang dipilih.

SQL MIN () Sintaks

SELECT MIN(column_name) FROM table_name

---

SQL MIN () Contoh

Saya memiliki berikut tabel "Perintah" :

O_Id	OrderDate	OrderPrice	Pelanggan
1	2008/11/12	1.000	Hansen
2	2008/10/23	1.600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2.000	Jensen
6	2008/10/04	100	Nilsen

Sekarang kita ingin mencari nilai terkecil dari kolom "OrderPrice".

Saya menggunakan pernyataan SQL berikut :

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

Hasilnya akan terlihat seperti ini :

SmallestOrderPrice
100

• SQL SUM ()

SUM () mengembalikan fungsi jumlah total dari kolom numerik.

SQL SUM () Sintaks

SELECT SUM(column_name) FROM table_name

---

SQL SUM () Contoh

Saya memiliki berikut tabel "Perintah" :

O_Id	OrderDate	OrderPrice	Pelanggan
1	2008/11/12	1.000	Hansen
2	2008/10/23	1.600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2.000	Jensen
6	2008/10/04	100	Nilsen

Sekarang kita ingin mencari jumlah dari semua bidang "OrderPrice" ".

Kami menggunakan pernyataan SQL berikut :

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

Hasilnya akan terlihat seperti ini :

OrderTotal
5700

• SQL GROUP BY

Fungsi agregat seringkali membutuhkan tambahan GROUP BY.

---

GROUP BY Pernyataan

GROUP BY digunakan dalam hubungannya dengan fungsi agregat untuk mengelompokkan hasilnya oleh satu atau lebih kolom.

SQL GROUP BY Sintaks

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

---

SQL GROUP BY Contoh

Saya memiliki berikut "Perintah" tabel :

O_Id	OrderDate	OrderPrice	Pelanggan
1	2008/11/12	1.000	Hansen
2	2008/10/23	1.600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2.000	Jensen
6	2008/10/04	100	Nilsen

Sekarang kita ingin mencari total jumlah (total order) dari setiap pelanggan.

Saya akan harus menggunakan GROUP BY untuk mengelompokkan pelanggan.

Saya menggunakan pernyataan SQL berikut :

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

Hasilnya akan terlihat seperti ini :

Pelanggan	SUM (OrderPrice)
Hansen	2.000
Nilsen	1.700
Jensen	2.000

Mari kita lihat apa yang terjadi jika kita menghilangkan GROUP BY :

SELECT Customer,SUM(OrderPrice) FROM Orders

Hasilnya akan terlihat seperti ini :

Pelanggan	SUM (OrderPrice)
Hansen	5700
Nilsen	5700
Hansen	5700
Hansen	5700
Jensen	5700
Nilsen	5700

Hasilnya di atas tidak apa yang kita inginkan.

Penjelasan mengapa pernyataan SELECT di atas tidak dapat digunakan : Pernyataan SELECT di atas memiliki dua kolom yang ditentukan (Pelanggan dan SUM (OrderPrice), "SUM (OrderPrice)" mengembalikan nilai tunggal (yang merupakan jumlah total dari kolom "OrderPrice". ), sedangkan "Pelanggan" mengembalikan nilai 6 (satu nilai untuk setiap baris dalam tabel "Orders") karena itu akan tidak memberi kita hasil yang benar. Namun, Anda telah melihat bahwa GROUP BY memecahkan masalah ini.

---

GROUP BY Lebih dari Satu Kolom

Saya juga dapat menggunakan GROUP BY pernyataan di lebih dari satu kolom, seperti ini :

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

• SQL HAVING

Klausul HAVING

Klausa HAVING telah ditambahkan ke SQL karena kata kunci WHERE tidak bisa digunakan dengan fungsi agregat.

SQL HAVING Sintaks

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

---

SQL HAVING Contoh

Saya memiliki berikut tabel "Perintah" :

O_Id	OrderDate	OrderPrice	Pelanggan
1	2008/11/12	1.000	Hansen
2	2008/10/23	1.600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2.000	Jensen
6	2008/10/04	100	Nilsen

Sekarang kita ingin menemukan jika salah satu pelanggan memiliki total order kurang dari 2000.

Saya menggunakan pernyataan SQL berikut :

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

Hasilnya akan terlihat seperti ini :

Pelanggan	SUM (OrderPrice)
Nilsen	1.700

Sekarang kita ingin mencari jika pelanggan "Hansen" atau "Jensen" memiliki urutan total lebih dari 1500.

Saya menambahkan klausa WHERE biasa untuk pernyataan SQL :

SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

Hasilnya akan terlihat seperti ini :

Pelanggan	SUM (OrderPrice)
Hansen	2.000
Jensen	2.000

• SQL UCASE ()

Berfungsi untuk mengkonversi nilai dari bidang menjadi huruf besar.

SQL UCASE () Sintaks

SELECT UCASE(column_name) FROM table_name

Sintaks untuk SQL Server

SELECT UPPER(column_name) FROM table_name

---

SQL UCASE () Contoh

Saya memiliki berikut tabel "Orang" :

P_Id	LastName	FirstName	Alamat	Kota
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Sekarang kita ingin memilih isi kolom "LastName" dan "FirstName" di atas, dan mengubah "LastName" kolom ke huruf besar.

Saya menggunakan pernyataan SELECT berikut :

SELECT UCASE(LastName) as LastName,FirstName FROM Persons

Hasilnya akan terlihat seperti ini :

LastName	FirstName
HANSEN	Ola
SVENDSON	Tove
PETTERSEN	Kari

• SQL LCASE ()

Berfungsi untuk mengkonversi nilai dari bidang menjadi huruf kecil.

SQL lcase () Sintaks

SELECT LCASE(column_name) FROM table_name

Sintaks untuk SQL Server

SELECT LOWER(column_name) FROM table_name

---

SQL lcase () Contoh

Saya memiliki berikut tabel "Orang" :

P_Id	LastName	FirstName	Alamat	Kota
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Sekarang kita ingin memilih isi kolom "LastName" dan "FirstName" di atas, dan mengubah "LastName" kolom menjadi huruf kecil.

Saya menggunakan pernyataan SELECT berikut :

SELECT LCASE(LastName) as LastName,FirstName FROM Persons

Hasilnya akan terlihat seperti ini :

LastName	FirstName
hansen	Ola
svendson	Tove
Pettersen	Kari

• SQL MID ()

Berfungsi digunakan untuk mengekstrak karakter dari kolom teks.

SQL MID () Sintaks

SELECT MID(column_name,start[,length]) FROM table_name

Parameter	Deskripsi
nama_kolom	Diperlukan. Lapangan untuk mengekstrak karakter dari
mulai	Diperlukan. Menentukan posisi awal (dimulai pada 1)
panjangnya	Opsional. Jumlah karakter untuk kembali. Jika dihilangkan, MID () fungsi mengembalikan sisa teks

---

SQL MID () Contoh

Saya memiliki berikut tabel "Orang" :

P_Id	LastName	FirstName	Alamat	Kota
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Sekarang kita ingin mengekstrak empat karakter pertama dari kolom "Kota" di atas.

Saya menggunakan pernyataan SELECT berikut :

SELECT MID(City,1,4) as SmallCity FROM Persons

Hasilnya akan terlihat seperti ini :

SmallCity
Pasir
Pasir
Stav

• SQL LEN ()

Berfungsi untuk mengembalikan panjang dari nilai dalam kolom teks.

SQL LEN () Sintaks

SELECT LEN(column_name) FROM table_name

---

SQL LEN () Contoh

Saya memiliki berikut tabel "Orang" :

P_Id	LastName	FirstName	Alamat	Kota
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Sekarang kita ingin memilih panjang nilai-nilai di kolom "Alamat" di atas.

Saya menggunakan pernyataan SELECT berikut :

SELECT LEN(Address) as LengthOfAddress FROM Persons

Hasilnya akan terlihat seperti ini :

LengthOfAddress
12
9
9

• SQL ROUND ()

Digunakan untuk membulatkan bidang angka dengan jumlah desimal yang ditentukan.

SQL ROUND () Sintaks

SELECT ROUND(column_name,decimals) FROM table_name

Parameter	Deskripsi
nama_kolom	Diperlukan. Lapangan untuk putaran.
desimal	Diperlukan. Menentukan jumlah desimal yang akan dikembalikan.

---

SQL ROUND () Contoh

Saya memiliki berikut tabel "Produk" tabel :

Prod_Id	ProductName	Satuan	UnitPrice
1	Jarlsberg	1000 g	10.45
2	Mascarpone	1000 g	32.56
3	Gorgonzola	1000 g	15.67

Sekarang kita ingin menampilkan nama produk dan harga dibulatkan ke bilangan bulat terdekat.

Saya menggunakan pernyataan SELECT berikut :

SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

Hasilnya akan terlihat seperti ini :

ProductName	UnitPrice
Jarlsberg	10
Mascarpone	33
Gorgonzola	16

• SQL NOW ()

mengembalikan fungsi tanggal dan waktu sistem saat.

SQL NOW () Sintaks

SELECT NOW() FROM table_name

---

SQL NOW () Contoh

Saya memiliki berikut tabel "Produk" :

Prod_Id	ProductName	Satuan	UnitPrice
1	Jarlsberg	1000 g	10.45
2	Mascarpone	1000 g	32.56
3	Gorgonzola	1000 g	15.67

Sekarang kita ingin menampilkan produk dan harga per tanggal hari ini.

Saya menggunakan pernyataan SELECT berikut :

SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

Hasilnya akan terlihat seperti ini :

ProductName	UnitPrice	PerDate
Jarlsberg	10.45	2008/10/07 11:25:02
Mascarpone	32.56	2008/10/07 11:25:02
Gorgonzola	15.67	2008/10/07 11:25:02

• SQL FORMAT ()

digunakan untuk memformat bagaimana yang akan ditampilkan.

SQL FORMAT () Sintaks

SELECT FORMAT(column_name,format) FROM table_name

Parameter	Deskripsi
nama_kolom	Diperlukan. Lahan yang akan diformat.
format	Diperlukan. Menentukan format.

---

SQL FORMAT () Contoh

Saya memiliki berikut "Produk" tabel :

Prod_Id	ProductName	Satuan	UnitPrice
1	Jarlsberg	1000 g	10.45
2	Mascarpone	1000 g	32.56
3	Gorgonzola	1000 g	15.67

Sekarang kita ingin menampilkan produk dan harga per tanggal hari ini (dengan tanggal hari ini ditampilkan dalam format berikut "YYYY-MM-DD").

Saya menggunakan pernyataan SELECT berikut :

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products

Hasilnya akan terlihat seperti ini :

ProductName	UnitPrice	PerDate
Jarlsberg	10.45	2008-10-07
Mascarpone	32.56	2008-10-07
Gorgonzola	15.67	2008-10-07