Field Hitungan Menggunakan SQL

Data yang disimpan dalam table database seringkali tidak tersedia dalam format yang sebenarnya dibutuhkan oleh aplikasi atau user. Sebagai contoh, mungkin data yang ingin ditampilkan dalam bentuk gabungan dari beberapa kolom/field, ataupun ingin ditampilkan dalam huruf besar sedangkan data yang diinput dalam tabel dalam huruf kecil. Bahkan terkadang dibutuhkan data berupa hasil hitungan dari beberapa kolom/field.

Field hitungan adalah sebuah field yang dihasilkan dari kombinasi field-field yang ada dalam tabel. Field hitungan tidak benar-benar ada dalam tabel database, hanya bersifat on-the fly dalam statement SELECT. Field yang dihasilkan tidak mempunyai nama, tetapi  dapat diberikan alternatif nama dengan pemberian alias.

Rangkaian Field

Rangkian field adalah field yang terbentuk dari gabungan beberapa field data tipe teks yang dirangkaian menjadi sebuah field. Operator yang digunakan untuk merangkai field adalah tanda + ataupun ||, tergantung DBMS yang digunakan.

Contoh penggunaan Rangkaian Field:

SELECT FirstName, LastName, FirstName + '  ' + LastName As NamaLengkap,

Address, City, Country, Address + ', ' + City + ',  ' + Country As Alamat_Lengkap

FROM Employees Order By FirstName

Field Kalkulasi Matematis

Field kalkulasi sering digunakan untuk melakukan perhitungan-perhitungan matematis pada data yang diambil.

Tabel 4.3 Operator Matematis SQL

Operator	Keterangan
+	Penjumlahan
-	Pengurangan
*	Perkalian
/	Pembagian

Contoh penggunaan field kalkulasi matematis:

SELECT  ProductId, UnitPrice, Quantity, UnitPrice * Quantity As Harga_Total FROM [Order Details] ORDER BY ProductId

Fungsi

Fungsi adalah operasi-operasi yang dapat dilakukan pada data untuk memudahkan perubahan dan manipulasi atau ekstraksi data. Fungsi cenderung sangat khusus untuk setiap DBMS. Pada dasarnya sedikit saja fungsi yang secara identik didukung oleh semua DBMS besar. Walaupun semua tipe fungsionalitas biasanya tersedia di setiap DBMS, nama fungsi atau sintaks dapat sangat berbeda.

Contoh  perbedaaan penggunaan fungsi ekstraksi bagian string pada DBMS yang berbeda :

Tabel 4.4 Perbedaan penggunaan fungsi ekstraksi string pada beberapa DBMS

Fungsi	MS. Access	V. Foxpro	SQL Server	Oracle
Ekstraksi bagian string	Mid( )	Substr( )	Substring( )	Substr( )

Tipe-tipe Fungsi

1.        Fungsi Teks

2.        Fungsi Tanggal dan Waktu

3.        Fungsi Numerik

4.        Fungsi Sistem

Fungsi Teks

Fungsi teks digunakan untuk memanipulasi atau mengekstrak string teks (pemotongan atau penambahan nilai, konversi huruf besar dan huruf kecil).

Tabel 4.5 Contoh Fungsi Teks

Fungsi Teks	MS.Access	V.Foxpro	SQL Server
Mengambil beberapa karakter dari kiri string teks	Left( )	Left( )	Left( )
Mengambil beberapa karakter dari kanan string teks	Right( )	Right( )	Right( )
Meng-ekstrak bagian dari string teks	Mid( )	Substr( )	Substring( )
Menghitung panjang string teks	Len()	Len()	Len( )
Menghapus spasi di sebelah kiri string teks	Ltrim( )	Ltrim( )	Ltrim( )
Menghapus spasi di sebelah kanan string teks	Rtrim( )	Rtrim( )	Rtrim( )
Mengubah format string ke huruf besar	Ucase( )	Upper( )	Upper( )
Mengubah format string ke huruf kecil	Lcase( )	Lower( )	Lower( )

Catatan : format parameter yang digunakan dalam setiap fungsi untuk masing-masing DBMS tidak selalu sama.

