ADMINISTRASI DATABASE DAN PEMOGRAMAN SQL MENGGUNAKAN MICROSOFT SQL SERVER 2000
Masria
i
ADMINISTRASI DATABASE DAN PEMOGRAMAN SQL MENGGUNAKAN MICROSOFT SQL SERVER 2000
© Masria
Disain Sampul : Tim Lentera Cetakan Pertama, Februari 2014 Cetakan Kedua, Januari 2015
Penerbit Lentera Ilmu Cendekia Gedung Sentra Kramat Blok A-15 Jl. Kramat Raya Senen No. 7-9 Jakarta, Indonesia Telp. 021-3156126 Email:
[email protected]
Dicetak oleh: Lentera Printing, Jakarta
ii
KATA PENGANTAR Puji syukur kepada Allah SWT penulis ucapkan, karena berkat Rahmat Nya buku ini dapat diselesaikan dengan baik. Buku ini ditujukan kepada pembaca yang ingin menguasai Administrasi Database dengan SQL Server 2000 dan menguasai Bahasa Pemrograman SQL, disamping itu buku ini baik juga dibaca oleh para pengembang aplikasi pemrograman database karena didalamnya dimuat contoh-contoh penerapan untuk kebutuhan pengolahan data dengan TSQL. Dalam buku ini belum diuraikan seluruh komponen yang ada dalam SQL Server 2000 baik dari sisi administrasinya maupun dari sisi TSQL nya dikarenakan buku ini dibuat disesuaikan dengan kebutuhan bahan ajar SQL untuk lingkungan LP3i, harapan penulis semoga pada penyusunan berikutnya dapat mengupas isi dari SQL Server lebih dalam lagi agar dapat menjadi referensi bagi mahasiswa, praktisi dan lainya. Besar harapan penulis buku ini dapat memberikan manfaat bagi seluruh pembaca dilingkungan LP3i khususnya dan lingkungan luar LP3i pada umumnya, dan penulis sangat berterima kasih apabila pembaca dapat memberikan masukan dan saran demi terciptanya penyajian buku yang lebih baik lagi.
Jakarta, Juli 2013 Masria
iii
iv
DAFTAR ISI Kata Pengantar ........................................................................................................................................... iii BAB 1 Configurasi SQL Server Microsoft SQL Server ......................................................................................................................1 Install SQL Server 2000 ....................................................................................................................1 Kebutuhan Hardware ......................................................................................................................1 Kebutuhan Operating System...........................................................................................................2 Tahapan Instalasi ..............................................................................................................................3 Menjalankan SQL Server 2000........................................................................................................13 Menggunakan Enterprise Manager ................................................................................................16 Mendaftarkan Server SQL ..............................................................................................................18
BAB 2 Pengelolaan Database Membuat Database .......................................................................................................................24 Membuat Table .............................................................................................................................28 Primary Key dan Foreignt Key .......................................................................................................29 Merubah Type Data Table .............................................................................................................31 Menghapus Table ..........................................................................................................................31 Data Types .....................................................................................................................................33 View ...............................................................................................................................................36 Membuat View ..............................................................................................................................37 Menghapus View ...........................................................................................................................39 Merubah nama view.......................................................................................................................39 v
BAB 3 Administrasi Database Membuat User Login .....................................................................................................................42 Memberikan Hak pada User ..........................................................................................................45 DTS Import/Export Wizard ............................................................................................................48 Melakukan Import Data .................................................................................................................48 Export Data ....................................................................................................................................54 Backup Database ...........................................................................................................................58 Restore Database ...........................................................................................................................62 Attach Database ............................................................................................................................64
BAB 4 TSQL Query Analyzer ...............................................................................................................................66 SELECT Clause .................................................................................................................................70 Menampilkan Data Dengan Select .................................................................................................71 Menampilkan kolom tertentu dari sebuah table ..........................................................................71 Menampilkan kolom tertentu dari sebuah table dengan kriteria .................................................72 Menampilkan sebuah table menggunakan kriteria dengan klausa OR .........................................72 Menampilkan sebuah table dengan mengurutkan berdasarkan kolom tertentu menggunakan ORDER BY........................................................................................................................................73 Menampilkan table dengan kriteria LIKE .......................................................................................75 Menampilkan perhitungan kolom dengan menggunakan ALIAS ...................................................78 Menampilkan data sesuai dengan kriteria yang ada pada list dengan klausa IN ..........................79 Menampilkan data dengan kriteria numerik ..................................................................................80 Menampilkan data dengan kriteria numerik menggunakan BETWEEN .........................................81 Menampilkan data dengan kriteria numerik menggunakan OR ....................................................82 vi
Menampilkan data dengan kriteria numerik menggunakan NOT BETWEEN .................................83 Menampilkan data yang memiliki type datetime atau smalldatetime .........................................84 Menampilkan data dengan penulisan kriteria tanggal penuh. ......................................................84 Menampilkan data dengan kriteria tanggal menggunakan fungsi Month dan Year. .....................86 Menampilkan data dengan menyembunyikan data yang duplikat dengan DISTINCT ...................87 Menampilkan data dengan pengelompokan menggunakan GROUP BY ........................................89 Menampilkan data menggunakan GROUP BY dan kriteria ............................................................91 Menampilkan data menggunakan fungsi Aggregate......................................................................92 Menampilkan data dari beberapa table yang terelasi dengan INNER JOIN ...................................94 Menampilkan data dalam bentuk CROSSTAB ................................................................................96 Create Database ...........................................................................................................................101 Create Table..................................................................................................................................104 Alter Table ....................................................................................................................................105 Drop Table ....................................................................................................................................107 Insert Into .....................................................................................................................................108 Delete ...........................................................................................................................................110
BAB 5 Operator Operator .......................................................................................................................................112 Arithmetic Operator .....................................................................................................................112 Assignment Operator ...................................................................................................................113 Comparison Operator / Operator Perbandingan .........................................................................113 Logical Operators..........................................................................................................................114 IF ..................................................................................................................................................115 CASE .............................................................................................................................................118
vii
BAB 6 Fungsi-Fungsi Fungsi-Fungsi ...............................................................................................................................135 Select Top ……………………………………………………………………………………………………………………………… 139 Fungsi Tanggal ………………………………………………………………………………………………………………………. 143 Fungsi String …………………………………………………………………………………………………………………………. 161 Fungsi Matematika ……………………………………………………………………………………………………..……….. 169 Fungsi Lain ……………………………………………………………………………………………………………………………. 172
viii
BAB I KONFIGURASI SQL SERVER Microsoft SQL Server Microsoft SQL Server adalah sebuah sistem manajemen basis data relasional (RDBMS) produk Microsoft. Bahasa queri utamanya adalah Transact-SQL yang merupakan implementasi dari SQL standar ANSI/ISO yang digunakan oleh Microsoft dan Sybase. Umumnya SQL Server digunakan di dunia bisnis yang memiliki basis data berskala kecil sampai dengan menengah, tetapi kemudian berkembang dengan digunakannya SQL Server pada basis data besar. Microsoft SQL Server dan Sybase/ASE dapat berkomunikasi lewat jaringan dengan menggunakan protokol TDS (Tabular Data Stream). Selain dari itu, Microsoft SQL Server juga mendukung ODBC (Open Database Connectivity), dan mempunyai driver JDBC untuk bahasa pemrograman Java. Fitur yang lain dari SQL Server ini adalah kemampuannya untuk membuat basis data mirroring dan clustering. Pada versi sebelumnya, MS SQL Server 2000 terserang oleh cacing komputer SQL Slammer yang mengakibatkan kelambatan akses Internet pada tanggal 25 Januari 2003.
Install SQL Server 2000 Sebelum melakukan Install Microsoft SQL Server 2000 ada beberapa hal yang perlu dipertimbangkan, pastikan komputer yang akan di Install sudah memenuhi standard minimum untuk Microsoft SQL Server
Kebutuhan Hardware Berikut ini hardware requirements untuk melakukan instal Microsoft SQL Server 2000 atau Client Management Tools dan Libraries Hardware Computer
Minimum requirements Intel® atau compatible Pentium 166 MHz atau diatasnya.
Memory (RAM)1
Enterprise Edition
: 64 MB minimum, 128 MB atau diatasnya
Standard Edition
: 64 MB minimum
1
Personal Edition
Developer Edition
: 64 MB minimum pada Windows 2000, 32 MB minimum pada seluruh operating systems lain : 64 MB minimum
Desktop Engine
: 64 MB minimum on Windows 2000, 32 MB
minimum pada seluruh operating systems lain Hard disk space2
SQL Server database components: 95 s/d 270 MB, 250 MB typical Analysis Services : 50 MB minimum, 130 MB typical English Query: 80 MB Desktop Engine only : 44 MB
Monitor
VGA atau resolusi diatasnya 800x600 or diatasnya untuk SQL Server graphical tools
Pointing device
Microsoft Mouse atau compatible
CD-ROM drive
Diperlukan untuk CD installer
Kebutuhan Operating System Table dibawah ini menampilkan operating system yang harus di instal yang digunakan untuk jenis-jenis Microsoft SQL Server 2000 SQL Server edition or component Enterprise Edition
Operating system requirement Microsoft Windows NT Server 4.0, Microsoft Windows NT Server Enterprise Edition 4.0, Windows 2000 Server, Windows 2000 Advanced Server, and Windows 2000 Data Center Server. Catatan: Microsoft Windows 2000 Server diperlukan untuk beberapa fitur SQL Server 2000.
Standard Edition
Microsoft Windows NT Server 4.0, Windows 2000 Server,
2
Microsoft Windows NT Server Enterprise Edition, Windows 2000 Advanced Server, dan Windows 2000 Data Center Server. Personal Edition
Microsoft Windows Me, Windows 98, Windows NT Workstation 4.0, Windows 2000 Professional, Microsoft Windows NT Server 4.0, Windows 2000 Server, dan seluruh Windows operating systems diatasnya.
Developer Edition
Microsoft Windows NT Workstation 4.0, Windows 2000 Professional, and all other Windows NT and Windows 2000 operating systems.
Client Tools Only
Microsoft Windows NT 4.0, Windows 2000, Windows Me, and Windows 98.
Connectivity Only
Microsoft Windows NT 4.0, Windows 2000, Windows Me, Windows 98, and Windows 95.
Tahapan Instalasi 1. Masukan CD Microsoft SQL 2000 Setup kedalam CDRom Komputer autorun akan dijalankan dan akan muncul tampilan seperti gambar berikut
3
2. Pada tampilan gambar 1.1 Pilih SQL Server 2000 Componets
3. Pada tampilan gambar 1.2 Pilih Install Database Server
4. Pada tampilan gambar 1.3 Klik Next, akan tampil gambar seperti berikut :
4
5. Pada tampilan gambar 1.4 Pilih Local Computer kemudian klik Next
Local Computer maksudnya adalah : Setup SQL Server 2000 akan dilakukan di computer yang sedang digunakan Remote Computer maksudnya adalah : Setup SQL Server 2000 akan dilakukan dikomputer Client melalui komputer yang sedang kita gunakan, fasilitas ini sangat berguna untuk melakukan setup dibeberapa komputer berbeda hanya dari tempat, tentunya hanya komputer yang terkoneksi kedalam jaringan komputer.
5
6. Pada tampilan gambar 1.5 Pilih Create a new instance of SQL Server, or install Client Tools, kemudian klik Next.
Create a new instance of SQL Server, or install Client Tools maksudnya adalah : setup yang dilakukan meliputi SQL Server dan Client Tool standard
Upgrade, Remove, or add Components to an existing instance of SQL Server digunakan untuk melakukan upgrade, penghapusan atau penambahan komponen SQL Server yang sudah terinstall
Advanced option digunakan untuk setup SQL Server dimana kita dapat melakukan pilihan instalasi sesuai kebutuhan.
6
7. Pada tampilan gambar 1.6 Ketikan nama anda dan nama perusahaan , kemudian klik Next
8. Pada tampilan gambar 1.7 pilih Yes
7
9. Pada tampilan gambar 1.8 pilih Server and Client Tools, kemudian klik Next
Client Tools Only, Pilihan ini akan menjalankan setup SQL Server 2000 khusus untuk Tool untuk Client. Server and Client Tools, Pilihan ini akan menjalankan setup SQL Server 2000 untuk Server dan Tool untuk client. Connectivity Only, Pilihan ini akan mensetup hanya tool untuk koneksi ke SQL Server 2000
8
10. Pada tampilan gambar 1.9 pilih Default, kemudian klik Next Apabila Anda belum memiliki instalasi SQL Server di komputer yang sedang diinstall, pilih “Default”. Jika sudah ada SQL Server di komputer, Anda dapat membuat instance baru sehingga terdapat 2 SQL Server di komputer Anda.
11. Pada tampilan gambar 1.10 pilih Typical, kemudian klik Next
9
Typical, pilihan ini melakukan instalasi secara umum, pilihan ini direkomendasikan khusu untuk para pemakai umum.
Minimum, pilihan ini melakukan instalasi kebutuhan minimum untuk menjalankan SQL Server 2000
Custom, piihan ini melakukan instalasi sesuai dengan pilihan komponen yang dibutuhkan, direkomendasikan khusus bagi pemakai yang sudah mahir.
Destination Folder, Klik Browse untuk menentukan lokasi instal SQL Server 2000, jika tidak dipilih maka Setup akan menggunakan Lokasi Default yaitu C:\Program Files\Microsoft SQL Server.
12. Pada tampilan gambar 1.11 pilih Use the same account for each service. Auto start SQL Service, pada Service Setting pilih Use a Domain User account.
10
Use a Domain User account, pilihan ini dilakukan pada Operatng Sytem Windows 2000 Server dengan menggunakan account/user yang sudah terdaftar pada domain/ Active Directory untuk menjalankan service tersebut.
Use the Local System account, pilihan ini SQL Server menggunakan account system di OS untuk menjalankan servicenya.
13. Pada tampilan gambar 1.12 pilih Mixed Mode (Windows Authentication and SQL server Authentication). Kemudian masukan Password untuk sa (system administrator) kemudian klik Next
Windows Authentication Mode, Pilihan ini akan menggunakan Authentication yang ada pada system windows artinya users dapat mengakses SQL Server dengan menggunakan account/user yang ada pada Operating System Windows, sehingga jika user dapat login ke windows maka secara otomatis user dapat mengakses SQL Server.
11
Mixed Mode (Windows Authentication and SQL server Authentication), Pilihan ini dapat menerapkan Authentication access ke SQL Server seperti Windows Authentication Mode dan juga menggunakan Authentication pada SQL Server, artinya user yang berhasil Login ke windows tidak secara otomatis dapat mengakses SQL Server melainkan user tersebut harus terdaftar di account yang ada pada SQL Server.
14. Pada tampilan gambar 1.13 pilih Next maka proses instalasi akan berjalan dan tunggu sampai muncul dialog bahwa setup sudah selesai.
15. Pada tampilan gambar 1.14 klik Finish.
12
Menjalankan SQL Server 2000 Setelah selesai proses instalasi SQL Server 2000 untuk memulai menggunakan SQL Server 2000 kita harus mengaktifkan Service Microsoft SQL Server 2000 terlebih dahulu, selama Service tidak aktif maka proses akses ke server Microsoft SQL Server tidak dapat dilakukan baik dari komputer servernya maupun dari komputer Client. Tools yang dipakai untuk mengolah data pada SQL Server 2000 menggunakan Enterprice Manager dan Query Analyzer. Tampilan Enterprise Manager
Tampilan Query Analyzer
SQL Server Service Manager
13
SQL Server Service Manager digunakan untuk menjalankan dan menghentikan komponen SQL Server 2000 pada Server.
SQL Server service Untuk menjalankan fasilitas Database Engine SQL Server.
SQL Server Agent service Untuk dapat menggunakan fasilitas penjadwalan dalam melakukan administrasi database pada SQL Server
Microsoft Search service (Windows NT and Windows 2000 only) Untuk dapat meggunakan fasilitas pencarian informasi yang dbutuhkan
Mengaktifkan Service Untuk mengaktifkan Service pada SQL Server gunakan menu Start pilih Programs pilih Microsoft SQL Server pilih Service Manager.
14
Dari dialog Server Manager klik tombol
Server
Start/Continue untuk mengktifkan service
List Server yang bisa diaktifkan Servicenya, nilai default adalah nama komputer local.
Services
Service yang dapat diaktifkan meliputi SQL Server, Distribute Transaction Coordinator dan SQL Server Agent. Untuk menggunakan SQL Server gunakan Service SQL Server
Auto-start service when OS
cheklist pilihan ini apabila menginginkan service
start
akan aktif secara otomatis ketika windows dijalankan.
Informasi mengenai bahwa service sudah aktif dapat dilihat pada Task Bar seperti terlihat pada gambar dibawah ini.
15
Menggunakan Enterprise Manager Dalam mengolah data di Microsoft SQL Server 2000 dapat menggunakan dua interface yaitu Enterprise Manager dan Query Analyzer. Enterprise Manager adalah interface utama yang dipakai dalam melakukan administrasi database pada SQL Server 2000, EM juga digunakan untuk melakukan Management Console (MMC) dimana user dapat melakukan : 1.
Menentukan Group Server
2.
Mendaftarkan sebuah Server pada Group Server
3.
Melakukan pengadministrasian pada seluruh fasilitas yang ada pada SQL Server Databse, meliputi object, Login, user dan pengaturan permissions pada setiap pendaftaran Server.
Untuk menjalankan Enterprise Manager lakukan dari Menu Start pilih Programs pilih Microsoft SQL Server pilih Enterprise Manager.
16
Secara normal Enterprise Manager akan menampilkan SQL Server dengan menggunakan authentication windows, sehingga secara otomatis user dapat masuk kedalam SQL Server dan dapat melakukan access secara penuh. Setelah berhasil masuk kedalam Enterprise Manager anda dapat melakukan seluruh aktifitas administrasi yang ada pada SQL Server karena pada console seperti terlihat digambar 1.20 sudah memiliki Group yaitu SQL Server Group, dan juga sudah terdaftar Server yaitu Local Server.
17
Mendaftarkan Server SQL. Seperti yang sudah disampaikan diatas, ketika pertama kali kita menjalankan SQLServer dengan menggunakan Enterprise Manager secara default sudah terdaftar lokal server. Pada pembahasan berikut ini kita akan mempelajari bagaimana mendaftarkan server pada Enterprise Manager.
Agar dalam simulasi pendaftaran Server dapat terlihat dengan jelas proses penambahan Server, kita akan menghapus Lokal Server yang saat ini sudah terdaftar. Untuk melakukan penghapusan Server lakukan instruksi berikut ini: 1. Klik kanan pada Local Server / Nama Server yang sudah terdaftar 2. Pilih Delete SQL Server Registration
Maka akan terlihat pada Enterprise Manager kita sudah tidak memiliki server SQL yang sudah terdaftar (No Items).
18
3. Berikutnya klik kanan pada SQL Server Group pilih New SQL Server Registration
4. Maka akan muncul dialog wizard untuk melakukan Registrasi / pendaftaran server
5. Klik Next 6. Pada step berikutnya list nama server yang memungkinkan dapat kita daftarkan akan muncul dan kita dapat memilih nama server yang akan kita daftarkan dengan mengklik nama server kemudian klik add. Setelah ini klik Next. Nama server lain biasanya akan terdeteksi apabila dalam sebuah jaringan komputer ada komputer lain yang terinstall SQL Server.
19
7. Pada tahapan berikutnya kita dapat menentukan jenis autentikasi yang akan diterapkan dalam koneksi kedala SQl Server, sebagai contoh kita gunakan SQLServer Authentication
8. Windows Authentication : Mengijinkan user untuk dapat login kedalam SQL Server begitu user berhasil Login Ke Windows. SQL Server Authentication :
20
User yang berhasil Login ke Windows tidak secara otomatis dapat Login ke SQL Server, untuk dapat login ke SQL Server nama user harus didaftarkan terlebih dahulu di SQL Server, setelah itu user dapat melakukan login ke SQL Server.
Dalam SQL Server secara default ada satu nama user yang sudah terdaftar secara otomatis pada saat install SQLServer dengan nama sa. Username ini memiliki tingkat akses server secara penuh, artinya dapat melakukan seluruh kegiatan administrasi database pada SQLServer. Oleh karena itu sebaiknya yang mengetahui password dari user ini adalah hanya untuk level Administrator Database atau level pimpinan dalam sebuah perusahaan yang menggunakan SQLServer sebagai Database Engine .
9. Berikutnya masukan nama user dan password yang akan digunakan untuk Login ke SQL Server dengan nama user yang sudah terdaftar pada SQLServer. Karena belum ada nama user baru maka kita dapat menggunakan user sa dengan password sesuai yang sudah kita setup pada saat instalasi SQL Server dilakukan.
10. Tahapan selanjutnya pilih penambahan SQLServer pada Group yang sudah ada, Klik Next
21
11. Pendaftaran SQLServer diakhiri dengan muncul dialog bahwa pendaftaran sudah selesai seperti yang terlihat pada gambar berikut
12. Klik Finish. Untuk mengetahui bahwa pendaftaran Server SQL berhasil akan diinformasikan dengan dialog bahwa pendaftaran berhasil.
22
Klik tombol Close
Setelah pesan Registered Successfully, maka kita sudah berhasil mendaftarkan sebuah server SQL. Kita dapat meregister lebih dari 1 (satu) server kedalam SQL Server yang kita gunakan dengan cara yang sama seperti diatas.
23
BAB 2 PENGOLAHAN DATABASE
Membuat Database Pada saat kita selesai melakukan instalasi Microsoft SQL Server, ada beberapa database yang sudah dibuat yang merupakan bawaan SQL Server setup sebagai database system dan database sample yaitu: Database
Type
Description
master
System
Menyimpan informasi level sistem seperti use account,
database
configuration setting, dan informasi pada seluruh database lainya.
model
msdb
System
Databsae ini digunakan sebagai templet untuk seluruh
database
database yang dibuat.
System
Digunakan oleh SQL Server Agent untuk melakukan
database
konfigurasi mengenai pesan, jadwal kerja dan lain sebagainya.
tempdb
System
Menyimpan seluruh temporari table, temporari store
database
procedure dan penyimpanan temporari lainya yang dapa di generate oleh SQL Server.
pubs
Northwind
Sample
Database ini digunakan untuk tujuan latihan mengenai
database
Perusahaan Percetakan Buku.
Sample
Database ini digunakan untuk tujuan latihan mengenai
database
perusahaan fiktif perdagangan yang diberi nama “Northwind Traders”.
24
Sekarang kita akan membuat database sendiri yang disesuaikan dengan kebutuhan. 1. Untuk membuat database baru lakukan klik kanan pada icon Database, kemudian pilih
“New Database”
2. Ketikan Nama Database yang akan dibuat, sebagai contoh : Penjualan
3. Klik OK Pada langkah diatas akan membuat database baru dengan nama “Penjualan” yang akan tersimpan pada lokasi default di “C:\Program Files\Microsoft SQL Server\MSSQL\Data”
25
Nama file yang terbentuk adalah : “Penjualan_Data.MDF” dan “Penjualan_Log.LDF”
Membuat Database ke Folder Lain Jika kita akan membuat database dengan lokasi yang kita akan tentukan sendiri, setelah anda mengetikan nama database lakukan pilihan penentuan lokasi pada tab “Data Files” untuk file MDF dan tab “Transaction Log” untuk file LDF
Data File menghasilkan MDF file, merupakan file untuk menyimpan data. Transaction Log menghasilkan LDF file,
merupakan file yang menyimpan histori
transaksi dan semua kejadian di database tersebut.
Pada Tab Data Files klik
pada location, pindahkan lokasi penyimpanan pada lokasi
yang anda inginkan Pada Tab Transaction Log klik
pada location, pindahkan lokasi penyimpanan pada
lokasi yang anda inginkan.
26
File MDF dan LDF disarankan harus berada dalam lokasi folder yang sama, ini untuk memudahkan pada saat akan dilakukan pengelolaan database.
Penjelasan.
File Name dan Location, untuk memberi nama file dan meletakkan file database tersebut di suatu lokasi folder yang diinginkan.
File growth, mengatur penambahan ukuran file database. Dapat ditetapkan dengan persentase tertentu atau berdasarkan jumlah megabytes tertentu.
Maximum file size, ukuran file dapat dibatasi pada tingkat tertentu atau unlimited.
Pada tab Transaction Log terdapat option yang sama dengan tab Data Files. Data File, adalah file untuk menyimpan data. Transaction Log, adalah file ynag menyimpan histori transaksi dan semua kejadian di database tersebut.
27
Membuat Table Pada bagian ini kita akan belajar bagaimana membuat table pada database yang sudah kita buat sebelumnya, untuk pembuatan table lakukan langkah seperti berikut ini: 1. Klik Expand atau tanda + pada database, maka akan terlihat Tables Klik kanan pada icon ”Tables”, pilih ”New Table”
Tentukan berapa kolom yang akan dibuat berikut dengan type data dan ukuran dari kolom seperti terlihat pada gambar berikut
28
untuk memberikan Primery Key pada table, gunakan simbol kunci
pada
kolom yang akan dijadikan sebagai Primery Key. Kemudian simpan denga nama Customer, tekan OK
Primary Key dan Foreignt Key Setiap tabel sebaiknya dilengkapi dengan primary key yang merupakan identitas unik dari setiap baris data. Dengan demikian apabila dalam suatu tabel terdapat sekian ribu baris data misalnya, maka setiap barisnya memiliki identitas sendiri berdasarkan primary key nya. Pada contoh diatas yang bertindak sebagai primary key adalah KodeCustomer, yang berbeda untuk setiap barisnya.
Dalam sebuah table sebagian besar pasti ada satu kolom yang akan dijadikan sebagai ID, dimana ID tersebut akan mewakili muatan yang ada pada table tersebut, ID disini ada yang bersifat unik, dan ada juga ID juga yang bersifat tidak unik. Untuk ID yang bersifat unik biasanya akan dijadikan sebagai Primery Key sedangkan untuk ID yang tidak unik akan dijadikan sebagai Foreignt Key”. ID ini kemudian dijadikan sebagai kolom untuk penghubung antara satu table dengan table yang lainya (Relational Database).
Untuk melakukan pengisian table, lakukan klik kanan pada table Customers pilih Open Table pilih Return All Rows
29
Isi dengan contoh data seperti gambar berikut
Column Name : Nama kolom yang akan dibuat dalam table Data Type
: Type dari data yang akan diisi kedalam komom
Lenght
: Ukuran maximum pengisian pada kolom
Allow Nulls
: ChekList jika pada kolom diizinkan untuk dapat diisi dengan nilai kosong (null)
30
Merubah Struktur Table Setelah kita berhasil membuat table, sering kali kita menemui kebutuhan untuk menambah ukuran kolom, atau merubah type data. Untuk melakukan perubahan struktur pada table gunakan langkah berikut:
Lakukan klik kanan pada table Customer yang sudah kita buat sebelumnya, kemudian pilih Design Table. Lakukan perubahan pada table sesuai dengan kebutuhan
Menghapus Table Untuk melakukan penghapusan table melalui Enterprise Manager, lakukan klik kanan pada table yang sudah kita buat sebelumnya, kemudian pilih Delete. Dari Drop Object Dialog klik Drop All
31
Untuk bahan latihan pada pembahasan berikutnya buatlah database baru dengan beberapa table seperti keterangan berikut. 1. Buat database dengan nama “LATDB” dengan lokasi penyimpanan D:\Latihan 2. Buat table Barang, Pelanggan dan Pemasok dengan struktur data seperti berikut Barang
Pemasok
Pelanggan
32
Data Types Dalam Microsoft SQL Server setiap kolom, lokal variable, ekspresi / ungkapan dan parameter mempunyai type data yang berkaitan, dimana atribut type data tersebut terdiri dari (integer, character, money dan sebagainya), type data yang dapat digunakan dalam sql server adalah sebagai berikut: Exact Numerics 1. Integers bigint Integer (semua angka) data mulai -2^63 (-9223372036854775808) sampai 2^63-1 (9223372036854775807).
int Integer semua angka) data mulai -2^31 (-2,147,483,648) sampai 2^31 - 1 (2,147,483,647).
smallint Integer data mulai 2^15 (-32,768) sampai 2^15 - 1 (32,767).
tinyint Integer data mulai 0 sampai 255.
2. bit bit Integer data dengan nilai 1 atau 0.
3. decimal and numeric decimal Skala numerik data mulai -10^38 +1 sampai 10^38 –1.
33
numeric secara fungsional setara dengan decimal.
4. money and smallmoney money Data moneter nilai mulai dari -2^63 (-922,337,203,685,477.5808) sampai 2^63 - 1 (+922,337,203,685,477.5807),
smallmoney Data moneter nilai mulai dari -214,748.3648 sampai +214,748.3647.
5. Approximate Numerics float Angka data mulai -1.79E + 308 sampai 1.79E + 308.
real Angka data mulai -3.40E + 38 sampai 3.40E + 38.
Datetime And Smalldatetime 1. datetime Date and time data mulai January 1, 1753, sampai December 31, 9999.
2. smalldatetime Date and time data mulai January 1, 1900, sampai June 6, 2079.
Character Strings 1. char Fixed-length non-Unicode karakter data dengan panjang maksimum 8,000 karakter.
34
2. varchar Variable-length non-Unicode data dengan panjang maksimum 8,000 karakter.
3. text Variable-length non-Unicode data dengan panjang maksimum 2^31 - 1 (2,147,483,647) karakter.
Unicode Character Strings 1. nchar Fixed-length Unicode data dengan panjang maksimum 4,000 karakter.
2. nvarchar Variable-length Unicode data dengan panjang maksimum 4,000 karakter.
3. ntext Variable-length Unicode data dengan panjang maksimum 2^30 - 1 (1,073,741,823) karakter.
Binary Strings 1. binary Fixed-length binary data dengan panjang maksimum 8,000 bytes.
2. varbinary Variable-length binary data dengan panjang maksimum 8,000 bytes.
3. image Variable-length binary data dengan panjang maksimum 2^31 - 1 (2,147,483,647) bytes.
35
View View merupakan virtual table yang isinya merupakn penggambaran dari sebuah Query. View hampir mirip dengan table yang terdiri dari kolom dan baris-baris data. Tetapi view bukanlah sebuah media untuk menyimpan data dalam database. View digunakan untuk menampilkan data berdasarkan hasil penyeleksian sesuai dengan informasi yang dibutuhkan. Informasi dalam view diperoleh berdasarkan penyeleksian dari satu table atau dari beberapa table yang terelasi antar tablenya atau dapat juga diambil dari view lainya. Proses penyeleksian data sendiri sebenarnya tetap menggunakan statement SQL (Standard Query Language) hanya kita tidak perlu secara manual mengetikan statement SQL nya tetapi View akan membentuk statement SQL sendiri berdasarkan Design View yang kita buat.
Ilustrasi dibawah ini menggambarkan sebuah view yang diambil dari dua table
Kapan Kita Menggunakan View? Dalam sebuah database biasanya terdiri dari beberapa table, dalam satu table terdiri dari beberapa kolom, pada kondisi tertentu kita biasanya membutuhkan informasi berikut :
36
Mendapatkan informasi dari satu table dimana informasi data yang dibutuhkan hanya pada kolom-kolom tertentu saja (tidak semua kolom)
Mendapatkan informasi dari satu table dimana informasi data yang dibutuhkan hanya pada kolom-kolom tertentu saja (tidak semua kolom) dengan kriteria tertentu.
Mendapatkan informasi dari beberapa table yang terelasi untuk kolom-kolom tertentu
Mendapatkan informasi dari hasil perhitungan-perhitungan kolom-kolom yang ada pada table dan juga informasi yang menggunakan kriteria tertentu.
Membuat View Untuk membuat view lakukan langkah seperti berikut 1. Klik Kanan pada Icon ”Views”, pilih ”New View”
2. Klik tombol
untuk menambahkan table
37
Dari dialog Add Table pilih satu atau beberapa table yang akan dibuatkan view nya, kemudian klik Close. Lakukan ChekList pada kolom yang akan ditampilkan,
secara otomatis akan
tersusun statement SQL untuk model view yang kita design, dan untuk menampilkan hasil design yang sudah kita lakukan klik tombol run design selengkapnya dapat dilihat pada gambar berikut.
3. Simpan view dengan mengklik tombol save
38
Menghapus View Untuk menghapus View, lakukan klik kanan pada view yang sudah kita buat, pilih Delete, klik Drop All.
Merubah Nama View Lakukan klik kanan pada view yang akan diganti nama, pilih Rename, ketikan nama baru pada view.
Berikut ini contoh pembuatan View untuk mendapatkan informasi tertentu 1. Menampilkan data Customers untuk yang Country hanya USA
39
2. Contoh menampilkan data Products dengan menambahkan kolom baru hasi perkalian UnitPrice * UnitsInStock, dimana data yang ditampilkan hanya untuk yang UnitsInStock nya antara 5 s/d 15 dan diurutkan dari hasil perkalian yang tertinggi
40
3. Contoh berikut ini menampilkan data Total Penjualan tiap Country per tahun, data yang ditampilkan untuk yang Country nya berakhiran ‘A’ diurutkan berdasarkan Tahun dan Country . Pada view ini menggunakan tiga table yang terelasi serta dalam prosesnya menggunakan
Group dan Total sehingga dalam pembuatan View harus mengaktifkan
fasilitas Grouping dengan mengkil tombol
41
BAB 3 ADMINISTRASI DATABASE
Membuat User Login Untuk penggunaan SQL Server berikutnya diupayakan agar setiap User yang mempunyai hak untuk Login ke SQL Server diberikan user name dan password masing-masing, jangan menggunakan username SA untuk semua user.
Pada pembahasan berikut ini kita akan mempelajari bagaimana menambahkan UserLogin baru dengan hak-hak tertentu sesuai dengan kapasitas user yang akan didaftarkan. Untuk membuat UserLogin Baru lakukan langkah seperti berikut. 1. Expand pada Security 2.
Klik kanan pada Logins pilih New Login
3. Pada dialog pengisian Nama User sebagai contoh ketik User Login
: zidan
Password
: zidan
4. Pilih SQL Server Authentication 5. Pilih Default Database : LATDB Sebagai contoh user atas nama zidan ini adalah jenis user biasa yang haknya hanya dapat menginput, dan mengedit table Pelanggan dan table Pemasok.
42
6. Kita tidak perlu memilih Server Roles, langsung klik ke Database Access. Untuk penjelasan mengenai Server Roles akan dijelaskan dalam sub pembasan tersendiri.
7. Cheklist database yang diizinkan untuk di akses oleh zidan, sebagai contoh LATDB
43
8. Pada Confirm Password masukan password sama seperti diatas : zidan
9. klik OK 10. Maka kita barusan saja sudah berhasil menambahkan user baru dengan nama zidan.
44
Memberikan Hak pada User Setelah User Login dibuat selanjutnya kita akan berikan hak-hak apa saja yang perlu kita berikan kepada User. Sebagai contoh kita akan memberikan hak kepada user yang pada pembahasan sebelumnya sudah kita buat. 1. Silakan anda ekspand database LATD, pilih Users, akan terlihat dua user yang mempunyai hak untuk menggunakan database pada list user yaitu sa dan zidan. 2. Klik kanan pada user zidan 3. Pilih All Tasks pilih Manage Permissions…
4. Pada contoh kali ini kita akan berikan hak kepada zidan untuk mengakses table barang & pelanggan kedua-duanya hanya bisa select, insert dan update.
45
Klik OK
Sekarang kita akan membuktikan cara kerja user yang baru saja kita atur pemberian haknya dengan cara Login ke SQL Server dengan user tersebut. 1. Klik kanan pada Local Server, pilih Edit SQL Server Registration properties …
46
2. Ketikan pada Login Name : zidan, Password : zidan, kemudian klik OK
Jika muncul konfirmasi bahwa kita akan memutuskan koneksi dengan user sebelumnya dan diganti koneksinya dengan user baru kita jawab Yes
Sekarang kita dapat membuktikan cara kerja user yang barusan kita gunakan untuk Login 1. Silakan anda coba buat Database baru, atau coba membuat table baru, maka hak itu tidak dapat dilakukan lagi. 2. Sekarang coba anda buka table Pelanggan, kemudian lakukan pengisian atau perubahan. Kita dapat melakukan penambahan dan perubahan pada table tersebut. Tetapi coba anda hapus salah satu barisnya, maka akan muncul pesan bahwa anda tidak mempunyai hak untuk menghapus baris.
47
DTS Import/Export Wizard Pada seluruh Data Transformation Service (DTS) tools, DTS Import/Export Wizard mempunyai metode yang begitu mudah dalam melakukan pengcopyan data yang bersumber dari sesama OLE DB. Setelah berhasil melakukan keneksi data ke sumber atau tujuan, anda dapat memilih data untuk di import atau export dan menjalankan proses transformasi data dengan proses copy. Dengan menggunakan DTS Import/Export Wizard kita dapat melakukan koneksi kebeberapa sumber data seperti berikut: 1. Ke hampir semua sumber data dari OLEDB dan ODBC 2. Text File 3. Microsoft SQL Server yang terkoneksi 4. Oracle & Informix database (anda harus memiliki Client software Oracle & Informix yang terinstal) 5. Microsoft Excel Spreadsheets 6. Microsoft Access dan Microsoft Foxpro Database 7. dBase atau Paradox Database
Melakukan Import Data Untuk melakukan Import data dapat dilakukan melalui menu Tools, Pilih Data Transformation Service pilih Import Data
48
Atau melalui icon Tables, pilih All Task pilih Import Data, akan muncul DTS Import/Export Wizard Klik Next untuk melanjutkan Import Data.
Sebagai contoh kita akan melakukan Import dari database Access Nwind yang ada di c:\Program Files\Microsoft Visual Studio\VB98 Karena database sumber yang akan kita import Microsoft Access, maka DATA SOURCE kita rubah menjadi Microsoft Access
Klik Next
49
Klik Next
Pada Dialog berikutnya sebagai target Import kita akan copykan ke Database Latihan. Kemudian pilih table yang akan kita import, sebagai contoh table Customers, Order Details, Orders dan Products. Chek list ke empat table tersebut kemudian klik Next.
50
Klik Next sampai muncul dialog Finish Maka proses import akan dijalankan dan muncul konfirmasi 4 table sudah berhasil di Import
51
Dialog Konfirmasi table berhasil di Import
Lakukan Refresh pada Tables, maka hasil table yang diimport akan terlihat sudah masuk kedalam database.
52
53
Export Data Untuk melakukan Export secara teknik hampir sama dengan Import, hanya perlakuanya yang berbalik yaitu sumbernya dari Microsoft SQL Server dan Targetnya adalah bisa Microsoft SQL server atau database lainya, sebagai contoh berikut ini langkah untuk melakukan Export data 1. Lakukan Klik kanan pada area Enterprise Manager 2. Pilih All Tasks 3. Pilih Export Data,
Klik Next
Data Source : pilih Microsoft OLE DB Provider SQL Server,
54
kemudian tentukan sumber database yang akan di export datanya, sebagai contoh database yang digunakan adalan : “Latihan”
Choose a Destination : tentukan kemana data akan di copykan, sebagai contoh akan di export ke Microsoft Access dengan nama file “dataku.mdb”, klik Next
Pilih “Copy table(s) and view(s) from data source database, klik Next
55
Pilih table yang akan di export dengan memberi tanda cheklist pada kotak Source untuk memilih satu persatu, atau klik Select All untuk memimilih semua table untuk di export, selanjutnya klik Next.
Dialog konfirmasi yang menginformasikan proses export selesai, klik Finish
56
Progress Export data akan terlihat dan konfirmasi bahwa data yang di export telah berhasil akan ditampilkan seperti terlihat pada gambar diatas.
Berikut ini adalah hasil export ke Microsoft Access
57
Backup Database Backup data adalah memindahkan atau menyalin kumpulan informasi (data) yang tersimpan di dalam
hardisk
komputer
yang biasanya dilakukan
dari
satu lokasi/perangkat
lokasi/perangkat lain. Untuk melakukan backup data berikut ini langkahnya : 1. Klik kanan pada area Databases pilih All Tasks, kemudian pilih Backup Database
2. Maka akan muncul dialog sebagai berikut
58
ke
3. Backup : Pilih Database – Complete pada pilihan Backup untuk membackup secara keseluruhan sebuah database. 4. Destination : Tentukan kemana database akan di backup dengan menentukan lokasi penyimpanan dan nama file backup, lakukan dengan mengklik tombol Add
Setelah menentukan lokasi dan nama file selanjutnya klik OK
59
Klik OK untuk menyelesaikan proses Backup 5. Overwrite : a. Append to media : Menambahkan backup ke media yang ada. Isi sebelumnya media tetap terjaga, dan backup baru ditulis setelah akhir dari backup terakhir pada media. b. Overwrite existing media : Menimpa media yang sudah ada sebelumnya dengan backup yang baru, 6. Schedule : Melakukan backup dengan menentukan jadwal yang akan dilakukan secara otomatis sesuai dengan jadwal yang di buat, untuk melukan penjadwalan lakukan cheklist Schedule, kemudian klik (...)
One time : sebagai contoh backup akan dilakukan otomatis pada tanggal 13/07/2010 puluk 12:57:24, dan hanya dilakukan satu kali pada waktu tersebut.
Requiring :
60
Backup akan dilakukan secara berulang sesuai dengan waktu yang kita tentukan, Pilih Requiring kemudian klik tombol Change untuk mengatur jadwal pengulangan backup
Bakcup dapat dilakukan setiap hari, minggu atau bulan
61
Restore Database Proses pengembalian database dari file backup yang sudah dilakukan sebelumnya, dengan dilakukan restore maka isi data yang sedang digunakan akan ditimpa dengan isi data dari backup. Untuk melakukan restore database, lakukan langkah berikut : 1. Dari area databases, lakukan klik kanan kemudian pilih All tasks, pilih Restore database
Maka akan muncul dialog sebagai berikut :
62
a. Restore as database : pilih database yang akan direstore datanya b. Restore :
Pilih ”Database” untuk menampilkan daftar backup database yang sudah dilakukan
Pilih ”From Device” jika restore akan dilakukan dari Device yang berbeda misalnya dari External Harddisk, atau dari CD, dll
c. Untuk selanjutnya lakukan pilihan nama file backup yang akan direstore kemudian klik
OK
63
Attach Database Attach, untuk meregistrasikan kembali database (MDF dan LDF file) ke SQL server, baik itu hasil copy an dari komputer lain ataupun dari komputer yang sama. Untuk melakukan attach database lakukan langkah berikut : 1. Lakukan klik kanan pada area database, pilih All tasks, pilih Attach Database
Akan muncul dialog seperti berikut :
Selanjutnya klik tombol (…) untuk memilih lokasi dan nama database yang akan di attach, sebagai ilustrasi dapat dilihat pada contoh gambar berikut :
64
Klik OK untuk melanjutkan.
65
BAB 4 TSQL Query Analyzer SQL Query Analyzer adalah suatu alat grafis yang interaktif yang memungkinkan seorang database administrator atau developer untuk menulis query, mengeksekusi berbagai query secara serempak, menampilkan hasil, menganalisa rencana query dan menerima bantuan untuk miningkatkan performen query. Untuk menjalankan query analyzer dapat dilakukan melalui Start Menu, atau melalui Tools Entreprise Manager. Berikut ini menjalankan query analyzer melalui menu start.
Setelah dijalankan Query analizer akan muncul dialog untuk Login ke SQLServer seperti tampilan berikut:
66
Jika Koneksi menggunakan SQL Authentication masukan Login name yang sudah terdaftar berikut dengan Password nya.
Atau
dapat
juga
menggunakan
Windows
Authentication untuk Login tanpa Login Name dan password.
Setelah berhasil Login maka anda akan masuk ke lembar kerja Query.
Area Pengetikan Query dan Tampilan Hasil Query
Gambar Lembar kerja Query Analizer
67
Berikut ini tombol perintah yang dapat digunakan dalam Query Analyzer
Membuat Query Baru, dapat juga membuat quary baru dengan menggunakan template yang sudah ada. Membuka File Query. Menyimpan Query. Menyisipkan Template Query. Membersihkan lembar kerja penulisan Query. Mode tampilan hasil eksekusi query, dapat ditampilkan dalam bentuk Grid, Text atau File. Menguji Statement Query apakah sudah valid atau masih ada kesalahan dalam penulisan. Mengeksekusi Query pada lembar kerja Query, eksekusi dapat juga dilakukan hanya sebagian Statement saja dengan cara memberikan highlight atau blok pada statement yang akan dieksekusi. List Pemilihan Database yang akan digunakan. Menampilkan Estimated Execution Plan Menampilkan atau menyembunyikan Object Browser Pencarian Object pada Server meliputi Database, Table, View,
68
Procedure, Trigger dll. Menampilkan atau menyembunyikan hasil eksekusi
Berikut ini contoh pengetikan sebuah query pada lembar kerja query, sebagai contoh membuka database Northwind salah satu database yang sudah ada dalam SQLServer.
Jalankan dengan Tombol Eksekusi
atau dapat juga dilakukan dengan menekan tombol F5.
Dengan perintah diatas maka database yang aktif saati ini adalah database Northwind.
Klik tombol Object Browser
untuk menampilkan daftar object yang ada pada SQLServer. Klik
ekspan (+) di database Northwind pada Object Browser, maka akan terlihat daftar table yang ada pada database Northwind. Coba tampilkan isi salah satu table dengan menggunakan statemen SELECT. Blok statemen yang akan dieksekusi kemudian tekan tombol F5 untuk eksekusi, maka secara lengkap akan tampak pada gambar berikut ini:
69
SELECT Clause Menetapkan kolom-kolom yang akan dikembalikan atau ditampilkan dengan query.
Syntax SELECT [ ALL | DISTINCT ] [ TOP n [ PERCENT ] [ WITH TIES ] ] < select_list > < select_list > ::= { * | { table_name | view_name | table_alias }.* |
{ column_name | expression | IDENTITYCOL | ROWGUIDCOL } [ [ AS ] column_alias ]
| column_alias = expression } [ ,...n ]
70
Menampilkan Data Dengan Select Pada contoh penggunaan klausa Select dibawah ini, kita akan menggunakan database Northwind sebagai media untuk latihan. Anda dapat mengaktifkan database Northwind melalui Toolbar pemilihan database
, atau pada lembar kerja query anda dapat
menggunakan statement : USE Northwind
1. Menampilkan kolom tertentu dari sebuah table Untuk menampilkan kolom tertentu, anda dapat menuliskan nama kolom tertentu yang diinginkan yang ada pada table. Berikut ini contoh menampilkan kolom tertentu dari table Customers pada database Northwind. Select CustomerID,CompanyName,Country From Customers
Gambar menampilan kolom tertentu
71
2. Menampilkan kolom tertentu dari sebuah table dengan kriteria Untuk menampilkan isi table dengan kriteria tertentu dapat menggunakan klausa WHERE, seperti contoh dibawah ini menampilan table Customers untuk Country=’USA’
Select CustomerID,CompanyName,Country From Customers Where Country='USA'
Jumlah Baris
Pada gambar terlihat data yang ditampilkan hanya untuk yang country nya ‘USA’ dengan jumlah data 13 baris.
3. Menampilkan sebuah table menggunakan kriteria dengan klausa OR Jika kita ingin menampilkan suatu data dengan kriteria salah satu nilai yang sesuai yang ditampilkan, kita dapat menggunakan klausa OR dalam penerapanya. Berikut ini contoh menampilkan data customers untuk yang Country nya ‘USA’ atau ‘Germany’ Select CustomerID,CompanyName,Country From Customers Where Country='USA' or Country='Germany'
72
4. Menampilkan sebuah table dengan mengurutkan berdasarkan kolom tertentu menggunakan ORDER BY.
Kita dapat menampilkan data secara terurut baik secara ascending atau descending berdasarkan satu atau lebih kolom yang kita inginkan. Contoh dibawah ini menampilkan data customers dengan diurutkan berdasarkan CompanyName secara ascending, secara default jika tidak disebutkan jenis pengurutan pada klausa ORDER BY maka akan ditampilkan secara ascending. Select CustomerID,CompanyName,Country From Customers Where Country='USA' or Country='Germany' Order By CompanyName
73
Pada tampilan gambar terlihat menampilkan data customers berdasarkan kriteria Country USA atau Germany dan diurutkan berdasarkan Company Name secara Ascending, sementara untuk mengurutkan secara Descending dapat ditambahkan ungkapan DESC dibelakan statement seperti contoh berikut
Select CustomerID,CompanyName,Country From Customers Where Country='USA' or Country='Germany' Order By CompanyName Desc
74
Terlihat pada gambar data sudah terurut secara Descending.
5. Menampilkan table dengan kriteria LIKE Kriteria dengan menggunakan LIKE hanya dapat dilakukan untuk kolom yang bertype string, yaitu menyaring data berdasarkan kolom yang memiliki unsur huruf sesuai dengan keingingan. Kita dapat menambahkan symbol % untuk huruf setelah atau huruf sebelum kriteria. Contoh dibawah ini menampilkan data customers untuk yang Country nya diawali dengan huruf A, sedangkan huruf kedua dan seterusnya bisa semua huruf.
Select CustomerID,CompanyName,Country From Customers Where Country Like 'A%'
75
Pada gambar terlihat data yang ditampilkan hanya yang Country nya berawalan A saja dimana data yang tersaring adalah 5 baris. Contoh berikut ini menampilkan data Customers untuk yang Country nya berakhiran A, sedangkan huruf sebelumnya sampai dengan dijit pertama bisa semua huruf.
Select CustomerID,CompanyName,Country From Customers Where Country Like '%A'
76
Pada gambar terlihat data yang ditampilkan hanya yang Country nya berakhiran A saja dimana data yang tersaring adalah 25 baris. Contoh berikut ini menampilkan data Customers untuk yang Country nya mengandung huruf A, SELECT CustomerID,CompanyName,Country FROM Customers WHERE Country LIKE '%A%'
77
6. Menampilkan perhitungan kolom dengan menggunakan ALIAS Selain menampilkan kolom pada table, kita dapat menampilkan proses perhitungan pada kolom yang memiliki type data numerik dan memberikan nama baru dari hasil perhitungan kolom. Contoh dibawah ini menghitung perkalian antara UnitPrice*UnitsInStock ditampung kedalam nama alias Amount Select ProductID,ProductName,UnitPrice,UnitsInStock, UnitPrice*UnitsInStock as Amount From Products
Tampak pada gambar hasil perkalian antara UnitPrice * UnitsInStock yang ditampug dalam sebuah kolom dengan nama Amount. Penamaan dengan Alias ini tidak selalu diterapkan untuk kolom yang memiliki perhitungan, untuk kolom yang tidak memiliki perhitungan pun dapat dilakukan dalam rangka memberikan nama lain selain nama asli dari kolom.
78
7. Menampilkan data sesuai denga kriteria yang ada pada list dengan klausa IN Kriteria ini dapat dilakukan untuk menampilkan data dengan kriteria yang sesuai dengan list, list dapat ditulis secara langsung atau diambil dari list table lain dengan menggunakan klausa Select. Contoh dibawah ini menampilkan data Customers untuk yang country nya Argentina, Venezuela dan Brazil. Select CustomerID,ContactName,Country From Customers Where Country IN ('Argentina','Venezuela','Brazil')
79
8. Menampilkan data dengan kriteria numerik Untuk penggunaan kriteria numerik, kolom yang diberikan kriteria harus bertype numerik, dan kriterianya pun kita berikan dalam bentuk numerik, dan bisa dituliskan tanpa menggunakan tanda (‘) diantara nilai kriteria. Contoh dibawah ini menampilkan data products untuk yang UnisInStock < 10
Select ProductID,ProductName,UnitPrice,UnitsInStock, UnitPrice*UnitsInStock as Amount From Products Where UnitsInStock <10
80
9. Menampilkan data dengan kriteria numerik menggunakan BETWEEN Contoh dibawah ini menampilkan data products dengan kriteria UnitsInStock yang memiliki nilai antara 10 sampai dengan 20. Select ProductID,ProductName,UnitPrice,UnitsInStock, UnitPrice*UnitsInStock as Amount From Products Where UnitsInStock Between 10 and 20
Penulisan diatas dapat dilakukan juga dengan cara seperti berikut :
Select ProductID,ProductName,UnitPrice,UnitsInStock, UnitPrice*UnitsInStock as Amount From Products Where UnitsInStock>=10 and UnitsInStock<=20
81
10. Menampilkan data dengan kriteria numerik menggunakan OR Contoh dibawah ini menampilkan data products dengan kriteria UnitsInStock yang memiliki nilai 10 atau 20.
Select ProductID,ProductName,UnitPrice,UnitsInStock, UnitPrice*UnitsInStock as Amount From Products Where UnitsInStock=10 or UnitsInStock=20
82
11. Menampilkan data dengan kriteria numerik menggunakan NOT BETWEEN Contoh dibawah ini menampilkan data products dengan kriteria UnitsInStock yang tidak memiliki nilai antara 10 dan 20. Select ProductID,ProductName,UnitPrice,UnitsInStock, UnitPrice*UnitsInStock as Amount From Products Where UnitsInStock NOT Between 10 and 20
83
12. Menampilkan data yang memiliki type datetime atau smalldatetime Contoh dibawah ini menampilkan data table Orders, dimana pada sesi latihan selanjutnya akan mensimulasikan bagaimana penggunaan kriteria dengan menggunakan type datetime atau smalldatetime
13. Menampilkan data dengan penulisan kriteria tanggal penuh. Contoh dibawah ini menampilkan data table Orders, untuk yang OrderDate antara ‘1 Jan 1997’ sampai dengan ’31 Jan 1997’
Select OrderID,CustomerID,OrderDate From Orders Where OrderDate>='1 Jan 1997' and OrderDate<='31 Jan 1997'
84
Perintah diatas akan sama hasilnya dengan penulisan seperti berikut ini Select OrderID,CustomerID,OrderDate From Orders Where OrderDate Between '1 Jan 1997' and '31 Jan 1997'
85
14. Menampilkan data dengan kriteria tanggal menggunakan fungsi Month dan Year. Contoh dibawah ini menampilkan data Customers, untuk Customers yang melakukan pemesanan / order pada bulan Juli tahun 1996.
Select CustomerID,ContactName,Country From Customers Where CustomerID IN (Select CustomerID From Orders Where Year(OrderDate)=1996 and Month(OrderDate)=7)
86
15. Menampilkan data dengan menyembunyikan data yang duplikat dengan DISTINCT Kita dapat menampilkan data dengan hanya menampilkan 1 data saja dari data yang memiliki duplikasi atau data yang bernilai sama dengan menggunakan DISTINCT pada satu kolom. Sebagai contoh kita akan menampilkan data secara normal tanpa menggunakan DISTINCT, kemudian kita akan bandingkan dengan perintah menampilkan data dengan menggunakan DISTINCT.
Dapat dilihat pada gambar data customers untuk kolom Country masih menampilkan nilai yang memiliki duplikasi.
87
Pada contoh dibawah ini hasil data menggunakan DISTINCT, dimana tidak lagi tampil nilai yang memiliki duplikasi.
88
16. Menampilkan data dengan pengelompokan menggunakan GROUP BY Kita dapat menampilkan data dengan dikelompokan berdasarkan satu atau beberapa kolom menggunakan klausa GROUP BY. Dengan klausa ini data akan ditampilkan terkelompok menjadi satu baris untuk baris yang memiliki nilai sama. Contoh dibawah ini menampilkan data tidak di group kan hanya diurutkan berdasarkan Country
Select Country,City From Customers Order By Country
89
Terlihat pada gambar berikutnya data akan terkelompok menjadi satu baris untuk baris yang memiliki nilai yang sama setelah menggunakan klausa GROUP BY
90
17. Menampilkan data menggunakan GROUP BY dan kriteria Contoh menampilkan data dengan kriteria LIKE, dikelompokan berdasarkan Country dan City serta diurutkan berdasarkan Country dan City
91
18. Menampilkan data menggunakan fungsi Aggregate Fungsi Aggregate seperti (SUM, AVG, COUNT, COUNT(*) , MAX dan MIN) menghasilkan ringkasan nilai dari sebuah query. Contoh dibawah ini menghitung Jumlah, Rata-rata, Nilai Tertinggi dan nilai terendah dari kolom UnitsInStock pada table Products
Select SUM(UnitsInStock) As Total_stock, Avg(UnitsInStock) As Rata2_Stock, Max(UnitsInStock) As Stock_Tertinggi, Min(UnitsInStock) As Stock_Terendah From Products
Hasil statement diatas akan terlihat seperti tampilan berikut : Total_stock Rata2_Stock Stock_Tertinggi Stock_Terendah ----------- ----------- --------------- -------------3119
40
125
0
(1 row(s) affected)
Contoh diatas menghasilkan mengeksekusi dari seluruh baris, sementara jika kita ingin mendapatkan nilai aggregate berdasarkan kolom tertentu, kita dapat menuliskan kolom yang akan menjadi dasar perhitungan sebuah aggregate.
92
Contoh menghitung Jumlah, Rata-rata, Nilai Tertinggi dan nilai terendah dari kolom UnitsInStock pada table Products berdasarkan CategoryID
Select CategoryID,SUM(UnitsInStock) As Total_stock, Avg(UnitsInStock) As Rata2_Stock, Max(UnitsInStock) As Stock_Tertinggi, Min(UnitsInStock) As Stock_Terendah From Products Group By CategoryID
Hasil statement diatas akan terlihat seperti tampilan berikut :
93
19. Menampilkan data dari beberapa table yang terelasi dengan INNER JOIN INNER JOIN menampilkan data yang diambil dari beberapa table yang diganungkan, dimana nilai-nilai
pada
kolom
dihubungkan
dan
dibandingkan
dengan
suatu
operator
pembandingan. Contoh berikut ini menampilkan data OrderID, OrderDate dari table Orders, kemudian ContactName dan Country dari table Customers, relasi antara kedua table tersebut menggunakan kolom CustomerID.
Select OrderID,OrderDate,ContactName,Country FROM Orders Inner Join Customers ON Orders.CustomerID=Customers.CustomerID
94
Contoh berikut ini penggunaan INNER JOIN dengan menggunakan kriteria dan pengurutan yaitu menampilkan OrderID untuk yang Country nya ‘USA’.
Select OrderID,OrderDate,ContactName,Country FROM Orders Inner Join Customers ON Orders.CustomerID=Customers.CustomerID Where Country='USA' Order By OrderDate
95
20. Menampilkan data dalam bentuk CROSSTAB Contoh berikut ini menampilkan data jumlah Order yang terjadi pada setiap tahun untuk tiap-tiap negara, bentuk tahun disajikan dalam bentuk kolom. Berikut ini contoh sebagian data pada table Customers. Contoh data table Customers CustomerID ALFKI ANATR ANTON AROUT BERGS BLAUS BLONP BOLID BONAP BOTTM BSBEV CACTU
CompanyName Alfreds Futterkiste Ana Trujillo Emparedados y helados Antonio Moreno Taquería Around the Horn Berglunds snabbköp Blauer See Delikatessen Blondesddsl père et fils Bólido Comidas preparadas Bon app' Bottom-Dollar Markets B's Beverages Cactus Comidas para llevar
Dst… Contoh data Table Orders OrderID 10248 10249 10250 10251 10252 10253 10254 10255 10256 10257 10258 10259 10260 10261
CustomerID VINET TOMSP HANAR VICTE SUPRD HANAR CHOPS RICSU WELLI HILAA ERNSH CENTC OTTIK QUEDE
EmployeeID 5 6 4 3 4 3 5 9 3 4 1 4 4 4
OrderDate 04/07/1996 05/07/1996 08/07/1996 08/07/1996 09/07/1996 10/07/1996 11/07/1996 12/07/1996 15/07/1996 16/07/1996 17/07/1996 18/07/1996 19/07/1996 19/07/1996
Dst…
96
Country Germany Mexico Mexico UK Sweden Germany France Spain France Canada UK Argentina
Dari data contoh diatas akan disajikan data dalam bentuk Country
1996
1997
1998
Total
Berikut adalah Penulisan Query yang lakukan
Select Country, Sum(Case Year(OrderDate) When 1996 Then Sum(Case Year(OrderDate) When 1997 Then Sum(Case Year(OrderDate) When 1998 Then Sum(Case Year(OrderDate) When 1996 Then Sum(Case Year(OrderDate) When 1997 Then Sum(Case Year(OrderDate) When 1998 Then FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID Group By Country Order By Total Desc
1 1 1 1 1 1
Else Else Else Else Else Else
0 0 0 0 0 0
END) END) END) END) END) END)
as as as + + as
'1996', '1997', '1998',
Total
Berikut ini adalah hasil dari penulisan query diatas. Country --------------USA Germany Brazil France UK Venezuela Austria Sweden Canada Mexico Italy Spain Finland Belgium Ireland Switzerland Denmark Argentina Portugal Poland Norway
1996 ----------23 24 13 15 10 8 8 6 4 9 3 6 4 2 5 3 3 0 4 1 1
1997 ----------60 64 42 39 30 20 21 17 17 12 15 5 13 7 10 8 11 6 7 2 2
(21 row(s) affected)
97
1998 ----------39 34 28 23 16 18 11 14 9 7 10 12 5 10 4 7 4 10 2 4 3
Total ----------122 122 83 77 56 46 40 37 30 28 28 23 22 19 19 18 18 16 13 7 6
Contoh berikut ini menampilkan data jumlah Quantity yang di Order yang terjadi pada setiap bulan pada tahun 1997 untuk tiap-tiap negara, bentuk bulan disajikan dalam bentuk kolom. Berikut ini contoh sebagian data pada table Customers. CustomerID ALFKI ANATR ANTON AROUT BERGS BLAUS BLONP BOLID BONAP BOTTM BSBEV CACTU
CompanyName Alfreds Futterkiste Ana Trujillo Emparedados y helados Antonio Moreno Taquería Around the Horn Berglunds snabbköp Blauer See Delikatessen Blondesddsl père et fils Bólido Comidas preparadas Bon app' Bottom-Dollar Markets B's Beverages Cactus Comidas para llevar
Dst…
Contoh data Table Orders OrderID 10400 10401 10402 10403 10404 10405 10406 10407 10408 10409 10410 10411 10412 10413
CustomerID EASTC RATTC ERNSH ERNSH MAGAA LINOD QUEEN OTTIK FOLIG OCEAN BOTTM BOTTM WARTH LAMAI
EmployeeID 1 1 8 4 2 1 7 2 8 3 3 9 8 3
OrderDate 01/01/1997 01/01/1997 02/01/1997 03/01/1997 03/01/1997 06/01/1997 07/01/1997 07/01/1997 08/01/1997 09/01/1997 10/01/1997 10/01/1997 13/01/1997 14/01/1997
Dst…
98
Country Germany Mexico Mexico UK Sweden Germany France Spain France Canada UK Argentina
Contoh Table Order Details OrderID
ProductID
UnitPrice
Quantity
Discount
10248
11
14
12
0
10248
42
9,8
10
0
10248
72
34,8
5
0
10249
14
18,6
9
0
10249
51
42,4
40
0
10250
41
7,7
10
0
10250
51
42,4
35
0,15
10250
65
16,8
15
0,15
Dst… Berikut adalah Penulisan Query yang lakukan
Use Northwind Select Country, Sum(Case month(OrderDate) Sum(Case month(OrderDate) Sum(Case month(OrderDate) Sum(Case month(OrderDate) Sum(Case month(OrderDate) Sum(Case month(OrderDate) Sum(Case month(OrderDate) Sum(Case month(OrderDate) Sum(Case month(OrderDate) Sum(Case month(OrderDate) Sum(Case month(OrderDate) Sum(Case month(OrderDate) Sum(Case Sum(Case Sum(Case Sum(Case Sum(Case Sum(Case Sum(Case Sum(Case Sum(Case Sum(Case Sum(Case Sum(Case Total
month(OrderDate) month(OrderDate) month(OrderDate) month(OrderDate) month(OrderDate) month(OrderDate) month(OrderDate) month(OrderDate) month(OrderDate) month(OrderDate) month(OrderDate) month(OrderDate)
When When When When When When When When When When When When
1 2 3 4 5 6 7 8 9 10 11 12
Then Then Then Then Then Then Then Then Then Then Then Then
Quantity Quantity Quantity Quantity Quantity Quantity Quantity Quantity Quantity Quantity Quantity Quantity
else else else else else else else else else else else else
0 0 0 0 0 0 0 0 0 0 0 0
END)as END)as END)as END)as END)as END)as END)as END)as END)as END)as END)as END)as
When When When When When When When When When When When When
1 2 3 4 5 6 7 8 9 10 11 12
Then Then Then Then Then Then Then Then Then Then Then Then
Quantity Quantity Quantity Quantity Quantity Quantity Quantity Quantity Quantity Quantity Quantity Quantity
else else else else else else else else else else else else
0 0 0 0 0 0 0 0 0 0 0 0
END)+ END)+ END)+ END)+ END)+ END)+ END)+ END)+ END)+ END)+ END)+ END)as
99
Jan, Feb, Mar, Apr, Mei, Jun, Jul, Agu, Sep, Okt, Nop, Des,
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID Where Year(OrderDate)=1997 Group By Country
Berikut ini adalah hasil Query diatas setelah dijalankan
100
Create Database Membuat database baru atau menyertakan database dari file yang sudah dibuat sebelumnya. Syntax CREATE DATABASE database_name [ ON [ < filespec > [ ,...n ] ] [ , < filegroup > [ ,...n ] ] ] [ LOG ON { < filespec > [ ,...n ] } ] [ COLLATE collation_name ] [ FOR LOAD | FOR ATTACH ] < filespec > ::= [ PRIMARY ] ( [ NAME = logical_file_name , ] FILENAME = 'os_file_name' [ , SIZE = size ] [ , MAXSIZE = { max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ] ) [ ,...n ] < filegroup > ::= FILEGROUP filegroup_name < filespec > [ ,...n ]
Arguments database_name Nama dari database baru. Nama database harus unik dalam suatu server, database_name dapat ditulis dalam 128 karakter maksimum. ON Menetapkan file database yang akan disimpan menggunakan bagian-bagian data pada database yang didifinisikan, penulisan tiap bagian difinisi dibatasi dengan koma.
101
LOG ON Menetapkan file log yang akan disimpan menggunakan bagian-bagian data pada database yang didifinisikan, penulisan tiap bagian difinisi dibatasi dengan koma.
Contoh Contoh dibawah ini membuat database baru dengan lokasi penyimpanan d:\Database, dengan menentukan size, maxsize dan filegrowth masing-masing untuk file database dan file log nya. CREATE DATABASE Penjualan ON ( NAME = Penjualan_dat, FILENAME = 'd:\Database\Penjualandat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = 'Penjualan_log', FILENAME = 'd:\Database\Penjualanlog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GO
Hasil akan terlihat seperti tampilan berikut : The CREATE DATABASE process is allocating 10.00 MB on disk 'Penjualan_dat'. The CREATE DATABASE process is allocating 5.00 MB on disk 'Penjualan_log'.
Pesan diatas menunjukan proses pembuatan database sudah berhasil dilakukan.
102
Gambar dibawah ini menampilkan file yang terbentuk setelah perintah pembuatan database dieksekusi.
Contoh dibawah ini membuat database baru tanpa menspesifikan dengan atribut pembuatan databasae
CREATE DATABASE Mahasiswa
Hasil akan terlihat seperti tampilan berikut : The CREATE DATABASE process is allocating 0.63 MB on disk 'Mahasiswa'. The CREATE DATABASE process is allocating 0.49 MB on disk 'Mahasiswa_log'.
Pembuatan database diatas secara defult akan menyimpan lokasi file pada : C:\Program Files\Microsoft SQL Server\MSSQL\Data
103
Create Table Membuat table baru. Syntax CREATE TABLE [ database_name.[ owner ] . | owner. ] table_name ( { < column_definition > | column_name AS computed_column_expression | < table_constraint > ::= [ CONSTRAINT constraint_name ] } | [ { PRIMARY KEY | UNIQUE } [ ,...n ] )
Contoh Membuat table dengan cara sederhana dengan nama “Mhs”
Use Mahasiswa CREATE TABLE Mhs ( Nim nvarchar(10), Nama nvarchar(10), TglLahir smalldatetime, Telp nvarchar(15) )
Pada object Bowser akan terlihat table yang barusan kita buat
104
Alter Table Modifikasi sebuah table meliputi perubahan, penambahan dan penghapusan kolom. Syntax ALTER TABLE table { [ ALTER COLUMN column_name { new_data_type [ ( precision [ , scale ] ) ] [ COLLATE < collation_name > ] [ NULL | NOT NULL ] | {ADD | DROP } ROWGUIDCOL } ] | ADD { [ < column_definition > ] | column_name AS computed_column_expression } [ ,...n ] | [ WITH CHECK | WITH NOCHECK ] ADD { < table_constraint > } [ ,...n ] | DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ] | { CHECK | NOCHECK } CONSTRAINT { ALL | constraint_name [ ,...n ] } | { ENABLE | DISABLE } TRIGGER { ALL | trigger_name [ ,...n ] } }
Menambahkan kolom Alamat dengan type nvarchar(50) pada table “Mhs”
Use Mahasiswa ALTER TABLE MHS ADD Alamat varchar(50) Select * FROM Mhs
105
Menghapus kolom Alamat pada table “Mhs”
Use Mahasiswa ALTER TABLE MHS DROP COLUMN Alamat Select * From Mhs
Merubah panjang kolom Telp menjadi 20 pada table “Mhs”
Use Mahasiswa ALTER TABLE MHS ALTER COLUMN Telp varchar(20) Select * From Mhs
106
Drop Table Menghapus sebuah table yang didifinisikan berikut dengan datanya, index, trigger dan hak-hak penggunaan table yang terpasang pada table tersebut. Syntax DROP TABLE table_name Arguments table_name nama table yang akan dihapus. Contoh Use Mahasiswa DROP TABLE MHS
107
Insert Into Menginput data kedalam sebuah table baik seluruh kolom atau beberapa kolom yang ditentukan. Syntax INSERT [INTO] table_or_view [(column_list)] data_values Contoh Menginput kedalam table Mhs untuk seluruh kolom Use Mahasiswa CREATE TABLE Mhs ( Nim nvarchar(10), Nama nvarchar(10), TglLahir smalldatetime, Telp nvarchar(15) ) Insert Insert Insert Select
Into Mhs Values (‘0901001’,’Desylina’,’08 Dec 1988’,’98789879’) Into Mhs Values (‘0902001’,’M Zidan’,’23 Mar 1992’,’2343456’) Into Mhs Values (‘0903001’,’A Faiz’,’31 Aug 1985’,’5677890’) * From Mhs
Hasil akan terlihat seperti tampilan berikut : Nim ---------0901001 0902001 0903001
Nama ---------Desylina M Zidan A Faiz
TglLahir --------------------------1988-12-08 00:00:00 1992-03-23 00:00:00 1985-08-31 00:00:00
Telp -------------------98789879 2343456 5677890
(3 row(s) affected)
Menginput kedalam table Mhs untuk kolom NIM,Nama dan TglLahir Use Mahasiswa Insert Into Mhs (Nim,Nama,TglLahir) Values (‘0904001’,’Zahra’,’17 Sep 1982’) Select * From Mhs
108
Hasil akan terlihat seperti tampilan berikut : Nim Nama TglLahir Telp ---------- ---------- ------------------------ -----------------0901001 Desylina 1988-12-08 00:00:00 98789879 0902001
M Zidan
1992-03-23 00:00:00
2343456
0903001
A Faiz
1985-08-31 00:00:00
5677890
0904001
Zahra
1982-09-17 00:00:00
NULL
(4 row(s) affected)
Menginput kedalam table Mhs dari table lain yang mempunyai spesifikasi kolom yang sama. Use Mahasiswa CREATE TABLE MHS2 ( Nim nvarchar(10), Nama nvarchar(10), TglLahir smalldatetime, Telp nvarchar(15) ) Insert Into Mhs2 Values ('0801001','Ridwan','10 Jan 1988','1234567') Insert Into Mhs2 Values ('0801002','Firman','15 Feb 1988','7654321') Insert Into Mhs2 Values ('0801003','Ikhsan','20 Mar 1988','4567890') Insert Into MHS select * from MHS2 Select * From Mhs
Nim Nama TglLahir Telp ---------- ---------- ----------------------------- ---------------0901001 Desylina 1988-12-08 00:00:00 98789879 0902001
M Zidan
1992-03-23 00:00:00
2343456
0903001
A Faiz
1985-08-31 00:00:00
5677890
0904001
Zahra
1982-09-17 00:00:00
NULL
0801001
Ridwan
1988-01-10 00:00:00
1234567
0801002
Firman
1988-02-15 00:00:00
7654321
0801003
Ikhsan
1988-03-20 00:00:00
4567890
(7 row(s) affected)
109
Delete Menghapus baris pada table. Syntax DELETE [ FROM ] { table_name WITH ( < table_hint_limited > [ ...n ] ) | view_name | rowset_function_limited } [ FROM { < table_source > } [ ,...n ] ] [ WHERE { < search_condition > | { [ CURRENT OF { { [ GLOBAL ] cursor_name } | cursor_variable_name } ]} } ] Contoh Menghapus seluruh baris pada table MHS Use Mahasiswa DELETE FROM MHS SELECT * From MHS
Hasil akan terlihat seperti tampilan berikut : (7 row(s) affected)
Pesan diatas maksudnya adalah 7 baris sudah berhasil dihapus.
110
Menghapus berdasarkan kriteria Use Mahasiswa DELETE FROM MHS Where Nim=’0901001’ SELECT * From MHS
Hasil akan terlihat seperti tampilan berikut : (1 row(s) affected)
Pesan diatas maksudnya adalah 1 baris sudah berhasil dihapus yang sesuai dengan kriteria.
111
BAB 5 OPERATOR
Operator Operator merupakan suatu lambang untuk menetapkan suatu tindakan yang dilakukan pada satu atau lebih ungkapan. Operator terbagi menjadi beberapa jenis operator yaitu:
1. Arithmetic Operator Operator Aritmathic atau perhitungan melaksanakan operasi matematika atas dua ungkapan terhadap jenis data bertipe numerik. Operator
Keterangan
+
Penjumlahan
-
Pengurangan
*
Pengalian
/
Pembagian
%
Mencari nilai sisa dari suatu pembagian. contoh, 12 % 5 = 2 , sebab nilai sisa dari 12 dibagi 5 adalah 2.
Operator (+) dan (-) dapat juga melakukan operasi matematika untuk jenis data bertipe datetime dan smalldatetime.
112
Contoh:
Select X = 10 + 4 *5 / 2 – 5
Hasil proses diatas akan terlihat seperti berikut : X ----------------15
2. Assignment Operator SQL Server hanya memiliki satu operator penugasan atau pemberian nilai yaitu sama dengan (= ). Sebagai contoh memberikan nilai kepada variable @x dengan nilai 10.
Declare @x Int SET @x = 10
3. Comparison Operator / Operator Perbandingan Operator perbandingan menguji Ya atau Tidak nya dua ungkapan sama. Opator perbandingan dapat digunakan untuk seluruh jenis ungkapan kecuali ungkapan untuk tipe text, ntext atau image. Operator
Meaning
=
Sama dengan
>
Lebih Besar dari
<
Lebih Kecil dari
>=
Lebih Besar sama dengan dari
113
<=
Lebih kecil sama dengan dari
<>
Tidak sama dengan
!=
Tidak sama dengan (bukan SQL-92 standard)
!<
Tidak lebih kecil dari (bukan SQL-92 standard)
!>
Tidak lebih besar dari (bukan SQL-92 standard)
Contoh: Contoh berikut ini akan menampilkan data product untuk yang ProductID = 10 apabila nilai @MyProduct tidak sama dengan (<>) 0
USE Northwind GO DECLARE @MyProduct int SET @MyProduct = 10 IF (@MyProduct <> 0) SELECT * FROM Products WHERE ProductID = @MyProduct GO
4. Logical Operators
Logical Operator digunakan untuk menguji kebenaran beberapa kondisi, Logical Operator sama seperti operator perbandingan akan mengembalikan nilai dengan type Boolean dengan nilai TRUE atau FALSE. Operator
Meaning
ALL
TRUE jika seluruh set perbadingan bernilai benar
AND
TRUE jika kedua ungkapan Boolean bernilai benar
114
ANY
TRUE jika ada salah satu dari satu set perbandingan benar
BETWEEN
TRUE jika kondisi berada dalam suatu cakupan
EXISTS
TRUE jika subquery memiliki nilai baris
IN
TRUE jika nilai sama seperti dari salah satu daftar ungkapan
LIKE
TRUE jika kondisi menyerupai dengan kriteria
NOT
Kebalikan nilai dari hasil operator lain
OR
TRUE jika salah satu ungkapan bernilai benar
SOME
TRUE jika sebagian dari satu set perbandingan benar
IF Suatu kondisi untuk menentukan apakah suatu statement akan dieksekusi atau tidak, statement akan dieksekusi apabila Ekspresi Boolean bernilai TRUE, pilihan lain dapat menggunakan ELSE untuk melakukan alternatif statement yang akan dieksekusi apabila ekspresi boolean bernilai FALSE. Syntax IF Boolean_expression { sql_statement | statement_block } [ ELSE { sql_statement | statement_block } ] Arguments Boolean_expression Sebuah ungkapan yang akan mengembalikan nilai TRUE atau FALSE. {sql_statement | statement_block}
115
Statement atau Statement group TSQL, untuk menggunakan Statement group penulisan harus berada diantara BEGIN dan END.
Contoh: Contoh berikut ini kondisi bernilai FALSE, sehingga statement yang dijalankan adalah statement yang kedua.
Declare @a int set @a=10 if @a<10 print 'Nilai diatas 5' else Print 'Nilai Dibawah 5' Go
Hasil akan terlihat seperti tampilan berikut : Nilai dibawah 5
Contoh berikut ini kondisi bernilai TRUE sehingga statement yang dijalankan adalah blok statement yang pertama. Declare @a int set @a=10 IF @a>5 Begin set @a=@a+25 print @a END ELSE BEGIN set @a=@a-5 print @a END Go
Hasil akan terlihat seperti tampilan berikut : 35
116
Contoh berikut ini menampilkan data Orders tahun 1996, Jika pada tahun 1996 ada catatan transaksi maka informasi OrderID, OrderDate dan CustomerID akan ditampilkan, tetapi jika tahun 1996 tidak ada catatan transaksi maka akan menampilkan informasi OrderID, OrderDate dan CustomerID untuk yang tahun transaksinya diatas tahun 1996 Use Northwind IF(Select Count(OrderDate) From Orders Where Year(OrderDate)=1996) > 0 Select OrderID,OrderDate,CustomerID From Orders Where Year(OrderDate)=1996 ELSE Select OrderID,OrderDate,CustomerID From Orders Where Year(OrderDate)>1996 Go
Hasil akan terlihat seperti tampilan berikut :
OrderID
OrderDate
CustomerID
--------
-----------------------
----------
10248
1996-07-04 00:00:00.000
VINET
10249
1996-07-05 00:00:00.000
TOMSP
10250
1996-07-08 00:00:00.000
HANAR
10251
1996-07-08 00:00:00.000
VICTE
Dst … (152 row (s)
affected)
117
CASE Mengevaluasi daftar kondisi-kondisi dan mengembalikan salah satu dari beberapa ungkapan untuk hasil evaluasi benar. Syntax CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END Arguments input_expression
ungkapan yang akan dievaluasi ketika menggunakan CASE format.
WHEN when_expression
Ungkapan yang akan dibandingkan dengan input_expression.
THEN result_expression
Ungkapan yang akan dikembalikan apabila perbandingan input_expression dengan when_epression bernilai TRUE.
ELSE else_result_expression
Ungkapan alternatif yang akan dikembalikan apabila perbandingan input_expression dengan when_epression bernilai FALSE.
118
Contoh: Contoh berikut memberikan keterangan untuk UnitsInStock dengan ‘Stock Habis’ apabila jumlah UnitsInstock < 5, keterangan ‘Harus Order’ apabila UnitsInStock antara 5 sampai 15 dan memberikan keterangan ‘Stock Cukup’ apabila UnitsInStock>15
Use Northwind Select ProductID,ProductName,UnitsInStock, 'Keterangan'= Case When UnitsInStock <5 Then 'Stock Habis' When UnitsInStock Between 5 and 15 Then 'Harus Order' Else 'Stock Cukup' END From Products Go
Hasil akan terlihat seperti tampilan berikut : ProductID
ProductName
UnitsInStock Keterangan
---------
---------------------------------- ------------ -----------
1
Chai
39
Stock Cukup
2
Chang
17
Stock Cukup
3
Aniseed Syrup
13
Harus Order
4
Chef Anton's Cajun Seasoning
53
Stock Cukup
5
Chef Anton's Gumbo Mix
0
Stock Habis
6
Grandma's Boysenberry Spread
120
Stock Cukup
7
Uncle Bob's Organic Dried Pears
15
Harus Order
8
Northwoods Cranberry Sauce
6
Harus Order
9
Mishi Kobe Niku
29
Stock Cukup
10
Ikura
31
Stock Cukup
11
Queso Cabrales
22
Stock Cukup
12
Queso Manchego La Pastora
86
Stock Cukup
13
Konbu
24
Stock Cukup
14
Tofu
35
Stock Cukup
15
Genen Shouyu
39
Stock Cukup
Dst … (77 row(s) affected)
119
Contoh berikut memberikan nilai “United State of America’ untuk Country=’USA’ dan memberikan nilai “United Kingdoms” untuk Country=’UK’ sementara selain kondisi diatas diberikan nilai sesuai dengan nilai Country itu sendiri.
Use Northwind Select CustomerID,CompanyName,Negara= CASE Country When 'USA' Then 'United State of America' When 'UK' Then 'United Kingdoms' Else Country END FROM Customers Go
Hasil akan terlihat seperti tampilan berikut : CustomerID CompanyName
Negara
---------- ------------------------------------ ------------------ALFKI
Alfreds Futterkiste
Germany
ANATR
Ana Trujillo Emparedados y helados
Mexico
ANTON
Antonio Moreno Taquería
Mexico
AROUT
Around the Horn
United Kingdoms
BERGS
Berglunds snabbköp
Sweden
BLAUS
Blauer See Delikatessen
Germany
BOTTM
Bottom-Dollar Markets
Canada
BSBEV
B's Beverages
United Kingdoms
CACTU
Cactus Comidas para llevar
Argentina
DRACD
Drachenblut Delikatessen
Germany
DUMON
Du monde entier
France
EASTC
Eastern Connection
United Kingdoms
GOURL
Gourmet Lanchonetes
Brazil
GREAL
Great Lakes Food Market
United State of America
Dst … (91 row(s) affected)
120
Create Procedure Membuat sebuah prosedur tersimpan, akan tersimpan dalam daftar statement TSQL yang dapat digunakan tanpa dan dengan menggunakan parameter. Store Procedure juga dapat dibuat untuk dijalankan secara otomatis ketika SQL Server di aktifkan. Syntax CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
Contoh Membuat procedure untuk menampilkan data Customers pada database Northwind untuk informasi CustomerID, ContactName, Country dan City dengan nama ListCustomer.
USE NORTHWIND CREATE PROCEDURE ListCustomer AS Select CustomerID,ContactName,Country, City FROM Customers Go
Dari perintah diatas maka kita sudah memiliki Store Procedure baru dengan nama ListCustomer, untuk membuktikan bahwa procedure sudah terdaftar dalam koleksi TSQL dapat dilihat dengan perintah berikut ini :
select name from sysobjects Where name like 'L%'
121
Akan tampil informasi seperti berikut : name -------------------------------------------------ListCustomer (1 row(s) affected)
Untuk menjalankan Store Procedure dapat anda tuliskan nama procedure nya seperti contoh berikut : Exec ListCustomer atau cukup ketik ListCusomers
Perintah akan mengeksekusi statement yang ada pada procedure ListCustomer, yaitu menampilkan isi table Customers, secara tampilan akan terlihat seperti berikut ini : CustomerID ---------ALFKI ANATR ANTON AROUT Dst… (91 row(s)
ContactName ------------------------Maria Anders Ana Trujillo Antonio Moreno Thomas Hardy
Country --------------Germany Mexico Mexico UK
City --------------Berlin México D.F. México D.F. London
affected)
Contoh berikut ini membuat procedure untuk menampilkan data Customers pada database Northwind untuk kolom CustomerID, ContactName, Country dan City dengan nama ListCustomer2 dengan menggunakan parameter untuk menentukan data yang ditampilkan berdasarkan Country yang dipilih
USE NORTHWIND CREATE PROCEDURE ListCustomer2 @Negara varchar(15) AS Select CustomerID,ContactName,Country, City FROM Customers WHERE Country = @Negara Go
122
Berikut ini contoh cara menggunkan Procedure diatas, menampilkan data customers untuk yang Country nya USA
Listcustomer2 'Argentina'
Maka dari penulisan diatas akan tampil hasil seperti berikut : CustomerID ---------CACTU OCEAN RANCH
ContactName ---------------------------Patricio Simpson Yvonne Moncada Sergio Gutiérrez
Country --------------Argentina Argentina Argentina
City --------------Buenos Aires Buenos Aires Buenos Aires
(3 row(s) affected)
Pembuatan Procedure dengan menggunakan parameter dapat dilakukan dengan menggunakan lebih dari satu parameter.
123
Alter Procedure Memperbaharui Store Procedure yang sudah dibuat sebelumnya.
Syntax ALTER PROCEDURE procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ] Contoh Berikut ini akan memperbaharui Store Proceure ListCustomer2 dengan menambahkan klausa ORDER BY pada baris query yang sebelumnya. USE NORTHWIND ALTER PROCEDURE ListCustomer2 @Negara varchar(15) AS Select CustomerID,ContactName,Country, City FROM Customers WHERE Country = @Negara ORDER BY ContactName Go
124
Drop Procedure Menghapus satu atau lebih store procedure pada database yang sedang digunakan. Syntax DROP PROCEDURE { procedure } [ ,...n ]
Contoh Menghapus Procedure ListCustomer2 DROP Procedure ListCustomer2 GO
125
Create Trigger Membuat sebuah Trigger, sejenis Procedure tersimpan khusus yang dapat melakukan eksekusi secara otomatis ketika user melakukan pengolahan pada suatu table tertentu. Microsoft SQL Server mengijinkan membuat trigger dengan berbagai statemen seperti INSERT, UPDATE atau DELETE. Syntax CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] }] sql_statement [ ...n ] } } Untuk contoh penggunaan TRIGGER kita akan coba simulasikan kedalam sebuah database baru dengan nama Penjualan, dan berikut ini table yang akan kita gunakan. Pelanggan
126
Sebelumnya kita buat ketentuan yang akan kita terapkan berkenaan dengan table Pelanggan ini. Sebagai contoh kolom UserName akan terisi secara otomatis dengan nama User yang Login ke SQLServer dan LastUpdate akan terisi degan Tanggal dan jam ketika melakukan Penginputan.
Berikut Trigger untuk menerapkan keterangan diatas CREATE DATABASE PENJUALAN GO USE PENJUALAN GO Create Trigger TPelanggan On Pelanggan For Insert AS Declare @IDPel varchar(5) Select @IDPel = PelangganID from Inserted Update Pelanggan Set UserName=User_Name(), LastUpdate = Getdate() Where PelangganID=@IDPel Go
Jalankan Query diatas, maka sekarang kita mempunyai TRIGGER baru dengan nama Tpelanggan. Trigger diatas akan berkerja hanya apabila terjadi penambahan data saja, tidak untuk apabila terjadi perubahan data atau penghapusan data. Silaka isi table diatas melalui Entreprise Manager.
Akan terlihat UserName dan LastUpdate akan terisi secara otomatis begitu kursor pindah baris.
127
Pengisian dengan QueryAnalizer INSERT INTO PELANGGAN (PelangganID,PelangganNama,Kota,Telp,JumlahKredit,BatasanKredit) VALUES ('PL001','Desyina Koyansow','Jakarta','123457',0,15000000) Select * From Pelanggan
128
Alter Trigger Memperbaharui Trigger yang sudah dibuat sebelumnya. Syntax ALTER TRIGGER trigger_name ON ( table | view ) [ WITH ENCRYPTION ] { { ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } [ NOT FOR REPLICATION ] AS sql_statement [ ...n ] } | { ( FOR | AFTER | INSTEAD OF ) { [ INSERT ] [ , ] [ UPDATE ] } [ NOT FOR REPLICATION ] AS { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } sql_statement [ ...n ] } }
Ketika login dengan User lain kemudian kita lakukan perubahan terhadap isi table pelanggan, maka UserName dan LastUpdate tidak terjadi perubahan, masih terisi dengan data ketika pertama kali kita menginput. Hal ini terjadi karena ketika kita mendeklrasikan Trigger hanya menggunakan penerapan untuk FOR INSERT. Kita dapat menambahkan dengan Update dengan memodifikasi Trigger.
129
USE PENJUALAN GO ALTER Trigger TPelanggan On Pelanggan For Insert, Update AS Declare @IDPel varchar(5) Select @IDPel = PelangganID from Inserted Update Pelanggan Set UserName=User_Name(), LastUpdate = Getdate() Where PelangganID=@IDPel Go
Jalankan ulang Query diatas, kemudian lakukan perubahan terhadap table Pelanggan, sekarang fungsi UPDATE sudah bekerja dengan bukti UserName dan LastUdate akan menyesuaikan dengan nama User yang sedang Login dan Tanggal serta jam ketika mengupdate.
Drop Trigger Menghapus satu atau lebih TRIGGER pada database yang sedang aktif. Syntax DROP TRIGGER { trigger } [ ,...n ]
Contoh DROP TRIGGER TPenjualan
130
Begin Transaction Tanda dimulainya sebuah kegiatan pengolahan data. Setelah Begin Transaction anda dapat menuliskan beberapa perintah query yang berkenaan dengan pengisian, perubahan atau penghapusan isi sebuah atau beberapa table. Perintah Query benar-benar secara permanen akan memperbaharui table jika diakhiri dengan pernyataan COMMIT TRANSACTION, dan akan membatalkan instruksi yang diberikan dalam perintan Query jika diakhiri dengan pernyataan ROLLBACK TRANSACTION. Syntax BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable [ WITH MARK [ 'description' ] ] ] Contoh: Contoh berikut ini kita akan menambahkan dua baris pada table Pelanggan di database Penjualan, kemudian kita akan batalkan perintah penambahan data tersebut dengan menggunakan RollBack. Kita lihat data awal sebelum Transaksi. USE PENJUALAN Select * From Pelanggan
Hasilnya jumlah baris terdiri dari satu barus.
131
Sekarang kita akan mulai melakukan Transaksi pengisian data Pelanggan. Begin Tran T01 Insert Into Pelanggan (PelangganID,PelangganNama,Kota,Telp) Values ('PL002','M Zidan Arrizky','Jakarta','87654321') Insert Into Pelanggan (PelangganID,PelangganNama,Kota,Telp) Values ('PL003','Ahmad Faiz','Jakarta','91827364') Select * From Pelanggan
Akan terlihat hasil dari perintah query diatas akan menambah dua baris baru seperti terlihat pada tampilan berikut.
Sekarang adalah penentuan apakah transaksi diatas akan secara permanen akan diterapkan atau akan dibatalkan. Anggap kita akan membatalkan perintah query diatas dengan menuliskan Rollback ROLLBACK T01 Select * From Pelanggan
Setelah perintah diatas kita jalankan maka dua baris pengisian data baru diatas akan dibatalkan, akan dikembalikan kepada saat kondisi data pertama kali sebelum kita melakukan proses transaksi dengan BEGIN TRANSACTION, akan tampil seperti tampilan berkut.
132
Secara Lengkap penulisan Query diatas adalah seperti berikut ini USE PENJUALAN BEGIN TRAN T01 Insert Into Pelanggan (PelangganID,PelangganNama,Kota,Telp) Values ('PL002','M Zidan Arrizky','Jakarta','87654321') Insert Into Pelanggan (PelangganID,PelangganNama,Kota,Telp) Values ('PL003','Ahmad Faiz','Jakarta','91827364') ROLLBACK T01
Kita lakukan hal yang sama dalam penulisan query diatas, tetapi akhir dari BEGIN TRANSACTION kita ganti dengan COMMIT TRANSACTION, maka penambahan data diatas secara permanen akan menerap kedalam table Pelanggan. Berikut ini adalah penulisan lengkap query penggunaan Begin Transaction dan Commit Transaction. USE PENJUALAN SELECT * FROM PELANGGAN BEGIN TRAN T01 Insert Into Pelanggan (PelangganID,PelangganNama,Kota,Telp) Values ('PL002','M Zidan Arrizky','Jakarta','87654321') Insert Into Pelanggan (PelangganID,PelangganNama,Kota,Telp) Values ('PL003','Ahmad Faiz','Jakarta','91827364') COMMIT TRAN T01 SELECT * FROM PELANGGAN
Akan terlihat pada gambar berikut kondisi data sebelum transaksi, dan kondisi data setelah transaksi yang dipermanenkan penerapanya dengan perintah Commit Transaction.
133
Commit Transaction Penandaan akhir dari transaksi, melakukan pererapan secara permanen terhadap transaksi yang dilakukan setelah pernyataan BEGIN TRANSACTION. Syntax COMMIT [ TRAN [ SACTION ] [ transaction_name | @tran_name_variable ] ]
Rollback Transaction Mengembalikan data kembali ke kondisi seperti pertama kali sebelum terjadi transaksi, menutup transaksi yang diawali dengan pernyataan BEGIN TRANSACTION Syntax ROLLBACK [ TRAN [ SACTION ] [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ] ]
134
BAB 6 FUNGSI-FUNGSI
Fungsi-Fungsi SQL Server memiliki banyak fungsi yang dapat digunakan untuk kebutuhan pengolahan database, diantara fungsi-fungsi tersebut adalah sebagai berikut :
SQL Aggregate Functions SQL aggregate functions berfungsi untuk mengembalikan nilai tunggal, yang dikalkulasikan dari nilai pada sebuah kolom, berikut ini adalah fungsi Aggregate :
1. AVG()
– Mengembalikan nilai rata-rata
2. COUNT()
– Mengembalikan jumlah baris pada sebuah table
3. SELECT TOP
– Mengembalikan baris teratas, atau terbawah dari sebuah table
4. MAX()
– Mengembalikan nilai terbesar
5. MIN()
– Mengembalikan nilai terkecil
6. SUM()
– Mengembalikan hasil penjumlahan
Dalam simulasi contoh penggunaan Function diatas kita akan menggunakan database Northwind sebagai data untuk latihan.
135
Berikut ini contoh table Products
AVG() Function AVG() function mengembalikan nilai rata-rata dari sebuah kolom numerik SQL AVG() Syntax SELECT AVG(column_name) FROM table_name
SELECT AVG(UnitPrice) As Harga_Rata2 from Products
136
Hasilnya akan terlihat sebagai berikut :
Contoh : Menampilkan daftar produk yang harganya diatas harga rata-rata SELECT ProductName, Price FROM Products WHERE Price>(SELECT AVG(Price) FROM Products);
ProductName ---------------------------------------Uncle Bob's Organic Dried Pears Northwoods Cranberry Sauce Mishi Kobe Niku Ikura Queso Manchego La Pastora Alice Mutton Carnarvon Tigers Sir Rodney's Marmalade Gumbär Gummibärchen Schoggi Schokolade Rössle Sauerkraut Thüringer Rostbratwurst Mascarpone Fabioli Côte de Blaye Ipoh Coffee Manjimup Dried Apples Perth Pasties Gnocchi di nonna Alice Raclette Courdavault Camembert Pierrot Tarte au sucre Vegie-spread Wimmers gute Semmelknödel Gudbrandsdalsost Mozzarella di Giovanni (25 row(s) affected)
137
UnitPrice --------------------30.0000 40.0000 97.0000 31.0000 38.0000 39.0000 62.5000 81.0000 31.2300 43.9000 45.6000 123.7900 32.0000 263.5000 46.0000 53.0000 32.8000 38.0000 55.0000 34.0000 49.3000 43.9000 33.2500 36.0000 34.8000
COUNT() Function COUNT(column_name) function : menghitung jumlah baris pada kolom yang dispesifikan (nilai NULL tidak akan dihitung) SELECT COUNT(column_name) FROM table_name;
Contoh : Menghitung jumlah Product yang kategorID nya 1
Select count(categoryid) as Jml_Kategori FROM Products where categoryid=1
Jml_Kategori -----------12 (1 row(s) affected)
Menghitung jumlah Order pada table Orders SELECT COUNT(*) AS NumberOfOrder FROM Orders;
NumberOfOrder ----------------830 (1 row(s) affected)
Menghitung jumlah Customer yang melakukan Order pada table Orders SELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomers FROM Orders;
NumberOfCustomers ----------------89 (1 row(s) affected)
138
SELECT TOP Mengembalikan baris teratas, atau terbawah dari sebuah table Syntax SELECT TOP 1 column_name FROM table_name ORDER BY column_name ASC (DESC) ;
Contoh : Menampilkan daftar Product untuk 10 Jumlah Stock Tertinggi SELECT TOP 10 ProductName,UnitsInStock FROM Products Order By UnitsInStock Desc
ProductName ---------------------------------------Rhönbräu Klosterbier Boston Crab Meat Grandma's Boysenberry Spread Pâté chinois Sirop d'érable Geitost Inlagd Sill Sasquatch Ale Gustaf's Knäckebröd Röd Kaviar
UnitsInStock -----------125 123 120 115 113 112 112 111 104 101
(10 row(s) affected)
Menampilkan daftar Product untuk 10 Jumlah Stock Terendah SELECT TOP 10 ProductName,UnitsInStock FROM Products where UnitsInStock<>0 ORDER BY UnitsInStock ASC ProductName ---------------------------------------Sir Rodney's Scones Louisiana Hot Spiced Okra Longlife Tofu Rogede sild Northwoods Cranberry Sauce Scottish Longbreads Mascarpone Fabioli Nord-Ost Matjeshering Maxilaku Gravad lax (10 row(s) affected)
139
UnitsInStock -----------3 4 4 5 6 6 9 10 10 11
MAX() Function MAX() function : mengembalikan nilai tertinggi / terbesar dari sebuah kolom MAX() Syntax SELECT MAX(column_name) FROM table_name;
Contoh : Menampilkan informasi harga tertinggi dari setiap CategoryID pada table Products SELECT CategoryID,MAX(UnitPrice) as MaxPrice FROM Products GROUP BY CategoryID
CategoryID ----------1 2 3 4 5 6 7 8
MaxPrice --------------------263.5000 43.9000 81.0000 55.0000 38.0000 123.7900 53.0000 62.5000
(8 row(s) affected)
140
MIN() Function MIN() function : mengembalikan nilai terendah / terkecil dari sebuah kolom MIN() Syntax SELECT MIN(column_name) FROM table_name;
Contoh : Menampilkan informasi harga terendah dari setiap CategoryID pada table Products SELECT CategoryID,MIN(UnitPrice) as MinPrice FROM Products GROUP BY CategoryID
CategoryID ----------1 2 3 4 5 6 7 8
MinPrice --------------------4.5000 10.0000 9.2000 2.5000 7.0000 7.4500 10.0000 6.0000
(8 row(s) affected)
141
SUM() Function Mengembalikan total penjumlahan dari sebuah kolom numerik SUM() Syntax SELECT SUM(column_name) FROM table_name;
Contoh : Menampilkan data total penjualan untuk setiap ProductID pada table Order Details SELECT ProductID, SUM(Quantity) as TotalQty FROM [Order Details] GROUP BY ProductID ORDER BY ProductID
ProductID ----------1 2 3 4 5 6 7 8 9 dst ....
TotalQty ----------828 1057 328 453 298 301 763 372 95
(77 row(s) affected)
142
FUNGSI TANGGAL DAY Fungsi ini digunakan untuk mengambil nilai tanggal/hari dari suatu nilai tanggal lengkap yang menghasilkan nilai integer, syntax penulisan Fungsi Day seperti berikut ini : Syntax DAY ( date ) Arguments date sebuah expresi dari type datetime atau smalldatetime. Return Type int
Contoh Contoh berikut ini mengambil nilai hari dari suatu nilai tanggal 12/Mar/1998. SELECT DAY('12/Mar/1998') AS 'Day Number'
Hasil dari penilisan diatas adalah sebagai berikut : Day Number -----------12
Penulisan Tanggal dapat dilakukan dengan menggunakan format seperti diatas atau dengan format mm/dd/yy seperti 01/01/1997, penulisan tanggal
menggunakan nama bulan secara default
menggunakan format English seperti : Jan, Feb, Mar, Apr, May, Jun, Jul, August, Sep, Oct, Nov, Dec.
143
MONTH Mengembalikan nilai bulan dari suatu nilai tanggal lengkap yang menghasilkan nilai integer Syntax MONTH ( date ) Arguments date Nilai tanggal dengan format datetime or smalldatetime , atau sebuah karakter string dengan format tanggal. Penggunaan type data datetime dapat dilakukan hanya untuk tanggal diatas 1 Januari 1753 . Return Types Int
Contoh Contoh berikut ini mengambil nilai bulan dari suatu nilai tanggal 08/Dec/1978 SELECT "Month Number" = MONTH('08/Dec/1978')
Hasilnya akan terlihat: Month Number -------------------12
144
YEAR Mengambil nilai tahun dari suatu nilai tanggal lengkap dengan format yang menghasilkan nilai integer.
Syntax YEAR ( date )
Arguments date Sebuah expresi nilai dengan type datetime atau smalldatetime. Return Types int
DATEADD Menghasilkan nilai tanggal baru berdasarkan penambahan suatu nilai dengan interval tertentu dari suatu nilai tanggal. Syntax DATEADD ( datepart , number, date ) Arguments datepart Sebuah parameter yang menentukan jenis penambahan nilai tanggal baru berdasarkan jenis bagian tanggal tertentu yang dikenali oleh Microsoft SQL Server. Berikut ini parameter bagian tanggal yang dapat digunakan : Datepart
Abbreviations
Year
yy, yyyy
quarter
qq, q
Month
mm, m
dayofyear
dy, y
Day
dd, d
145
Week
wk, ww
Hour
hh
minute
mi, n
second
ss, s
Millisecond
ms
number merupakan nilai yang digunakan untuk penambahan pada datepart. jika anda menentukan nilai bukan dalam bentuk integer, sebagai contoh anda menentukan day untuk datepart dengan nilai 1.89, maka penambahan tanggal akan menggunakan nilai 1
date sebuah ekspresi berupa nilai datetime atau smalldatetime yang akan proses untuk penambahan, penulisan tahun sebaiknya ditulis dengan 4 (empat) dijit, jika ditulis dalam dua dijit sebagai contoh tahun 49 maka akan diakui sebagai 2049, dan tahun 50 akan diakui sebagai 1950, bukan 2050.
Return Types Mengembalikan nilai dalam format datetime, dan smalldatetime jika date argument dalam format smalldatetime.
Contoh 1. Contoh berikut ini akan menambahkan nilai tanggal sebanyak 15 hari dari 23 Mar 2002 SELECT DATEADD(day, 15, ’23 Mar 2002’) AS Tanggal
Hasil akan terlihat seperti tampilan berikut : Tanggal ---------------------------------2002-04-07 00:00:00.000
146
2. Contoh berikut ini akan menambahkan nilai bulan sebanyak 25 bulan dari 29 Jul 2005 SELECT DATEADD(Month, 25, '29 Jul 2005') AS Tanggal
Hasil akan terlihat seperti tampilan berikut : Tanggal ---------------------------------2007-08-29 00:00:00.000
3. Contoh berikut ini akan menambahkan nilai minggu sebanyak 25 Minggu dari 29 Jul 2005 SELECT DATEADD(Week, 25, '29 Jul 2005') AS Tanggal
Hasil akan terlihat seperti tampilan berikut : Tanggal ---------------------------------2006-01-20 00:00:00.000
4. Contoh berikut ini akan menambahkan nilai kuartal sebanyak 3 kuartal dari 29 Jul 2005 SELECT DATEADD(quarter, 3, '29 Jul 2005') AS Tanggal
Hasil akan terlihat seperti tampilan berikut : Tanggal ---------------------------------2006-04-29 00:00:00.000
5. Contoh berikut ini akan menambahkan nilai kuartal sebanyak 3 kuartal dari 29 Jul 2005 SELECT DATEADD(quarter, 3, '29 Jul 2005') AS Tanggal
Hasil akan terlihat seperti tampilan berikut : Tanggal ---------------------------------2006-04-29 00:00:00.000
147
6. Contoh berikut ini akan menambahkan nilai jam sebanyak 30 jam dari 31 Aug 2005 SELECT DATEADD(hour, 30, '31 Aug 2005') AS Tanggal
Hasil akan terlihat seperti tampilan berikut : Tanggal ---------------------------------2005-09-01 06:00:00.000
7. Contoh berikut ini akan menambahkan nilai menit sebanyak 1625 menit dari 31 Aug 2005 SELECT DATEADD(minute, 1625, '31 Aug 2005') AS Tanggal
Hasil akan terlihat seperti tampilan berikut : Tanggal ---------------------------------2005-09-01 03:05:00.000
148
DATENAME Mengembalikan nilai dalam bentuk karekter yang merepresentasikan nama penggalan suatu tanggal dari suatu nilai tanggal. Syntax DATENAME ( datepart , date ) Arguments Datepart Sebuah parameter yang menetapan bagian dari tanggal untuk dikembalikan, berikut ini list penulisan datepart yang dikenali oleh Microsoft SQL Server. Datepart Abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw hour hh minute mi, n second ss, s millisecond ms Return Types nvarchar Contoh: 1. Contoh berikut ini mengambil nilai nama bulan dari nilai tanggal 08-Dec-1978 SELECT DATENAME(month, '08 Dec 1978') AS 'Nama Bulan'
Hasil akan terlihat seperti tampilan berikut : Nama Bulan -----------------------------December
149
2. Contoh berikut ini mengambil nilai nama hari dari nilai tanggal 08-Dec-1978 SELECT DATENAME(weekday, '08 Dec 1978') AS 'Nama Hari'
Hasil akan terlihat seperti tampilan berikut : Nama Hari -----------------------------Friday
3. Contoh berikut ini mengambil nilai kuartal dari nilai tanggal 08-Dec-1978 SELECT DATENAME(quarter, '08 Dec 1978') AS 'Kuartal'
Hasil akan terlihat seperti tampilan berikut : Kuartal -----------------------------4
4. Contoh berikut ini mengambil nilai hari dalam satu tahun dari nilai tanggal 08-Dec-1978 SELECT DATENAME(dayofyear, '08 Dec 1978') AS 'Hari Ke Dalam Tahun'
Hasil akan terlihat seperti tampilan berikut : Hari Ke Dalam Tahun -----------------------------342
150
DATEDIFF Mengembalikan nilai berupa tanggal dan waktu yang merupakan selisih dari dua nilai tanggal. Syntax DATEDIFF ( datepart , startdate , enddate ) Arguments datepart sebuah parameter berupa bagian dari tanggal yang akan mengkalkulasikan perbedaan antara dua tanggal. Berikut ini list parameter datepart yang dikenali oleh Microsoft SQL Server. Datepart Year quarter Month dayofyear Day Week Hour minute second millisecond
Abbreviations yy, yyyy qq, q mm, m dy, y dd, d wk, ww hh mi, n ss, s ms
startdate Permulaan Tanggal yang akan dikalkulasikan, startdate merupakan sebuah ekspresi berupa nilai dengan format datetime atau smalldatetime atau karakter string dalam format tanggal. enddate Akhir dari nilai tanggal yang akan dikalkulasikan enddate merupakan sebuah ekspresi berupa nilai dengan format datetime atau smalldatetime atau karakter string dalam format tanggal. Return Types integer
151
Contoh : 1. Contoh berikut ini mencari selisih jumlah hari antara tanggal 12 Feb 2009 dan tanggal system dengan menggunakan GETDATE(), diasumsikan tanggal system adalah 11 Aug 2009
SELECT DATEDIFF(Day, '12 Feb 2009', getdate()) AS 'Jumlah Hari'
Hasil akan terlihat seperti tampilan berikut : Jumlah Hari -----------------------------180
2. Contoh berikut ini mencari selisih jumlah bulan antara tanggal 12 Feb 2009 dan tanggal system dengan menggunakan GETDATE(), diasumsikan tanggal system adalah 11 Aug 2009
SELECT DATEDIFF(Month, '12 Feb 2009', getdate()) AS 'Jumlah Bulan'
Hasil akan terlihat seperti tampilan berikut : Jumlah Bulan -----------------------------6
3. Contoh berikut ini mencari selisih jumlah Minggu antara tanggal 12 Feb 2009 dan tanggal system dengan menggunakan GETDATE(), diasumsikan tanggal system adalah 11 Aug 2009
SELECT DATEDIFF(Week, '12 Feb 2009', getdate()) AS 'Jumlah Minggu'
Hasil akan terlihat seperti tampilan berikut : Jumlah Minggu -----------------------------26
152
4. Contoh berikut ini mencari selisih jumlah jam antara tanggal 12 Feb 2009 dan tanggal system dengan menggunakan GETDATE(), diasumsikan tanggal system adalah 11 Aug 2009
SELECT DATEDIFF(hour, '12 Feb 2009', getdate()) AS 'Jumlah Jam'
Hasil akan terlihat seperti tampilan berikut : Jumlah Jam -----------------------------4333
5. Contoh berikut ini mencari selisih jumlah kuartal antara tanggal 12 Feb 2009 dan tanggal system dengan menggunakan GETDATE(), diasumsikan tanggal system adalah 11 Aug 2009
SELECT DATEDIFF(quarter, '12 Feb 2009', getdate()) AS 'Jumlah Kuartal'
Hasil akan terlihat seperti tampilan berikut : Jumlah Kuartal -----------------------------2
153
DATEPART Mengambil nilai bagian dari suatu nilai tanggal yang menghasilkan nilai integer. Syntax DATEPART ( datepart , date) Arguments datepart sebuah parameter yang merupakan bagian dari tanggal untuk nilai yang dikembalikan. Berikut ini list parameter datepart yang dikenali oleh Microsoft SQL Server. Datepart Year quarter Month dayofyear Day Week Hour minute second millisecond
Abbreviations yy, yyyy qq, q mm, m dy, y dd, d wk, ww hh mi, n ss, s ms
date Merupakan nilai tanggal yang akan diambil bagian nilainya, date merupakan sebuah ekspresi berupa nilai dengan format datetime atau smalldatetime atau karakter string dalam format tanggal. Return Types int Contoh : 1. Contoh berikut ini mengambil nilai hari dari tanggal 23 Mar 2002 SELECT DATEPART(Day, '23 Mar 2002') AS 'Nilai Hari'
Hasil akan terlihat seperti tampilan berikut : Nilai Hari -----------------------------23
154
2. Contoh berikut ini mengambil nilai Bulan dari tanggal 23 Mar 2002 SELECT DATEPART(Month, '23 Mar 2002') AS 'Nilai Bulan'
Hasil akan terlihat seperti tampilan berikut : Nilai Bulan -----------------------------3
3. Contoh berikut ini mengambil Jam dari Tanggal sistem saat ini dengan fungsi Getdate() SELECT GETDATE() as ‘Tanggal Sekarang’ SELECT DATEPART(hour, getdate) AS ‘Nilai Jam’
Hasil akan terlihat seperti tampilan berikut : Tanggal Sekarang -----------------------------2009-08-11 14:25:35.773 Nilai Jam ------------------------------
14
155
SET DATEFORMAT Menformat urutan tanggal untuk pengisian data tanggal yang memiliki format datetime atau smalldatetime. Syntax SET DATEFORMAT { format | @format_var } Arguments format | @format_var format untuk mengurutkan dalam pengisian nilai tanggal. Parameter yang diizinkan meliputi mdy, dmy, ymd, ydm, myd, dan dym. U.S. English default adalah mdy. Contoh 1. Contoh berikut ini memberikan nilai tanggal dengan format mdy SET DATEFORMAT mdy GO DECLARE @datevar datetime SET @datevar = '07/29/2009' SELECT @datevar GO
Jika Dateformat yang digunakan adalah mdy, sementara penulisan tanggal diatas ditulis dengan ‘29/07/2009’ maka penulisan tanggal akan dianggap salah dengan pesan kesalahan sebagai berikut : Server: Msg 242, Level 16, State 3, Line 2 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
2. Contoh berikut ini memberikan nilai tanggal dengan format ymd SET DATEFORMAT ymd GO DECLARE @datevar datetime SET @datevar = '2009/07/29' SELECT @datevar GO
156
3. Contoh berikut ini menampilkan data Orders berdasarkan tanggal dengan format dmy SET DATEFORMAT dmy USE NORTHWIND SELECT OrderID,OrderDate,CustomerID FROM Orders WHERE OrderData = ‘12/07/1996’
OrderID OrderDate CustomerID ----------- ------------------------------ ---------10255 1996-07-12 00:00:00.000 RICSU (1 row(s) affected)
157
CONVERT() Function Digunakan untuk menampilkan data tanggal dalam format yang berbeda Syntax CONVERT(data_type(length),expression,style) Value
Description
data_type(length)
Menentukan type data (panjang disesuaikan)
Expression
Menentukan nilai yang akan di konvert
Style
Menentukan format keluaran untuk tanggal
Value (century yy)
Value (century yyyy)
Input/Output
Standard
-
0 or 100
mon dd yyyy hh:miAM (or PM)
Default
1
101
mm/dd/yy
USA
2
102
yy.mm.dd
ANSI
3
103
dd/mm/yy
British/French
4
104
dd.mm.yy
German
5
105
dd-mm-yy
Italian
6
106
dd mon yy
7
107
Mon dd, yy
8
108
hh:mm:ss
-
9 or 109
mon dd yyyy hh:mi:ss:mmmAM (or PM) Default+millisec
10
110
mm-dd-yy
USA
11
111
yy/mm/dd
Japan
12
112
yymmdd
ISO
-
13 or 113
dd mon yyyy hh:mi:ss:mmm (24h)
14
114
hh:mi:ss:mmm (24h)
158
-
20 or 120
yyyy-mm-dd hh:mi:ss (24h)
-
21 or 121
yyyy-mm-dd hh:mi:ss.mmm (24h)
-
126
yyyy-mm-ddThh:mi:ss.mmm (no spaces)
ISO8601
-
130
dd mon yyyy hh:mi:ss:mmmAM
Hijiri
-
131
dd/mm/yy hh:mi:ss:mmmAM
Hijiri
Contoh : Mencetak tanggal system dengan menggunakan format diatas print print print print print print
CONVERT(VARCHAR(19),GETDATE()) CONVERT(VARCHAR(10),GETDATE(),10) CONVERT(VARCHAR(10),GETDATE(),110) CONVERT(VARCHAR(11),GETDATE(),6) CONVERT(VARCHAR(11),GETDATE(),106) CONVERT(VARCHAR(24),GETDATE(),113)
Hasilnya akan terlihat seprti berikut : Jul 22 2013 9:38AM 07-22-13 07-22-2013 22 Jul 13 22 Jul 2013 22 Jul 2013 09:38:24:703
Berikut ini kita coba menampilkan data Orders untuk tanggal tertentu, misalkan data yang akan ditampilkan adalah transaksi tanggal 12 Juli 1996 : SELECT OrderID,OrderDate,CustomerID FROM Orders WHERE OrderDate='12/07/1996'
Hasil penulisan SQL diatas tidak akan menemukan tanggal tersebut, walaupun dari transaksi pada tanggal tersebut ada, hal ini dikarekan format tanggal yang tidak sama, untuk memecahkan permasalahan ini kita dapat menggunakan Fungsi Convert sebagai alternatif pemecahan : SELECT OrderID,OrderDate,CustomerID FROM Orders WHERE Convert(Varchar(10),OrderDate,103)='12/07/1996'
159
OrderID OrderDate CustomerID ----------- ------------------------------ ---------10255 1996-07-12 00:00:00.000 RICSU (1 row(s) affected)
160
FUNGSI STRING LEFT Mengambil bagian nilai dari sisi sebelah kiri suatu nilai karakter Syntax LEFT ( character_expression , integer_expression )
Arguments character_expression Nilai karakter yang akan diambil bagian nilainya.
integer_expression Nilai integer untuk menentukan jumlah dijit karakter yang akan diambil.
Return Type varchar
Contoh Mengambil nilai karakter dari sebelah kiri sebanyak 8 dijit dari karakter ‘LP3I JAKARTA’ SELECT LEFT('LP3I JAKARTA',8) as 'Hasil'
Hasil akan terlihat seperti tampilan berikut : Hasil -----------------------------LP3I JAK
161
RIGHT Mengambil bagian nilai dari sisi sebelah kanan suatu nilai karakter Syntax RIGHT( character_expression , integer_expression )
Arguments character_expression Nilai karakter yang akan diambil bagian nilainya.
integer_expression Nilai integer untuk menentukan jumlah dijit karakter yang akan diambil.
Return Type varchar
Contoh Mengambil nilai karakter dari sebelah kanan sebanyak 10 dijit dari karakter ‘LP3I JAKARTA’ SELECT RIGHT('LP3I JAKARTA',10) as 'Hasil'
Hasil akan terlihat seperti tampilan berikut : Hasil -----------------------------3I JAKARTA
162
SUBSTRING Mengambil bagian dari nilai karakter,text atau image. Syntax SUBSTRING(expression , start , length )
Arguments expression suatu nilai berupa karakter, binary string, text, image
start Berupa nilai integer untuk menentukan mulai dijit yang akan diambil.
length Berupa nilai integer untuk menentukan jumlah karakter yang akan diambil. Contoh Mengambil nilai karakter ‘JAKAR’ dari karakter ‘LP3I JAKARTA’ SELECT SUBSTRING('LP3I JAKARTA',6,5) as 'Hasil'
Hasil akan terlihat seperti tampilan berikut : Hasil -----------------------------JAKAR
163
Mengambil nilai karakter ContactName dari table Customers pada database Northwind USE northwind Select ContactName,NickName=Substring(ContactName,1,3) From Customers
Hasil akan terlihat seperti tampilan berikut : ContactName
NickName
------------------------------ -------Maria Anders
Mar
Ana Trujillo
Ana
Antonio Moreno
Ant
Thomas Hardy
Tho
Christina Berglund
Chr
Hanna Moos
Han
Frédérique Citeaux
Fré
Dst ..
LEN Mengambil jumlah karakter pada suatu nilai string Syntax LEN ( string_expression )
Arguments string_expression nilai string yang akan diambil jumlah karakternya
Return Types Int
164
Contoh USE Northwind GO SELECT CompanyName,LEN(CompanyName) AS 'Panjang' FROM Customers
Hasil akan terlihat seperti tampilan berikut : CompanyName ---------------------------------------Alfreds Futterkiste Ana Trujillo Emparedados y helados Antonio Moreno Taquería Around the Horn Berglunds snabbköp Blauer See Delikatessen Blondesddsl père et fils Bólido Comidas preparadas
Panjang ----------19 34 23 15 18 23 24 25
dst …
LOWER Mengkonversi nilai karakter menjadi karakter dengan huruf kecil Syntax LOWER ( character_expression )
Arguments character_expression sebuah ekspresi dari karakter atau data binari, character_expression harus merupakan data yang memiliki tipe dapat dikonversikan kedalam varchar.
Return Types varchar
165
Contoh SELECT 'Huruf Kecil'= LOWER('Selamat dAtanG')
Hasil akan terlihat seperti tampilan berikut : Huruf Kecil -------------------selamat datang
UPPER Mengkonversi nilai karakter menjadi karakter kapital Syntax UPPER ( character_expression )
Arguments character_expression sebuah ekspresi dari karakter atau data binari, character_expression harus merupakan data yang memiliki tipe dapat dikonversikan kedalam varchar.
Return Types varchar
Contoh SELECT 'Huruf Kapital'= UPPER('Selamat dAtanG')
Hasil akan terlihat seperti tampilan berikut : Huruf Kapital -------------------SELAMAT DATANG
166
LTRIM Menghilangkan nilai kosong (blank) pada sebelah kiri karakter Syntax LTRIM ( character_expression )
Arguments character_expression sebuah ekspresi dari karakter atau data binari, character_expression harus merupakan data yang memiliki tipe dapat dikonversikan kedalam varchar.
Return Types varchar
Contoh SELECT '
LP3I JAKARTA' as N1, LTRIM('
Hasil akan terlihat seperti tampilan berikut : N1
N2
----------------
----------------
LP3I JAKARTA
LP3I JAKARTA
167
LP3I JAKARTA') as N2
RTRIM Menghilangkan nilai kosong (blank) pada sebelah kanan karakter Syntax RTRIM ( character_expression )
Arguments character_expression sebuah ekspresi dari karakter atau data binari, character_expression harus merupakan data yang memiliki tipe dapat dikonversikan kedalam varchar.
Return Types varchar Contoh SELECT 'LP3I
' + 'JAKARTA' as N1, RTRIM('LP3I
Hasil akan terlihat seperti tampilan berikut : N1
N2
----------------
----------------
LP3I
LP3IJAKARTA
JAKARTA
168
') + 'JAKARTA' as N2
FUNGSI MATEMATIKA ABS Mengambil nilai positiv dari suatu ungkapan numeric. Syntax ABS ( numeric_expression ) Arguments numeric_expression merupakan ungkapan numeric kecuali untuk type date bit. Contoh: SELECT X = ABS(-15.0)
Hasil akan terlihat seperti tampilan berikut : X ---------------15.0
CEILING Mengembalikan nilai bulat yang paling kecil, lebih besar dari atau sama dengan ungkapan numerik yang diberikan. Syntax CEILING ( numeric_expression ) Arguments numeric_expression merupakan ungkapan numeric kecuali untuk type date bit. Contoh: SELECT N1=CEILING(123.55), N2=CEILING(-123.95), N3=CEILING($0.0)
169
Hasil akan terlihat seperti tampilan berikut : N1 N2 N3 ------- ------- --------124 -123 .0000
ROUND Mengembalikan suatu ungkapan numerik, membulatkan kepada ketepatan atau panjang yang ditetapkan. Syntax ROUND (numeric_expression , length [ , function ] ) Arguments numeric_expression merupakan ungkapan numeric kecuali untuk type date bit.
length panjang pembulatan desimal yang akan ditetapkan.
Contoh: SELECT N1=ROUND(123.126723, 2)
Hasil akan terlihat seperti tampilan berikut : N1 ------123.130000
170
SQUARE Mengembalikan kelipatan dari sebuah ungkapan nilai (pangkat)
Syntax SQUARE (float_expression ) Arguments float_expression merupakan ungkapan dalam bentuk type Float. Return Types float
Contoh: SELECT N1=SQUARE(6),N2=SQUARE(15)
Hasil akan terlihat seperti tampilan berikut : N1 ------36.0
N2 -------225.0
171
FUNGSI LAIN ISNULL Merubah nilai NULL menjadi nilai tertentu. Syntax ISNULL ( check_expression , replacement_value ) Arguments check_expression Ungkapan yang akan decek nilai NULL nya, check_expression dapat berupa type data apapun.
replacement_value Nilai yang akan dikembalikan apabila check_expression bernilai NULL, replacement_value berupa nilai yang memiliki type yang sama dengan check_expression. Contoh: USE Northwind Select Country,City,Isnull(Region,'Belum Terdaftar') as Wilayah from customers Go
Hasil akan terlihat seperti tampilan berikut : Country --------------SA Venezuela Brazil Venezuela USA Ireland UK Germany France France Canada USA Germany USA Venezuela Venezuela USA Italy
City --------------Eugene Caracas Rio de Janeiro San Cristóbal Elgin Cork Cowes Brandenburg Versailles Toulouse Vancouver Walla Walla Frankfurt a.M. San Francisco Barquisimeto I. de Margarita Portland Bergamo
Wilayah --------------OR DF RJ Táchira OR Co. Cork Isle of Wight Belum Terdaftar Belum Terdaftar Belum Terdaftar BC WA Belum Terdaftar CA Lara Nueva Esparta OR Belum Terdaftar
172
IS [NOT] NULL Menentukan nilai sebuah ekspresi bernilai Null atau tidak Syntax expression IS [ NOT ] NULL Arguments Arguments expression Seluruh ekspresi atau ungkapan yang dikenali oleh Microsoft SQL Server Contoh: Menampilkan data Customers untuk yang Region nya bernilai Null USE Northwind Select Country,City,Region From Customers Where Region IS NULL Go
Hasil akan terlihat seperti tampilan berikut : Country --------------Germany Mexico Mexico UK Sweden Germany France Spain Dst . . .
City --------------Berlin México D.F. México D.F. London Luleå Mannheim Strasbourg Madrid
Region --------------NULL NULL NULL NULL NULL NULL NULL NULL
173
Menampilkan data Customers untuk yang Region nya tidak bernilai Null USE Northwind Select Country,City,Region From Customers Where Region IS NULL Go
Hasil akan terlihat seperti tampilan berikut : Country --------------USA Canada Canada USA Ireland Venezuela USA UK Venezuela USA USA Venezuela USA USA USA USA Canada Brazil Brazil Brazil Dst . . .
City --------------Anchorage Tsawassen Vancouver San Francisco Cork Caracas Boise Cowes Barquisimeto Butte Albuquerque I. de Margarita Eugene Elgin Portland Portland Montréal Rio de Janeiro Rio de Janeiro Rio de Janeiro
Region --------------AK BC BC CA Co. Cork DF ID Isle of Wight Lara MT NM Nueva Esparta OR OR OR OR Québec RJ RJ RJ
174
ISDATE Menentukan apakah suatu ekspresi atau ungkapan merupakan type Date yang benar. Syntax ISDATE ( expression ) Arguments expression Sebuah ekspresi yang akan divalidasi sebagai Date, expression merupakan ekspresi yang disajikan dalam format varchar. Return Types int ISDATE akan mengembalikan nilai 1 jika nilai yang divalidasi adalah benar sebuah ekspresi date, dan mengembalikan nilai 0 jika bukan bertipe date. Contoh: Contoh berikut ini menguji apakah nilai pada variable @taggal=’12/08/78’ merupakan penulisan tanggal yang valid. DECLARE @tanggal varchar(8) SET @tanggal = '12/08/98' SELECT ISDATE(@tanggal)
Hasil akan terlihat seperti tampilan berikut : --------------1
Contoh berikut ini menguji apakah nilai pada variable @taggal=’12/32/78’ merupakan penulisan tanggal yang tidak valid. DECLARE @tanggal varchar(8) SET @tanggal = '12/32/98' SELECT ISDATE(@tanggal)
Hasil akan terlihat seperti tampilan berikut : --------------0
175
ISNUMERIC Menentukan apakah sebuah sebuah ekspresi bertype Numerik atau tidak Syntax ISNUMERIC ( expression ) Arguments expression Ekspresi atau ungkapan yang akan dievaluasi apakah bernilai numrik atau tidak.
Hasil dari fungsi ISNUMERIC bernilai 1 jika ekspresi yang dievaluasi bertype numerik dan bernilai 0 jika tidak bertype numerik. Contoh: Menguji untuk nilai yang bertype numerik. Declare @a varchar(8) set @a = '1350' Select @a as N1, isnumeric(@a) as N2
Hasil akan terlihat seperti tampilan berikut : N1
N2
-------- ----------1350
1
Menguji untuk nilai yang bertype bukan numerik. Declare @a varchar(8) set @a = '1350a' Select @a as N1, isnumeric(@a) as N2
Hasil akan terlihat seperti tampilan berikut : N1
N2
-------- ----------1350
0
176
