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 NilaiTanggal
|
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