Contoh penggunaan fungsi teks :

1.      SELECT CompanyName, Length(CompanyName) as PanjangData,  Left(CompanyName,4) As Kiri4, Right(CompanyName,4) As Kanan4, Mid(CompanyName,3,5) As Tengah35, Ucase(CompanyName) As HurufBesar, Lcase(CompanyName) As HurufKecil FROM Customers Order By CompanyName

2. SELECT

     Ltrim(‘    JURUSAN        ‘) As SpasiKiri, 

     Rtrim(‘    JURUSAN      ‘) As SpasiKanan, 

     Trim(‘    JURUSAN       ‘) As TanpaSpasi

     Lcase(JURUSAN) As HurufKecil

Fungsi Tanggal

Fungsi Tanggal digunakan untuk memanipulasi nilai tanggal dan waktu atau untuk mengekstrak komponen-komponen spesifik dari nilai-nilai tersebut.

Tabel 4.6 Contoh Fungsi Tanggal

Fungsi Tanggal	MS.Access	V.Foxpro	SQL Server
Mengambil Nilai Tanggal	Day()	Day()	Day()
Mengambil Nilai Bulan	Month()	Month()	Month()
Mengambil Nilai Tahun	Year()	Year()	Year()
Mengambil Nilai Tanggal	DatePart('d',x)		DatePart(dd,x)
Mengambil Nilai Bulan	DatePart('m',x)		DatePart(mm,x)
Mengambil Nilai Tahun	DatePart('yyyy',x)		DatePart(yy,x)
Menghitung Selisih Tanggal	DateDiff('d',x,y)		DateDiff(dd,x,y)
Menghitung Selisih Tahun	DateDiff('yyyy',x,y)		DateDiff(yy,x,y )
Menambah Tahun	DateAdd('yyyy',x,y)		DateAdd(yy,x,y)
Mengambil Waktu System (komputer)	Now()		Getdate()

Catatan : Format parameter yang digunakan dalam setiap fungsi untuk masing-masing DBMS tidak selalu sama.

Contoh penggunaan Fungsi Tanggal:

1.    SELECT FirstName + ' ' + LastName As Nama, BirthDate,

     Day(BirthDate) As Tanggal,

     Month(BirthDate) As Bulan,

     Year(BirthDate) As Tahun 

     FROM Employees

2.    SELECT FirstName + ' ' + LastName As Nama, BirthDate,

     Datepart(dd,BirthDate) As Tanggal,

     Datepart(mm,BirthDate) As Bulan,

     Datepart(yy,BirthDate) As Tahun

     FROM Employees

3.    SELECT FirstName + ' ' + LastName As Nama, BirthDate FROM Employees

     WHERE Datepart(yy,BirthDate) Between 1960 And 1968

     ORDER BY BirthDate

Fungsi Numerik

Fungsi Numerik digunakan untuk melakukan operasi-operasi matematik pada data numerik. Fungsi Numerik hanya digunakan untuk manipulasi data numerik, cenderung digunakan untuk perhitungan aljabar, trigonometri, ataupun geometri. Fungsi numerik adalah fungsi yang paling seragam dan konsisten pada DBMS yang besar.

Tabel 4.7 Fungsi Manipulasi Numerik yang Umum Digunakan

Fungsi	Keterangan
ABS()	Menghasilkan nilai mutlak sebuah bilangan
COS()	Menghasilkan cosin trigonometri dari sudut tertentu
EXP()	Menghasilkan nilai eksponen dari bilangan khusus
PI()	Menghasilkan nilai PI
SIN()	Menghasilkan sinus trigonometri dari sudut tertentu
SQRT()	Menghasilkan akar kuadrat bilangan tertentu
ROUND()	Menghasilkan pembulatan bilangan tertentu

Catatan : Lihat dokumentsi DBMS masing-masing untuk daftar fungsi matematis yang didukung.

Contoh penggunaan Fungsi Numerik:

1.    SELECT Abs(-1) as NilaiMutlak, Sqrt(81) As NilaiAkar

2.    SELECT UnitPrice, Sqrt(UnitPrice) As NilaiAkar FROM Products

3.    SELECT  Round(Sqrt(UnitPrice),3) As Pembulatan3 FROM Products

Fungsi Sistem

Fungsi Sistem mengembalikan informasi-informasi khusus ke DBMS yang sedang digunakan, seperti siapa yang sedang meng-akses database, daftar nama user yang terdaftar dan lain-lain. Fungsi sistem sangat khusus untuk setiap DBMS.

Contoh penggunaan Fungsi Sistem:

SELECT User_Name(1), User_Name(2), User_Name(3), Current_User

Menyisipkan Data

Statement INSERT digunakan untuk menyisipkan (menambah)  baris ke suatu tabel database. Insert/menyisipkan data dapat dilakukan dengan penginputan perbaris data secara langsung atau data yang disisipkan dapat berasal dari bari-baris tabel lain (hasil dari query).

a.    Insert dan Input Satu Baris Data

Pola :

INSERT INTO TableName[(columns,…)]

VALUES (value1, value2,…)

Contoh :

Insert sebuah baris tunggal lengkap :

Penulisan tabel secara implisit :

INSERT INTO Pelanggan

             VALUES ('XYZZ',

                        'PT. Maju Mundur',

                        ‘Anty',

                        ‘Secretary',

                        'Jl. Pengayoman No. 20',

                        'Makassar',

                        'MKS',

                        '1234',

                        'Indonesia',

                        '(0411) 444123',

                        '(0411) 444321')

Penulisan tabel secara eksplisit :

INSERT INTO Pelanggan (CustomerId,

                           CompanyName,

                           ContactName,

                           ContactTitle,

                           Address,

                           City,

                           Region,

                           PostalCode,

                           Country,

                           Phone,

                           Fax)

   VALUES ('XYZZ',

               'PT. Maju Mundur',

               ‘Anty',

               ‘Secretary',

               'Jl. Pengayoman No. 20',

               'Makassar',

               'MKS',

               '1234',

               'Indonesia',

               '(0411) 444123',

               '(0411) 444321')

Insert sebuah baris tunggal sebagian:

INSERT INTO Pelanggan (CustomerId,

                           CompanyName,

                           Address,

                           City)

   VALUES ('XYZZ',

               'PT. Maju Mundur',

               ‘Anty',

               ‘Secretary',

               'Jl. Pengayoman No. 20',

               'Makassar')

b.      Insert Data Dari Tabel /Hasil Query

Pola:

INSERT INTO TableName

SELECT columns,…

FROM TableName

[WHERE]….

Contoh :

1.    INSERT INTO Pelanggan

SELECT * FROM Customers

WHERE Region Is Null

2.    INSERT  INTO Pelanggan  (CustomerId,

                                                   CompanyName,

                                                   Address)

SELECT  CustomerId,

                     CompanyName,

                     Address

FROM Customers

WHERE CompanyName Like ‘[ABC]*’

c.         Meng-copy /menyalindari tabel

Pola :

SELECT * Into DestinationTable

FROM SourceTable

Contoh :

Membuat Tabel Pelanggan dengan meng-copy Tabel Customers:

SELECT * INTO Pelanggan FROM Customers

Memperbaharui Struktur Tabel

Untuk memperbaharui defenisi tabel digunakan statemen ALTER TABLE. Namun demikian perubahan tabel yang sudah ada, bukanlah masalah sederhana. Pastikan merujuk dokumentasi DBMS untuk melihat bagaimana perubahan bisa dilakukan.

Pola :

ALTER TABLE

(

  ADD|DROP COLUMN |ALTER COLUMN column DataType [NULL|NOT NULL] [constraints],

     ADD|DROP COLUMN |ALTER COLUMN column DataType [NULL|NOT NULL] [constraints],

    ….

)

Contoh :

1.  ALTER TABLE Mahasiswa

     ADD Jurusan Char(15) NULL

2. ALTER TABLE Mahasiswa

     ALTER COLUMN Jurusan Char(20) NULL

3.  ALTER TABLE Mahasiswa

     DROP COLUMN Jurusan